Štatistické funkcie Excelu na výpočet priemerov: Výukový program s príkladmi, časť druhá

Excel poskytuje širokú škálu štatistických funkcií, ktoré vykonávajú výpočty od základného priemeru, mediánu a režimu až po zložitejšie štatistické rozdelenia a testy pravdepodobnosti.

V tomto článku sa ponoríme do štatistických funkcií Excelu na výpočet priemeru.

Upozorňujeme, že niektoré štatistické funkcie boli zavedené v najnovších verziách Excelu, a preto nie sú dostupné v starších verziách.

Funkcie na výpočet priemerov

AVERAGE

Funkcia AVERAGE je jednou zo štatistických funkcií Excelu. Funkcia vráti priemer číselných hodnôt zadaných do funkcie. Jednoducho povedané, pridá všetky hodnoty špecifikované vo funkcii, potom ich vydelí počtom a vráti výsledok.

syntax

= AVERAGE(number1,number2,…)

predmetov

• numero1 : Prvé číslo, ktoré chcete použiť na výpočet priemeru.
• [numero2] : Druhé číslo, ktoré chcete použiť na spriemerovanie.

príklad

Ak chcete vidieť, ako funkcia funguje AVERAGE pozrime sa na príklad:

V prvom príklade sme argumenty vložili priamo do funkcie.

V druhom príklade sme odkazovali na rozsah obsahujúci čísla. Môžete odkazovať na neohraničenú bunku pomocou súvislého rozsahu a ak chcete odkazovať na dynamický rozsah, môžete na to použiť tabuľku.

Môžete odkazovať na neohraničenú bunku pomocou súvislého rozsahu a ak chcete odkazovať na dynamický rozsah, môžete použiť tabuľku.

V treťom príklade sme hovorili o rozsahu, v ktorom sú bunky formátované ako textové hodnoty. V tomto prípade môžete tieto textové čísla previesť na reálne čísla a vypočítať tak priemer.

Vo štvrtom príklade máme apostrof pred každou hodnotou v každej bunke, a preto ho funkcia ignoruje.

AVERAGEA

Funkcia AVERAGEA programu Excel je uvedený v kategórii Štatistické funkcie programu Microsoft Excel. Vráti priemer zadaných čísel vo funkcii, ale na rozdiel od AVERAGE, zaobchádza s boolovskými hodnotami a číslami naformátovanými ako s textom.

syntax

=AVERAGEA(valore1,valore2,…)

predmetov

• value1 : Hodnota, ktorá je číslom, logickou hodnotou alebo číslom uloženým ako text.
• [valore2] : Hodnota, ktorá je číslom, logickou hodnotou alebo číslom uloženým ako text.

príklad

Aby ste pochopili funkciu AVERAGEA musíme vidieť príklad:

Hodnota vrátená funkciou je 10,17, čo je „(0+0+1+10+20+30)/6“.

AVERAGEIF

Funkcia AVERAGEIF programu Excel je uvedený v kategórii Štatistické funkcie programu Microsoft Excel. Vráti priemer čísel, ktoré spĺňajú viaceré zadané podmienky . 

syntax

= AVERAGEIF( range, criteria, [average_range] )

témy

• range:  pole hodnôt (alebo rozsah buniek obsahujúcich hodnoty) na testovanie podľa poskytnutých kritérií.
• criteria:  Podmienka, ktorá sa má testovať proti každej z hodnôt v poskytnutom rozsahu.
• [average_range]:  Voliteľné pole číselných hodnôt (alebo buniek obsahujúcich čísla), ktoré by sa mali spriemerovať, ak zodpovedajúca hodnota v rozsahu spĺňa poskytnuté kritériá.

Ak téma [average_range] sa vynechá, vypočíta sa priemer pre hodnoty v počiatočnom poskytnutom rozsahu.

Poskytnuté kritériá môžu byť:

číselná hodnota (vrátane celých čísel, desatinných miest, dátumov, časov a logických hodnôt) (napríklad 10, 01/01/2008, TRUE)
O
textový reťazec (napr. „Text“, „Štvrtok“) – MUSÍ byť uvedený v úvodzovkách
O
výraz (napr. „>12“, „<>0“) – MUSÍ byť uvedený v úvodzovkách.
Všimnite si tiež, že funkcia AVERAGEIF Excel nerozlišuje veľké a malé písmená. Takže napríklad textové reťazce „TEXT"A"text“ bude vyhodnotený ako rovný.

príklad

Aby ste pochopili funkciu AVERAGEIF musíme si to vyskúšať na príklade.

Bunky A16-A20 nasledujúcej tabuľky uvádza päť príkladov funkcie AVERAGEIF programu Excel.

Pre každé volanie funkcie AVERAGEIF Excel, téma range (na testovanie proti criteria) je rozsah buniek A1-A14 a tému [average_range] (obsahuje hodnoty, ktoré sa majú spriemerovať) je rozsah buniek B1-B14.

Všimnite si, že v bunkách A16, A18 a A20 vyššie uvedenej tabuľky je textová hodnota „Štvrtok“ a výrazy „>2“ a „<>TRUE“ sú uzavreté v úvodzovkách. Toto je nevyhnutné pre všetky texty alebo výrazy.

AVERAGEIFS

Funkcia AVERAGEIFS programu Excel je uvedený v kategórii Štatistické funkcie programu Microsoft Excel. Vráti priemer čísel, ktoré spĺňajú viaceré zadané podmienky . Na rozdiel od AVERAGEIF, môžete nastaviť viacero podmienok a vypočítať priemer len pre čísla, ktoré spĺňajú všetky podmienky.

syntax

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

témy

• average_range:  Pole číselných hodnôt (alebo buniek obsahujúcich čísla), ktoré sa majú spriemerovať.
• criteria_range1, [criteria_range2], …: Polia hodnôt (alebo rozsahy buniek obsahujúcich hodnoty), ktoré sa majú navzájom otestovať criteria1, criteria2, … (Polia criteria_range dodané musia mať rovnakú dĺžku).
• criteria1, [criteria2], …: Podmienky, ktoré sa majú testovať s ohľadom na hodnoty v criteria_range1, [criteria_range2], …

príklad

Teraz sa pozrime na príklad funkcie AVERAGEIFS:

V nasledujúcom príklade sme použili funkciu AVERAGEIFS na výpočet priemerného množstva predávaného predávajúcim „Pietro“ a za produkt „B“. Kritériá sme zadali priamo do funkcie a máme dva záznamy o Petrovom predaji produktu B.

V nasledujúcom príklade sme použili AVERAGEIFS s hviezdičkou na výpočet priemernej ceny ovocia, ktorého množstvo je väčšie ako 20 jednotiek a má B v názve.

V nižšie uvedených údajoch máme dva druhy ovocia, ktoré spĺňajú tieto kritériá.

MEDIAN

Funkcia MEDIAN Excel vráti štatistický medián (priemernú hodnotu) zoznamu zadaných čísel.

syntax

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

témy

číselné argumenty sú množinou jednej alebo viacerých číselných hodnôt (alebo polí číselných hodnôt), pre ktoré chcete vypočítať medián

Poznač si to:

• Ak je v danom súbore údajov párny počet hodnôt, vráti sa priemer dvoch priemerných hodnôt;
• Ak dodané pole obsahuje prázdne bunky, text alebo logické hodnoty, tieto hodnoty sa pri výpočte mediánu ignorujú.
• V aktuálnych verziách Excelu (Excel 2007 a novšie) môžete do funkcie Medián zadať až 255 číselných argumentov, ale v Exceli 2003 môže funkcia prijať iba 30 číselných argumentov. Každý z číselných argumentov však môže predstavovať pole mnohých hodnôt.

príklad

Nasledujúca tabuľka zobrazuje tri príklady funkcie Median:

Zvážte, že v predchádzajúcich príkladoch:

• Príklad v bunke B2 dostane párny počet hodnôt, a preto sa medián vypočíta ako priemer dvoch stredných hodnôt, 8 a 9;
• Príklad v bunke B3 zahŕňa prázdnu bunku A8. Táto bunka sa pri výpočte mediánu ignoruje.

Pre viac podrobností o funkcii MEDIAN Excel, pozri Webová stránka Microsoft Office .

MODE

Funkcia MODE programu Excel vráti MODE štatistika (najčastejšia hodnota) zoznamu dodaných čísel. Ak sú v zadaných údajoch 2 alebo viac opakujúcich sa hodnôt, funkcia vráti najnižšiu hodnotu spomedzi nich

syntax

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

témy

sú množinou jednej alebo viacerých číselných hodnôt (alebo polí číselných hodnôt), pre ktoré chcete vypočítať MODE štatistiky.

Poznámka:

• V aktuálnych verziách Excelu (Excel 2007 a novších) môžete do funkcie zadať až 255 číselných argumentov MODE, ale v Exceli 2003 môže funkcia akceptovať iba 30 číselných argumentov.
• Funkcia ignoruje text a logické hodnoty v rámci zadaného poľa čísel Mode.

Príklady funkcií MODE

Príklad 1

Funkciu zobrazuje nasledujúca tabuľka MODE Excel, ktorý sa používa na výpočet MODE štatistiky množiny hodnôt v bunkách A1-A6.

Príklad 2

Funkciu zobrazuje nasledujúca tabuľka MODE, ktorý sa používa na výpočet MODE štatistiky množiny hodnôt v bunkách A1-A10.

Všimnite si, že v tomto prípade sú dve mode v údajoch.

Vo vyššie uvedenom prípade, kde údaje v stĺpci A predchádzajúcej tabuľky majú dva MODE štatistika (3 a 4), funkcia MODE vráti nižšiu z týchto dvoch hodnôt.

Ďalšie podrobnosti a príklady funkcií MODE Excel, pozri Webová stránka Microsoft Office .

MODE.SNGL

Funkcia MODE.SNGL programu Excel vráti MODE štatistika (najčastejšia hodnota) zoznamu dodaných čísel. Ak sú v zadaných údajoch 2 alebo viac opakujúcich sa hodnôt, funkcia vráti najnižšiu hodnotu spomedzi nich.

Funkcia Mode.Sngl je novinka v Exceli 2010, a preto nie je dostupná v starších verziách Excelu. Funkcia je však jednoducho premenovaná verzia funkcie MODE dostupné v starších verziách Excelu.

syntax

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

témy

sú množinou jednej alebo viacerých číselných hodnôt (alebo polí číselných hodnôt), pre ktoré chcete vypočítať MODE.SNGL štatistiky.

Príklady funkcií MODE.SNGL

Príklad 1

Funkciu zobrazuje nasledujúca tabuľka MODE.SNGL Excel, ktorý sa používa na výpočet štatistického MODE množiny hodnôt v bunkách A1-A6.

Príklad 2

Funkciu zobrazuje nasledujúca tabuľka MODE.SNGL, ktorý sa používa na výpočet štatistického režimu množiny hodnôt v bunkách A1-A10.

Všimnite si, že v tomto prípade sú dve mode v údajoch.

Vo vyššie uvedenom prípade, kde údaje v stĺpci A predchádzajúcej tabuľky majú dva MODE štatistika (3 a 4), funkcia MODE.SNGL vráti nižšiu z týchto dvoch hodnôt.

Ďalšie podrobnosti a príklady funkcií MODE.SNGL Excel, pozri Webová stránka Microsoft Office .

GEOMEAN

