AVERAGEIF
Vrátí aritmetický průměr všech buněk oblasti, které splňují danou podmínku. Funkce AVERAGEIF sečte všechny výsledky, které vyhovují danému logickému testu, a součet vydělí počtem vybraných hodnot.
AVERAGEIF(Oblast; Kritérium [; Oblast_pro_průměr ])
Oblast – povinný argument. Pole, název pojmenované oblasti nebo popisek sloupce nebo řádku obsahujícího čísla pro výpočet průměru nebo čísla či text pro podmínku.
Vyhledávání podporuje zástupné znaky nebo regulární výrazy. Zadáte-li například "all.*", najdete první výskyt "all" následovaný jakýmikoliv znaky. Chcete-li hledat text, který je rovněž regulárním výrazem, musíte před každý regulární výraz napsat metaznak nebo operátor se znakem "\" nebo text uzavřít mezi \Q...\E. Vypnout a zapnout automatické vyhodnocování regulárních výrazů můžete v .
Pokud použijete funkci, jejíž jeden nebo více argumentů představují řetězce s kritérii vyhledávání, nejprve dojde k pokusu převést tyto řetězce na číslo, například ".0" se převede na 0 a podobně. V případě úspěšného převodu se nebude vyhledávat regulární výraz, ale číslo. Pokud však používáte národní prostředí s jiným oddělovačem desetinných míst, než je tečka, regulární výraz bude fungovat. Chcete-li regulární výraz vždy vynutit místo číselného výrazu, použijte výraz, který nelze interpretovat jako číslo, například ".[0]", ".\0" nebo "(?i).0".
Oblast_pro_průměr – nepovinný. Jedná se o oblast hodnot pro výpočet průměru.
Není-li Oblast_pro_průměr určena, Oblast se použije jak pro výpočet průměru, tak pro vyhledávání podle podmínek. Pokud Oblast_pro_průměr určena je, Oblast se použije pouze pro testování podmínky a Oblast_pro_průměr pro výpočet průměru.
Tato funkce je součástí standardu Open Document Format for Office Applications (OpenDocument) verze 1.2 (ISO/IEC 26300:2-2015).
Pokud je buňka v oblasti hodnot pro výpočet průměru prázdná nebo obsahuje text, funkce AVERAGEIF ji ignoruje.
Pokud je celá tato oblast prázdná, obsahuje pouze text nebo žádná z hodnot oblasti nesplňuje podmínku, funkce vrátí chybu #DIV/0!
Uvažujme následující tabulku
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
Název zboží |
Prodeje |
Tržby |
|
2 |
nůžky |
20 |
65 |
|
3 |
nůž |
35 |
85 |
|
4 |
poznámkový blok |
20 |
190 |
|
5 |
známka |
17 |
180 |
|
6 |
nůž na papír |
ne |
ne |
Ve všech níže uvedených příkladech rozsahy pro výpočet obsahují řádek číslo 6, který je ignorován, protože obsahuje text.
Základní použití
=AVERAGEIF(B2:B6;"<35")
Vypočítá průměr hodnot z oblasti B2:B6, které jsou menší než 35. Vrátí 19, protože při výpočtu není použit druhý řádek.
=AVERAGEIF(B2:B6;"<"&MAX(B2:B6))
Vypočítá průměr hodnot z téže oblasti, které jsou menší než maximální hodnota v této oblasti. Vrátí 19, protože při výpočtu není použita největší hodnota (druhý řádek).
=AVERAGEIF(B2:B6;">"&SMALL(B2:B6;1))
Vypočítá průměr hodnot z téže oblasti, které jsou větší než první nejmenší hodnota v této oblasti. Vrátí 25, protože při výpočtu není použita první nejmenší hodnota (čtvrtý řádek).
Použití Oblasti_pro_průměr
=AVERAGEIF(B2:B6;"<35";C2:C6)
Funkce vyhledá, které hodnoty jsou v oblasti B2:B6 menší než 35, a vypočítá průměr odpovídajících hodnot z oblasti C2:C6. Vrátí 145, protože při výpočtu není použit druhý řádek.
=AVERAGEIF(B2:B6;">"&MIN(B2:B6);C2:C6)
Funkce vyhledá, které hodnoty jsou v oblasti B2:B6 větší než nejmenší hodnota v téže oblasti, a vypočítá průměr odpovídajících hodnot z oblasti C2:C6. Vrátí 113,3, protože při výpočtu není použit čtvrtý řádek (v němž se nachází nejmenší hodnota z oblasti B2:B6).
=AVERAGEIF(B2:B6;"<"&LARGE(B2:B6;2);C2:C6)
Funkce vyhledá, které hodnoty jsou v oblasti B2:B6 menší než druhá největší hodnota v téže oblasti, a vypočítá průměr odpovídajících hodnot z oblasti C2:C6. Vrátí 180, protože při výpočtu je použit pouze čtvrtý řádek.
Použití regulárních výrazů
=AVERAGEIF(A2:A6;"nůž";B2:B6)
Funkce vyhledá, které buňky z oblasti A2:A6 obsahují pouze slovo „nůž“, a vypočítá průměr odpovídajících hodnot z oblasti B2:B6. Vrátí 35, protože při výpočtu je použit pouze druhý řádek. Vyhledává se v oblasti A2:A6, hodnoty jsou však vraceny z oblasti B2:B6.
=AVERAGEIF(A2:A6;"nůž.*";B2:B6)
Funkce vyhledá, které buňky z oblasti A2:A6 začínají na „nůž“ a přitom končí libovolným počtem jiných znaků, a vypočítá průměr odpovídajících hodnot z oblasti B2:B6. Vrátí 27,5, protože nyní splňuje podmínku také řetězec „nůžky“, a při výpočtu tak jsou použity první i druhý řádek.
=AVERAGEIF(A2:A6;".*známk.*";B2:B6)
Funkce vyhledá, které buňky z oblasti A2:A6 obsahují „známk“ a přitom začínají a končí libovolným počtem jiných znaků, a vypočítá průměr odpovídajících hodnot z oblasti B2:B6. Vrátí 18,5, protože při výpočtu jsou použity pouze třetí a čtvrtý řádek.
Odkaz na buňku jako kritérium
Potřebujete-li kritérium snadno změnit, můžete jej zadat do samostatné buňky, na kterou odkážete v podmínce funkce AVERAGEIF.
=AVERAGEIF(A2:A6;".*"&E2&".*";B2:B6)
Funkce vyhledá, které buňky z oblasti A2:A6 obsahují kombinaci znaků zadaných do buňky E2 (přitom se na začátku a na konci řetězce může nacházet libovolný počet jiných znaků), a vypočítá průměr odpovídajících hodnot z oblasti B2:B6. Pokud E2 = známk, funkce vrátí 18,5.
=AVERAGEIF(B2:B6;"<"&E2;C2:C6)
Funkce vyhledá, které buňky z oblasti B2:B6 obsahují hodnotu menší než určenou v buňce E2, a vypočítá průměr odpovídajících hodnot z oblasti C2:C6. Pokud E2 = 35, funkce vrátí 145.