Excel - Návody, vysvetlenie

Funkcie COUNTIF, SUMIF, AVERAGEIF…

Rudolf Zrebný

S funkciami COUNT, SUM, AVERAGE sme sa oboznámili v článku Štatistické funkcie SUM, AVERAGE, MIN…. Všimnite si, že v tomto prípade je k názvu funkcie pripojené IF – AK, čo znamená „splnenie podmienky“.

Stiahnite a otvorte si súbor countif-sumif-averageif-countifs-sumifs-averageifs-vysvetlenie.xlsx. Budeme pracovať v hárku Brigádnici.

Ikona

countif-sumif-averageif-countifs-sumifs-averageifs-vysvetlenie.xlsx

1 súbor(y) 9.67 KB
Stiahnuť súbor

Vo vzorcoch píšte úvodzovky ručne! Automatické formátovanie ich niekedy zobrazí ako dolné úvodzovky a to nie je správne, vzorec potom nefunguje.

Funkcia COUNTIF

1. úloha: Koľkokrát bol odpracovaný počet hodín vyšší ako 50?

Pýtame sa: Koľkokrát bol odpracovaný počet hodín vyšší ako 50?.

Potrebujeme zistiť počet buniek, v ktorých hodnoty spĺňajú dané kritérium. Použijeme funkciu COUNTIF.

Funkcia COUNTIF obsahuje 2 argumenty: =COUNTIF(rozsah; kritérium)

  • rozsah – oblasť, v ktorej overujeme kritérium, v našom prípade stĺpec s počtom odpracovaných hodín;
  • kritérium – podmienka, ktorá má byť splnená, v našom prípade viac ako 50.

