Az eltérés százalékos fogalma két számérték különbségét jelenti százalékban. Mondjunk egy konkrét példát: mondjuk egyik nap 120 darab tablettát adtak el a nagykereskedelmi raktárból, másnap pedig 150 darabot. Az eladási mennyiségek közötti különbség szembetűnő, másnap 30 tablettával több kelt el. Ha 150-ből kivonjuk a 120-as számot, akkor megkapjuk az eltérést, ami egyenlő a +30 számmal. Felmerül a kérdés: mekkora a százalékos eltérés?

Hogyan lehet kiszámítani a százalékos eltérést Excelben

Az eltérés százalékát úgy számítjuk ki, hogy a régi értéket kivonjuk az új értékből, majd az eredményt elosztjuk a régi értékkel. A képlet Excelben történő kiszámításának eredményét a cella százalékos formátumában kell megjeleníteni. Ebben a példában a számítási képlet így néz ki (150-120)/120=25%. A képlet könnyen ellenőrizhető 120+25%=150.

Jegyzet! Ha felcseréljük a régi és az új számokat, akkor lesz egy képletünk a jelölés kiszámításához.

Az alábbi ábra egy példát mutat be a fenti számítás Excel-képletként való bemutatására. A D2 cellában található képlet kiszámítja az aktuális és a tavalyi eladások százalékos eltérését: =(C2-B2)/B2

Fontos figyelni a zárójelek jelenlétére ebben a képletben. Alapértelmezés szerint az Excelben az osztás mindig elsőbbséget élvez a kivonással szemben. Ezért, ha nem teszünk zárójeleket, akkor először az értéket osztjuk fel, majd egy másik értéket vonunk le belőle. Egy ilyen számítás (zárójelek nélkül) hibás lesz. A számítás első részének zárójeles képletben történő lezárása automatikusan magasabbra emeli a kivonási művelet elsőbbségét az osztási művelethez képest.

Helyesen, zárójelekkel írja be a képletet a D2 cellába, majd egyszerűen másolja át a D2:D5 tartomány többi üres cellájába. A képlet leggyorsabb másolásához vigye az egérkurzort a billentyűzet kurzorjelölőjére (a jobb alsó sarokba), így az egérkurzor nyílról fekete keresztre vált. Ezután kattintson duplán a bal egérgombbal, és az Excel automatikusan kitölti az üres cellákat a képlettel, miközben meghatározza a D2:D5 tartományt, amelyet a D5 celláig kell kitölteni, és nem tovább. Ez egy nagyon praktikus életmentő hack az Excelben.



Alternatív képlet az eltérési százalék kiszámításához az Excelben

Egy alternatív képletben, amely kiszámítja relatív eltérés a tárgyévi értékesítési értékeket azonnal elosztjuk az előző év eladási értékeivel, és csak ezután vonunk le egyet az eredményből: =C2/B2-1.

Amint az ábrán látható, az alternatív képlet kiszámításának eredménye megegyezik az előző képletével, ami azt jelenti, hogy helyes. De az alternatív képletet könnyebb leírni, bár lehet, hogy valakinek nehezebb lesz elolvasnia, hogy megértse, hogyan működik. Vagy nehezebb megérteni, hogy az adott képlet milyen értéket produkál a számítás eredményeként, ha nincs aláírva.

Ennek az alternatív képletnek az egyetlen hátránya, hogy nem lehet kiszámítani a százalékos eltérést a számlálóban vagy a helyettesítőben szereplő negatív számok esetén. Még ha az ABS függvényt használjuk is a képletben, a képlet hibás eredményt ad vissza negatív számmal a helyőrzőben.

Mivel az Excelben alapértelmezés szerint az osztás operátora elsőbbséget élvez a kivonás operátorral szemben, nincs szükség zárójelek használatára ebben a képletben.

Az átlagérték megtalálásához az Excelben (legyen az numerikus, szöveges, százalékos vagy egyéb érték), számos függvény létezik. És mindegyiknek megvan a maga sajátossága és előnyei. Hiszen ebben a feladatban bizonyos feltételek szabhatók.

