Excel бағдарламасында стандартты ауытқуды қалай есептеу керек. Кездейсоқ шаманың дисперсиясы

Статистика көптеген көрсеткіштерді пайдаланады, олардың бірі Excel бағдарламасында дисперсияны есептеу болып табылады. Егер сіз мұны өзіңіз қолмен жасасаңыз, бұл көп уақытты алады және сіз көптеген қателіктер жібере аласыз. Бүгін біз математикалық формулаларды қалай бөлуге болатынын қарастырамыз қарапайым функциялар. Бірнеше минут ішінде бәрін жасауға мүмкіндік беретін қарапайым, жылдам және ыңғайлы есептеу әдістерін қарастырайық.

Дисперсияны есептеңіз

Дисперсия кездейсоқ шамакездейсоқ шаманың оның математикалық күтуінен квадраттық ауытқуының математикалық күтуі.

Біз жалпы халық санына қарай есептейміз

Матаны есептеу үшін. Бағдарламаның DISP.G функциясын пайдалануын күту және оның синтаксисі келесідей көрінеді: “=DISP.G(Number1;Number2;…)”.

Ең көбі 255 аргумент пайдалануға болады, артық емес. Аргументтер болуы мүмкін жай сандарнемесе олар көрсетілген ұяшықтарға сілтемелер. Microsoft Excel бағдарламасында дисперсияны қалай есептеу керектігін қарастырайық:

1. Бірінші қадам - ​​​​есептеу нәтижесі көрсетілетін ұяшықты таңдау, содан кейін «Функцияны кірістіру» түймесін басыңыз.

2. Функцияны басқару қабығы ашылады. Онда «Статистикалық» немесе «Толық алфавиттік тізім» санатында болуы мүмкін «DISP.G» функциясын іздеу керек. Ол табылған кезде оны таңдап, «OK» түймесін басыңыз.

3. Функция аргументтері бар терезе ашылады. Онда сіз «1-нөмір» жолын таңдап, парақта сандар қатары бар ұяшықтар ауқымын таңдауыңыз керек.

4. Осыдан кейін есептеу нәтижелері функция енгізілген ұяшықта көрсетіледі.

Осылайша Excel бағдарламасында дисперсияны оңай табуға болады.

Үлгі бойынша есептер жасаймыз

Бұл жағдайда Excel бағдарламасындағы таңдау дисперсиясы емес бөлгішімен есептеледі жалпы санысандар, бірақ бір кем. Бұл синтаксисі =DISP.V(Number1;Number2;...) болып табылатын DISP.V арнайы функциясы арқылы кішірек қате үшін орындалады. Әрекеттер алгоритмі:

• Алдыңғы әдістегідей, нәтиже үшін ұяшықты таңдау керек.
• Функция шеберінде «Толық алфавиттік тізім» немесе «Статистикалық» санатының астынан «DISP.B» табу керек.

• Әрі қарай, терезе пайда болады және сіз алдыңғы әдістегідей әрекет етуіңіз керек.

Бейне: Excel бағдарламасында дисперсияны есептеу

Қорытынды

Excel бағдарламасындағы дисперсия оны қолмен орындауға қарағанда өте қарапайым, әлдеқайда жылдам және ыңғайлы есептеледі, өйткені математикалық күту функциясы өте күрделі және оны есептеу көп уақыт пен күш жұмсауы мүмкін.

Қайырлы күн

Бұл мақалада мен стандартты ауытқудың Excel бағдарламасында STANDARDEVAL функциясы арқылы қалай жұмыс істейтінін қарастыруды шештім. Мен ұзақ уақыт бойы сипаттамадым немесе түсініктеме бермедім, сонымен қатар бұл оқитындар үшін өте пайдалы функция болғандықтан жоғары математика. Ал студенттерге көмектесу – бұл киелі, оны меңгеру қаншалықты қиын екенін өз тәжірибемнен білемін; Шындығында стандартты ауытқу функциялары сатылған өнімдердің тұрақтылығын анықтау, бағаларды құру, ассортиментті реттеу немесе қалыптастыру және сатудың басқа да бірдей пайдалы талдаулары үшін пайдаланылуы мүмкін.

Excel бұл дисперсия функциясының бірнеше нұсқаларын пайдаланады:

Математикалық теория

Біріншіден, функцияны математикалық тілде қалай сипаттауға болатыны туралы аздап стандартты ауытқуоны Excel бағдарламасында пайдалану үшін, мысалы, сату статистикасы деректерін талдау үшін, бірақ бұл туралы кейінірек. Бірден ескертемін, көп түсініксіз сөздер жазамын...)))), егер мәтінде төменде бірдеңе болса, дереу қараңыз. практикалық қолданубағдарламада.

Стандартты ауытқу нақты не істейді? Ол Х кездейсоқ шамасының стандартты ауытқуын оның дисперсиясын бейтарап бағалау негізінде оның математикалық күтуіне қатысты бағалайды. Келісіңіз, бұл түсініксіз естіледі, бірақ студенттер шын мәнінде не туралы айтып жатқанымызды түсінеді деп ойлаймын!

Біріншіден, кейіннен «стандартты ауытқуды» есептеу үшін «стандартты ауытқуды» анықтау керек, бұл формула бізге көмектеседі: Формула келесідей сипатталуы мүмкін: ол кездейсоқ шаманың өлшемдері сияқты бірліктермен өлшенеді және стандартты арифметикалық орташа қатені есептеу кезінде, сенімділік интервалдарын құру кезінде, статистика үшін гипотезаларды тексеру кезінде немесе сызықтық мәнді талдау кезінде қолданылады. тәуелсіз айнымалылар арасындағы байланыс. Функция келесідей анықталады шаршы түбіртәуелсіз айнымалылардың дисперсиясынан.

Енді біз анықтай аламыз және стандартты ауытқуХ кездейсоқ шамасының оның дисперсиясын бейтарап бағалауға негізделген оның математикалық перспективасына қатысты стандартты ауытқуын талдау болып табылады. Формула былай жазылады:
Мен екі бағаның да біржақты екенін ескертемін. Жалпы жағдайларда объективті бағалауды құру мүмкін емес. Бірақ бейтарап дисперсияны бағалауға негізделген бағалау дәйекті болады.

Excel бағдарламасында практикалық енгізу

Енді қызықсыз теориядан алшақтап, STANDARDEVAL функциясының қалай жұмыс істейтінін іс жүзінде көрейік. Мен Excel бағдарламасындағы стандартты ауытқу функциясының барлық нұсқаларын қарастырмаймын, бірақ мысалдарда. Мысал ретінде сату тұрақтылығы статистикасы қалай анықталатынын қарастырайық.

Алдымен, функцияның емлесін қараңыз және көріп отырғаныңыздай, бұл өте қарапайым:

СТАНДАРТТЫҚ АЛУ.Г(_сан1_;_сан2_; ….), мұндағы:

Енді мысал файлын жасап, оның негізінде бұл функция қалай жұмыс істейтінін қарастырайық. Аналитикалық есептеулерді жүргізу үшін кем дегенде үш мәнді пайдалану қажет болғандықтан, негізінен кез келген статистикалық талдауда мен шартты түрде 3 кезеңді алдым, бұл бір жыл, тоқсан, ай немесе апта болуы мүмкін. Менің жағдайда - бір ай. Максималды сенімділік үшін мен мүмкіндігінше көп кезеңдерді қабылдауды ұсынамын, бірақ үштен кем емес. Формуланың жұмысы мен функционалдығы анықтығы үшін кестедегі барлық деректер өте қарапайым.

Біріншіден, біз айлар бойынша орташа мәнді есептеуіміз керек. Ол үшін AVERAGE функциясын қолданамыз және формуланы аламыз: = AVERAGE(C4:E4).
Енді, шын мәнінде, біз стандартты ауытқуды STANDARDEVAL.G функциясының көмегімен таба аламыз, оның мәніне біз әрбір кезеңдегі өнімнің сатылуын енгізуіміз керек. Нәтиже келесі түрдегі формула болады: =СТАНДАРТТЫҚ АЛУ.Г(C4;D4;E4).
Ал, жұмыстың жартысы бітті. Келесі қадам «Вариацияны» қалыптастыру болып табылады, ол орташа мәнге, стандартты ауытқуға бөлу және нәтижені пайыздарға түрлендіру арқылы алынады. Біз келесі кестені аламыз:
Негізгі есептеулер аяқталды, сатылымның тұрақты немесе тұрақты еместігін анықтау ғана қалды. Шарт ретінде 10% ауытқулар тұрақты болып есептелетінін алайық, 10-нан 25% -ға дейін шағын ауытқулар болып табылады, бірақ 25% -дан жоғары кез келген нәрсе енді тұрақты емес. Шарттарға сәйкес нәтиже алу үшін логиканы қолданамыз және нәтиже алу үшін формуланы жазамыз:

ЕГЕР(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

Барлық ауқымдар нақтылық үшін алынады;
Деректерді визуализациялауды жақсарту үшін кестеде мыңдаған позициялар болғанда, сізге қажет белгілі шарттарды қолдану немесе белгілі бір опцияларды түс схемасымен бөлектеу үшін пайдалану мүмкіндігін пайдалану керек, бұл өте түсінікті болады.

Алдымен шартты пішімдеуді қолданатындарды таңдаңыз. «Басты» басқару тақтасында «Шартты пішімдеу» тармағын таңдап, ашылмалы мәзірде «Ұяшықтарды ерекшелеу ережелері» тармағын таңдаңыз, содан кейін «Мәтінде ...» мәзір элементін басыңыз. Шарттарды енгізетін диалогтық терезе пайда болады.

Шарттарды жазғаннан кейін, мысалы, «тұрақты» - жасыл, «қалыпты» - сары және «тұрақсыз» - қызыл, біз әдемі және түсінікті кесте аламыз, онда сіз алдымен не нәрсеге назар аудару керектігін көре аласыз.

STDEV.Y функциясы үшін VBA пайдалану

Кез келген қызығушылық танытқан адам макростар арқылы есептеулерін автоматтандырып, келесі функцияны пайдалана алады:

Функция MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Әрбір x In Arr үшін aSum = aSum + x "жиым элементтерінің қосындысын есептеу aCnt = aCnt + 1 "элементтердің санын есептеу Келесі x aAver = aSum / aCnt "орташа мән Әрбір x In Arr үшін tmp = tmp + (x - aAver) ^ 2 "жиым элементтері мен орташа мән арасындағы айырмашылық квадраттарының қосындысын есептеңіз Келесі x MyStDevP = Sqr(tmp / aCnt ) "STANDARDEV.G() End функциясын есептеңіз

MyStDevP(Arr) функциясы Dim x , aCnt & , aSum #, aAver#, tmp# Әрбір x In Arr үшін aSum = aSum + x «массив элементтерінің қосындысын есептеңіз

Статистикада қолданылатын көптеген көрсеткіштердің ішінде дисперсияны есептеуді ерекше атап өту қажет. Айта кету керек, бұл есептеуді қолмен орындау өте қиын жұмыс. Бақытымызға орай, Excel бағдарламасында есептеу процедурасын автоматтандыруға мүмкіндік беретін функциялар бар. Осы құралдармен жұмыс істеу алгоритмін анықтайық.

Дисперсия – вариацияның көрсеткіші, ол математикалық күтуден ауытқудың орташа квадраты болып табылады. Осылайша, ол орташа мәннің айналасындағы сандардың таралуын білдіреді. Дисперсияны есептеу жалпы жиынтық үшін де, іріктеу үшін де жүргізілуі мүмкін.

1-әдіс: халық саны бойынша есептеу

Бұл көрсеткішті Excel бағдарламасында жалпы халық үшін есептеу үшін функцияны пайдаланыңыз DISP.G. Бұл өрнектің синтаксисі келесідей:

DISP.G(1-сан;2-сан;…)

Барлығы 1-ден 255-ке дейін аргументтерді қолдануға болады. Аргументтер сандық мәндер немесе олар қамтылған ұяшықтарға сілтеме болуы мүмкін.

Бұл мәнді сандық деректері бар ауқым үшін қалай есептеу керектігін көрейік.

2-әдіс: үлгі бойынша есептеу

Бастапқыға негізделген мәнді есептеуден айырмашылығы, іріктемені есептеу кезінде бөлгіш сандардың жалпы санын көрсетпейді, бірақ бір кем. Бұл қатені түзету мақсатында жасалады. Excel бұл нюансты есептеудің осы түріне арналған арнайы функцияда ескереді - DISP.V. Оның синтаксисі келесі формуламен берілген:

DISP.B(1-сан;2-сан;…)

Аргументтер саны, алдыңғы функциядағыдай, 1-ден 255-ке дейін болуы мүмкін.

Көріп отырғаныңыздай, Excel бағдарламасы дисперсияны есептеуді айтарлықтай жеңілдетеді. Бұл статистиканы қолдану арқылы не жиынтықтан, не іріктеуден есептеуге болады. Бұл жағдайда пайдаланушының барлық әрекеттері іс жүзінде өңделетін сандар ауқымын көрсетуге келеді және Excel негізгі жұмысты өзі жасайды. Әрине, бұл пайдаланушы уақытын айтарлықтай үнемдейді.

Статистикалық талдаудың негізгі құралдарының бірі стандартты ауытқуды есептеу болып табылады. Бұл көрсеткіш үлгі немесе жиынтық үшін стандартты ауытқуды бағалауға мүмкіндік береді. Excel бағдарламасында стандартты ауытқу формуласын пайдалануды үйренейік.

Стандартты ауытқудың не екенін және оның формуласы қандай болатынын бірден анықтайық. Бұл шама қатардағы барлық шамалардың айырмасының квадраттарының орташа арифметикалық мәні мен олардың арифметикалық ортасының квадрат түбірі болып табылады. Бұл көрсеткіштің бірдей атауы бар - стандартты ауытқу. Екі атау да толығымен баламалы.

Бірақ, әрине, Excel бағдарламасында пайдаланушыға мұны есептеудің қажеті жоқ, өйткені бағдарлама ол үшін бәрін жасайды. Excel бағдарламасында стандартты ауытқуды қалай есептеу керектігін үйренейік.

Excel бағдарламасында есептеу

Excel бағдарламасында көрсетілген мәнді екі арнайы функцияны пайдаланып есептей аласыз STDEV.V(үлгіленген жиынтық негізінде) және STDEV.G(жалпы халық санына негізделген). Олардың жұмыс істеу принципі мүлдем бірдей, бірақ оларды үш жолмен шақыруға болады, біз төменде талқылаймыз.

1-әдіс: Функция шебері

2-әдіс: Формулалар қойындысы

3-әдіс: формуланы қолмен енгізу

Сондай-ақ, дәлелдер терезесін мүлдем шақырудың қажеті болмайтын әдіс бар. Ол үшін формуланы қолмен енгізу керек.

Көріп отырғаныңыздай, Excel бағдарламасында стандартты ауытқуды есептеу механизмі өте қарапайым. Пайдаланушы тек популяциядан сандарды немесе оларды қамтитын ұяшықтарға сілтемелерді енгізуі керек. Барлық есептеулерді бағдарламаның өзі жасайды. Есептелген көрсеткіштің не екенін және есептеу нәтижелерін іс жүзінде қалай қолдануға болатынын түсіну әлдеқайда қиын. Бірақ мұны түсіну бағдарламалық жасақтамамен жұмыс істеуді үйренуден гөрі статистика саласына көбірек қатысты.

Excel бағдарламасында орташа мәнді табу үшін (сандық, мәтіндік, пайыздық немесе басқа мәнге қарамастан) көптеген функциялар бар. Және олардың әрқайсысының өзіндік ерекшеліктері мен артықшылықтары бар. Шынында да, бұл тапсырмада белгілі бір шарттар қойылуы мүмкін.

Мысалы, Excel бағдарламасындағы сандар қатарының орташа мәндері статистикалық функциялар арқылы есептеледі. Өз формулаңызды қолмен енгізуге де болады. Әртүрлі нұсқаларды қарастырайық.

Сандардың арифметикалық ортасын қалай табуға болады?

Орташа арифметикалық мәнді табу үшін жиынтықтағы барлық сандарды қосып, қосындыны шамаға бөлу керек. Мысалы, оқушының информатикадан алған бағалары: 3, 4, 3, 5, 5. Тоқсанға не кіреді: 4. Орташа арифметикалық мәнді мына формула арқылы таптық: =(3+4+3+5+5) /5.

Excel функцияларын пайдаланып мұны қалай тез орындауға болады? Мысалы, жолдағы кездейсоқ сандар қатарын алайық:

Немесе: белсенді ұяшықты жасаңыз және формуланы қолмен енгізіңіз: =Орташа (A1:A8).

Енді AVERAGE функциясы тағы не істей алатынын көрейік.

Алғашқы екі және соңғы үш санның арифметикалық ортасын табайық. Формула: =Орташа(A1:B1,F1:H1). Нәтиже:



Орташа жағдай

Орташа арифметикалық мәнді табу шарты сандық немесе мәтіндік критерий болуы мүмкін. Біз мына функцияны қолданамыз: =AVERAGEIF().

10-нан үлкен немесе тең сандардың арифметикалық ортасын табыңыз.

Функция: =Орташа мән (A1:A8,">=10")

">=10" шартында AVERAGEIF функциясын пайдалану нәтижесі:

Үшінші аргумент – «Орташа диапазон» алынып тасталды. Біріншіден, бұл талап етілмейді. Екіншіден, бағдарлама талданатын ауқымда ТЕК сандық мәндер бар. Бірінші аргументте көрсетілген ұяшықтар екінші аргументте көрсетілген шартқа сәйкес ізделеді.

Назар аударыңыз! Іздеу шартын ұяшықта көрсетуге болады. Және формулада оған сілтеме жасаңыз.

Мәтіндік критерий арқылы сандардың орташа мәнін табайық. Мысалы, өнімнің орташа сатылымы «кестелер».

Функция келесідей болады: = AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Ауқым – өнім атаулары бар баған. Іздеу шарты – «кестелер» сөзі бар ұяшыққа сілтеме (A7 сілтемесінің орнына «кестелер» сөзін енгізуге болады). Орташа диапазон – орташа мәнді есептеу үшін деректер алынатын ұяшықтар.

Функцияны есептеу нәтижесінде келесі мәнді аламыз:

Назар аударыңыз! Мәтіндік критерий (шарт) үшін орташалау ауқымы көрсетілуі керек.

Excel бағдарламасында орташа өлшенген бағаны қалай есептеуге болады?

Орташа өлшенген бағаны қалай білдік?

Формула: =ҚОРЫНДЫ(C2:C12,B2:B12)/SUM(C2:C12).

SUMPRODUCT формуласын қолдана отырып, біз тауардың барлық санын сатқаннан кейінгі жалпы табысты анықтаймыз. Ал SUM функциясы тауардың санын қорытындылайды. Тауарларды сатудан түскен жалпы табысты тауар бірлігінің жалпы санына бөлу арқылы орташа өлшенген бағаны таптық. Бұл көрсеткіш әрбір бағаның «салмасын» ескереді. Құндылықтардың жалпы массасындағы оның үлесі.

Стандартты ауытқу: Excel бағдарламасындағы формула

Жалпы жиынтық пен іріктеу үшін стандартты ауытқулар бар. Бірінші жағдайда бұл жалпы дисперсияның түбірі. Екіншіден, таңдау дисперсиясынан.

Бұл статистикалық көрсеткішті есептеу үшін дисперсия формуласы құрастырылады. Одан тамыр алынады. Бірақ Excel-де стандартты ауытқуды табу үшін дайын функция бар.

Стандартты ауытқу бастапқы деректердің масштабына байланысты. Бұл талданатын диапазонның вариациясын бейнелі түрде көрсету үшін жеткіліксіз. Мәліметтердің шашырауының салыстырмалы деңгейін алу үшін вариация коэффициенті есептеледі:

стандартты ауытқу / орташа арифметикалық

Excel бағдарламасындағы формула келесідей көрінеді:

STDEV (мәндер ауқымы) / AVERAGE (мәндер ауқымы).

Вариация коэффициенті пайызбен есептеледі. Сондықтан ұяшыққа пайыздық форматты орнатамыз.