Funkce AVERAGEIF – Podpora Microsoftu

Platí pro Excel pro Microsoft 365, Excel pro Microsoft 365 pro Mac, Excel pro web, Excel 2024, Excel 2024 pro Mac, Excel 2021, Excel 2021 pro Mac, Excel 2019, Excel 2016, Excel Web App, Microsoft365.com, Můj Office pro iPhone

Tento článek popisuje syntaxi a použití vzorců. PARAMETR AVERAGEIFFunkce . v aplikaci Microsoft Excel.

popis

Vrátí průměrnou hodnotu (aritmetický průměr) všech buněk v oblasti, které splňují zadanou podmínku.

syntax

AVERAGEIF(rozsah; podmínky; [rozsah_průměru])

Argumenty funkce AVERAGEIF jsou uvedeny níže.

• Rozsah. Povinné. Jedna nebo více buněk pro výpočet průměru obsahujících čísla nebo názvy, pole nebo odkazy obsahující čísla.
• Stav Povinné. Podmínka ve formě čísla, výrazu, odkazu na buňku nebo textu, která určuje buňky použité při výpočtu průměru. Podmínka by mohla být vyjádřena například jako 32, „32“, „>32“, „jablka“ nebo B4.
• Rozsah_průměrování. Volitelné. Skutečná sada buněk, ze které se má vypočítat průměr. Pokud není zadán, použije se rozsah.

Poznámky

• Buňky v rozsahu, které obsahují hodnoty TRUE nebo FALSE, jsou ignorovány.
• Pokud je buňka v „průměrném_rozsahu“ prázdná, funkce AVERAGEIF ji ignoruje.
• Pokud je rozsah prázdný nebo je to textová hodnota, funkce AVERAGEIF vrátí #DIV0! (chybovou hodnotu).
• Pokud je buňka v podmínce prázdná, funkce AVERAGEIF s ní zachází, jako by to byla buňka s hodnotou 0.
• Pokud žádné buňky v rozsahu nesplňují podmínky, funkce AVERAGEIF vrátí chybovou hodnotu #DIV/0!.
• Tento argument akceptuje zástupné znaky otazník (?) a hvězdičku (*). Otazník odpovídá libovolnému jednotlivému znaku; hvězdička odpovídá jakékoli posloupnosti znaků. Pokud chcete, aby se shodoval samotný otazník nebo hvězdička, uveďte před ně vlnovku (~).
• Hodnota average_range nemusí mít stejnou velikost a tvar jako rozsah. Při určování skutečných buněk k průměrování se jako počáteční buňka použije levá horní buňka v average_range a poté se přidají buňky stejné velikosti a tvaru. Například:

Pokud je rozsah

A „průměrovací_rozsah“

Zpracované buňky

Poznámka: Funkce AVERAGEIF měří průměr neboli střed sady čísel ve statistickém rozdělení. Existují tři běžné způsoby, jak průměr určit:

• Znamenat — je aritmetický průměr, který se vypočítá sečtením sady čísel a následným vydělením výsledného součtu jejich číslem. Například průměr čísel 2, 3, 3, 5, 7 a 10 je 5, což je výsledek vydělení jejich součtu 30 jejich číslem 6.
• Medián — je číslo, které je středem množiny čísel, tj. polovina čísel má hodnoty větší než medián a polovina čísel má hodnoty menší než medián. Například medián pro čísla 2, 3, 3, 5, 7 a 10 je 4.
• Móda — je číslo, které se v dané číselné sadě vyskytuje nejčastěji. Například modus čísel 2, 3, 3, 5, 7 a 10 je 3.

Pokud je rozdělení množiny čísel symetrické, všechny tři hodnoty centrální tendence se budou shodovat. Pokud je rozdělení množiny čísel zkreslené, hodnoty se mohou lišit.

Příklady

