Statistické funkce Excelu pro výpočet průměrů: Výukový program s příklady, část druhá

Excel poskytuje širokou škálu statistických funkcí, které provádějí výpočty od základního průměru, mediánu a režimu až po složitější statistické rozdělení a testy pravděpodobnosti.

V tomto článku se ponoříme do statistických funkcí Excelu pro výpočet průměru.

Upozorňujeme, že některé statistické funkce byly zavedeny v posledních verzích Excelu, a proto nejsou dostupné ve starších verzích.

Funkce pro výpočet průměrů

AVERAGE

Funkce AVERAGE je jednou ze statistických funkcí Excelu. Funkce vrací průměr číselných hodnot zadaných do funkce. Jednoduše řečeno, sečte všechny hodnoty zadané ve funkci, poté je vydělí počtem a vrátí výsledek.

syntax

= AVERAGE(number1,number2,…)

předměty

• numero1 : První číslo, které chcete použít pro výpočet průměru.
• [numero2] : Druhé číslo, které chcete použít pro průměrování.

příklad

Chcete-li vidět, jak funkce funguje AVERAGE podívejme se na příklad:

V prvním příkladu jsme vložili argumenty přímo do funkce.

Ve druhém příkladu jsme odkazovali na rozsah obsahující čísla. Můžete odkazovat na neohraničenou buňku pomocí spojitého rozsahu a pokud chcete odkazovat na dynamický rozsah, můžete k tomu použít tabulku.

Můžete odkazovat na neohraničenou buňku pomocí spojitého rozsahu a pokud chcete odkazovat na dynamický rozsah, můžete použít tabulku.

Ve třetím příkladu jsme odkazovali na rozsah, ve kterém jsou buňky formátovány jako textové hodnoty. V tomto případě můžete tato textová čísla převést na reálná čísla a vypočítat průměr.

Ve čtvrtém příkladu máme před každou hodnotou v každé buňce apostrof, a proto je funkce ignorována.

AVERAGEA

Funkce AVERAGEA aplikace Excel je uvedena v kategorii Statistické funkce aplikace Microsoft Excel. Vrátí průměr zadaných čísel ve funkci, ale na rozdíl od AVERAGE, zachází s booleovskými hodnotami a čísly formátovanými jako text.

syntax

=AVERAGEA(valore1,valore2,…)

předměty

• value1 : Hodnota, která je číslem, logickou hodnotou nebo číslem uloženým jako text.
• [valore2] : Hodnota, která je číslem, logickou hodnotou nebo číslem uloženým jako text.

příklad

Pro pochopení funkce AVERAGEA musíme vidět příklad:

Hodnota vrácená funkcí je 10,17, což je „(0+0+1+10+20+30)/6“.

AVERAGEIF

Funkce AVERAGEIF aplikace Excel je uvedena v kategorii Statistické funkce aplikace Microsoft Excel. Vrátí průměr čísel, která splňují více zadaných podmínek . 

syntax

= AVERAGEIF( range, criteria, [average_range] )

Témata

• range:  pole hodnot (nebo rozsah buněk obsahujících hodnoty), které se mají otestovat podle poskytnutých kritérií.
• criteria:  Podmínka, která má být testována proti každé z hodnot v poskytnutém rozsahu.
• [average_range]:  Volitelné pole číselných hodnot (nebo buněk obsahujících čísla), které by měly být zprůměrovány, pokud odpovídající hodnota v rozsahu splňuje zadaná kritéria.

Pokud téma [average_range] je vynechán, průměr se vypočítá pro hodnoty v počátečním poskytnutém rozsahu.

Uvedená kritéria mohou být:

číselná hodnota (včetně celých čísel, desetinných míst, dat, časů a logických hodnot) (například 10, 01/01/2008, TRUE)
O
textový řetězec (např. „Text“, „Čtvrtek“) – MUSÍ být uveden v uvozovkách
O
výraz (např. „>12“, „<>0“) – MUSÍ být uveden v uvozovkách.
Všimněte si také, že funkce AVERAGEIF Excel nerozlišuje malá a velká písmena. Takže například textové řetězce „TEXT"A"text“ bude vyhodnocen jako rovný.

příklad

Pro pochopení funkce AVERAGEIF musíme to zkusit na příkladu.

Buňky A16-A20 z následující tabulky ukazuje pět příkladů funkce AVERAGEIF aplikace Excel.

