Általános képlet
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Összegzés
Ha meg kell számolnia, hogy egy adott szó (vagy bármely részrész) hányszor jelenik meg egy cellában, használhat egy olyan képletet, amely SUBSTITUTE és LEN-t használ. A fenti képlet általános formájában a "szöveg" egy cellát tartalmaz, amely szöveget tartalmaz, a "szó" pedig a számolni kívánt szót vagy alszöveget. A példában ezt a képletet használjuk:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Magyarázat
B4 az a cella, amelyben a szavakat számoljuk, és a C4 tartalmazza az Ön által számított alszöveget (szót vagy bármely részt).
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.
Végül az eltávolított karakterek számát elosztjuk az alsor hosszával. Tehát, ha az alszöveg 5 karakter hosszú, és 10 karakter hiányzik, miután eltávolítottuk az eredeti szövegből, tudjuk, hogy az alszöveg kétszer jelent meg az eredeti szövegben.
Kezelő tok
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:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Mivel ez a képlet a szubsztrátumot és a szöveget nagybetűvé alakítja, mielőtt a helyettesítést végrehajtaná, minden esetben ugyanolyan jól fog működni a szöveggel.
Problémák kezelése
A szavak számlálása az Excelben bonyolult, mert az Excel nem támogatja a reguláris kifejezéseket. Ennek eredményeként nehéz megcélozni azokat a szavakat, amelyeket pontosan meg akar számlálni, miközben figyelmen kívül hagyja az alszövegeket és más részleges egyezéseket (azaz megtalálja a "rókát", de nem a "rókákat"). Az írásjelek és a kis- és nagybetűs variációk miatt ez a probléma meglehetősen kihívást jelent.
Az egyik megoldás egy másik képlet használata egy segítő oszlopban a "szöveg normalizálása" első lépésként. Ezután az ezen az oldalon található képlet segítségével számolja meg a szóközökbe burkolt szavakat, hogy pontos számot kapjon (azaz a "rókát" megkeresheti a normalizált szövegben.
Megjegyzés: ez a megközelítés csak olyan jó, mint a normál szöveg, amelyet létrehozhat, és a kívánt eredmény elérése érdekében sokszor módosítania kell a normalizálási képletet.
