ВАРИАНТ - Excel и Google Таблици

Този урок демонстрира как да използвате Функция Excel VARIANCE в Excel за оценка на дисперсията въз основа на дадена извадка.

Преглед на функцията VARIANCE

Функцията ВАРИАНС Изчислява вариацията на оценката въз основа на дадена извадка.

За да използвате функцията VARIANCE Excel на работен лист, изберете клетка и въведете:

(Забележете как се появяват входовете на формулата)

Функция VARIANCE Синтаксис и входове:

1	= VAR (номер1, [номер2], …)

числа- Стойности за получаване на вариация

Как да изчислим вариацията в Excel

Дисперсията ви казва колко разпределени са стойностите в набор от данни от средната стойност. От математическа гледна точка, отклонението е средната стойност на квадратната разлика на всеки резултат от средната стойност (но ще стигнем до това скоро).

Excel ви дава редица функции за изчисляване на дисперсията - VAR.S, VAR.P, VARA, VARPA и две по -стари функции, VAR и VARP.

Преди да се задълбочим в тези функции и да се научим как да ги използваме, нека поговорим за вариацията и как се изчислява.

Какво представлява вариацията?

При анализиране на данни обща първа стъпка е да се изчисли средната стойност. Разбира се, това е полезна статистика за изчисляване, но не ви дава пълна представа за това, което се случва с вашите данни.

Вземете следния набор от данни, който може да е група от резултатите от теста, отбелязани от 100:

1	48,49,50,51,52

Средната стойност на този диапазон е 50 (сумирайте числата и разделете на n, където n е броят на стойностите).

След това вземете следния набор от резултати от теста:

1	10,25,50,75,90

Средната стойност на този диапазон е също 50 - но очевидно имаме два много различни диапазона от данни тук.

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

Дисперсията се използва и като базова точка за редица по -сложни статистически процедури.

Как се изчислява вариацията

Нека да разгледаме един основен пример и да изчислим отклонението на ръка. По този начин ще знаете какво се случва зад кулисите, когато всъщност започнете да прилагате функциите за дисперсия на Excel в действие.

Да речем, че имаме набор от данни, представящ три карти за игра, 4, 6 и 8.

За да изчислите дисперсията, работите по този процес:

1) Изчислете средната стойност

Първо изчисляваме средната стойност. Знаем, че нашият диапазон от данни е 4, 6, 8, така че средната стойност ще бъде:

1	(6 + 4 + 8) / 3 = 6

Потвърдих това по -долу с функцията Excel AVERAGE <>:

1	= СРЕДНО (C4: C6)

2) Извадете средната стойност от всяка стойност в набора от данни

След това изваждаме средната стойност от всяка от нашите стойности.

Направих това със следната формула:

1	= C4- $ H $ 4

Средната стойност се съхранява в H4, така че просто изваждам това от всяка стойност в таблицата. Знакът за долар тук просто „заключва“ тази клетъчна препратка към H4, така че когато я копирам в колоната, тя остава същата.

Резултатите:

Ние имаме:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Трябва да извлечем средната стойност на тези разлики от средната стойност, но средната стойност на тези три стойности е нула! Затова трябва да подчертаем различията, които правим, като ги квадратираме.

3) Квадрат разликите

Нека добавим нова колона и начертаем числата в колоната D:

1

= D4*D4

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

4) Изчислете средната стойност на квадратните разлики

Тук срещаме разклонение на пътя. Има два начина за изчисляване на дисперсията и този, който използвате, зависи от вида на данните, с които разполагате.

• Ако използвате данни за населението, просто приемате средната стойност като нормална (сумирате стойностите и разделяте на n)
• Ако използвате примерни данни, сумирате стойностите и разделяте на n-1

Данните за населението означават, че имате съвкупност от необходимите данни, например, ако искате средната възраст на учителите в определено училище и имате данни за възрастта за всеки учител в това училище, имате данни за населението.

Примерни данни означават, че нямате всичките си данни, а само извадка, взета от по -голяма популация. Така че, ако искате средната възраст на учителите в цялата страна и имате само данни за учители в едно училище, имате примерни данни.

В нашия пример имаме данни за населението. Интересуваме се само от трите ни карти - това е населението и не сме взели извадка от тях. Така че можем просто да вземем средната стойност на квадратните разлики по нормалния начин:

1	= СРЕДНО (E4: E8)

Така че дисперсията на нашето население е 2.666.

Ако това беше примерни данни (може би сме извадили тези три карти от по -голям набор), бихме изчислили средната стойност, както следва:

1	Примерна дисперсия = (4 + 0 + 4) / (3 - 1)

Или:

1	Примерна дисперсия = 8 /2 = 4

Защо да разделяте на n-1 с примерни данни, вместо само с n?

Краткият отговор на този въпрос е „Защото дава правилен отговор“. Но си представям, че ще искате малко повече от това! Това е сложна тема, затова ще дам кратък преглед тук.

