Регрессионная статистика в excel значения. Корреляционно-регрессионный анализ в MS EXCEL

Известна тем, что она полезна в разных областях деятельности, включая и такую дисциплину, как эконометрика, где в работе используется данная программная утилита. В основном все действия практических и лабораторных занятий выполняют в Excel, которая существенно облегчает работу, давая подробные объяснения тех или иных действий. Так, один из инструментов анализа «Регрессия» применяется с целью подбора графика для набора наблюдений за счет метода наименьших квадратов. Рассмотрим, что представляет собой данный инструмент программы и в чем заключается его польза для пользователей. Ниже также предоставлена краткая, но понятная инструкция построения регрессионной модели.

Основные задачи и виды регрессии

Регрессия представляет собой зависимость между заданными переменными, за счет чего можно определить прогноз будущего поведения данных переменных. Переменные — это различные периодические явления, включая и поведение человека. Такой анализ программы Excel применяется для того, чтобы проанализировать воздействие на конкретную зависимую переменную значений одной или некоторым количеством переменных. К примеру, на продажи в магазине влияет несколько факторов, включая ассортимент, цены и место локализации магазина. Благодаря регрессии в Excel можно определять степень влияния каждого из указанных факторов по результатам имеющихся продаж, а после применить полученные данные для прогнозирования продаж на другой месяц или для другого магазина, расположенного рядом.

Обычно регрессия представлена в виде простого уравнения, раскрывающего зависимости и силу связи между двумя группами переменных, где одна группа является зависимой или эндогенной, а другая — независимой или экзогенной. При наличии группы взаимосвязанных показателей зависимая переменная Y определяется исходя из логики рассуждений, а остальные выступают в роли независимых Х-переменных.

Основные задачи построения регрессионной модели заключаются в следующем:

1. Отбор значимых независимых переменных (Х1, Х2, …, Xk).
2. Выбор вида функции.
3. Построение оценок для коэффициентов.
4. Построение доверительных интервалов и функции регрессии.
5. Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

• парный (1 зависимая и 1 независимая переменные);
• множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

1. Линейные, иллюстрирующие строгую линейную связь между переменными.
2. Нелинейные — уравнения, которые могут включать степени, дроби и тригонометрические функции.

Инструкция построения модели

Чтобы выполнить заданное построение в Excel, необходимо следовать указаниям:

Для дальнейшего вычисления следует использоваться функцию «Линейн ()», указывая Значения Y, Значения Х, Конст и статистику. После этого определите множество точек на линии регрессии с помощью функции «Тенденция» — Значения Y, Значения Х, Новые значения, Конст. При помощи заданных параметров вычислите неизвестное значение коэффициентов, опираясь на заданные условия поставленной задачи.

Статистическая обработка данных может также проводиться с помощью надстройки ПАКЕТ АНАЛИЗА (рис. 62).

Из предложенных пунктов выбирает пункт «РЕГРЕССИЯ » и щелкаем на нем левой кнопкой мыши. Далее нажимаем ОК.

Появится окно, показанное на рис. 63.

Инструмент анализа «РЕГРЕССИЯ » применяется для подбора графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или нескольких независимых переменных. Например, на спортивные качества атлета влияют несколько факторов, включая возраст, рост и вес. Можно вычислить степень влияния каждого из этих трех факторов по результатам выступления спортсмена, а затем использовать полученные данные для предсказания выступления другого спортсмена.

Инструмент «Регрессия» использует функцию ЛИНЕЙН .

Диалоговое окно «РЕГРЕССИЯ»

Метки Установите флажок, если первая строка или первый столбец входного диапазона содержит заголовки. Снимите этот флажок, если заголовки отсутствуют. В этом случае подходящие заголовки для данных выходной таблицы будут созданы автоматически.

Уровень надежности Установите флажок, чтобы включить в выходную таблицу итогов дополнительный уровень. В соответствующее поле введите уровень надежности, который следует применить, дополнительно к уровню 95%, применяемому по умолчанию.

Константа - ноль Установите флажок, чтобы линия регрессии прошла через начало координат.

Выходной интервал Введите ссылку на левую верхнюю ячейку выходного диапазона. Отведите как минимум семь столбцов для выходной таблицы итогов, которая будет включать в себя: результаты дисперсионного анализа, коэффициенты, стандартную погрешность вычисления Y, среднеквадратичные отклонения, число наблюдений, стандартные погрешности для коэффициентов.

Новый рабочий лист Установите переключатель в это положение, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки A1. При необходимости введите имя для нового листа в поле, расположенном напротив соответствующего положения переключателя.

Новая рабочая книга Установите переключатель в это положение для создания новой книги, в которой результаты будут добавлены в новый лист.

Остатки Установите флажок для включения остатков в выходную таблицу.

Стандартизированные остатки Установите флажок для включения стандартизированных остатков в выходную таблицу.

График остатков Установите флажок для построения графика остатков для каждой независимой переменной.

График подбора Установите флажок для построения графика зависимости предсказанных значений от наблюдаемых.

График нормальной вероятности Установите флажок, для построения графика нормальной вероятности.

Функция ЛИНЕЙН

Для проведения расчетов выделяем курсором ячейку, в которой хотим отобразить среднее значение и нажимаем на клавиатуре клавишу =. Далее в поле Имя указываем нужную функцию, например СРЗНАЧ (рис. 22).

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Можно также объединять функцию ЛИНЕЙН с другими функциями для вычисления других видов моделей, являющихся линейными в неизвестных параметрах (неизвестные параметры которых являются линейными), включая полиномиальные, логарифмические, экспоненциальные и степенные ряды. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.

Уравнение для прямой линии имеет следующий вид:

y=m 1 x 1 +m 2 x 2 +…+b (в случае нескольких диапазонов значений x),

где зависимое значение y - функция независимого значения x, значения m - коэффициенты, соответствующие каждой независимой переменной x, а b - постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив{mn;mn-1;…;m 1 ;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

ЛИНЕЙН (известные_значения_y; известные_значения_x; конст; статистика)

Известные_значения_y - множество значений y, которые уже известны для соотношения y=mx+b.

Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x - необязательное множество значений x, которые уже известны для соотношения y=mx+b.

Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы_известные_значения_y и известные_значения_x могут иметь любую форму - при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).

Если массив_известные_значения_x опущен, то предполагается, что этот массив {1;2;3;...} имеет такой же размер, как и массив_известные_значения_y.

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

Если аргумент «конст» имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.

Если аргумент «конст» имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y=mx.

Статистика - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

Если аргумент «статистика» имеет значение ИСТИНА, функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Возвращаемый массив будет иметь следующий вид: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid}.

Если аргумент «статистика» имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика.(табл.17)

Величина	Описание
se1,se2,...,sen	Стандартные значения ошибок для коэффициентов m1,m2,...,mn.
seb	Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент «конст» имеет значение ЛОЖЬ).
r2	Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. различия между фактическим и оценочным значениями y не существует. В противоположном случае, если коэффициент детерминированности равен 0, использовать уравнение регрессии для предсказания значений y не имеет смысла. Для получения дополнительных сведений о способах вычисления r2, см. «Замечания» в конце данного раздела.
sey	Стандартная ошибка для оценки y.
F	F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.
df	Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Для получения дополнительных сведений о вычислении величины df см. «Замечания» в конце данного раздела. Далее в примере 4 показано использование величин F и df.
ssreg	Регрессионная сумма квадратов.
ssresid	Остаточная сумма квадратов. Для получения дополнительных сведений о расчете величин ssreg и ssresid см. «Замечания» в конце данного раздела.

