Пресметување интервал на доверба во Excel

Excel се користи за извршување на различни статистички задачи, од кои едната е пресметување на интервалот на доверба, кој се користи како најсоодветна замена за проценка на точка со мала големина на примерокот.

Сакаме веднаш да забележиме дека самата процедура за пресметување на интервалот на доверба е прилично комплицирана, меѓутоа, во Excel има голем број алатки дизајнирани да ја олеснат оваа задача. Ајде да ги погледнеме.

Пресметка на интервал на доверба

Потребен е интервал на доверливост за да се даде проценка на интервалот на некои статични податоци. Главната цел на оваа операција е да се отстранат неизвесностите на проценката на поени.

Постојат два методи за извршување на оваа задача во Microsoft Excel:

• Оператор НОРМА НА ДОВЕРБА – се користи во случаи кога е позната дисперзијата;
• Оператор ДОВЕРБА.УЧЕНИКкога варијансата е непозната.

Подолу чекор по чекор ќе ги анализираме двата методи во пракса.

Метод 1: Изјава TRUST.NORM

Оваа функција за прв пат беше воведена во арсеналот на програмата во изданието Excel 2010 (пред оваа верзија, таа беше заменета со операторот „ДОВЕРЛИВО”). Операторот е вклучен во категоријата „статистички“.

Формула за функции НОРМА НА ДОВЕРБА изгледа вака:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Како што можеме да видиме, функцијата има три аргументи:

• "Алфа" е показател за нивото на значајност, што се зема како основа за пресметка. Нивото на доверба се пресметува на следниов начин:
  • 1-"Альфа". Овој израз е применлив ако вредноста "Алфа" претставен како коефициент. На пример, 1-0,7 0,3 =, каде што 0,7=70%/100%.
  • (100-"Альфа")/100. Овој израз ќе се примени ако го земеме предвид нивото на доверба со вредноста "Алфа" во проценти. На пример, (100-70) / 100 = 0,3.
• "Стандардна девијација" – соодветно, стандардното отстапување на анализираниот примерок на податоци.
• „Големина“ е големината на примерокот на податоци.

Забелешка: За оваа функција, предуслов е присуството на сите три аргументи.

Оператор“ДОВЕРЛИВО”, што се користеше во претходните верзии на програмата, ги содржи истите аргументи и ги извршува истите функции.

Формула за функции ВЕРУВА како што следува:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

Нема разлики во самата формула, само името на операторот е различно. Во Excel 2010 и подоцнежните изданија, овој оператор е во категоријата Компатибилност. Во постарите верзии на програмата, таа се наоѓа во делот за статички функции.

Границата на интервалот на доверба се одредува со следнава формула:

X+(-)ДОВЕРИТ.НОРМ

каде Х е просечната вредност над наведениот опсег.

Сега да видиме како да ги примениме овие формули во пракса. Значи, имаме табела со различни податоци од 10 направени мерења. Во овој случај, стандардното отстапување на множеството податоци е 8.

Наша задача е да ја добиеме вредноста на интервалот на доверба со 95% ниво на доверба.

1. Прво, изберете ќелија за прикажување на резултатот. Потоа кликнуваме на копчето „Вметни функција“ (лево од лентата со формули).
2. Се отвора прозорецот Function Wizard. Со кликнување на тековната категорија на функции, проширете ја листата и кликнете на линијата во неа „Статистички“.
3. Во предложената листа, кликнете на операторот „НОРМА НА ДОВЕРБА“, потоа притиснете OK.
4. Ќе видиме прозорец со поставките на функционалните аргументи, пополнувајќи го што го притискаме копчето OK.
   • на терен "Алфа" укажуваат на нивото на значајност. Нашата задача претпоставува ниво на доверба од 95%. Заменувајќи ја оваа вредност во формулата за пресметка, која ја разгледавме погоре, го добиваме изразот: (100-95)/100. Ние го пишуваме во полето за аргументи (или можете веднаш да го напишете резултатот од пресметката еднаков на 0,05).
   • на терен „std_off“ според нашите услови го пишуваме бројот 8.
   • во полето „Големина“, наведете го бројот на елементи што треба да се испитаат. Во нашиот случај беа направени 10 мерења, па го пишуваме бројот 10.