Мислете за това така: ако вземете извадка от данни от популация, тези стойности ще бъдат по -близо до средната стойност на проба отколкото те са до средата на население.

Това означава, че ако просто разделите на n, ще подцените малко вариацията на населението. Разделянето на n-1 коригира това малко.

С нашия набор от три карти сме на добро място да тестваме тази теория. Тъй като има само три карти, евентуално можем да вземем малък брой проби.

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

Само с девет възможни извадки можем да изчислим всяка възможна вариация на извадката, като използваме двата метода (разделим на n и разделим на n-1), вземем средната стойност от тях и ще видим кой от тях ни дава правилния отговор.

В таблицата по -долу изложих всичко. Всеки ред в таблицата е различна извадка, а колоните B и C показват двете карти, които са избрани във всяка извадка. След това добавих още две колони: една, в която изчислих дисперсията на тази извадка от две карти, като разделя на n, и друга, където разделих на n - 1.

Погледни:

Вдясно от таблицата показах средните стойности на колони D и E.

Средната стойност на колона D, разделена на n, ни дава вариация от 1,333.

Средната стойност на колона E, разделена на n-1, ни дава вариация от 2.666.

Вече знаем от предишния ни пример, че дисперсията на популацията е 2,666. Така че разделянето на n-1 при използване на примерни данни ни дава по-точни оценки.

Функциите на Excel за изчисляване на вариацията

Сега, когато видяхте пример за това как се изчислява дисперсията, нека преминем към функциите на Excel.

Тук имате няколко възможности:

• P връща дисперсията за данните за населението (използвайки метода разделяне по n)
• С връща дисперсията за примерни данни (разделена на n-1)
• VAR е по -стара функция, която работи по абсолютно същия начин като VAR.S
• VARA е същото като VAR.S, с изключение на това, че включва текстови клетки и булеви стойности
• VARPA е същото като VAR.P, с изключение на това, че включва текстови клетки и булеви стойности

Нека преминем през тези един по един.

Функцията Excel VAR.P

VAR.P изчислява вариацията за данните за населението (използвайки метода разделяне по n). Използвайте го така:

1	= VAR.P (C4: C6)

Вие дефинирате само един аргумент във VAR.P: диапазонът от данни, за който искате да изчислите дисперсията. В нашия случай това са стойностите на картите в C4: C6.

Както виждате по -горе, VAR.P връща 2.666 за нашия набор от три карти. Това е същата стойност, която изчислихме на ръка по -рано.

Обърнете внимание, че VAR.P напълно игнорира клетки, съдържащи текст или булеви (TRUE/FALSE) стойности. Ако трябва да ги включите, използвайте VARPA вместо това.

Функцията Excel VAR.S

VAR.S изчислява вариацията за примерни данни (разделена на n-1). Използваш го така:

1	= VAR.S (C4: C6)

Отново има само един аргумент - диапазонът от данни.

В този случай VAR.S връща 4. Получихме същата цифра в стъпка 4, когато направихме ръчното изчисление по -горе.

VAR.S напълно игнорира клетки, съдържащи текст или булеви (TRUE/FALSE) стойности. Ако трябва да ги включите, използвайте VARA вместо това.

Функцията Excel VAR

VAR е напълно еквивалентен на VAR.S: изчислява отклоненията за примерни данни (използвайки метода n-1). Ето как да го използвате:

1	= VAR (C4: C6)

VAR е „функция за съвместимост“. Това означава, че Microsoft е в процес на премахване на тази функция от Excel. В момента той все още е достъпен за използване, но вместо това трябва да използвате VAR.S, така че вашите електронни таблици да останат съвместими с бъдещите версии на Excel.

Функцията Excel VARA

VARA също връща дисперсията на примерните данни, но има някои ключови разлики спрямо VAR и VAR.S. А именно, тя включва булеви и текстови стойности в изчислението си:

• ИСТИНСКИ стойности се отчитат като 1
• FALSE стойностите се отчитат като 0
• Текстовите низове се броят като 0

Ето как го използвате:

1	= VARA (C4: C11)

Добавихме още пет реда към таблицата: J, Q, K, TRUE и FALSE. Колона D показва как VARA интерпретира тези стойности.

Тъй като сега имаме нова партида с ниски стойности в нашата таблица, отклонението се е увеличило до 10.268.

Функцията Excel VARPA

VARPA изчислява вариацията за данните за населението. Той е подобен на VAR.P, с изключение на това, че включва и булеви стойности и текстови низове в изчислението:

• ИСТИНСКИ стойности се отчитат като 1
• FALSE стойностите се отчитат като 0
• Текстовите низове се броят като 0

Използваш го така:

1	= VARPA (C4: C12)

Добавихме още пет реда към таблицата: J, Q, K, TRUE и FALSE. Колона D показва как VARPA интерпретира тези стойности.

В резултат на добавянето на тази група от по -ниски стойности към данните, отклонението се е увеличило до 8.984.

Функция VARIANCE в Google Sheets

Функцията CORREL работи абсолютно същото в Google Sheets като в Excel: