Ak potrebujete vytvoriť zložité štatistické alebo inžinierske analýzy, môžete pomocou doplnku Analytické nástroje ušetriť množstvo krokov a čas. Pre každú analýzu zadáte údaje a parametre a nástroj použije príslušné štatistické alebo inžinierske funkcie makier na výpočet a zobrazenie výsledkov vo výstupnej tabuľke. Niektoré nástroje okrem výstupných tabuliek vygenerujú aj grafy.
Funkcie na analýzu údajov je možné použiť naraz iba na jednom hárku. Keď vykonáte analýzu údajov na zoskupených hárkoch, výsledky sa zobrazia v prvom hárku a v zostávajúcich hárkoch sa zobrazia prázdne formátované tabuľky. Ak chcete vykonať analýzu údajov v zostávajúcich hárkoch, vykonajte výpočet s analytickým nástrojom pre každý hárok.
Doplnok Analytické nástroje obsahuje nástroje popísané nižšie. Prístup k týmto nástrojom získate výberom položky Analýza údajov na karte Údaje . Ak príkaz Analýza údajov nie je k dispozícii, je potrebné načítať a aktivovať doplnok Analytické nástroje .
Vykonávanie štatistických a inžinierskych analýz pomocou doplnku Analytické nástroje
Načítanie a aktivácia doplnku Analytické nástroje:
VExceli pre Mac prejdite v ponuke Súbor na položku Nástroje>Doplnky Excelu.
V Exceli pre Windows:
- Vyberte položku Súbor, Možnosti a potom položku Doplnky.
- V poli Spravovať vyberte položku Doplnky programu Excel a potom vyberte položku Spustiť.
V dialógovom okne Doplnky začiarknite políčko Analytické nástroje a potom vyberte tlačidlo OK.
- Ak sa položka Analytické nástroje nenachádza v poli Dostupné doplnky, kliknite na položku Prehľadávať a vyhľadajte ju.
- Ak sa zobrazí výzva, že doplnok Analytické nástroje nie je momentálne nainštalovaný, vyberte možnosť Áno a doplnok sa nainštaluje.
Poznámka
Ak chcete mať v doplnku Analytické nástroje k dispozícii aj funkcie jazyka Visual Basic for Application (VBA), môžete zaviesť doplnok Analytické nástroje – VBA rovnakým spôsobom ako doplnok Analytické nástroje. V poli Dostupné doplnky začiarknite políčko Analytické nástroje - VBA.
Anova
Analytické nástroje Anova poskytujú rôzne typy analýz rozptylov. Výber vhodného nástroja závisí od počtu faktorov a počtu vzoriek zo základného súboru, ktorý chcete testovať.
Anova: jeden faktor
Tento nástroj vykoná jednoduchú analýzu rozptylu údajov pre dve alebo viaceré vzorky. Analýza poskytuje test hypotézy, že každá vzorka je vybraná z rovnakého základného rozdelenia pravdepodobnosti, a oproti alternatívnej hypotéze, že rozdelenia pravdepodobnosti nie sú pre všetky vzorky rovnaké. Ak sú k dispozícii len dve vzorky, môžete použiť funkciu hárka T.TEST. Pri viac ako dvoch vzorkách neexistuje vhodné zovšeobecnenie T.TEST a namiesto neho možno použiť jednofaktorový model Anova.
Anova: dva faktory s opakovaním
Tento analytický nástroj je užitočný v prípade, že údaje je možné klasifikovať podľa dvoch odlišných dimenzií. Napríklad pri pokusoch, pri ktorých sa meria výška rastlín, je možné rastliny hnojiť rôznymi typmi hnojív (napríklad A, B, C) a pestovať pri rôznych teplotách (napríklad pri vysokej a nízkej teplote). Pre každú zo šiestich možných kombinácií hnojiva a teploty sa počas pozorovania získa rovnaký počet údajov o výške rastliny. Pomocou tohto nástroja Anova je možné testovať nasledovné skutočnosti:
- Môžete testovať, či sú hodnoty výšky rastlín v prípade jednotlivých hnojív vybraté z rovnakého základného súboru. Teploty sa pri tejto analýze neberú do úvahy.
- Môžete testovať, či sú hodnoty výšky rastlín pre rôzne teploty vybraté z rovnakého základného súboru. Hnojivá sa pri tejto analýze neberú do úvahy
Nakoniec môžete testovať, či po zahrnutí rozdielnych vplyvov jednotlivých typov hnojív, ktoré sa zistili v prvom bode, a zahrnutí rozdielnych vplyvov teplôt, ktoré sa zistili v druhom bode, boli hodnoty šiestich vzoriek reprezentujúcich všetky kombinácie hnojiva a teploty vybraté z rovnakého základného súboru. Alternatívna hypotéza vychádza z predpokladu, že existujú rôzne vplyvy konkrétnych kombinácií hnojiva a teploty, ktoré sú výraznejšie ako rozdiely vyplývajúce len z rozdielov v hnojivách alebo v teplote.
Anova: dva faktory bez opakovania
Tento analytický nástroj je užitočný pri klasifikácii podľa dvoch odlišných dimenzií, tak ako v prípade dvojfaktorovej analýzy s opakovaním. Pri tomto nástroji sa však predpokladá, že pre každú kombináciu je k dispozícii len jedno pozorovanie (napríklad pre každú kombináciu hnojiva a teploty vo vyššie uvedenom príklade).
Korelácia
Funkcie hárka CORREL a PEARSON slúžia na výpočet korelačného koeficientu medzi dvoma meranými veličinami, ak sú hodnoty meraní jednotlivých veličín zaznamenané pre každý z N prvkov. (Ak pre určitý prvok nie sú k dispozícii údaje, daný prvok sa pri analýze neberie do úvahy.) Analytický nástroj korelácie je užitočný najmä vtedy, ak pre každý z N prvkov existujú viac ako dve merané veličiny. Výstupom analýzy vo forme tabuľky je korelačná matica, v ktorej sú zobrazené hodnoty funkcie CORREL (alebo PEARSON) aplikovanej na každú vzájomnú kombináciu meraných veličín.
Korelačný koeficient, tak ako kovariancia, predstavuje mieru "vzájomnej odlišnosti" dvoch meraných veličín. Na rozdiel od kovariancie je korelačný koeficient škálovaný, čo znamená, že jeho hodnota nie je závislá od jednotiek, v ktorých sú uvedené dané dve merané veličiny. (Ak sú meranými veličinami napríklad hmotnosť a výška a jednotka hmotnosti sa konvertuje z libier na kilogramy, hodnota korelačného koeficientu sa nezmení.) Hodnota každého korelačného koeficientu musí byť v intervale -1 až +1 vrátane.
Analytický nástroj korelácie sa používa na analýzu každej vzájomnej kombinácie meraných veličín, ktorá slúži na určenie závislosti dvoch meraných veličín – t. j. či vyššie hodnoty jednej veličiny súvisia skôr s vyššími hodnotami druhej veličiny (kladná korelácia), alebo či nižšie hodnoty jednej veličiny súvisia skôr s vyššími hodnotami druhej veličiny (záporná korelácia), prípadne, či sú hodnoty obidvoch veličín nezávislé (korelácia blízka nule).
Kovariancia
Nástroje Correlation a Covariance možno použiť v prípade, ak je k dispozícii N rôznych meraných veličín, ktoré boli namerané na množine prvkov. Výstupom týchto nástrojov je tabuľka obsahujúca maticu, ktorá uvádza korelačný koeficient alebo kovarianciu medzi jednotlivými vzájomnými kombináciami meraných veličín. Rozdiel je v tom, že korelačné koeficienty sú škálované tak, aby ich hodnota ležala v uzavretom intervale (–1,1). Príslušné kovariancie nie sú škálované. Korelačný koeficient aj kovariancia predstavujú mieru, akou sa dve veličiny „vzájomne odlišujú“.
Nástroj Covariance vypočíta hodnotu funkcie COVARIANCE hárka. P pre každú dvojicu meraných veličín. (Priame použitie funkcie COVARIANCE. Ak existujú len dve merané veličiny, t. j. N=2, je vhodnejšie P namiesto nástroja Covariance.) Položka na uhlopriečke výstupnej tabuľky nástroja kovariancie v riadku i stĺpci i je kovariancia i-tej meranej premennej so sebou samým. Toto je len rozptyl základného súboru pre danú premennú vypočítaný funkciou hárka VAR.P.
Analytický nástroj Covariance sa používa na analýzu každej vzájomnej kombinácie meraných veličín, ktorá slúži na určenie závislosti dvoch meraných veličín – t. j. či vyššie hodnoty jednej veličiny súvisia skôr s vyšším hodnotami druhej veličiny (kladná kovariancia), alebo či nižšie hodnoty jednej veličiny súvisia skôr s vyšším hodnotami druhej veličiny (záporná kovariancia), prípadne či sú hodnoty obidvoch veličín nezávislé (kovariancia blízka nule).
Popisná štatistika
Analytický nástroj Descriptive Statistics vygeneruje zostavu štatistického hodnotenia jednorozmerných údajov vo vstupnom rozsahu a poskytne informácie o hlavnom trende a premenlivosti údajov.
Exponenciálne vyrovnávanie
Analytický nástroj Exponential Smoothing slúži na výpočet prognózovanej hodnoty na základe prognózy predchádzajúceho obdobia, pričom sa zohľadnia chyby predchádzajúcej prognózy. Nástroj používa konštantu vyrovnávania a, ktorej veľkosť vyjadruje citlivosť prognózovaných hodnôt na prípadné chyby v predchádzajúcej prognóze.
Poznámka
Vhodné konštanty vyrovnávania sú v rozsahu 0,2 až 0,3. Tieto hodnoty vyjadrujú, že prognózovaná hodnota by mala zohľadniť chyby v predchádzajúcej prognóze na 20 až 30 percent. Vyššie hodnoty konštanty umožňujú rýchlejšiu odozvu na chybu, ale môžu viesť k chybným prognózam. Nižšie hodnoty môžu spôsobiť zdĺhavé odozvy prognózovaných hodnôt.
F-test rozptylov dvoch vzoriek
Analytický nástroj F-Test Two-Sample for Variances vykoná F-test dvoch vzoriek na porovnanie odchýlok základného súboru.
Nástroj F-Test môžete napríklad aplikovať na dosiahnuté časy dvoch súťažných tímov, ktoré boli namerané v rámci plaveckých pretekov. Výsledkom je test nulovej hypotézy, že dané dva časy sú z rozdelení s rovnakým rozptylom a voči alternatívnej hypotéze nie sú rozptyly v uvedených rozdeleniach zhodné.
Tento nástroj vypočíta hodnotu f F-štatistiky (alebo F-pomer). Ak sa hodnota f blíži k 1, znamená to, že odchýlky základného súboru sú rovnaké. Ak je vo výstupnej tabuľke f1, jednostranná pravdepodobnosť " < P(F <= f) one-tail" určuje pravdepodobnosť výskytu hodnoty F-štatistiky nižšej ako f, ak sú rozptyly základného súboru rovnaké a jednostranná pravdepodobnosť "F Critical one-tail" určuje kritickú hodnotu nižšiu ako 1 pre zvolenú hladinu významnosti alfa. Ak je hodnota f > 1, jednostranná pravdepodobnosť "P(F <= f) one-tail" určuje pravdepodobnosť výskytu hodnoty F-štatistiky vyššej ako f, ak sú odchýlky základného súboru rovnaké a jednostranná pravdepodobnosť "F Critical one-tail" určuje kritickú hodnotu vyššiu ako 1 pre hladinu alfa.
Fourierova analýza
Nástroj Fourier Analysis umožňuje vyriešiť problémy v lineárnych systémoch a analyzovať periodické údaje pomocou metódy rýchlej Fourierovej transformácie (FFT) na transformáciu údajov. Nástroj podporuje aj inverzné transformácie, pri ktorých sú výsledkom inverzie transformovaných údajov opäť pôvodné údaje.
Histogram
Analytický nástroj Histogram vypočíta individuálne a kumulatívne frekvencie pre údaje v rozsahu buniek a blokoch údajov. Nástroj vygeneruje frekvenciu výskytov určitej hodnoty v množine údajov.
Napríklad v triede s 20 študentmi možno určiť rozdelenie dosiahnutých známok pomocou kategórií jednotlivých známok. V tabuľke histogramu sa zobrazia hranice známok a počet výskytov získaných známok medzi dolnou a aktuálnou hranicou. Najčastejší výskyt známky sa nazýva modus.
Kĺzavý priemer
Analytický nástroj Moving Average sa používa na prognózu hodnôt pre obdobie prognózy na základe priemernej hodnoty premennej za stanovený počet predchádzajúcich období. Kĺzavý priemer poskytuje informácie o trende, ktoré nemožno zistiť pri výpočte jednoduchého priemeru všetkých historických údajov. Tento nástroj sa používa na vytváranie prognóz výsledkov predaja, stavu zásob a iných trendov. Prognózované hodnoty sa vypočítajú podľa nasledovného vzorca.
kde:
- N je počet predchádzajúcich období, ktoré sú zahrnuté do výpočtu kĺzavého priemeru,
- Aj je skutočná hodnota v čase j
- Fj je predpovedaná hodnota v čase j
Generovanie náhodných čísel
Analytický nástroj Random Number Generation vyplní rozsah nezávislými náhodnými číslami vybratými z niektorého rozdelenia. Prvky základného súboru možno charakterizovať rozdelením pravdepodobnosti. Môžeme napríklad použiť normálne rozdelenie na charakterizovanie výšky jednotlivcov v základnom súbore alebo použiť Bernoulliho rozdelenie dvoch možných výsledkov na charakterizovanie základného súboru výsledkov hádzania mince.
Poradie a percentil
Analytický nástroj Poradie a percentil vytvorí tabuľku, ktorá obsahuje poradové a percentuálne poradie každej hodnoty v množine údajov. Môžete analyzovať relatívne umiestnenie hodnôt v množine údajov. Tento nástroj používa funkcie hárka RANK. EQ a PERCENTRANK. INC. Ak chcete zohľadniť nerozhodné hodnoty, použite funkciu RANK. Funkcia EQ , ktorá považuje nerozhodné hodnoty za rovnakú relatívnu veľkosť, alebo použite funkciu RANK. AVG (funkcia ), ktorá vráti priemerné poradie pre nerozhodné hodnoty.
Regresia
Analytický nástroj Regression vykoná lineárnu regresnú analýzu pomocou metódy najmenších štvorcov, pričom preloží priamku cez množinu pozorovaní. Umožňuje analyzovať ako hodnoty jednej alebo viacerých nezávislých premenných ovplyvňujú danú závislú premennú. Môžete napríklad analyzovať ako je výkonnosť atléta ovplyvnená faktormi ako vek, výška a hmotnosť. Podiely v miere výkonnosti môžete priradiť ku každému z týchto troch faktorov na základe údajov o výkonnosti a potom výsledky použiť na predpovedanie výkonu ďalšieho atléta, ktorý nebol testovaný.
Nástroj Regression používa funkciu hárka LINEST.
Vzorkovanie
Analytický nástroj Sampling vytvorí vzorku zo základného súboru, pričom za základný súbor sa považuje vstupný rozsah. Ak je základný súbor príliš veľký na spracovanie alebo vytvorenie grafu, možno použiť reprezentatívnu vzorku. Ak predpokladáte, že vstupné údaje sú periodické, môžete vytvoriť vzorku obsahujúcu hodnoty len z určitej časti cyklu. Ak napríklad vstupný rozsah obsahuje štvrťročné údaje predaja, výber vzorky s periódou štyri sa umiestni do výstupného rozsahu hodnoty z rovnakého štvrťroku.
t-Test
Analytické nástroje Two-Sample t-Test testujú rovnosť stredných hodnôt základných súborov jednotlivých vzoriek. Tieto tri nástroje vychádzajú z troch rôznych predpokladov: 1) rozptyly základných súborov sú rovnaké; 2) rozptyly základných súborov nie sú rovnaké; 3) dve vzorky reprezentujú pozorovania rovnakých objektov pred spracovaním a po spracovaní.
Pre všetky tri nástroje uvedené nižšie sa vypočíta hodnota t-štatistiky t, ktorá sa vo výstupných tabuľkách zobrazí ako "t Stat". V závislosti od údajov môže byť táto hodnota t záporná alebo nezáporná. Ak sa predpokladá rovnaká základná hodnota základného súboru, ak je hodnota t < 0, jednostranná pravdepodobnosť "P(T <= t)" určuje pravdepodobnosť zápornejšej hodnoty t-štatistiky ako hodnota t. Ak t >= 0, jednostranná pravdepodobnosť "P(T <= t) one-tail" určuje pravdepodobnosť výskytu hodnoty t-štatistiky, ktorá je kladnejšia ako hodnota t. Jednostranná pravdepodobnosť "t Critical one-tail" určuje hraničnú hodnotu, takže pravdepodobnosť výskytu hodnoty t-štatistiky vyššej alebo rovnej jednostrannej fáze "t Critical one-tail" je alfa.
Obojstranná pravdepodobnosť "P(T <= t) two-tail" určuje pravdepodobnosť výskytu hodnoty t-štatistiky, ktorá je v absolútnej hodnote vyššia ako hodnota t. Obojstranná pravdepodobnosť "P Critical two-tail" určuje hraničnú hodnotu, pri ktorej je pravdepodobnosť výskytu t-štatistiky, ktorá je v absolútnej hodnote vyššia ako obojstranná pravdepodobnosť "P Critical two-tail", je alfa.
t-Test: Dvojvýberový párový test pre stredné hodnoty
Párový test môžete použiť, ak v prípade vzoriek existuje prirodzené párovanie pozorovaní, napríklad ak je skupina vzoriek testovaná dvakrát – pred a po experimente. Tento analytický nástroj a jeho vzorec určia pomocou dvojvýberového párového Studentovho t-testu, či majú pozorovania získané pred spracovaním a pozorovania získané po spracovaní rozdelenia s rovnakými strednými hodnotami základných súborov. V prípade tohto t-Testu sa nepredpokladá rovnosť rozptylov oboch základných súborov.
Poznámka
Jedným z výsledkov nástroja je združený rozptyl alebo akumulovaná miera variability údajov okolo strednej hodnoty, ktorá sa vypočíta pomocou nasledovného vzorca.
t-Test: Dvojvýberový test s rovnosťou odchýlok
Tento analytický nástroj vykoná Studentov t-Test dvoch vzoriek. Pri tomto type t-Testu sa predpokladá, že obe množiny údajov pochádzajú z rozdelení s rovnakými rozptylmi. Označuje sa ako homoskedastický t-Test. Pomocou tohto t-Testu možno určiť, či dve vzorky pochádzajú z rozdelení s rovnakými strednými hodnotami základných súborov.
t-Test: Dvojvýberový test s nerovnosťou odchýlok
Tento analytický nástroj vykoná Studentov t-Test dvoch vzoriek. Pri tomto type t-Testu sa predpokladá, že obe množiny údajov pochádzajú z rozdelení s nerovnakým rozptylom. Označuje sa ako heteroskedastický t-Test. Rovnako ako v predchádzajúcom prípade rovnosti rozptylov môžete tento t-test použiť na určenie, či dve vzorky pochádzajú z rozdelení s rovnakými strednými hodnotami základných súborov. Tento test sa použite, ak sa v dvoch výberových skupinách nachádzajú odlišné subjekty. Párový test popísaný v nasledujúcom príklade použite, ak existuje jedna skupina subjektov a dve vzorky predstavujú miery pre každý objekt pred a po ošetrení.
Nasledovný vzorec sa používa na výpočet štatistickej hodnoty t.
Nasledujúci vzorec sa používa na výpočet stupňov voľnosti df. Keďže výsledkom výpočtu zvyčajne nie je celé číslo, hodnota df sa zaokrúhli na najbližšie celé číslo, čím sa získa kritická hodnota z tabuľky t. Funkcia hárka programu Excel T.TEST používa vypočítanú hodnotu df bez zaokrúhľovania, pretože hodnotu pre funkciu T.TEST je možné vypočítať aj s hodnotou df, ktorá nie je celé číslo. Na základe týchto rozdielnych prístupov k určeniu stupňov voľnosti sa výsledky funkcie T.TEST a tohto nástroja t-Test budú v prípade nerovnosti rozptylov líšiť.
z-Test
Analytický nástroj z-Test: Two Sample for Means vykoná dvojvýberový z-test stredných hodnôt so známymi rozptylmi. Tento nástroj sa používa na otestovanie nulovej hypotézy, podľa ktorej neexistuje žiadny rozdiel medzi dvoma strednými hodnotami základného súboru voči jednostranným alebo obojstranným alternatívnym hypotézam. Ak odchýlky nie sú známe, má sa namiesto nej použiť funkcia hárka Z.TEST .
Pred používaním nástroja z-test by ste mali porozumieť výstupu tohto nástroja. "P(Z <= z) one-tail" je v skutočnosti P(Z >= ABS(z)), čo je pravdepodobnosť výskytu hodnoty z vo väčšej vzdialenosti od 0 v rovnakom smere ako pozorovaná hodnota z, ak medzi strednými hodnotami základných súborov nie je rozdiel. "P(Z <= z) two-tail-tail" je v skutočnosti P(Z >= ABS(z) alebo Z <= -ABS(z)), čo je pravdepodobnosť, že hodnota z bude smerovať ďalej od 0 v oboch smeroch ako pozorovaná hodnota z, ak medzi strednými hodnotami základných súborov nie je rozdiel. Obojstranný výsledok je teda len vynásobením jednostranného výsledku hodnotou 2. Nástroj z-test možno tiež použiť v prípade, keď nulovú hypotézu predstavuje skutočnosť, že pre rozdiel medzi dvoma strednými hodnotami základných súborov existuje konkrétna nenulová hodnota. Tento test môžete napríklad použiť na určenie rozdielov medzi výkonom dvoch modelov automobilov.
Potrebujete ďalšiu pomoc?
Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.
Pozrite tiež
Vytvorenie histogramu v Exceli 2016
Vytvorenie Paretovho grafu v Exceli 2016
Načítanie doplnku Analytické nástroje v Exceli
Zabránenie vzniku nefunkčných vzorcov
Vyhľadanie a oprava chýb vo vzorcoch
Excelové klávesové skratky a funkčné klávesy