Výsledky vyhľadávania

Funkcie COUNTIF, SUMIF, AVERAGEIF...

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.

📥 Stiahnuť súbor countif-sumif-averageif-countifs-sumifs-averageifs-vysvetlenie.xlsx

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: