Počítanie je jednou zo základných súčastí analýzy údajov, či už chcete spočítať zamestnancov oddelenia vo svojej organizácii, alebo jednotky predané v jednotlivých štvrťrokoch. Excel poskytuje viacero techník na spočítanie buniek, riadkov alebo stĺpcov s údajmi. V tomto článku nájdete komplexný súhrn metód, zošit na stiahnutie s interaktívnymi príkladmi a prepojenia na súvisiace témy, ktoré vám pomôžu lepšie porozumieť.
Poznámka: Nezamieňajte si počítanie so sčítavaním. Ďalšie informácie o sčítaní hodnôt v bunkách, stĺpcoch alebo riadkoch nájdete v téme Sčítanie spôsobov pridávania a sčítania excelových údajov.
Stiahnite si naše príklady
Môžete si stiahnuť vzorový zošit s príkladmi na doplnenie informácií v tomto článku. Väčšina častí tohto článku bude odkazovať na príslušný hárok v rámci vzorového zošita, ktorý obsahuje príklady a ďalšie informácie.
Stiahnutie príkladov na spočítanie hodnôt v tabuľkovom hárku
Obsah tohto článku
-
Počítanie s jednou alebo viacerými podmienkami
-
Počítanie buniek v rozsahu s jednou podmienkou pomocou funkcie COUNTIF
-
Počítanie buniek v stĺpci s jednou alebo viacerými podmienkami pomocou funkcie DCOUNT
-
Spočítanie buniek v rozsahu na základe viacerých podmienok pomocou funkcie COUNTIFS
-
Spočítanie počtu výskytov na základe kritérií pomocou funkcií COUNT a IF
-
Spočítanie počtu výskytov viacerých textových alebo číselných hodnôt pomocou funkcií SUM a IF
-
Počítanie buniek v stĺpci alebo riadku v kontingenčnej tabuľke
-
Počítanie, ak údaje obsahujú prázdne hodnoty.
-
Počítanie buniek v rozsahu, ktoré nie sú prázdne, pomocou funkcie COUNTA
-
Spočítanie buniek v zozname, ktoré nie sú prázdne, pomocou funkcie DCOUNTA
-
Počítanie prázdnych buniek v súvislom rozsahu pomocou funkcie COUNTBLANK
-
Počítanie prázdnych buniek v nesúvislom rozsahu pomocou kombinácie funkcií SUM a IF
-
Jednoduché počítanie
Môžete spočítať hodnoty v rozsahu alebo tabuľke pomocou jednoduchého vzorca, kliknutím na tlačidlo alebo pomocou funkcie hárka.
Excel môže zobraziť aj počet vybratých buniek v excelovom stavový riadok. Pozrite si nasledujúcu video ukážku, kde sa môžete rýchlo pozrieť na používanie stavového riadka. Ďalšie informácie nájdete v časti Zobrazenie výpočtov a počtov v stavovom riadku . Ak chcete rýchlo zobraziť údaje a nemáte čas na zadávanie vzorcov, môžete odkazovať na hodnoty zobrazené v stavovom riadku.
Video: Počítanie buniek pomocou stavového riadka Excelu
Pozrite si nasledujúce video a zistite, ako zobraziť počet v stavovom riadku.
Použitie funkcie Automatický súčet
Pomocou funkcie Automatický súčet vyberte rozsah buniek, ktorý obsahuje aspoň jednu číselnú hodnotu. Potom na karte Vzorce kliknite na položku Automatický súčet > Spočítať čísla.
Excel zobrazí počet číselných hodnôt v rozsahu v bunke vedľa rozsahu, ktorý ste vybrali. Výsledok sa bežne zobrazí v bunke vpravo, ak ste označili vodorovný rozsah, a v bunke nižšie, ak ide o zvislý rozsah.
Pridanie riadka Medzisúčet
Do excelových údajov môžete pridať riadok medzisúčtu. Kliknite na ľubovoľné miesto v údajoch a potom kliknite na položku Údaje > Medzisúčet.
Poznámka: Možnosť Medzisúčet bude fungovať iba s normálnymi excelovými údajmi, a nie s excelovými tabuľkami, kontingenčnými tabuľkami ani kontingenčnými grafmi.
Pozrite si aj nasledujúce články:
Počítanie buniek v zozname alebo v stĺpci excelovej tabuľky pomocou funkcie SUBTOTAL
Funkcia SUBTOTAL sa používa na spočítanie hodnôt v excelovej tabuľke alebo rozsahu buniek. Ak tabuľka alebo rozsah obsahuje skryté bunky, môžete použiť funkciu SUBTOTAL na zahrnutie alebo vylúčenie týchto skrytých buniek, čo je najväčší rozdiel medzi funkciami SUM a SUBTOTAL.
Syntax SUBTOTAL vyzerá takto:
SUBTOTAL(číslo_funkcie;odk1;[odk2];...])
Ak chcete do rozsahu zahrnúť skryté hodnoty, mali by ste nastaviť argument function_num na hodnotu 2.
Ak chcete vylúčiť skryté hodnoty v rozsahu, nastavte argument function_num na hodnotu 102.
Počítanie s jednou alebo viacerými podmienkami
Na spočítanie buniek v rozsahu, ktoré spĺňajú podmienky (nazývajú sa tiež kritériá) môžete použiť viacero funkcií hárka.
Video: Použitie funkcií COUNT, COUNTIF a COUNTA
V nasledujúcom videu sa dozviete, ako sa používa funkcia COUNT a ako použiť funkcie COUNTIF a COUNTA na spočítanie buniek, ktoré spĺňajú vami zadané podmienky.
Počítanie buniek v rozsahu pomocou funkcie COUNT
Pomocou funkcie COUNT vo vzorci spočítajte počet číselných hodnôt v rozsahu.
Vo vyššie uvedenom príklade sú bunky A2, A3 a A6 jediné bunky, ktoré obsahujú číselné hodnoty v rozsahu, a preto je výstup 3.
Poznámka: A7 je časová hodnota, ale obsahuje text (hod.), preto ju funkcia COUNT nepovažuje za číselnú hodnotu. Ak by ste mali odstrániť ráno. Z bunky bude funkcia COUNT považovať bunku A7 za číselnú hodnotu a zmení výstup na hodnotu 4.
Počítanie buniek v rozsahu s jednou podmienkou pomocou funkcie COUNTIF
Funkcia COUNTIF sa používa na spočítanie počtu výskytov určitej hodnoty v rozsahu buniek.
Počítanie buniek v stĺpci s jednou alebo viacerými podmienkami pomocou funkcie DCOUNT
Funkcia DCOUNT spočíta bunky obsahujúce čísla v poli (stĺpci) záznamov v zozname alebo databáze, ktoré spĺňajú zadané podmienky.
V nasledujúcom príklade chcete zistiť počet mesiacov vrátane alebo neskorších ako marec 2016, ktoré mali viac ako 400 predaných jednotiek. Prvá tabuľka v hárku od A1 po B7 obsahuje údaje o predaji.
Funkcia DCOUNT používa podmienky na určenie, odkiaľ sa majú hodnoty vrátiť. Podmienky sa zvyčajne zadávajú do buniek v samotnom hárku a potom odkazujú na tieto bunky v argumente kritériá . V tomto príklade bunky A10 a B10 obsahujú dve podmienky – jednu, ktorá určuje, že vrátená hodnota musí byť väčšia ako 400, a druhá, ktorá určuje, že koncový mesiac by mal byť rovnaký alebo väčší ako 31. marec 2016.
Mali by ste použiť nasledujúcu syntax:
=DCOUNT(A1:B7;"Koniec mesiaca";A9:B10)
Funkcia DCOUNT skontroluje údaje v rozsahu A1 až B7, použije podmienky zadané v rozsahu A10 a B10 a vráti hodnotu 2, čo je celkový počet riadkov, ktoré spĺňajú obe podmienky (riadky 5 a 7).
Spočítanie buniek v rozsahu na základe viacerých podmienok pomocou funkcie COUNTIFS
Funkcia COUNTIFS je podobná funkcii COUNTIF až na jednu dôležitú výnimku: funkcia COUNTIFS umožňuje používať kritériá na bunky v rámci viacerých rozsahov a spočíta, koľkokrát boli splnené všetky kritériá. S funkciou COUNTIFS môžete použiť až 127 párov rozsahov alebo kritérií.
Syntax funkcie COUNTIFS vyzerá takto:
COUNTIFS(rozsah_kritérií1; kritériá1; [rozsah_kritérií2; kritériá2];…)
Pozrite si nasledujúci príklad:
Spočítanie počtu výskytov na základe kritérií pomocou funkcií COUNT a IF
Povedzme, že potrebujete určiť počet predajcov, ktorí predali konkrétnu položku v určitej oblasti, alebo chcete vedieť, koľko predaja za určitú hodnotu vykonal konkrétny predajca. Funkcie IF a COUNT môžete používať spoločne. to znamená, že najprv použijete funkciu IF na testovanie podmienky a potom, iba ak má výsledok funkcie IF hodnotu True, použijete funkciu COUNT na spočítanie buniek.
Poznámky:
-
Vzorce v tomto príklade treba zadať ako vzorce poľa. Ak ste tento zošit otvorili v Exceli pre Windows alebo Excel 2016 pre Mac a chcete vzorec zmeniť alebo vytvoriť podobný vzorec, stlačte kláves F2, potom kombináciu klávesov Ctrl + Shift + Enter a vzorec vráti očakávané výsledky. V starších verziách Excel pre Mac použite kombináciu klávesov + Shift + Enter.
-
Na to, aby vzorce v príklade fungovali správne, druhý argument funkcie IF musí byť číslo.
Spočítanie počtu výskytov viacerých textových alebo číselných hodnôt pomocou funkcií SUM a IF
V nasledujúcich príkladoch sme použili spoločne funkcie IF a SUM. Funkcia IF najskôr testuje hodnoty v niektorých bunkách a ak je výsledok testu True, funkcia SUM sčíta tie hodnoty, ktoré prešli testom.
Príklad 1
Vyššie uvedená funkcia hovorí, že ak rozsah C2:C7 obsahuje hodnoty Kováč a Novák, potom by mala funkcia SUM zobraziť súčet záznamov, ktoré spĺňajú podmienku. Vzorec nájde v danom rozsahu tri záznamy pre hodnotu Kováč a jeden pre hodnotu Novák a zobrazí číslo 4.
Príklad 2
Vyššie uvedená funkcia hovorí, že ak rozsah D2:D7 obsahuje hodnoty, ktoré sú menšie ako 9 000 EUR alebo väčšie ako 19 000 EUR, potom by mala funkcia SUM zobraziť súčet všetkých záznamov, ktoré spĺňajú podmienku. Vzorec nájde dva záznamy D3 a D5 s hodnotami menšími ako 9 000 EUR, potom záznamy D4 a D6 s hodnotami väčšími ako 19 000 EUR a zobrazí číslo 4.
Príklad 3
Vyššie uvedená funkcia hovorí, že ak má funkcia D2:D7 faktúry pre Kováča za menej ako 9 000 EUR, funkcia SUM by mala zobraziť súčet záznamov, v ktorých je splnená podmienka. Vzorec zistí, že bunka C6 spĺňa podmienku, a zobrazí číslo 1.
Dôležité: Vzorce v tomto príklade musia byť zadané ako vzorce polí. To znamená, že stlačíte kláves F2 a potom kombináciu klávesov Ctrl + Shift + Enter. V starších verziách Excelu pre Mac použite kombináciu + Shift + Enter.
Ďalšie tipy nájdete v týchto článkoch databázy Knowledge Base:
Počítanie buniek v stĺpci alebo riadku v kontingenčnej tabuľke
Kontingenčná tabuľka sumarizuje údaje a pomáha analyzovať a prechádzať na detaily údajov tak, že umožňuje vybrať kategórie, v ktorých chcete údaje zobraziť.
Kontingenčnú tabuľku môžete rýchlo vytvoriť tak, že označíte bunku v rozsahu údajov alebo excelovej tabuľke a na karte Vložiť v skupine Tabuľky kliknete na položku Kontingenčná tabuľka.
Ukážeme si vzorový scenár tabuľkového hárka predaja, kde môžete spočítať počet hodnôt predaja pre golf a tenis v konkrétnych štvrťrokoch.
Poznámka: Ak chcete využívať interaktívne prostredie, môžete tieto kroky spustiť na vzorových údajoch uvedených v hárku kontingenčnej tabuľky v zošite na stiahnutie.
-
V excelovom tabuľkovom hárku zadajte nasledujúce údaje.
-
Vyberte bunky A2:C8
-
Kliknite na položky Vložiť > Kontingenčná tabuľka.
-
V dialógovom okne Vytvorenie kontingenčnej tabuľky kliknite na položku Vybrať tabuľku alebo rozsah, potom kliknite na položku Nový hárok a potom na položku OK.
Vytvorí sa prázdna kontingenčná tabuľka v novom hárku.
-
Na table Polia kontingenčnej tabuľky postupujte takto:
-
Presuňte položku Šport do oblasti Riadky.
-
Presuňte položku Štvrťrok do oblasti Stĺpce.
-
Presuňte položku Predaj do oblasti Hodnoty.
-
Opakujte krok c.
Názov poľa sa zobrazí ako SúčetPredaja2 v kontingenčnej tabuľke a aj v oblasti hodnôt.
V tomto bode tabla Polia kontingenčnej tabuľky vyzerá takto:
-
V oblasti Hodnoty kliknite na rozbaľovací zoznam vedľa položky SúčetPredaja2 a vyberte položku Nastavenie poľa hodnoty.
-
V dialógovom okne Pole hodnoty – nastavenie postupujte takto:
-
V časti Sumarizovať pole hodnoty podľa vyberte položku Počet.
-
V poli Vlastný názov upravte názov na Počet.
-
Kliknite na tlačidlo OK.
-
Kontingenčná tabuľka zobrazí počet záznamov pre golf a tenis za 3. štvrťrok a 4. štvrťrok spolu s údajmi o predaji.
-
Počítanie, ak údaje obsahujú prázdne hodnoty.
Pomocou funkcií hárka možno počítať bunky, ktoré obsahujú údaje, alebo sú naopak prázdne.
Počítanie buniek v rozsahu, ktoré nie sú prázdne, pomocou funkcie COUNTA
Funkcia COUNTA sa používa na spočítanie iba buniek v rozsahu, ktorý obsahuje hodnoty.
Pri počítaní buniek môže byť niekedy potrebné ignorovať prázdne bunky, pretože význam majú iba bunky obsahujúce hodnoty. Chcete napríklad spočítať celkový počet predajcov, ktorí uskutočnili predaj (stĺpec D).
Funkcia COUNTA ignoruje prázdne hodnoty v bunkách D3, D4, D8 a D11 a spočíta iba bunky obsahujúce hodnoty v stĺpci D. Funkcia vyhľadá šesť buniek v stĺpci D obsahujúcich hodnoty a zobrazí 6 ako výstup.
Spočítanie buniek v zozname, ktoré nie sú prázdne, pomocou funkcie DCOUNTA
Použite funkciu DCOUNA na spočítanie buniek v stĺpci záznamov v zozname alebo databáze, ktoré nie sú prázdne a spĺňajú zadané kritériá.
Nasledujúci príklad používa funkciu DCOUNTA na spočítanie počtu záznamov v databáze obsiahnutej v rozsahu A1:B7, ktoré spĺňajú podmienky zadané v rozsahu kritérií A9:B10. Ide o podmienky, že hodnota ID produktu musí byť väčšia alebo rovná 2 000 a hodnota Hodnotenie musí byť väčšia alebo rovná 50.
Funkcia DCOUNTA vyhľadá dva riadky, ktoré spĺňajú podmienky– riadky 2 a 4 a ako výstup zobrazí hodnotu 2 .
Počítanie prázdnych buniek v súvislom rozsahu pomocou funkcie COUNTBLANK
Funkcia COUNTBLANK sa používa na vrátenie počtu prázdnych buniek v súvislom rozsahu (bunky sú súvislé, ak sú všetky spojené v neprerušenej postupnosti). Ak bunka obsahuje vzorec, ktorého výsledkom je prázdny text (""), zahrnie sa do výpočtu.
Pri počítaní buniek môže byť niekedy potrebné zahrnúť do výpočtu aj prázdne bunky, pretože majú pre vás význam. V nasledujúcom príklade tabuľkového hárka s predajom potravín. predpokladajme, že chcete zistiť, koľko buniek neobsahuje uvedené údaje o predaji.
Poznámka: Funkcia hárka COUNTBLANK poskytuje najvhodnejšiu metódu na určenie počtu prázdnych buniek v rozsahu, ale nefunguje veľmi dobre, keď sa požadované bunky nachádzajú v uzavretom zošite alebo ak nevytvárajú súvislý rozsah. Článok databázy Knowledge Base XL: Kedy použiť funkciu SUM(IF()) namiesto funkcie CountBlank() vám ukáže, ako používať vzorec poľa SUM(IF()) v týchto prípadoch.
Počítanie prázdnych buniek v nesúvislom rozsahu pomocou kombinácie funkcií SUM a IF
Použite kombináciu funkcie SUM a funkcie IF . Vo všeobecnosti to urobíte pomocou funkcie IF vo vzorci poľa na určenie, či každá bunka, na ktorú sa odkazuje, obsahuje hodnotu, a následné sčítanie počtu hodnôt FALSE vrátených vzorcom.
Pozrite si niekoľko príkladov kombinácií funkcií SUM a IF v predchádzajúcej časti Spočítanie výskytu viacerých textových alebo číselných hodnôt pomocou funkcií SUM a IF spolu v tejto téme.
Počítanie jedinečných hodnôt
Jedinečné hodnoty v rozsahu môžete spočítať pomocou kontingenčnej tabuľky, funkcie COUNTIF, funkcií SUM a IF alebo dialógového okna Rozšírený filter .
Počítanie jedinečných hodnôt v stĺpci zoznamu pomocou rozšíreného filtra
Pomocou dialógového okna Rozšírený filter môžete nájsť jedinečné hodnoty v stĺpci údajov. Hodnoty môžete filtrovať na pôvodnom mieste, alebo ich môžete vyňať a prilepiť na nové miesto. Potom môžete pomocou funkcie ROWS zistiť počet položiek v novom rozsahu.
Ak chcete použiť rozšírený filter, kliknite na kartu Údaje a v skupine Zoradiť & filter kliknite na položku Rozšírené.
Na nasledujúcom obrázku je znázornené, ako skopírovať pomocou rozšíreného filtra iba jedinečné záznamy na nové miesto v hárku.
Na nasledujúcom obrázku stĺpec E obsahuje hodnoty skopírované z rozsahu v stĺpci D.
Poznámky:
-
Ak filtrujete údaje na mieste, hodnoty sa z hárka neodstránia – jeden alebo viaceré riadky môžu byť skryté. Kliknutím na položku Vymazať v skupine Zoradiť a filtrovať na karte Údaje znova zobrazíte tieto hodnoty.
-
Ak chcete zobraziť len počet jedinečných hodnôt v stručnom prehľade, po použití rozšíreného filtra označte údaje (filtrované alebo skopírované údaje) a pozrite sa na stavový riadok. Hodnota Počet v stavovom riadku by sa mala rovnať počtu jedinečných hodnôt.
Ďalšie informácie nájdete v téme Filtrovanie pomocou rozšírených kritérií
Spočítanie jedinečných hodnôt v rozsahu, ktoré spĺňajú jednu alebo viaceré podmienky, pomocou funkcií IF, SUM, FREQUENCY, MATCH a LEN
Na vykonanie tejto úlohy môžete použiť rôzne kombinácie funkcií IF, SUM, FREQUENCY, MATCH a LEN:
Ďalšie informácie a príklady nájdete v časti Spočítanie jedinečných hodnôt pomocou funkcií v článku Spočítanie jedinečných hodnôt medzi duplikátmi.
Špeciálne prípady (počet všetkých buniek, počet slov)
Pomocou rôznych kombinácií funkcií hárka môžete spočítať bunky alebo počet slov v rozsahu.
Celkový počet buniek v rozsahu pomocou funkcií ROWS a COLUMNS
Potrebujete napríklad zistiť veľkosť rozsiahleho hárka, aby ste sa mohli rozhodnúť, či v zošite použijete manuálny alebo automatický výpočet. Ak chcete spočítať všetky bunky v rozsahu, použite vzorec, ktorý vynásobí vrátené hodnoty pomocou funkcií ROWS a COLUMNS . Príklad nájdete na nasledujúcom obrázku:
Počítanie slov v rozsahu pomocou kombinácie funkcií SUM, IF, LEN, TRIM a SUBSTITUTE
Vo vzorci poľa môžete použiť kombináciu funkcií SUM, IF, LEN, TRIM a SUBSTITUTE . Nasledujúci príklad znázorňuje výsledok použitia vnoreného vzorca na vyhľadanie počtu slov v rozsahu 7 buniek (z ktorých 3 sú prázdne). Niektoré bunky obsahujú úvodné alebo koncové medzery – funkcie TRIM a SUBSTITUTE odstránia tieto nadbytočné medzery ešte pred výskytom akéhokoľvek počítania. Pozrite si nasledujúci príklad:
Ak chcete, aby vyššie uvedený vzorec fungoval správne, musíte ho zmeniť na vzorec poľa. V opačnom prípade vzorec vráti #VALUE! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!. Ak to chcete urobiť, kliknite na bunku obsahujúcu vzorec a potom v riadku vzorcov stlačte kombináciu klávesov Ctrl + Shift + Enter. Excel pridá na začiatok a koniec vzorca zátvorku, čím sa z nej vytvorí vzorec poľa.
Ďalšie informácie o vzorcoch poľa nájdete v téme Prehľad vzorcov v Exceli a Vytvorenie vzorca poľa.
Zobrazenie výpočtov a počtov v stavovom riadku
Keď označíte jednu alebo viaceré bunky, v stavovom riadku Excelu sa zobrazia informácie o údajoch v daných bunkách. Ak napríklad v hárku vyberiete štyri bunky, ktoré obsahujú hodnoty 2, 3, textový reťazec (napríklad „cloud“) a 4, v stavovom riadku sa môžu súčasne zobraziť všetky tieto hodnoty: Priemer, Počet, Číselný počet, Min, Max a Súčet. Kliknutím na stavový riadok pravým tlačidlom myši môžete zobraziť alebo skryť niektoré alebo všetky tieto hodnoty. Hodnoty sú znázornené na nasledujúcom obrázku.
Potrebujete ďalšiu pomoc?
Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.