В статистическом анализе дисперсия среди членов набора данных показывает, как далеко друг от друга точки данных находятся от линии тренда, также известной как линия регрессии, Чем выше дисперсия, тем больше разбросаны точки данных. Исследование дисперсионного анализа показывает, какие части дисперсии можно объяснить характеристиками данных, а какие можно отнести к случайным факторам. Часть дисперсии, которая не может быть объяснена, называется остаточной дисперсией.
Использование электронных таблиц Excel для расчета остаточной дисперсии
Формула для расчета остаточной дисперсии включает в себя многочисленные сложные вычисления. Для небольших наборов данных процесс вычисления остаточной дисперсии вручную может быть утомительным. Для больших наборов данных задача может быть утомительной. Используя электронную таблицу Excel, вам нужно всего лишь ввести точки данных и выбрать правильную формулу. Программа обрабатывает сложные расчеты и быстро выдает результат.
Точки данных
Откройте новую электронную таблицу Excel и введите точки данных в два столбца. Линии регрессии требуют, чтобы каждая точка данных имела два элемента. Статистики обычно обозначают эти элементы как «X» и «Y». Например, Generic Insurance Co. хочет найти остаточную дисперсию роста и веса своих сотрудников. Переменная X представляет рост, а переменная Y представляет вес. Введите значения высоты в столбце A, а веса - в столбце B.
В поисках среднего
имею в виду представляет среднее для каждого элемента в наборе данных. В этом примере Generic Insurance хочет найти среднее, стандартное отклонение и ковариацию высот и весов 10 сотрудников. Среднее значение высоты, указанное в столбце A, можно найти, введя функцию "= AVERAGE (A1: A10)" в ячейку F1. Среднее значение весов, перечисленных в столбце B, можно найти, введя функцию "= AVERAGE (B1: B10)" в ячейку F3.
Нахождение стандартного отклонения и ковариации
стандартное отклонение измеряет расстояние между точками данных от среднего значения. ковариационная измеряет, насколько два элемента точки данных изменяются вместе. Стандартное отклонение высот находится путем ввода функции "= STDEV (A1: A10)" в ячейку F2. Стандартное отклонение весов находится путем ввода функции "= STDEV (B1: B10)" в ячейку F4. Ковариация между высотами и весами определяется путем ввода функции "= COVAR (A1: A10; B1: B10)" в ячейку F5.
Нахождение линии регрессии
линия регрессии представляет линейную функцию, которая следует за тенденцией точек данных. Формула для линии регрессии выглядит следующим образом: Y = aX + b.
Пользователь может найти значения для «a» и «b», используя вычисления для средних значений, стандартных отклонений и ковариации. Значение для «b» представляет точку, где линия регрессии пересекает ось Y. Значение можно найти, взяв ковариацию и разделив ее на квадрат стандартного отклонения значений X. Формула Excel помещается в ячейку F6 и выглядит следующим образом: = F5 / F2 ^ 2.
Значение для «а» представляет наклон линии регрессии. Формула Excel переходит в ячейку F7 и выглядит следующим образом: = F3-F6 * F1.
Чтобы увидеть формулу для линии регрессии, введите эту конкатенацию строк в ячейку F8:
= CONCATENATE ("Y ="; КРУГЛЫЙ (F6; 2); "X"; IF (ЗНАК (F7) = 1; "+"; "-"); ABS (КРУГЛЫЙ (F7; 2)))
Рассчитать значения Y
Следующим шагом является вычисление значений Y на линии регрессии для данных значений X в наборе данных. Формула для поиска значений Y помещается в столбец C и выглядит следующим образом:
= $ F $ 6 * А (я) + $ F $ 7
Где A (i) - это значение для столбца A в строке (i). Формулы выглядят так в таблице:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7 и т. Д.
Записи в столбце D показывают различия между ожидаемыми и фактическими значениями для Y. Формулы выглядят так:
= B (I) -C (я), Где B (i) и C (i) - значения в строке (i) в столбцах B и C соответственно.
Нахождение остаточной дисперсии
формула для остаточной дисперсии переходит в Cell F9 и выглядит так:
= СУММКВ (D1: D10) / (COUNT (D1: D10) -2)
Где SUMSQ (D1: D10) - сумма квадратов разностей между фактическим и ожидаемым значениями Y, а (COUNT (D1: D10) -2) - количество точек данных, минус 2 для степеней свободы в данные.
