Általános képlet
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Összegzés
Ha meg akarja számolni, hogy egy adott szó (vagy bármely részrész) hányszor jelenik meg egy cellatartományon belül, használhat egy SUBSTITUTE, LEN és SUMPRODUCT függvényeken alapuló képletet. A bemutatott példában a C11 képlete a következő:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Megjegyzés: Az ezen az oldalon található képlet megszámolja egy tartomány szójának az előfordulását. Például, ha egy cellában két szó szerepel, akkor 2-vel járul hozzá a teljes számhoz. Ha csak egy adott szót tartalmazó cellákat szeretne megszámolni, olvassa el ezt az egyszerű képletet a COUNTIF függvény alapján.
Magyarázat
A képlet általános változatában az rng az ellenőrizendő tartományt jelenti, a txt pedig a számolni kívánt szó vagy alszöveg.
A bemutatott példában a B5: B8 az ellenőrizendő tartomány, a C2 pedig a megszámlálandó szöveget (szót vagy alszöveget) tartalmazza.
A tartomány minden cellájához a SUBSTITUTE eltávolítja az alszöveget az eredeti szövegből, és a LEN kiszámítja a szöveg hosszát az alfejezet nélkül. Ezt a számot ezután kivonjuk az eredeti szöveg hosszából. Az eredmény a SUBSTITUTE által eltávolított karakterek száma.
Ezután az eltávolított karakterek számát elosztjuk az alsor hosszával. Tehát, ha egy alszöveg vagy szó 5 karakter hosszú, és 10 karakter hiányzik, miután eltávolítottuk az eredeti szövegből, tudjuk, hogy az alfej / szó kétszer is megjelent az eredeti szövegben.
Mivel a fenti számítás a SUMPRODUCT függvénybe van csomagolva, az eredmény egy tömb, amely számot tartalmaz a tartomány minden cellájához. Ezek a számok jelzik az alszekvenciák előfordulásának számát az egyes cellákban. Ebben a példában a tömb így néz ki: (1; 1; 0; 1)
Végül a SUMPRODUCT összesíti a tömb összes elemét, hogy megkapja a szubsztring összes előfordulását a cellák tartományában.
Az ügy figyelmen kívül hagyása
A SUBSTITUTE a kis- és nagybetűk függvénye, ezért helyettesíti a kis- és nagybetűket, ha futtatja a helyettesítést. Ha meg kell számolnia egy szó vagy alszöveg kis- és nagybetűk előfordulását, akkor a helyettesítés futtatása előtt használja a SUBSTITUTE FELSŐ funkcióját a szöveg nagybetűvé konvertálásához:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
