Статистическая обработка экспериментальных данных в MS EXCEL

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РФ

ДЕПАРТАМЕНТ НАУЧНО-ТЕХНОЛОГИЧЕСКОЙ ПОЛИТИКИ 

И ОБРАЗОВАНИЯ

ФГБОУ ВО «САНКТ-ПЕТЕРБУРГСКАЯ ГОСУДАРСТВЕННАЯ

АКАДЕМИЯ ВЕТЕРИНАРНОЙ МЕДИЦИНЫ»

СМИРНОВА Е.М.

СТАТИСТИЧЕСКАЯ ОБРАБОТКА 

ЭКСПЕРИМЕНТАЛЬНЫХ 

ДАННЫХ В MS EXCEL

Учебно-методическое пособие

для студентов, обучающихся по специальности

36.05.01 –Ветеринария,

по направлениям подготовки

06.03.01 – Биология, 35.03.08 – Водные биоресурсы и аквакультура, 

36.03.01 – ветеринарно-санитарная экспертиза

Санкт-Петербург

2019

УДК: 510 (022)

Смирнова Е.М. Статистическая обработка экспериментальных данных 

в MS EXCEL : учебно-методическое пособие для студентов, обучающихся по 
специальности 36.05.01 – Ветеринария, по направлениям подготовки 06.03.01 
– Биология, 35.03.08 – Водные биоресурсы и аквакультура, 36.03.01 –
ветеринарно-санитарная экспертиза / Е.М. Смирнова ; МСХ РФ, СПбГАВМ. 
– Санкт-Петербург : СПбГАВМ, 2019. – 24 с.

Учебно-методическое пособие
по  информатике – статистическая 

обработка экспериментальных данных в MS EXCEL– предназначено для 
студентов, обучающихся по специальности 36.05.01 –ветеринария, по 
направлениям подготовки06.03.01 – Биология, 35.03.08 – Водные биоресурсы 
и аквакультура, 36.03.01 – ветеринарно-санитарная экспертиза. В учебно-
методическое пособие включен теоретический материал и контрольные 
вопросы для защиты лабораторной работы.

Автор: к.п.н. Смирнова Е.М.

Рецензент: к.т.н, доц. Чумаков С.И.

Одобрено методическим советом 

Санкт-Петербургской государственной 

академии ветеринарной медицины, 

протокол №6 от 21.06.2019 г.

© Санкт-Петербургская государственная академия

ветеринарной медицины, 2019 г.

Математическая статистика.

Цель работы: 
Освоение стандартных программ и пакета "Анализ 

данных" электронных таблиц Excel для решения задач математической 
статистики.

Появление электронных таблиц (табличных процессоров) привело к 

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

Первичная обработка статистических данных осваивалась студентами 

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

В статистике, как правило, статистические данные являются 

результатами наблюдений над некоторой случайной величиной X. В 
настоящей лабораторной работе будет рассмотрены задачи по обработке
дискретных случайных величин. 

Точечные оценки параметров распределения.

Основными 
характеристиками 
случайных 
величин
являются 

математическое 
ожидание 
и 
дисперсия. 
Математическое 
ожидание 

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

квадраты отклонений этих значений от среднего значения.

В статистике аналогом математического ожидания случайной 

величины Х является среднее арифметическое значение всех наблюдаемых 
значений случайной величины – mx. Разброс значений Х вокруг среднего 
арифметического значения называется дисперсией и обозначают её Dx.

Множество всех значений случайной величины Х в статистике 

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

Из генеральной совокупности выбирают случайным образом меньшее 

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

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

генеральная совокупность в уменьшенном масштабе, сохраняющая все 
свойства генеральной совокупности. (Далее будем считать, что все 
рассматриваемые выборки являются репрезентативными). Тогда по выборке 
находят среднее арифметическое значение
–
m̃ xи дисперсию
– D̃ x. 

Найденные значения m̃ xи 
D̃ x называют оценками соответствующих 

параметров генеральной совокупности. Таким образом, найденные значения 
m̃ x и D̃ x можно считать приближенными значениями математического 
ожидания и дисперсии генеральной совокупности: mx, Dx.
Приведём 

расчетные формулы m̃ x и D̃ x, когда объём выборки равен n. Тогда:

𝑚̃𝑥 =