На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика (рис. 64).

Замечания:

Любую прямую можно описать ее наклоном и пересечением с осью y:

Наклон (m): чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x 1 ,y 1) и(x 2 ,y 2); наклон будет равен (y 2 -y 1)/(x 2 -x 1).

Y-пересечение (b): Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.

Уравнение прямой имеет вид y=mx+b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.

Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:

Наклон: ИНДЕКС (ЛИНЕЙН(известные_значения_y; известные_значения_x); 1)

Y-пересечение: ИНДЕКС (ЛИНЕЙН (известные_значения_y; известные_значения_x); 2)

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН. Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшей аппроксимации данных. Когда имеется только одна независимая переменная x, m и b вычисляются по следующим формулам:

где x и y – выборочные средние значения, например x = СРЗНАЧ (известные_значения_x), а y = СРЗНАЧ (известные_значения_y).

Функции аппроксимации ЛИНЕЙН и ЛГРФПРИБЛ могут вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую данные. Однако они не дают ответа на вопрос, какой из двух результатов больше подходит для решения поставленной задачи. Можно также вычислить функцию ТЕНДЕНЦИЯ (известные_значения_y; известные_значения_x) для прямой или функцию РОСТ(известные_значения_y; известные_значения_x) для экспоненциальной кривой. Эти функции, если не задавать аргумент новые_значения_x, возвращают массив вычисленных значений y для фактических значений x в соответствии с прямой или кривой. После этого можно сравнить вычисленные значения с фактическими значениями. Можно также построить диаграммы для визуального сравнения.

Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов, тем больше значение коэффициента детерминированности r2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными. Коэффициент r2 равен ssreg/sstotal.

В некоторых случаях один или более столбцов X (пусть значения Y и X находятся в столбцах) не имеет дополнительного предикативного значения в других столбцах X. Другими словами, удаление одного или более столбцов X может привести к значениям Y, вычисленным с одинаковой точностью. В этом случае избыточные столбцы X будут исключены из модели регрессии. Этот феномен называется «коллинеарностью», поскольку избыточные столбцы X могут быть представлены в виде суммы нескольких неизбыточных столбцов. Функция ЛИНЕЙН проверяет на коллинеарность и удаляет из модели регрессии все избыточные столбцы X, если обнаруживает их. Удаленные столбцы X можно определить в выходных данных ЛИНЕЙН по коэффициенту, равному 0, и по значению se, равному 0. Удаление одного или более столбцов как избыточных изменяет величину df, поскольку она зависит от количества столбцов X, в действительности используемых для предикативных целей. Подробнее о вычислении величины df см. ниже в примере 4. При изменении df вследствие удаления избыточных столбцов значения sey и F также изменяются. Часто использовать коллинеарность не рекомендуется. Однако ее следует применять, если некоторые столбцы X содержат 0 или 1 в качестве индикатора указывающего, входит ли предмет эксперимента в отдельную группу. Если конст = ИСТИНА или значение этого аргумента не указано, функция ЛИНЕЙН вставляет дополнительный столбец X для моделирования точки пересечения. Если имеется столбец со значениями 1 для указания мужчин и 0 - для женщин, а также имеется столбец со значениями 1 для указания женщин и 0 - для мужчин, то последний столбец удаляется, поскольку его значения можно получить из столбца с «индикатором мужского пола».

Вычисление df для случаев, когда столбцы X не удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n - k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.

Формулы, которые возвращают массивы, должны быть введены как формулы массива.

При вводе массива констант в качестве, например, аргумента известные_значения_x следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть различными в зависимости от параметров, заданных в окне «Язык и стандарты» на панели управления.

Следует отметить, что значения y, предсказанные с помощью уравнения регрессии, возможно, не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.

Основной алгоритм, используемый в функции ЛИНЕЙН , отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК . Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:

Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.

Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для поиска только одного ответа, а в данном случае их может быть несколько.

Помимо вычисления статистики для других типов регрессии функцию ЛИНЕЙН можно использовать при вычислении диапазонов для других типов регрессии, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:

ЛИНЕЙН(значения_y, значения_x^СТОЛБЕЦ($A:$C))

работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

Формула может быть изменена для расчетов других типов регрессии, но в отдельных случаях требуется корректировка выходных значений и других статистических данных.

Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.

Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.

Регрессия бывает:

· линейной (у = а + bx);

· параболической (y = a + bx + cx 2);

· экспоненциальной (y = a * exp(bx));

· степенной (y = a*x^b);

· гиперболической (y = b/x + a);

· логарифмической (y = b * 1n(x) + a);

· показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

Модель линейной регрессии имеет следующий вид:

У = а 0 + а 1 х 1 +…+а к х к.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

В Excel существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».

Активируем мощный аналитический инструмент:

1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».

2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.

3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».

2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.

3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Пакет MS Excel позволяет при построении уравнения линейной регрессии большую часть работы сделать очень быстро. Важно понять, как интерпретировать полученные результаты. Для построения модели регрессии необходимо выбрать пункт Сервис\Анализ данных\Регрессия (в Excel 2007 этот режим находится в блоке Данные/Анализ данных/Регрессия). Затем полученные результаты скопировать в блок для анализа.

Исходные данные:

Результаты анализа

Включать в отчет
Расчет параметров уравнения регрессии
Теоретический материал
Уравнение регрессии в стандартном масштабе
Множественный коэффициент корреляции (Индекс множественной корреляции)
Частные коэффициенты эластичности
Сравнительная оценка влияния анализируемых факторов на результативный признак (d - коэффициенты раздельной детерминации)

Проверка качества построенного уравнения регрессии
Значимость коэффициентов регрессии b i (t-статистика. Критерий Стьюдента)
Значимость уравнения в целом (F-статистика. Критерий Фишера). Коэффициент детерминации
Частные F-критерии

Уровень значимости 0.005 0.01 0.025 0.05 0.1 0.25 0.4

Регрессионный анализ в Microsoft Excel – наиболее полное руководств по использованию MS Excel для решения задач регрессионного анализа в области бизнес-аналитики. Конрад Карлберг доступно объясняет теоретические вопросы, знание которых поможет вам избежать многих ошибок как при самостоятельном проведении регрессионного анализа, так и при оценке результатов анализа, выполненного другими людьми. Весь материал, от простых корреляций и t-тестов до множественного ковариационного анализа, основан на реальных примерах и сопровождается подробным описанием соответствующих пошаговых процедур.

В книге обсуждаются особенности и противоречия, связанные с функциями Excel для работы с регрессией, рассматриваются последствия использования каждой их опции и каждого аргумента и объясняется, как надежно применять регрессионные методы в самых разных областях, от медицинских исследований до финансового анализа.

Конрад Карлберг. Регрессионный анализ в Microsoft Excel. – М.: Диалектика, 2017. – 400 с.

Скачать заметку в формате или , примеры в формате

Глава 1. Оценка изменчивости данных

В распоряжении статистиков имеется множество показателей вариации (изменчивости). Один из них – сумма квадратов отклонений индивидуальных значений от среднего. В Excel для него используется функция КВАДРОТКЛ(). Но чаще используется дисперсия. Дисперсия - это среднее квадратов отклонений. Дисперсия нечувствительна к количеству значений в исследуемом наборе данных (в то время как сумма квадратов отклонений растет с числом измерений).

Программа Excel предлагает две функции, возвращающие дисперсию: ДИСП.Г() и ДИСП.В():