Pro každé volání funkce AVERAGEIF Excel, téma range (bude testováno proti criteria) je rozsah buněk A1-A14 a téma [average_range] (obsahující hodnoty, které mají být zprůměrovány) je rozsah buněk B1-B14.

Všimněte si, že v buňkách A16, A18 a A20 výše uvedené tabulky je textová hodnota „Čtvrtek“ a výrazy „>2“ a „<>TRUE“ jsou uzavřeny v uvozovkách. To je nezbytné pro všechny texty nebo výrazy.

AVERAGEIFS

Funkce AVERAGEIFS aplikace Excel je uvedena v kategorii Statistické funkce aplikace Microsoft Excel. Vrátí průměr čísel, která splňují více zadaných podmínek . Na rozdíl od AVERAGEIF, můžete nastavit více podmínek a vypočítat průměr pouze pro čísla, která splňují všechny podmínky.

syntax

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Témata

• average_range:  Pole číselných hodnot (nebo buněk obsahujících čísla), které mají být zprůměrovány.
• criteria_range1, [criteria_range2], …: Pole hodnot (nebo rozsahy buněk obsahujících hodnoty), které se mají vzájemně testovat criteria1, criteria2, … (Pole criteria_range dodané musí mít všechny stejnou délku).
• criteria1, [criteria2], …: Podmínky, které mají být testovány s ohledem na hodnoty v criteria_range1, [criteria_range2], …

příklad

Nyní se podívejme na příklad funkce AVERAGEIFS:

V následujícím příkladu jsme použili funkci AVERAGEIFS pro výpočet průměrného množství prodaného prodejcem „Pietro“ a pro produkt „B“. Kritéria jsme zadali přímo do funkce a máme dva záznamy Petrova prodeje produktu B.

V následujícím příkladu jsme použili AVERAGEIFS s hvězdičkou pro výpočet průměrné ceny ovoce, jehož množství je větší než 20 jednotek a má B v názvu.

V níže uvedených datech máme dvě ovoce, která tato kritéria splňují.

MEDIAN

Funkce MEDIAN Excel vrátí statistický medián (průměrnou hodnotu) seznamu zadaných čísel.

syntax

= MEDIAN( number1, [number2], ... )

Témata

číselné argumenty jsou sada jedné nebo více číselných hodnot (nebo polí číselných hodnot), pro které chcete vypočítat medián

Všimněte si, že:

• Pokud je v dané datové sadě sudý počet hodnot, vrátí se průměr dvou průměrných hodnot;
• Pokud dodané pole obsahuje prázdné buňky, text nebo logické hodnoty, jsou tyto hodnoty při výpočtu mediánu ignorovány.
• V aktuálních verzích aplikace Excel (Excel 2007 a novější) můžete do funkce Medián zadat až 255 číselných argumentů, ale v aplikaci Excel 2003 může tato funkce přijmout pouze 30 číselných argumentů. Každý z číselných argumentů však může být polem mnoha hodnot.

příklad

Následující tabulka ukazuje tři příklady funkce Median:

Zvažte, že v předchozích příkladech:

• Příklad v buňce B2 obdrží sudý počet hodnot, a proto se medián vypočítá jako průměr dvou středních hodnot, 8 a 9;
• Příklad v buňce B3 zahrnuje prázdnou buňku A8. Tato buňka je při výpočtu mediánu ignorována.

Další podrobnosti o funkci MEDIAN Excel, viz Web Microsoft Office .

MODE

Funkce MODE aplikace Excel vrátí MODE statistika (nejčastější hodnota) seznamu dodaných čísel. Pokud jsou v dodaných datech 2 nebo více opakujících se hodnot, funkce vrátí nejnižší hodnotu z nich

syntax

= MODE( number1, [number2], ... )

Témata

jsou množinou jedné nebo více číselných hodnot (nebo polí číselných hodnot), pro které chcete vypočítat MODE statistika.

Nota:

• V aktuálních verzích aplikace Excel (Excel 2007 a novější) můžete funkci zadat až 255 číselných argumentů. MODE, ale v aplikaci Excel 2003 může funkce přijmout pouze 30 číselných argumentů.
• Text a logické hodnoty v rámci zadaného pole čísel jsou funkcí ignorovány Mode.

Příklady funkcí MODE

Příklad 1

Následující tabulka ukazuje funkci MODE Excel, který se používá k výpočtu MODE statistiky sady hodnot v buňkách A1-A6.

Příklad 2

Následující tabulka ukazuje funkci MODE, který se používá k výpočtu MODE statistiky sady hodnot v buňkách A1-A10.

Všimněte si, že v tomto případě jsou dva mode v datech.

Ve výše uvedeném případě, kdy data ve sloupci A předchozí tabulky mají dvě MODE statistika (3 a 4), funkce MODE vrátí nižší z těchto dvou hodnot.

Další podrobnosti a příklady funkcí MODE Excel, viz Web Microsoft Office .

MODE.SNGL

Funkce MODE.SNGL aplikace Excel vrátí MODE statistika (nejčastější hodnota) seznamu dodaných čísel. Pokud jsou v dodaných datech 2 nebo více opakujících se hodnot, funkce vrátí nejnižší hodnotu z nich.

Funkce Mode.Sngl je v Excelu 2010 novinkou, a proto není k dispozici v dřívějších verzích Excelu. Funkce je však jednoduše přejmenovanou verzí funkce MODE dostupné v dřívějších verzích Excelu.

syntax

= MODE.SNGL( number1, [number2], ... )

Témata

jsou množinou jedné nebo více číselných hodnot (nebo polí číselných hodnot), pro které chcete vypočítat MODE.SNGL statistika.

Příklady funkcí MODE.SNGL

Příklad 1

Následující tabulka ukazuje funkci MODE.SNGL Excel, který se používá k výpočtu statistického MODE sady hodnot v buňkách A1-A6.

Příklad 2

Následující tabulka ukazuje funkci MODE.SNGL, sloužící k výpočtu statistického režimu množiny hodnot v buňkách A1-A10.

Všimněte si, že v tomto případě jsou dva mode v datech.

Ve výše uvedeném případě, kdy data ve sloupci A předchozí tabulky mají dvě MODE statistika (3 a 4), funkce MODE.SNGL vrátí nižší z těchto dvou hodnot.

Další podrobnosti a příklady funkcí MODE.SNGL Excel, viz Web Microsoft Office .

GEOMEAN

Geometrický průměr je míra průměru, která udává typickou hodnotu sady čísel. Toto měření lze použít pouze pro kladné hodnoty.

Geometrický průměr množiny hodnot y ₁ A ₂ , …, tam _n počítá se podle vzorce:

Všimněte si, že geometrický průměr je vždy menší nebo roven aritmetickému průměru.

Funkce Geomean Excel vypočítá geometrický průměr daného souboru hodnot.

syntax

= GEOMEAN( number1, [number2], ... )

Témata

jednu nebo více kladných číselných hodnot (nebo polí číselných hodnot), pro které chcete vypočítat geometrický průměr.

V aktuálních verzích aplikace Excel (Excel 2007 a novější) může funkce přijmout až 255 číselných argumentů, ale v aplikaci Excel 2003 může funkce přijmout pouze 30 číselných argumentů. Každý argument však může být pole hodnot nebo rozsah buněk, z nichž každá může obsahovat mnoho hodnot.

příklad

Buňka B1 tabulky ukazuje jednoduchý příklad funkce geomean v Excelu slouží k výpočtu geometrického průměru hodnot v buňkách A1-A5.

V tomto příkladu vrací funkce Geomean hodnotu 1.622671112 .

HARMEAN

Harmonický průměr je mírou průměru vypočteného jako převrácená hodnota aritmetického průměru převrácených hodnot. To lze vypočítat pouze pro kladné hodnoty.

Harmonický průměr množiny hodnot y1, y2, ..., yn je tedy dán vzorcem:

harmonický průměr je vždy menší nebo roven geometrickému průměru a geometrický průměr je vždy menší nebo roven aritmetickému průměru.

Funkce Harmean Excel vypočítá harmonický průměr daného souboru hodnot.

syntax

= HARMEAN( number1, [number2], ... )

Témata

jednu nebo více kladných číselných hodnot (nebo polí číselných hodnot), pro které chcete vypočítat harmonický průměr.

V aktuálních verzích aplikace Excel (Excel 2007 a novější) může funkce přijmout až 255 číselných argumentů, ale v aplikaci Excel 2003 může funkce přijmout pouze 30 číselných argumentů. Každý argument však může být pole hodnot nebo rozsah buněk, z nichž každá může obsahovat mnoho hodnot.