Például egy számsorozat átlagértékeit az Excelben statisztikai függvényekkel számítják ki. Saját képletét manuálisan is megadhatja. Tekintsünk különböző lehetőségeket.

Hogyan találjuk meg a számok számtani középértékét?

A számtani átlag meghatározásához összeadja a halmaz összes számát, és elosztja az összeget a számmal. Például egy tanuló számítástechnikai osztályzatai: 3, 4, 3, 5, 5. Mi jár egy negyedévre: 4. A számtani átlagot a következő képlettel találtuk meg: \u003d (3 + 4 + 3 + 5 + 5) / 5.

Hogyan lehet gyorsan megtenni az Excel funkcióival? Vegyük például a sorozatot véletlen számok Sorban:

Vagy: aktiválja a cellát, és egyszerűen írja be kézzel a következő képletet: =ÁTLAG(A1:A8).

Most nézzük meg, mire képes még az AVERAGE függvény.

Határozza meg az első két és az utolsó három szám számtani középértékét! Képlet: =ÁTLAG(A1:B1;F1:H1). Eredmény:



Átlagos állapot szerint

A számtani átlag megtalálásának feltétele lehet numerikus vagy szöveges ismérv. A következő függvényt fogjuk használni: =AVERAGEIF().

Találd meg az átlagot számtani számok amelyek nagyobbak vagy egyenlőek 10-nél.

Függvény: =AVERAGEIF(A1:A8,">=10")

Az AVERAGEIF függvény ">=10" feltételre történő használatának eredménye:

A harmadik argumentum - "Átlagolási tartomány" - kimarad. Először is, nem kötelező. Másodszor, a program által elemzett tartomány CSAK számértékeket tartalmaz. Az első argumentumban megadott cellákban a keresés a második argumentumban megadott feltétel szerint történik.

Figyelem! A keresési feltétel megadható egy cellában. És a képletben hivatkozni rá.

Keressük meg a számok átlagos értékét a szöveges kritérium alapján! Például a termék átlagos eladásai "táblázatok".

A függvény így fog kinézni: =AVERAGEIF($A$2:$A$12;A7;$B$2:$B$12). Tartomány – termékneveket tartalmazó oszlop. A keresési feltétel egy hivatkozás a "táblázat" szót tartalmazó cellára (az A7 hivatkozás helyett beillesztheti a "táblázat" szót is). Átlagolási tartomány - azok a cellák, amelyekből az átlagérték kiszámításához adatokat veszik.

A függvény kiszámítása eredményeként a következő értéket kapjuk:

Figyelem! Szöveges kritériumhoz (feltételhez) meg kell adni az átlagolási tartományt.

Hogyan lehet kiszámítani a súlyozott átlagárat Excelben?

Honnan tudjuk a súlyozott átlagárat?

Képlet: =ÖSSZEG(C2:C12,B2:B12)/SZUM(C2:C12).

A SUMPRODUCT képlet segítségével a teljes árumennyiség értékesítése után megtudjuk a teljes bevételt. És a SUM függvény - összegzi az áruk mennyiségét. Az árueladásból származó teljes bevételt elosztva az áruk összértékével, megkaptuk a súlyozott átlagárat. Ez a mutató figyelembe veszi az egyes árak "súlyát". Részesedése az értékek össztömegében.

Szórás: képlet Excelben

Tegyen különbséget az általános sokaság és a minta szórása között. Az első esetben ez az általános variancia gyökere. A másodikban a mintavarianciából.

Ennek kiszámításához statisztikai mutató diszperziós képlet. A gyökeret veszik belőle. De az Excelben van egy kész függvény a szórás megtalálásához.

A szórás a forrásadatok skálájához kapcsolódik. Ez nem elegendő az elemzett tartomány változásának figuratív ábrázolásához. Az adatok relatív szóródási szintjének meghatározásához a variációs együtthatót számítjuk ki:

szórás / számtani átlag

Az Excel képlete így néz ki:

STDEV (értéktartomány) / AVERAGE (értéktartomány).

A variációs együtthatót százalékban számítják ki. Ezért a cellában beállítjuk a százalékos formátumot.

A szórás egyike azoknak a statisztikai kifejezéseknek a vállalati világban, amelyek növelik azoknak az embereknek a profilját, akik sikeresen elrontják ezt egy beszélgetésben vagy prezentációban, és homályos félreértést hagy azokban, akik nem tudják, mi az, de zavarban vannak kérdez. Valójában a legtöbb menedzser nem érti a szórás fogalmát, és ha te is közéjük tartozol, akkor ideje felhagynod a hazugságokkal. A mai cikkben megmutatom, hogy ez az alulértékelt statisztika hogyan segíthet jobban megérteni azokat az adatokat, amelyekkel dolgozik.

Mit mér a szórás?

Képzeld el, hogy két üzlet tulajdonosa vagy. A veszteségek elkerülése érdekében pedig fontos a készletek egyenlegének egyértelmű ellenőrzése. Annak érdekében, hogy megtudja, ki a legjobb részvénymenedzser, úgy dönt, hogy elemzi az elmúlt hat hét részvényeit. Mindkét üzlet készletének átlagos heti költsége megközelítőleg azonos, és körülbelül 32 hagyományos egység. A részvény átlagértéke első ránézésre azt mutatja, hogy mindkét menedzser egyformán dolgozik.

De ha jobban megnézzük a második üzlet tevékenységét, akkor láthatjuk, hogy bár az átlagérték megfelelő, a készlet változékonysága igen nagy (10-58 USD). Ebből az a következtetés vonható le, hogy az átlag nem mindig becsüli meg helyesen az adatokat. Itt jön be a szórás.

A szórás megmutatja, hogyan oszlanak meg az értékek a mi átlaghoz képest. Vagyis megértheti, mekkora a lefolyás hétről hétre.

Példánkban használtuk Excel funkció STDEV a szórási pontszám és az átlag kiszámításához.

Az első menedzser esetében a szórás 2 volt. Ez azt mutatja, hogy a mintában szereplő minden egyes érték átlagosan 2-vel tér el az átlagtól. Ez jó? Nézzük meg a kérdést más szemszögből – a 0 szórása azt jelzi, hogy a mintában minden érték megegyezik a középértékével (esetünkben 32,2). Például a 2-es szórás nem sokban különbözik a 0-tól, ami azt jelzi, hogy a legtöbb érték közel van az átlaghoz. Minél közelebb van a szórás a 0-hoz, annál megbízhatóbb az átlag. Ráadásul a 0-hoz közeli szórás az adatok csekély változékonyságát jelzi. Vagyis a 2-es szórással rendelkező lefolyási érték az első menedzser valószínűtlen konzisztenciáját jelzi.

A második üzlet esetében a szórás 18,9 volt. Azaz a lefolyás költsége hétről hétre átlagosan 18,9-el tér el az átlagos értéktől. Őrült terjedés! Minél távolabb van a szórás 0-tól, annál kevésbé pontos az átlag. Esetünkben a 18,9-es szám azt jelzi, hogy az átlagértékben (32,8 dollár hetente) egyszerűen nem lehet megbízni. Azt is elárulja, hogy a heti lefolyás nagyon változó.

Ez a szórás fogalma dióhéjban. Bár más fontos dolgokba nem ad betekintést statisztikai mérések(Mód, Medián…), valójában a szórás döntő szerepet játszik a legtöbb statisztikai számításban. A szórás elveinek megértése számos folyamat lényegére világít rá a tevékenységében.

Hogyan kell kiszámítani a szórást?

Tehát most már tudjuk, mit mond a szórása. Lássuk, hogy számít.

Tekintsünk egy 10-től 70-ig terjedő adathalmazt 10-es lépésekben. Amint látja, már kiszámoltam ezek szórását a H2 cellában található STDEV függvény segítségével (narancs).

Az alábbiakban bemutatjuk azokat a lépéseket, amelyekkel az Excel eléri a 21.6.

Kérjük, vegye figyelembe, hogy a jobb megértés érdekében minden számítás látható. Valójában az Excelben a számítás azonnali, minden lépést a színfalak mögött hagyva.

Az Excel először megkeresi a minta átlagát. Esetünkben az átlag 40-nek bizonyult, amit a következő lépésben minden mintaértékből levonunk. Minden kapott különbséget négyzetre emelünk és összeadunk. 2800-nak megfelelő összeget kaptunk, amit el kell osztani a mintaelemek számával mínusz 1. Mivel 7 elemünk van, így kiderül, hogy 2800-at el kell osztanunk 6-tal. Az eredményből megtaláljuk a négyzetgyököt, ezt a számot lesz a szórás.

Azok számára, akik nem teljesen világosak a szórás vizualizációval történő kiszámításának elvét illetően, matematikai értelmezést adok ennek az értéknek a megállapításához.

Szórás számítási függvények Excelben

Az Excelben többféle szórási képlet létezik. Csak be kell írnia az =STDEV parancsot, és meglátja.

Érdemes megjegyezni, hogy az STDEV.V és STDEV.G függvények (a lista első és második funkciója) megduplázzák az STDEV és STDEV függvényeket (a lista ötödik és hatodik funkciója), amelyeket megtartottunk a korábbiakkal való kompatibilitás érdekében. az Excel verziói.

Általánosságban elmondható, hogy a végződések különbsége: In és G függvények jelzik a minta vagy sokaság szórásának kiszámításának elvét. A két tömb közötti különbséget már az előzőben kifejtettem.

Az STDEV és STDEVPA függvények (a lista harmadik és negyedik funkciója) egyik jellemzője, hogy egy tömb szórásának kiszámításakor figyelembe veszik a logikai és szöveges értékeket. A szöveg és a valódi logikai értékek 1, a hamis logikai értékek pedig 0. Nehezen tudok elképzelni olyan helyzetet, amikor szükségem lenne erre a két függvényre, ezért úgy gondolom, hogy figyelmen kívül hagyhatók.

A szórás függvény már kikerült a kategóriából felsőbb matematika statisztikákkal kapcsolatban. Az Excelben számos lehetőség van a szórás függvény használatára:

• STDEV funkció.
• STDEV funkció.
• STDEV funkció

Szükségünk lesz ezekre a függvényekre az értékesítési statisztikákban az értékesítés stabilitásának azonosításához (XYZ elemzés). Ezek az adatok mind az árazáshoz, mind a szortiment mátrix kialakításához (korrekciójához) és egyéb hasznos értékesítési elemzésekhez felhasználhatók, amelyekről a következő cikkekben mindenképpen szó lesz.

Előszó

Nézzük először matematikai nyelven a képleteket, majd (a szövegben lent) elemezzük részletesen Excelben a képletet, és az így kapott eredményt hogyan alkalmazzuk az értékesítési statisztikák elemzésében.

Tehát a Standard Deviation a szórás becslése valószínűségi változó xőt illetően matematikai elvárás szórásának elfogulatlan becslése alapján)))) Ne félj az érthetetlen szavaktól, légy türelmes, és mindent meg fogsz érteni!

A képlet leírása: A szórást magának a valószínűségi változónak egységeiben mérjük, és a számtani átlag standard hibájának számításakor, konfidenciaintervallumok felépítésénél, hipotézisek statisztikai tesztelésekor, valószínűségi változók közötti lineáris kapcsolat mérésénél használjuk. Egy valószínűségi változó varianciájának négyzetgyöke

Most a szórás egy valószínűségi változó szórásának becslése x a variancia elfogulatlan becslésén alapuló matematikai elvárása tekintetében:

diszperzió;

- én-adik mintaelem;

Minta nagysága;

Minta aritmetikai átlag:

Meg kell jegyezni, hogy mindkét becslés elfogult. Általános esetben lehetetlen torzítatlan becslést készíteni. Az elfogulatlan varianciabecslésen alapuló becslés azonban konzisztens.

három szigma szabály() - egy normális eloszlású valószínűségi változó szinte minden értéke az intervallumban található. Pontosabban, körülbelül 0,9973 valószínűséggel egy normális eloszlású valószínűségi változó értéke a megadott intervallumban található (feltéve, hogy az érték igaz, és nem a mintafeldolgozás eredményeként kaptuk meg). 0,1-es kerekített intervallumot fogunk használni

Ha a valódi érték ismeretlen, akkor a not, de adjon meg s. Ily módon három szabálya a szigmát a három szabályává alakítjuk s. Ez a szabály segít meghatározni az eladások stabilitását, de erről majd később...

