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

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

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

Структура этой книги

Книга «Анализ данных в Excel: наглядный курс создания отчетов, диаграмм и сводных таблиц» состоит из 11 глав и пяти приложений.

• Первая глава, «Начало работы в Excel», знакомит вас с разными способами анализа данных. Вы узнаете, как выделять, диапазон ячеек, использовать именованные диапазоны, присваивать имена диапазонам и стилям, а также создавать пользовательские шаблоны.
• Глава 2, «Организация данных в рабочих листах», научит вас создавать рабочие листы Excel. Из этой главы вы узнаете, как создавать, добавлять, сортировать, специальным образом обрабатывать, а также объединять ваши данные.
• Глава 3, «Обработка данных», расскажет о принципах применения условного форматирования и фильтров. Кроме того, в ней приведены упражнения по оценке данных: и созданию сценариев.
• В четвертой главе, «Создание формул», с помощью диалогового окна вставки функций строятся простые и сложные формулы. Вы также узнаете об использовании надстройки Поиск решения и создании условной формулы.
• В главе 5 изложена полная информация о внешних данных, в частности, о способах их копирования и импортирования, а также о работе с запросами к базам данных и с функцией ДСРЗНАЧ.
• В главах с 6 по 8 показано, каким образом можно улучшить данные с помощью диаграмм, сводных таблиц и сводных диаграмм.
• В главе 9 вы узнаете, как создавать макросы с помощью VBA.
• В главе 10 рассказывается о формах и об их элементах управления, а в главе 11 — о многих надстройках, интегрируемых в программу Excel. В пяти приложениях представлено следующее: список комбинаций клавиш, справочник по функциям Excel, а также краткий справочник по VBA и раздел, посвященный формулам (он предназначен для тех, кто с Excel еще не знаком).