AverageIfs

enAVERAGEIFS

Definice

Funkce zprůměruje hodnoty ve vybrané oblasti pro splněné kritérium nebo více kritérií a oblastí, typicky určité kategorie.

Vzorec

=AVERAGEIFS(oblast_pro_průměr; oblast_kritérií1; kritérium1; [oblast_kritérií2; kritérium2];…;[oblast_kritériíN];[kritériumN])

Argumenty funkce

Oblast_pro_průměr (povinný) – Oblast buněk, které mají být zprůměrovány, pokud jsou splněna kritéria.

Oblast_kritérií1 (povinný) – Oblast kritérií, ve kterých se vyhodnocují podmínky.

Kritérium1 (povinný) – Daná podmínka, která musí být splněna v oblasti kritérií.

Oblast_kritérií2… Oblast_kritériíN (volitelný) – Pokud má být vyhodnoceno více kritérií, je nutné zadat další oblasti. Počet oblastí kritérií musí být roven počtu kritérií.

Kritérium2… KritériumN (volitelný) – Další kritérium, které vždy navazuje na danou oblast kritérií.

Praktické vysvětlení

Oblast pro průměr může být jenom jedna, jelikož výstupem je pouze jedna hodnota, proto není možné mít více oblastí průměru. Lze však mít více oblastí kritérií v případě, kdy je nutné zjistit průměr na základě kritérií ve více sloupcích nebo více hodnot v jednom sloupci. Existuje i funkce AverageIf, která má možnost průměrovat pouze na základě jednoho kritéria v oblasti kritérií. Pomocí funkce AverageIfs lze ale průměrovat data na základě více kritérií, proto je vhodnější ji používat. Počet oblastí kritérií musí být shodná s počtem kritérií.

V sloupci „A“ se nachází jediná oblast kritérií, přičemž oblast pro průměr je v sloupci „B“. Kritérium se nachází v buňce „C2“, tedy nalezne se každý text „Auto“ v sloupci „A“ a k nim se zprůměrují příslušné hodnoty výnosů, pokud mají kategorii „Auto“.

Nyní máme 2 kritéria, jejichž oblasti jsou v odlišných sloupcích. Kritérium „Auto“ je hledáno v sloupci „G“ a kritérium „Červená“ je hledáno v sloupci „H“ barva, přičemž oblast pro průměr je v sloupci „I“ výnosy. Je nutné vždy pár kritérium a oblast kritérií navázat na sebe. Obě kritéria musí být splněné, tedy v sloupci „G“ se musí najít „auto“ a zároveň v sloupci „H“ červená, jinak hodnota výnosů nebude zprůměrována. Proto výsledkem je 125 000, protože pouze 2 řádky splňují obě podmínky zároveň, a to řádek první (100 000) a poslední (150 000), přičemž průměr těchto 2 hodnot je 125 000. Existuje i řádek, kde je napsáno auto, ale má modrou barvu. Stejně tak existuje řádek s červenou barvou, ale kategorií je motocykl, proto tyto řádky nejsou zahrnuty ve výpočtu.

Do funkce lze zadávat i matematické podmínky. Chtějme např. průměrovat sloupec výnosy pouze v případě, kdy náklady budou vyšší než 4 000. V takovém případě můžeme matematický operátor „>4000“ zadat přímo do buňky, nebo přímo do argumentu funkce následovně, kdy se zprůměrují pouze hodnoty, kde je náklady 5 000 a 8 000, jelikož pouze tyto 2 řádky jsou vyšší než 4 000, přičemž jejich výnosy jsou 100 000 a 30 000, proto je výsledkem 65 000.

Použití s ostatními funkcemi

Funkci lze používat zejména ve výrokové logice, kdy chceme zjistit, zdali průměr na základě daného kritéria přesáhne určitou částku, pokud ano, budeme chtít vypsat bonus ve výši 20 000, jinak bude bonus 0, a to s kombinací funkce Když.

=KDYŽ(AVERAGEIFS(A2:A6;B2:B6;C2)>100000;20000;0)

Funkce lze kombinovat s více funkcemi, ne pouze s matematickými. Pokud bychom měli např. v sloupci „A“ datumy, kdy chceme zjistit průměr ze sloupce „B“ pouze v případě, kdy je datum v sloupci „A“ až po dnešním dni, je možné využít kombinaci s funkcí Dnes, kdy je nutné dát do řetězce pouze znaménko větší nebo rovno a následně znakem & přidat danou funkci.

=AVERAGEIFS(B2:B6;A2:A6;“>=“&DNES())

Pokud by funkce začala být nepřehledná z důvodu více oblastí kritérií, je možné definovat proměnné, které se do funkce AverageIfs dosadí pomocí funkce Let.

=LET(data;B2:B6;podmínka;A2:A6;AVERAGEIFS(data;podmínka;“auto“))