Методы математической статистики Excel

  Инструмент  Описательная статистика вычисляет полный доверительный интервал выборки: на рис. 2.84 он равен 31. Таким образом, можно утверждать, что в 95% случаев значения выборки попадут в доверительный интервал [228.2-15.5, 228.2+15.5].

  Функция ДОВЕРИТ вычисляет полуширину доверительного интервала среднего по заданному уровню значимости, стандартному отклонению и числу значений в выборке. Пусть требуется найти границы доверительного интервала для среднего с 95% надежностью (уровень значимости a=0.05) для 50 отправлений по электронной почте, если известно среднее время доставки сообщения m=30сек, стандартное отклонение r=3сек.

  Введите статистическую функцию ДОВЕРИТ  и заполните параметры, как показано на рис. 2.85. После нажатия ОК, вы получите значение ДОВЕРИТ(0,05;3;50)=0.83154. Это означает, что с уверенностью 95% среднее арифметическое времени доставки сообщения по E-mail для генеральной совокупности будет находиться в интервале [30-0.83154, 30+0.83154]. 

Рис. 2.85 

ДОВЕРИТ(0,01;3;50) = 1.09283

ДОВЕРИТ(0,05;5;50) = 1.3859

ДОВЕРИТ(0,05;3;150) = 0.48009 

  Выше  рассчитаны доверительные интервалы  среднего для различных значений параметров. Как видно, доверительный  интервал шире для больших значений уровня значимости a и стандартного отклонения r; и – уже при большем размере выборки. 
 

Подбор  типа распределения 

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

• графический метод, позволяющий визуально оценить меру соответствия; например, график на рис. 2.81 напоминает форму нормальной кривой и при большом объеме (>50) выборки совпадения/расхождения более очевидны;
• числовые характеристики асимметрию и эксцесс; асимметрия характеризует степень несимметричности распределения относительно среднего вправо (>0) и влево (<0); эксцесс характеризует степень остроконечности (>0) или сглаженности (<0) «хвостов» распределения; можно говорить о нормальности распределения, если асимметрия находится в интервале [–0.2;+0.2], а эксцесс – в интервале [2;4];
• критерии согласия, в частности ХИ-квадрат, который вычисляет вероятность совпадения выборки с нормальным распределением (функция ХИ2ТЕСТ в Excel).

  Рассмотрим  применение функции ХИ2ТЕСТ, дающей наиболее убедительную оценку меры соответствия выборки нормальному распределению. Если вычисленная вероятность совпадения ниже 0.95 (95%), то выборка не соответствует  нормальному распределению, если выше 0.95, то можно утверждать о нормальном законе распределения выборки.

  Поскольку критерий ХИ-квадрат основан на сравнении частот интервалов, то для функции ХИ2ТЕСТ должны быть предварительно подготовлены выборочное и теоретическое распределения частот по интервалам с помощью функции ЧАСТОТА или инструмента Гистограмма. На рис. 2.86 дана некоторая выборка, к ней вычислены частоты и теоретические частоты, на основе которых вычислена вероятность совпадения распределений 0.989531786. Это значение говорит о высокой степени соответствия выборки нормальному распределению. 

Рис. 2.86 

  Последовательность  действий результата на рис. 2.86 следующая:

1. Введите исходные данные в ячейки А3:Е14. В колонке G введите интервалы карманов и с помощью функции ЧАСТОТА в колонке H вычислите относительные частоты значений выборки.
2. В ячейке Н15 вычислите размер выборки (=СУММ(H2:H14)), в ячейке Н16 – среднее арифметическое выборки (=СРЗНАЧ(A3:E14)), в ячейке Н17 – стандартное отклонение (=СТАНДОТКЛОН(A3:E14)).
3. В колонке I вычислите статистические вероятности – это необходимо для дальнейшего графического сравнения выборочного распределения вероятностей с теоретическим. В ячейку I3 запишите формулу =H2/H$15, затем размножьте ее на диапазон I4:I14.
4. По вычисленным в п.2 данным постройте теоретическое нормальное распределение вероятностей, для чего в ячейку J3 запишите функцию =НОРМРАСП(G2;H$16;H$17;0). Затем размножьте ее на диапазон J4:J14.
5. В колонке К вычислите теоретические частоты: в ячейку К3 запишите формулу =J2*H$15 и размножьте ее на диапазон К4:К14.
6. В ячейку К17 введите функцию ХИ2ТЕСТ. Параметры функции показаны на рис. 2.87.

 

Рис. 2.87 

  Для графической оценки постройте графики  выборочного (I4:I14) и теоретического (J4:J14) распределения вероятностей – рис. 2.88. Сравнение графиков не опровергает результата работы функции ХИ2ТЕСТ: выборка в целом соответствует нормальному распределению 

Рис. 2.88 

  Функцию ХИ2ТЕСТ применяют также в случаях, когда требуется выявить наличие  различий между выборками, а закон  распределения данных неизвестен. При этом обычно известны лишь расчетные, теоретические значения, которые принимают за генеральную совокупность. Вычисляется вероятность случайного появления значений в выборках: если вероятность p меньше уровня значимости a=0.05, то различия между выборками не случайны и делают вывод о достоверном отличии (независимости) выборок друг от друга (уровень значимости a – максимальное значение вероятности, при котором появление события практически невозможно). 