Most a szórásfüggvény az Excelben

Remélem nem nyomtalak el a matekkal? Talán valaki ez az információ absztrakt vagy más célra lesz szükség. Most rágódjunk azon, hogyan működnek ezek a képletek az Excelben...

Az értékesítés stabilitásának meghatározásához nem kell elmélyednünk a szórásfüggvények összes lehetőségében. Csak egyet fogunk használni:

STDEV funkció

STDEV(szám1;2. számú;... )

Number1, Number2,...- 1-30 numerikus argumentum, amely megfelel az általános sokaságnak.

Most nézzünk egy példát:

Hozzunk létre egy könyvet és egy rögtönzött táblázatot. Ezt a példát a cikk végén Excelben töltheti le.

Folytatjuk!!!

Szia ismét. Jól!? Van egy szabad perc. Folytassuk?

És így a stabilitás az értékesítés segítségével STDEV funkciók

Az érthetőség kedvéért vegyünk néhány rögtönzött árut:

Az analitikában, legyen szó előrejelzésről, kutatásról vagy valami más statisztikával kapcsolatos dologról, mindig három periódusra van szükség. Ez lehet egy hét, hónap, negyedév vagy év. Lehetséges és még a legjobb is, ha minél több időszakot veszünk, de nem kevesebbet háromnál.

Kifejezetten eltúlzott eladásokat mutattam be, ahol szabad szemmel lehet látni, hogy mit árulnak folyamatosan és mit nem. Ez megkönnyíti a képletek működésének megértését.

Így vannak eladásaink, most ki kell számítanunk az átlagos értékesítési értékeket időszakonként.

Átlagérték képlet ÁTLAG(időszak adat) az én esetemben a képlet így néz ki =ÁTLAG(C6:E6)

Minden terméknél kinyújtjuk a képletet. Ezt úgy tehetjük meg, hogy a kijelölt cella jobb sarkát lenyomva húzzuk a lista végére. Vagy vigye a kurzort a terméket tartalmazó oszlopra, és nyomja meg a következő billentyűkombinációkat:

Ctrl + Le mozgassa a kurzort a lista aljára.

Ctrl + Jobb, a kurzor a táblázat jobb oldalára kerül. Még egyszer jobbra, és eljutunk a képletet tartalmazó oszlophoz.

Most szorítjuk

Ctrl+Shift és nyomd felfelé. Tehát kiválasztjuk a képlet nyújtásának területét.

A Ctrl + D billentyűkombináció pedig ott nyújtja a funkciót, ahol szükségünk van rá.

Ne felejtse el ezeket a kombinációkat, ezek valóban növelik az Excel sebességét, különösen akkor, ha nagy tömbökkel dolgozik.

A következő lépés, maga a szórásfüggvény, mint mondtam, csak egyet fogunk használni STDEV

Előírjuk a függvényt, és a függvényértékekbe beleírjuk az egyes időszakok értékesítési értékeit. Ha a táblázatban egymás után szerepelnek eladások, használhatja a tartományt, mint az én képletemben az =SDV(C6:E6), vagy pontosvesszővel listázhatja ki a szükséges cellákat =SDV(C6;D6;E6)

Itt az összes számítás és kész. De honnan tudod, hogy mi ad el folyamatosan és mi nem? Tegyük le az XYZ konvenciót, ahol

X stabil

Y - kis eltérésekkel

Z - nem stabil

Ehhez hibaintervallumokat használunk. ha az ingadozások 10%-on belül jelentkeznek, akkor azt feltételezzük, hogy az eladások stabilak.

Ha 10 és 25 százalék között van, akkor Y lesz.

És ha az eltérési értékek meghaladják a 25%-ot - ez nem stabilitás.

Az egyes termékek betűinek helyes beállításához az IF képletet használjuk részletesebben. az én asztalomban adott funkciótígy fog kinézni:

IF(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Ennek megfelelően minden névre kinyújtjuk az összes képletet.

Megpróbálok azonnal válaszolni arra a kérdésre, hogy miért a 10%-os és a 25%-os intervallum?

Valójában az intervallumok eltérőek lehetnek, minden az adott feladattól függ. Kifejezetten eltúlzott eladási értékeket mutattam be, ahol "szemmel" látszik a különbség. Nyilvánvaló, hogy az 1. terméket nem értékesítik folyamatosan, de a dinamika az eladások növekedését mutatja. Hagyja békén ezt az elemet...

De a 2. termék már destabilizálódik az arcon. Számításaink pedig Z-t mutatnak, ami az eladások instabilitásáról árulkodik. A 3. és 5. tétel stabil teljesítményt mutat, kérjük, vegye figyelembe, hogy az eltérés 10%-on belül van.

Azok. A 45, 46 és 45 pontszámú 5. tétel 1%-os eltérést mutat, ami egy stabil számsor.

De a 2. termék 10, 50 és 5 pontszámmal 93%-os eltérést mutat, ami NEM egy stabil számsor.

A számítások elvégzése után szűrőt rakhatunk, és kiszűrhetjük a stabilitást, így ha több ezer cikkből áll a táblázatunk, akkor könnyen kiválaszthatjuk, hogy melyik nem stabil az értékesítésben, vagy éppen ellenkezőleg, melyik stabil.

Az "Y" nem működött a táblázatomban, azt hiszem, a számsorok egyértelműsége érdekében hozzá kell adni. Kisorsolom a 6. árut...

Ugyanis a 40, 50 és 30 számsorok 20%-os eltérést mutatnak. Úgy tűnik, nincs nagy hiba, de így is jelentős a terjedés...

És így összegezve:

10,50,5 - Z nem stabil. 25% feletti eltérés

40,50,30 - Y odafigyelhet erre a termékre és javíthatja eladásait. 25%-nál kisebb, de 10%-nál nagyobb eltérés

45,46,45 - X a stabilitás, ezzel a termékkel még semmit sem kell tenni. 10%-nál kisebb eltérés

Ez minden! Remélem mindent érthetően elmagyaráztam, ha nem, kérdezz, ami nem világos. És hálás leszek minden megjegyzésért, legyen az dicséret vagy kritika. Így tudni fogom, hogy olvasol engem és téged, ami nagyon FONTOS, érdekes. És ennek megfelelően új leckék jelennek meg.

Andrej Lipov

Egyszerűen fogalmazva, a szórás megmutatja, hogy egy instrumentum ára mennyit ingadozik az idő múlásával. Vagyis minél nagyobb ez a mutató, annál erősebb számos érték volatilitása vagy változékonysága.

A szórással értékhalmazokat lehet és kell is elemezni, hiszen két látszólag azonos átlagú halmaz az értékek szórása szempontjából teljesen eltérőnek bizonyulhat.

Példa

Vegyünk két sor számot.

a) 1,2,3,4,5,6,7,8,9. Átlagos - 5. Art. eltérés = 2,7386

b) 20.1.7.1.15, -1, -20.4, 18.5. Átlagos - 5. Art. eltérés = 12,2066

Ha nem tartja a teljes számsort a szeme előtt, akkor a szórás azt mutatja, hogy "b" esetén az értékek sokkal jobban szétszóródnak az átlagos értékük körül.

Nagyjából a "b" sorban az érték 5 plusz-mínusz 12 (átlagosan) - nem pontosan, de elárulja a jelentést.

A szórás kiszámítása

A szórás kiszámításához használhatja a befektetési alapok hozamainak szórásának kiszámításából kölcsönzött képletet:

Itt N az értékek száma,
DOHaverage – az összes érték átlaga,
DOH periódus - N értéke.

Az Excelben a megfelelő függvény neve STDEV (vagy a program angol verziójában STDEV).

A lépésről lépésre szóló utasítások a következők:

1. Számítsd ki egy számsorozat átlagát!
2. Minden értéknél határozza meg az átlag és az adott érték közötti különbséget.
3. Számítsa ki ezeknek a különbségeknek a négyzetösszegét!
4. A kapott összeget osszuk el a sorozatban szereplő számok számával.
5. Vegyük az utolsó bekezdésben kapott szám négyzetgyökét.

Ismerősei hasznot húznak ebből az információból. Oszd meg velük!