• Используйте функцию ДИСП.Г(), если подлежащие обработке значения образуют генеральную совокупность. Т.е., значения, содержащиеся в диапазоне, являются единственными значениями, которые вас интересуют.
• Используйте функцию ДИСП.В(), если подлежащие обработке значения образуют выборку из совокупности большего объема. Предполагается, что имеются дополнительные значения, дисперсию которых вы также можете оценить.

Если такая величина, как среднее значение или коэффициент корреляции, рассчитывается на основе генеральной совокупности, то она называется параметром. Аналогичная величина, рассчитываемая на основе выборки, называется статистикой. Отсчитывая отклонения от среднего значения в данном наборе, вы получите сумму квадратов отклонений меньшей величины, чем если бы отсчитывали их от любого другого значения. Аналогичное утверждение справедливо и для дисперсии.

Чем больше объем выборки, тем точнее рассчитанное значение статистики. Но не существует ни одной выборки с объемом меньше объема генеральной совокупности, относительно которой вы могли бы быть уверены в том, что значение статистики совпадает со значением параметра.

Допустим, у вас есть набор из 100 значений роста, среднее которых отличается от среднего по генеральной совокупности, каким бы малым ни было это различие. Рассчитав дисперсию для выборки, вы получите некоторое ее значение, скажем, 4. Это значение меньше любого другого, которое можно получить, рассчитывая отклонение каждого из 100 значений роста относительно любого значения, отличного от среднего по выборке, в там числе и относительно истинного среднего по генеральной совокупности. Поэтому вычисленная дисперсия будет отличаться, причем в меньшую сторону, от дисперсии, которую вы получили бы, если бы каким-то образом узнали и использовали не выборочное среднее, а параметр генеральной совокупности.

Средняя сумма квадратов, определенная для выборки, дает нижнюю оценку дисперсии генеральной совокупности. Вычисленную таким способом дисперсию называют смещенной оценкой. Оказывается, чтобы исключить смещение и получить несмещенную оценку, достаточно разделить сумму квадратов отклонений не на n , где n - размер выборки, а на n – 1 .

Величина n – 1 называется количеством (числом) степеней свободы. Существуют разные способы расчета этой величины, хотя все они включают либо вычитание некоторого числа из размера выборки, либо подсчет количества категорий, в которые попадают наблюдения.

Суть различия между функциями ДИСП.Г() и ДИСП.В() состоит в следующем:

• В функции ДИСП.Г() сумма квадратов делится на количество наблюдений и, следовательно, представляет смещенную оценку дисперсии, истинное среднее.
• В функции ДИСП.В() сумма квадратов делится на количество наблюдений минус 1, т.е. на количество степеней свободы, что дает более точную, несмещенную оценку дисперсии генеральной совокупности, из которой была извлечена данная выборка.

Стандартное отклонение (англ. standard deviation , SD) – есть квадратный корень из дисперсии:

Возведение отклонений в квадрат переводит шкалу измерений в другую метрику, являющуюся квадратом исходной: метры - в квадратные метры, доллары - в квадратные доллары и т.д. Стандартное отклонение - это корень квадратный из дисперсии, и поэтому оно возвращает нас к исходным единицам измерения. Что удобнее.

Часто приходится рассчитывать стандартное отклонение после того, как данных были подвергнуты некоторым манипуляциям. И хотя в этих случаях результаты несомненно являются стандартными отклонениями, их принято называть стандартными ошибками . Существует несколько разновидностей стандартных ошибок, в том числе стандартная ошибка измерения, стандартная ошибка пропорции, стандартная ошибка среднего.

Предположим, вы собрали данные о росте 25 случайно выбранных взрослых мужчин в каждом из 50 штатов. Далее вы вычисляете средний рост взрослых мужчин в каждом штате. Полученные 50 средних значений в свою очередь можно считать наблюдениями. Исходя из этого, вы могли бы рассчитать их стандартное отклонение, которое и является стандартной ошибкой среднего . Рис. 1. позволяет сравнить распределение 1250 исходных индивидуальных значений (данные о росте 25 мужчин по каждому из 50 штатов) с распределением средних значений 50 штатов. Формула для оценки стандартной ошибки среднего (т.е. стандартного отклонения средних значений, а не индивидуальных наблюдений):

где – стандартная ошибка среднего; s – стандартное отклонение исходных наблюдений; n – количество наблюдений в выборке.

Рис. 1. Вариация средних значений от штата к штату значительно меньше вариации индивидуальных результатов наблюдений

В статистике существует соглашение относительно использования греческих и латинских букв для обозначения статистических величин. Греческими буквами принято обозначать параметры генеральной совокупности, латинскими - выборочные статистики. Следовательно, если речь идет о стандартном отклонении генеральной совокупности, мы записываем его как σ; если же рассматривается стандартное отклонение выборки, то используем обозначение s. Что касается символов для обозначения средних, то они согласуются между собой не столь удачно. Среднее по генеральной совокупности обозначается греческой буквой μ. Однако для представления выборочного среднего традиционно используется символ X̅.

z-оценка выражает положение наблюдения в распределении в единицах стандартного отклонения. Например, z = 1,5 означает, что наблюдение отстоит от среднего на 1,5 стандартного отклонения в сторону больших значений. Термин z-оценка используют для индивидуальных оценок, т.е. для измерений, приписываемых отдельным элементам выборки. В отношении таких статистик (например, среднее значение по штату) используют термин z-значение :

где X̅ – среднее значение выборки, μ – среднее значение генеральной совокупности, – стандартная ошибка средних набора выборок:

где σ – стандартная ошибка генеральной совокупности (индивидуальных измерений), n – размер выборки.

Предположим, вы работаете инструктором в гольф-клубе. Вы имели возможность в течение длительного времени измерять дальность ударов и знаете, что ее среднее значение составляет 205 ярдов, а стандартное отклонение - 36 ярдов. Вам предложили новую клюшку, утверждая, что она увеличит дальность удара на 10 ярдов. Вы просите каждого из последующих 81 посетителей клуба выполнить пробный удар новой клюшкой и записываете его дальность удара. Оказалось, что средняя дальность удара новой клюшкой составляет 215 ярдов. Какова вероятность того, что разница в 10 ярдов (215 – 205) обусловлена исключительно ошибкой выборки? Или по-другому: какова вероятность того, что при более масштабном тестировании новая клюшка не продемонстрирует увеличение дальности удара по сравнению с имеющимся долговременным средним показателем 205 ярдов?

Мы можем проверить это, сформировав z-значение. Стандартная ошибка среднего:

Тогда z-значение:

Нам нужно найти вероятность того, что среднее по выборке будет отстоять от среднего по генеральной совокупности на 2,5σ. Если вероятность будет маленькой, значит отличия обусловлены не случайностью, а качеством новой клюшки. В Excel для определения вероятности z-значения нет готовой функции. Однако можно использовать формулу =1-НОРМ.СТ.РАСП(z-значение;ИСТИНА), где функция НОРМ.СТ.РАСП() возвращает площадь под нормальной кривой слева от z-значения (рис. 2).

Рис. 2. Функция НОРМ.СТ.РАСП() возвращает площадь под кривой слева от z-значения; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Второй аргумент функции НОРМ.СТ.РАСП() может принимать два значения: ИСТИНА – функция возвращает площадь области под кривой слева от точки, заданной первым аргументом; ЛОЖЬ – функция возвращает высоту кривой в точке, заданной первым аргументом.

