Бизнес-аналитика средствами Excel

Я.Л. ГОБАРЕВА, О.Ю. ГОРОДЕЦКАЯ, 
А.В. ЗОЛОТАРЮК
БИЗНЕС-АНАЛИТИКА
СРЕДСТВАМИ
EXCEL
УЧЕБНОЕ ПОСОБИЕ
Второе издание, исправленное и дополненное
Рекомендовано УМО
по образованию в области финансов, 
учета и мировой  экономики в качестве  учебного пособия 
для студентов, обучающихся по направлению «Экономика»
(квалификация (степень) — «бакалавр»)
Москва
ВУЗОВСКИЙ УЧЕБНИК
ИНФРА-М
2018

УДК 004.9(075.8)
ББК 32.973.26я73
 
Г57
А в т о р ы :
Я.Л. Гобарева, канд. экон. наук, доцент;
О.Ю. Городецкая, канд. экон. наук, доцент;
А.В. Золотарюк, канд. техн. наук, доцент
Р е ц е н з е н т ы :
С.В. Назаров, д-р техн. наук, профессор;
Е.Л. Шуремов, д-р экон. наук, профессор
Г57
Гобарева Я.Л., Городецкая О.Ю., Золотарюк А.В.
Бизнес-аналитика средствами Excel: учеб. пособие. — 2-е изд., испр. 
и доп. — М.: Вузовский учебник: ИНФРА-М, 2018. — 336 с. + Доп. материалы [Электронный ресурс; Режим доступа http://www.znanium.com].
ISBN 978-5-9558-0390-6 (Вузовский учебник)
ISBN 978-5-16-010235-1 (ИНФРА-М, print)
ISBN 978-5-16-102121-7 (ИНФРА-М, online)
Подробно излагаются возможности актуальных версий табличного 
процессора Microsoft Excel. Детально, с использованием многочисленных 
примеров, рисунков, таблиц, схем и диаграмм описывается технология 
анализа и решения финансово-экономических задач. Приведены варианты лабораторных и контрольных работ и самостоя 
тельных заданий для 
закрепления рассматриваемого материала.
Для студентов академического и прикладного бакалавриата направления 38.03.01 «Экономика», а также для магистрантов, аспирантов, преподавателей экономических дисциплин и профессионалов в различных 
экономических областях.
УДК 004.9(075.8)
ББК 32.973.26я73
Материалы, отмеченные знаком 
, доступны 
в электронно-библиотечной системе Znanium.com
© Вузовский учебник, 
 
2013, 2014
ISBN 978-5-9558-0390-6 (Вузовский учебник)
ISBN 978-5-16-010235-1 (ИНФРА-М, print)
ISBN 978-5-16-102121-7 (ИНФРА-М, online)

Введение
В современных рыночных условиях экономист-профессионал должен 
уверенно использовать программные комплексы и системы не только в 
качестве инструмента, облегчающего и ускоряющего процесс решения 
прикладных задач, но и как средство анализа и исследования предметной 
области с целью получения объективной оценки финансово-экономической деятельности, системно учитывающей множество факторов проведения достоверного прогнозирования и планирования, а также принятия на 
их основе научно-обоснованного решения, способствующего росту финансового благополучия и дальнейшему развитию бизнеса.
Одним из таких программно-инструментальных средств, которое может быть применено при решении широкого класса задач финансовоэкономического характера, доступным практически любому пользователю, является табличный процессор Microsoft Excel. Его используют экономисты, финансисты, кредитные работники, сотрудники страховых 
компаний и налоговых ведомств, представители малого бизнеса и др., 
поскольку множество решаемых ими задач имеет учетно-аналитический 
характер и требует табличной компоновки данных с последующим их 
анализом, сортировкой, группировкой, подведением итогов, построением диаграмм и графиков.
Целью настоящего издания учебного пособия является формирование 
компетенций, теоретических знаний, практических навыков и умений в 
использовании инструментальных средств актуальных версий  табличного 
процессора Excel для анализа и решения финансово-экономических задач. 
По сравнению с первым изданием, в котором раскрывались технологические возможности процессора Excel 2010 и отмечались аспекты работы в 
более ранних версиях, в данной книге в дополнение отражаются особенности новых версий процессора, входящих в состав пакетов Microsoft 
Office 2013 и Microsoft Office 365.
Учебное пособие подготовлено на основе многолетнего педагогического опыта преподавания авторов в Финансовом университете при Правительстве Российской Федерации на кафедре «Информационные технологии» и состоит из введения, пяти глав и приложений. Изложение материала иллюстрируется примерами, схемами, таблицами и рисунками. 
В первой главе рассмотрены основы работы в Microsoft Excel. Во второй главе описана технология обработки и анализа экономической информации, связанная с графическим представлением данных, работой 
со списками и базами данных, фильтрацией, формированием итогов и 
проведением анализа. Третья глава включает обзор категорий встроенных 
функций Excel и содержит многочисленные примеры их использования 
в бизнес-аналитике. Четвертая глава посвящена рассмотрению технологии решения экономических задач с применением финансовых функций 
3

Excel, в частности, для расчетов по кредитам и займам, прогнозирования 
возвратности кредитов, оценки и анализа ценных бумаг, расчета амортизационных отчислений и т.п. По каждому пункту главы приведены задачи для самостоятельного решения, позволяющие освоить методику финансового анализа и закрепить полученные навыки финансово-аналитических расчетов. Пятая глава, представленная в электронном формате, 
содержит 16 комплексных лабораторных работ для самостоятельного 
выполнения студентами под руководством преподавателя, в том числе 
междисциплинарные задания, которых не было в первом издании, а также 30 вариантов контрольных работ для обобщенной, интегрированной 
оценки компетенций, знаний, навыков и умений студентов по итогам 
изучения бизнес-аналитических возможностей процессора Excel. Приложения включают информационно-справочные материалы.
Учебное пособие подготовлено в соответствии с требованиями Федерального государственного образовательного стандарта высшего профессионального образования по направлению подготовки бакалавров 
080100 «Экономика» (направлению подготовки академических и прикладных бакалавров 38.03.01 «Экономика»). Материал пособия ориентирован на содержание рабочих программ учебных дисциплин «Эко-номическая информатика» и «Профессиональные компьютерные про-граммы», входящих в вариативную часть математического цикла основных 
образовательных программ по профилям «Финансы и кредит», «Бухгалтерский учет, анализ и аудит», «Налоги и налогообложение», «Мировая 
экономика», «Анализ и управление рисками» («Банковское дело и рынок 
ценных бумаг», «Анализ рисков и экономическая безопасность», «Страхование», «Г
осударственные и муниципальные финансы», «Корпоративные финансы», «Мировая экономика и международный бизнес», «Международные валютно-кредитные и финансовые отношения», «Международные финансы», «Налоги и налогообложение», «Бухгалтерский учет, 
анализ и аудит»).
Содержание пособия расширено и может быть применено при изучении соответствующих информационных дисциплин для бакалавров 
других направлений и профилей.
Материал учебного пособия не накладывает ограничений на предварительную подготовку читателей и может быть одинаково полезным 
как для студентов бакалавриата и магистратуры, так и для аспирантов, 
преподавателей экономического блока дисциплин, а также профессионалов в различных финансово-экономических областях. 
4

Глава 1.	
Основы работы 
в Microsoft Excel
1.1.	
Основные возможности табличного 
процессора
1.1.1.	 Назначение MS Excel
Табличный процессор Microsoft Excel (©  Microsoft Corporation, 1985 — 2014) предназначен для решения многофункциональных 
задач по обработке разнотипной информации, представленной в табличной форме. Процессор обеспечивает:
•	 подготовку, редактирование, преобразование, распечатку и использование электронных таблиц, различных по форме, структуре, сложности, объему и схемам оформления;
•	 автоматизацию выполнения расчетов над числовыми данными, в 
том числе сложнейших вычислений с использованием многочисленных встроенных функций и инструментальных средств;
•	 анализ, поиск, сортировку, выборку и обработку числовых, текстовых и иных данных по установленным, легко изменяемым критериям;
•	 решение математических, оптимизационных, статистических и 
иных задач;
•	 проведение финансово-экономических расчетов, включая задачи 
планирования, учета, контроля, анализа и регулирования бизнеспроцессов; 
•	 выполнение моделирования многочисленных вариантов и ситуаций при решении параметрических и многокритериальных задач;
•	 формирование и вывод полученных результатов в виде консолидированной отчетности, сводных таблиц, графиков и диаграмм 
различных типов;
•	 интеграцию с облачными сервисами: взаимодействие с Web-ресурсами (YouTube и др.), облачное хранение и обработка контента (через учетную запись OneDrive и др.) — для версий 2013 и 365.
Обработка информации в среде табличного процессора осуществляется в электронной таблице большой размерности. В клетки электронной таблицы записываются исходные данные прикладной задачи, а также формульные зависимости, обеспечивающие вычисление 
результатов. 
Результаты решения формируются на месте записи формул. По 
желанию они могут быть проиллюстрированы графиками, диаграммами, отчетами сводных таблиц, вставленными рисунками, схемами 
5

и пояснениями. При изменении исходных данных автоматически 
выполняется пересчет результатов электронной таблицы, изменяется вид построенных графиков и диаграмм.
Текстовая часть документов Excel готовится с использованием 
инструментальных средств, аналогичных имеющимся в текстовом 
процессоре Microsoft Word. 
Созданные в Excel документы могут быть сохранены в различных 
форматах, что обеспечивает совместимость при работе в среде предшествующих версий пакета1 либо при дальнейшей обработке документов другими программами (табл. 1.1).
1.1.2.	 Электронная книга Excel
После загрузки табличного процессора Excel пользователю для 
обработки данных предоставляется книга (рис. 1.1). По умолчанию 
она состоит из трех электронных листов2. Каждый лист электронной 
книги — совокупность клеток (ячеек), образованных 16 384 столбцами (214) и 1 048 576 строками (220). 
Т а б л и ц а  1 . 1
Некоторые возможности сохранения документов MS Excel 2010
Расширение 
Пиктограмма 
сохранения
имени файла
файла
Формат сохранения
Версии  
2007–2013 
.xlsx 
Книга (лист) MS Excel на основе языка XML без сохранения 
кодов макросов для приложений VBA
2007–2013
.xlsm
Книга (лист) MS Excel на основе языка XML с поддержкой 
макросов VBA
1997–2003
.xls
Книга (лист) MS Excel в двоичном формате
1	
Документы Excel, сохраненные в форматах версий 2007–2013, не могут быть 
открыты в среде Excel более ранних версий без использования специальных 
программ-конверторов. 
2	
В настройках Excel можно изменить число листов новой книги, установленное по умолчанию, выполнив команды:
Команда Файл → Параметры → Вкладка Общие → 
Указать число листов при создании новой книги: от 1 до 255
	
Пользователь имеет возможность добавить в существующую книгу значительное число листов сверх 255, по крайней мере до 65 535 (216–1). 
6

Окончание табл. 1.1
2007–2013 
.xltx
Шаблон MS Excel без поддержки макросов VBA
1997–2003 
.xlt
Шаблон MS Excel в двоичном 
формате
2007–2013
.xltm
Шаблон MS Excel с под- 
держкой макросов
2007–2013
.xlam
Надстройка MS Excel на основе 
XML с поддержкой макросов
1997–2003
.xla
Надстройка MS Excel с под- 
держкой проектов VBA, предназначенная для выполнения 
дополнительного кода 
Web-страница в одном файле
2007–2013
.mht
2007–2013
.htm
Web-страница (+папка объектов)
.html
Документ PDF (Adobe Reader)
2007–2013
.pdf
2007–2013
.ods
Документ электронной таблицы Open Document
Столбцы электронного листа именуются латинскими буквами (от 
A до XFD), строки — цифрами1. Это позволяет адресовать ячейки. 
1	
Книга Excel в версиях 2003 и более ранних имела ограничения: 255 листов, 
каждый лист состоит из 256 столбцов (от А до IV) и 65 536 строк. Кроме того, 
инструментальные возможности предшествующих версий более скромные. 
7

Рис. 1.1. Элементы окна табличного процессора Excel (на примере версии  2010)
8

Например, левая верхняя ячейка листа имеет адрес А1, правая нижняя ячейка — адрес XFD1048576. 
Одна из ячеек листа — активная. В ней находится табличный курсор. Нажатие алфавитно-цифровых клавиш на клавиатуре приводит 
к появлению данных в этой ячейке.
Обработка данных в книге осуществляется с применением команд 
системы меню Excel и совокупности инструментальных кнопок, расположенных на интерфейсной ленте Office, которая, начиная с версии пакета 2007 г., заменила возможности более чем 30 Панелей инструментов (Стандартной, Форматирования, Рисования и др.1).
Для введенных в ячейку данных можно задать различные параметры шрифта, указать необходимые атрибуты выравнивания информации, установить особенности графического отображения числовых данных. Можно выполнить заливку ячеек, установить их границы, задать фон листа и т.п. Отдельные строки и столбцы можно 
скрыть, сделав их невидимыми.
1.1.3.	 Интерфейсная лента Office
Интерфейсная лента Office призвана предоставить пользователям 
простые, удобные и наглядные средства для работы в среде пакета. 
Рассмотрим элементы ленты Office для MS Excel 2010. Ленты версий 
2007, 2013, 365 отличаются в основном дизайнерским оформлением.
1.	 Панель быстрого доступа
Содержит набор команд, требующих постоянного доступа вне 
зависимости от вкладки, отображаемой в данный момент на ленте 
Office. По умолчанию располагается в левой верхней части окна и 
включает кнопки Сохранить, Отменить и Вернуть, а также список 
настройки, используемый для добавления кнопок на данную панель 
и изменения места ее расположения (рис. 1.2).
2.	 Система меню — вкладки интерфейсной ленты Office
По умолчанию (см. рис. 1.1) включает команды: Файл, Главная, 
Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид, 
Надстройки. С каждой из команд связана совокупность инструментальных кнопок, разделенных на группы. 
Имеется возможность перенастройки системы команд. 
1	
В предшествующих версиях MS Excel (2003, XP и др.) любую из панелей 
инструментов можно было вызвать посредством меню команды Сервис; наиболее часто используемые — через меню команды Вид; ряд панелей появлялись на экране автоматически в зависимости от ситуации обработки данных (например, Сводные таблицы, Диаграммы).
9

Рис. 1.2. Вариант панели быстрого доступа с раскрытым 
списком ее настройки
Ниже перечня команд интерфейсной ленты Office располагаются 
инструментальные кнопки активной вкладки (в случае, если лента не 
свернута). 
Кнопки сведены в группы по функциональному признаку. С некоторыми из них связаны списки, позволяющие уточнить функцию 
обработки. Для некоторых групп инструментальных кнопок имеется 
возможность вызова диалогового окна, облегчающего действия пользователя. Для дополнительного удобства с каждой инструментальной 
кнопкой связана краткая информация о ее предназначении и возможностях использования. Подсказка автоматически всплывает при 
подведении к пиктограмме кнопки курсора мыши. 
Функциональные возможности вкладок интерфейсной ленты 
 
Office, назначение инструментальных кнопок и других ее элементов 
рассматриваются в последующих пунктах. Технология использования инструментария Excel при решении прикладных задач изложена 
в следующих главах.
10