Geometrický priemer je miera priemeru, ktorá udáva typickú hodnotu množiny čísel. Toto meranie je možné použiť len pre kladné hodnoty.

Geometrický priemer množiny hodnôt, y ₁ A ₂ , …, tam _n vypočíta sa podľa vzorca:

Všimnite si, že geometrický priemer je vždy menší alebo rovnaký ako aritmetický priemer.

Funkcia Geomean Excel vypočíta geometrický priemer daného súboru hodnôt.

syntax

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

témy

jednu alebo viac kladných číselných hodnôt (alebo polí číselných hodnôt), pre ktoré chcete vypočítať geometrický priemer.

V aktuálnych verziách Excelu (Excel 2007 a novšie) môže funkcia akceptovať až 255 číselných argumentov, ale v Exceli 2003 môže funkcia prijať iba 30 číselných argumentov. Každý argument však môže byť pole hodnôt alebo rozsah buniek, z ktorých každá môže obsahovať veľa hodnôt.

príklad

Bunka B1 tabuľky ukazuje jednoduchý príklad funkcie geomean v Exceli, ktorý sa používa na výpočet geometrického priemeru hodnôt v bunkách A1-A5.

V tomto príklade funkcia Geomean vracia hodnotu 1.622671112 .

HARMEAN

Harmonický priemer je mierou priemeru vypočítaného ako prevrátená hodnota aritmetického priemeru recipročných hodnôt. Toto je možné vypočítať len pre kladné hodnoty.

Harmonický priemer množiny hodnôt y1, y2, ..., yn je teda daný vzorcom:

harmonický priemer je vždy menší alebo rovný geometrickému priemeru a geometrický priemer je vždy menší alebo rovný aritmetickému priemeru.

Funkcia Harmean Excel vypočíta harmonický priemer daného súboru hodnôt.

syntax

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

témy

jednu alebo viac kladných číselných hodnôt (alebo polí číselných hodnôt), pre ktoré chcete vypočítať harmonický priemer.

V aktuálnych verziách Excelu (Excel 2007 a novšie) môže funkcia akceptovať až 255 číselných argumentov, ale v Exceli 2003 môže funkcia prijať iba 30 číselných argumentov. Každý argument však môže byť pole hodnôt alebo rozsah buniek, z ktorých každá môže obsahovať veľa hodnôt.

príklad

Bunka B1 v tabuľke vpravo zobrazuje jednoduchý príklad funkcie Harmean v Exceli, ktorý sa používa na výpočet harmonického priemeru hodnôt v bunkách A1-A5.

V tomto príklade funkcia Harmean vráti hodnotu 1.229508197.

TRIMMEAN

Funkcia TRIMMEAN (tiež známy ako skrátený priemer) je miera priemeru, ktorá označuje centrálnu tendenciu súboru hodnôt.

Orezaný priemer sa vypočíta vypustením niektorých hodnôt na koncoch rozsahu hodnôt pred výpočtom aritmetického priemeru zostávajúcich hodnôt. Tým sa zabráni tomu, aby vypočítaný priemer bol skreslený extrémnymi hodnotami (technicky známymi aj ako odľahlé hodnoty). outliers).

syntax

= TRIMMEAN( array, percent )

témy

• rad – Pole číselných hodnôt, pre ktoré chcete vypočítať skrátený priemer.
• percento – Percento hodnôt, z ktorých chcete odstrániťarray poskytnuté.

Upozorňujeme, že zadaná percentuálna hodnota je celkovým percentom hodnôt, ktoré sa majú vylúčiť z výpočtu. Toto percento sa vydelí dvoma, aby sa získal počet hodnôt odstránených z každého konca rozsahu.

Treba tiež poznamenať, že keď Excel vypočíta, koľko hodnôt bolo odstránených zarray z poskytnutých hodnôt sa vypočítané percento zaokrúhli nadol na najbližší násobok 2. Napríklad, ak chcete vypočítať skrátený priemer a array z 10 hodnôt, preto:

• Percento 15 % zodpovedá 1,5 hodnotám, ktoré budú zaokrúhlené nadol na 0 (t. j. žiadne hodnoty nebudú vyradené zarray pred výpočtom priemeru);
• Percento 20 % zodpovedá 2 hodnotám, takže pred spriemerovaním zostávajúcich hodnôt bude z každého konca rozsahu vyradená 1 hodnota;
• Percentuálny podiel 25 % zodpovedá 2,5 hodnotám, ktoré budú zaokrúhlené nadol na 2 (to znamená, že pred spriemerovaním zostávajúcich hodnôt bude z každého konca rozsahu vyradená 1 hodnota).

príklad

Bunky B1-B3 v tabuľke nižšie ukážte 3 príklady funkcie trimmean v Exceli sa všetky používajú na výpočet skráteného priemeru hodnôt v bunkách A1-A10, pre rôzne percentuálne hodnoty.

Majte na pamäti, že v cele B1 z vyššie uvedenej tabuľky je uvedený percentuálny argument 15 %. Keďže varray ak existuje 10 hodnôt, počet hodnôt, ktoré sa majú ignorovať, je 1,5 zaokrúhlený nadol na najbližší násobok 2, čo je nula.

Funkcie na výpočet permutácií

PERMUT

Počet permutácií pre daný počet objektov je počet kombinácií v akomkoľvek možnom poradí.

Permutácie sa líšia od kombinácií tým, že pri permutácii záleží na poradí objektov, ale pri kombinácii na poradí nezáleží.

Počet možných permutácií je daný vzorcom:

holubica k je počet vybraných objektov e n je počet možných objektov.

Funkcia Excel Permut vypočíta počet permutácií zadaného počtu objektov z množiny objektov.

syntax

= PERMUT( number, number_chosen )

témy

• number: Celkový počet dostupných položiek
• number_chosen: Počet objektov v každej permutácii (t. j. počet objektov vybraných z množiny)

Všimnite si, že ak sú niektoré z argumentov uvedené ako desiatkové hodnoty, funkcia ich skráti na celé čísla Permut.

príklad

V nasledujúcej tabuľke je Excel Permut sa používa na výpočet počtu permutácií šiestich objektov vybraných z množín rôznych veľkostí:

PERMUTATIONA

Excel funkcie Výmena a Permutationa počítajú počet permutácií výberu objektov z množiny.

Tieto dve funkcie sa však líšia tým, že Permut. funkcia nepočíta opakovania, zatiaľ čo funkcia Permutationa počíta opakovania.

Napríklad v sade 3 objektov, a , b , c , koľko permutácií majú 2 objekty?