Если среднее значение (μ) и стандартное отклонение (σ) генеральной совокупности не известны, используется t-значение (подробнее см. ). Структуры z- и t-значения отличаются тем, что для нахождения t-значения используется стандартное отклонение s, полученное на основе выборочных результатов, а не известное значение параметра генеральной совокупности σ. Нормальная кривая имеет единственную форму, а форма распределения t-значений варьирует в зависимости от количества степеней свободы df (от англ. degrees of freedom ) выборки, которую оно представляет. Количество степеней свободы выборки равно n – 1 , где n - размер выборки (рис. 3).

Рис. 3. Форма t-распределений, возникающих в тех случаях, когда параметр σ неизвестен, отличается от формы нормального распределения

В Excel есть две функции для t-распределения также называемого распределением Стьюдента: СТЬЮДЕНТ.РАСП() возвращает величину площади под кривой слева от заданного t-значения, а СТЬЮДЕНТ.РАСП.ПХ() – справа.

Глава 2. Корреляция

Корреляция - это мера зависимости между элементами набора упорядоченных пар. Корреляция характеризуется коэффициентам корреляции Пирсона – r. Коэффициент может принимать значения в интервале от –1,0 до +1,0.

где S x и S y – стандартные отклонения переменных Х и Y , S xy – ковариация:

В этой формуле ковариация делится на стандартные отклонения переменных Х и Y , тем самым удаляя из ковариации эффекты масштабирования, связанные с единицами измерения. В Excel используется функция КОРРЕЛ(). В названии этой функции отсутствуют уточняющие элементы Г и В, которые используются в названиях таких функций, как СТАНДОТКЛОН(), ДИСП() или КОВАРИАЦИЯ(). Хотя коэффициенте корреляции по выборке предоставляемая смещенную оценку, однако причина смещения иная, нежели в случае дисперсии или стандартного отклонения.

В зависимости от величины генерального коэффициента корреляции (часто обозначаемого греческой буквой ρ ), коэффициент корреляции r дает смещенную оценку, причем эффект смещения усиливается с уменьшением размера выборок. Тем не менее мы не пытаемся корректировать это смещение аналогично тому, как, например, делали это при вычислении стандартного отклонения, когда подставляли в соответствующую формулу не количество наблюдений, а количество степеней свободы. В действительности количество наблюдений, используемое для вычисления ковариации, не оказывает никакого влияния на величину.

Стандартный коэффициент корреляции предназначен для использования с переменными, связанными между собой линейным соотношением. Наличие нелинейности и/или ошибок в данных (выбросы) приводят к неверному расчету коэффициента корреляции. Для диагностики проблем с данными рекомендуется строить точечные диаграммы. Это единственный тип диаграмм в Excel, в котором и горизонтальная, и вертикальная оси трактуются как оси значений. Линейная же диаграмма один из столбцов определяет, как ось категорий, что искажает картину данных (рис. 4).

Рис. 4. Линии регрессии кажутся одинаковыми, однако сравните между собой их уравнения

Наблюдения, использованные для построения линейной диаграммы, располагаются вдоль горизонтальной оси эквидистантно. Надписи делений вдоль этой оси - это и есть всего лишь надписи, а не числовые значения.

Несмотря на то что корреляция часто означает наличие причинно-следственной связи, она не может служить доказательством того, что так оно и есть. Статистика не используется для демонстрации того, истинна или ложна теория. Для исключения конкурирующих объяснений результатов наблюдений ставят плановые эксперименты . Статистика же привлекается для обобщения информации, собранной в ходе таких экспериментов, и количественной оценки вероятности того, что принимаемое решение может быть неверным при имеющейся доказательной базе.

Глава 3. Простая регрессия

Если две переменные связаны между собой, так что значение коэффициента корреляции превышает, скажем, 0,5, то в этом случае можно прогнозировать (с некоторой точностью) неизвестное значение одной переменной по известному значению другой. Для получения прогнозных значений цены, исходя из данных, приведенных на рис. 5, можно использовать любой из нескольких возможных способов, но почти наверняка вы не будете использовать тот, который представлен на рис. 5. И все же вам стоит с ним ознакомиться, поскольку ни один другой способ не позволяет так же отчетливо продемонстрировать связь между корреляцией и прогнозированием, как этот. На рис. 5 в диапазоне В2:С12 представлена случайная выборка из десяти домов и приведены данные о площади каждого дома (в квадратных футах) и его продажной цене.

Рис. 5. Прогнозные значения продажной цены образуют прямую линию

Найдите средние значения, стандартные отклонения и коэффициент корреляции (диапазон А14:С18). Рассчитайте z-оценки площади (Е2:Е12). Например, ячейка ЕЗ содержит формулу: =(В3-$В$14)/$В$15. Вычислите z-оценки прогнозной цены (F2:F12). Например, ячейка F3 содержит формулу: =ЕЗ*$В$18. Переведите z-оценки в цены в долларах (Н2:Н12). В ячейке НЗ формула: =F3*$C$15+$C$14.

Обратите внимание: прогнозное значение всегда стремится сместиться в сторону среднего, равного 0. Чем ближе к нулю коэффициент корреляции, тем ближе к нулю прогнозная z-оценка. В нашем примере коэффициент корреляции между площадью и продажной ценой равен 0,67, и прогнозная цена равна 1,0*0,67, т.е. 0,67. Этому соответствует превышение значения над средним значением, равное двум третям стандартного отклонения. Если бы коэффициент корреляции был равен 0,5, то прогнозная цена составила бы 1,0*0,5, т.е. 0,5. Этому соответствует превышение значения над средним значением, равное лишь половине стандартного отклонения. Всякий раз, когда значение коэффициента корреляции отличается от идеального, т.е. больше -1,0 и меньше 1,0, оценка прогнозируемой переменной должна быть ближе к своему среднему значению, чем оценка предикторной (независимой) переменной к своему. Это явление называется регрессией к среднему, или просто регрессией.

В Excel есть несколько функций для определения коэффициентов уравнения линии регрессии (в Excel она называется линией тренда) у = kx + b . Для определения k служит функция

=НАКЛОН(известные_значения_у; известные_значения_х)

Здесь у – прогнозируемая переменная, а х – независимая переменная. Вы должны строго следовать этому порядку переменных. Наклон линии регрессии, коэффициент корреляции, стандартные отклонения переменных и ковариация тесно связаны между собой (рис. 6). Функция ОТРЕЗОК() возвращает значение, отсекаемое линией регрессии на вертикальной оси:

=ОТРЕЗОК(известные_значения_у; известные_значения_х)

Рис. 6. Соотношение между стандартными отклонениями преобразует ковариацию в коэффициент корреляции и наклон линии регрессии

Обратите внимание, что количество значений х и у, предоставляемых функциям НАКЛОН() и ОТРЕЗОК() в качестве аргументов, должно быть одинаковым.

В регрессионном анализе используется еще один важный показатель – R 2 (R-квадрат), или коэффициент детерминации. Он определяет, какой вклад в общую изменчивость данных вносит выявленная с помощью регрессии зависимость между х и у . В Excel для него есть функция КВПИРСОН(), которая принимает точно те же аргументы, что и функция КОРРЕЛ().

О двух переменных с ненулевым коэффициентом корреляции между ними говорят, что они объясняют дисперсию или имеют объясненную дисперсию. Обычно объясненная дисперсия выражается в процентах. Так R 2 = 0,81 означает, что 81% дисперсии (разброса) двух переменных является объясненной. Остальные 19% обусловлены случайными флуктуациями.

В Excel имеется функция ТЕНДЕНЦИЯ, которая упрощает вычисления. Функция ТЕНДЕНЦИЯ():

• принимает предоставляемые вами известные значения х и известные значения у ;
• вычисляет наклон линии регрессии и константу (отрезок);
• возвращает прогнозные значения у , определяемые на основании применения уравнения регрессии к известным значениям х (рис. 7).

Функция ТЕНДЕНЦИЯ() является функцией массива (если вы ранее не сталкивались с такими функциями, рекомендую ).

Рис. 7. Использование функции ТЕНДЕНЦИЯ() позволяет ускорить и упростить вычисления по сравнению с использованием пары функций НАКЛОН() и ОТРЕЗОК()

Чтобы ввести функцию ТЕНДЕНЦИЯ() в виде формулы массива в ячейки G3:G12, выделите диапазон G3:G12, введите формулу ТЕНДЕНЦИЯ (СЗ:С12;ВЗ:В12), нажмите и удерживайте клавиши и только после этого нажмите клавишу . Обратите внимание, что формула заключена в фигурные скобки: { и }. Так Excel сообщает вам о том, что данная формула воспринята именно как формула массива. Не вводите сами скобки: если вы попытаетесь ввести их самостоятельно в составе формулы, Excel воспримет ваш ввод как обычную текстовую строку.

У функции ТЕНДЕНЦИЯ() есть еще два аргумента: новые_значения_х и конст . Первый позволяет построить прогноз на будущее, а второй может заставить линию регрессии пройти через начало координат (значение ИСТИНА говорит Excel использовать расчетную константу, значение ЛОЖЬ – константу = 0). Excel позволяет нарисовать регрессионную прямую на графике так, чтобы она проходила через начало координат. Начните с построения точечной диаграммы, после чего щелкните правой кнопкой мыши на одном из маркеров ряда данных. Выберите в открывшемся контекстном меню пункт Добавить линию тренда ; выберите вариант Линейная ; при необходимости прокрутите панель вниз, установите флажок Настроить пересечение ; убедитесь, что в связанном с ним текстовом поле задано значение 0,0.

Если у вас есть три переменных, и вы хотите определить корреляцию между двумя из них, исключив влияние третьей, можно использовать частную корреляцию . Предположим, вас интересует связь между процентной долей жителей города, закончивших колледж, и количеством книг в городских библиотеках. Вы собрали данные по 50 городам, но… Проблема в том, что оба этих параметра могут зависеть от благосостояния жителей того или иного города. Разумеется, очень трудно подобрать другие 50 городов, характеризующихся в точности одинаковым уровнем благосостояния жителей.

Применяя статистические методы для исключения влияния, оказываемого фактором благосостояния как на финансовую поддержку библиотек, так и на доступность обучения в колледже, вы могли бы получить более точную количественную оценку степени зависимости между интересующими вас переменными, а именно: количеством книг и количеством выпускников. Такая условная корреляция между двумя переменными, когда значения других переменных фиксированы, и называется частной корреляцией. Один из способов ее расчета заключается в использовании уравнения:

Где r CB . W - коэффициент корреляции между переменными Колледж (College) и Книги (Books) при исключенном влиянии (фиксированном значении) переменной Благосостояние (Wealth); r CB - коэффициент корреляции между переменными Колледж и Книги; r CW - коэффициент корреляции между переменными Колледж и Благосостояние; r BW - коэффициент корреляции между переменными Книги и Благосостояние.

С другой стороны, частную корреляцию можно рассчитать на основе анализа остатков, т.е. разностей между прогнозными значениями и связанными с ними результатами фактических наблюдений (оба метода представлены на рис. 8).

Рис. 8. Частная корреляция, как корреляция остатков

Для упрощения подсчета матрицы коэффициентов корреляции (В16:Е19) используйте пакет анализа Excel (меню Данные –> Анализ –> Анализ данных ). По умолчанию этот пакет в Excel не активен. Для его установки пройдите по меню Файл –> Параметры –> Надстройки . Внизу открывшегося окна Параметры Excel найдите поле Управление , выберите Надстройки Excel , кликните Перейти . Поставьте галочку напротив надстройки Пакет анализа . Кликните Анализ данных , выберите опцию Корреляция . В качестве входного интервала укажите $B$2:$D$13, поставьте галочку Метки в первой строке , в качестве выходного интервала укажите $B$16:$E$19.

Еще одна возможность – определить получастную корреляцию. Например, вы исследуете влияние роста и возраста на вес. Таким образом, у вас две предикторные переменные – рост и возраст, и одна прогнозируемая переменная – вес. Вы хотите исключить влияние одной предикторной переменной на другую, но не на прогнозную переменную:

где Н – Рост (Height), W– Вес (Weight), А – Возраст (Age); в индексе получастного коэффициента корреляции используются круглые скобки, с помощью которых указывается, влияние какой переменной устраняется и из какой именно переменной. В данном случае обозначение W(Н.А) указывает на то, что влияние переменной Возраст удаляется из переменной Рост, но не из переменной Вес.

Может создаться впечатление, что обсуждаемый вопрос не имеет существенного значения. Ведь важнее всего то, насколько точно работает общее уравнение регрессии, тогда как проблема относительных вкладов отдельных переменных в суммарную объясненную дисперсию представляется второстепенной. Однако это далеко не так. Как только вы начинаете задумываться над тем, стоит ли вообще использовать какую-то переменную в уравнении множественной регрессии, проблема становится важной. Она может влиять на оценку правильности выбора модели для анализа.

Глава 4. Функция ЛИНЕЙН()

Функция ЛИНЕЙН() возвращает 10 статистик регрессионного анализа. Функция ЛИНЕЙН() является функцией массива. Для ее ввода выделите диапазон, содержащий пять строк и два столбца, напечатайте формулу, и нажмите (рис. 9):

ЛИНЕЙН(B2:B21;A2:A21;ИСТИНА;ИСТИНА)

Рис. 9. Функция ЛИНЕЙН(): а) выделите диапазон D2:E6, б) введите формулу, как показано в строке формул, в) нажмите

Функция ЛИНЕЙН() возвращает:

• коэффициент регрессии (или наклон, ячейка D2);
• отрезок (или константа, ячейка Е3);
• стандартные ошибки коэффициента регрессии и константы (диапазон D3:E3);
• коэффициент детерминации R 2 для регрессии (ячейка D4);
• стандартная ошибка оценки (ячейка Е4);
• F-критерий для полной регрессии (ячейка D5);
• количество степеней свободы для остаточной суммы квадратов (ячейка Е5);
• регрессионная сумма квадратов (ячейка D6);
• остаточная сумма квадратов (ячейка Е6).

Рассмотрим каждую из этих статистик и их взаимодействие.

Стандартная ошибка в нашем случае – это стандартное отклонение, вычисляемое для ошибок выборки. Т.е., это ситуация, когда генеральная совокупность имеет одну статистику, а выборка – другую. Разделив коэффициент регрессии на стандартную ошибку, вы получите значение 2,092/0,818 = 2,559. Иными словами, коэффициент регрессии, равный 2,092, отстоит от нуля на две с половиной стандартные ошибки.

Если коэффициент регрессии равен нулю, то наилучшей оценкой прогнозируемой переменной является ее среднее значение. Две с половиной стандартные ошибки - это довольно большая величина, и вы с уверенностью можете полагать, что коэффициент регрессии для генеральной совокупности имеет ненулевое значение.

Можно определить вероятность получения выборочного коэффициента регрессии 2,092, если его фактическое значение в генеральной совокупности равно 0,0 с помощью функции

