Pomocí funkce COUNTIFS v aplikaci Excel můžete spočítat počet datových záznamů ve vybraném rozsahu. COUNTIFS hledá konkrétní kritéria ve dvou nebo více polích v každém záznamu a počítá tento záznam pouze v případě, že najde shodu pro každé zadané kritérium. Informace v tomto článku platí pro Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel Online a Excel pro Mac.
Výukový program funkce COUNTIFS krok za krokem
COUNTIFS rozšiřuje užitečnost funkce COUNTIF tím, že vám umožňuje zadat více rozsahů a více kritérií než jen jedno jako ve funkci COUNTIF. Funkce COUNTIFS pracuje s řádky dat zvaných záznamy. V záznamu souvisí data v každé buňce nebo poli v řádku, například název společnosti, adresa a telefonní číslo. Zjistěte, jak funkce COUNTIF funguje, v tutoriálu COUNTIF krok za krokem, kde se pomocí jediného kritéria určí, kteří prodejní agenti prodali více než 250 objednávek za rok. V tomto kurzu je druhá podmínka nastavena pomocí funkce COUNTIFS. Toto druhé kritérium zužuje prodejní agenty v oblasti prodeje na východě, kteří v uplynulém roce dosáhli více než 250 prodejů. Funkce COUNTIFS to může udělat, protože umožňuje zadat více rozsahů (rozsah_kritérií) a více podmínek (kritérií) jako argumenty. Postupujte podle pokynů v tomto kurzu a vytvořte a použijte funkci COUNTIFS na obrázku výše.
Zadejte data výukového programu
Prvním krokem k použití funkce COUNTIFS v aplikaci Excel je nastavení dat. V tomto kurzu zadejte data viděná na obrázku výše do buněk D1 až F11 listu aplikace Excel. Na řádku 12 pod daty se přidá funkce COUNTIFS a dvě vyhledávací kritéria. Toto jsou kritéria:
- Obchodní zástupci z východního prodejního regionu
- Za rok bylo zadáno více než 250 objednávek
Chcete-li nastavit tato kritéria, zadejte Východní do buňky D12, a > 250 do buňky E12.
Syntaxe funkce COUNTIFS
V aplikaci Excel syntaxe funkce odkazuje na rozložení funkce a zahrnuje název funkce, závorky a argumenty. Syntaxe funkce COUNTIFS je: = COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2, …)
type = „code“> Ve funkci lze zadat až 127 párů Criteria_range a Criteria. Argumenty funkce sdělují COUNTIFS, jaká kritéria se snažíte splnit a jaký rozsah dat hledat, aby tato kritéria našel. Všechny argumenty v této funkci jsou povinné.
Rozsah_kritérií: Skupina buněk, jejíž funkcí je hledání shody s odpovídajícím argumentem Kritéria.
Kritéria: Hodnota, kterou se pokoušíte porovnat v datovém záznamu. Pro tento argument lze zadat skutečná data nebo odkaz na buňku k datům. Použití funkce COUNTIFS k určení více rozsahů a zúžení několika kritérií, které záznamy chcete počítat mnohem více než funkce COUNTIF.
Spusťte funkci COUNTIFS
Přestože je možné do buňky v listu zadat funkci COUNTIFS a její argumenty, může být jednodušší použít funkci dialogového okna funkce.
Výukové kroky
- Vyberte buňku F12 aby se stala aktivní buňkou. Zde se zadá funkce COUNTIFS.
- Vybrat Vzorce.
- Vybrat Další funkce > Statistický otevřete seznam statistických funkcí.
- Vybrat COUNTIFS otevřete dialogové okno Funkční argumenty.
Data zadaná do prázdných řádků v dialogovém okně tvoří argumenty funkce COUNTIFS.
Zadejte argument Criteria_range1
V tomto kurzu jsou dvě kritéria v každém datovém záznamu, která budou porovnána, následující:
- Obchodní zástupci z východního prodejního regionu
- Obchodní zástupci, kteří mají za rok více než 250 prodejních objednávek
Argument Criteria_range1 označuje rozsah buněk pro funkci COUNTIFS, které se mají hledat při pokusu o shodu s prvním kritériem, kterým je východní prodejní oblast.
Výukové kroky
- V dialogovém okně Argumenty funkcí umístěte kurzor do textového pole Criteria_range1.
- Zvýrazněte buňky D3 až D9 v listu zadejte tyto odkazy na buňky jako rozsah, který má funkce prohledávat.
Zadejte argument Criteria1
V tomto kurzu bude prvním kritériem, které bude porovnáno, to, zda se data v rozsahu D3: D9 rovnají východu. Lze zadat skutečná data, například slovo východ, do dialogového okna je obvykle nejlepší zadat do dialogového okna odkaz na buňku s daty.
Výukové kroky
- Umístěte kurzor do textového pole Kritéria 1 v dialogovém okně Argumenty funkcí.
- Vyberte buňku D12 zadejte odkaz na tuto buňku do dialogového okna.
- Hledaný výraz Východní je přidán do buňky D12.
Pokud je jako argument Kritéria zadán odkaz na buňku, například D12, funkce COUNTIFS hledá shody s libovolnými daty, která byla do této buňky v listu zadána. Protože se pro kritéria používá odkaz na buňku, poté, co se spočítají agenti z východní oblasti, kteří překonali 250 prodejů, je snadné najít stejná data pro jinou prodejní oblast změnou dat v buňce D12 (například změnit východ na sever nebo západ). Funkce se automaticky aktualizuje a zobrazuje nový výsledek.
Zadejte argument Criteria_range2
Argument Criteria_range2 označuje rozsah buněk pro funkci COUNTIFS, které se mají hledat při pokusu o shodu s druhým kritériem. V tomto tutoriálu jsou to prodejní agenti, kteří letos prodali více než 250 objednávek.
Výukové kroky
- V dialogovém okně Argumenty funkcí umístěte kurzor do textového pole Criteria_range2.
- Zvýrazněte buňky E3 až E9 v listu zadejte tyto odkazy na buňky jako druhý rozsah, který má funkce prohledávat.
Zadejte argumenty Criteria2
V tomto kurzu je druhým kritériem, které bude porovnáno, pokud jsou data v rozsahu E3: E9 větší než 250 prodejních objednávek. Stejně jako u argumentu Criteria1 zadejte do dialogového okna odkaz na buňku na umístění Criteria2 místo samotných dat.
Výukové kroky
- Umístěte kurzor do Kritéria 2 textové pole v dialogovém okně Argumenty funkcí.
- Vyberte buňku E12 pro zadání odkazu na tuto buňku. Funkce prohledá rozsah vybraný v předchozím kroku pro data, která odpovídají těmto kritériím.
- Vybrat OK k dokončení funkce COUNTIFS a zavření dialogového okna.
- Odpověď dvou (2) se objeví v buňce F12, buňce, kde jste zadali funkci.
Dokončete výukový program
Funkce COUNTIFS prohledávala pouze obchodní zástupce pracující v regionu východ a sečetla počet obchodních zástupců, kteří uskutečnili více než 250 prodejů. V tomto příkladu toho byli dva opakování.
Jak funguje COUNTIFS logika
Pořadí logiky, které probíhá u funkce COUNTIFS k provedení tohoto výpočtu, je následující.
- Pouze dva agenti, Ralph a Sam, pracují v oblasti prodeje na východě a za rok provedli více než 250 objednávek. Funkce počítá pouze tyto dva záznamy.
- Přestože Martha pracuje na východě, měla méně než 250 objednávek. Její rekord se nepočítá.
- Joe a Tom měli za rok více než 250 objednávek, ale žádná nepracuje v oblasti prodeje na východě. Jejich záznamy se nepočítají.
Když vyberete buňku F12, zobrazí se kompletní funkce v řádku vzorců nad listem. Vypadá to takto: = COUNTIFS (D3: D9, D12, E3: E9, E12)
type = „code“> Změňte buď oblast hledání na sever, jih nebo západ, nebo změňte #Orders na jiné číslo. Sledujte, jak se výsledky mění v závislosti na kritériích vyhledávání zadaných do těchto buněk.