Изчисляване на доверителния интервал в 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. Отваря се прозорецът на съветника за функции. Като щракнете върху текущата категория функции, разгънете списъка и щракнете върху реда в него „Статистически“.
3. В предложения списък щракнете върху оператора „НОРМА ЗА ДОВЕРИЕ“, След това натиснете OK.
4. Ще видим прозорец с настройките на аргументите на функцията, попълвайки които натискаме бутона OK.
   • в областта "Алфа" посочете нивото на значимост. Нашата задача предполага 95% ниво на сигурност. Замествайки тази стойност във формулата за изчисление, която разгледахме по-горе, получаваме израза: (100-95)/100. Пишем го в полето за аргумент (или можете веднага да напишете резултата от изчислението, равен на 0,05).
   • в областта „std_off“ според нашите условия пишем числото 8.
   • в полето „Размер“ посочете броя на елементите, които ще бъдат изследвани. В нашия случай са направени 10 измервания, така че записваме числото 10.
5. За да избегнете необходимостта от преконфигуриране на функцията при промяна на данните, можете да я автоматизирате. За целта използваме функцията „ПРОВЕРКА”. Поставете показалеца в областта за въвеждане на информацията за аргумента "Размер", след това щракнете върху иконата на триъгълник от лявата страна на лентата с формули и щракнете върху елемента „Още функции…“.
6. В резултат на това ще се отвори друг прозорец на съветника за функции. Чрез избор на категория „Статистически“, щракнете върху функцията „ПРОВЕРКА”, добре тогава.
7. На екрана ще се покаже друг прозорец с настройките на аргументите на функцията, която се използва за определяне на броя на клетките в даден диапазон, които съдържат числови данни.

   Формула на функцията CHECK написано е така: =СЧЁТ(Значение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: Оператор TRUST.STUDENT

Сега нека се запознаем с втория оператор за определяне на доверителния интервал − ДОВЕРИЕ.СТУДЕНТ. Тази функция беше въведена в програмата сравнително наскоро, започвайки от версията на Excel 2010, и е насочена към определяне на CI на избрания набор от данни, използвайки разпределението на Студент, с неизвестна вариация.

Формула на функцията ДОВЕРИЕ.СТУДЕНТ , както следва:

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

Нека анализираме приложението на този оператор на примера на същата таблица. Само че сега не знаем стандартното отклонение според условията на проблема.

1. Първо изберете клетката, в която планираме да покажем резултата. След това щракнете върху иконата „Вмъкване на функция“ (отляво на лентата с формули).
2. Ще се отвори вече добре познатият прозорец на съветника за функции. Избери категория „Статистически“, след което от предложения списък с функции щракнете върху оператора „ДОВЕРЕН СТУДЕНТ“, тогава - 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 е невероятно голям и заедно с общите функции, програмата предлага голямо разнообразие от специални функции, които ще направят работата с данни много по-лесна. Може би стъпките, описани по-горе, може да изглеждат сложни за някои потребители на пръв поглед. Но след подробно проучване на проблема и последователността от действия, всичко ще стане много по-лесно.