Komplex adatelemzés az Analysis ToolPak bővítménnyel

Ha komplex statisztikai vagy mérnöki elemzéseket kell készítenie, akkor időt és energiát takaríthat meg az Analysis ToolPak használatával. Elég megadnia az adatokat és az egyes elemzések paramétereit, és az eszköz a megfelelő statisztikai és mérnöki makrófunkciókkal kiszámítja és megjeleníti az eredményeket egy kimeneti táblázatban. Egyes eszközök a kimeneti táblázatok mellett diagramokat is előállítanak.

Az adatelemzési funkciók egyszerre csak egy munkalapon használhatók. Ha csoportosított munkalapokon végez adatelemzést, az eredmények az első munkalapon jelennek meg, a többi munkalapon pedig csak üres formázott táblázatok lesznek láthatók. Ha a fennmaradó munkalapok adatait is szeretné elemezni, akkor munkalaponként újra kell számítania az eredményeket az elemző eszközzel.

Az Analysis ToolPak az alábbi szakaszokban leírt eszközöket foglalja magában. Az eszközök eléréséhez kattintson az Adatok lap Elemzés csoportjának Adatelemzés parancsára. Ha az Adatelemzés parancs nem érhető el, akkor be kell töltenie az Analysis ToolPak bővítményt.

  1. Kattintson a Fájl lap Beállítások gombjára, majd a Bővítmények kategóriára.

    Excel 2007 használata esetén kattintson a Microsoft Office gombra Az Office gomb képe , majd Az Excel beállításai lehetőségre.

  2. A Kezelés legördülő listában válassza az Excel-bővítmények lehetőséget, és kattintson az Ugrás gombra.

    Mac Excel használata esetén a Fájl menüben keresse meg az Eszközök > Excel-bővítmények parancsot.

  3. Jelölje be a Bővítmények párbeszédpanelen az Analysis ToolPak jelölőnégyzetet, és kattintson az OK gombra.

    • Ha az Analysis ToolPak nem szerepel a Létező bővítmények listában, akkor a Tallózás gombra kattintva megkeresheti.

    • Ha az alkalmazás kiírja, hogy az Analysis ToolPak bővítmény nincs a számítógépen telepítve, akkor a telepítéshez kattintson az Igen gombra.

Megjegyzés : Ha az Analysis ToolPak bővítménnyel együtt az Analysis Toolpak VBA (Visual Basic for Applications) függvényeit is aktiválni szeretné, akkor töltse be az Analysis ToolPak – VBA bővítményt ugyanúgy, ahogy az Analysis ToolPak bővítményt betöltötte: jelölje be a Bővítmények mezőben az Analysis ToolPak – VBA jelölőnégyzetet.

Az Anova (Analysis of Variance) elemzőeszközzel különféle típusú varianciaanalízisek végezhetők. A választandó eszközt a tényezők száma, illetve a statisztikai sokaságból vett és a próbának alávetni kívánt minták száma határozza meg.

Egytényezős varianciaanalízis

Ezzel az eszközzel egyszerű varianciaanalízis végezhető két vagy több minta adatain. Az elemzés két hipotézist vet össze: az egyik szerint mindegyik minta ugyanolyan valószínűségi eloszlású sokaságból származik, a másik szerint a sokaság valószínűségi eloszlása nem minden mintában ugyanolyan. Ha csak két minta van, akkor használhatja a munkalapok T.PRÓB függvényét is. Kettőnél több mintára viszont már nincs kényelmesen használható általános T.PRÓB függvény, ehelyett az egytényezős Anova modell használható.

Kéttényezős varianciaanalízis ismétlésekkel

Ez az elemzőeszköz akkor hasznos, ha az adatok két különböző dimenzió szerint osztályozhatók. Adott például egy kísérlet, amelyben a növények magasságát mérik, miközben különféle márkájú tápoldatokkal (például A, B, C) kezelik, ezenkívül különböző hőmérsékletnek (alacsony, magas) is teszik ki őket. A hat lehetséges {tápoldat, hőmérséklet} párosítás mindegyikéhez egyenlő számú magasságmérés tartozik. Ekkor varianciaanalízissel vizsgálhatók a következők:

  • A különböző tápoldatmárkához tartozó növénymagasságok ugyanabból a sokaságból származnak-e. Ez az elemzés nem veszi figyelembe a hőmérséklet hatását.

  • A különböző hőmérsékletekhez tartozó növénymagasságok ugyanabból a sokaságból származnak-e. Ebben az esetben a tápoldatok hatását hagyja figyelmen kívül.