Zostavíme vzorec v bunke G2:

  • napíšeme =COUNTIF(;
  • označíme oblasť B2:B10 … =COUNTIF(B2:B10;
  • napíšeme bodkočiarku a do úvodzoviek kritérium … =COUNTIF(B2:B10;“>50″;
  • a stlačíme Enter

Výsledok je 4.

Často sa stáva, že kritérium alebo hodnota, ktorá je jeho súčasťou, je uvedená v samostatnej bunke. Vtedy využívame adresu bunky s kritériom (3. úloha) alebo spojenie porovnávacieho symbolu s adresou bunky pomocou symbolu & (2. úloha).

2. úloha: Koľkokrát bol odpracovaný počet hodín vyšší ako hodnota v bunke B12?

Vzorec: =COUNTIF(B2:B10;“>“&B12)

3. úloha: Koľkokrát bol odpracovaný počet hodín pri splnení kritéria v bunke B13?

Vzorec: =COUNTIF(B2:B10;B13)

SUMIF

4. úloha: Koľko bolo spolu odpracovaných hodín v júli 2018?

Potrebujeme sčítať odpracované hodiny pri splnení kritéria júl 2018. Použijeme funkciu SUMIF.

Funkcia SUMIF obsahuje 2 povinné argumenty a 1 nepovinný argument: =SUMIF(rozsah; kritérium;[rozsah_súhrnu]).

  • rozsah – oblasť, v ktorej overujeme kritérium, v našom prípade stĺpec s obdobím;
  • kritérium – podmienka, ktorá má byť splnená, v našom prípade júl 2018;
  • rozsah súhrnu – nepovinný argument, ktorý využívame vtedy, ak oblasť, v ktorej je potrebné sčítavať údaje je odlišná od oblasti, v ktorej overujeme kritérium.

Zostavíme vzorec v bunke G5:

  • napíšeme =SUMIF(;
  • označíme oblasť A2:A10 … =SUMIF(A2:A10;
  • napíšeme bodkočiarku a kritérium …
  • pri zadávaní kritéria je potrebné využiť jednu z dvoch funkcií – DATE(rok;mesiac;deň) alebo DATEVALUE(„júl 2018“); =SUMIF(A2:A10;DATE(2018;7;1); alebo =SUMIF(A2:A10;DATEVALUE(„júl 2018“);
  • a zadáme rozsah súhrnu – stĺpec s odpracovanými hodinami
  • =SUMIF(A2:A10;DATE(2018;7;1);B2:B10) alebo =SUMIF(A2:A10;DATEVALUE(„júl 2018“);B2:B10)

AVERAGEIF

5. úloha: Koľko bolo priemerne odpracovaných hodín v auguste 2018?

Pýtame sa na priemerný počet odpracovaných hodín pri splnení podmienky august 2018. Využijeme funkciu AVERAGEIF.

Funkcia AVERAGEIF obsahuje 2 povinné argumenty a 1 nepovinný argument: =AVERAGEIF(rozsah; kritérium;[priemerný_rozsah]).

  • rozsah – oblasť, v ktorej overujeme kritérium, v našom prípade stĺpec s obdobím;
  • kritérium – podmienka, ktorá má byť splnená, v našom prípade august 2018;
  • priemerný rozsah – nepovinný argument, ktorý využívame vtedy, ak oblasť, v ktorej je potrebné zisťovať priemer je odlišná od oblasti, v ktorej overujeme kritérium.

Zostavíme vzorec v bunke G6:

  • napíšeme =AVERAGEIF(;
  • označíme oblasť A2:A10 … =AVERAGEIF(A2:A10;
  • napíšeme bodkočiarku a kritérium… =AVERAGEIF(A2:A10;DATEVALUE(„august 2018“);
  • a zadáme rozsah, v ktorom potrebujeme počítať priemer – stĺpec s odpracovanými hodinami… =AVERAGEIF(A2:A10;DATEVALUE(„august 2018“);B2:B10)
  • A stlačíme Enter.

COUNTIFS

6. úloha: Koľkokrát bol počet odpracovaných hodín v auguste 2018 menší ako 70?

V tomto prípade zisťujeme počet buniek v prípade splnenia 2 kritérií – august 2018 a menší ako 70. Využijeme funkciu COUNTIFS, ktorá umožňuje zadanie viacerých podmienok.

Funkcia COUNTIFS obsahuje 2 povinné argumenty a ďalšie nepovinné argumenty: =COUNTIFS(rozsah_kritérií1; kritérium1;[rozsah_kritérií2; kritérium2]…).

Povolených je maximálne 127 dvojíc rozsah-kritérium. Všetky rozsahy musia pozostávať z rovnakého počtu riadkov a stĺpcov.

V bunke G7 zostavíme postupne vzorec:

  • napíšeme =COUNTIFS(;
  • označíme oblasť A2:A10 … =COUNTIFS(A2:A10;
  • napíšeme bodkočiarku a kritérium… =COUNTIFS(A2:A10;DATEVALUE(„august 2018“);
  • po ďalšej bodkočiarke opäť rozsah pre ďalšie kritérium, ktorým je počet odpracovaných hodín… =COUNTIFS(A2:A10;DATEVALUE(„august 2018″);B2:B10;“<70“)
  • A stlačíme Enter.

SUMIFS

7. úloha: Spolu koľko hodín odpracovali chlapci v auguste 2018?

V tomto prípade zisťujeme celkový počet odpracovaných hodín v prípade splnenia 2 kritérií – august 2018 a pohlavie CH. Využijeme funkciu SUMIFS, ktorá umožňuje zadanie viacerých podmienok.

Funkcia SUMIFS obsahuje 3 povinné argumenty a ďalšie nepovinné argumenty: =SUMIFS(rozsah_súhrnu; rozsah_kritérií1; kritérium1;[rozsah_kritérií2; kritérium2]…).

Povolených je maximálne 127 dvojíc rozsah-kritérium. Všetky rozsahy musia pozostávať z rovnakého počtu riadkov a stĺpcov.

V bunke G8 zostavíme postupne vzorec:

  • napíšeme =SUMIFS(;
  • označíme oblasť súhrnu B2:B10 a napíšeme bodkočiarku… =SUMIFS(B2:B10;;
  • označíme oblasť pre 1. kritérium A2:A10, napíšeme bodkočiarku… =SUMIFS(B2:B10;A2:A10;;
  • napíšeme kritérium… =SUMIFS(B2:B10;A2:A10;DATEVALUE(„august 2018“);;
  • označíme rozsah pre 2. kritérium, ktorým je pohlavie CH a do úvodzoviek aj kritérium… =SUMIFS(B2:B10;A2:A10;DATEVALUE(„august 2018″);C2:C10;“CH“);
  • a stlačíme Enter.

AVERAGEIFS

8. úloha: Koľko hodín priemerne odpracovali chlapci od júla 2018?

V tomto prípade zisťujeme priemerný počet odpracovaných hodín v prípade splnenia 2 kritérií – > júl 2018 a pohlavie CH. Využijeme funkciu AVERAGEIFS, ktorá umožňuje zadanie viacerých podmienok.

Funkcia AVERAGEIFS obsahuje 3 povinné argumenty a ďalšie nepovinné argumenty: =AVERAGEIFS(priemerný_rozsah; rozsah_kritérií1; kritérium1;[rozsah_kritérií2; kritérium2]…).

Povolených je maximálne 127 dvojíc rozsah-kritérium. Všetky rozsahy musia pozostávať z rovnakého počtu riadkov a stĺpcov.

V bunke G9 zostavíme postupne vzorec:

  • napíšeme =AVERAGEIFS(;
  • označíme oblasť B2:B10 (potrebujeme priemerný počet hodín) a napíšeme bodkočiarku… =AVERAGEIFS(B2:B10;;
  • označíme oblasť pre 1. kritérium A2:A10, napíšeme bodkočiarku… =AVERAGEIFS(B2:B10;A2:A10;;
  • napíšeme kritérium, „od júla“ znamená, že treba použiť >=… =AVERAGEIFS(B2:B10;A2:A10;“>=“&DATEVALUE(„júl 2018“);;
  • označíme rozsah pre 2. kritérium, ktorým je pohlavie CH a do úvodzoviek aj kritérium… =AVERAGEIFS(B2:B10;A2:A10;
    „>=“& DATEVALUE(„júl 2018″);C2:C10;“CH“);
  • a stlačíme Enter.

Pozrite si obrázok s výsledkami:

A obrázok s vypísanými vzorcami:

25 tipov a trikov v Exceli,
ktoré musíte milovať 🥰

5-denný emailový kurz (text + video)
Neskôr každý týždeň 1 email s ďalšími tipmi

Zásady spracúvania osobných údajov