příklad

Buňka B1 v tabulce vpravo ukazuje jednoduchý příklad funkce Harmean v Excelu slouží k výpočtu harmonického průměru hodnot v buňkách A1-A5.

V tomto příkladu funkce Harmean vrátí hodnotu 1.229508197.

TRIMMEAN

Funkce TRIMMEAN (také známý jako oříznutý průměr) je míra průměru, která označuje centrální tendenci souboru hodnot.

Oříznutý průměr se vypočítá vyřazením některých hodnot na koncích rozsahu hodnot před výpočtem aritmetického průměru zbývajících hodnot. Tím se zabrání zkreslení vypočítaného průměru extrémními hodnotami (technicky také známými jako odlehlé hodnoty outliers).

syntax

= TRIMMEAN( array, percent )

Témata

• řada – Pole číselných hodnot, pro které chcete vypočítat zkrácený průměr.
• procent – Procento hodnot, ze kterých chcete smazatarray pokud.

Všimněte si, že zadaná procentuální hodnota je celkové procento hodnot, které mají být vyloučeny z výpočtu. Toto procento se vydělí dvěma, aby se získal počet hodnot odstraněných z každého konce rozsahu.

Je třeba také poznamenat, že když Excel vypočítá, kolik hodnot bylo odstraněno zarray z poskytnutých hodnot se vypočítané procento zaokrouhlí dolů na nejbližší násobek 2. Například, pokud chcete vypočítat oříznutý průměr a array z 10 hodnot, proto:

• Procento 15 % odpovídá 1,5 hodnotám, které budou zaokrouhleny dolů na 0 (tj. žádné hodnoty nebudou vyřazeny zarray před výpočtem průměru);
• Procento 20 % odpovídá 2 hodnotám, takže před zprůměrováním zbývajících hodnot bude z každého konce rozsahu vyřazena 1 hodnota;
• Procento 25 % odpovídá 2,5 hodnotám, které budou zaokrouhleny dolů na 2 (to znamená, že před zprůměrováním zbývajících hodnot bude z každého konce rozsahu vyřazena 1 hodnota).

příklad

Buňky B1-B3 v tabulce níže ukažte 3 příklady funkce trimmean v Excelu se všechny používají k výpočtu oříznutého průměru hodnot v buňkách A1-A10, pro různé procentuální hodnoty.

Mějte na paměti, že v cele B1 z výše uvedené tabulky je daný procentuální argument 15 %. Protože varray pokud existuje 10 hodnot, počet hodnot k ignorování je 1,5 zaokrouhlený dolů na nejbližší násobek 2, což je nula.

Funkce pro výpočet permutací

PERMUT

Počet permutací pro daný počet objektů je počet kombinací v libovolném možném pořadí.

Permutace se liší od kombinací v tom, že u permutace záleží na pořadí objektů, ale v kombinaci na pořadí nezáleží.

Počet možných permutací je dán vzorcem:

holubice k je počet vybraných objektů e n je počet možných objektů.

Funkce Excel Permut vypočítá počet permutací zadaného počtu objektů ze sady objektů.

syntax

= PERMUT( number, number_chosen )

Témata

• number: Celkový počet dostupných položek
• number_chosen: Počet objektů v každé permutaci (tj. počet objektů vybraných ze sady)

Všimněte si, že pokud jsou některé argumenty zadány jako desítkové hodnoty, budou funkcí zkráceny na celá čísla Permut.

příklad

V následující tabulce je Excel Permut se používá k výpočtu počtu permutací šesti objektů, vybraných ze sad různých velikostí:

PERMUTATIONA

Excel funkce Výměna a Permutationa oba vypočítají počet permutací výběru objektů ze sady.

Tyto dvě funkce se však liší tím, že Permut. funkce nepočítá opakování, zatímco funkce Permutationa počítá opakování.

Například v sadě 3 objektů a , b , c , kolik permutací mají 2 objekty?