Zkopírujte vzorová data z následující tabulky a vložte je do buňky A1 nového listu aplikace Excel. Chcete-li zobrazit výsledky vzorců, vyberte je a stiskněte klávesu F2 a poté stiskněte klávesu Enter. V případě potřeby změňte šířku sloupců, abyste viděli všechna data.

Hodnota majetku

Průměrný plat. Průměrná délka života. Téměř každý den slýcháme tyto fráze používané k popisu množiny s jedním jediným číslem. Ale kupodivu je „průměrná hodnota“ poněkud zákeřný pojem, který často klame průměrného člověka, nezkušeného v matematických statistikách.

Jaký je problém?

Průměrnou hodnotou se nejčastěji rozumí aritmetický průměr, který se pod vlivem jednotlivých skutečností nebo událostí značně mění. A nezískáte skutečnou představu o tom, jak přesně jsou distribuovány hodnoty, které studujete.

Podívejme se na klasický příklad průměrné mzdy.

Nějaká abstraktní společnost má deset zaměstnanců. Devět z nich pobírá plat asi 50 000 rublů a jeden dostává 1 500 000 rublů (zvláštní shodou okolností je zároveň generálním ředitelem této společnosti).

Průměrná hodnota v tomto případě bude 195 150 rublů, což, jak budete souhlasit, je nesprávné.

Jaké metody výpočtu průměru existují?

Prvním způsobem je výpočet již zmíněného aritmetický průměr, což je součet všech hodnot dělený jejich počtem.

• x – aritmetický průměr;
• x_n – specifický význam;
• n – počet hodnot.

• Funguje dobře s normálním rozložením hodnot ve vzorku;
• Snadno vypočítat;
• Intuitivně jasné.

• Nedává skutečnou představu o rozložení hodnot;
• Nestálá veličina, která snadno podléhá odlehlým hodnotám (jako v případě generálního ředitele).

Druhým způsobem je výpočet Móda, tedy nejčastěji se vyskytující hodnotu.

• M – móda;
• x – spodní hranice intervalu, který obsahuje režim;
• n – intervalová hodnota;
• f_m– frekvence (kolikrát se určitá hodnota vyskytuje v řadě);
• f_m-1 – četnost intervalu předcházejícího modálnímu;
• f_m+1 – četnost intervalu následujícího po modálním.

• Skvělé pro získání pocitu veřejného mínění;
• Dobré pro nečíselné údaje (barvy sezóny, nejprodávanější zboží, hodnocení);
• Snadno pochopitelné.

• Móda možná prostě neexistuje (žádné opakování);
• Může existovat několik režimů (multimodální distribuce).

Třetím způsobem je výpočet mediány, tedy hodnota, která rozděluje uspořádaný vzorek na dvě poloviny a leží mezi nimi. A pokud taková hodnota neexistuje, pak se jako medián bere aritmetický průměr mezi hranicemi polovin vzorku.

• M_e – medián;
• x – spodní hranice intervalu, který obsahuje medián;
• h – intervalová hodnota;
• f _i – frekvence (kolikrát se určitá hodnota vyskytuje v řadě);
• S_m-1 – součet četností intervalů předcházejících mediánu;
• f_m – počet hodnot ve středním intervalu (jeho četnost).

• Poskytuje nejrealističtější a nejreprezentativnější odhad;
• Odolný vůči emisím.

• Náročnější na výpočet, protože vzorek musí být objednán před výpočtem.

Podívali jsme se na hlavní metody pro zjištění průměrné hodnoty, tzv opatření centrální tendence (ve skutečnosti je jich více, ale tyto jsou nejoblíbenější).

Nyní se vraťme k našemu příkladu a vypočítejme všechny tři možnosti průměru pomocí speciálních funkcí Excelu:

• AVERAGE(číslo1;[číslo2];…) – funkce pro určení aritmetického průměru;
• MODE.ONE(číslo1,[číslo2];. ) – funkce režimu (ve starších verzích Excelu se používal MODE(číslo1,[číslo2];. ));
• MEDIAN(číslo1;[číslo2];. ) – funkce pro zjištění mediánu.

A zde jsou hodnoty, které máme:

Režim a medián v tomto případě mnohem lépe charakterizují průměrnou mzdu ve firmě.

Co ale dělat, když vzorek neobsahuje 10 hodnot jako v příkladu, ale miliony? V Excelu to nelze spočítat, ale v databázi, kde jsou vaše data uložena, žádný problém.

Výpočet aritmetického průměru v SQL

Vše je zde velmi jednoduché, protože SQL poskytuje speciální agregační funkci AVG.

A chcete-li jej použít, stačí napsat následující dotaz:

/* Zde a pod plat je sloupec s platy a zaměstnanci jsou tabulka zaměstnanců v naší databázi */ VYBERTE AVG(plat) AS 'Průměrný plat' OD zaměstnanců

Výpočet módy v SQL

V SQL neexistuje žádná samostatná funkce pro nalezení režimu, ale můžete si ji rychle a snadno napsat sami. K tomu musíme zjistit, který plat se nejčastěji opakuje a vybrat ten nejoblíbenější.

/* S VAZBAMI musí být přidáno k TOP(), pokud je sada multimodální, to znamená, že sada má několik režimů */ VYBERTE TOP(1) S VAZBAMI plat JAKO 'Režim platu' OD zaměstnanců SESKUPIT PODLE platu ORDER BY COUNT(* ) DESC

Výpočet mediánu v SQL

Stejně jako režim nemá SQL vestavěnou funkci pro výpočet mediánu, ale má obecnou funkci pro výpočet percentilů, PERCENTILE_CONT .

Všechno to vypadá takto:

/* V tomto případě je percentil 0.5 a bude mediánem */ VYBERTE TOP(1) PERCENTILE_CONT(0.5) V RÁMCI SKUPINY (POŘADÍ PODLE platu) NAD() JAKO 'střední plat' OD zaměstnanců

Více o fungování funkce PERCENTILE_CONT je lepší si přečíst v nápovědě Microsoft a Google BigQuery.

Jakou metodu mám použít?

Z výše uvedeného vyplývá, že medián je nejlepší způsob, jak vypočítat průměr.

Ale není tomu tak vždy. Pokud pracujete s průměrem, pak si dejte pozor na multimodální distribuci:

Graf ukazuje bimodální rozdělení se dvěma píky. Tato situace může nastat například při hlasování ve volbách.

Aritmetický průměr a medián jsou v tomto případě hodnoty, které jsou někde uprostřed a neřeknou nic o tom, co se vlastně děje, a je lepší okamžitě rozpoznat, že máte co do činění s bimodální distribucí nahlášením dvou režimů.

Ještě lépe, rozdělte vzorek do dvou skupin a pro každou shromážděte statistická data.

Závěr:

Při výběru metody pro zjištění průměru je třeba vzít v úvahu přítomnost odlehlých hodnot a také normalitu rozložení hodnot ve vzorku.

Konečná volba míry centrální tendence je vždy na analytikovi.

Užitečné odkazy:

• SQL a teorie pravděpodobnosti (YouTube)
• Analýza normality distribuce dat (YouTube)
• Míry centrální tendence

Roman Romančuk

Datový analytik s více než 10 lety zkušeností v ruských a mezinárodních fintech projektech, včetně Sravni, Xsolla a T-Bank. Během této doby se vypracoval z obyčejného analytika na ředitele, ponořil se do vývoje strategií, optimalizace metrik a řešení složitých obchodních problémů.

• Matice kompetencí analytika dat – 04.12.2024. XNUMX. XNUMX
• Jak správně organizovat práci s hypotézami? — 21.11.2023. XNUMX. XNUMX
• Případ: jak vybudovat analytické oddělení ve velké společnosti? — 06.05.2023