VI Международная студенческая научная конференция Студенческий научный форум - 2014

Введение

Для статистической обработки данных в Excel предусмотрены многочисленные статистические функции (порядка 80) и средства надстройки ПАКЕТ АНАЛИЗА. Эта надстройка содержит 19 статистических процедур и около 50 функций. Статистические процедуры предоставляют широкий спектр средств для статистического анализа начиная от простой описательной статистики или сглаживания данных и заканчивая анализом Фурье и проведением различных тестов.

1. Надстройка «Пакет анализа»

Средства, которые включены в надстройку Пакет анализа, доступны через команду Сервис→Анализ данных. (Если команды Анализ данных нет в меню Сервис, подключайте эту надстройку. Для этого выполните команду Сервис→Надстройки и в открывшемся диалоговом окне Надстройки в списке Доступные надстройки установите флажок напротив опции Пакет анализа). Команда Сервис→Анализ данных открывает одноимённое диалоговое окно, в списке Инструменты анализа которого следует выбрать необходимое средство (рис.1). После выбора какого-либо средства (и последующего щелчка на кнопке OK) открывается диалоговое окно этого средства.

Рис.1. Диалоговое окно Анализ данных со списком

инструментов статистического анализа

Полный список средств и их краткое описание представлены в табл.1.

Таблица 1

Статистические средства надстройки Пакет анализа

Средство	Описание
Однофакторный дисперсионный анализ	Используется для проверки гипотезы о равенстве математических ожиданий двух или более выборок
Двухфакторный дисперсионный анализ без повторений	Двухфакторный дисперсионный анализ на основе одной выборки
Двухфакторный дисперсионный анализ с повторениями	Двухфакторный дисперсионный анализ на основе нескольких выборок
Корреляция	Вычисляет корреляционную матрицу
Ковариация	Вычисляет матрицу ковариации
Описательная статистика	Создаёт отчёт, содержащий статистические характеристики представленной выборки
Экспоненциальное сглаживание	Реализует метод экспоненциального сглаживания данных
Двухвыборочный F-тест для дисперсий	Применяется для сравнения дисперсий двух генеральных совокупностей
Анализ Фурье	Реализует метод быстрого преобразования Фурье (БПФ) для анализа данных
Гистограмма	Используется для анализа распределения выборочных данных и построения гистограмм
Скользящее среднее	Используется для сглаживания данных
Генерация случайных чисел	Генерирует случайные числа, имеющие заданное распределение
Ранг и персентиль	Используется для вычисления рангов и квантилей
Регрессия	Используется для построения линейной регрессии
Выборка	Создаёт случайную выборку, рассматривая входной диапазон значений как генеральную совокупность
Парный двухвыборочный t-тест для средних	Используется для проверки гипотезы о равенстве математических ожиданий для дву3умерной выборки данных
Двухвыборочный t-тест с одинаковыми дисперсиями	Служит для проверки гипотезы о равенстве математических ожиданий для двух выборок. Предполагается равенство дисперсий генеральных совокупностей
Двухвыборочный t-тест с разыми дисперсиями	Используется для проверки гипотезы о равенстве математических ожиданий для двух выборок. Не требует предположения о равенстве дисперсий генеральных совокупностей
Двухвыборочный z-тест для средних	Используется для проверки гипотезы о различии между математическими ожиданиями двух генеральных совокупностей

2. Описательная статистика

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

В большинстве таких диалоговых окон (на рис.2 для примера показано диалоговое окно средства Описательная статистика) выделены области Входные данные и Параметры вывода.

Рис.2. – Диалоговое окно средства Описательная статистика

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

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

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

В данном случае имеются выборки разных размеров. Средство Описательная статистика правильно определяет размеры выборок, игнорируя пустые ячейки. На рис.4 показан рабочий лист с результатами расчётов.

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

Рис.3 – Три выборки и диалоговое окно Описательная статистика

Рис.4 – Результаты работы средства Описательная статистика

Таблица 2

Значения, вычисляемые средством Описательная статистика

