Jak spočítat filtrované buňky s textem v Excelu?
V Excelu je počítání buněk s textem jednoduché. Ale když na to přijde počítání filtrovaných buněk s textem, věci jsou složité. Tento tutoriál představí tři způsoby spočítat buňky s textem z filtrovaného seznamu.
Počítejte filtrované buňky s textem pomocí pomocného sloupce
Počítejte filtrované buňky s textem zřetězením funkcí SUMPRODUCT, SUBTOTAL, INDIRECT, ROW & ISTEXT
Počítejte filtrované buňky s textem kombinací funkcí SUMPRODUCT, SUBTOTAL, OFFSET, MIN, ROW & ISTEXT
Pomocí funkce COUNTIF navíc s pomocí pomocného sloupce snadno spočítáme vyfiltrované buňky s textem. Postupujte prosím následovně.
1. Zkopírujte níže uvedený vzorec do buňky D2 a poté stiskněte klávesu Enter, abyste získali první výsledek.
=MEDZISOUČET (103, A2)
Poznámka: Pomocný sloupec se vzorcem SUBTOTAL slouží k testování, zda je řádek filtrován nebo ne. A 103 je zkratka pro COUNTA funkce v číslo_funkce argument.
2. Pak přetáhněte rukojeť výplně dolů na buňky, na které chcete tento vzorec také použít.
3. Zkopírujte níže uvedený vzorec do buňky F2, poté stiskněte tlačítko vstoupit klíč k získání konečného výsledku.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Můžeme vidět, že existují 4 buňky s textem ve filtrovaných datech.
Další metodou počítání filtrovaných buněk s textem je použití kombinace z SUMPRODUCT, SUBTOTAL, NEPŘÍMÝ, ŘÁDEK & ISTEXT Funkce. Postupujte prosím následovně.
Zkopírujte prosím níže uvedený vzorec do buňky E2, poté stiskněte tlačítko vstoupit klíč k získání výsledku.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Vysvětlení vzorce:
- ŘÁDEK(A2:A18) vrátí čísla příslušných řádků z rozsahu A2:A18.
- NEPŘÍMÉ("A"&ŘÁDEK(A2:A18)) vrátí platné odkazy na buňky z daného rozsahu.
- SUBTOTAL(103; NEPŘÍMÉ("A"&ROW(A2:A18))) testuje, zda je řádek filtrován nebo ne, a vrátí 1 pro viditelné buňky, 0 pro skryté a prázdné buňky.
- ISTEXT(A2:A18) zkontroluje, zda každá buňka v rozsahu A2:A18 obsahuje text, a vrátí True pro buňky s textem, False pro ostatní buňky. Dvojitý unární operátor (--) vynucuje hodnoty TRUE a FALSE na 1 a 0.
- SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18))) lze zobrazit jako SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Potom SUMPRODUCT vynásobí dvě pole dohromady a vrátí součet hodnot, což je 4.
Počítejte filtrované buňky s textem kombinací funkcí SUMPRODUCT, SUBTOTAL, OFFSET, MIN, ROW & ISTEXT
Třetí způsob počítání buněk s textem z filtrovaných dat je zřetězení ο SUMPRODUCT, SUBTOTAL, OFFSET, MIN, ŘÁDEK & ISTEXT Funkce. Postupujte prosím následovně.
Zkopírujte prosím níže uvedený vzorec do buňky E2, poté stiskněte tlačítko vstoupit klíč k získání výsledku.
=SOUČET (SUBTOTAL(103, OFFSET(A2:A18, ŘÁDEK(A2:A18)-2 -- MIN(ŘÁDEK(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)) )
Vysvětlení vzorce:
- OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) vrátí odkazy na jednotlivé buňky z rozsahu A2:A18.
- SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) zkontroluje, zda je řádek filtrován nebo ne, a vrátí 1 pro viditelné buňky, 0 pro skryté a prázdné buňky.
- ISTEXT(A2:A18) zkontroluje, zda každá buňka v rozsahu A2:A18 obsahuje text, a vrátí True pro buňky s textem, False pro ostatní buňky. Dvojitý unární operátor (--) vynucuje hodnoty TRUE a FALSE na 1 a 0.
- SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ŘÁDEK(A2:A18)-2 -- MIN(ŘÁDEK(A2:A18)-2),,1)), -- (ISTEXT(A2:A18))) lze zobrazit jako SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Potom SUMPRODUCT vynásobí dvě pole dohromady a vrátí součet hodnot, což je 4.
Další operace (články)
Kombinujte funkce COUNTIF a LEFT a počítání buněk začíná určitým znakem v Excelu
Chcete-li spočítat počet buněk, které začínají určitým znakem v aplikaci Excel, můžete to provést kombinací funkcí COUNTIF a LEFT. Ve skutečnosti toho lze dosáhnout různými druhy vzorců. Tento výukový program vás provede krok za krokem.
Jak spočítat buňky obsahující čísla nebo ne v Excelu?
Pokud existuje řada buněk, některé obsahují čísla a druhé obsahují text, jak můžete spočítat buňky obsahující čísla nebo ne rychle v aplikaci Excel?
Jak spočítat buňky, pokud bylo v Excelu splněno jedno z více kritérií?
Co když počítání buněk obsahuje jedno z více kritérií? Zde budu sdílet způsoby, jak počítat buňky, pokud obsahují X nebo Y nebo Z … atd. v Excelu.
Jak počítat buňky se specifickým textem a barvou výplně/písma v Excelu?
Víte, jak spočítat buňky s více podmínkami? Spočítejte například počet buněk, které obsahují konkrétní text i barvu písma/výplně. Tento článek vám ukáže řešení.
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%
- Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
- Sloučit buňky / řádky / sloupce a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... Zabraňte duplicitním buňkám; Porovnat rozsahy...
- Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
- Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
- Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
- Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
- Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
- Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
- Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