СТЬЮДЕНТ.РАСП.ПХ(t-критерий = 2,559; количество степеней свободы =18)

В общем количество степеней свободы = n – k – 1, где n - количество наблюдений, а k - количество предикторных переменных.

Эта формула возвращает значение 0,00987 или, округленно, 1%. Оно сообщает нам следующее: если коэффициент регрессии для генеральной совокупности равен 0%, то вероятность получения выборки из 20 человек, для которой расчетное значение коэффициента регрессии равно 2,092, составляет скромный 1%.

F-критерий (ячейка D5 на рис. 9) выполняет те же функции по отношению к полной регрессии, что и t-критерий по отношению к коэффициенту простой парной регрессии. F-критерий используется для проверки того, действительно ли коэффициент детерминации R 2 для регрессии имеет достаточно большую величину, позволяющую отбросить гипотезу о том, что в генеральной совокупности он имеет значение 0,0, которое указывает на отсутствие дисперсии, объясняемой предикторной и прогнозируемой переменной. При наличии только одной предикторной переменной F-критерий в точности равен квадрату t-критерия.

До сих пор мы рассматривали интервальные переменные. Если же у вас переменные, которые могут принимать несколько значений, представляющих собой простые имена, например, Мужчина и Женщина или Пресмыкающееся, Земноводное и Рыба, представьте их в виде числового кода. Такие переменные называются номинальными.

Статистика R 2 дает количественную оценку доли объясненной дисперсии.

Стандартная ошибка оценки. На рис. 4.9 представлены прогнозные значения переменной Вес, полученные на основании ее связи с переменной Рост. В диапазоне Е2:Е21 содержатся значения остатков для переменной Вес. Точнее эти остатки называть ошибками - отсюда и следует термин стандартная ошибка оценки.

Рис. 10. Как R 2 , так и стандартная ошибка оценки выражают точность прогнозов, получаемых с помощью регрессии

Чем меньше стандартная ошибка оценки, тем точнее уравнение регрессии и тем более близкого совпадения любого прогноза, полученного с помощью уравнения, с фактическим наблюдением вы ожидаете. Стандартная ошибка оценки предоставляет способ количественной оценки этих ожиданий. Вес 95% людей, обладающих неким ростом, будет находиться в диапазоне:

(рост * 2,092 – 3,591) ± 2,092*21,118

F-статистика – это отношение межгрупповой дисперсии к внутригрупповой дисперсии. Это название было введено статистиком Джорджем Снедекором в честь сэра , разработавшего в начале XX столетия дисперсионный анализ (ANOVA, Analysis of Variance).

Коэффициент детерминации R 2 выражает долю общей суммы квадратов, связанную с регрессией. Величина (1 – R 2) выражает долю общей суммы квадратов, связанную с остатками - ошибками прогнозирования. F-критерий можно получить с использованием функции ЛИНЕЙН (ячейка F5 на рис. 11), с использованием сумм квадратов (диапазон G10:J11), с использованием долей дисперсии (диапазон G14:J15). Формулы можно изучить в прилагаемом файле Excel.

Рис. 11. Расчет F-критерия

При использовании номинальных переменных используется фиктивное кодирование (рис. 12). Для кодирования значений удобно использовать значения 0 и 1. Вероятность F рассчитывается с помощью функции:

F.РАСП.ПХ(К2;I2;I3)

Здесь функция F.РАСП.ПХ() возвращает вероятность получения F-критерия, подчиняющегося центральному F-распределению (рис. 13) для двух наборов данных с количествами степеней свободы, приведенными в ячейках I2 и I3, значение которого совпадает со значением, приведенным в ячейке К2.

Рис. 12. Регрессионный анализ с использованием фиктивных переменных

Рис. 13. Центральное F-распределение при λ = 0

Глава 5. Множественная регрессия

Переходя от простой парной регрессии с одной предикторной переменной к множественной регрессии, вы добавляете одну или несколько предикторных переменных. Сохраняйте значения предикторных переменных в смежных столбцах, например, в столбцах А и В в случае двух предикторов или А, В и С в случае трех предикторов. Прежде чем вводить формулу, включающую функцию ЛИНЕЙН(), выберите пять строк и столько столбцов, сколько имеется предикторных переменных, плюс еще один для константы. В случае регрессии с двумя предикторными переменными можно использовать следующую структуру:

ЛИНЕЙН(А2: А41; В2: С41;;ИСТИНА)

Точно так же в случае трех переменных:

ЛИНЕЙН(А2:А61;В2:D61;;ИСТИНА)

Предположим, вы хотите изучить возможное влияние возраста и диеты на содержание ЛПНП - липопротеинов низкой плотности, которые считаются ответственными за образование атеросклеротических бляшек, служащих причиной атеротромбоза (рис. 14).

Рис. 14. Множественная регрессия

R 2 множественной регрессии (отражаемый в ячейке F13), больше, чем R 2 любой простой регрессии (Е4, Н4). В множественной регрессии одновременно используются несколько предикторных переменных. При этом R 2 почти всегда увеличивается.

Для любого простого линейного уравнения регрессии с одной предикторной переменной между прогнозными значениями и значениями предикторной переменной всегда будет наблюдаться идеальная корреляция, поскольку в таком уравнении значения предиктора умножаются на одну константу и к каждому произведению прибавляется другая константа. Этот эффект не сохраняется во множественной регрессии.

Отображение результатов, возвращаемых функцией ЛИНЕЙН() для множественной регрессии (рис. 15). Коэффициенты регрессии выводятся в составе результатов, возвращаемых функцией ЛИНЕЙН() в порядке обратном расположению переменных (G–H–I соответствует С–В–А).

Рис. 15. Коэффициенты и их стандартные ошибки отображаются в обратном порядке их следования на рабочем листе

Принципы и процедуры, используемые в регрессионном анализе с одной предикторной переменной, легко адаптируются для учета нескольких предикторных переменных. Оказывается, что многое в этой адаптации зависит от устранения влияния предикторных переменных друг на друга. Последнее связано с частной и получастной корреляциями (рис. 16).

Рис. 16. Множественная регрессия может быть выражена через парную регрессию остатков (формулы см. в Excel-файле)

В Excel, имеются функции, предоставляющие информацию о t- и F-распределениях. Функции, имена которых включают часть РАСП, такие как СТЬЮДЕНТ.РАСП() и F.РАСП(), принимают t- или F-критерий в качестве аргумента и возвращают вероятность наблюдения указанного значения. Функции, имена которых включают часть ОБР, такие как СТЬЮДЕНТ.ОБР() и F.ОБР(), принимают значение вероятности в качестве аргумента и возвращают значение критерия, соответствующее указанной вероятности.

Поскольку мы ищем критические значения t-распределения, которые отсекают края его хвостовых областей, мы передаем 5% в качестве аргумента одной из функций СТЬЮДЕНТ.ОБР(), которая возвращает значение, соответствующее этой вероятности (рис. 17, 18).

Рис. 17. Двусторонний t-тест

Рис. 18. Односторонний t-тест

Устанавливая правило принятия решений в случае однохвостовой альфа-области, вы увеличиваете статистическую мощность теста. Если, приступая к эксперименту, вы уверены в том, что у вас есть все основания ожидать получения положительного (или отрицательного) коэффициента регрессии, то вам следует выполнить однохвостовой тест. В этом случае вероятность того, что вы принимаете правильное решение, отвергая гипотезу о нулевом коэффициенте регрессии в генеральной совокупности, будет выше.

Статистики предпочитают использовать термин направленный тест вместо термина однохвостовой тест и термин ненаправленный тест вместо термина двуххвостовой тест . Термины направленный и ненаправленный предпочтительнее, поскольку делают акцент на типе гипотезы, а не на природе хвостов распределения.

Подход к оценке влияния предикторов, основанный на сравнении моделей. На рис. 19 представлены результаты регрессионного анализа, в котором тестируется вклад переменной Диета в уравнение регрессии.

Рис. 19. Сравнение двух моделей путем проверки различий в их результатах

Результаты функции ЛИНЕЙН() (диапазон Н2:К6) имеют отношение к тому, что я называю полной моделью, в которой выполняется регрессия переменной ЛПНП по переменным Диета, Возраст и ЛПВП. В диапазоне Н9:J1З представлены расчеты без учета предикторной переменной Диета. Я называю это ограниченной моделью. В полной модели 49,2% дисперсии зависимой переменной ЛПНП объясняется предикторными переменными. В ограниченной модели лишь 30,8% ЛПНП объясняется переменными Возраст и ЛПВП. Потеря R 2 , обусловленная исключением переменной Диета из модели, составляет 0,183. В диапазоне G15:L17 сделаны расчеты, которые показывают, что лишь с вероятностью 0,0288 влияние переменной Диета является случайным. В остальных 97,1% Диета оказывает влияние на ЛПНП.

Глава 6. Допущения и предостережения в отношении регрессионного анализа

Термин «допущение» не определен достаточно строго, а способ его использования предполагает, что если допущение не соблюдается, то результаты всего анализа являются по меньшей мере сомнительными или, возможно, не имеющими силы. На самом деле это не так, хотя, безусловно, существуют случаи, когда нарушение допущения в корне меняет картину. Основные допущения: а) остатки переменной Y нормально распределены в любой точке X вдоль линии регрессии; б) значения Y находятся в линейной зависимости от значений X; в) дисперсия остатков примерно одинакова в каждой точке Х; г) между остатками отсутствует зависимость.

Если допущения не играют существенной роли, статистики говорят о робастности анализа по отношению к нарушению допущения. В частности, когда вы используете регрессию для тестирования различий между групповыми средними, допущение о том, что значения Y - а значит, и остатки - нормально распределены, не играет существенной роли: тесты робастны по отношению к нарушению допущения о нормальности. При этом важно анализировать данные с помощью диаграмм. Например, включенных в надстройку Анализ данных инструмент Регрессия .

Если данные не соответствуют допущениям линейной регрессии, в вашем распоряжении имеются другие подходы, отличные от линейного. Один из них – логистическая регрессия (рис. 20). Вблизи верхнего и нижнего предельных значений предикторной переменной линейная регрессия приводит к нереалистичным прогнозам.

Рис. 20. Логистическая регрессия

На рис. 6.8 отображены результаты двух методов анализа данных, направленного на исследование связи между ежегодным доходом и вероятностью покупки дома. Очевидно, вероятность совершения покупки будет увеличиваться с увеличением дохода. Диаграммы упрощают выявление различий между результатами, прогнозирующими вероятность покупки дома посредством линейной регрессии, и результатами, которые вы могли бы получить, используя другой подход.

На языке статистиков отбрасывание нулевой гипотезы, когда в действительности она является истинной, называется ошибкой I рода.

В надстройке Анализ данных предлагается удобный инструмент для генерации случайных чисел, предоставляющий пользователю возможность задать желаемую форму распределения (например, Нормальное, Биномиальное или Пуассона), а также среднее значение и стандартное отклонение.

Различия между функциями семейства СТЬЮДЕНТ.РАСП(). Начиная с версии Excel 2010 доступны три разные формы функции, возвращающей долю распределения слева и/или справа от заданного значения t-критерия. Функция СТЬЮДЕНТ.РАСП() возвращает долю площади под кривой распределения слева от указанного вами значения t-критерия. Предположим, у вас имеется 36 наблюдений, и поэтому количество степеней свободы для анализа равно 34, а значение t-критерия = 1,69. В этом случае формула

СТЬЮДЕНТ.РАСП(+1,69;34;ИСТИНА)

возвращает значение 0,05, или 5% (рис. 21). Третий аргумент функции СТЬЮДЕНТ.РАСП() может иметь значение ИСТИНА или ЛОЖЬ. Если он задан равным ИСТИНА, функция возвращает кумулятивную площадь под кривой слева от заданного t-критерия, выраженную в виде доли. Если же он равен ЛОЖЬ, функция возвращает относительную высоту кривой в точке, соответствующей t-критерию. Другие версии функции СТЬЮДЕНТ.РАСП() - СТЬЮДЕНТ.РАСП.ПХ() и СТЬЮДЕНТ.РАСП.2Х() - принимают в качестве аргументов только значение t-критерия и количество степеней свободы и не требуют задания третьего аргумента.

Рис. 21. Более темная затененная область в левом хвосте распределения соответствует доле площади под кривой слева от большого положительного значения t-критерия

Чтобы определить площадь справа от t-критерия используйте одну из формул:

1 — СТЫОДЕНТ.РАСП (1, 69;34;ИСТИНА)

СТЬЮДЕНТ.РАСП.ПХ(1,69;34)

Вся площадь под кривой должна составлять 100%, поэтому вычитание из 1 доли площади слева от значения t-критерия, которую возвращает функция, дает долю площади, располагающейся справа от значения t-критерия. Возможно, вам покажется более предпочтительным вариант непосредственного получения интересующей вас доли площади с помощью функции СТЬЮДЕНТ.РАСП.ПХ(), где ПХ означает правый хвост распределения (рис. 22).

Рис. 22. 5%-ная альфа область для направленного теста

Использование функций СТЬЮДЕНТ.РАСП() или СТЬЮДЕНТ.РАСП.ПХ () подразумевает, что вы выбрали направленную рабочую гипотезу. Направленная рабочая гипотеза в сочетании с установкой значения альфа на уровне 5% означает, что вы помещаете все 5% в правый хвост распределениями. Вы должны будете отвергнуть нулевую гипотезу лишь в том случае, если вероятность полученного вами значения t-критерия составит 5% и менее. Направленные гипотезы обычно приводят к более чувствительным статистическим тестам (эту большую чувствительность также называют большей статистической мощностью).

При ненаправленном тесте значение альфа остается на том же уровне 5%, но распределение будет иным. Поскольку вы должны допускать два исхода вероятность ложноположительного результата должна быть распределена между двумя хвостами распределения. Общепринято распределять эту вероятность поровну (рис. 23).

Используя то же самое полученное значение t-критерия и то же количество степеней свободы, что и в предыдущем примере, воспользуйтесь формулой

СТЬЮДЕНТ.РАСП.2Х(1,69;34)

Без каких-либо особых на то причин функция СТЬЮДЕНТ.РАСП.2Х() возвращает код ошибки #ЧИСЛО!, если в качестве первого аргумента ей предоставляется отрицательное значение t-критерия.

Если выборки содержат разное число данных, воспользуйтесь двухвыборочным t-тестом с различными дисперсиями, включенным в пакет Анализ данных .

Глава 7. Использование регрессии для тестирования различий между групповыми средними

Переменные, которые ранее фигурировали под названием прогнозируемых переменных, в этой главе будут называться результативными переменными, а вместо термина предикторные переменные будет использоваться термин факторные переменные.

Простейшим из подходов к кодированию номинальной переменной является фиктивное кодирование (рис. 24).

Рис. 24. Регрессионный анализ на основе фиктивного кодирования

При использовании фиктивного кодирования любого рода следует придерживаться правил:

• Количество столбцов, резервируемых для новых данных, должно быть равным количеству уровней фактора минус
• Каждый вектор представляет один уровень фактора.
• Субъекты одного из уровней, которым часто является контрольная группа, получают код 0 во всех векторах.

Формула в ячейках F2:H6 =ЛИНЕЙН(A2:A22;C2:D22;;ИСТИНА) возвращает регрессионные статистики. Для сравнения на рис. 24 отображены результаты традиционного дисперсионного анализа, возвращаемого инструментом Однофакторный дисперсионный анализ надстройки Анализ данных .

Кодирование эффектов. В другом типе кодирования, получившем название кодирование эффектов, среднее каждой группы сравнивается со средним групповых средних. Этот аспект кодирования эффектов обусловлен использованием значения -1 вместо 0 в качестве кода для группы, которая получает один и тот же код во всех кодовых векторах (рис. 25).

Рис. 25. Кодирование эффектов

Когда используется фиктивное кодирование, значение константы, возвращаемое функцией ЛИНЕЙН(), совпадает со средним группы, которой во всех векторах назначены нулевые коды (обычно это контрольная группа). В случае кодирования эффектов константа равна общему среднему (ячейка J2).

Общая линейная модель - полезный способ концептуализации компонентов значения результирующей переменной:

Y ij = μ + α j + ε ij

Использование в этой формуле греческих букв вместо латинских подчеркивает тот факт, что она относится к генеральной совокупности, из которой извлекаются выборки, но ее можно переписать в виде, указывающем на то, что она относится к выборкам, извлекаемым изданной генеральной совокупности:

Y ij = Y̅ + a j + e ij

Идея состоит в том, что каждое наблюдение Y ij можно рассматривать как сумму следующих трех компонентов: общее среднее, μ; эффект обработки j, а j ; величина e ij , которая представляет отклонение индивидуального количественного показателя Y ij от комбинированного значения общего среднего и эффекта j-й обработки (рис. 26). Целью уравнения регрессии является минимизация суммы квадратов остатков.

Рис. 26. Наблюдения, разложенные на компоненты общей линейной модели

Факторный анализ. Если исследуется связь между результативной переменной и одновременно двумя или более факторами, то в этом случае говорят об использовании факторного анализа. Добавление одного или нескольких факторов в однофакторный дисперсионный анализ может увеличивать статистическую мощность. В однофакторном дисперсионном анализе вариация результативной переменной, которая не может быть приписана фактору, включается в остаточный средний квадрат. Но вполне может быть так, что эта вариация с вязана с другим фактором. Тогда эта вариация может быть удалена из среднеквадратической ошибки, уменьшение которой приводит к увеличению значений F-критерия, а значит, к увеличению статистической мощности теста. Надстройка Анализ данных включает инструмент, обеспечивающий обработку двух факторов одновременно (рис. 27).

Рис. 27. Инструмент Двухфакторный дисперсионный анализ с повторениями Пакета анализа

Использованный на этом рисунке инструмент дисперсионного анализа, полезен тем, что он возвращает среднее и дисперсию результативной переменной, а также значение счетчика для каждой группы, включенной в план. В таблице Дисперсионный анализ отображаются два параметра, отсутствующие в выходной информации однофакторной версии инструмента дисперсионного анализа. Обратите внимание на источники вариации Выборка и Столбцы в строках 27 и 28. Источник вариации Столбцы относится к полу. Источник вариации Выборка относится к любой переменной, значения которой занимают различные строки. На рис. 27 значения для группы КурсЛеч1 находятся в строках 2-6, группы КурсЛеч2 - в строках 7-11, а группы КурсЛечЗ - в строках 12-16.

Главный момент заключается в том, что оба фактора, Пол (подпись Столбцы в ячейке Е28) и Лечение (подпись Выборка в ячейке Е27), включены в таблицу Дисперсионный анализ как источники вариации. Средние для мужчин отличаются от средних для женщин, и это создает источник вариации. Средние для трех видов лечения также различаются - вот вам еще один источник вариации. Существует также третий источник - Взаимодействие, который относится к объединенному эффекту переменных Пол и Лечение.

Глава 8. Ковариационный анализ

Ковариационный анализ, или ANCOVA (Analysis of Covariation) уменьшает смещения и увеличивает статистическую мощность. Напомню, что одним из способов оценки надежности регрессионного уравнения являются F-тесты:

F = MS Regression /MS Residual

где MS (Mean Square) - средний квадрат, а индексы Regression и Residual указывают на регрессионную и остаточную компоненты соответственно. Расчет MS Residual выполняется по формуле:

MS Residual = SS Residual / df Residual

где SS (Sum of Squares) - сумма квадратов, a df – количество степеней свободы. Когда вы добавляете ковариацию в уравнение регрессии, некоторая доля общей суммы квадратов включается не в SS ResiduaI , а в SS Regression . Это приводит к уменьшению SS Residua l , а значит, и MS Residual . Чем меньше MS Residual , тем больше F-критерий и тем вероятнее, что вы отвергнете нулевую гипотезу об отсутствии различий между средними. В результате вы перераспределяете изменчивость результативной переменной. В ANOVA, когда ковариация не учитывается, изменчивость переходит в ошибку. Но в ANCOVA часть изменчивости, ранее относившаяся к ошибке, назначается ковариате и становится частью SS Regression .

Рассмотрим пример, в котором один и тот же набор данных анализируется сначала с помощью ANOVA, а затем с помощью ANCOVA (рис. 28).

Рис. 28. Анализ ANOVA указывает на то, что результаты, полученные с помощью уравнения регрессии, ненадежны

В исследовании сравниваются относительные эффекты физических упражнений, развивающих мышечную силу, и когнитивных упражнений (разгадывание кроссвордов), активизирующих мозговую деятельность. Субъекты были случайным образом распределены по двум группам, чтобы в начале эксперимента обе группы находились в одинаковых условиях. По прошествии трех месяцев были измерены когнитивные характеристики субъектов. Результаты этих измерений приведены в столбце В.

В диапазоне А2:С21 размещены исходные данные, передаваемые функции ЛИНЕЙН() для выполнения анализа с использованием кодирования эффектов. Результаты работы функции ЛИНЕЙН() приведены в диапазоне E2:F6, где в ячейке Е2 отображается коэффициент регрессии, связанный с вектором воздействия. В ячейке Е8 содержится t-критерий = 0,93, а в ячейке Е9 тестируется надежность этого t-критерия. Содержащееся в ячейке Е9 значение говорит о том, что вероятность встретить различие между групповыми средними, наблюдаемое в данном эксперименте, составляет 36%, если в генеральной совокупности групповые средние равны. Лишь немногие признают этот результат статистически значимым.

На рис. 29 показано, что произойдет при добавлении ковариаты в анализ. В данном случае я добавил в набор данных возраст каждого субъекта. Коэффициент детерминации R 2 для уравнения регрессии, в котором используется ковариата, равен 0,80 (ячейка F4). Значение R 2 в диапазоне F15:G19, в котором я воспроизвел результаты ANOVA, полученные без использования ковариаты, равно всего лишь 0,05 (ячейка F17). Следовательно, уравнение регрессии, включающее ковариату, предсказывает значения переменной Когнитивный показатель намного точнее, чем с использованием только вектора Воздействие. Для ANCOVA вероятность случайного получения значения F-критерия, отображаемого в ячейке F5, равна менее чем 0,01%.

Рис. 29. ANCOVA возвращает совершенно иную картину