Анализ данных в MS Excel

В.С. Мхитарян 
В.Ф. Шишов 
А.Ю. Козлов
АНАЛИЗ ДАННЫХ 
В MS EXCEL
УЧЕБНОЕ ПОСОБИЕ
Электронная копия печатной версии
Основные сведения о MS Excel
Статистические таблицы и графики
Статистические функции
Пакет анализа (анализ данных)
Рекомендовано в качестве учебного пособия для студентов 

высших учебных заведений, обучающихся по направлениям подготовки: 
1.01.03.01 и 1.01.04.01 — «Математика» (квалификация «бакалавр», «магистр»), 

5.38.03.01 и 5.38.04.01 — «Экономика» (квалификация «бакалавр», «магистр»)
Москва
КУРС
2024

УДК	 004.67(075.8)
ББК	 32.973.2я73	
ФЗ 
№ 436-ФЗ
Издание не подлежит маркировке 
в соответствии с п. 1 ч. 4 ст. 11-    -М93  ---Р е ц е н з е н т  ы:
Кафедра статистики, эконометрики и информационных технологий 
в управлении Национального исследовательского мордовского государственного университета им. Н.П. Огарева (заведующий кафедрой — 
д-р экон. наук, проф. Ю.В. Сажин); 
Т.А. Дуброва — д-р экон. наук, проф., профессор кафедры математических методов в экономике Российского экономического университета имени Г
. В. Плеханова
М93
Мхитарян В.С.
Анализ данных в MS Excel: учебное пособие / В.С. Мхитарян, 
В.Ф. Шишов, А.Ю. Козлов. — Москва: КУРС. — 1 файл.pdf:  368 с. – 
Электронная копия печатной версии.
ISBN 978-5-906923-26-4
Учебное пособие содержит подробное описание «Статистических функций» и средств анализа данных (инструментов) «Пакета анализа», 
входящих в настоящее время в MS Excel. В пособии также изложены 
сведения по основам работы в Excel, представлению первичной статистической информации в виде статистических таблиц и графиков; рассмотрены теоретические основы статистических вычислений, распределения 
случайных величин. Приведены формульные зависимости, используемые 
для расчета различных параметров и показателей. Все рассмотренные вопросы сопровождаются примерами решения технических и экономических 
задач с использованием методов статистики и теории вероятностей.
Учебное пособие предназначено для студентов, аспирантов, преподавателей и практических работников, занимающихся вопросами сбора, анализа и обработки статистической информации.
УДК 004.67(075.8) 
ББК 32.973.2я73
ISBN 978-5-906923-26-4
© 
Мхитарян В.С., Шишов В.Ф., 
Козлов А.Ю., 2018
© КУРС, 2018

ПРЕДИСЛОВИЕ
Анализ данных, опирающийся на множество подходов и алгоритмов, используется практически во всех областях науки, производства 
и жизнедеятельности общества. Он осуществляется с целью формирования определенных представлений о характере анализируемого 
явления с использованием методов теории вероятностей, статистики, эконометрики и других методов, опирающихся на вероятностно-статистические закономерности.
Все это предопределяет необходимость овладения инструментами 
статистического анализа и прогнозирования изучаемых явлений 
и процессов. При этом компьютерные программы для аналитических 
исследований и прогнозирования должны являться повседневным 
рабочим инструментом специалиста, связанного с обработкой статистической информации.
В настоящее время наиболее популярным является программное 
обеспечение, работающее в операционной системе Windows и поставляемое вместе с компьютером. Одним из составляющих этого 
обеспечения является программа Microsoft Excel, которая позволяет 
упорядочивать, обрабатывать, графически представлять и анализировать статистическую информацию, работать с таблицами статистических данных.
Основное преимущество этой программы в том, что она является 
неотъемлемой частью рабочего места специалиста и многие пользователи в той или иной степени знакомы с ней. Программа проста для 
обучения и применения, а графические и статистические функции 
программы Exсel оперируют с теми же рабочими листами, которые 
пользователи применяют для хранения данных. Кроме того, она активно развивается и только за последние пять лет число реализованных статистических функций значительно увеличилось.
Однако, используя программу Exсel для статистического анализа, 
пользователь должен хорошо знать возможности методов и условия 
их применения. Только глубокое понимание статистических методов, используемых для решения задачи, и ограничений, которые 
на  них налагаются, может привести к  корректным результатам 
и к адекватному ее решению.
Основная задача книги — ​
научить читателя успешно применять 
статистические методы с целью информационной поддержки процесса принятия оптимальных решений.
3

Для этого необходимо уметь:
	
• представлять статистические данные;
	
• делать выводы о генеральной совокупности по выборке из нее;
	
• прогнозировать тенденции развития изучаемого процесса и явления.
	
