Tento návod ukazuje, ako používať funkcie Excel AVERAGEIF a AVERAGEIFS v Exceli a Tabuľkách Google na priemer údajov, ktoré spĺňajú určité kritériá.
Prehľad funkcií AVERAGEIF
Funkciu AVERAGEIF v Exceli môžete použiť na počítanie buniek obsahujúcich konkrétnu hodnotu, počítanie buniek väčších alebo rovných hodnote atď.
Ak chcete použiť funkciu pracovného hárka programu AVERAGEIF Excel, vyberte bunku a zadajte:
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Syntax a argumenty funkcie AVERAGEIF:
= AVERAGEIF (rozsah, kritériá, [priemerný_rozsah])
rozsah - Rozsah buniek, ktoré sa majú počítať.
kritériá - Kritériá, ktoré určujú, ktoré bunky sa majú započítať.
priemerný_rozsah - [voliteľné] Priemerné bunky. Ak je vynechaný, použije sa rozsah.
Čo je to funkcia AVERAGEIF?
Funkcia AVERAGEIF je jednou zo starších funkcií používaných v tabuľkových procesoroch. Používa sa na skenovanie v rozsahu buniek, ktoré kontrolujú konkrétne kritérium, a potom poskytuje priemer (aka matematický priemer), ak sú hodnoty v rozsahu, ktoré zodpovedajú týmto hodnotám. Pôvodná funkcia AVERAGEIF bola obmedzená iba na jedno kritérium. Po roku 2007 bola vytvorená funkcia AVERAGEIFS, ktorá umožňuje množstvo kritérií. Väčšina všeobecného použitia medzi nimi zostáva rovnaká, existujú však kritické rozdiely v syntaxi, o ktorej budeme diskutovať v tomto článku.
Ak ste to ešte neurobili, veľa z podobnej štruktúry a príkladov si môžete prečítať v článku COUNTIFS.
Základný príklad
Pozrime sa na tento zoznam zaznamenaných tržieb a chceme poznať priemerný príjem.
Pretože sme mali výdavok, zápornú hodnotu, nemôžeme urobiť len základný priemer. Namiesto toho chceme priemerovať iba hodnoty, ktoré sú vyššie ako 0. „Väčšia ako 0“ je to, čo budú naše kritériá vo funkcii AVERAGEIF. Náš vzorec, ktorý to uvádza, je
= AVERAGEIF (A2: A7, "> 0")
Dvojstĺpcový príklad
Aj keď bola pôvodná funkcia AVERAGEIF navrhnutá tak, aby vám umožnila použiť kritérium na rozsah čísel, ktoré chcete sčítať, väčšinu času budete musieť použiť jedno alebo viac kritérií na ostatné stĺpce. Uvažujme o tejto tabuľke:
Ak teraz použijeme pôvodnú funkciu AVERAGEIF a zistíme, koľko banánov máme v priemere. Naše kritériá vložíme do bunky D1 a budeme musieť zadať požadovaný rozsah priemer ako posledný argument, a taký by bol aj náš vzorec
= AVERAGEIF (A2: A7, D1, B2: B7)
Keď však programátori nakoniec zistili, že používatelia chcú zadať viac ako jedno kritérium, bola vytvorená funkcia AVERAGEIFS. Aby sa vytvorila jedna štruktúra, ktorá by fungovala pre ľubovoľný počet kritérií, AVERAGEIFS vyžaduje, aby bol najskôr uvedený rozsah súčtov. V našom prípade to znamená, že vzorec musí byť
= AVERAGEIFS (B2: B7, A2: A7, D1)
POZNÁMKA: Tieto dva vzorce dosahujú rovnaký výsledok a môžu vyzerať podobne. Dávajte si preto veľký pozor na to, ktorá funkcia sa používa, aby ste uviedli všetky argumenty v správnom poradí.
Práca s dátumami, viac kritérií
Pri práci s dátumami v tabuľke je síce možné zadať dátum priamo do vzorca, ale je najlepšie mať dátum v bunke, aby ste na bunku vo vzorci mohli iba odkazovať. Vďaka tomu počítač napríklad vie, že chcete použiť dátum 27.5.2020, a nie číslo 5 delené 27 delené rokom 2022.
Pozrime sa na našu ďalšiu tabuľku zaznamenávajúcu počet návštevníkov webu každé dva týždne.
V D2 a E2 môžeme určiť počiatočný a koncový bod rozsahu, na ktorý sa chceme pozrieť. Náš vzorec na nájdenie priemerného počtu návštevníkov v tomto rozsahu môže byť:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Všimnite si, ako sme dokázali spojiť kritériá „=“ s odkazmi na bunky a vytvoriť tak kritériá. Aj keď sa obe kritériá uplatňovali na rovnaký rozsah buniek (A2: A7), musíte rozsah zapísať dvakrát, raz za každé kritérium.
Viac stĺpcov
Pri použití viacerých kritérií ich môžete použiť na rovnaký rozsah ako v predchádzajúcom prípade, alebo ich môžete použiť na rôzne rozsahy. Spojme naše vzorové údaje do tejto tabuľky:
Nastavili sme niekoľko buniek, aby používateľ zadal to, čo chce hľadať v bunkách E2 až G2. Potrebujeme teda vzorec, ktorý bude sčítať celkový počet jabĺk nazbieraných vo februári. Náš vzorec vyzerá takto:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS s logikou typu ALEBO
Až do tohto bodu boli všetky príklady, ktoré sme použili, porovnaním A, kde hľadáme riadky, ktoré spĺňajú všetky naše kritériá. Teraz zvážime prípad, keď budete chcieť nájsť možnosť, aby riadok spĺňal jedno alebo druhé kritérium.
Pozrime sa na tento zoznam predajov:
Chceli by sme sčítať priemerné tržby pre Adama a Boba. Najprv krátka diskusia o priemerovaní. Ak máte nerovnomerný počet vecí, napríklad 3 záznamy pre Adama a 2 pre Boba, nemôžete jednoducho vziať priemer tržieb každej osoby. Toto je známe ako priemer priemerov a nakoniec pripisujete nespravodlivú váhu položke, ktorá má málo záznamov. Ak sa to týka vašich údajov, budete musieť priemer vypočítať „manuálne“: súčet všetkých vašich položiek vydelený počtom vašich položiek. Ak sa chcete dozvedieť, ako to urobiť, môžete si pozrieť články tu:
Teraz, ak je počet záznamov rovnaký, ako napríklad v našej tabuľke, máte niekoľko možností, ktoré môžete urobiť. Najjednoduchšie je sčítať dve AVERAGEIFY dohromady a potom ich vydeliť 2 (počet položiek v našom zozname)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam")+AVERAGEIFS (B2: B7, A2: A7, "Bob"))/2
Tu sme nechali počítač vypočítať naše jednotlivé skóre a potom ich sčítali.
Naša ďalšia možnosť je vhodná vtedy, keď máte viac rozsahov kritérií, napríklad aby ste nechceli opakovane prepisovať celý vzorec. V predchádzajúcom vzorci sme počítaču ručne povedali, aby pridal dve rôzne AVERAGEIFS dohromady. Môžete to však urobiť aj tak, že zadáte svoje kritériá do poľa takto:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"})))
Pozrite sa, ako je pole konštruované v zložených zátvorkách. Keď počítač vyhodnotí tento vzorec, bude vedieť, že chceme vypočítať funkciu AVERAGEIFS pre každú položku v našom poli, čím vytvoríme pole čísel. Vonkajšia priemerná funkcia potom vezme toto pole čísel a zmení ho na jedno číslo. Po vyhodnotení vzorca by to vyzeralo takto:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))) = AVERAGE (13701, 21735) = 17718
Dosiahneme rovnaký výsledok, ale vzorec sme dokázali napísať o niečo stručnejšie.
Riešenie prázdnych miest
Niekedy bude mať váš súbor údajov prázdne bunky, ktoré musíte nájsť alebo sa im vyhnúť. Nastavenie kritérií pre ne môže byť trochu zložité, takže sa pozrime na ďalší príklad.
Bunka A3 je skutočne prázdna, zatiaľ čo bunka A5 má vzorec vracajúci reťazec nulovej dĺžky „“. Ak chceme nájsť celkový priemer naozaj prázdne bunky, použili by sme kritérium „=“ a náš vzorec by vyzeral takto:
= AVERAGEIFS (B2: B7, A2: A7, "=")
Na druhej strane, ak chceme získať priemer pre všetky bunky, ktoré vizuálne vyzerajú prázdne, zmeníme kritériá na „“ a vzorec bude vyzerať takto
= AVERAGEIFS (B2: B7, A2: A7, "")
Pozrime sa na to: čo keď chcete nájsť priemer prázdnych buniek? Súčasný dizajn vám bohužiaľ nedovolí vyhnúť sa reťazcom s nulovou dĺžkou. Môžete použiť kritérium „“, ale ako vidíte v príklade, stále obsahuje hodnotu z riadku 5.
= AVERAGEIFS (B2: B7, A2: A7, "")
Ak nepotrebujete počítať bunky obsahujúce reťazce nulovej dĺžky, mali by ste zvážiť použitie funkcie LEN v rámci SUMPRODUKTU
AVERAGEIF v Tabuľkách Google
Funkcia AVERAGEIF funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
