Výukový program vysvětluje, jak počítat znaky v aplikaci Excel. Naučíte se vzorce, jak získat celkový počet znaků v rozsahu, a počítat pouze konkrétní znaky v buňce nebo v několika buňkách.
Náš předchozí tutoriál představil funkci Excel LEN, která umožňuje počítat celkový počet znaků v buňce.
Vzorec LEN je užitečný sám o sobě, ale ve spojení s dalšími funkcemi, jako jsou SUM, SUMPRODUCT a SUBSTITUTE, zvládne mnohem složitější úkoly. Dále v tomto tutoriálu se blíže podíváme na několik základních a pokročilých vzorců pro počítání znaků v aplikaci Excel.
- Spočítat celkový počet znaků v rozsahu
- Spočítat konkrétní znaky v buňce
- Spočítat konkrétní znak v rozsahu
Jak spočítat všechny znaky v rozsahu
Když spočítá celkový počet znaků v několika buňkách, okamžité řešení, které přijde na mysl, je získat počet znaků pro každou buňku a poté tato čísla sečíst:
=LEN(A2)+LEN(A3)+LEN(A4)
Nebo
=SUM(LEN(A2),LEN(A3),LEN(A4))
Výše uvedené vzorce mohou pro malý rozsah fungovat dobře. Abychom spočítali celkový počet znaků ve větším rozsahu, měli bychom přijít s něčím kompaktnějším, např. Funkcí SUMPRODUCT, která vynásobí pole a vrátí součet produktů.
Zde je obecný Excel vzorec pro počítání znaků v rozsahu:
A váš vzorec v reálném životě může vypadat podobně jako tento:
=SUMPRODUCT(LEN(A1:A7))
Dalším způsobem, jak počítat znaky v rozsahu, je použití funkce LEN v kombinaci s SUM:
=SUM(LEN(A1:A7))
Na rozdíl od SUMPRODUCT funkce SUM ve výchozím nastavení nepočítá pole a musíte stisknout Ctrl + Shift + Stisknutím klávesy Enter jej proměníte na maticový vzorec.
Jak ukazuje následující snímek obrazovky, vzorec SUM vrací stejný celkový počet znaků:
Jak funguje tento vzorec pro počítání znaků rozsahu
Toto je jeden z nejpřímějších vzorců pro počítání znaků v aplikaci Excel. Funkce LEN vypočítá délku řetězce pro každou buňku ve specifikovaném rozsahu a vrátí je jako pole čísel. A potom SUMPRODUCT nebo SUM sčítá tato čísla a vrací celkový počet znaků.
Ve výše uvedeném příkladu je sečteno pole 7 čísel, která představují délky řetězců v buňkách A1 až A7:
Jak počítat konkrétní znaky v buňce
Někdy, místo počítání všech znaků v buňce, budete možná muset počítat pouze výskyty konkrétního písmene, čísla nebo zvláštního symbolu.
spočítat, kolikrát se daný znak objeví v buňce, použijte funkci LEN společně s SUBSTITUTE:
Abyste lépe porozuměli vzorci, zvažte následující příklad.
Předpokládejme, že budete udržovat databázi doručovaných položek, kde každý typ položky má svůj vlastní jedinečný identifikátor. A každá buňka obsahuje několik položek oddělených čárkou, mezerou nebo jakýmkoli jiným oddělovačem. Úkolem je spočítat, kolikrát se daný jedinečný identifikátor objeví v každé buňce.
Za předpokladu, že seznam doručených položek je ve sloupci B (počínaje B2), a počítáme počet „A „výskyty, vzorec je následující:
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
Jak funguje tento vzorec pro výpočet počtu znaků v programu Excel
Abyste pochopili logiku vzorce, rozdělte jej na menší části:
Výsledkem bude odebrání počtu „znaky, což se rovná celkovému počtu výskytů tohoto znaku v buňce.
Místo toho, abyste ve vzorci určili znak, který chcete spočítat, můžete jej zadat do některé buňky a poté na tuto buňku odkazovat. ve vzorci. Tímto způsobem budou vaši uživatelé schopni počítat výskyty jakýchkoli dalších znaků, které zadají do této buňky, aniž by narušili váš vzorec:
Vzorec Excel bez rozlišení velkých a malých písmen pro počítání konkrétních znaků v buňce
Pokud potřebujete počet znaků bez rozlišení velkých a malých písmen, vložte do funkce SUBSTITUTE funkci HORNÍ, abyste před zadaným znakem převedli velká písmena spuštění střídání. A nezapomeňte do vzorce zadat velká písmena.
Chcete-li například spočítat položky „A“ a „a“ v buňce B2, použijte tento vzorec:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))
Dalším způsobem je použití vnořených náhradních funkcí:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE (B2,"A",""),"a","")
Jak můžete vidět na následujícím snímku obrazovky, oba vzorce bezchybně počítají výskyt malých a velkých písmen zadaného znaku:
V některých případech , možná budete muset v tabulce spočítat mnoho různých znaků, ale vzorec možná nebudete chtít pokaždé upravovat. V takovém případě vnořte jednu funkci Substitute do jiné, zadejte znak, který chcete spočítat do některé buňky (v tomto příkladu D1), a převeďte hodnotu této buňky na velká a malá pomocí funkcí UPPER a LOWER:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), ""), LOWER($D$1),""))
Alternativně můžete převést zdrojovou buňku i buňku obsahující znak na velká nebo malá písmena. Například:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),""))
Výhodou tohoto přístupu je, že bez ohledu na to, zda je v odkazované buňce zadán znak velkého nebo malého písmene, váš vzorec pro výpočet počtu znaků nerozlišuje malá a velká písmena vrátí správný počet:
Spočítat výskyty určitého textu nebo podřetězce v buňce
Chcete-li počítat kolikrát se v dané buňce objeví konkrétní kombinace znaků (tj. určitý text nebo podřetězec), např. „A2“ nebo „SS“, pak vydělte počet znaků vrácených výše uvedenými vzorci délkou podřetězce .
Vzorec rozlišující velká a malá písmena:
=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,"")))/LEN($C$1)
Vzorec nerozlišující velká a malá písmena:
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)
Kde B2 je buňka obsahující celý textový řetězec a C1 je text (podřetězec), který chcete počítat.
Podrobné vysvětlení vzorce najdete v části Jak počítat konkrétní text / slova v buňce.
Jak počítat konkrétní znak ( s) v rozsahu
Nyní, když znáte vzorec aplikace Excel pro počítání znaků v buňce, můžete jej dále vylepšit a zjistit, kolikrát se určitý znak v rozsahu objeví. Z tohoto důvodu vezmeme vzorec Excel LEN k výpočtu konkrétního znaku v buňce popsané v předchozím příkladu a vložíme jej do funkce SUMPRODUCT, která dokáže zpracovat pole:
V tomto příkladu má vzorec následující tvar:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))
A tady je další vzorec pro počítání znaků v rozsahu aplikace Excel:
=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))
Ve srovnání s prvním vzorcem je nejzřetelnějším rozdílem použití SUM namísto SUMPRODUCT. Další rozdíl spočívá v tom, že vyžaduje stisknutí Ctrl + Shift + Enter, protože na rozdíl od SUMPRODUCT, který je navržen pro zpracování polí, dokáže SUM zpracovat pole pouze při použití ve vzorci pole.
Pokud si nepřejete pevný kód znak ve vzorci, můžete jej samozřejmě napsat do nějaké buňky, řekněme D1, a odkazovat na tuto buňku ve vzorci pro výpočet počtu znaků:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,"")))
Jak tento vzorec pro počítání znaků funguje
Jak si možná pamatujete, funkce SUBSTITUTE slouží k nahrazení všech výskytů zadaného znaku (v tomto příkladu „A“) prázdným textovým řetězcem ( „“).
Potom dodáme textový řetězec vrácený funkcí SUBSTITUTE do funkce Excel LEN, aby vypočítal délku řetězce bez znaků A. A potom odečteme tento počet znaků od celkové délky textového řetězce. Výsledkem těchto výpočtů je pole počtu znaků s jedním počtem znaků na buňku.
Nakonec SUMPRODUCT sečte čísla v poli a vrátí celkový počet zadaných znaků v rozsahu.
Vzorec nerozlišující velká a malá písmena pro počítání konkrétních znaků v rozsahu
Již víte, že SUBSTITUTE je funkce citlivá na velká a malá písmena, díky čemuž je v našem vzorci aplikace Excel pro počet znaků také malá a velká písmena.
Chcete-li, aby vzorec ignoroval velká a malá písmena, postupujte podle příkladů uvedených v předchozím příkladu: Vzorec bez ohledu na velikost písmen, který spočítá konkrétní znaky v buňce.
Zejména můžete použít jeden z následující vzorce pro počítání konkrétních znaků v rozsahu ignorujícím velká a malá písmena:
Následující snímek obrazovky ukazuje poslední vzorec v akci:
Takto můžete počítat znaky v aplikaci Excel pomocí LEN funkce.Pokud chcete vědět, jak počítat slova, nikoli jednotlivé znaky, najdete v našem příštím článku několik užitečných vzorců, prosím, zůstaňte naladěni!
Mezitím si můžete stáhnout ukázkové sešity s počtem znaků vzorec popsaný v tomto kurzu a podívejte se na seznam souvisejících zdrojů na konci stránky. Děkuji za přečtení a doufám, že se brzy uvidíme!
- Funkce Excel LEN – vzorce pro výpočet délky buňky, tj. Celkového počtu znaků v buňce, s mezerami nebo bez nich.
- Jak počítat slova v aplikaci Excel – vzorce citlivé na velká a malá písmena pro počítání celkových nebo konkrétních slov v buňce nebo oblasti buněk.
- Funkce COUNT a COUNTA – počet buněk s čísly a neprázdnými buňkami.
- Funkce COUNTIF – vzorce pro podmíněné počítání buněk v aplikaci Excel.
- Funkce COUNTIFS – počet buněk s více než jedním kritériem.