Рис. 2.89 

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

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

Рис. 2.90 

  Далее применим функцию =ХИ2ТЕСТ(B3:D12;E3:G12). Результат 0.868486 (>0.05) говорит о том, что  различия между выборками случайны и не выявлено достоверных отличий  выборок друг от друга. 
 

Сравнение и анализ двух выборок 

  Для выявления различий между двумя  выборками с известным законом  распределения применяют t-критерий различия Стьюдента и критерий различия Фишера. При этом предполагается, что данные распределены по нормальному закону. Первый критерий сравнивает средние двух выборок и вычисляет вероятность того, что они относятся к одной и той же генеральной совокупности. Второй критерий проверяет принадлежность дисперсий двух выборок одной генеральной совокупности. В обоих случаях по вычисленной вероятности судят о принадлежности выборок к одной или разным совокупностям: если вероятность случайного появления значений в исследуемых выборках меньше уровня значимости a<0.05, то различия между выборками не случайны и они достоверно отличаются друг от друга.

  Рассмотрим  использование t-критерия Стьюдента для определения наличия различий между двумя выборками. При этом выборки могут быть:

• независимыми, несвязными с разным числом значений в выборках – анализируют с помощью инструмента Двухвыборочный t-тест с различными дисперсиями или Двухвыборочный t-тест с одинаковыми дисперсиями;
• зависимыми, связанными с равным числом значений в выборках – анализируют с помощью инструмента Парный двухвыборочный t-тест для средних или Двухвыборочный t-тест с различными дисперсиями.

  Включенная  в Excel функция ТТЕСТ для оценки отличий по t-критерия Стьюдента имеет параметр Тип для настройки на один из видов t-теста: 1 – парный тест, 2 - двухвыборочный t-тест с одинаковыми дисперсиями, 3 - двухвыборочный t-тест с разными дисперсиями.

  На  рис. 2.91 приведены данные о месячных продажах хлебцев Burger, продаваемых без рекламы, и хлебцев Finn Crisp, продаваемых с рекламной поддержкой. Необходимо выявить достоверность различий в этих данных. Здесь же приведены результаты функции ТТЕСТ (ячейка В14) и инструмента Двухвыборочный t-тест с различными дисперсиями. 

Рис. 2.91 

  Полученное  с помощью функции ТТЕСТ значение величины случайного появления анализируемых  выборок 0.07895 больше уровня значимости a=0.05. Таким образом, различия между выборками случайны и считаются не отличающимися друг от друга, что говорит о неэффективности рекламной поддержки хлебцев Finn Crisp и, возможно, о большей «раскрученности» бренда Burger. Аналогичные результаты получены инструментом Двухвыборочный t-тест с различными дисперсиями – вероятность случайного появления выборок P(T<=t) двухстороннее=0.0787.

  Воспроизведите  полученные результаты. В ячейку В14 введите функцию ТТЕСТ из группы Статистические, заполните параметры, как на рис. 2.92 и нажмите ОК. Здесь выбран Тип=3, поскольку выборки не связаны, независимы и с разным числом значений. 

Рис. 2.92 

  Далее вызовите инструмент Двухвыборочный t-тест с различными дисперсиями через меню СервисàАнализ данных…. На рис. 2.93 показано заполнение параметров инструмента. Интервал переменной 1 $А$2:$A$10 и интервал переменной 2 $B$2:$B$12 это диапазоны анализируемых данных. Выходной интервал $D$1 – это ячейка, начиная с которой будет выведен результат. Поле Альфа позволяет установить требуемый уровень значимости a=0.05. 

Рис. 2.93 

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

Дисперсионный анализ 

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

  Рассмотрим  однофакторный дисперсионный анализ. Степень влияния фактора на выборку определяется сравнением дисперсий двух выборок: выборки с наличием исследуемого фактора и выборки без этого фактора (со случайными причинами). Инструмент Excel Однофакторный дисперсионный анализ вычисляет вероятность случайности различий (Р-значение), которая указывает на значимость различий: если уровень значимости меньше 0.05, то различия не случайны и говорят о статистическом влиянии фактора на выборку (переменную).

  В качестве примера проведем анализ влияния  фактора цены комплексного обеда на дневную посещаемость кафе – рис. 2.94. На рис. 2.95 приведен результат анализа: Р-значение=0.00068257 <0.05. Это доказывает влияние фактора цены на посещаемость кафе. 

Рис. 2.94 

Рис. 2.95 

  Воспроизведите  полученные результаты. Введите данные и вызовите инструмент Однофакторный дисперсионный анализ через меню СервисàАнализ данных…. На рис. 2.96 показано заполнение параметров инструмента. Входной интервал $В$2:$I$4 это диапазон исследуемых данных. Переключатель Группирование установлен по строкам, т.к. выборки располагаются по строкам. Выходной интервал $J$1 – это ячейка, начиная с которой будет выведен результат. Поле Альфа позволяет установить требуемый уровень значимости, здесь a=0.05.