Tento výukový program ukazuje, jak používatVýbornýel Funkce COUNTIF a COUNTIFStions v Excelu počítat data, která splňují určitá kritéria.
Přehled funkcí COUNTIF
Pomocí funkce COUNTIF v aplikaci Excel můžete počítat buňky obsahující konkrétní hodnotu, počítat buňky větší nebo rovné hodnotě atd.
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a argumenty funkce COUNTIF:
= COUNTIF (rozsah, kritéria)
rozsah - Rozsah buněk, které se mají počítat.
kritéria - Kritéria, která určují, které buňky by měly být započítány.
Co je funkce COUNTIF?
Funkce COUNTIF je jednou ze starších funkcí používaných v tabulkách. Jednoduše řečeno, je skvělé skenovat rozsah a říct vám, kolik buněk splňuje tuto podmínku. Podíváme se, jak funkce funguje s textem, čísly a daty; stejně jako některé další situace, které mohou nastat.
Základní příklad
Začněme tím, že se podíváme na tento seznam náhodných položek. Máme nějaká čísla, prázdné buňky a několik textových řetězců.
Pokud jste chtěli vědět, kolik položek přesně odpovídá kritériím, můžete jako druhý argument určit, co chcete hledat. Příklad tohoto vzorce může vypadat
= COUNTIF (A2: A9, „Apple“)
Tento vzorec by vrátil číslo 3, protože v našem rozsahu jsou 3 buňky, které splňují tato kritéria. Alternativně můžeme místo pevného kódování hodnoty použít odkaz na buňku. Pokud bychom do buňky G2 napsali „Apple“, mohli bychom vzorec změnit na
= COUNTIF (A2: A9, G2)
Při práci s číslem je důležité rozlišovat mezi čísly a čísly, která byla uložena jako text. Při psaní vzorců obecně nedáváte uvozovky kolem čísel. Chcete -li tedy napsat vzorec, který kontroluje číslo 5, napíšete
= COUNTIF (A2: A9, 5)
Nakonec bychom také mohli zkontrolovat prázdné buňky pomocí řetězce nulové délky. Tento vzorec bychom napsali jako
= COUNTIF (A2: A9, "")
Poznámka: Tento vzorec bude počítat jak buňky, které jsou skutečně prázdné, tak buňky, které jsou prázdné jako výsledek vzorce, jako funkce IF.
Dílčí zápasy
Funkce COUNTIF podporuje v kritériích použití zástupných znaků „*“ nebo „?“. Podívejme se na tento seznam chutných pekařských výrobků:
Abychom našli všechny položky začínající na Apple, mohli bychom napsat „Apple*“. Abychom dostali odpověď 3, náš vzorec v D2 je
= COUNTIF (A2: A5, "Apple*")
Poznámka: Funkce COUNTIF nerozlišuje velká a malá písmena, takže můžete také napsat „apple*“, pokud chcete.
Zpět k našemu pečivu můžeme také chtít zjistit, kolik koláčů máme v seznamu. Můžeme to zjistit tak, že umístíme zástupný znak na začátek hledaného výrazu a napíšeme
= COUNTIF (A2: A5, "*koláč")
Tento vzorec dává výsledek 2.
Můžeme také použít zástupné znaky ke kontrole všech buněk s textem. Vraťme se k původnímu seznamu dat.
Chcete -li spočítat počet buněk, které mají alespoň nějaký text, tedy nepočítat čísla nebo prázdnou buňku, můžeme psát
= COUNTIF (A2: A9, "*")
Vidíte, že náš vzorec správně vrací výsledek 4.
Operátory porovnání v COUNTIF
Při psaní kritérií jsme zatím naznačovali, že náš operátor porovnávání je „=“. Ve skutečnosti jsme mohli napsat toto:
= COUNTIF (A2: A9, "= Apple")
Je to však další znak na zapisování, takže je obvykle vynechán. To však znamená, že můžete použít jiné operátory, například větší než, menší než nebo nerovný. Podívejme se na tento seznam zaznamenaných věků:
Pokud bychom chtěli vědět, kolik dětí je nejméně 5 let, můžeme napsat srovnání „větší nebo rovno“ takto:
= COUNTIF (A2: A8, "> = 5")
Poznámka: Operátor porovnání je vždy uveden jako textový řetězec, a proto musí být v uvozovkách.
Podobně můžete také zkontrolovat položky, které jsou menší než daná hodnota. Pokud potřebujeme zjistit, kolik je méně než 8, můžeme napsat
= COUNTIF (A2: A8, "<8")
To nám dává požadovaný výsledek 5. Nyní si představme, že všechny 6leté děti vyrážejí na výlet. Kolik dětí zůstane? Můžeme to zjistit pomocí srovnání „nerovná se“ takto:
= COUNTIF (A2: A8, "6")
Nyní můžeme rychle vidět, že máme 6 dětí, kterým není 6 let.
V těchto srovnávacích příkladech jsme zatím tvrdě kódovali hodnoty, které jsme chtěli. Můžete také použít odkaz na buňku. Jde o to, že potřebujete zřetězit operátor porovnání s odkazem na buňku. Řekněme, že do buňky C2 vložíme číslo 7 a chceme, aby náš vzorec v D2 ukázal, kolik dětí je mladších 7 let.
Náš vzorec v D2 musí vypadat takto:
= COUNTIF (A2: A8, "<" & C2)
Poznámka: Při psaní těchto vzorců věnujte zvláštní pozornost tomu, zda potřebujete položku vložit do uvozovek nebo mimo ni. Operátory jsou vždy uvnitř uvozovek, odkazy na buňky jsou vždy mimo uvozovky. Čísla jsou venku, pokud provádíte přesnou shodu, ale uvnitř, pokud provádíte srovnávací operátor.
Práce s daty
Viděli jsme, jak můžete jako kritérium zadat text nebo číslo, ale co když potřebujeme pracovat s daty? Zde je rychlý ukázkový seznam, se kterým můžeme pracovat:
Abychom spočítali, kolik dat je po 4. květnu, musíme být opatrní. Počítače ukládají data jako čísla, takže se musíme ujistit, že počítač používá správné číslo. Pokud bychom napsali tento vzorec, dostali bychom správný výsledek?
= COUNTIF (A2: A9, "
Odpověď je „možná“. Protože jsme z našich kritérií vynechali rok, počítač bude předpokládat, že máme na mysli aktuální rok. Pokud jsou všechna data, se kterými pracujeme, aktuální rok, pak dostaneme správnou odpověď. Pokud však budou nějaká data v budoucnosti, dostali bychom špatnou odpověď. Jakmile začne další rok, vrátí tento vzorec jiný výsledek. Proto by se této syntaxi mělo pravděpodobně vyhnout.
Protože může být obtížné správně zapsat data do vzorce, je nejlepší napsat datum, které chcete v buňce použít, a poté můžete použít odkaz na buňku ve vzorci COUNTIF. Zapište tedy datum 7. května 2020 do buňky C2 a poté můžeme vzorec vložit do C4.
Vzorec v C4 je
= COUNTIF (A2: A9, "<" & C2)
Nyní víme, že výsledek 7 je správný, a odpověď se neočekávaně nezmění, pokud tuto tabulku otevřeme někdy v budoucnosti.
Než opustíme tuto sekci, je běžné při práci s daty používat funkci DNES. Můžeme to použít stejně jako odkaz na buňku. Například bychom mohli změnit předchozí vzorec takto:
= COUNTIF (A2: A9, "<" A DNES ())
Náš vzorec se bude stále aktualizovat, jak postupuje v reálném čase, a budeme mít počet položek, které jsou menší než dnes.
Více kritérií a COUNTIFS
Původní funkce COUNTIF byla vylepšena v roce 2007, kdy vyšel COUNTIFS. Syntaxe mezi těmito dvěma je velmi podobná, přičemž druhá vám umožňuje zadat další rozsahy a kritéria. COUNTIFS můžete snadno použít v jakékoli situaci, kdy COUNTIF existuje. Je dobré vědět, že obě funkce existují.
Podívejme se na tuto tabulku dat:
Chcete -li zjistit, kolik lidí je v platových úrovních 1 až 2, můžete napsat souhrn funkcí COUNTIF takto:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Tento vzorec bude fungovat, protože najdete vše, co je nad 1, ale poté odečtete počet záznamů, které jsou mimo váš mezní bod. Alternativně můžete použít COUNTIFS takto:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Čtení druhého je intuitivnější, takže možná budete chtít použít tuto trasu. COUNTIFS je také výkonnější, když potřebujete vzít v úvahu více sloupců. Řekněme, že chceme vědět, kolik lidí je ve správě a v platové úrovni 1. To nemůžete udělat jen s COUNTIF; budete muset napsat
= COUNTIFS (A2: A7, „Správa“, B2: B7, 1)
Tento vzorec by vám poskytl správný výsledek 2. Než opustíme tuto část, uvažujme logiku typu Or. Co kdybychom chtěli zjistit, kolik lidí je ve vedení nebo? Budete muset přidat nějaké COUNTIFS dohromady, ale existují dva způsoby, jak to udělat. Nejjednodušší způsob je napsat to takto:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "správa")
Můžete také využít pole a napsat tento vzorec pole:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Poznámka: Array formulas must be guaranteed using `Ctrl+Shift+Enter` not just` Enter`.
Jak bude tento vzorec fungovat, je vidět, že jste jako vstup zadali pole. Výsledek tedy vypočítá pro dvě různé funkce COUNTIF a uloží je do pole. Funkce SUMA pak sečte všechny výsledky v našem poli dohromady a vytvoří jeden výstup. Náš vzorec bude tedy vyhodnocen takto:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Počítejte jedinečné hodnoty
Nyní, když jsme viděli, jak používat pole s funkcí COUNTIF, můžeme tento krok posunout dále, aby nám pomohl spočítat, kolik jedinečných hodnot je v rozsahu. Nejprve se podívejme znovu na náš seznam oddělení.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Vidíme, že existuje 6 dat v hodnotě dat, ale existují pouze 3 různé položky. Abychom matematiku zvládli, potřebovali bychom, aby každá položka měla hodnotu 1/N, kde N je počet opakování položky. Pokud by například každý HR měl hodnotu jen 1/2, pak když je sečtete, získáte počet 1 za 1 jedinečnou hodnotu.
Zpět k našemu COUNTIF, který je navržen tak, aby zjistil, kolikrát se položka objeví v rozsahu. V D2 napíšeme vzorec pole
= SUM (1/COUNTIF (A2: A7, A2: A7))
Jak bude tento vzorec fungovat, je u každé buňky v rozsahu A2: A7 zkontrolováno, kolikrát se objeví. S naší ukázkou to vytvoří řadu
{2, 2, 3, 3, 3, 1}
Potom všechna tato čísla převedeme na zlomky tak, že provedeme nějaké dělení. Nyní naše pole vypadá
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Když je všechny sečteme, dostaneme požadovaný výsledek 3.
Countif se dvěma nebo více podmínkami - funkce Countifs
Doposud jsme pracovali pouze s funkcí COUNTIF. Funkce COUNTIF může současně zpracovávat pouze jedno kritérium. K COUNTIF s více kritérii musíte použít funkci COUNTIFS. COUNTIFS se chová přesně jako COUNTIF. Stačí přidat další kritéria. Podívejme se na níže uvedený příklad.
= COUNTIFS (B2: B7, "= 130")
COUNTIF a COUNTIFS v Tabulkách Google
Funkce COUNTIF a COUNTIFS funguje v Tabulkách Google úplně stejně jako v Excelu:
