V tomto článku se seznámíme s tím, jak počítat buňky, které se nerovná mnoha věcem v Excelu.
Jednoduše řečeno, zvažte scénář, ve kterém jsme povinni počítat data na základě konkrétních kritérií v poli. počítání buněk, které neobsahují hodnoty pro zachycení hodnoty buňky pomocí funkce Excel, vysvětlené zde na příkladu. K zachycení řetězců použijte znaky zástupných znaků a provádějte na nich funkce.
K tomu budeme používat následující funkce.
- Funkce SUMPRODUCT
- Funkce MATCH
- Funkce ISNA
- Funkce COUNTA SEO
- Funkce COUNTIFS
Nyní se o nich ve zkratce seznámíme. Máme různé metody, jak udělat totéž. Uvidíme to tedy jeden po druhém.
První metoda
Počítejte buňku, která neobsahuje hodnoty v rozsahu. Zde máme vzorec, jak to udělat. Pomocí funkce MATCH zjistíme, kde jsou hodnoty, které je třeba spočítat, a funkce ISNA zachytí chybu #NA. Poté SUMPRODUCT vrátí součet hodnot.
- Funkce SUMPRODUCT
- Funkce MATCH
- Funkce ISNA
Obecný vzorec:
| = SUMPRODUCT ( - (ISNA (MATCH (rozsah, hodnoty, 0))) |
Proměnné:
rozsah: pole buňky k počítání
hodnoty: pole hodnot, které se mají v rozsahu ignorovat.
Příklad
Pojďme tuto funkci pochopit na příkladu.
Tady máme v jednom sloupci ovoce a v druhém zeleninu. První chtíč má v seznamu nějakou zeleninu a my musíme počítat pouze s ovocem, které neuvažuje o zelenině.
Použijte vzorec:
| = SUMPRODUCT ( -- (ISNA (MATCH (ovoce, zelenina, 0)))) |
ovoce: pojmenovaný rozsah pro pole B2: B10
Zelenina: pojmenovaný rozsah použitý pro pole D3: D5
Vysvětlení:
- Funkce MATCH vrací pozici každého indexu, kde se hodnoty shodují v rozsahu, nebo vrací chybu #NA, pokud nebyla nalezena.
- Funkce ISNA zachytí chybu #NA a vrátí hodnotu TRUE pro chybu #NA a FALSE jinde.
- -- operátor převede všechny TRUE na 1 s a FALSE na 0 s.
- Funkce SUMPRODUCT vrací součet všech čísel v poli, což je ve skutečnosti počet buněk, kde hodnoty nejsou brány v úvahu.
Jak vidíte, vzorec vrací počet ovoce bez ohledu na hodnotu ze zeleniny.
Druhá metoda:
Budeme zvažovat jiné metody, protože různé typy problémů mají různá řešení. Použití funkce COUNTIFS v aplikaci Excel.
Obecný vzorec:
| = COUNTIFS (rozsah, "hodnota1", rozsah, "hodnota2", …) |
Proměnné:
rozsah: pole buňky k počítání
hodnota1: hodnota, která má být v rozsahu ignorována
hodnota2: hodnota, která má být v rozsahu ignorována
Příklad
Pojďme tuto funkci pochopit na příkladu.
Tady máme v jednom sloupci ovoce a v druhém zeleninu. V prvním sloupci je v seznamu nějaká zelenina a musíme počítat pouze ovoce, které zeleninu neuvažuje.
Použijte vzorec:
| = COUNTIFS (ovoce, "" & D3, ovoce, "" & D4, ovoce, "" & D5) |
ovoce: pojmenovaný rozsah pro pole B2: B10
D3, D4 a D5 jsou odkazy na buňky použité ve vzorci
Vysvětlení:
- Zde je rozsah zkontrolován s jednotlivými hodnotami a vrací počet buněk.
- operátor je nerovná se
- & operátor, zřetězte hodnoty s referenční hodnotou buňky.
Použijte vzorec:
| = COUNTIFS (ovoce, "" & D3, ovoce, "" & D4, ovoce, "" & D5) |
ovoce: pojmenovaný rozsah pro pole B2: B10
D3, D4 a D5 jsou odkazy na buňky použité ve vzorci
Vysvětlení:
- Zde je rozsah zkontrolován s jednotlivými hodnotami a vrací počet buněk.
- operátor je nerovná se
- & spojte operátor s hodnotou použitou odkazem na buňku.
Jak vidíte, vzorec vrací počet ovoce bez ohledu na hodnotu zeleniny.
Třetí metoda
Budeme zvažovat jiné metody, protože různé typy problémů mají různá řešení. Použití funkce COUNTA v Excelu. Zde logika vypočítá počet buněk s hodnotami a poté je odečte od počtu všech hodnot v rozsahu.
Obecný vzorec:
| = COUNTA (rozsah) - SUMPRODUCT (COUNTIF (rozsah, hodnoty) |
Proměnné:
rozsah: pole buňky k počítání
hodnoty: pole hodnot, které se mají v rozsahu ignorovat.
Příklad
Pojďme tuto funkci pochopit na příkladu.
Tady máme ovoce v jednom sloupci a zeleninu v jiném. První chtíč má v seznamu nějakou zeleninu a my musíme počítat pouze s ovocem, které neuvažuje o zelenině.
Použijte vzorec:
| = COUNTA (ovoce) - SUMPRODUKT (COUNTIF (ovoce, zelenina)) |
ovoce: pojmenovaný rozsah pro pole B2: B10
Zelenina: pojmenovaný rozsah použitý pro pole D3: D5
Vysvětlení:
- Funkce COUNTIF počítá buňky v ovoci se zeleninou.
- SUMPRODUCT získá součet s ohledem na všechny hodnoty.
- Funkce COUNTA vypočítá počet neprázdných buněk v rozsahu.
- Rozdíl bude mít počet buněk (ovoce), které nemají hodnoty (zelenina).
Jak vidíte, vzorec vrací počet ovoce bez ohledu na hodnotu zeleniny.
Doufám, že jste pochopili, jak počítat buňky, které se nerovná mnoha věcem v aplikaci Excel. Prozkoumejte další články o funkcích počtu Excel zde. Neváhejte uvést svůj dotaz nebo zpětnou vazbu k výše uvedenému článku.
COUNTIFS s rozsahem dynamických kritérií: Počet buněk s dynamickým rozsahem kritérií pomocí funkce COUNTIFS.
COUNTIFS odpovídá dvěma kritériím: Počet buněk s více kritérii se pohybuje pomocí funkce COUNTIFS.
COUNTIFS S NEBO Pro více kritérií: Počet buněk pomocí logických funkcí s funkcí COUNTIFS.
Funkce COUNTIFS v aplikaci Excel: použití funkce COUNTIFS k počítání buněk s více kritérii
Jak používat Countif ve VBA v Microsoft Excelu: K počítání buněk použijte funkci COUNTIF ve VBA.
Jak používat zástupné znaky v aplikaci Excel: Chytněte fráze a klíčová slova pomocí vysvětlených zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity
Upravte rozevírací seznam
Pokud s podmíněným formátováním
Pokud se zástupnými znaky
Vlookup podle data