5. За да избегнете повторно конфигурирање на функцијата кога податоците се менуваат, можете да ја автоматизирате. За ова ја користиме функцијата „ПРОВЕРЕТЕ“. Ставете го покажувачот во влезната област на информациите за аргументот „Големина“, потоа кликнете на иконата за триаголник на левата страна од лентата со формули и кликнете на ставката „Повеќе карактеристики…“.
6. Како резултат на тоа, ќе се отвори друг прозорец на Волшебникот за функции. Со избирање на категорија „Статистички“, кликнете на функцијата „ПРОВЕРЕТЕ“, тогаш ОК.
7. На екранот ќе се прикаже друг прозорец со поставките на аргументите на функцијата, кој се користи за одредување на бројот на ќелии во даден опсег што содржат нумерички податоци.

   Формула за функции ПРОВЕРКА напишано е вака: =СЧЁТ(Значение1;Значение2;...).

   Бројот на достапни аргументи за оваа функција може да биде до 255. Овде можете да напишете или специфични броеви, или адреси на ќелии или опсези на ќелии. Ќе ја искористиме последната опција. За да го направите ова, кликнете на областа за внесување информации за првиот аргумент, потоа, држејќи го левото копче на глувчето, изберете ги сите ќелии од една од колоните на нашата табела (не сметајќи го заглавјето), а потоа притиснете го копчето OK.

8. Како резултат на преземените активности, резултатот од пресметките за операторот ќе се прикаже во избраната ќелија НОРМА НА ДОВЕРБА. Во нашиот проблем, неговата вредност се покажа еднаква на 4,9583603.
9. Но, ова се уште не е конечниот резултат во нашата задача. Следно, треба да ја пресметате просечната вредност во даден интервал. За да го направите ова, треба да ја користите функцијата „СРЦЕ“A што ја извршува задачата за пресметување на просекот во одреден опсег на податоци.

   Формулата на операторот е напишана вака: =СРЗНАЧ(число1;число2;...).

   Изберете ја ќелијата каде што планираме да ја вметнеме функцијата и притиснете го копчето „Вметни функција“.

10. Во категоријата „Статистички“ изберете здодевен оператор „СРЦЕ“ и кликнете на OK.
11. Во функциските аргументи во вредност на аргументот „Број“ наведете го опсегот, кој ги вклучува сите ќелии со вредностите на сите мерења. Потоа кликнуваме ДОБРО.
12. Како резултат на преземените дејства, просечната вредност автоматски ќе се пресмета и ќе се прикаже во ќелијата со нововметнатата функција.
13. Сега треба да ги пресметаме границите CI (интервал на доверба). Да почнеме со пресметување на вредноста на десната граница. Ја избираме ќелијата каде што сакаме да го прикажеме резултатот и вршиме додавање на добиените резултати со помош на операторите “СРЦЕ“ и "НОРМИ ЗА ДОВЕРБА“. Во нашиот случај, формулата изгледа вака: A14+A16. Откако ќе го напишете, притиснете Внесете.
14. Како резултат на тоа, пресметката ќе се изврши и резултатот веднаш ќе се прикаже во ќелијата со формулата.
15. Потоа, на сличен начин, ја вршиме пресметката за да ја добиеме вредноста на левата граница на CI. Само во овој случај вредноста на резултатот “НОРМИ ЗА ДОВЕРБА“ не треба да додавате, туку одземете од резултатот добиен со помош на операторот “СРЦЕ“. Во нашиот случај, формулата изгледа вака: =A16-A14.
16. Откако ќе притиснете Enter, ќе го добиеме резултатот во дадената ќелија со формулата.

Забелешка: Во горенаведените параграфи, се обидовме да ги опишеме колку што е можно подетално сите чекори и секоја користена функција. Сепак, сите пропишани формули можат да се напишат заедно, како дел од една голема:

• За да се одреди десната граница на CI, општата формула ќе изгледа вака:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• Слично на тоа, за левата граница, само наместо плус, треба да ставите минус:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Метод 2: ДОВЕРБА.УЧЕНИК оператор

Сега, ајде да се запознаеме со вториот оператор за одредување на интервалот на доверба − ДОВЕРБА.УЧЕНИК. Оваа функција беше воведена во програмата релативно неодамна, почнувајќи од верзијата на Excel 2010, и е насочена кон одредување на CI на избраната база на податоци користејќи ја распределбата на Студентот, со непозната варијанса.

Формула за функции ДОВЕРБА.УЧЕНИК како што следува:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

Ајде да ја анализираме примената на овој оператор на примерот на истата табела. Само сега не ја знаеме стандардната девијација според условите на проблемот.

1. Прво, изберете ја ќелијата каде што планираме да го прикажеме резултатот. Потоа кликнете на иконата „Вметни функција“ (лево од лентата со формули).
2. Ќе се отвори веќе добро познатиот прозорец на Function Wizard. Изберете категорија „Статистички“, потоа од предложената листа на функции, кликнете на операторот „ДОВЕРБЕН СТУДЕНТ“, тогаш - OK.
3. Во следниот прозорец, треба да ги поставиме функционалните аргументи:
   • Во "Алфа" како и во првиот метод, наведете ја вредноста 0,05 (или „100-95)/100“).
   • Да преминеме на аргументот. „std_off“. Бидејќи според условите на проблемот, неговата вредност ни е непозната, треба да ги направиме соодветните пресметки, во кои операторот „STDEV.B“. Кликнете на копчето за додавање функција, а потоа на ставката „Повеќе карактеристики…“.
   • Во следниот прозорец на Волшебникот за функции, изберете го операторот „STDEV.B“ во категоријата „Статистички“ и кликнете на OK.
   • Влегуваме во прозорецот за поставки за аргументи за функции, чија формула изгледа вака: =СТАНДОТКЛОН.В(число1;число2;...). Како прв аргумент, одредуваме опсег што ги вклучува сите ќелии во колоната „Вредност“ (не сметајќи го заглавието).
   • Сега треба да се вратите во прозорецот со аргументите на функцијата “ДОВЕРБА.СТУДЕНТ“. За да го направите ова, кликнете на натписот со исто име во полето за внесување формула.
   • Сега да преминеме на последниот аргумент „Големина“. Како и во првиот метод, тука можете или едноставно да наведете опсег на ќелии или да го вметнете операторот „ПРОВЕРЕТЕ“. Ја избираме последната опција.
   • Откако ќе се пополнат сите аргументи, кликнете на копчето OK.
4. Избраната ќелија ќе ја прикаже вредноста на интервалот на доверба според параметрите што ги наведовме.
5. Следно, треба да ги пресметаме вредностите на границите на CI. И за ова треба да ја добиете просечната вредност за избраниот опсег. За да го направите ова, повторно ја применуваме функцијата „СРЦЕ“. Алгоритмот на дејства е сличен на оној опишан во првиот метод.
6. Откако ја добив вредноста „СРЦЕ“, можете да започнете со пресметување на границите на CI. Самите формули не се разликуваат од оние што се користат со „НОРМИ ЗА ДОВЕРБА“:
   • Десна граница CI=ПРОСЕЧНА+ДОВЕРБА НА СТУДЕНТИТЕ
   • Лево гранично CI=ПРОСЕЧНА-УЧЕНИЧКА ДОВЕРБА

Заклучок

Арсеналот на алатки на Excel е неверојатно голем, а заедно со вообичаените функции, програмата нуди широк спектар на специјални функции кои многу ќе ја олеснат работата со податоци. Можеби чекорите опишани погоре може да изгледаат комплицирани за некои корисници на прв поглед. Но, по детално проучување на прашањето и редоследот на дејства, сè ќе стане многу полесно.