Разлік давернага інтэрвалу ў 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. На экране з'явіцца яшчэ адно акно з наладамі аргументаў функцыі, якая выкарыстоўваецца для вызначэння колькасці вочак у зададзеным дыяпазоне, якія змяшчаюць лікавыя дадзеныя.

   Формула функцыі ПРАВЕРКА гэта напісана так: =СЧЁТ(Значение1;Значение2;...).

   Колькасць даступных аргументаў для гэтай функцыі можа быць да 255. Тут вы можаце напісаць альбо канкрэтныя лічбы, альбо адрасы вочак, альбо дыяпазоны вочак. Мы будзем выкарыстоўваць апошні варыянт. Для гэтага клікніце па вобласці ўводу інфармацыі для першага аргументу, затым, утрымліваючы левую кнопку мышы, вылучыце ўсе вочкі аднаго з слупкоў нашай табліцы (не лічачы загалоўка), а затым націсніце кнопку OK.

8. У выніку праведзеных дзеянняў у абранай вочку будзе адлюстраваны вынік разлікаў для аператара НОРМА ДАВЕРУ. У нашай задачы яго значэнне аказалася роўным 4,9583603.
9. Але гэта яшчэ не канчатковы вынік у нашай задачы. Далей трэба вылічыць сярэдняе значэнне за зададзены інтэрвал. Для гэтага трэба выкарыстоўваць функцыю «СЭРЦА”A, які выконвае задачу вылічэння сярэдняга значэння па вызначаным дыяпазоне даных.

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

   Вылучыце вочка, куды мы плануем ўставіць функцыю, і націсніце кнопку «Уставіць функцыю».

10. У катэгорыі «Статыстычны» выбраць сумнага аператара «СЭРЦА» і націсніце OK.
11. У аргументах функцыі ў значэнні аргумента «Нумар» пакажыце дыяпазон, які ўключае ўсе ячэйкі са значэннямі ўсіх вымярэнняў. Затым націскаем ДОБРА.
12. У выніку праведзеных дзеянняў сярэдняе значэнне будзе аўтаматычна вылічана і адлюстравана ў вочку з зноў устаўленай функцыяй.
13. Цяпер нам трэба вылічыць межы CI (даверны інтэрвал). Пачнем з разліку значэння правай мяжы. Мы выбіраем ячэйку, у якой хочам адлюстраваць вынік, і выконваем складанне атрыманых вынікаў з дапамогай аператараў «СЭРЦА” і "НОРМЫ ДАВЕРУ». У нашым выпадку формула выглядае так: A14+A16. Пасля ўводу націсніце Уводзіць.
14. У выніку будзе праведзены разлік, і вынік адразу адлюструецца ў вочку з формулай.
15. Затым аналагічным чынам выконваем разлік для атрымання значэння левай мяжы КІ. Толькі ў гэтым выпадку значэнне выніку «НОРМЫ ДАВЕРУ» вам трэба не складаць, а адымаць вынік, атрыманы з дапамогай аператара «СЭРЦА”. У нашым выпадку формула выглядае так: =A16-A14.
16. Пасля націску Enter мы атрымаем вынік у дадзенай вочку з формулай.

нататка: У абзацах вышэй мы паспрабавалі як мага больш падрабязна апісаць усе этапы і кожную выкарыстоўваную функцыю. Аднак усе прапісаныя формулы можна запісаць разам, у складзе адной вялікай:

• Для вызначэння правай мяжы КІ агульная формула будзе выглядаць так:

  =СРЗНАЧ(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=СЯРЭДНЯЯ+ДАВЭР СТУДЭНТА
   • Left Bound CI=СЯРЭДНЯЯ ДАВЕРУ СТУДЭНТА

заключэнне

Арсенал інструментаў Excel неверагодна вялікі, і разам з агульнымі функцыямі праграма прапануе шырокі спектр спецыяльных функцый, якія значна палегчаць працу з дадзенымі. Магчыма, некаторым карыстальнікам на першы погляд апісаныя вышэй дзеянні здадуцца складанымі. Але пасля дэталёвага вывучэння пытання і паслядоўнасці дзеянняў усё стане значна прасцей.