Стандартты ауытқу excel функциясы. Көмектесетін бейне
Есептеп көрейікХАНЫМEXCELүлгі дисперсиясы және стандартты ауытқу. Кездейсоқ шаманың таралуы белгілі болса, оның дисперсиясын да есептейміз.
Алдымен қарастырайық дисперсия, содан кейін стандартты ауытқу.
Үлгі дисперсиясы
Үлгі дисперсиясы (үлгі дисперсиясы,үлгідисперсия) қатысты массивтегі мәндердің таралуын сипаттайды.
Барлық 3 формула математикалық эквивалент.
Бірінші формуладан бұл анық үлгі дисперсиясымассивтегі әрбір мәннің квадраттық ауытқуларының қосындысы болып табылады орташадан, үлгі өлшеміне минус 1 бөледі.
ауытқулар үлгілері DISP() функциясы пайдаланылады, ағылшын. VAR атауы, яғни. VARIance. MS EXCEL 2010 нұсқасынан оның аналогы DISP.V(), ағылшын тілін пайдалану ұсынылады. VARS атауы, яғни. VARiance үлгісі. Сонымен қатар, MS EXCEL 2010 нұсқасынан бастап DISP.Г(), ағылшын тілі функциясы бар. VARP атауы, яғни. Популяцияның VARiance, ол есептейді дисперсияүшін халық. Барлық айырмашылық бөлгішке келеді: DISP.V() сияқты n-1 орнына DISP.G() азайғышта тек n болады. MS EXCEL 2010 нұсқасына дейін жиынтық дисперсиясын есептеу үшін VAR() функциясы қолданылды.
Үлгі дисперсиясы
=QUADROTCL(Үлгі)/(COUNT(Үлгі)-1)
=(СОМ(Үлгі)-COUNT(Үлгі)*Орташа(Үлгі)^2)/ (COUNT(Үлгі)-1)- кәдімгі формула
=SUM((Үлгі -Орташа(Үлгі))^2)/ (COUNT(Үлгі)-1) –
Үлгі дисперсиясы 0-ге тең, егер барлық мәндер бір-біріне тең болса және сәйкесінше тең болса орташа мән. Әдетте, мән соғұрлым үлкен болады ауытқулар, массивтегі мәндердің таралуы соғұрлым көп болады.
Үлгі дисперсиясынүктелік бағалау болып табылады ауытқуларол жасалған кездейсоқ шаманың таралуы үлгі. Құрылыс туралы сенімділік интервалдарыбағалау кезінде ауытқулармақаласынан оқуға болады.
Кездейсоқ шаманың дисперсиясы
Есептеу үшін дисперсиякездейсоқ шама, сіз оны білуіңіз керек.
үшін ауытқуларкездейсоқ шама X жиі Var(X) деп белгіленеді. Дисперсия E(X) орташа мәннен ауытқу квадратына тең: Var(X)=E[(X-E(X)) 2 ]
дисперсияформула бойынша есептеледі:
мұндағы x i – кездейсоқ шама қабылдай алатын мән, ал μ – орташа мән (), p(x) – кездейсоқ шаманың х мәнін қабылдау ықтималдығы.
Кездейсоқ шамада болса, онда дисперсияформула бойынша есептеледі:
Өлшем ауытқуларбастапқы мәндердің өлшем бірлігінің квадратына сәйкес келеді. Мысалы, егер үлгідегі мәндер бөлік салмағының өлшемдерін білдірсе (кг), онда дисперсия өлшемі кг 2 болады. Мұны түсіндіру қиын болуы мүмкін, сондықтан мәндердің таралуын сипаттау үшін мәнге тең шаршы түбірбастап ауытқулар – стандартты ауытқу.
Кейбір қасиеттер ауытқулар:
Var(X+a)=Var(X), мұндағы X – кездейсоқ шама, ал а – тұрақты.
Var(aХ)=a 2 Var(X)
Var(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2
Бұл дисперсиялық қасиет қолданылады сызықтық регрессия туралы мақала.
Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y), мұндағы X және Y - кездейсоқ айнымалылар, Cov(Х;Y) – осы кездейсоқ шамалардың ковариациясы.
Егер кездейсоқ шамалар тәуелсіз болса, онда олар ковариация 0-ге тең, сондықтан Var(X+Y)=Var(X)+Var(Y). Бұл дисперсия қасиеті туындыда қолданылады.
Тәуелсіз шамалар үшін Var(X-Y)=Var(X+Y) болатынын көрсетейік. Шынында да, Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var( X)+(- 1) 2 Var(Y)= Var(X)+Var(Y)= Var(X+Y). Бұл дисперсиялық қасиет құрастыру үшін пайдаланылады.
Стандартты ауытқу үлгісі
Стандартты ауытқу үлгісі- үлгідегі мәндердің оларға қатысты қаншалықты кең шашырағанының өлшемі.
Анықтама бойынша, стандартты ауытқуквадрат түбіріне тең ауытқулар:
Стандартты ауытқуішіндегі мәндердің шамасын есепке алмайды үлгі, бірақ олардың айналасындағы құндылықтардың таралу дәрежесі ғана орташа. Мұны түсіндіру үшін мысал келтірейік.
2 үлгі үшін стандартты ауытқуды есептейік: (1; 5; 9) және (1001; 1005; 1009). Екі жағдайда да s=4. санының қатынасы екені анық стандартты ауытқуүлгілердің массив мәндері айтарлықтай ерекшеленеді. Мұндай жағдайларда ол қолданылады Вариация коэффициенті(Вариация коэффициенті, CV) – қатынас Стандартты ауытқуорташаға дейін арифметика, пайызбен көрсетілген.
Есептеу үшін MS EXCEL 2007 және бұрынғы нұсқаларында Стандартты ауытқу үлгісі=STDEVAL() функциясы пайдаланылады, ағылшын. атауы STDEV, яғни. Стандартты ауытқу. MS EXCEL 2010 нұсқасынан оның аналогы =STDEV.B() , ағылшын тілін пайдалану ұсынылады. атауы STDEV.S, яғни. Стандартты ауытқу үлгісі.
Сонымен қатар, MS EXCEL 2010 нұсқасынан бастап STANDARDEV.G(), ағылшын тілі функциясы бар. атауы STDEV.P, яғни. Есептейтін халық стандартының ауытқуы стандартты ауытқуүшін халық. Бүкіл айырмашылық азайғышқа келеді: STANDARDEV.V() сияқты n-1 орнына STANDARDEVAL.G() азайғышта n ғана бар.
Стандартты ауытқутөмендегі формулалар арқылы тікелей есептеуге болады (мысалы файлды қараңыз)
=ROOT(QUADROTCL(Үлгі)/(COUNT(Үлгі)-1))
=ТҮБІР((СОМ(Үлгі)-САН(Үлгі)*Орташа(Үлгі)^2)/(COUNT(Үлгі)-1))
Басқа шашырау шаралары
SQUADROTCL() функциясы арқылы есептейді мәндерінің олардың квадраттық ауытқуларының қосындысы орташа. Бұл функция =DISP.G( формуласымен бірдей нәтижені береді. Үлгі)*ТЕКСЕРУ( Үлгі), Қайда Үлгі- үлгі мәндерінің массивін қамтитын ауқымға сілтеме (). QUADROCL() функциясындағы есептеулер мына формула бойынша орындалады:
SROTCL() функциясы да деректер жиынының таралу өлшемі болып табылады. SROTCL() функциясы мәндердің ауытқуларының абсолютті мәндерінің орташа мәнін есептейді орташа. Бұл функция формула сияқты нәтижені қайтарады =ҚОРЫНДЫ(ABS(Үлгі-орташа(Үлгі)/COUNT(Үлгі), Қайда Үлгі- үлгі мәндерінің массивін қамтитын ауқымға сілтеме.
SROTCL () функциясындағы есептеулер мына формула бойынша орындалады:
Статистикалық талдаудың негізгі құралдарының бірі стандартты ауытқуды есептеу болып табылады. Бұл көрсеткіш үлгі немесе жиынтық үшін стандартты ауытқуды бағалауға мүмкіндік береді. Excel бағдарламасында стандартты ауытқу формуласын пайдалануды үйренейік.
Стандартты ауытқудың не екенін және оның формуласы қандай болатынын бірден анықтайық. Бұл шама қатардағы барлық шамалардың айырмасының квадраттарының орташа арифметикалық мәні мен олардың арифметикалық ортасының квадрат түбірі болып табылады. Бұл көрсеткіштің бірдей атауы бар - стандартты ауытқу. Екі атау да толығымен баламалы.
Бірақ, әрине, Excel бағдарламасында пайдаланушыға мұны есептеудің қажеті жоқ, өйткені бағдарлама ол үшін бәрін жасайды. Excel бағдарламасында стандартты ауытқуды қалай есептеу керектігін үйренейік.
Excel бағдарламасында есептеу
Excel бағдарламасында көрсетілген мәнді екі арнайы функцияны пайдаланып есептей аласыз STDEV.V(Орындаушы үлгі популяциясы) Және STDEV.G(жалпы халық санына негізделген). Олардың жұмыс істеу принципі мүлдем бірдей, бірақ оларды үш жолмен шақыруға болады, біз төменде талқылаймыз.
1-әдіс: Функция шебері
2-әдіс: Формулалар қойындысы
3-әдіс: формуланы қолмен енгізу
Сондай-ақ, дәлелдер терезесін мүлдем шақырудың қажеті болмайтын әдіс бар. Ол үшін формуланы қолмен енгізу керек.
Көріп отырғаныңыздай, Excel бағдарламасында стандартты ауытқуды есептеу механизмі өте қарапайым. Пайдаланушы тек популяциядан сандарды немесе оларды қамтитын ұяшықтарға сілтемелерді енгізуі керек. Барлық есептеулерді бағдарламаның өзі жасайды. Есептелген көрсеткіштің не екенін және есептеу нәтижелерін іс жүзінде қалай қолдануға болатынын түсіну әлдеқайда қиын. Бірақ мұны түсіну бағдарламалық жасақтамамен жұмыс істеуді үйренуден гөрі статистика саласына көбірек қатысты.
Орташа квадраттық ауытқу (немесе стандартты ауытқу) вариациялық қатардағы екінші ең үлкен тұрақты болып табылады. Бұл топқа кіретін объектілердің әртүрлілігінің өлшемі және қаншалықты екенін көрсетеді орта есеппеннұсқалар зерттелетін халықтың орташа арифметикалық мәнінен ауытқиды. Опциялар орташа мәннің айналасында неғұрлым шашыраңқы болса, вариациялық қатардың орташа мәнінен ауытқудың экстремалды немесе басқа да қашық кластары неғұрлым жиі орын алса, орташа квадраттық ауытқу соғұрлым үлкен болады. Стандартты ауытқу – кездейсоқ факторлардың әсерінен сипаттамалардың өзгергіштігінің өлшемі. Шаршы стандартты ауытқу ( С²) деп аталады дисперсия .
Егжей-тегжейлі қарастырған кезде «кездейсоқ» деген не? Вариант моделінің формуласында кездейсоқ компонент жүйелі факторлардың әсерінен қалыптасқан варианттар үлесіне белгілі бір «қоспа» түрінде көрінеді, ± x жағдайы. . Ол өз кезегінде шексіз әсер ету әсерлерінен тұрады үлкен санфакторлар: x жағдайы . = Σ x кездейсоқ к.
Бұл факторлардың әрқайсысы өзінің күшті әсерін ашуы мүмкін (үлкен үлес қосады), немесе нақты нұсқаны қалыптастыруға қатыспауы мүмкін (әлсіз әсер, шамалы үлес). Сонымен қатар, әрбір опция үшін кездейсоқ «өсім» үлесі әртүрлі болып шығады! Мысалы, дафнияның мөлшерін ескере отырып, сіз бір особь үлкенірек, екіншісі кішірек, өйткені біреуі бірнеше сағат бұрын, екіншісі кейінірек туылғанын немесе генетикалық жағынан басқаларына мүлдем сәйкес келмейтінін, ал үшіншісі өскенін көруге болады. аквариумның жылырақ аймағында және т.б.
Егер бұл нақты факторлар бақыланатындар қатарына кірмейдіопцияны жинаған кезде, олар әр түрлі дәрежеде өзін-өзі көрсете отырып, қамтамасыз етеді кездейсоқвариация опциясы. Кездейсоқ факторлар неғұрлым көп болса, соғұрлым олар күштірек болса, опциялар соғұрлым орташа шаманың айналасында шашыраңқы болады және вариациялық сипаттама, стандартты ауытқу соғұрлым көп болады. Біздің кітабымыздың контекстінде «кездейсоқ» термині «белгісіз», «бақыланбайтын» сөзінің синонимі болып табылады. Біз фактордың интенсивтілігін қандай да бір жолмен (топтау, градация, сан арқылы) білдірмейінше, оған дейін ол кездейсоқ өзгергіштік тудыратын фактор болып қала береді.
Стандартты ауытқудың мәні (орташадан ауытқу) мына формуламен өрнектеледі:
Қайда x-топтағы әрбір нысанның төлсипат мәні,
М -белгінің арифметикалық ортасы,
p -үлгі нұсқаларының саны.
пайдалану арқылы есептеулерді орындау ыңғайлырақ жұмыс формуласы:
,
қайда Σ x² - барлық опциялар үшін сипаттамалық мәндердің квадраттарының қосындысы,
Σ x-атрибут мәндерінің қосындысы,
n- үлгі көлемі.
Ақылды дене салмағының мысалы үшін стандартты ауытқу: С= 0,897216496, және қажетті дөңгелектеуден кейін С= 0,897 г
Кейбір жағдайларда анықтау қажет болуы мүмкін өлшенген стандартты ауытқустандартты ауытқулары бұрыннан белгілі бірнеше үлгілерден тұратын жинақталған үлестірім үшін. Бұл мәселе мына формула арқылы шешіледі:
,
Қайда СΣ - жалпы таралу үшін стандартты ауытқудың орташа мәні,
С--- орташа алынған стандартты ауытқу мәндері,
p -жеке үлгілердің көлемі,
к- орташа алынған стандартты ауытқулар саны.
Осы мысалды қарастырайық. Маусым, шілде, тамыз және қыркүйек айларында бұталардың бауыр салмағын (мг) төрт тәуелсіз анықтау келесі стандартты ауытқуларды берді: 93, 83, 50, 71 (сағ. n= 17, 115, 132, 140). Қажетті мәндерді жоғарыдағы формулаға ауыстыра отырып, жалпы үлгі бойынша стандартты ауытқуларды аламыз (барлық қарсыз кезең үшін):
Бастапқы жағдайда статистикалық өңдеуүлгілердің үлкен саны, бірақ үлкен дәлдікпен міндетті емес, пайдалануға болады экспресс әдісізаңдарды білуге негізделген қалыпты таралу. Жоғарыда айтылғандай, үлгі үшін экстремалды мәндер (ықтималдықпен П= 95% 2 қашықтықтағы орташадан қашықтағы шекаралар деп санауға болады С: xмин = M − 2С, xмакс = M+ 2С. Бұл максимумнан ең төменгі іріктеу мәніне дейінгі диапазондағы шектің (Lim) төрт стандартты ауытқуға сәйкес келетінін білдіреді:
Лим = (M+ 2С) − (M − 2С) = 4С.
Дегенмен, бұл қорытынды тек үлкен үлгілер үшін жарамды, ал шағын үлгілер үшін түзетулер енгізу қажет. Стандартты ауытқуды шамамен есептеу үшін келесі формула ұсынылады (Ashmarin et al., 1975):
,
мәні қайда г 3-кестеден алынған (тиісті іріктеу өлшеміне қарсы, n).
3-кесте
Айырықша дене салмағының стандартты ауытқу үлгісі ( n= 63), жоғарыда көрсетілген формула бойынша есептелген:
С= (11,9 − 7,3) / 4 = 1,15 г,
бұл нақты мәнге жақын, С= 0,89 г.
Стандартты ауытқудың экспресс-бағалауын қолдану олардың дәлдігіне айтарлықтай әсер етпей, есептеу уақытын айтарлықтай қысқартады. Бұл әдіспен алынған стандартты ауытқу мәндерінің шағын іріктеу өлшемдері үшін асыра бағалану үрдісі шамалы ғана.
Стандартты ауытқу – аталған мән, сондықтан оны тек бірдей сипаттамалардың өзгеру сипатын салыстыру үшін пайдалануға болады. Әртүрлі өлшем бірліктерімен көрсетілген гетерогенді сипаттамалардың өзгермелілігін салыстыру, сондай-ақ өлшем шкаласының әсерін теңестіру үшін, деп аталатын вариация коэффициенті (CV), өлшемсіз шама, таңдауды бағалау коэффициенті Сорташа иелену М:
.
Біздің мысалда сиқырдың дене салмағымен:
9.6%.
Белгілердің жеке өзгергіштігі (вариация) биологиялық популяцияның, кез келген биологиялық процестің немесе құбылыстың ең сыйымды сипаттамаларының бірі болып табылады. Вариация коэффициентін белгінің абсолютті мәніне қарамастан популяцияның нақты әртүрлілігін жақсы көрсететін толықтай адекватты және объективті көрсеткіш деп санауға болады. Индекс әртүрлі немесе әртүрлі өлшемді белгілердің өзгергіштік көрсеткіштерін бір шкалаға келтіру арқылы біріздендіру үшін жасалған.
Тәжірибе көрсеткендей, көптеген биологиялық белгілер үшін олардың мәнінің (орта арифметикалық) жоғарылауымен өзгергіштіктің (стандартты ауытқу) жоғарылауы байқалады. Бұл ретте вариация коэффициенті шамамен бір деңгейде қалады – 8-15%. Әдетте, вариация коэффициентінің ұлғаюына қалыпты заңнан сипаттаманың таралуындағы өсу айырмашылықтары жауап береді.
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 (мәндер ауқымы).
Вариация коэффициенті пайызбен есептеледі. Сондықтан ұяшыққа пайыздық форматты орнатамыз.
Басқарудың араласуы ауытқулардың себептерін анықтау үшін қажет.
Бақылау диаграммасын құру үшін мен бастапқы деректерді, орташа (μ) және стандартты ауытқуды (σ) пайдаланамын. Excel бағдарламасында: μ = AVERAGE($F$3:$F$15), σ = СТАНДАРТТЫҚ ($F$3:$F$15)
Басқару диаграммасының өзі мыналарды қамтиды: бастапқы деректер, орташа (μ), төменгі бақылау шегі (μ – 2σ) және жоғарғы бақылау шегі (μ + 2σ):
Жазбаны форматта, мысалдарды форматта жүктеп алыңыз
Ұсынылған картаға қарап, мен бастапқы деректерде үстеме шығындар үлесінің төмендеуіне қарай өте айқын сызықтық тенденцияны көрсететінін байқадым:
Тренд сызығын қосу үшін диаграммадағы деректері бар жолды таңдаңыз (біздің мысалда жасыл нүктелер), тінтуірдің оң жақ түймешігімен басып, «Тренд сызығын қосу» опциясын таңдаңыз. Ашылған Trendline Format терезесінде опциялармен тәжірибе жасаңыз. Мен сызықтық трендке тоқтадым.
Егер бастапқы деректер орташа мәннің айналасында шашыраңқы болмаса, онда оларды μ және σ параметрлерімен сипаттау мүлде дұрыс емес. Сипаттама үшін орташа мәннің орнына сызықтық тренд сызығы және осы тренд сызығынан бірдей қашықтықтағы бақылау шекаралары жақсырақ.
Excel бағдарламасы FORECAST функциясын пайдаланып тренд сызығын құруға мүмкіндік береді. Бізге қосымша A3:A15 жолы қажет X-тің белгілі мәндеріболды үздіксіз қатар(блок сандары мұндай үздіксіз қатарды құрамайды). H бағанындағы орташа мәннің орнына БОЛЖАУ функциясын енгіземіз:
Стандартты ауытқу σ (Excel бағдарламасындағы STDEVAL функциясы) мына формула арқылы есептеледі:
Өкінішке орай, мен Excel бағдарламасында стандартты ауытқуды (трендке қатысты) осылай анықтайтын функцияны таппадым. Мәселені массив формуласы арқылы шешуге болады. Массив формулаларымен таныс емес адамдар үшін алдымен оларды оқып шығуды ұсынамын.
Жиым формуласы бір мәнді немесе массивті қайтара алады. Біздің жағдайда массив формуласы бір мәнді қайтарады:
Жиым формуласының G3 ұяшығында қалай жұмыс істейтінін толығырақ қарастырайық
SUM(($F$3:$F$15-$H$3:$H$15)^2) квадраттық айырмашылықтардың қосындысын анықтайды; шын мәнінде формула келесі қосындыны есептейді = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2
COUNTA($F$3:$F$15) – F3:F15 ауқымындағы мәндер саны
SQRT(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNTA($F$3:$F$15)-1)) = σ
6,2% мәні төменгі бақылау шегінің нүктесі = 8,3% – 2 σ
Формуланың екі жағындағы бұйра тырнақшалар оның массив формуласы екенін көрсетеді. G3 ұяшығына формуланы енгізгеннен кейін массив формуласын жасау үшін:
H4 – 2*ТҮБІР((($F$3:$F$15-$H$3:$H$15)^2)/(COUNT($F$3:$F$15)-1))
Enter пернелерін емес, Ctrl + Shift + Enter пернелерін басу керек. Пернетақтадан бұйра жақшаларды енгізуге тырыспаңыз - массив формуласы жұмыс істемейді. Жиым формуласын өңдеу қажет болса, оны кәдімгі формуламен бірдей орындаңыз, бірақ өңдеуді аяқтаған кезде Enter емес, Ctrl + Shift + Enter пернелерін басыңыз.
Жалғыз мәнді қайтаратын жиым формуласын кәдімгі формула сияқты «сүйреп апаруға» болады.
Нәтижесінде төмендеу үрдісі бар деректерге арналған бақылау диаграммасын алдық
P.S. Ескертпе жазылғаннан кейін мен трендтік деректер үшін стандартты ауытқуды есептеу үшін қолданылатын формулаларды нақтылай алдым. Оларды Excel файлында көруге болады