Значение	Описание
Среднее	Выборочное среднее . Функция СРЗНАЧ
Стандартная ошибка	Оценка среднеквадратического отклонения выборочного среднего; вычисляется по формуле
Медиана	Значение медианы, т.е. квантиля порядка 0,5. Функция МЕДИАНА
Мода	Значение моды. Вычисляется так же, как и функция МОДА – если нет одинаковых выборочных значений, то возвращается значение ошибки #Н/Д
Стандартное отклонение	Оценка среднеквадратического отклонения генеральной совокупности . Функция СТАНДТКЛОН
Дисперсия выборки	Оценка дисперсии генеральной совокупности . Функция ДИСП
Эксцесс	Выборочный коэффициент эксцесса. Функция ЭКСЦЕСС
Асимметричность	Выборочный коэффициент асимметрии. Функция СКОС
Интервал	Размах выборки. Вычисляется как разность между максимальным и минимальным выборочными значениями
Минимум	Минимальное выборочное значение. Функция МИН
Максимум	Максимальное выборочное значение. Функция МАКС
Сумма	Сумма выборочных значений. Функция СУММ
Счёт	Объём выборки. Функция СЧЁТ
Наибольший (К)	К-е наибольшее значение. Если К=1, то выводится максимальное выборочное значение. Функция НАИБОЛЬШИЙ
Наименьший (К)	К-е наименьшее значение. Если К=1, то выводится минимальное выборочное значение. Функция НАИМЕНЬШИЙ
Уровень надёжности (X%)	Граница доверительного интервала для неизвестного математического ожидания с доверительным уровнем X%; доверительный интервал строится как выборочное среднее плюс-минус данное значение. Граница вычисляется с помощью распределения Стьюдента, то есть здесь неявно используется предположение о нормальности распределения генеральной совокупности. Поэтому к данному показателю следует относиться осторожно, особенно при малых выборках.

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

Опция Уровень надёжности указывает, надо ли вычислять границу доверительногоинтервала для среднего. В поле ввода рядом с этой опцией задаётся доверительный уровень в

процентах.

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

3. Корреляция

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

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

Максимальная степень корреляции принята за 1. Различают следующие виды корреляционной связи:

Сильная: ±0,7…±1,0

Умеренная: ±0,5…±0,7

Слабая: ±0,3…±0,5

Если r< ±0,3, то связь практически отсутствует.

Знак минус или плюс у коэффициента корреляции r указывает на направление связи. Знак плюс означает, что связь между признаками X и Y прямая (положительная), знак минус – связь обратная (отрицательная)

где: и - значения единичного результата;

и - средние арифметические признаков X и Y.

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

Величина коэффициента корреляции служит также оценкой соответствия уравнения регрессии выявленным связями.

На рис.5 указаны массивы данных Выборка 1 и Выборка 2, а также построен графики изменения переменных X и Y.

Рис.5 – Выборки данных и график изменения переменных

Рис.6 - Результаты работы средства Корреляция

Через команду Сервис→Анализ данных выбираем средство Корреляция. В область Входные данные вводим Входной интервал. В Параметрах вывода ставим галочку напротив Новый рабочий лист, где указываем наименование листа Корреляция.

В результате расчёта степень корреляции составила r = -0,18, это свидетельствует о том, что связь между переменными X и Y практически отсутствует.

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

Корреляция и регрессия тесно связаны между собой. Корреляция оценивает силу связи, регрессия исследует её форму.

Исследуется некоторая зависимость . Будем исходить из предположения, что эта зависимость описывается линейным уравнением. Об этом предварительно можно судить по виду построенного графика (рис.7).

Рис.7 – Графическое изображение исследуемой зависимости

На рис.8 размещена таблица с исходными данными.

В главном меню запустим серию команд Сервис→Анализ данных→Регрессия (рис.8). В появившемся диалоговом окне заполним поля ввода данных для обоих параметров Y и X; для этого в каждое окно (Входной интервал Y и Входной интервал X) поместим наши

Рис.8 – Таблица с исходными данными

данные, выделив их предварительно в соответствующих столбцах (рис.9).

Рис.9 – Диалоговое окно Регрессия

Отметим Уровень надёжности (доверительную вероятность), равный 95%. Укажем в окне вывода Выходной интервал рабочий лист, где будет формироваться весь блок получаемых статистических показателей. После чего нажмём кнопку OK.

На рис.10 представлены рассчитанные статистические показатели под заголовком «Вывод итогов».

Интерес представляют показатели, которые именованы как Коэффициенты. Один из них назван Y-пересечение, а второй – Переменная . Это и есть нужные коэффициенты регрессии: свободный член и коэффициент при аргументе .

Рис.10 – Лист Excel с результатами расчёта коэффициентов регрессии

Следовательно, уравнение регрессии, то есть формула, с некоторой вероятностью отображающая зависимость y от x, имеет следующий вид:

4.1 Проверка значимости модели

При проверке значимости модели принято придерживаться следующей последовательности действий:

1. Сначала выполняется общая проверка полученного уравнения на пригодность

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

3. Даётся сравнительная оценка степени влияния каждого из анализируемых факторов

4.2 Проверка на адекватность уравнения регрессии

