Ha gyakran dolgozunk szövegeket tartalmazó listával, időről időre előfordul, hogy kíváncsiak vagyunk, hányszor fordul elő egy bizonyos karakter vagy karaktersorozat egy szövegben. Amikor először találkoztam ezzel a problémával, azt hittem, találok az Excelben egy függvényt, ami pontosan erre a kérdésre ad választ. Sajnos azonban ilyen függvényt nem találtam. Valószínűleg nem lehet annyira gyakori az ilyen vizsgálat, mint ahogy gondoltam, vagy mindenki elégedett azokkal a megoldásokkal, amelyeket végül én is használtam és amelyeket a következőkben bemutatok.
Excel függvények
Habár nincs egy, kimondottan ezt elvégző függvény az Excelben, a feladatot több, alapértelmezetten is elérhető függvény kombinációjával végre lehet hajtani. Az alapvető logika az, hogy megnézzük, milyen hosszú az eredeti szöveg, majd kicseréljük a szövegben a keresett karaktereket üres szövegre ("semmire") és megnézzük, milyen hosszú a módosított szöveg. Az eredeti szöveg hosszából kivonva a módosított szöveg hosszát, megkapjuk, hogy hányszor fordul elő benne a keresett karakter. Mindez megírható egyetlen függvényben is a HOSSZ (LEN) és a HELYETTE (REPLACE) segítségével:
A példában email címek listáját (véletlenszerűen generált mintaadatok) szeretnénk ellenőrizni. Az első kérdés, hogy vajon mindegyik email címben csak egyetlen @ (kukac) karakter szerepel-e? Az A2 cellában szereplő email címet ezzel a függvénnyel tudjuk ellenőrizni:
=HOSSZ(A2)-HOSSZ(HELYETTE(A2;"@";""))
A zöld színnel jelölt függvény adja meg az eredeti szöveg hosszát, a kék pedig a csere utáni, módosított szöveg hosszát. A "@" cseréjével bármelyik másik karakter előfordulási gyakoriságát tudjuk ellenőrizni, például a pontokét (".").
A fenti megoldás már nagyon sok esetben használható, azonban ha nem csak egyetlen karakter, hanem egy több karakterből álló karaktersorozat előfordulási gyakoriságára vagyunk kíváncsiak, akkor hibás eredményt ad. Vizsgáljuk meg egy példán keresztül, hogy miért lehet ez? Tegyük fel, hogy a "csirkecsőr" szóban szeretnénk meghatározni a "cs" karaktersorozat előfordulásainak számát! Az eredeti szó hossza 10 karakter. A csere után a módosított szó ("irkeőr") hossza 6 karakter. A kettő különbsége (10-6) 4, tehát ennyiszer fordulna elő a szóban a "cs" karaktersorozat? Biztosan nem, látjuk, hogy mindössze kettőször. A két hossz különbsége valójában azt adja meg, hogy a keresett karaktersorozat hány karakternyi helyet foglal el a vizsgált szövegben. Az előbbi példában a 2 darab "cs" karaktersorozat összesen 4 karakternyi helyet foglalt el a 10-ből. Az eddig használt képlet eredményét tehát el kell osztani a keresett karaktersorozat hosszával! Az email címek vizsgálatát folytatva a "nu" karaktersorozatot keresve így néz ki a javított képle:
=(HOSSZ(A2)-HOSSZ(HELYETTE(A2;"nu";"")))/HOSSZ("nu")
Az első példában azért nem okozott ez hibát, mert a keresett karaktersorozat egyetlen karakterből állt. Ennek hossza 1, az 1-el való osztás pedig ugyanazt a számot adja. Ha tehát csak egyetlen karaktert keresünk, nem pedig karaktersorozatot, akkor az osztásra nincs szükség.
Az eddigi megoldásunk már sokkal elegánsabb és univerzálisabban is használható, különösen, ha a keresett karakter(sorozato)t egy cellába írjuk és a képletben a "nu" helyére ennek a cellának a rögzített hivatkozását írjuk. Viszont egy dologgal még tehetnénk szebbé az eredményt: ha megkérhetnénk, hogy a kis- és nagybetűk különbségeit is hagyja figyelmen kívül. Jelenleg a "nu" karaktersorozatot keresve talál eredményt, de nem mindet! Ahol "Nu" szerepel, a kis- és nagybetű különbsége miatt nem lesz találat. Ezt úgy tudjuk áthidalni, hogy a módosítás során az eredeti szöveget és a keresett karaktersorozatot is vagy csupa kisbetűssé vagy csupa nagybetűssé tesszük. Ehhez használhatjuk a KISBETŰ (LOWER) vagy NAGYBETŰS (UPPER) függvények bármelyikét.
=(HOSSZ(A2)-HOSSZ(HELYETTE(KISBETŰ(A2);KISBETŰ($B$1);"")))/HOSSZ($B$1)
Így a "nu" karaktersorozatot keresve már a "nu" és "Nu" (sőt, ha lenne, akkor a "nU" és "NU") előfordulásait is számolja.
Excel makró
Az előző, igen hosszúra nyúlt képlet helyett elkészíthetjük a saját függvényünket is.
Nem ez az első alkalom, hogy saját Excel függvényt készítünk! Javaslom, fusd át az Excel makró - Egyéni függvény létrehozása című írásunkat, valamint ha szükségesnek érzed, az Excel makró - Alapfogalmak című cikket is!
A makró Function lesz, ami eredményül egy számot ad és 3 bemeneti paramétert adhatunk meg neki:
- txt (String): a szöveg, amelyben keresünk,
- char (String): a karakter(sorozat), amit keresünk,
- caseSensitive (Boolean): logikai változó, amely azt jelöli, hogy a függvény figyeljen-e a kis- és nagybetű különbségeire. Nem kötelező megadni, alapértelmezett értéke az IGAZ.
Function CountCharacter(txt As String, char As String, Optional caseSensitive As Boolean = True) As Long
'Ide kerül a makró fő logikája'
End Function
A makró belsejében egy döntési logikát helyezünk el (If... Then... Else), attól függően, hogy a mi a logika változó (caseSensitive) értéke. A függvény által visszaadott érték számítása nagyon hasonló az Excel függvényekhez, amelyeket fent összeállítottunk, csupán az angol elnevezéseket kell használnunk.
Function CountCharacter(txt As String, char As String, Optional caseSensitive As Boolean = True) As Long
If caseSensitive Then
CountCharacter = (Len(txt) - Len(Replace(txt, char, ""))) / Len(char)
Else
CountCharacter = (Len(txt) - Len(Replace(LCase(txt), LCase(char), ""))) / Len(char)
End If
End Function
Ha ezt a makró elhelyezzük a munkafüzetünkben, elérhető lesz a CountCharacter() függvény.
=CountCharacter(A2;$B$1;HAMIS)
Használjátok sok sikerrel és nyúljatok hozzá bátran a kódhoz, módosítgassátok, próbálkozzatok, így egyre jobban fogjátok érteni az Excel makrók működését!