Excel standartinis nuokrypis. Kaip apskaičiuoti variacijos koeficientą ir kitą statistiką programoje „Excel“.

30.03.2024

Viena iš pagrindinių statistinės analizės priemonių yra standartinio nuokrypio skaičiavimas. Šis indikatorius leidžia įvertinti imties arba visumos standartinį nuokrypį. Išmokime naudoti standartinio nuokrypio formulę „Excel“.

Iš karto nustatykime, koks yra standartinis nuokrypis ir kaip atrodo jo formulė. Šis dydis yra kvadratinė šaknis iš visų eilutės dydžių ir jų aritmetinio vidurkio skirtumo kvadratų aritmetinio vidurkio. Yra identiškas šio rodiklio pavadinimas – standartinis nuokrypis. Abu pavadinimai yra visiškai lygiaverčiai.

Tačiau, žinoma, „Excel“ vartotojas neturi to skaičiuoti, nes programa viską daro už jį. Išmokime apskaičiuoti standartinį nuokrypį „Excel“.

Skaičiavimas Excel programoje

Nurodytą reikšmę galite apskaičiuoti „Excel“ naudodami dvi specialias funkcijas STDEV.V(remiantis imties visuma) ir STDEV.G(pagal bendrą populiaciją). Jų veikimo principas yra visiškai tas pats, tačiau juos galima vadinti trimis būdais, kuriuos aptarsime toliau.

1 būdas: funkcijų vedlys

2 metodas: Formulės skirtukas

3 būdas: formulės įvedimas rankiniu būdu

Taip pat yra būdas iš viso neiškviesti argumentų lango. Norėdami tai padaryti, turite rankiniu būdu įvesti formulę.

Kaip matote, „Excel“ standartinio nuokrypio skaičiavimo mechanizmas yra labai paprastas. Vartotojui tereikia įvesti skaičius iš populiacijos arba nuorodas į langelius, kuriuose jie yra. Visus skaičiavimus atlieka pati programa. Kur kas sunkiau suprasti, kas yra skaičiuojamas rodiklis ir kaip skaičiavimo rezultatus galima pritaikyti praktikoje. Tačiau to supratimas jau labiau susijęs su statistikos sritimi, o ne su mokymusi dirbti su programine įranga.

Norint rasti vidutinę reikšmę Excel (nesvarbu, ar tai skaitinė, tekstinė, procentinė ar kita reikšmė), yra daug funkcijų. Ir kiekvienas iš jų turi savo ypatybes ir privalumus. Iš tiesų, atliekant šią užduotį, gali būti nustatytos tam tikros sąlygos.

Pavyzdžiui, vidutinės skaičių serijos reikšmės programoje „Excel“ apskaičiuojamos naudojant statistines funkcijas. Taip pat galite rankiniu būdu įvesti savo formulę. Apsvarstykime įvairius variantus.

Kaip rasti skaičių aritmetinį vidurkį?

Norint rasti aritmetinį vidurkį, reikia susumuoti visus aibės skaičius ir padalyti sumą iš kiekio. Pavyzdžiui, mokinio informatikos pažymiai: 3, 4, 3, 5, 5. Kas įskaičiuota į ketvirtį: 4. Aritmetinį vidurkį radome pagal formulę: =(3+4+3+5+5) /5.

Kaip greitai tai padaryti naudojant „Excel“ funkcijas? Paimkime, pavyzdžiui, atsitiktinių skaičių seriją eilutėje:

Arba: sukurkite aktyvų langelį ir tiesiog rankiniu būdu įveskite formulę: = AVERAGE(A1:A8).

Dabar pažiūrėkime, ką dar gali padaryti funkcija AVERAGE.

Raskime pirmųjų dviejų ir paskutinių trijų skaičių aritmetinį vidurkį. Formulė: =VIDUTINIS(A1:B1,F1:H1). Rezultatas:

Būklė vidutinė

Aritmetinio vidurkio nustatymo sąlyga gali būti skaitinis arba tekstinis kriterijus. Naudosime funkciją: =AVERAGEIF().

Raskite skaičių, kurie yra didesni arba lygūs 10, aritmetinį vidurkį.

Funkcija: =AVERAGEIF(A1:A8,">=10")

Funkcijos AVERAGEIF naudojimo su sąlyga ">=10" rezultatas:

Trečiasis argumentas – „Averaging range“ – praleistas. Visų pirma, tai nėra būtina. Antra, programos analizuojamame diapazone yra TIK skaitinės reikšmės. Pirmajame argumente nurodytų langelių bus ieškoma pagal antrajame argumente nurodytą sąlygą.

Dėmesio!

Paieškos kriterijus galima nurodyti langelyje. Ir padarykite nuorodą į ją formulėje.

Raskime vidutinę skaičių reikšmę naudodami teksto kriterijų. Pavyzdžiui, vidutiniai prekės pardavimai „lentelės“.

Funkcija atrodys taip: = AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Diapazonas – stulpelis su prekių pavadinimais. Paieškos kriterijus yra nuoroda į langelį su žodžiu „lentelės“ (vietoj nuorodos A7 galite įterpti žodį „lentelės“). Vidurkinimo diapazonas – tie langeliai, iš kurių bus imami duomenys vidutinei vertei apskaičiuoti.

Apskaičiavę funkciją gauname tokią reikšmę:

Dėmesio!

Teksto kriterijui (sąlygai) turi būti nurodytas vidurkinimo diapazonas.

Kaip apskaičiuoti vidutinę svertinę kainą Excel?

Naudodami formulę SUMPRODUCT sužinome bendras pajamas pardavus visą prekių kiekį. O funkcija SUM sumuoja prekių kiekį. Visas pajamas iš prekių pardavimo padalijus iš bendro prekių vienetų skaičiaus, radome vidutinę svertinę kainą. Šis rodiklis atsižvelgia į kiekvienos kainos „svorį“. Jo dalis bendroje vertybių masėje.

Standartinis nuokrypis: formulė Excel

Yra bendrosios aibės ir imties standartiniai nuokrypiai. Pirmuoju atveju tai yra bendros dispersijos šaknis. Antroje – iš imties dispersijos.

Šiam statistiniam rodikliui apskaičiuoti sudaroma sklaidos formulė. Iš jo išgaunama šaknis. Tačiau „Excel“ yra paruošta funkcija standartiniam nuokrypiui rasti.

Standartinis nuokrypis yra susietas su šaltinio duomenų mastu. To nepakanka vaizdiniam analizuojamo diapazono kitimo pavaizdavimui. Norint gauti santykinį duomenų sklaidos lygį, apskaičiuojamas variacijos koeficientas:

standartinis nuokrypis / aritmetinis vidurkis

„Excel“ formulė atrodo taip:

STDEV (reikšmių diapazonas) / AVERAGE (reikšmių diapazonas).

Variacijos koeficientas skaičiuojamas procentais. Todėl langelyje nustatome procentų formatą.

Procentinio nuokrypio sąvoka reiškia skirtumą tarp dviejų skaitinių verčių procentais. Pateikime konkretų pavyzdį: tarkime, vieną dieną iš didmeninio sandėlio buvo parduota 120 tablečių, o kitą dieną – 150 vnt. Pardavimų apimčių skirtumas akivaizdus, kitą dieną parduota dar 30 tablečių. Iš 150 atėmus skaičių 120, gauname nuokrypį, lygų skaičiui +30. Kyla klausimas: kas yra procentinis nuokrypis?

Kaip apskaičiuoti procentinį nuokrypį „Excel“.

Procentinis nuokrypis apskaičiuojamas iš naujos vertės atimant senąją vertę ir padalijus rezultatą iš senosios vertės. Šios formulės skaičiavimo „Excel“ rezultatas turėtų būti rodomas procentų langelių formatu. Šiame pavyzdyje skaičiavimo formulė yra tokia (150-120)/120=25%. Formulę lengva patikrinti: 120+25%=150.

Pastaba! Jei sukeisime senus ir naujus numerius, tada gausime antkainio skaičiavimo formulė.

Toliau pateiktame paveikslėlyje parodytas pavyzdys, kaip pateikti aukščiau pateiktą skaičiavimą kaip Excel formulę. D2 langelyje esanti formulė apskaičiuoja procentinį nuokrypį tarp einamųjų ir praėjusių metų pardavimo verčių: =(C2-B2)/B2

Svarbu atkreipti dėmesį į skliaustų buvimą šioje formulėje. Pagal numatytuosius nustatymus programoje „Excel“ padalijimo operacija visada turi viršenybę prieš atimties operaciją. Todėl, jei nedėsime skliaustų, tada vertė pirmiausia bus padalinta, o tada iš jos bus atimta kita reikšmė. Toks skaičiavimas (be skliaustų) bus klaidingas. Uždarius pirmąją skaičiavimo dalį formulėje su skliaustais, atimties operacijos prioritetas automatiškai pakyla virš padalijimo operacijos.

