Po tomto výukovém programu budete moci spočítat celkový počet znaků v buňce nebo oblasti, výskyty konkrétních kombinací znaků nebo znaků v buňce nebo oblasti, přičemž se rozlišují malá a velká písmena a necitlivé alternativy. Znalosti získané v tomto výukovém programu jsou opravdu dobrým základem pro další kreativní řešení problémů s Excelem.

Jak počítat znaky v aplikaci Excel

Pokud se chcete naučit počítat znaky v aplikaci Excel, musíte použít funkci LEN, pomocí vzorce = LEN (buňka) pro počítání celkového počtu znaků v buňce, nebo kombinace funkcí SUMPRODUCT a LEN pro počítání celkového počtu znaků v rozsahu se vzorcem = SUMPRODUCT (LEN (rozsah)). Dále bude podrobně vysvětleno počítání konkrétního znaku v buňce nebo oblasti a konkrétní kombinace znaků v buňce nebo oblasti.

1. Počet celkových znaků v buňce
2. Počet celkových znaků v rozsahu
3. Počet konkrétních znaků v buňce
4. počet Specifický znak v rozsahu
5. Počet specifických kombinací znaků v buňce nebo rozsahu

Počet celkových znaků v buňce

Celkový počet znaků v buňku lze snadno najít pomocí funkce Excel LEN. Tato funkce má pouze jeden argument, odkaz na buňku nebo text, kde se má počítat celkový počet znaků:

=LEN(text)

=LEN(B3)

Tato funkce počítá celkový počet znaků v buňce, včetně mezer, interpunkčních znamének, symbolů, bez ohledu na to, kolikrát se v řetězci vyskytnou.

Pokud chceme v buňce spočítat celkový počet znaků, s výjimkou mezer, bude nutná kombinace vzorců LEN a SUBSTITUTE. Podívejme se na příklad níže, vzorec vylučující mezery vypadá takto:

=LEN(SUBSTITUTE(B3;" ";""))

Funkce SUBSTITUTE mění v definované buňce jeden znak / text s jiným znakem / textem. Pokud chceme vyloučit mezery, pomocí této funkce budou odstraněny a všechny výskyty mezer budou nahrazeny prázdným řetězcem. Po SUBSTITUTE by text funkce v buňce vypadal takto:

= LEN („Sunisshining“)

Poté je snadné spočítat celkový počet znaků pomocí funkce LEN, přičemž poslední výsledek 12 znaků.

Počet celkových znaků v rozsahu

Pro počítání celkového počtu znaků v definovaném rozsahu je nutná kombinace dvou funkcí, SUMPRODUCT a LEN. Použití funkce SUMPRODUCT je elegantní řešení, kdykoli pracujeme s více buňkami nebo poli. Podívejte se na níže uvedený příklad a kombinaci vzorců:

=SUMPRODUCT(LEN(range))

=SUMPRODUCT(LEN(B3:B6))

Funkce LEN souvisí s buňkou, ale pro využití rozsahů buněk sečtených funkcí LEN (= LEN (B3) + LEN (B4) + LEN (B5) + LEN (B6)) není nejlepším řešením, protože bychom mohli pracovat s obrovskými rozsahy. Místo toho funkce SUMPRODUCT shrnuje výsledky funkce LEN v definovaném rozsahu. Při hodnocení vzorce bude výsledek vypadat takto:

Čísla z pole jsou výsledky funkce LEN z každé buňky v definovaném rozsahu B3: B6, což dává konečné výsledky znaků 75.

Alternativním řešením pro práci s rozsahy je použití kombinace funkcí SUM a LEN. Syntaxe je téměř stejná, s jediným rozdílem použití složených závorek, aby bylo možné převést funkci SUM ve vzorci pole (zkratka pro vytvoření pole je CTRL + SHIFT + ENTER). Ve vzorci níže je přesná syntaxe pro kombinaci funkcí SUM / LEN:

={SUM(LEN(B3:B6))}

Počet konkrétních znaků v buňce

Kromě celkového počtu znaků existuje v aplikaci Excel také možnost počítání počtu výskytů konkrétních znaků. Pojďme si projít příklad počítání počtu jednotlivých znaků v konkrétní buňce. Pro tento účel je nutná kombinace funkcí LEN a SUBSTITUTE, jak jsme to udělali v podobném příkladu počítání počtu znaků v buňce bez mezery. Syntaxe vzorce bude vypadat takto:

=LEN(cell)-LEN(SUBSTITUTE(cell;character;""))

V konkrétním příkladu, pokud chceme spočítat počet znaků v definovaném buňka B3, vzorec bude vypadat takto:

=LEN(B3)-LEN(SUBSTITUTE(B3;"s";""))

Vysvětlíme stručně logiku kombinace funkcí. Celkové číslo znaku v buňce B3 se odečte od čísla znaku ve stejné buňce, ale bez konkrétního znaku, který chceme počítat. Jak je uvedeno v tutoriálu bod 1, funkce SUBSTITUTE se používá ke změně řetězce v definované buňce ve formě bez konkrétního znaku a nahrazení tohoto znaku prázdným řetězcem.

=SUBSTITUTE(cell;"character";"")

V konečném výsledku je patrné, že funkce nepočítá velká písmena, protože funkce LEN rozlišuje velká a malá písmena. Řešením pro počítání znaků bez rozlišování velkých a malých písmen je použití funkce UPPER / LOWER, kde budou všechny znaky přeloženy na velká / malá písmena a funkce nebude rozlišovat velká a malá písmena.

V níže uvedeném příkladu funkce LOWER je vnořeno do funkce SUBSTITUTE a mění celý řetězec v buňce B3 na malá písmena, protože kritéria jsou definována jako malá písmena, „s“:

=LEN(cell)-LEN(SUBSTITUTE(LOWER(cell);"lowercase character";""))

=LEN(B3)-LEN(SUBSTITUTE(LOWER(B3);"s";""))

Další řešení pro vytváření funkcí nerozlišujících velká a malá písmena je použití funkce SUBSTITUTE s dvojitým vnořením v kombinaci s funkcí LEN. V dalším příkladu bude počítání znaků v konkrétní buňce, protože někdy není praktické pokaždé psát, aby se spočítal znak ve vzorci, zvláště pokud máte co do činění se složitými rozsahy a vzorci.

Vzorec s dvojitě vnořenou funkcí SUBSTITUTE:

=LEN(cell)-LEN(SUBSTITUTE(SUBSTITUTE(cell;LOWER(character);"");UPPER(character);""))

=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

Vzorec může vypadat složitě, ale po vysvětlení bude vše jasné. Pojďme vyhodnotit vzorec krok za krokem.

Nejprve chceme z textu vyloučit znak pro počítání malých písmen:

=SUBSTITUTE(B3;LOWER(B6);"")

=SUBSTITUTE("Sun is shining ”;LOWER("S”);"")

Funkce LOWER je uvedena za účelem překladu počítaného znaku na malá písmena a poté funkce + SUBSTITUTE nahradí malá písmena prázdným řetězcem. Po tomto kroku bude výsledek vzorce vypadat takto: „Sun i hining“.

V dalším kroku vzorce je cílem vyloučit z definovaného textu / buňky znak pro velké počítání. To je vyřešeno vnořenou funkcí SUBSTITUTE :

=SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")

V rámci funkce SUBSTITUTE jsme již vysvětlili výsledek „Slunce i hining“ a uvedeme jej do níže uvedené funkce objasnit situaci. V první funkci SUBSTITUTE byla malá písmena nahrazena prázdným řetězcem a ve druhé funkci SUBSTITUTE byla velká písmena spočítána nahrazena prázdným řetězcem, což mělo za následek text bez počítání znaků „s“ a „S“: „un i hining“ .

=SUBSTITUTE("Sun i hining”;UPPER("S”);"")

