Az Excel programot a profik és az amatőrök egyaránt nagyra értékelik, mert bármilyen képzettségi szintű felhasználó dolgozhat vele. Például bárki, aki minimális „kommunikációs” készségekkel rendelkezik az Excelben, tud egyszerű grafikont rajzolni, tisztességes táblát készíteni stb.
Ugyanakkor ez a program lehetővé teszi különféle típusú számítások, például számítások elvégzését is, de ez kissé eltérő szintű képzést igényel. Ha azonban csak most kezdte el közelebbről megismerkedni ezzel a programmal, és minden érdekli, ami segít haladóbb felhasználóvá válni, akkor ez a cikk neked szól. Ma elmondom, mi az Excel szórásképlete, miért van rá egyáltalán szükség, és szigorúan véve mikor használják. Megy!
Ami
Kezdjük az elmélettel. A szórást általában négyzetgyöknek nevezik, amelyet a rendelkezésre álló mennyiségek közötti különbségek négyzetes átlagából, valamint azok számtani átlagából kapunk. Egyébként ezt az értéket általában görög „szigma” betűnek nevezik. A szórást a STANDARDEVAL képlet segítségével számítjuk ki, ennek megfelelően a program ezt a felhasználó számára elvégzi.
Ennek a koncepciónak az a lényege, hogy azonosítsa egy instrumentum variabilitásának mértékét, vagyis a maga módján leíró statisztikákból származó mutató. Azonosítja az instrumentum volatilitásában bekövetkezett változásokat egy bizonyos időtartam alatt. Az STDEV képletek felhasználhatók egy minta szórásának becslésére, figyelmen kívül hagyva a logikai és a szöveges értékeket.
Képlet
Az Excelben automatikusan megadott képlet segít a szórás kiszámításában az Excelben. Megtalálásához meg kell találnia a képlet szakaszt az Excelben, majd ki kell választania a STANDARDEVAL nevűt, tehát nagyon egyszerű.
Ezt követően egy ablak jelenik meg Ön előtt, amelyben meg kell adnia az adatokat a számításhoz. Különösen két számot kell beírni a speciális mezőkbe, amelyek után maga a program kiszámítja a minta szórását.
Kétségtelen, hogy a matematikai képletek és számítások meglehetősen összetett problémát jelentenek, és nem minden felhasználó képes azonnal megbirkózni vele. Ha azonban egy kicsit mélyebbre ásunk, és kicsit részletesebben megvizsgáljuk a kérdést, kiderül, hogy nem minden olyan szomorú. Remélem, hogy a szórás számításának példájával meggyőződött erről.
Videó segítségül
A statisztikai elemzés egyik fő eszköze a szórás számítása. Ez a mutató lehetővé teszi egy minta vagy egy sokaság szórásának becslését. Tanuljuk meg a szórásképlet használatát az Excelben.
Azonnal határozzuk meg, mi a szórás, és hogyan néz ki a képlete. Ez a mennyiség a sorozat összes mennyisége és számtani középértéke közötti különbség négyzetének számtani középértékének négyzetgyöke. Ennek a mutatónak azonos neve van - szórás. Mindkét név teljesen egyenértékű.
De természetesen az Excelben a felhasználónak ezt nem kell kiszámítania, mivel a program mindent megtesz helyette. Tanuljuk meg, hogyan kell kiszámítani a szórást az Excelben.
Számítás Excelben
A megadott értéket az Excelben két speciális függvény segítségével számíthatja ki STDEV.V(a minta sokasága alapján) és STDEV.G(az általános populáció alapján). Működésük elve teljesen azonos, de háromféleképpen nevezhetjük őket, amelyeket az alábbiakban tárgyalunk.
1. módszer: Funkcióvarázsló
2. módszer: Képletek Tab
3. módszer: A képlet manuális bevitele
Van olyan módszer is, amellyel egyáltalán nem kell meghívnia az argumentumok ablakát. Ehhez manuálisan kell megadnia a képletet.
Amint láthatja, az Excelben a szórás kiszámításának mechanizmusa nagyon egyszerű. A felhasználónak csak számokat kell megadnia a sokaságból vagy hivatkozásokat az ezeket tartalmazó cellákra. Minden számítást maga a program hajt végre. Sokkal nehezebb megérteni, hogy mi a számított mutató, és a számítási eredmények hogyan alkalmazhatók a gyakorlatban. De ennek megértése már inkább a statisztika területére vonatkozik, mint a szoftverekkel való munka megtanulására.
Ennek a cikknek az a célja, hogy bemutassa, mint a könyvekben és cikkekben előforduló matematikai képletek, az Excelben elemi függvényekre bomlanak le.
Ebben a cikkben a képleteket elemezzük szórást és szórást, és kiszámítja azokat Excelben.
Mielőtt rátérnénk a szórás számítására és a képlet elemzésére, célszerű megérteni az alapvető statisztikai mutatókat és a jelöléseket.
Az előrejelzési modellek képleteit figyelembe véve a következő mutatókkal fogunk találkozni:
Például van egy idősorunk – heti értékesítés mértékegységben.
|
Egy hét |
||||||||||
|
Szállítás, db |
Ennél az idősornál i=1, n=10, 
,
Tekintsük az átlagérték képletét:
|
Egy hét |
||||||||||
|
Szállítás, db |
Idősorainkhoz az átlagértéket határozzuk meg
A trendek azonosításához az átlagérték mellett az is érdekes, hogy a megfigyelések mennyire szóródnak az átlaghoz képest. A szórás a megfigyelések átlagtól való eltérésének mértékét mutatja.
A minta szórásának kiszámításának képlete a következő:
Bontsuk fel a képletet alkotórészeire, és számítsuk ki a szórást Excelben, példaként az idősorunkkal.
1. Számítsa ki ennek átlagértékét az Excel képlet segítségével = ÁTLAG(B11:K11)
2. Határozza meg a sorozat egyes értékeinek eltérését az átlaghoz képest!
az első hétre = 6-10=-4
a második hétre = 10-10=0
harmadoknál = 7-1=-3 stb.
3. A sorozat minden értékéhez meghatározzuk a sorozat értékeinek átlagtól való eltérésének négyzetes különbségét.
az első hétre = (-4)^2=16
a második hétre = 0^2=0
harmadoknál = (-3)^2=9 stb.
4. Számítsa ki az értékek átlaghoz viszonyított eltéréseinek négyzetes összegét! 
a =SUM(tartományhivatkozás (tartományhivatkozás a következővel) képlet használatával
átlagos érték- ez a statisztikai sokaság általános mutatója, amely kiküszöböli a statisztikai mennyiségek értékeinek egyéni különbségeit, lehetővé téve a különböző populációk összehasonlítását egymással.
Létezik 2 osztályátlagos értékek: és .
A strukturális átlagok közé tartozik divatÉs középső, de leggyakrabban használt teljesítmény átlagok különféle típusok.
Teljesítményátlagok
Teljesítményátlagok lehetnek egyszerűÉs súlyozott.
Egyszerű átlag akkor számítjuk ki, ha kettő vagy több van csoportosítatlan statisztikai mennyiségek véletlenszerű sorrendben a következő általános képlet szerint:
Súlyozott átlagáltal számított csoportosítva statisztikai értékeket a következő általános képlet segítségével:
ahol X az egyes statisztikai értékek értékei vagy a csoportosítási intervallumok közepe;
m egy kitevő, amelynek értéke a következőket határozza meg teljesítményátlagok típusai:
ahol m = -1;
m értéke 0;
ha m = 1;
m értéke 2;
m = 3-nál.
A különböző m kitevők egyszerű és súlyozott átlagainak általános képleteit használva minden típushoz sajátos képleteket kapunk, amelyeket az alábbiakban részletesen tárgyalunk.
Számtani átlaga
Számtani átlaga- ez a leggyakrabban használt átlagérték, amelyet az általános képletbe m=1 behelyettesítésével kapunk. Számtani átlaga egyszerű a következő formája van:
ahol X azoknak a mennyiségeknek az értékei, amelyekre az átlagértéket ki kell számítani; N az X értékek teljes száma (a vizsgált populáció egységeinek száma).
Például egy diák 4 vizsgát tett, és a következő osztályzatokat kapta: 3, 4, 4 és 5. Számítsuk ki az átlagpontszámot az egyszerű számtani átlaggal: (3+4+4+5)/4 = 16/4 = 4.
Számtani átlaga súlyozott a következő formája van:
Ahol f az azonos értékű X (gyakoriság) mennyiségek száma.
Például egy diák 4 vizsgát tett, és a következő osztályzatokat kapta: 3, 4, 4 és 5. Számítsuk ki az átlagpontszámot a súlyozott számtani átlaggal: (3*1 + 4*2 + 5*1)/4 = 16/4 = 4.
Ha az X értékeket intervallumként adjuk meg, akkor a számításokhoz az X intervallumok felezőpontjait használjuk, amelyeket az intervallum felső és alsó határának fele összegeként határozunk meg. És ha az X intervallumnak nincs alsó vagy felső határa (nyitott intervallum), akkor ennek megtalálásához használja a szomszédos X intervallum tartományát (a felső és alsó határ közötti különbséget).
Például egy vállalkozásnak 10 alkalmazottja van legfeljebb 3 éves tapasztalattal, 20 alkalmazottja 3-5 éves tapasztalattal, 5 alkalmazottja több mint 5 éves tapasztalattal. Ezután kiszámítjuk az alkalmazottak átlagos szolgálati idejét a súlyozott számtani átlaggal, X-nek a szolgálati időintervallumok (2, 4 és 6 év) felezőpontját véve:
(2*10+4*20+6*5)/(10+20+5) = 3,71 év.
A leggyakrabban a számtani átlagot használják, de vannak esetek, amikor más típusú átlagok használata szükséges. Nézzük tovább az ilyen eseteket.
Harmonikus átlag
Harmonikus átlag akkor használatos, ha a forrásadatok nem tartalmaznak f gyakoriságot az egyes X értékekhez, hanem azok Xf szorzataként jelennek meg. Ha Xf=w-t jelöltünk ki, akkor f=w/X-et fejezünk ki, és ezeket a jelöléseket behelyettesítve a számtani súlyozott átlag képletébe, megkapjuk a harmonikus súlyozott átlag képletét:
Így a súlyozott harmonikus átlagot használjuk, ha az f frekvenciák ismeretlenek, és a w=Xf ismert. Azokban az esetekben, amikor minden w = 1, azaz X egyedi értékei egyszer fordulnak elő, az átlagos harmonikus prímképletet alkalmazzuk:
Például egy autó 90 km/h sebességgel haladt A pontból B pontba, majd vissza 110 km/h sebességgel. Az átlagsebesség meghatározásához az átlagos harmonikus egyszerű képletét alkalmazzuk, mivel a példában a w 1 =w 2 távolság adott (A pont és B pont távolsága megegyezik B-től A-val). egyenlő a sebesség (X) és az idő (f) szorzatával. Átlagsebesség = (1+1)/(1/90+1/110) = 99 km/h.
Geometriai átlag
Geometriai átlag Az átlagos relatív változások meghatározásához használják, a Dinamikus sorozatok témakörben tárgyaltak szerint. A geometriai átlag akkor adja a legpontosabb átlagolási eredményt, ha az a feladat, hogy olyan X értéket találjunk, amely egyenlő távolságra lenne X maximális és minimális értékétől.
Például 2005 és 2008 között inflációs index Oroszországban: 2005-ben - 1,109; 2006-ban - 1090; 2007-ben - 1119; 2008-ban - 1133. Mivel az inflációs index relatív változás (dinamikus index), az átlagértéket a geometriai átlaggal kell kiszámítani: (1,109*1,090*1,119*1,133)^(1/4) = 1,1126, azaz a 2005-től kezdődő időszakra. 2008-ra az éves árak átlagosan 11,26%-kal nőttek. A számtani átlagot használó hibás számítás 11,28%-os hibás eredményt adna.
Közepes négyzet
Közepes négyzet Olyan esetekben használják, amikor az X kezdeti értéke pozitív és negatív is lehet, például az átlagos eltérések kiszámításakor.
A másodfokú átlag fő alkalmazása az X értékek változásának mérése, amelyről szó lesz.
Átlagos köbméter
Átlagos köbméter rendkívül ritkán használják, például az ENSZ által javasolt és kiszámított fejlődő országok (TIN-1) és fejlett országok (TIN-2) szegénységi indexének kiszámításakor.
Strukturális átlagok
A leggyakrabban használthoz szerkezeti átlag tartalmazza és .
Statisztikai mód
Statisztikai mód az X leggyakrabban ismétlődő értéke egy statisztikai sokaságban.
Ha X adott diszkréten, akkor az üzemmód számítás nélkül kerül meghatározásra a legmagasabb frekvenciájú jellemző értékeként. Egy statisztikai sokaságban 2 vagy több módozat van, akkor azt veszik figyelembe bimodális(ha két mód van) ill kombinált(ha kettőnél több mód van), és ez a populáció heterogenitását jelzi.
Például a cég 16 főt foglalkoztat: ebből 4 fő 1 éves, 3 fő 2 éves, 5 fő 3 év, 4 fő pedig 4 év gyakorlattal rendelkezik. Így a modális tapasztalat Mo = 3 év, mivel ennek az értéknek a gyakorisága maximális (f = 5).
Ha X adott egyenlő időközönként, akkor a modális intervallumot először a legnagyobb f frekvenciájú intervallumként definiáljuk. Ezen az intervallumon belül a mód feltételes értékét a következő képlet segítségével találjuk meg:
Ahol Mo a divat;
X NMo – a modális intervallum alsó határa;
h Mo a modális intervallum tartománya (a felső és alsó határa közötti különbség);
f Mo – a modális intervallum gyakorisága;
f Mo-1 – a modálist megelőző intervallum gyakorisága;
f Mo+1 – a modálist követő intervallum gyakorisága.
Például egy vállalkozásnak 10 alkalmazottja van legfeljebb 3 éves tapasztalattal, 20 alkalmazottja 3-5 éves tapasztalattal, 5 alkalmazottja több mint 5 éves tapasztalattal. Számítsuk ki a modális munkatapasztalatot a 3-5 év közötti modális intervallumban: Mo = 3 + 2*(20-10)/(2*20-10-5) = 3,8 (év).
Ha a h intervallum tartománya eltérő, akkor f frekvenciák helyett intervallumsűrűségeket kell használni, amelyeket úgy számítunk ki, hogy az f frekvenciákat elosztjuk a h intervallum tartományával.
Statisztikai medián
Statisztikai medián– ez az X mennyiség értéke, amely egy növekvő vagy csökkenő sorrendben rendezett statisztikai sokaságot 2 egyenlő részre oszt. Ennek eredményeként az egyik fele nagyobb, mint a medián, a másik fele pedig kisebb, mint a medián.
Ha X adott diszkréten, majd a medián meghatározásához minden értéket 0-tól N-ig számozunk növekvő sorrendben, akkor a páros N szám mediánja a 0,5N és a (0,5N+1) X között lesz középen, páratlan N szám esetén pedig a 0,5(N+1) számmal rendelkező X értékének felel meg. .
Például vannak adatok a részidős hallgatók életkoráról egy 10 fős csoportban - X: 18, 19, 19, 20, 21, 23, 23, 25, 28, 30 év. Ezek az adatok már növekvő sorrendben vannak rendezve, és számuk N=10 páros, így a medián X között lesz 0,5*10=5 és (0,5*10+1)=6 számokkal, amelyek megfelelnek az értékeknek. X 5 = 21 és X 6 =23, akkor a medián: Me = (21+23)/2 = 22 (év).
Ha X szerepel az űrlapon egyenlő időközönként, akkor először meghatározzuk a medián intervallumot (az az intervallum, amelyben az f gyakoriságok egyik fele véget ér és a másik fele kezdődik), amelyben a medián feltételes értékét a következő képlet segítségével találjuk meg:
Ahol Én a medián;
X НМе – a medián intervallum alsó határa;
h Ме – a medián intervallum tartománya (a felső és alsó határa közötti különbség);
f Ме – a medián intervallum gyakorisága;
f Ме-1 – a mediánt megelőző intervallumok gyakoriságának összege.
A korábban tárgyalt példában a modális szolgálati idő számításánál (a vállalkozásnak 10 fő legfeljebb 3 éves gyakorlattal, 20 fő 3-5 év gyakorlattal, 5 fő 5 év feletti gyakorlattal) a mediánt számoljuk. szolgálati idő. A teljes létszám fele (10+20+5)/2 = 17,5 és 3 és 5 év közötti intervallumban van, és az első intervallumban 3 évig csak 10 fő, az első kettőben pedig - (10+20) =30, ami több mint 17,5, azt jelenti, hogy a 3 és 5 év közötti intervallum a medián. Ezen belül meghatározzuk a medián feltételes értékét: Me = 3+2*(0,5*30-10)/20 = 3,5 (év).
Csakúgy, mint a módus esetében, a medián meghatározásakor, ha a h intervallum tartománya eltérő, akkor f gyakoriságok helyett intervallumsűrűségeket kell használni, amelyeket úgy számítunk ki, hogy az f gyakoriságokat elosztjuk a h intervallum tartományával.
Változási mutatók
Variáció a statisztikai sokaság egyes egységeinek X-értékeinek különbsége. A változás erősségének vizsgálatához a következőket számítjuk ki változási mutatók: , , , , .
Variációs tartomány
Variációs tartomány a különbség a vizsgált statisztikai sokaságban elérhető X maximális és minimális értéke között:
A H hátránya, hogy csak az X értékek maximális eltérését mutatja, és nem tudja mérni a variáció erősségét a teljes populációban.
Átlagos lineáris eltérés
Átlagos lineáris eltérés az X értékek eltérésének átlagos modulusa a számtani átlagtól. Az aritmetikai átlag képlettel számítható ki egyszerű- kapunk :
Például egy diák 4 vizsgát tett, és a következő osztályzatokat kapta: 3, 4, 4 és 5. = 4. Számítsuk ki az egyszerű átlagos lineáris eltérést: L = (|3-4|+|4-4|+|4 -4|+|5-4|)/4 = 0,5.
Ha az X forrásadatokat csoportosítjuk (f gyakoriságok vannak), akkor az átlagos lineáris eltérést a számtani középképlet segítségével számítjuk ki. súlyozott- kapunk :
Térjünk vissza egy diák példájához, aki 4 vizsgát tett, és a következő osztályzatokat kapta: 3, 4, 4 és 5. = 4 és = 0,5. Számítsuk ki a súlyozott átlagos lineáris eltérést: L = (|3-4|*1+|4-4|*2+|5-4|*1)/4 = 0,5.
Lineáris variációs együttható
Lineáris variációs együttható az átlagos lineáris eltérés és a számtani átlag aránya:
A lineáris variációs együttható segítségével összehasonlíthatja a különböző populációk változását, mert az átlagos lineáris eltéréssel ellentétben ennek értéke nem függ az X mértékegységektől.
A vizsgált példában egy olyan tanulóról, aki 4 vizsgát tett, és a következő osztályzatokat kapta: 3, 4, 4 és 5, a lineáris variációs együttható 0,5/4 = 0,125 vagy 12,5%.
Diszperzió
Diszperzió az X értékek számtani átlagtól való eltérésének átlagos négyzete. A diszperzió kiszámítható a számtani átlag képlettel egyszerű- kapunk egyszerű szórás:
A már ismert példában egy diákról, aki 4 vizsgát tett, és 3, 4, 4 és 5, = 4 osztályzatot kapott. Ekkor a szórás egyszerű D = ((3-4) 2 +(4-4) 2 +(4-4) 2 +(5-4) 2)/4 = 0,5.
Ha az eredeti X adatokat csoportosítjuk (f gyakoriságok vannak), akkor a szórást a számtani átlag képlettel számítjuk súlyozott- kapunk szórással súlyozott:
A vizsgált példában egy diákról, aki 4 vizsgát tett, és a következő osztályzatokat kapta: 3, 4, 4 és 5, kiszámítjuk a súlyozott szórást: D = ((3-4) 2 *1+(4-4) 2 *2+(5-4)2*1)/4 = 0,5.
Ha átalakítja a varianciaképletet (nyitja a zárójelet a számlálóban, tagonként osztja el a nevezőt, és hasonlókat ad), akkor egy másik képletet kaphat a kiszámításához az átlagnégyzetek és a négyzetes átlag különbségeként:
Még könnyebb megtalálni szórás, ha a variancia ennek négyzetgyökeként van előre kiszámítva:
A fenti diákról szóló példában a szórást ennek négyzetgyökeként találjuk:.
Másodfokú variációs együttható
Másodfokú variációs együttható a variáció legnépszerűbb relatív mértéke:
Kritériumérték A V másodfokú variációs együttható 0,333 vagy 33,3%, vagyis ha V kisebb vagy egyenlő, mint 0,333, akkor a szórást gyengének, ha pedig nagyobb, mint 0,333, akkor erősnek. Erős szórás esetén a vizsgált statisztikai sokaságot vesszük figyelembe heterogén, és az átlagos érték atipikusés nem használható e populáció általános mutatójaként.
A tanulóra vonatkozó példában, amelyben a fenti , a V = 0,707/4 = 0,177 másodfokú variációs együtthatót találjuk, amely kisebb, mint a 0,333-as kritériumérték, ami azt jelenti, hogy a szórás gyenge és egyenlő 17,7%-kal.
Amellett, hogy a matematikai elvárás egy valószínűségi változó, amely. meghatározza a valószínűségi eloszlás középpontjának helyzetét, a valószínűségi változó eloszlásának mennyiségi jellemzője a valószínűségi változó diszperziója
A diszperziót D [x] vagy -vel fogjuk jelölni.
A diszperzió szó szóródást jelent. A diszperzió a diszperzió numerikus jellemzője, egy valószínűségi változó értékeinek terjedése a matematikai elvárásokhoz képest.
Definíció 1. Egy valószínűségi változó varianciája a valószínűségi változó és a matematikai elvárása közötti különbség négyzetének matematikai elvárása (azaz a megfelelő középpontos valószínűségi változó négyzetének matematikai elvárása):
A variancia a valószínűségi változó négyzetének dimenziója. Néha a diszperzió jellemzésére célszerűbb olyan mennyiséget használni, amelynek mérete egybeesik egy valószínűségi változó dimenziójával. Ez az érték a szórás.
Definíció 2. Egy valószínűségi változó négyzetes eltérésének négyzetgyöke a varianciájának négyzetgyöke:
vagy bővített formában
A szórást is jelöljük
Megjegyzés 1. A variancia számításakor az (1) képlet kényelmesen átalakítható a következőképpen:
azaz a szórás egyenlő a valószínűségi változó négyzetének matematikai elvárása és a valószínűségi változó matematikai elvárása négyzete közötti különbséggel.
1. példa: Egy lövést adnak le egy tárgyra. A találat valószínűsége. Határozza meg a matematikai elvárást, szórást és szórást!
Megoldás. Találatszámértékek táblázatának elkészítése
Ennélfogva,
A szórás és a szórás fogalmának mint egy valószínűségi változó diszperziójának jellemzőinek bemutatásához tekintsünk példákat.
2. példa Egy valószínűségi változót a következő eloszlási törvény ad meg (lásd a táblázatot és a 413. ábrát):
3. példa Egy valószínűségi változót a következő eloszlási törvény adja meg (lásd a táblázatot és a 414. ábrát):
Határozza meg: 1) matematikai elvárást, 2) diszperziót, 3) szórást.
A valószínűségi változó diszperziója, szórása az első példában kisebb, mint a második példában szereplő valószínűségi változó szórása (lásd 414. és 415. ábra). Ezen értékek szórása 0,6 és 2,4.
4. példa; A valószínűségi változót a következő eloszlási törvény adja (lásd a táblázatot és a 415. ábrát):
Határozza meg: 1) matematikai elvárást, 2) diszperziót, 3) szórást.
