Yleinen kaava
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Yhteenveto
Voit laskea, kuinka monta kertaa tietty sana (tai mikä tahansa alimerkkijono) esiintyy solualueella, käyttämällä kaavaa, joka perustuu SUBSTITUTE, LEN ja SUMPRODUCT -funktioihin. Esitetyssä esimerkissä kaavassa kohdassa C11 on:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Huomaa: Tämän sivun kaava laskee alueen sanan esiintymät. Esimerkiksi, jos solu sisältää kaksi sanan esiintymää, se lisää 2 kokonaismäärään. Jos haluat vain laskea tietyn sanan sisältävät solut, katso tämä yksinkertainen kaava, joka perustuu funktioon COUNTIF.
Selitys
Kaavan yleisessä versiossa rng edustaa tarkistettavaa aluetta ja txt on laskettava sana tai alisarja.
Esitetyssä esimerkissä B5: B8 on tarkistettava alue, ja C2 sisältää laskettavan tekstin (sana tai alimerkkijono).
Jokaisen alueen solun kohdalla SUBSTITUTE poistaa alimerkinnän alkuperäisestä tekstistä ja LEN laskee tekstin pituuden ilman alaosaa. Tämä luku vähennetään sitten alkuperäisen tekstin pituudesta. Tulos on niiden merkkien lukumäärä, jotka SUBSTITUTE poisti.
Sitten poistettujen merkkien lukumäärä jaetaan alamerkkijonon pituudella. Joten, jos alimerkkijono tai sana on 5 merkkiä pitkä ja 10 merkkiä puuttuu sen jälkeen, kun se on poistettu alkuperäisestä tekstistä, tiedämme, että alaosa / sana esiintyi kahdesti alkuperäisessä tekstissä.
Koska yllä oleva laskelma on kääritty SUMPRODUCT-funktioon, tulos on taulukko, joka sisältää numeron jokaiselle alueen solulle. Nämä numerot edustavat alisarjan esiintymien määrää kussakin solussa. Tässä esimerkissä taulukko näyttää tältä: (1; 1; 0; 1)
Lopuksi SUMPRODUCT laskee yhteen kaikki matriisin kohteet saadakseen kokonaisalamerkkijonot solualueella.
Ohitetaan tapaus
SUBSTITUTE on kirjainkokoinen funktio, joten se vastaa kirjainkokoa suoritettaessa korvausta. Jos sinun on laskettava sekä isojen että pienten kirjainten esiintyminen sanassa tai alaotsikossa, käytä SUBSTITUTE-tekstin sisällä olevaa UPPER-funktiota, kun haluat muuntaa tekstin isoksi, ennen kuin suoritat korvaamisen:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