Figyelembe véve az első pontban a tápoldatmárkák között észlelt különbségeket, valamint a második pontban a hőmérsékletek között észlelt különbségeket, az összes {tápoldat, hőmérséklet} értékpárt képviselő hat minta ugyanabból a sokaságból származik-e. Az alternatív hipotézis szerint amellett, hogy a hőmérséklet vagy a tápoldat változása külön-külön eltérést okoz, az egyes {tápoldat, hőmérséklet} pároknak további hatás is tulajdonítható.

A varianciaanalízis bemeneti tartományának beállítása

Kéttényezős varianciaanalízis ismétlések nélkül

Ez az elemzőeszköz akkor használható, ha az adatok két különböző dimenzió szerint osztályozhatók, a kéttényezős, ismétléses varianciaanalízishez hasonlóan. Itt azonban feltételezzük, hogy minden párhoz (például az előző példában minden {tápoldat, hőmérséklet} párhoz) csak egy megfigyelés tartozik.

A KORREL és a PEARSON munkalapfüggvény egyaránt két mérési változó korrelációs együtthatóját számítja ki olyan mérések alapján, amelyek során mindegyik változót n egyeden mértek meg. (Ha bármelyik egyed esetében hiányzik valamelyik mérés, akkor az az egyed kimarad az elemzésből). A korrelációanalízis különösen hasznos, ha az n egyed mindegyikéhez kettőnél több mérési változó tartozik. Az eszköz egy táblázatban – a korrelációs mátrixban – megjeleníti a KORREL (vagy PEARSON) értéket az összes lehetséges értékpárra.

A korrelációs együttható – a kovarianciához hasonlóan – azt jelzi, hogy két mérési változó mennyire „változik egyszerre”. A varianciától eltérően a korrelációs együttható értéke arányos, így független a két változó mértékegységétől. (Ha például a két mérési változó a súly és a magasság, akkor a korrelációs együttható értéke nem változik, ha a súlyt átváltja fontról kilogrammra.) Bármely korrelációs együttható értékének a -1; +1 zárt intervallumban kell lennie.

Ha a korrelációanalízissel megvizsgálja az összes értékpárt, meghatározhatja, hogy a két mérési változó együtt mozog-e, vagyis az egyik változó nagyobb értékei a másik változó értékének növekedésével vannak-e összefüggésben (pozitív korreláció), vagy éppen fordítva, az egyik változó nagyobb értékeihez a másik változó kisebb értékei tartoznak (negatív korreláció), vagy a két változó értékei között nem fedezhető fel kapcsolat (a korreláció nullához közeli).

Mind a korreláció-, mind a kovarianciaanalízis használható abban az esetben, ha egyedek egy adott halmazán n különböző mérési változót figyelnek meg. Mindkét eszköz egy táblázatot – egy mátrixot – ad eredményül, amelyből leolvasható a korrelációs együttható, illetve értelemszerűen a kovariancia minden értékpár esetében. A különbség az, hogy míg a korrelációs együttható csak -1 és +1 között lehet, addig a kovariancia értéke nincs korlátozva. A korrelációs együttható és a kovariancia és azt jelzi, hogy a két változó mennyire „változik egyszerre”.

A kovarianciaanalízis a KOVARIANCIA.S munkalapfüggvény értékét számítja ki az egyes változópárokra. (A KOVARIANCIA.S függvény közvetlen alkalmazása akkor célszerű, ha csak két mérési változó van, vagyis n=2.) A kovarianciaanalízis eredményeként megjelenő táblázat átlóján, az i-edik sor i-edik oszlopának értéke az i-edik mérési változó önmagával vett kovarianciája. Ez éppen az adott változó varianciája a sokaságban, vagyis a VAR.S munkalapfüggvény által számított érték.

Ha a varianciaanalízissel megvizsgálja az összes értékpárt, meghatározhatja, hogy a két mérési változó együtt mozog-e, vagyis az egyik változó nagyobb értékei a másik változó értékének növekedésével vannak-e összefüggésben (pozitív kovariancia), vagy éppen fordítva, az egyik változó nagyobb értékeihez a másik változó kisebb értékei tartoznak (negatív kovariancia), vagy a két változó értékei között nem fedezhető fel kapcsolat (a kovariancia nullához közeli).