• La Permut. funkcia vráti výsledok 6 (permutácie: ab , ac , ba , bc , ca , cb );
• Funkcia Permutationa vráti výsledok 9 (permutácie: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

Funkcia Excel Permutationa vypočíta počet permutácií zadaného počtu objektov z množiny objektov.

syntax

= PERMUTATIONA( number, number_chosen )

témy

• number: Celkový počet objektov v sade (musí byť ≥ 0).
• number_chosen: Počet objektov vybraných zo sady (musí byť ≥ 0).

Všimnite si, že ak sú niektoré z argumentov uvedené ako desiatkové hodnoty, funkcia ich skráti na celé čísla PERMUTATIONA.

príklad

V nasledujúcej tabuľke je Excel PERMUTATIONA sa používa na výpočet počtu permutácií šiestich objektov vybraných z množín rôznych veľkostí:

Funkcie na výpočet intervalov spoľahlivosti

CONFIDENCE

V Exceli 2010 je funkcia CONFIDENCE bola nahradená funkciou Confidence.Norm.

Hoci bola nahradená, aktuálne verzie Excelu túto funkciu stále majú Confidence (uložené v zozname funkcií kompatibility), aby bola umožnená kompatibilita s predchádzajúcimi verziami Excelu.

Avšak funkcia Confidence nemusí byť k dispozícii v budúcich verziách Excelu, preto odporúčame použiť túto funkciu Confidence.Norm, Ak je to možné.

Funkcia Confidence Excel používa normálne rozdelenie na výpočet hodnoty spoľahlivosti, ktorú možno použiť na zostavenie intervalu spoľahlivosti pre priemer populácie, danú pravdepodobnosť a veľkosť vzorky. Predpokladá sa, že štandardná odchýlka populácie je známa.

syntax

= CONFIDENCE( alpha, standard_dev, size )

témy

• alfa: Hladina významnosti (= 1 – hladina spoľahlivosti). (Napríklad hladina významnosti 0,05 sa rovná 95 % hladine spoľahlivosti).
• standard_dev: štandardná odchýlka populácie.
• size: Veľkosť vzorky populácie.

Na výpočet intervalu spoľahlivosti pre strednú hodnotu populácie sa potom musí vrátená hodnota spoľahlivosti pripočítať a odpočítať od priemeru vzorky. Znamená čo. pre vzorový priemer x:

Confidence Interval =   x   ±   CONFIDENCE

príklad

V tabuľke nižšie sa funkcia spoľahlivosti Excelu používa na výpočet intervalu spoľahlivosti s významnosťou 0,05 (t. j. 95 % úroveň spoľahlivosti) pre priemer vzorky 100 výšok mužov . Priemer vzorky je 1,8 metra a štandardná odchýlka je 0,07 metra.

Predchádzajúca funkcia vráti hodnotu spoľahlivosti 0,013719748

Preto je interval spoľahlivosti 1,8 ± 0,013719748, čo je ekvivalentné rozsahu medzi 1,786280252 a 1,813719748

CONFIDENCE.NORM

V štatistike je interval spoľahlivosti rozsah, do ktorého pravdepodobne spadá parameter populácie pre danú pravdepodobnosť.

Napríklad. Pre danú populáciu a 95 % pravdepodobnosť je interval spoľahlivosti rozsah, do ktorého parameter populácie s 95 % pravdepodobnosťou spadne.

Všimnite si, že presnosť intervalu spoľahlivosti závisí od toho, či má populácia normálne rozdelenie.

Funkcia Confidence.Norm Excel používa normálne rozdelenie na výpočet hodnoty spoľahlivosti, ktorú možno použiť na zostavenie intervalu spoľahlivosti pre priemer populácie, danú pravdepodobnosť a veľkosť vzorky. Predpokladá sa, že štandardná odchýlka populácie je známa.

syntax

= CONFIDENCE.NORM( alpha, standard_dev, size )

témy

• alfa: Hladina významnosti (= 1 – hladina spoľahlivosti). (Napríklad hladina významnosti 0,05 sa rovná 95 % hladine spoľahlivosti).
• standard_dev: štandardná odchýlka populácie.
• size: Veľkosť vzorky populácie.

Na výpočet intervalu spoľahlivosti pre strednú hodnotu populácie sa potom musí vrátená hodnota spoľahlivosti pripočítať a odpočítať od priemeru vzorky. Znamená čo. pre vzorový priemer x:

Confidence Interval =   x   ±   CONFIDENCE

príklad

V tabuľke nižšie sa funkcia spoľahlivosti Excelu používa na výpočet intervalu spoľahlivosti s významnosťou 0,05 (t. j. 95 % úroveň spoľahlivosti) pre priemer vzorky 100 výšok mužov . Priemer vzorky je 1,8 metra a štandardná odchýlka je 0,07 metra.

Predchádzajúca funkcia vráti hodnotu spoľahlivosti 0,013719748

Preto je interval spoľahlivosti 1,8 ± 0,013719748, čo je ekvivalentné rozsahu medzi 1,786280252 a 1,813719748

Ercole Palmeri