• La Permut. funkce vrátí výsledek 6 (permutace: ab , ac , ba , bc , ca , cb );
• Funkce Permutationa vrací výsledek 9 (permutace: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Funkce Excel Permutationa vypočítá počet permutací zadaného počtu objektů ze sady objektů.

syntax

= PERMUTATIONA( number, number_chosen )

Témata

• number: Celkový počet objektů v sadě (musí být ≥ 0).
• number_chosen: Počet objektů vybraných ze sady (musí být ≥ 0).

Všimněte si, že pokud jsou některé argumenty zadány jako desítkové hodnoty, budou funkcí zkráceny na celá čísla PERMUTATIONA.

příklad

V následující tabulce je Excel PERMUTATIONA se používá k výpočtu počtu permutací šesti objektů, vybraných ze sad různých velikostí:

Funkce pro výpočet intervalů spolehlivosti

CONFIDENCE

V Excelu 2010 je funkce CONFIDENCE byla nahrazena funkcí Confidence.Norm.

Přestože byla nahrazena, aktuální verze aplikace Excel tuto funkci stále mají Confidence (uloženo v seznamu funkcí kompatibility), aby byla umožněna kompatibilita s předchozími verzemi aplikace Excel.

Nicméně funkce Confidence nemusí být k dispozici v budoucích verzích Excelu, proto doporučujeme tuto funkci používat Confidence.Norm, Pokud možno.

Funkce Confidence Excel používá normální rozdělení k výpočtu hodnoty spolehlivosti, kterou lze použít ke konstrukci intervalu spolehlivosti pro průměr populace, danou pravděpodobnost a velikost vzorku. Předpokládá se, že standardní odchylka populace je známa.

syntax

= CONFIDENCE( alpha, standard_dev, size )

Témata

• alfa: Hladina významnosti (= 1 – hladina spolehlivosti). (Například hladina významnosti 0,05 odpovídá 95% hladině spolehlivosti).
• standard_dev: Směrodatná odchylka populace.
• size: Velikost vzorku populace.

Aby bylo možné vypočítat interval spolehlivosti pro průměr populace, musí být vrácená hodnota spolehlivosti přidána a odečtena od průměru vzorku. Co znamená. pro průměr vzorku x:

Confidence Interval =   x   ±   CONFIDENCE

příklad

V tabulce níže je funkce spolehlivosti Excel použita k výpočtu intervalu spolehlivosti s významností 0,05 (tj. 95% hladina spolehlivosti) pro průměr vzorku 100 mužských výšek. Průměr vzorku je 1,8 metru a standardní odchylka je 0,07 metru.

Předchozí funkce vrací hodnotu spolehlivosti 0,013719748

Proto je interval spolehlivosti 1,8 ± 0,013719748, což je ekvivalentní rozsahu mezi 1,786280252 a 1,813719748

CONFIDENCE.NORM

Ve statistice je interval spolehlivosti rozsah, do kterého pravděpodobně bude spadat parametr populace pro danou pravděpodobnost.

Například. Pro danou populaci a 95% pravděpodobnost je interval spolehlivosti rozsah, do kterého parametr populace s 95% pravděpodobností spadne.

Všimněte si, že přesnost intervalu spolehlivosti závisí na tom, zda má populace normální rozdělení.

Funkce Confidence.Norm Excel používá normální rozdělení k výpočtu hodnoty spolehlivosti, kterou lze použít ke konstrukci intervalu spolehlivosti pro průměr populace, danou pravděpodobnost a velikost vzorku. Předpokládá se, že standardní odchylka populace je známa.

syntax

= CONFIDENCE.NORM( alpha, standard_dev, size )

Témata

• alfa: Hladina významnosti (= 1 – hladina spolehlivosti). (Například hladina významnosti 0,05 odpovídá 95% hladině spolehlivosti).
• standard_dev: Směrodatná odchylka populace.
• size: Velikost vzorku populace.

Aby bylo možné vypočítat interval spolehlivosti pro průměr populace, musí být vrácená hodnota spolehlivosti přidána a odečtena od průměru vzorku. Co znamená. pro průměr vzorku x:

Confidence Interval =   x   ±   CONFIDENCE

příklad

V tabulce níže je funkce spolehlivosti Excel použita k výpočtu intervalu spolehlivosti s významností 0,05 (tj. 95% hladina spolehlivosti) pro průměr vzorku 100 mužských výšek. Průměr vzorku je 1,8 metru a standardní odchylka je 0,07 metru.

Předchozí funkce vrací hodnotu spolehlivosti 0,013719748

Proto je interval spolehlivosti 1,8 ± 0,013719748, což je ekvivalentní rozsahu mezi 1,786280252 a 1,813719748

Ercole Palmeri