A leíró statisztika elemzőeszköz egyváltozós statisztikai jelentést készít a kiindulási adattartományról, és információt nyújt az adatok súlypontjáról és változatosságáról.

Az exponenciális simítással a korábbi időszak adatai alapján előre lehet jelezni egy, az előző időszak hibaértékeivel korrigált értéket. A módszer az a simítási állandót használja, ennek nagysága határozza meg, hogy a megelőző időszakból származó hibák mekkora hatást gyakoroljanak az előrejelzésekre.

Megjegyzés : A simítási állandó értékét 0,2 és 0,3 között célszerű megválasztani. Ez az érték azt jelzi, hogy az aktuális előrejelzést a korábbi előrejelzési hibájának 20–30 százalékával kell korrigálni. Nagyobb állandókkal gyorsabb közelítés érhető el, de az előrejelzés hibája is nagyobb lesz. Ha az állandó értéke túl kicsi, akkor viszont a becsült értékek csak sokára érik el a tényleges értékeket.

A kétmintás f-próba két statisztikai sokaság szórásnégyzetét hasonlítja össze.

Az f-próbát elvégezheti például egy úszóbajnokságon szereplő két csapat időeredményein. Az f-próba azon nullhipotézis ellenőrzésének eredményét adja vissza, miszerint a két minta eloszlásának varianciája azonos. Az alternatív hipotézis szerint az alapul szolgáló eloszlásokban a varianciák nem egyenlőek.

Az eszköz egy F-statisztika (vagy F viszonyszám) f értékét számolja ki. Az 1-hez közeli f érték azt bizonyítja, hogy a vizsgált sokasági varianciák megegyeznek. Ha az eredménytáblában f < 1, akkor a „P(F <= f) egyoldalú próba” annak a valószínűségét jelenti, hogy az F-statisztika megfigyelt értéke kisebb, mint f, amennyiben a sokasági varianciák egyenlőek, és az „F-kritikus egyoldalú próba” kritikus értéke 1-nél kisebb a választott alfa pontossági szinthez. Ha f > 1, akkor a „P(F >= f) egyoldalú próba” annak a valószínűségét jelenti, hogy az F-statisztika megfigyelt értéke nagyobb, mint f, amennyiben a sokasági varianciák egyenlőek, és az „F-kritikus egyoldalú próba” kritikus értéke 1-nél nagyobb a választott alfa szinthez.

Lineáris rendszerek és periodikus adatok elemzésére használható, az adatokat a gyors Fourier-transzformáció (Fast Fourier Transform - FFT) módszerével transzformálja. A művelet segítségével inverz transzformáció is végezhető, amely a transzformált adatokból visszaadja az eredeti adatokat.

A Fourier-analízis bemeneti és kimeneti tartománya

Egy cellatartomány adatai és az adatkategóriák alapján egyenkénti és halmozott gyakoriságok számíthatók ki. Az eljárással meg lehet határozni, hogy egy adott érték hányszor fordul elő az adathalmazban.

Ha például egy 20 fős osztályban a tanulók érdemjegyeinek kategóriánkénti megoszlására kíváncsi, a hisztogram eredménytáblája közli az érdemjegyek határértékeit, valamint a legalsó határérték és az aktuális határérték közé eső érdemjegyek számát. A leggyakoribb érdemjegy az adatok módusza.

Tipp : Az Excel 2016-ban létrehozhat egy hisztogramot vagy egy Pareto diagramot.

Ez az eljárás a becsült időszak értékeit úgy számítja ki, hogy a megelőző időszak adatait megadott számú periódusonként átlagolja. A mozgó átlagolás módszerével olyan részletek derülhetnek ki a trendről, amelyek a meglévő adatok egyszerű átlagolásával elmosódnak. Ez a módszer értékesítési adatok, raktárkészletadatok vagy egyéb változó adatok előrejelzésére használható. A becsült értékeket az alábbi képlet alapján számítja ki:

Mozgó átlagok számítására szolgáló képlet

ahol:

  • N a mozgó átlag periódusainak száma

  • A j a tényleges érték a j időpontban

  • F j a becsült érték a j időpontban

Ezzel a módszerrel több eloszlásból származó és egymástól független véletlen számokkal tölthető fel egy tartomány. Egyik alkalmazása egy sokaság egyedeinek jellemzése valószínűségi eloszlás segítségével. Például egy populáció egyedeinek magassága normális eloszlással írható le, egy két lehetséges kimenetelű esemény – ilyen a pénzfeldobás – bekövetkezésének valószínűsége pedig a Bernoulli-eloszlással jellemezhető.