• делать выводы по результатам статистического анализа, направленным на совершенствование процесса управления.
В книге при решении прикладных задач подробно изложены вычислительные основы используемых статистических методов. Таким 
образом, решения задач, полученные с помощью программы Exсel, 
позволяют пользователям лучше разобраться как в применяемых 
статистических методах, так и в используемых вычислительных процедурах. Программа Microsoft Exсel 2016 предоставляет для этого 
превосходные возможности.
В текст книги включены теоретические сведения, содержащие 
краткие описания конкретных статистических процедур программы 
Microsoft Exсel 2016, и детальные инструкции, предназначенные 
как читателям, которые просто хотят использовать программу для 
расчетов, так и тем, кто хочет вникнуть в ее технические детали. 
Книга предоставляет студентам и преподавателям возможность 
гибко использовать программу MS Exсel в процессе обучения, так 
как в ней описаны инструментальные возможности этой программы и даются объяснения, как ими пользоваться при решении 
конкретных задач.
Программа MS Exсel содержит встроенные функции, использование которых значительно облегчает обработку статистической информации. В книге подробно рассмотрены все 109 статистических 
функций, распределенных на восемь разделов, которые позволяют 
выполнять следующие действия:
	
• проводить предварительную обработку статистических данных;
	
• рассчитывать характеристики положения и рассеивания;
	
• определять зависимость определяемых величин;
	
• проводить интервальное оценивание;
	
• определять параметры законов распределения непрерывных случайных величин; 
	
• проверять статистические гипотезы о параметрах распределения 
и виде закона распределения случайных величин;
	
• определять параметры законов распределения дискретных случайных величин;
	
• строить уравнения регрессии, проводить их анализ и давать прогнозные оценки результативного признака.
4

Подробно рассмотрен весь набор инструментов, входящий в Пакет анализа, предназначенный для решения сложных статистических 
и инженерных задач. С помощью Пакета анализа можно проводить 
следующие действия:
	
• генерировать случайные числа, подчиняющиеся различным законам распределения;
	
• формировать выборки из генеральной совокупности;
	
• по выборке строить интервальный вариационный ряд, гистограмму, кумулятивную кривую и диаграмму Парето;
	
• вычислять по выборке точечные и интервальные оценки параметров генеральной совокупности;
	
• проводить сглаживание временных рядов;
	
• оценивать зависимость случайных величин, входящих в систему;
	
• проверять статистические гипотезы с использованием различных 
критериев значимости;
	
• проводить однофакторный и двухфакторный дисперсионный 
анализ;
	
• строить множественное уравнение регрессии;
	
• ранжировать статистические данные;
	
• проводить прямое и обратное преобразование Фурье.
Для новичков, впервые встречающихся с MS Excel, в книге имеется раздел, посвященный основам работы в Excel. Он составлен 
таким образом, что за минимальное время можно овладеть основными практическими навыками работы с Excel и всем, что необходимо для работы с инструментами Пакета анализа и Статистическими функциями.
Так как результаты статистического наблюдения представляются 
в виде таблиц и графиков, то во второй главе даны основные сведения по построению таблиц и наиболее часто встречающихся графиков.
При рассмотрении статистических функций MS Excel, инструментов Пакета анализа подробно изложены теоретические основы 
вопроса исследования, представлены формульные зависимости, используемые для расчета различных параметров, приведены примеры, 
которые решены «вручную» и с помощью соответствующего инструмента Пакета анализа или статистической функции.
Такой подход дает возможность пользователю понять, каким образом решаются различные технические и экономические задачи с помощью инструментов Пакета анализа и статистических функций, как 
правильно анализировать полученные результаты, делать обоснованные выводы.
5

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

Глава 1
ОСНОВНЫЕ СВЕДЕНИЯ 
О MICROSOFT EXCEL
Excel относится к программным продуктам, которые известны под 
названием «электронные таблицы». Здесь рассмотрены сведения, необходимые при обработке статистических данных при проведении 
технических, экономических и статистических расчетов с помощью 
средств Microsoft Excel (далее по тексту — Excel). При этом в качестве 
базовой версии будем использовать русскую версию MS Excel 2016.
Электронные таблицы — ​
это интерактивная программа, состоящая 
из набора строк и столбцов, изображенных на экране в специальном 
окне. Область, находящаяся на пересечении строки и столбца, называется ячейкой. В ячейке могут находиться число, текст или формула, 
с помощью которой осуществляются вычисления, относящиеся к одной или нескольким ячейкам. Ячейки можно копировать, перемещать, а также изменять их содержимое. При изменении содержимого 
ячейки производится автоматический пересчет содержимого всех 
ячеек, использующих в формулах измененную ячейку. На основе 
групп ячеек создаются диаграммы, графики и сводные таблицы. 
Электронную таблицу можно сохранить в отдельном файле для дальнейшего использования.
Пользовательский интерфейс Excel 2016, как и интерфейс Excel 
2007/2010/2013, характеризуется полосой в верхней части рабочего 
листа, называемой лентой. На ней собраны чаще всего используемые 
команды.
Следует отметить, что новый и усовершенствованный интерфейс 
Excel 2016 содержит множество улучшений. Отдельного внимания 
заслуживает оперативный предварительный просмотр, позволяющий 
увидеть, как будут выглядеть текущие данные рабочего листа при 
использовании другого шрифта или форматирования, еще до того, 
как вы примените сами настройки. Возможности предварительного 
просмотра дополняют такие относительно новые команды, как 
Быстрый анализ, Рекомендуемые сводные таблицы и Рекомендуемые 
диаграммы. С их помощью можно выполнять предварительный 
просмотр данных в различных форматах.
7

Также Excel включает улучшенное представление разметки страницы, отображающее для каждой страницы линейки, колонтитулы 
и ползунок изменения масштаба. Путем перемещения ползунка масштаба можно увеличивать или уменьшать рабочие листы в режиме 
реального времени. И последнее, что следует отметить, — ​
это то, что 
программа Excel просто переполнена динамическими коллекциями, 
которые существенно упрощают создание и форматирование диаграмм, особенно в сочетании с оперативным предварительным просмотром.
Для начала работы необходимо запустить Excel. Это можно сделать несколькими способами.
Если вы работаете с операционной системой Windows 7 (или более ранними версиями), найдите на  Рабочем столе ярлык 
 
и дважды щелкните на нем левой кнопкой мыши или нажмите 
кнопку Пуск, выполните команду Все программы, откройте папку 
Microsoft Office 2016 и выберите Excel 2016.
В Windows 8 и 10 найдите на экране Пуск или плитку 
 панели 
Приложения и щелкните на ней кнопкой мыши. Аналогично можно 
поступить, если на Панели задач расположен значок 
.
1.1. Основные положения работы 
с электронной таблицей
После запуска Excel появится начальный экран, подобный показанному на рис. 1.1. На этом экране можно выбрать создание новой 
пустой книги, щелкнув на шаблоне Пустая книга, либо выбрать другой 
шаблон, на основе которого будет создана новая электронная таблица. 
Если вы уже успели поработать с Excel, то на начальном экране отобразится список недавно открывавшихся книг. Выберите одну 
из книг в этом списке, чтобы изменить ее либо вывести на печать.
Если на начальном экране Excel выбирается шаблон Пустая книга, 
то в программном окне (рис. 1.2) появится новая книга Книга1, состоящая из единственного рабочего листа с присвоенным именем 
Лист1.
Рассмотрим основные элементы рабочей области окна Excel.
Кнопка доступа к командам вкладки Файл. Нажав ее, откроется 
страница, в левой части которой находятся вкладки, включающие 
команды для работы с электронной таблицей в целом, в том числе 
Сведения, Создать, Открыть (выбирается по умолчанию после первого запуска Excel), Сохранить, Сохранить как, Печать, Общий доступ, 
Экспорт, Опубликовать и Закрыть (рис. 1.3).
8

Рис. 1.1. Начальный экран Excel
Помимо этого, в нижней части окна находятся настройки Учетная 
запись, обеспечивающая доступ к сведениям о пользователе и программе, и Параметры, позволяющая изменить многие настройки 
-
Excel, заданные по умолчанию. Чтобы выйти из этой страницы 
и вернуться к обычному представлению листа, щелкните на стрелочке в левом верхнем углу окна.
Панель быстрого доступа. Здесь находятся самые востребованные 
команды.
Строка заголовка. В ней отображаются названия документа и самой программы, разделенные знаком «тире». Например, Книга1 — 
​
Excel.
Кнопки управления окном. Позволяют свернуть окно, развернуть 
его на весь экран или восстановить до предыдущего размера, а также 
закрыть. Кроме того, с их помощью можно вызвать Справку и настроить ленту инструментов.
Строка формул. Служит для ввода и отображения содержимого 
ячеек.
Полосы прокрутки. Предназначены для перемещения по таблице.
9

Выделить весь лист
Поле имени
Название рабочей книги
Строка формул
Заголовки столбцов
Активная ячейка
Границы ячейки
Маркер автозаполнения Полоса вертикальной
прокрутки
Заголовки строк
Масштаб
Ярлычок активного
рабочего листа
Строка состояния Полоса
горизонтальной
прокрутки
Кнопки прокрутки ярлычков
Рис. 1.2. Программное окно Excel
Регулятор масштаба. Предназначен для изменения масштаба отображения.
Кнопки смены режима просмотра документа. Позволяют отобразить 
документ в том виде, который наиболее удобен для выполнения конкретной операции.
Строка состояния. Отображает вспомогательную информацию, 
например название текущей операции.
Лента инструментов. Один из важнейших элементов окна, который открывает доступ ко всем функциям Excel. Внешний вид ленты 
меняется в зависимости от настроек и размера монитора.
Поле задания имени объекта. В нем присваивают имена различным объектам: группам ячеек, рисункам, диаграммам и т. 
д. Кроме 
того, здесь отображается адрес текущей ячейки, поэтому его можно 
использовать для перехода по ячейкам таблицы.
Область управления листами. Содержит ярлыки листов, кнопки 
перехода между ними, а также кнопку создания нового листа.
10