Статистический анализ данных в MS Excel

СТАТИСТИЧЕСКИЙ 
АНАЛИЗ ДАННЫХ 

В MS EXCEL

Москва
ИНФРА-М

202УЧЕБНОЕ ПОСОБИЕ

А.Ю. КОЗЛОВ 
В.С. МХИТАРЯН 
В.Ф. ШИШОВ

Рекомендовано 

УМО по образованию в области статистики
в качестве учебного пособия для студентов

высших учебных заведений, обучающихся 

по экономическим специальностям

УДК 311(075.8)
ББК 60.6я73
 
К59

Козлов А.Ю.

Статистический анализ данных в MS Excel : учебное пособие / 
А.Ю. Козлов, В.С. Мхитарян, В.Ф. Шишов. – Москва :
ИНФРА-М, 2023. – 320 с. – (Высшее образование: Бакалавриат). — 
DOI 10.12737/2842.

ISBN 978-5-16-004579-5 (print)
ISBN 978-5-16-101024-2 (online)
Учебное пособие содержит полное и подробное описание средств 

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

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

УДК 311(075.8)

ББК 60.6я73

Р е ц е н з е н т ы:

Ю.В. Сажин, д-р экон. наук, профессор, зав. кафедрой статистики 

Мордовского государственного университета;

Н.П. Тихомиров, д-р экон. наук, профессор, декан экономико-мате-

матического факультета Российского экономического университета 
им. Г.В. Плеханова

К59

© Козлов А.Ю., Мхитарян В.С., 
    Шишов В.Ф., 2012

ISBN 978-5-16-004579-5 (print)
ISBN 978-5-16-101024-2 (online)

Оригинал-макет подготовлен в НИЦ ИНФРА-М

Подписано в печать 14.12.2022. 

Формат 6090/16. Бумага офсетная. Гарнитура Newton. 

Печать цифровая. Усл. печ. л. 20,0. 

ППТ30. Заказ № 00000

ТК 160850-1907518-250711

ФЗ 

№ 436-ФЗ

Издание не подлежит маркировке 
в соответствии с п. 1 ч. 4 ст. 11

Отпечатано в типографии ООО «Научно-издательский центр ИНФРА-М»

127214, Москва, ул. Полярная, д. 31В, стр. 1

Тел.: (495) 280-15-96, 280-33-86. Факс: (495) 280-36-29

ООО «Научно-издательский центр ИНФРА-М»

127214, Москва, ул. Полярная, д. 31В, стр. 1

Тел.: (495) 280-15-96, 280-33-86. Факс: (495) 280-36-29

E-mail: books@infra-m.ru        http://www.infra-m.ru

ПРЕДИСЛОВИЕ

В настоящее время трудно себе представить исследование и про-

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

В настоящее время наиболее популярным является программное 

обеспечение, работающее в операционной системе и по-
и по-
 и по-

ставляемое вместе с компьютером. Одним из составляющих этого 
обеспечения является программа , с помощью ко-
, с помощью ко-
 , с помощью ко-
, с помощью ко-
, с помощью ко-

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

Многие книги, посвященные S , описывают лишь инстру-

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

 
• генерировать случайные числа, подчиняющиеся различным за-

конам распределения;

 
• проводить формирование выборки из генеральной совокуп-

ности;

 
• по выборке строить интервальный вариационный ряд, гистог-

рамму, кумулятивную кривую и диаграмму Парето;

 
• вычислять точечные и интервальные оценки статистической со-

вокупности;

• проводить сглаживание временных рядов;

 
• оценивать зависимость системы двух случайных величин;

 
• проверять статистические гипотезы с использованием раз-

личных критериев;

 
• проводить однофакторный и двухфакторный дисперсионный 

анализ;

 
• строить множественное уравнение регрессии;

 
• ранжировать статистические данные;

 
• проводить прямое и обратное преобразование Фурье.

S содержит встроенные функции, использование которых 

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

 
• проводить предварительную обработку данных;

 
• рассчитывать характеристики положения и рассеивания;

 
• определять зависимость определяемых величин;

 
• проводить интервальное оценивание;

 
• определять параметры законов распределения непрерывных 

случайных величин;

 
• проверять статистические гипотезы о параметрах распреде-

ления и виде закона распределения случайных величин;

 
• определять параметры законов распределения дискретных слу-

чайных величин;

 
• строить линейные и нелинейные уравнения регрессии, прово-

дить их анализ и давать прогнозные оценки результативного 
признака.
Для новичков, впервые встречающихся с S , в книге име-
S , в книге име-
 , в книге име-
, в книге име-
, в книге име-

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

димо для работы с инструментами пакета анализа и статистическими 
функциями.

В данной книге при рассмотрении пакета анализа, той или иной 

статистической функции S подробно изложены теорети-
S подробно изложены теорети-
 подробно изложены теорети-
подробно изложены теорети-
 подробно изложены теорети-

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

Такой подход дает возможность пользователю понять, каким об-

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

Учебный материал в книге изложен таким образом, что читатель 

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

Книга может быть рекомендована студентам, а также преподава-

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

1. НЕОБХОДИМЫЕ СВЕДЕНИЯ О MS EXCEL

В этой главе изложены сведения, необходимые для статистиче-

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

относится к программным продуктам, которые известны 

под названием «электронные таблицы». Электронная таблицы — это 
интерактивная программа, состоящая из набора строк и столбцов, 
изображенных на экране в специальном окне. Область, находящаяся 
на пересечении строки и столбца, называется ячейкой. В ячейке 
могут находиться число, текст или формула, с помощью которой 
осуществляются вычисления, относящиеся к одной или нескольким 
ячейкам. Ячейки можно копировать, перемещать, а также изменять 
их содержимое. При изменении содержимого ячейки производится 
автоматический пересчет содержимого всех ячеек, использующих в 
формулах измененную ячейку. На основе групп ячеек создаются диаграммы, 
графики и сводные таблицы. Электронную таблицу можно 
сохранить в отдельном файле для дальнейшего использования.

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

1.1. 
ОСНОВНЫЕ ПОЛОЖЕНИЯ РАБОТЫ  
С ЭЛЕКТРОННОЙ ТАБЛИЦЕЙ

После запуска на экране появится окно программы 
(рис. 1.1). 

Рассмотрим основные элементы этого окна, которыми будем 

пользоваться при использовании статистических функций S . 

При запуске автоматически создает файл, который называ-
автоматически создает файл, который называ-
 автоматически создает файл, который называ-

ется Книга (на рис. 1.1 — Книга1). Книга состоит из листов, число 
которых обычно равно 16. Каждому листу можно присвоить имя, 
которое укладывается на ярлычке листа. По умолчанию имена 
листов: Лист1, Лист2 и т.д.

Щелкнув мышью на нужном ярлычке, на экран выводится соот-

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

ляющаяся элементом одного файла-книги. Электронная таблица 
состоит из строк и столбцов; строки нумеруются 1, 2, ..., 16 394; 
столбцы обозначаются буквами А, В, ... АА, АВ, ...; всего столбцов 
256. На пересечении строк и столбцов находятся ячейки. Каждая 
ячейка имеет свой адрес А1, В18, А0243 и т.д.

Вертикальная и горизонтальная полосы прокрутки предназна-

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

Настройка Excel
Для работы с программой следует провести необходимые 

настройки. Практически все настройки можно сделать с по-
можно сделать с по-
 можно сделать с по-

Рис. 1.1. Окно программы Excel

мощью диалога Параметры, который вызывается командой меню 
Сервис → Параметры. 

 
• Выбрать команду меню Сервис → Параметры. На экране поя-

вится диалог Параметры. 

 
• Если какой-либо из описанных выше элементов окна, за ис-

ключением панелей инструментов, не присутствует на экране 
монитора, щелкнуть мышью на ярлычке с надписью Вид, чтобы 
выбрать нужную вкладку с элементами управления (рис. 1.2).

 
• Затем с помощью мыши установить флажки напротив названий 

тех элементов, которых нет на экране.

 
• Выбрать вкладку Правка (рис. 1.3) и убедиться, что установлены 

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

 
• После установки флажков закрыть диалог Параметры, нажав 

кнопку ОК.
Видимостью панелей инструментов можно управлять с помощью 

диалога Панели инструментов, который вызывается с помощью команды 
меню Вид → Панели инструментов. Если в окне на вашем 
экране нет панелей инструментов Стандартная или Форматирование, 
выполнить следующие действия: 

 
• выбрать команду меню Вид → Панели инструментов. На экране 

появится диалог Панели инструментов (рис. 1.4);

Рис. 1.2. Ярлык Вид

Рис. 1.3. Ярлык Правка

Рис. 1.4. Диалог Панели инструментов

установить с помощью щелчка мыши флажки напротив названий 

Стандартная и Форматирование и убедиться, что все остальные 
флажки сброшены. Если нет, то сбросить их.

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

 
¾ Для вызова справки нажать клавишу F1. На экране появится 

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

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

 
¾ Закрыть справочное окно, нажав комбинацию клавиш Alt+F4.

 
¾ Подвести указатель мыши к кнопке 
 на панели инструментов 

Стандартная.

 
¾ Под указателем мыши появится надпись Печать (Название 

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

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

 
¾ Нажать команду меню Справка → Что это такое? или комби-

нацию клавиш Shift+F1. К указателю мыши добавится знак во-
проса. 
Когда перемещается этот указатель над кнопками панелей ин-

струментов, в строке состояния также появляются подсказки.

 
¾ Щелкнуть мышью на кнопке 
. На экране появится окно, ко-

торое содержит подсказку о назначении этой кнопки (рис. 1.5).

Рис. 1.5. Подсказка о назначении кнопки