Funkce LEN poté pouze spočítá počet znaků z upraveného textu:

=LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

=LEN("un i hining”)

V posledním kroku je vyhodnocen výsledek vzorce, text bez „s“ a „S“ se odečte od celkového počtu znaků v definované buňce:

=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

=LEN("Sun is shining”) - LEN("un i hining”)

Počítání konkrétních znaků v rozsahu

Kdykoli máme co do činění s rozsahy buněk a poli, je v kombinaci s dalšími funkcemi nutná funkce SUMPRODUCT. znak v rozsahu, pro počítání malých a velkých písmen se používá kombinace tří funkcí: SUMPRODUCT, LEN a SUBSTITUTE. Pro počítání bez rozlišování velkých a malých písmen je třeba přidat další vzorce: HORNÍ / NÍZKÉ.

Nejprve vysvětlíme počítání počtu výskytů znaku „S“ v definovaném rozsahu (verze bez rozlišování velkých a malých písmen). je téměř podobný vzorci vysvětlujícímu počítání znaků. Rozdíl vychází z funkce LEN vnořené do funkce SUMPRODUCT a v oblasti počítání, místo buňky je definován rozsah buněk:

=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;"")))

Pro počítání znaků bez rozlišování malých a velkých písmen v definovaném rozsahu můžeme použít podrobnou vysvětlenou funkci pro případ -necitlivé počítání znaků v konkrétní buňce v bodě výuky 3, se dvěma změnami: vnořením funkce LEN do funkce SUMPRODUCT a nahrazením buňky rozsahem buněk:

=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);"")))

Nezapomeňte, že při práci s oblastmi buněk budete muset použít funkci SUMPRODUCT. U znaků citlivých na velká a malá písmena počítání v ra Je nutná kombinace funkcí: SUMPRODUCT, LEN a SUBSTITUTE a pro počítání bez rozlišování velkých a malých písmen: funkce SUMPRODUCT, LEN, SUBSTITUTE a UPPER / LOWER.

Spočítat konkrétní kombinaci znaků v buňce nebo rozsahu

Existuje také možnost počítat konkrétní kombinaci znaků v definované buňce nebo rozsahu. V předchozích bodech tutoriálu jsme pokryli počítání jednotlivých znaků v buňce nebo rozsahu alternativami citlivými na malá a velká písmena. Vzorec pro počítání kombinace znaků je stejný, pouze jej musíme rozdělit počtem znaků v kombinaci znaků.

Podívejme se na příklad níže, který spočítá konkrétní kombinaci znaků v buňce (verze bez rozlišování malých a velkých písmen):

=(LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")))/LEN(B6)

Logika je stejná jako pro počítání jednoho znaku, pouze jsme museli rozdělit regulární vzorec na počet konkrétních znaků, které počítáme, snadno pomocí vzorce: LEN („in“). Bez rozdělení vzorce pomocí LEN („in“) by byl výsledek znásoben počtem znaků v kombinaci znaků (v našem příkladu s 2, protože „in ”Má dva znaky)

Pro počítání konkrétní kombinace znaků v buňce (verze s malými a velkými písmeny) bude vzorec vypadat takto:

=(LEN(B3)-LEN(SUBSTITUTE(B3;B6;"")))/LEN(B6)

Kombinace počítání znaků v rozsahu má stejnou logiku jako počítání jednoho znaku a v případě bude vypadat citlivý vzorec verze:

=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);""))))/LEN(B9)

Pravidelný vzorec vysvětlený v tu téma kurzů 4 je rozděleno počtem konkrétních znaků, které počítáme, pomocí funkce LEN.

Pokud chceme verzi bez rozlišení velkých a malých písmen, bude syntaxe vzorce vypadat takto:

=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;""))))/LEN(B9)

Stále potřebujete pomoc s formátováním aplikace Excel nebo máte další dotazy týkající se aplikace Excel? Spojte se zde s živým odborníkem na Excel a získejte pomoc 1 na 1. Vaše první relace je vždy zdarma.