A Rangsor és százalékos rangsor elemzési módszer táblázatban jeleníti meg egy adathalmaz egyes értékeinek tényleges és százalékos rangsorát, így vizsgálható az adathalmaz értékeinek egymáshoz viszonyított helye. Az eszköz a RANG.EGY és SZÁZALÉKRANG.TARTALMAZ munkalapfüggvényeket használja. Ha figyelembe szeretné venni a kötött értékeket, akkor a RANG.EGY függvényt, amely egyenlő rangúként kezeli a kötött értékeket, vagy használja a RANG.ÁTL függvényt, amely a kötött értékek átlagos rangját adja eredményül.

Ez az eljárás lineáris regresszióanalízist végez: a legkisebb négyzetek módszerével egyenest illeszt az adatpontok halmazára. Segítségével elemezheti, hogy egy függő változó értékét hogyan befolyásolja több független változó értéke. Megvizsgálható például, hogy egy atléta teljesítményét hogyan befolyásolják az olyan adatok, mint a kora, a magassága és a testsúlya. A teljesítményadatok alapján meghatározható, hogy az egyes tényezők milyen mértékben befolyásolják az eredményt, majd ezek alapján előre jelezhető egy új, még nem vizsgált sportoló teljesítménye.

A regresszióanalízis a LIN.ILL munkalapfüggvényt használja.

Ez az eljárás a kiindulási adathalmazt alapsokaságnak tekinti, és abból egy mintát választ ki. A sokaságot képviselő mintát akkor használhatja, ha a teljes sokaság túl nagy a vizsgálathoz vagy az ábrázoláshoz. Ha úgy véli, hogy a kiindulási adatok periodikusak, az adatok egyetlen meghatározott ciklusából is mintát tud venni. Ha például a kiindulási tartomány értékesítési adatokat tartalmaz negyedéves bontásban, akkor a teljes időszak minden negyedik adatának kiválasztásával megkaphatja az egy adott negyedévre vonatkozó eredményeket.

A kétmintás t-próba eszközeivel megvizsgálhatja, hogy a két sokaságnak, amelyekből a mintát vette, egyenlő-e a várható értéke. A három eszköz különböző feltételezésekből indul ki: a sokasági varianciák egyenlőek, a sokasági varianciák különbözőek, illetve a két minta olyan mérésekből áll, amelyek ugyanazon egyedek kezelés előtti és kezelés utáni állapotát tükrözik.

Mindhárom eszköz egy t-statisztika értékét, t-t számítja ki és jeleníti meg t-statisztika néven az eredménytáblákban. Az adatoktól függően ez a t érték lehet negatív vagy nem negatív. Feltéve, hogy a vizsgált sokaságok várható értékei egyenlőek, ha t < 0, akkor a „P(T <= t) egyoldalú próba” annak a valószínűségét jelenti, hogy a t-statisztika vizsgált értéke egy t-nél kisebb negatív érték. Ha t >=0, a „P(T >= t) egyoldalú próba” annak a valószínűségét jelenti, hogy a t-statisztika vizsgált értéke egy t-nél nagyobb pozitív érték. A „t-kritikus egyoldalú próba” a szakadási pont értékét adja meg, így annak a valószínűsége, hogy a t-statisztika vizsgált értéke a „t-kritikus egyoldalú próba” értékénél nagyobb vagy egyenlő, éppen alfa.

A „P(T <= t) kétoldalú próba” annak a valószínűségét jelenti, hogy a t-statisztika vizsgált értékének abszolút értéke nagyobb t-nél. A „P-kritikus kétoldalú próba” a szakadási pont értékét adja meg, így annak a valószínűsége, hogy a t-statisztika vizsgált értékének abszolút értéke a „P-kritikus kétoldalú próba” értékénél nagyobb, éppen alfa.

Kétmintás párosított t-próba a várható értékre

A párosított t-próba akkor használható, ha a minták vizsgálati adatai természetes módon párba állíthatók. Ez például akkor fordul elő, amikor egy mintacsoportot kétszer vizsgál: kísérlet előtt és után. Az eszköz és képlete kétmintás, párosított Student-féle t-próbával állapítja meg, hogy a kísérlet előtti és utáni mérések származhatnak-e azonos sokasági középértékű eloszlásokból. Ez a típusú próba nem feltételezi, hogy a két sokaság varianciája egyenlő.

Megjegyzés : Ezzel a vizsgálati módszerrel a súlyozott variancia is kiszámítható, amely az adatok várható érték körüli szóródását összesíti, és a következő képlettel határozható meg:

Súlyozott varianciát számító képlet

Kétmintás t-próba egyenlő szórásnégyzeteknél

Ez az eszköz kétmintás Student-féle t-próbát végez. A t-próbának ez a formája abból indul ki, hogy a két adathalmaz azonos varianciájú eloszlásból származik, ezért homoszcedasztikus t-próbának is szokták nevezni. Ezt a módszert akkor használhatja, ha meg szeretné állapítani, hogy a két minta származhat-e azonos sokasági középértékű eloszlásból.

Kétmintás t-próba nem egyenlő szórásnégyzeteknél

Ez az eszköz kétmintás Student-féle t-próbát végez. A t-próbának ez a formája abból indul ki, hogy a két adathalmaz nem azonos varianciájú eloszlásból származik, ezért heteroszcedasztikus t-próbának is szokták nevezni. Az egyenlő varianciák esetéhez hasonlóan ez a t-próba is akkor használható, ha meg szeretné állapítani, hogy a két minta származhat-e azonos sokasági középértékű eloszlásból. Ezt a próbát akkor alkalmazhatja, ha a két minta egyedei különböznek. Az alább ismertetett párosított próbát akkor használhatja, ha egyetlen adatcsoportot vizsgál, és a két minta a kezelés előtti és utáni méréseket tartalmazza.

A t statisztikai érték az alábbi képlettel számítható ki.

A t érték számítási képlete

A alábbi képlet a szabadságfok kiszámítására szolgál. Mivel a számítás eredménye általában nem egész szám, a képlet a szabadságfokot a legközelebbi egész számra kerekíti, így a t-táblából megkapja a kritikus értéket. Az Excel T.PRÓB munkalapfüggvénye a számított szabadságfokot kerekítés nélkül használja, hiszen a T.PRÓB függvényben nem egész értékű szabadságfokkal is számolhat. A szabadságfok két különböző megközelítése miatt a T.PRÓB függvény és a t-próba analízis nem egyenlő varianciák esetén eltérő eredményt ad.

A szabadságfok közelítő értékét számító képlet

A Kétmintás z-próba a várható értékre eszköz kétmintás z-próbát hajt végre ismert varianciájú középértékeken. Ezzel az eszközzel ellenőrizhető az a nullhipotézis, hogy a két sokaság középértéke nem eltérő, megcáfolva ezzel mind az egyoldalú, mind a kétoldalú hipotéziseket. Ha a varianciák nem ismertek, a Z.PRÓB munkalapfüggvényt kell használnunk.

A z-próba használatakor fontos, hogy jól értelmezze az eredményt. A „P(Z <= z) egyoldalú próba” valójában a P(Z >= ABS(z)), vagyis annak a z értéknek a valószínűsége, amely a 0-tól távolabb, ugyanabban az irányban van, mint a vizsgált z érték, és a sokaság várható értékei között nincs eltérés. A „P(Z <= z) kétoldalú próba” valójában a P(Z >= ABS(z) vagy a Z <= -ABS(z)), vagyis annak a z értéknek a valószínűsége, amely a 0-tól bármely irányban távolabb van a vizsgált z értéknél, és a sokaság várható értékei között nincs eltérés. A kétoldalú próba eredménye az egyoldalú próba eredményének a kétszerese. A z-próba akkor is használható, ha a nullhipotézis az, hogy a két sokaság várható értékeinek különbsége egy adott, nullától különböző érték. A módszer alkalmas lehet például két autómodell teljesítménykülönbségének vizsgálatára.

További segítségre van szüksége?

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Lásd még

Hisztogram létrehozása az Excel 2016-ban

Pareto-diagram létrehozása az Excel 2016-ban

Videó megnézése az Analysis Toolpak és a Solver bővítmények telepítéséről és aktiválásáról

MÉRNÖKI függvények (segédlet)

STATISZTIKAI függvények (segédlet)

A képletek áttekintése az Excelben

Hibás képletek kiküszöbölése

Képletekben lévő hibák keresése és javítása

Az Excel billentyűparancsai és funkcióbillentyűi

Az Excel függvényeinek betűrendes listája

Az Excel függvényeinek kategória szerinti listája

Ismeretek bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×