Статическую оценку полученного уравнения (так называемый статический вывод) принято начинать с проведения F-теста, целью которого является выяснение способности исследуемых факторов объяснять значимую часть колебания функции . Этот тест используется как своеобразные «входные ворота» в статистический вывод: если результат значим, то связь существует, значит приступать к её исследованию и объяснению. Если проверка указывает на незначимость связи, то заключение лишь одно: мы имеем дело с набором случайных чисел, никак не связанных между собой. И больше делать нечего, так как нет предмета для анализа.

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

Использование так называемой нулевой гипотезы для F-теста означает, что между переменными и значимая связь отсутствует. Следовательно, признаётся, что параметр является чисто случайной величиной, поэтому значения переменных не оказывают на него никакого систематического влияния. Применительно к уравнению регрессии это утверждение можно трактовать как случай, когда все коэффициенты уравнения равны нулю.

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

Для выполнения F-теста воспользуемся результатами расчёта. Здесь обычно рекомендуются следующие приёмы:

1. Решение принимается на основе критерия Фишера

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

Обычно F-тест проводится путём сопоставления вычисленного значения F-критерия с эталонным (табличным) показателем для соответствующего уровня значимости. Если выполняется неравенство , то с уверенностью, например на 95%, можно утверждать, что рассматриваемая зависимость является статистически значимой. В противном случае наоборот.

2. Решение принимается на основе уровня значимости .

Для этого обратим внимание на представленные значения уровня значимости (в интерпретации Excel это показатель). Если -значение больше, чем 0,05, то полученный результат нужно трактовать как незначимый (для 95-процентной вероятности). В этом случае, когда величина оказывается меньше 0,05, то вывод такой: это значимое уравнение с вероятностью 95%. Если же , то полученный результат является высоко значимым, (степень риска ошибиться в нашем утверждении оказывается меньше 1%, то есть степень надёжности составляет 99%).

3. Решение принимается на основе коэффициента детерминации .

В этом случае имеющуюся расчётную величину (это то, что нам выдал Excel, см. рис.10) необходимо сравнить с табличными (критическими) значениями для соответствующего уровня значимости (повторим ещё раз, обычно это 0,05). Если окажется, что , то с упомянутой степенью вероятности (95%) можно утверждать, что анализируемая регрессия является значимой.

Теперь проанализируем наше уравнение с использованием рассмотренных статистических критериев.

1. Проведём проверку по F-критерию. Величина равна 19,14 (рис.10). Для анализа уравнения будем пользоваться величиной , обратной представленной Excel. Она составит 1:19,14=0,05. Найдём по эталонной таблице (прил.1) критическую величину при условии, что для числителя степень свободы , то есть составит 1 (число воздействующих факторов равно 1), а для знаменателя. Тогда будем иметь следующие значения для = 5,99 (для). Понятно, что для рассмотренной вероятности выполняется соотношение, поэтому уверенно можно говорить о высокой степени адекватности анализируемого уравнения.

2. Теперь выполним проверку с использованием уровня значимости (ещё раз напомним, что Excel этот показатель именует как). На рис.10 находим позицию Значимость F. Там указывается величина 0,0047. Фактически можно признать, что. Это говорит о том, что действительно обнаруживается устойчивая зависимость рассматриваемой функции от воздействующего фактора.

3. Напомним, что по нашим расчётам, коэффициент детерминации составляет 0,76 или 76%. Таблица для тестирования на уровне значимости 5% в случае выборки и числа переменных даёт критическое значение (прил.2). Поскольку выполняется соотношение , то с вероятностью 95% можно утверждать о наличии значимости данного уравнения регрессии.

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

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

4.3 Проверка на адекватность коэффициентов регрессии

Проверку на адекватность коэффициентов регрессии рекомендуется проводить по следующему методу.

Использование t-критерия. Необходимые расчёты делает Excel, который выдаёт показатель t. Анализируемый коэффициент считается значимым, если его t-критерий по абсолютной величине превышает 2,571 (прил. 3), что соответствует уровню значимости 0,05. В нашем примере имеем для коэффициентов следующие показатели критерия Стьюдента: ;. Из всего вышесказанного следует, что значимыми оказываются все коэффициенты нашего уравнения.

Поэтому в окончательном виде наше уравнение регрессии (для уровня значимости 0,05) следует записать так:

Литература:

1. Минько А.А. Статистический анализ в MS Excel. – М.: Издательский дом «Вильямс», 2004. – 448 с.

2. Бараз В.Р. Корреляционно-регрессионный анализ связи показателей коммерческой деятельности с использованием программы Excel. – Екатеринург, 2005. – 103 с.

Код для цитирования: Скопировать