Počítání tvoří nedílnou součást analýzy dat, ať počítáte zaměstnance některého oddělení ve vaší organizaci nebo jednotky prodané za jednotlivá čtvrtletí. Excel nabízí několik technik, které můžete použít ke spočítání buněk, řádků nebo sloupců dat. Tento článek vám pomůže vybrat co nejlépe, a proto obsahuje komplexní souhrn metod, sešit ke stažení s interaktivními příklady a odkazy na související témata pro další pochopení.
Poznámka: Počítání by se nemělo zaměňovat se sčítáním. Další informace o sčítání hodnot v buňkách, sloupcích nebo řádcích najdete v tématu Souhrn způsobů, jak přidat a spočítat excelová data.
Stažení příkladů
Můžete si stáhnout ukázkový sešit, který obsahuje příklady pro doplnění informací v tomto článku. Většina oddílů v tomto článku bude odkazovat na příslušný list v ukázkovém sešitu, který obsahuje příklady a další informace.
V tomto článku
-
Počítání hodnot na základě jedné nebo více podmínek
-
Počítání buněk v oblasti na základě jedné podmínky pomocí funkce COUNTIF
-
Počítání buněk ve sloupci na základě jedné nebo více podmínek pomocí funkce DPOČET
-
Počítání buněk v oblasti na základě více podmínek pomocí funkce COUNTIFS
-
Určit počet výskytů podle kritéria pomocí funkcí POČET a KDYŽ
-
Určit počet výskytů více textových nebo číselných hodnot pomocí funkcí SUMA a KDYŽ
Jednoduché počítání
Počet hodnot v oblasti nebo tabulce můžete spočítat pomocí jednoduchého vzorce, funkce listu nebo kliknutím na tlačítko.
Excel může zobrazit počet vybraných buněk na Stavový řádek Excelu. Podívejte se na video, které nabízí rychlou ukázku práce se stavovým řádkem. Další informace najdete také v části Zobrazení výpočtů a počtů na stavovém řádku. Hodnoty na stavovém řádku můžete využít, když chcete získat rychlý přehled o svých datech, ale nemáte čas zadávat vzorce.
Video: Počítání buněk pomocí stavového řádku Excelu
V následujícím videu se dozvíte, jak zobrazit počet na stavovém řádku.
Použití funkce Automatické shrnutí
Pomocí funkce Automatické shrnutí vyberte oblast buněk, která obsahuje alespoň jednu číselnou hodnotu. Potom na kartě Vzorce klikněte na Automatické shrnutí > Počet čísel.
Excel vrátí počet číselných hodnot v oblasti do buňky vedle této oblasti, kterou jste vybrali. Obecně platí, že v případě vodorovné oblasti buněk se tento výsledek zobrazí v buňce vpravo a v případě svislé oblasti v buňce pod ní.
Přidání řádku souhrnu
Do excelových dat můžete přidat řádek mezisoučtu. Klikněte na libovolné místo v datech a potom klikněte na Data > Souhrn.
Poznámka: Možnost Souhrn bude fungovat jenom u běžných excelových dat, nikoli u excelových tabulek, kontingenčních tabulek nebo kontingenčních grafů.
Přečtěte si také následující články:
Počítání buněk v seznamu nebo ve sloupci tabulky Excelu pomocí funkce SUBTOTAL
Pomocí funkce SUBTOTAL můžete spočítat počet hodnot v excelové tabulce nebo oblasti buněk. Pokud tabulka nebo oblast obsahuje skryté buňky, můžete tyto skryté buňky zahrnout nebo vyloučit pomocí funkce SUBTOTAL, což je největší rozdíl mezi funkcemi SUMA a SUBTOTAL.
Syntaxe SUBTOTAL vypadá takto:
SUBTOTAL(konstanta funkce;odkaz1;[odkaz2];...)
Pokud chcete do oblasti zahrnout skryté hodnoty, měli byste nastavit argument function_num na hodnotu 2.
Pokud chcete vyloučit skryté hodnoty v oblasti, nastavte argument function_num na 102.
Počítání hodnot na základě jedné nebo více podmínek
Počet buněk v oblasti, které splňují vámi zadané podmínky (neboli kritéria), můžete spočítat pomocí několika funkcí listu.
Video: Použití funkcí POČET, COUNTIF a POČET2
Podívejte se na následující video, které vám ukáže, jak použít funkce POČET, COUNTIF a POČET2 ke spočítání jen těch buněk, které splňují vámi zadané podmínky.
Počítání buněk v oblasti pomocí funkce POČET
Pomocí funkce POČET ve vzorci spočítáte počet číselných hodnot v oblasti.
Ve výše uvedeném příkladu jsou A2, A3 a A6 jedinými buňkami, které obsahují číselné hodnoty v oblasti, takže výstup je 3.
Poznámka: A7 je časová hodnota, ale obsahuje text (a.m.), proto ji funkce COUNT nepovažuje za číselnou hodnotu. Pokud byste odebrali a.m. funkce COUNT bude považovat hodnotu A7 za číselnou hodnotu a změní výstup na 4.
Počítání buněk v oblasti na základě jedné podmínky pomocí funkce COUNTIF
Pomocí funkce COUNTIF můžete spočítat, kolikrát se určitá hodnota objeví v oblasti buněk.
Počítání buněk ve sloupci na základě jedné nebo více podmínek pomocí funkce DPOČET
Funkce DCOUNT spočítá buňky, které obsahují čísla v poli (sloupci) záznamů v seznamu nebo databázi, které splňují zadané podmínky.
V následujícím příkladu chcete zjistit počet měsíců včetně nebo pozdějších než březen 2016, které měly více než 400 prodaných jednotek. První tabulka v listu, od A1 do B7, obsahuje data o prodeji.
Funkce DCOUNT používá podmínky k určení, odkud se mají vrátit hodnoty. Podmínky se obvykle zadávají do buněk v samotném listu a potom na tyto buňky odkazujete v argumentu kritéria . V tomto příkladu obsahují buňky A10 a B10 dvě podmínky – jednu, která určuje, že návratová hodnota musí být větší než 400, a druhá, která určuje, že koncový měsíc by měl být roven nebo větší než 31. března 2016.
Měli byste použít následující syntaxi:
=DPOČET(A1:B7;"Konec měsíce";A9:B10)
Funkce DCOUNT zkontroluje data v oblasti A1 až B7, použije podmínky zadané v A10 a B10 a vrátí hodnotu 2, celkový počet řádků, které splňují obě podmínky (řádky 5 a 7).
Počítání buněk v oblasti na základě více podmínek pomocí funkce COUNTIFS
Funkce COUNTIFS se podobá funkci COUNTIF, až na jeden zásadní rozdíl: funkce COUNTIFS umožňuje použít kritéria u buněk ve více oblastech a spočítá, kolikrát jsou splněná všechna kritéria. S funkcí COUNTIFS se dá použít až 127 dvojic oblast/kritérium.
Syntaxe funkce COUNTIFS:
COUNTIFS(oblast_kritérií1;kritérium1;[oblast_kritérií2;kritérium2];…)
Podívejte se na následující příklad:
Určení počtu výskytů podle kritéria pomocí funkcí POČET a KDYŽ
Řekněme, že potřebujete zjistit, kolik prodejců prodalo konkrétní položku v určité oblasti, nebo chcete vědět, kolik prodejů přes určitou hodnotu provedl konkrétní prodejce. Funkce KDYŽ a POČET můžete používat společně. To znamená, že nejprve použijete funkci KDYŽ k otestování podmínky a pak pouze v případě, že je výsledek funkce KDYŽ true, použijete funkci POČET k počítání buněk.
Poznámky:
-
Vzorce v tomto příkladu se musí zadat jako maticové vzorce. Pokud jste tento sešit otevřeli v Excelu pro Windows nebo Excelu 2016 pro Mac a chcete vzorec změnit nebo vytvořit podobný vzorec, stiskněte klávesu F2 a potom kombinaci kláves Ctrl+Shift+Enter, aby vzorec vrátil očekávané výsledky. V dřívějších verzích Excel pro Mac použijte +Shift+Enter.
-
Aby tyto vzorce v příkladu fungovaly, musí být druhým argumentem funkce KDYŽ číslo.
Určení počtu výskytů více textových nebo číselných hodnot pomocí funkcí SUMA a KDYŽ
V následujících příkladech jsou společně použité funkce KDYŽ a SUMA. Funkce KDYŽ nejdřív otestuje hodnoty v některých buňkách, a pokud se vrátí výsledek Pravda, funkce SUMA u těchto hodnot, které úspěšně prošly testem, spočítá součet.
Příklad 1
Uvedená funkce říká, že když oblast C2:C7 obsahuje hodnoty Novák a Veselý, měla by funkce SUMA zobrazit součet záznamů, v nichž je podmínka splněná. V zadané oblasti vzorec vyhledá tři záznamy pro hodnotu Novák a jeden záznam pro hodnotu Veselý a zobrazí 4.
Příklad 2
Uvedená funkce říká, že když oblast D2:D7 obsahuje hodnoty menší než 9 000 Kč nebo větší než 19 000 Kč, měla by funkce SUMA zobrazit součet všech příslušných záznamů, v nichž je podmínka splněná. Vzorec vyhledá dva záznamy D3 a D5 s hodnotami menšími než 9 000 Kč a potom záznamy D4 a D6 s hodnotami většími než 19 000 Kč a zobrazí 4.
Příklad 3
Výše uvedená funkce říká, že pokud má D2:D7 faktury pro Společnost Buchanan za méně než 9000 USD, měla by funkce SUMA zobrazit součet záznamů, ve kterých je splněna podmínka. Vzorec zjistí, že podmínku splňuje buňka C6, a zobrazí 1.
Důležité informace: Vzorce v tomto příkladu musí být zadané jako maticové. To znamená, že stisknete klávesu F2 a potom kombinaci kláves Ctrl+Shift+Enter. Ve starších verzích Excelu pro Mac použijte klávesovou zkratku +Shift+Enter.
Další tipy najdete v následujících článcích znalostní báze Knowledge Base:
Počet buněk ve sloupci nebo řádku v kontingenční tabulce
Kontingenční tabulka shrnuje vaše data a pomáhá analyzovat data a procházet je k podrobnostem tím, že vám umožní zvolit kategorie, ve kterých chcete data zobrazit.
Kontingenční tabulku můžete rychle vytvořit tak, že v oblasti dat nebo excelové tabulce vyberete buňku a na kartě Vložení ve skupině Tabulky kliknete na možnost Kontingenční tabulka.
Podívejme se na vzorový scénář tabulky Prodej, ve které můžete spočítat, kolik hodnot pro prodej existuje pro Golf a Tenis v konkrétních čtvrtletích.
Poznámka: Pokud chcete získat interaktivní prostředí, můžete tyto kroky spustit na ukázkových datech, která jsou k dispozici v listu kontingenční tabulky v sešitu ke stažení.
-
Do excelové tabulky zadejte následující data.
-
Vyberte A2:C8.
-
Klikněte na Vložení > Kontingenční tabulka.
-
V dialogovém okně Vytvořit kontingenční tabulku klikněte na Vybrat tabulku či oblast, potom na Nový list a nakonec klikněte na OK.
Na novém listu se vytvoří prázdná kontingenční tabulka.
-
V podokně polí kontingenční tabulky proveďte toto:
-
Přetáhněte Sport do oblasti Řádky.
-
Přetáhněte Čtvrtletí do oblasti Sloupce.
-
Přetáhněte Prodeje do oblasti Hodnoty.
-
Zopakujte krok C.
Název pole se zobrazí jako SoučetProdeje2 jak v kontingenční tabulce, tak i v oblasti Hodnoty.
V tuto chvíli vypadá podokno polí kontingenční tabulky takto:
-
V oblasti Hodnoty klikněte na rozevírací seznam vedle položky SoučetProdeje2 a vyberte Nastavení polí hodnot.
-
V dialogu Nastavení polí hodnot udělejte toto:
-
V oddílu Kritéria pro shrnutí hodnot pole vyberte Počet.
-
V poli Vlastní název změňte název na Počet.
-
Klikněte na tlačítko OK.
-
V kontingenční tabulce se spolu s údaji o prodeji zobrazí také počet záznamů pro Golf a Tenis ve 3. a 4. čtvrtletí.
-
Počítání, když data obsahují prázdné hodnoty
Pomocí funkcí listu můžete spočítat buňky, které obsahují data nebo které jsou prázdné.
Počítání neprázdných buněk v oblasti pomocí funkce POČET2
Funkce COUNTA slouží k počítání pouze buněk v oblasti, které obsahují hodnoty.
Při počítání buněk můžete někdy chtít ignorovat všechny prázdné buňky, protože význam pro vás mají jen buňky s hodnotami. Chcete například spočítat celkový počet prodejců, kteří provedli prodej (sloupec D).
Funkce COUNTA ignoruje prázdné hodnoty v D3, D4, D8 a D11 a spočítá pouze buňky obsahující hodnoty ve sloupci D. Funkce najde šest buněk ve sloupci D obsahujících hodnoty a jako výstup zobrazí 6 .
Počet neprázdných buněk v seznamu s konkrétními podmínkami pomocí funkce DCOUNTA
Funkci POČET2 použijte ke zjištění počtu neprázdných buněk ve sloupci záznamů ze seznamu nebo databáze, které splňují vámi zadané podmínky.
Následující příklad používá funkci DCOUNTA k číslu počtu záznamů v databázi obsažené v oblasti A1:B7, které splňují podmínky zadané v oblasti kritérií A9:B10. Tyto podmínky jsou takové, že hodnota ID produktu musí být větší nebo rovna 2000 a hodnota Hodnocení musí být větší než nebo rovna 50.
DCOUNTA najde dva řádky, které splňují podmínky – řádky 2 a 4, a jako výstup zobrazí hodnotu 2 .
Počítání prázdných buněk v souvislé oblasti pomocí funkce COUNTBLANK
Funkce COUNTBLANK slouží k vrácení počtu prázdných buněk v souvislé oblasti (buňky jsou souvislé, pokud jsou všechny propojené v nepřerušené sekvenci). Buňka obsahující vzorec, který vrací prázdný text (""), se započítá.
Při počítání buněk se může stát, že budete chtít zahrnout prázdné buňky, protože pro vás mají význam. V následujícím příkladu tabulky prodejů potravin. Předpokládejme, že chcete zjistit, kolik buněk neobsahuje uvedené údaje o prodeji.
Poznámka: Funkce listu COUNTBLANK poskytuje nejpohodlnější metodu pro určení počtu prázdných buněk v oblasti, ale nefunguje příliš dobře, když jsou buňky, které vás zajímají, v uzavřeném sešitu nebo když netvoří souvislou oblast. Článek znalostní báze Knowledge Base XL: Kdy použít funkci SUMA(KDYŽ()) místo funkce CountBlank() ukazuje, jak v těchto případech použít maticový vzorec SUMA(KDYŽ()).
Počítání prázdných buněk v nesouvislé oblasti pomocí kombinace funkcí SUMA a KDYŽ
Použijte kombinaci funkcí SUMA a KDYŽ. Obecně to uděláte pomocí funkce KDYŽ v maticovém vzorci, která určí, jestli každá odkazovaná buňka obsahuje hodnotu, a pak sečte počet hodnot NEPRAVDA vrácených vzorcem.
Podívejte se na několik příkladů kombinací funkcí SUMa a KDYŽ v předchozí části Určení počtu výskytů více textových nebo číselných hodnot pomocí funkcí SUMA a KDYŽ společně v tomto tématu.
Počítání jedinečných výskytů hodnot
Jedinečné hodnoty v oblasti můžete spočítat společně pomocí funkcí kontingenční tabulky, FUNKCE COUNTIF, SUMa a KDYŽ nebo dialogového okna Rozšířený filtr .
Počítání množství jedinečných hodnot ve sloupci seznamu pomocí rozšířeného filtru
Pomocí dialogového okna Rozšířený filtr můžete ve sloupci dat vyhledat jedinečné hodnoty. Hodnoty můžete buď filtrovat na místě, nebo extrahovat a vložit do nového umístění. Pomocí funkce ŘÁDKY pak můžete spočítat položky v nové oblasti.
Pokud chcete použít rozšířený filtr, klikněte na kartu Data a ve skupině Seřadit & Filtr klikněte na Upřesnit.
Následující obrázek znázorňuje, jak můžete pomocí funkce Rozšířený filtr na nové místo na listu zkopírovat jen jedinečné záznamy.
Na následujícím obrázku sloupec E obsahuje hodnoty, které byly zkopírovány z oblasti ve sloupci D.
Poznámky:
-
Pokud vyfiltrujete data na místě, hodnoty se z listu neodstraní, ale může se skrýt jeden nebo více řádků. Jestli chcete hodnoty znovu zobrazit, klikněte na tlačítko Vymazat ve skupině Seřadit a filtrovat na kartě Data.
-
Pokud chcete jen rychle zjistit počet jedinečných hodnot, po použití Rozšířeného filtru (po vyfiltrování nebo zkopírování dat) vyberte data a pak se podívejte na stavový řádek. Hodnota Počet na stavovém řádku by se měla rovnat počtu jedinečných hodnot.
Další informace najdete v tématu Filtrování pomocí upřesňujících kritérií.
Spočítejte počet jedinečných hodnot v oblasti, které splňují jednu nebo více podmínek, pomocí funkcí KDYŽ, SUMA, FREKVENCE, POZVYHLEDAT a DÉLKA.
Použijte různé kombinace funkcí KDYŽ, SUMA, ČETNOSTI, POZVYHLEDAT a DÉLKA.
Další informace a příklady najdete v části "Určení počtu jedinečných hodnot pomocí funkcí" v článku Počet jedinečných hodnot mezi duplikáty.
Speciální případy (počet všech buněk, počet slov)
Počet buněk nebo počet slov v oblasti můžete spočítat pomocí různých kombinací funkcí listu.
Počítání celkového počtu buněk v oblasti pomocí funkcí ŘÁDKY a SLOUPCE
Předpokládejme, že chcete určit rozsah velkého listu, abyste se mohli rozhodnout, jestli v listu použijete ruční nebo automatické výpočty. Pokud chcete spočítat všechny buňky v oblasti, použijte vzorec, který vynásobí návratové hodnoty pomocí funkcí ŘÁDKY a SLOUPCE . Příklad najdete na následujícím obrázku:
Počítání slov v oblasti pomocí kombinace funkcí SUMA, KDYŽ, DÉLKA, OŘÍZNUTÍ a DOSADIT
V maticovém vzorci můžete použít kombinaci funkcí SUMA, KDYŽ, DÉLKA, STŘIH a DOSADIT . Následující příklad ukazuje výsledek použití vnořeného vzorce k vyhledání počtu slov v oblasti 7 buněk (z nichž 3 jsou prázdné). Některé buňky obsahují mezery na začátku nebo na konci – funkce PROČISTIT a DOSADIT odeberou tyto přebytečné mezery, než začne počítání. Podívejte se na následující příklad:
Aby výše uvedený vzorec fungoval správně, musíte ho nastavit jako maticový vzorec, jinak vzorec vrátí #VALUE! . Uděláte to tak, že kliknete na buňku se vzorcem a potom na řádku vzorců stisknete kombinaci kláves Ctrl+Shift+Enter. Excel přidá na začátek a konec vzorce složenou závorku, takže se z něj vytvoří maticový vzorec.
Další informace o maticových vzorcích najdete v tématech Přehled vzorců v Excelu a Vytvoření maticového vzorce.
Zobrazení výpočtů a počtů na stavovém řádku
Pokud je vybraná jedna nebo více buněk, informace o datech v těchto buňkách se zobrazí na stavovém řádku Excelu. Například pokud máte na listu vybrané čtyři buňky a ty obsahují hodnoty 2, 3, textový řetězec (třeba „cloud“) a 4, na stavovém řádku se mohou současně zobrazit všechny následující hodnoty: Průměr, Počet, Počet buněk s čísly, Minimum, Maximum a Součet. Pokud chcete některé nebo všechny tyto hodnoty zobrazit nebo skrýt, klikněte na stavový řádek pravým tlačítkem. Tyto hodnoty jsou uvedené v následujícím příkladu.
Potřebujete další pomoc?
Kdykoli se můžete zeptat odborníka z komunity Excel Tech nebo získat podporu v komunitách.