Анализ данных в Microsoft Excel

Таблица 8.1. Набор  данных А

x

y

3

9

2

7

4

12

5

15

6

17

7

19

8

21

9

23,4

10

25,6

11

27,8

Таблица 8.2. Описательная статистика для набора данных А

x

y

Среднее

6,5

17,68

Стандартная ошибка

0,957427108

2,210922382

Медиана

6,5

18

Стандартное отклонение

3,027650354

6,991550456

Дисперсия выборки

9,166666667

48,88177778

Эксцесс

-1,2

-1,106006058

Асимметричность

0

-0,128299221

Интервал

9

20,8

Минимум

2

7

Максимум

11

27,8

Сумма

65

176,8

Счет

10

10

Наибольший (1)

11

27,8

Наименьший (1)

2

7

Уровень надежности (95,0%)

2,16585224

5,001457714

Центральная тенденция

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

Главная цель среднего - представление набора данных для последующего анализа, сопоставления и сравнения.

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

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

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

Свойства среднего

• При расчете среднего не допускаются пропущенные значения данных.
• Среднее может вычисляться только для числовых данных и для дихотомических шкал.
• Для одного набора данных может быть рассчитано одно и только одно значение среднего.

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

Ширина доверительного интервала  зависит от размера выборки и  от разброса данных.

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

Медиана - точная середина выборки, которая делит ее на две равные части по числу наблюдений.

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

Таким образом, для нечетного  количества наблюдений медианой выступает  наблюдение с номером (n+1)/2, где n - количество наблюдений в выборке.

Для четного числа наблюдений медианой является среднее значение наблюдений n/2 и (n+2)/2.

Некоторые свойства медианы

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

Характеристики вариации данных

Наиболее простыми характеристиками выборки являются максимум и минимум.

Минимум - наименьшее значение выборки.

Максимум - наибольшее значение выборки.

Размах - разница между наибольшим и наименьшим значениями выборки.

Дисперсия - среднее арифметическое квадратов отклонений значений от их среднего.

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

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

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

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

Выбросы (outliers) - данные, резко отличающиеся от основного числа данных.

При обнаружении выбросов перед исследователем стоит дилемма: оставить наблюдения-выбросы либо от них отказаться. Второй вариант требует серьезной аргументации и описания. Полезным будет провести анализ данных с выбросами и без и сравнить результаты.

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

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

Регрессионный анализ

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

Последовательность этапов регрессионного анализа

Рассмотрим кратко этапы  регрессионного анализа.

1. Формулировка задачи. На этом этапе формируются предварительные гипотезы о зависимости исследуемых явлений.
2. Определение зависимых и независимых (объясняющих) переменных.
3. Сбор статистических данных. Данные должны быть собраны для каждой из переменных, включенных в регрессионную модель.
4. Формулировка гипотезы о форме связи (простая или множественная, линейная или нелинейная).
5. Определение функции регрессии (заключается в расчете численных значений параметров уравнения регрессии)
6. Оценка точности регрессионного анализа.
7. Интерпретация полученных результатов. Полученные результаты регрессионного анализа сравниваются с предварительными гипотезами. Оценивается корректность и правдоподобие полученных результатов.
8. Предсказание неизвестных значений зависимой переменной.

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

Задачи регрессионного анализа

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

Установление  формы зависимости.

Характер и форма зависимости  между переменными могут образовывать следующие разновидности регрессии:

• положительная линейная регрессия (выражается в равномерном росте функции);
• положительная равноускоренно возрастающая регрессия;
• положительная равнозамедленно возрастающая регрессия;
• отрицательная линейная регрессия (выражается в равномерном падении функции);
• отрицательная равноускоренно убывающая регрессия;
• отрицательная равнозамедленно убывающая регрессия.

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

Определение функции  регрессии.

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

Оценка неизвестных  значений зависимой переменной.

Решение этой задачи сводится к решению задачи одного из типов:

• Оценка значений зависимой переменной внутри рассматриваемого интервала исходных данных, т.е. пропущенных значений; при этом решается задача интерполяции.
• Оценка будущих значений зависимой переменной, т.е. нахождение значений вне заданного интервала исходных данных; при этом решается задача экстраполяции.

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

Рассмотрим некоторые  предположения, на которые опирается  регрессионный анализ.

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

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

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

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

Уравнение регрессии.

Уравнение регрессии выглядит следующим образом: Y=a+b*X

При помощи этого уравнения  переменная Y выражается через константу  a и угол наклона прямой (или угловой коэффициент) b, умноженный на значение переменной X. Константу a также называют свободным членом, а угловой коэффициент - коэффициентом регрессии или B-коэффициентом.

В большинстве случав (если не всегда) наблюдается определенный разброс наблюдений относительно регрессионной прямой.

Остаток - это отклонение отдельной точки (наблюдения) от линии регрессии (предсказанного значения).

Для решения задачи регрессионного анализа в MS Excel выбираем в меню Сервис "Пакет анализа" и инструмент анализа "Регрессия". Задаем входные интервалы X и Y. Входной интервал Y - это диапазон зависимых анализируемых данных, он должен включать один столбец. Входной интервал X - это диапазон независимых данных, которые необходимо проанализировать. Число входных диапазонов должно быть не больше 16.

На выходе процедуры в  выходном диапазоне получаем отчет, приведенный в таблице 8.3а - 8.3в.

ВЫВОД ИТОГОВ

Сначала рассмотрим верхнюю  часть расчетов, представленную в таблице 8.3а, - регрессионную статистику.

Величина R-квадрат, называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала [0;1].

В большинстве случаев  значение R-квадрат находится между этими значениями, называемыми экстремальными, т.е. между нулем и единицей.

Если значение R-квадрата близко к единице, это означает, что построенная модель объясняет почти всю изменчивость соответствующих переменных. И наоборот, значение R-квадрата, близкое к нулю, означает плохое качество построенной модели.

В нашем примере мера определенности равна 0,99673, что говорит об очень  хорошей подгонке регрессионной  прямой к исходным данным.

множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y).

Множественный R равен квадратному корню из коэффициента детерминации, эта величина принимает значения в интервале от нуля до единицы.

В простом линейном регрессионном  анализе множественный R равен коэффициенту корреляции Пирсона. Действительно, множественный R в нашем случае равен коэффициенту корреляции Пирсона из предыдущего примера (0,998364).

Теперь рассмотрим среднюю  часть расчетов, представленную в таблице 8.3б. Здесь даны коэффициент регрессии b (2,305454545) и смещение по оси ординат, т.е. константа a (2,694545455).

Исходя из расчетов, можем  записать уравнение регрессии таким  образом:

Y= x*2,305454545+2,694545455

Направление связи между  переменными определяется на основании  знаков (отрицательный или положительный) коэффициентов регрессии (коэффициента b).

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

Если знак при коэффициенте регрессии - отрицательный, связь зависимой  переменной с независимой является отрицательной (обратной).

В таблице 8.3в. представлены результаты вывода остатков. Для того чтобы эти результаты появились в отчете, необходимо при запуске инструмента "Регрессия" активировать чекбокс "Остатки".

ВЫВОД ОСТАТКА

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

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

 
Рис. 8.3.  Исходные данные и линия регрессии

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

Имея уравнение регрессии, задача прогнозирования сводится к  решению уравнения Y= x*2,305454545+2,694545455 с известными значениями x. Результаты прогнозирования зависимой переменной Y на шесть шагов вперед представлены в таблице 8.4.

Таким образом, в результате использования регрессионного анализа  в пакете Microsoft Excel мы:

• построили уравнение регрессии;
• установили форму зависимости и направление связи между переменными - положительная линейная регрессия, которая выражается в равномерном росте функции;
• установили направление связи между переменными;
• оценили качество полученной регрессионной прямой;
• смогли увидеть отклонения расчетных данных от данных исходного набора;
• предсказали будущие значения зависимой переменной.

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

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

Выводы

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