Построить график функции Точки разрыва функции Построение графика методом дифференциального исчисления Создание схемы логических элементов
Примеры решений Показатели вариации Доверительный интервал
Расчет моды и медианы Группировка данных Децили
Проверка гипотез по Пирсону Корреляционная таблица Квартили

Статистические функции в Excel

  1. Среднее значение: =СРЗНАЧ(диапазон)
  2. Мода: =МОДА(диапазон)
  3. Медиана: =МЕДИАНА(диапазон)
  4. Дисперсия для генеральной совокупности: =ДИСПР(диапазон)
  5. Среднеквадратическое отклонение: =СТАНДОТКЛОН(диапазон)
  6. Дисперсия: =ДИСП(диапазон)
  7. Квадратическое отклонение: КВАДРОТКЛ(диапазон)
  8. Коэффициент корреляции: КОРРЕЛ(диапазон 1;диапазон 2)
  9. Прогноз на следующий период: =ТЕНДЕНЦИЯ(диапазон Y;диапазон X; новое значение Х)
  10. Уравнение регрессии y = bx+a: =ЛИНЕЙН(диапазон Y;диапазон X;1;1).
  11. Ковариация: =КОВАР(массив1; массив2).

Пример. В таблице приведены результаты обследования среднемесячной заработной платы (в тыс. руб.)  100 работников одного предприятия (X – среднемесячная заработная плата, k=7, α=0.05).

51.5 69.6 75.6 66.5 63.4 67.5 53.3 69.7 56.5 66.5
61.8 72.0 76.8 70.9 66.0 67.8 58.5 83.8 62.9 76.0
84.6 54.2 49.3 73.5 64.6 56.0 52.8 70.9 76.7 71.4
69.4 63.8 80.6 59.1 85.4 65.0 61.5 69.2 56.5 90.4
75.2 87.1 70.4 65.8 63.2 79.8 74.8 62.9 84.5 78.8
57.6 74.5 73.8 64.2 79.1 75.5 62.2 75.9 66.6 72.6
58.3 75.3 66.4 78.2 78.3 75.0 72.0 59.9 47.4 69.4
61.0 76.6 78.3 69.9 69.9 76.4 91.0 53.5 69.4 65.3
63.5 71.0 72.0 76.4 75.0 57.6 81.4 67.8 67.5 64.2
64.7 68.2 74.9 66.6 78.9 77.7 63.3 70.6 84.3 69.9

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

Щелкните на поле Входной интервал и выделите ячейки А1:А100.
В поле Входной интервал отобразится диапазон $A1:$A100.
Выберите флажок Итоговая статистика. Щелкните на кнопке Ок.
Будет создан новый лист с итоговой статистикой по выбранным данным.

В рассматриваемом случае As=-0,06, Ex=-0,19, что свидетельствует о распределении, близком к нормальному.

2. Построить вариационный ряд, или ряд распределений и гистограмму для него.
Как видно в итоговой статистике, все возможные значения данного распределения укладываются в интервал 3σ.
Действительно,
Размах выборки 43,6. Разбиваем данный ряд на 7 интервалов длины 43,6: 7≈6,2.
Составим таблицу ряда распределений:

Таблица 1.

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

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

Таблица 2.

Середина интервала Значение функции Гаусса Теоретическая вероятность
50,5

0,005055791

0,003440824

56,7

0,01649618

0,011226818

62,9

0,033870577

0,023051325

69,1

0,043762967

0,029783797

75,3

0,035582452

0,024216378

81,5

0,018205807

0,012390341

87,7

0,005861772

0,003989351


Середины интервала скопируем из таблицы 1. Значение функции Гаусса  вычислим с помощью встроенной в Excel статистической функции НОРМ.РАСП. Для этого на вкладке Формулы щелкаем на кнопку Другие функции – выбираем Статистические и, среди них находим НОРМ.РАСП. Откроется следующее окно:

В открывшемся окне в поле X  указываем на ячейку со значением середины интервала, в поле Среднее указываем среднее значение, полученное в первом пункте работы с помощью описательной статистики, в поле Стандартное_откл – стандартное отклонение, найденное также с помощью описательной статистики. В поле Интегральная – ЛОЖЬ, так как ищем функцию плотности вероятности и нажимаем ОК.
Теоретическая вероятность вычисляется по формуле:
.
В таблице 2 выделяем последний столбец и строим график теоретической вероятности. Для этого на вкладке Вставка в группе График щелкните на типе графика. Появится список доступных подтипов графиков. При щелчке на подтипе графика будет создан график с макетом и цветовой схемой по умолчанию, определенными в теме оформления книги.
Изначально на горизонтальной оси обозначены номера интервалов. Правой кнопкой мыши щелкаем на эту ось и нажимаем на Выбрать данные. Меняем подпись горизонтальной оси, указывая первый столбец таблицы 2. Нажимая на график правой кнопкой мыши можно поменять цвет графика.

А теперь выполним сравнительный графический анализ формы эмпирического и теоретического распределений построением графика теоретической вероятности на гистограмме частот.
Для этого построим гистограмму частостей на интервалах. Щелкнем правой кнопкой мыши в поле гистограммы и нажмем на кнопку Выбрать данные. В открывшемся окне в левом столбце Элементы легенды нажмем кнопку Добавить. Откроется окно Изменение ряда. Имя ряда: укажем на ячейку Теоретическая вероятность, значения: выделим соответствующую строку. После нажатия Ок появится гистограмма теоретических вероятностей. Укажем на неё правой кнопкой мыши и выберем Изменить тип диаграммы для ряда, выбрав график. Также поменяем цвет нового графика.

4.Проверка согласованности теоретического и эмпирического распределений.
Схема применения критерия Χ2 для проверки гипотезы H0 о соответствии эмпирического ряда нормальному закону распределения, сводится к следующему.
1) Определяется мера расхождения эмпирических и теоретических частот Χ2 по формуле .
Для этого в свободной ячейке нажмем на вкладке Формулы выберем Статистические и, среди них ХИ2.ТЕСТ. Откроется следующее окно:

В строке Фактический интервал укажем столбец с частостями из таблицы1. В строке Ожидаемый интервал укажем столбец с теоретическими вероятностями из таблицы2. Нажимая на кнопку Ок, получим наблюдаемое значение Χ2.
2) Для выбранного уровня значимости α находим критическое значение Χ2 при числе свободы k-r-1 (в нашем случае для нормального распределения k-3).
Для этого в свободной ячейке нажмем на вкладке Формулы кнопку Другие функции и выбираем Статистические.Среди них ̶  ХИ2.ОБР.ПХ (так как находим правостороннюю критическую область распределения хи-квадрат). Откроется следующее окно:

В строку Вероятность введем нужное значение (в нашем случае 0,05), в строку Степенисвободы соответствующее число (в нашем случае 4, так как k=7 – число интервалов разбиения). Нажав на Ок получим критическое значение Χ2.

Χ2набл0,999998579
Χ2критич (при α=0,05, r=7-3=4)9,487729037

3) Если фактически наблюдаемое значение Χ2 больше критического, то гипотеза H0 отвергается; если наблюдаемое значение не превосходит критического, то гипотеза H0 не противоречит опытным данным.
Так как, Χ2набл2критич, можно считать, что рассматриваемое распределение подчиняется нормальному закону.