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

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

В MS EXCEL

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

2022

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

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

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

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

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

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

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

Козлов А.Ю.

Статистический анализ данных в MS Excel : учебное посо
бие / А.Ю. Козлов, В.С. Мхитарян, В.Ф. Шишов. – Москва :
ИНФРА-М, 2022. – 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)

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

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

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

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

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

ТК 160850-1872730-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. Подсказка о назначении кнопки