Karakterek számlálása az Excel-ben
Az oktatóanyag után meg tudja számolni egy cellában vagy tartományban lévő összes karaktert, egy adott karakter vagy karakterkombináció előfordulásait egy cellában vagy tartományban, mindkét betű érzékeny és érzéketlen alternatívák. Az ebben az oktatóanyagban megszerzett ismeretek valóban jó alapot jelentenek a további kreatív Excel problémamegoldáshoz.
Karakterek számlálása az Excelben
Ha meg szeretné tudni, hogyan kell számolni a karaktereket az Excel programban, a LEN függvényt kell használnia, a képlet = LEN (cella) segítségével a cellában lévő összes karakter számolásához, vagy a SUMPRODUCT és a LEN függvény kombinációjával a tartomány összes karakterének számításához a (z) = SUMPRODUCT (LEN (tartomány)) képlettel. Ezenkívül részletesen ismertetjük egy cellában vagy tartományban egy adott karakter számlálását, valamint egy cellában vagy egy tartományban szereplő karakterek speciális kombinációját.
- A cellában lévő összes karakter megszámlálása
- A tartományban lévő összes karakter megszámlálása
- A cellában lévő egyedi karakterek megszámlálása
- Egy adott karakter egy tartományban
- a cellában vagy tartományban lévő karakterek specifikus kombinációjának megszámlálása
egy cellában lévő összes karakter számolása
egy cella könnyen megtalálható az Excel LEN funkcióval. Ennek a függvénynek csak egy argumentuma, cellahivatkozása vagy szövege van, ahol a teljes karakterszámot meg szeretné számolni:
=LEN(text)
=LEN(B3)
Ez a függvény megszámolja a cellák összes karakterét, beleértve a szóközöket, az írásjeleket és a szimbólumokat, annak ellenére, hogy hányszor fordulnak elő egy karakterláncban.
Ha egy cellában az összes karaktert meg akarjuk számolni, a szóközök nélkül, a LEN és a SUBSTITUTE képletek kombinációjára lesz szükség. Vessünk egy pillantást az alábbi példára, a szóközöket kizáró képlet a következőképpen néz ki:
=LEN(SUBSTITUTE(B3;" ";""))
A SUBSTITUTE függvény egy meghatározott cellában megváltoztatja az egyik karaktert / szöveget egy másik karakterrel / szöveggel. Ha ki akarjuk zárni a szóközöket, ezzel a funkcióval kiküszöböljük őket, és a szóközök minden előfordulását üres karakterlánccal helyettesítjük. A SUBSTITUTE után a függvény szövege a cellában a következőképpen néz ki:
= LEN (“Sunisshining”)
Ezután a LEN funkcióval könnyen meg lehet számolni az összes karaktert, az utolsó 12 karakter eredménye.
A tartomány összes karakterének megszámlálása
A megadott tartományban az összes karakter számának megszámításához két funkció kombinációjára van szükség, a SUMPRODUCT és a LEN. SUMPRODUCT függvényhasználat elegáns megoldás, amikor több cellával vagy tömbnel van dolgunk. Vessen egy pillantást az alábbi példára és a képletek kombinációjára:
=SUMPRODUCT(LEN(range))
=SUMPRODUCT(LEN(B3:B6))
A LEN függvény a cellához kapcsolódik, de cellatartományok az összesített LEN függvények használata (= LEN (B3) + LEN (B4) + LEN (B5) + LEN (B6)) nem a legjobb megoldás, mivel hatalmas tartományokkal lehetne foglalkozni. Ehelyett a SUMPRODUCT függvény összefoglalja A LEN függvény eredményei a megadott tartományban. A képlet kiértékelésében az eredmény a következőképpen fog kinézni:
A tömbből származó számok a meghatározott B3: B6 tartomány minden cellájának LEN függvényeredményei, a végső karaktereredmény 75-ös.
A tartományok kezelésének alternatív megoldása a SUM és LEN funkciókombináció használata. A szintaxis szinte ugyanaz, a göndör zárójelek használatának egyetlen különbségével, a SUM függvény tömbképletbe történő konvertálásához (A tömb létrehozásának parancsikonja CTRL + SHIFT + ENTER). Az alábbi képletben szerepel a SUM / LEN függvénykombináció pontos szintaxisa:
={SUM(LEN(B3:B6))}
Specifikus karakterek számlálása egy cellában
A teljes karakterszám mellett az Excelben lehetőség van arra is, hogy megszámolja az adott karakterek előfordulásának számát. Nézzük át azt a példát, hogy egy adott cellában megszámolhatjuk-e az egyetlen karaktert. Erre a célra a LEN és a SUBSTITUTE kombinációra van szükség, ahogyan azt a hasonló példában tettük, amikor a cellában a karakterek számát szóköz nélkül számoltuk meg. A képlet szintaxisa a következőképpen fog kinézni:
=LEN(cell)-LEN(SUBSTITUTE(cell;character;""))
Egy konkrét példában, ha meg akarjuk számolni az s karakterek számát egy megadott a B3 cellában a képlet a következőképpen fog kinézni:
=LEN(B3)-LEN(SUBSTITUTE(B3;"s";""))
Magyarázzuk el röviden a függvénykombináció logikáját. A B3 cellában lévő összes karakterszámot kivonjuk az ugyanabban a cellában lévő karakterszámmal, de külön karakter nélkül, amelyet meg akarunk számlálni. Amint az az 1. oktatópontban említettük, a SUBSTITUTE függvény egy megadott cellában lévő karakterlánc karakteres formában történő megváltoztatására szolgál, külön karakter nélkül, helyettesítve ezt a karaktert egy üres karakterlánccal.
=SUBSTITUTE(cell;"character";"")
A végeredményben észrevehető, hogy a függvény nem számít nagybetűs karakterekre, mivel a LEN függvény a kis- és nagybetűket érzékeli. A karakterek megkülönböztetéséhez a kis- és nagybetűk megkülönböztetése nélkül az UPPER / LOWER függvény használata lehetséges, ahol az összes karakter nagybetűsre / kisbetűvé lesz fordítva, és a funkció kis- és nagybetűvé nem válik.
Az alábbi példában a LOWER be van ágyazva a SUBSTITUTE függvénybe, és a B3 cellában lévő összes karakterláncot kisbetűvé változtatja, mivel a feltételeket kisbetűként definiálják, “s”:
=LEN(cell)-LEN(SUBSTITUTE(LOWER(cell);"lowercase character";""))
=LEN(B3)-LEN(SUBSTITUTE(LOWER(B3);"s";""))
A nem kis- és nagybetűk közötti függvények létrehozásának másik megoldása: a kettős beágyazott SUBSTITUTE függvény használata a LEN függvénnyel kombinálva. A további példában a számláló karakter egy adott cellában lesz, mert néha nem célszerű minden egyes alkalommal írni, hogy egy karaktert megszámláljon a képletben, különösen, ha foglalkozik összetett tartományokkal és képletekkel.
Képlet kettős beágyazott SUBSTITUTE függvénnyel:
=LEN(cell)-LEN(SUBSTITUTE(SUBSTITUTE(cell;LOWER(character);"");UPPER(character);""))
=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))
Lehet, hogy a képlet összetettnek tűnik, de minden egyértelmű lesz a magyarázat után. Vizsgáljuk meg a képletet lépésről lépésre.
Először ki akarjuk iktatni a kisbetűs számláló karaktert a szövegből:
=SUBSTITUTE(B3;LOWER(B6);"")
=SUBSTITUTE("Sun is shining ”;LOWER("S”);"")
A LOWER függvény azért kerül be, hogy a számláló karaktert kisbetűvé fordítsa, majd a + SUBSTITUTE függvény kicseréli a kisbetűket üres karakterláncra. A lépés után a képlet a következőképpen néz ki: “Sun i hining”.
A következő képlet lépésben a cél a nagybetűs számláló karakter kiküszöbölése a megadott szövegből / cellából. Ezt a beágyazott SUBSTITUTE függvény oldja meg. :
=SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")
A SUBSTITUTE függvény “Sun i hining” eredményét már kifejtettük, és az alábbi függvénybe helyezzük: világosabbá tegye a helyzetet. Az első SUBSTITUTE függvényben a kisbetűket üres karakterláncra cserélték, a másodikban a SUBSTITUTE függvényben a nagybetűs számláló karaktereket üres karakterláncokra cserélték, így a szöveg “s” és “S” karakterek száma nélkül: “un i hining” .
=SUBSTITUTE("Sun i hining”;UPPER("S”);"")
A LEN függvény csak megszámolja a karakterek számát egy módosított szövegből:
=LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))
=LEN("un i hining”)
Az utolsó lépésben értékelt képleteredmény, “s” nélküli szöveg és az “S”, kivonva a megadott cellák összes karakterének számával:
=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))
=LEN("Sun is shining”) - LEN("un i hining”)
Specifikus karakterek megszámlálása egy tartományban
Ha cellatartományokkal és tömbökkel van dolgunk, a SUMPRODUCT függvényre más funkciókkal kombinálva van szükség. karakter egy tartományban, a kis- és nagybetűk megkülönböztetéséhez három funkció kombinációját használják: SUMPRODUCT, LEN és SUBSTITUTE. A kis- és nagybetűk nélküli számláláshoz további képleteket kell hozzáadni: FELSŐ / ALSÓ.
Először elmagyarázzuk az “S” karakter előfordulásának számítását egy meghatározott tartományban (kis- és nagybetűk nélküli változat). szinte hasonló a számolási karaktereket magyarázó képlethez. A különbség a SUMPRODUCT függvénybe beágyazott LEN függvényből és a számlálási területből származik, a cella helyett a cellák meghatározott tartománya van:
=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;"")))
A megadott tartományban kis- és nagybetűk nélküli karakterszámláláshoz használhatjuk a részletes magyarázatot -érzéketlen karakterszámlálás egy adott cellában a 3. oktatópontban, két változtatással: a LEN függvény beágyazása a SUMPRODUCT függvénybe, és a cella helyettesítése cellatartománnyal:
=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);"")))
Ne feledje, hogy amikor cellatartományokkal foglalkozik, akkor a SUMPRODUCT függvényt kell használnia. ra-ban számolva Szükség van a funkciók kombinációjára: SUMPRODUCT, LEN és SUBSTITUTE, valamint a kis- és nagybetűk nélküli számlálásra: SUMPRODUCT, LEN, SUBSTITUTE és UPPER / LOWER függvények.
A cellában vagy tartományban szereplő karakterek kombinációja
Lehetőség van egy meghatározott karakterkombináció megszámolására is egy meghatározott cellában vagy tartományban. Korábbi oktatópontjainkban egy-egy karakterben történő számolást ismertettünk egy cellában vagy tartományban kis- és nagybetű érzékeny / érzéketlen alternatívákkal. A karakterkombináció számlálásának képlete megegyezik, csak nekünk kell elosztanunk a karakterkombinációban szereplő karakterek számával.
Vessünk egy pillantást az alábbi példára a cellában szereplő karakterkombinációk számolásához (kis- és nagybetűk nélküli változat):
=(LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")))/LEN(B6)
A logika megegyezik az egyes karakterek számlálásával, csak a szabályos képletet el kellett osztanunk a megadott karakterek számával számolunk, könnyen használva a következő képletet: LEN (“in”). A képlet LEN (“in”) osztása nélkül az eredmény megszorozódna a karakterkombinációban szereplő karakterek számával (példánkban 2-vel, mivel “in” ”Két karakterből áll)
A cellák karakterkombinációinak számításához (a kis- és nagybetűk közötti változat) a képlet a következőképpen fog kinézni:
=(LEN(B3)-LEN(SUBSTITUTE(B3;B6;"")))/LEN(B6)
A tartományban lévő karakterkombinációk logikája megegyezik az egyetlen karakter számlálásával, és az esetre az érzékeny verzió képlete a következőképpen fog kinézni:
=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);""))))/LEN(B9)
Szabályos képlet magyarázat A 4. témakör a LEN függvény használatával fel van osztva a számolni kívánt karakterek számával.
Ha kis- és nagybetűk nélküli verziót akarunk, akkor a képlet szintaxisa a következőképpen fog kinézni:
=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;""))))/LEN(B9)
Szüksége van még valamilyen segítségre az Excel formázásával kapcsolatban, vagy további kérdései vannak az Excel programmal kapcsolatban? Lépjen kapcsolatba egy élő Excel-szakértővel itt, ahol néhány az 1-hez segítséget nyújt. Az első munkamenet mindig ingyenes.