∑
𝑥𝑖

𝑛
𝑖=1
𝑚�
,

𝐷̃ 𝑥 =

∑
(𝑥𝑖 − 𝑚̃𝑥)2
𝑛
𝑖=1

𝑚� − 1
.

Кроме указанных оценок находят среднее квадратическое отклонение 𝜎̃𝑥 =
√ 𝐷̃ 𝑥.

Оценки m̃ x и D̃ x называют точечными статистическими оценками, так 

как каждая из них определяется точкой на оси значений Х.

Интервальные оценки. Доверительная вероятность, 

доверительный интервал.

Решение статистической задачи будет не полным, если не определить 

интервал, который покрывает с заданной надёжностью β
не только 

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

Надежность полученных оценок называют ещё доверительной 

вероятностью β. Эту вероятность задаёт сам исследователь. Конкретное 
значение доверительной вероятности β зависит от области исследования. 

Так, например, если статистические данные относятся к области 

медицины, ветеринарии, биологии, то обычно задаются доверительной 
вероятностью β = 0,95. Это значит: надёжность или доверие к полученным 
оценкам равно 95%, недоверие при этом определяется в 5%.

Напишем доверительный интервал для mx, предполагая,
что 

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

m̃ x − ε < mx < m̃ x + ε,

где ε = t ∙

σ

√n − точность оценки; n – объём выборки; σ – заданное среднее 

квадратическое отклонение; t – значение аргумента интеграла Лапласа 
Ф(t)=β/2 1.

Доверительный 
интервал 
при 
неизвестном 
σ
генеральной 

совокупности и при объёме выборки n < 30 будет иметь следующий вид:

𝑚̃𝑥 − 𝜀 < 𝑚𝑥 < 𝑚̃𝑥 + 𝜀, 

где ε = tСт ∙

σ̃

√n. В этой формуле неизвестное σ генеральной совокупности 

заменено на среднее квадратическое отклонение выборки, то есть на σ̃;     tСт
− есть значение критерия Стьюдента, который выбирается из таблицы 
критериев Стьюдента. Этот критерий зависит от доверительной вероятности 
β и числа степеней свободы k. Число степеней свободы определяется по 
формуле: k = n – 1.

Доказано: если объём выборки n ≥ 30, то распределение Стьюдента 

совпадает с нормальным распределением.

Следует отметить, что при проведении экспериментов в ветеринарии, 

как правило, приходится  иметь дело с малыми выборками, то есть 
рассматривать случаи, когда n < 30.

Открыть свою рабочую книгу переименовать свободный лист в 

лист "Лаб.работа 4". Дальше вводить задачу №1.

Задача №1.
Из генеральной совокупности, имеющей нормальное распределение, 

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

4,5; 4,7; 4,6; 4,4; 4,7; 4,7; 4,5; 4,6; 4,7; 4,6. 

Найти оценки математического среднего и дисперсии, среднее 

квадратическое отклонение выборки. Определить доверительный интервал, 
если доверительная вероятность β = 0,95. Задачу решать в среде Excel.

Решение. Из условия задачи ясно, что среднее квадратическое 

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

доверительного 
интервала 
воспользоваться 
функцией 
ДОВЕРИТ, 

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

использованием 
критерия 
Стьюдента 
для 
определения 
границ 

доверительного интервала.

1 Интеграл Лапласа Ф(х) =

1

√2𝜋 ∫ 𝑒−𝑧2

𝑧 𝑑𝑧 −

х
0
табличная функция.

Для определения оценки математического среднего воспользуемся 

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

ДИСП(диапазон исходных данных); среднее квадратическое отклонение 
выборки 
будем 
определять 
при 
помощи 
статистической 
функции 

СТАНДОТКЛОН(диапазон исходных данных). 

Итак, открыть лист Лаб.работа 4 своей рабочей книги.

В
ячейки В2:В11
ввести исходные 

данные из условия задачи. Затем в ячейку А12
ввести текст: Среднее, в ячейку А13
–

Дисперсия, в ячейку А14 – Сред.квадр.отклон. 

Теперь 
найдём 
значения 
оценок. 

Фиксируем курсор в ячейке В12, щелкаем по 
кнопке f(x) в строке формул, в открывшемся 
диалоговом 
окне 
среди 
статистических 

функций щелкаем по строке с функцией 
СРЗНАЧ. 
Открывается 
окно 
"Аргументы 

функции", в нём следует указать диапазон 
ячеек с исходными данными –
то есть 

протащить мышь по диапазону В2:В11 – и 
щелкнуть по кнопке  ОК. В ячейке В12
высветится полученный результат. 

Далее по тому же сценарию вычислить 

дисперсию 
и 
среднее 
квадратическое 

отклонение. Каждый раз в качестве диапазона 
ячеек указываем диапазон В2:В11. Результаты 
разместить соответственно в ячейках В13, В14.
Теперь 
найдём 
границы 
доверительного 

интервала. В ячейку А15 введём название Критерий Стьюдента. Чтобы 

определить
критерий

Стьюдента
установим 

курсор 
в 
ячейке 
В15, 

щёлкаем по кнопке f(x) в 
строке 
формул, 
в 

открывшемся диалоговом 
окне среди статистических 
функций 
щелкаем 
по 

строке 
с 
функцией

СТЬЮДРАСПОБР. 

Открывается 
окно 

"Аргументы функции", в 
нём 
следует 
указать 

значение вероятности: 1 – β, то есть 0,05 и число степеней свободы k = n – 1. 

В нашем случае k = 10 – 1 = 9. Щелкаем по кнопке  ОК. В результате в ячейке 
В15 высветится табличное значение критерия Стьюдента tСт = 2,2621572. 

Далее, в ячейку А16 записываем Epsilon, в ячейку В16 записываем 

формулу для вычисления ε: ε = tСт ∙

σ̃

√n = В15 ∙

В14

√10.

Обратите внимание, в формуле стоит ссылка на ячейку со значением σ̃. 

Итак, в ячейку В16 следует записать: =В15*В14/КОРЕНЬ(10). Получим 
результат 0,075405.

В ячейки А17 и А18 вводим соответственно названия: "Нижняя 

граница довер.инт." и "Верхняя граница довер.инт.". В ячейку В17 записать 
формулу = В12 – В16. В ячейку В18 записать =В12 + В16. Итак, 
доверительный интервал, который накрывает истинное среднее значение
𝑚𝑥 и его оценку 𝑚̃𝑥, имеет вид l: (4,524595; 4,675405).

Результаты решения поставленной задачи отображает
фрагмент 

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

Пусть статистические данные заданы в виде выборки  x1, x2, … , xN. 

Первичная обработка
данных, обычно включает в себя построение 

вариационного 
и 
статистического 
рядов, 
определение 
выборочных 

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

Статистическую обработку будем производить с помощью пакета

"Анализ данных" и статистических
функций библиотеки встроенных 

функций Excel. 

Если пакет "Анализ данных"
не подключен, то нужно его 

предварительно подключить. Для этого используя кнопку
Office,

выбрать в открывшемся окне Параметры
Excel, далее выбрать команду 

Надстройки. В окне Надстройки щелкнуть по строке "Пакет анализа – VBA", 

затем щелкнуть по кнопке Перейти. В появившемся диалоговом окне 
установить метку (галочку) в строке "Пакет анализа – VBA", нажать ОК.
Пакет будет установлен и станет доступен на вкладке Данные в группе 
Анализ через команду Анализ данных.

Команда Анализ данных позволяет осуществлять разные и сложные 

алгоритмы при решении задачи по обработке статистических данных.

Рассмотрим наш алгоритм решения:

1. ввод статистических данных, то есть требуется ввести исходные 

выборочные значения 𝑥1, 𝑥2, … , 𝑥𝑁;

2. ранжирование данных;
3. создание статистического ряда, построение гистограммы;
4. построение статистического распределения;
5. определение выборочных характеристик;
6. формулирование гипотез о виде закона распределения;
7. проверка гипотез с помощью критерия согласия Пирсона – хи-квадрат.

Используя предлагаемый алгоритм, решим задачу по обработке 

статистических данных.

Сначала открыть свободный лист 8 рабочей книги переименовать в 

лист "Мат. статистика" и открыть его. Продолжать работать на этом листе.

Задача №2. Имеется выборка из 30 значений некоторой случайной 

величины Х: 8, 7, 5, 7, 3, 7, 7, 6, 8, 5, 9, 2, 4, 6, 6, 9, 8, 7, 7, 5, 9, 6, 7, 4, 11, 4, 3, 
10,10, 5. Объём приведённой выборки N = 30.

Требуется провести обработку выборки с помощью команды Анализ 

данных. 

Решение. 
Шаг 
первый.
Исходные 

экспериментальные 
данные
необходимо 

ввести в столбец А. Сначала в ячейку А1 ввести
название столбца данных – "Выборка". В 
ячейки
В1
и С1 ввести соответственно 

названия: 
"Ранжированная 
выборка"
и 

"Повторяющиеся значения выборки".

Далее в ячейки А2:А31 ввести исходные 

данные из условия задачи
№2.
Справа 

приведён фрагмент с данными в столбце А. 

Шаг второй. Ранжирование заданной 

выборки или создание вариационного ряда. 

Сначала копируем выборку из ячеек 

А2:А31 в ячейки В2:В31 и  воспользуемся 
командой Сортировка. Для этого выделим 
ячейки В2:В31, далее на вкладке Главная в группе Редактирование
щелкаем по команде Сортировка. В открывшемся списке подкоманд 

выбираем – щелкаем – по строке "Сортировать от минимального к 
максимальному". На экране появляется следующее диалоговое окно: 

Так как сортировать 

данные надо только в 
столбце 
В, 
то 
в 

представленном 
окне 

делаем соответствующий 
выбор – "сортировать в 
пределах 
указанного 

выделения" – и нажимаем 
на кнопку Сортировка. В 

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

Шаг третий. Построение статистического ряда.
Напомним, статистическим рядом называется таблица, в которой 

указаны повторяющиеся значения случайной величины и их абсолютные 
частоты ni.

Поэтому из столбца В в столбец С в ячейки С2:С11 вводим сначала 

различные значения повторяющихся вариант выборки в возрастающем 
порядке. Всего должно быть 10 различных значений, а именно: 2, 3, 4, 5, 6, 7, 
8, 9, 10, 11.

На предущей странице приведён фраемент листа Excel с исходными 

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

Перейдем к определению частот повторяющихся значений выборки. 

Обратимся 
к 
вкладке 

Данные
в 
группу 

Анализ. 
Щелкаем 
по 

команде 
Анализ 

данных.

Открывается 

диалоговое окно "Анализ
данных", 
в 
котором 

представлен 
список 

процедур 
–

"Инструменты анализа". Щелкаем по строке Гистограмма и нажимаем на 
кнопку ОК. Появляется другое диалоговое окно – "Гистограмма". В этом 
окне в соответствующие поля следует ввести необходимые параметры для 
построения гистограммы.

Рассмотрим эти параметры.


В поле Входной интервал необходимо ввести ссылку на выборку, то 

есть на диапазон ячеек:  А1:А31. Для этого надо протащить мышь по 
указанному диапазону.


В поле Интервал 

карманов ввести ссылку 
на диапазон ячеек с 
различными 
выборочными 
значениями: 
 
С1:С11. 

Здесь 
карман 
–
это 

отрезки 
граничных 

значений 
случайной 

величины. 
Excel 

вычисляет 
число 

попаданий 
данных 
в 

диапазон 
между 

текущим 
началом 

отрезка и началом следующего по возрастанию отрезка.


В поле Метки 
поставить галочку, если обрабатываемые столбцы 

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


Поле Выходной интервал по умолчанию не активизировано, поэтому 

сначала его активизируем – щёлкаем слева от названия поля. Далее в окошко 
поля справа вводим ссылку на ячейку, которая будет левой верхней ячейкой 
диапазона ячеек для размещения результатов запускаемой команды. В нашем 
случае диапазон будет начинаться в ячейке Н2.


В поле Вывод графика нужно в окошке слева от названия поля 

установить флажок, так как гистограмму необходимо вывести. Нажить ОК.

На 
экране 
в 
диапазоне 
для 
выходных 
результатов,
Н2:I13, 

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

В столбце Карман (столбец Н) отображены повторяющиеся значения в 

исходной выборке x1, x2, … , xN. В столбце Частота (столбец I) размещены 
вычисленные Excel значения абсолютных частот ni для каждого значения xi. 
Диапазон ячеек Н2:I13 и есть искомый статистический ряд.