Teisingai įveskite formulę su skliaustais langelyje D2 ir tiesiog nukopijuokite ją į likusius tuščius diapazono D2:D5 langelius. Norėdami greičiau nukopijuoti formulę, tiesiog perkelkite pelės žymeklį į klaviatūros žymeklio žymeklį (apatiniame dešiniajame kampe), kad pelės žymeklis pasikeistų iš rodyklės į juodą kryžių. Tada tereikia dukart spustelėti kairiuoju pelės klavišu ir Excel automatiškai užpildys tuščius langelius formule ir nustatys diapazoną D2:D5, kurį reikia užpildyti iki langelio D5 ir ne daugiau. Tai labai patogus Excel gyvenimo įsilaužimas.

Alternatyvi formulė procentiniam nuokrypiui apskaičiuoti programoje Excel

Alternatyvioje formulėje, apskaičiuojančioje santykinį pardavimo verčių nuokrypį nuo einamųjų metų, iš karto padalinkite iš praėjusių metų pardavimų verčių ir tik tada iš rezultato atimkite vieną: =C2/B2-1.

Kaip matote paveikslėlyje, alternatyvios formulės apskaičiavimo rezultatas yra toks pat kaip ir ankstesnėje, todėl teisingas. Tačiau alternatyvią formulę parašyti lengviau, nors kai kuriems gali būti sunkiau perskaityti, kad suprastų jos veikimo principą. Arba sunkiau suprasti, kokią reikšmę duota formulė sukuria atlikus skaičiavimą, jei ji nepasirašyta.

Vienintelis šios alternatyvios formulės trūkumas yra nesugebėjimas apskaičiuoti neigiamų skaičių procentinis nuokrypis skaitiklyje arba pakaitalu. Net jei formulėje naudosime ABS funkciją, formulė pateiks klaidingą rezultatą, jei pakaitalo skaičius bus neigiamas.

Kadangi „Excel“ pagal numatytuosius nustatymus skirstymo operacijos prioritetas yra prieš atimties operaciją, šioje formulėje nereikia naudoti skliaustų.

Standartinis nuokrypis yra klasikinis aprašomosios statistikos kintamumo rodiklis.

Standartinis nuokrypis, standartinis nuokrypis, standartinis nuokrypis, imties standartinis nuokrypis (angl. standard deviation, STD, STDev) – labai dažnas sklaidos rodiklis aprašomojoje statistikoje. Bet, nes techninė analizė yra panaši į statistiką, šis rodiklis gali būti (ir turėtų būti) naudojamas atliekant techninę analizę, siekiant nustatyti analizuojamo instrumento kainos sklaidos laipsnį. Žymima graikišku simboliu Sigma „σ“.

Dėkojame Carlui Gaussui ir Pearsonui už leidimą naudoti standartinį nuokrypį.

Naudojant standartinis nuokrypis techninėje analizėje, paverčiame tai "dispersijos indeksas""V nepastovumo indikatorius“, išlaikant prasmę, bet keičiant terminus.

Kas yra standartinis nuokrypis

Tačiau, be tarpinių pagalbinių skaičiavimų, standartinis nuokrypis yra gana priimtinas nepriklausomam skaičiavimui ir taikymas techninėje analizėje. Kaip pažymėjo aktyvus mūsų žurnalo skaitytojas, varnalėša: Vis dar nesuprantu, kodėl standartinis nuokrypis neįtrauktas į vidaus prekybos centrų standartinių rodiklių rinkinį«.

tikrai, standartinis nuokrypis gali išmatuoti instrumento kintamumą klasikiniu ir „grynu“ būdu. Bet, deja, šis rodiklis nėra toks įprastas vertybinių popierių analizėje.

Standartinio nuokrypio taikymas

Rankiniu būdu apskaičiuoti standartinį nuokrypį nėra labai įdomu, bet naudinga patirtis. Galima išreikšti standartinį nuokrypį formulė STD=√[(∑(x-x ) 2)/n] , kuri skamba kaip imties elementų ir vidurkio skirtumų kvadrato sumos šaknis, padalyta iš imties elementų skaičiaus.

Jei imtyje elementų skaičius viršija 30, tai trupmenos po šaknimi vardiklis įgyja reikšmę n-1. Kitu atveju naudojamas n.

Žingsnis po žingsnio standartinio nuokrypio skaičiavimas: