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

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

Москва 
ВузоВский учебник
инФРА-М 
2017

учебное пособие

Рекомендовано УМО 
по образованию в области финансов,  
учета и мировой  экономики в качестве  учебного пособия  
для студентов, обучающихся по направлению «Экономика» 
(квалификация (степень) — «бакалавр»)

Я.л. ГоБарева, о.Ю. ГородецкаЯ,  
а.в. золотарЮк

Второе издание, исправленное и дополненное

Финансовый университет при Правительстве Российской Федерации

УДК 004.9(075.8)
ББК 32.973.26я73
 
Г57

Гобарева Я.Л., Городецкая О.Ю., Золотарюк А.В.
Бизнес-аналитика средствами Excel: учеб. пособие. — 2-е изд., испр. 
и доп. — М.: Вузовский учебник: ИНФРА-М, 2017. — 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 «Экономика», а также для магистрантов, аспирантов, преподавателей экономических дисциплин и профессионалов в различных 
экономических областях.

Г57

© Вузовский учебник,  
 
2013, 2014

А в т о р ы :
Я.Л. Гобарева, канд. экон. наук, доцент;
О.Ю. Городецкая, канд. экон. наук, доцент;
А.В. Золотарюк, канд. техн. наук, доцент

Материалы, отмеченные знаком 
, доступны  
в электронно-библиотечной системе znanium (www.znanium.com)

ISBN 978-5-9558-0390-6 (Вузовский учебник) 
ISBN 978-5-16-010235-1 (ИНФРА-М, print) 
ISBN 978-5-16-102121-7 (ИНФРА-М, online)

Р е ц е н з е н т ы :
С.В. Назаров, д-р техн. наук, профессор;
Е.Л. Шуремов, д-р экон. наук, профессор

УДК 004.9(075.8)
ББК 32.973.26я73

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

ВВедение

В современных рыночных условиях экономист-профессионал должен 

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

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

Целью настоящего издания учебного пособия является формирование 

компетенций, теоретических знаний, практических навыков и умений в 
использовании инструментальных средств актуальных версий  табличного 
процессора Excel для анализа и решения финансово-экономических задач. 
По сравнению с первым изданием, в котором раскрывались технологические возможности процессора Excel 2010 и отмечались аспекты работы в 
более ранних версиях, в данной книге в дополнение отражаются особенности новых версий процессора, входящих в состав пакетов Microsoft 
Office 2013 и Microsoft Office 365.

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

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

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

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

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

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

Глава 1.
ОснОВы рабОты 
В Microsoft ExcEl

1.1.
ОснОВные ВОзмОжнОсти табличнОГО
прОцессОра

1.1.1. назначение Ms Excel

Табличный процессор Microsoft Excel (© Microsoft Corpora
tion, 1985 — 2014) предназначен для решения многофункциональных 
задач по обработке разнотипной информации, представленной в табличной форме. Процессор обеспечивает:
• подготовку, редактирование, преобразование, распечатку и ис
пользование электронных таблиц, различных по форме, структуре, сложности, объему и схемам оформления;

• автоматизацию выполнения расчетов над числовыми данными, в 

том числе сложнейших вычислений с использованием многочисленных встроенных функций и инструментальных средств;

• анализ, поиск, сортировку, выборку и обработку числовых, текс
товых и иных данных по установленным, легко изменяемым критериям;

• решение математических, оптимизационных, статистических и 

иных задач;

• проведение финансово-экономических расчетов, включая задачи 

планирования, учета, контроля, анализа и регулирования бизнеспроцессов; 

• выполнение моделирования многочисленных вариантов и ситуа
ций при решении параметрических и многокритериальных задач;

• формирование и вывод полученных результатов в виде консоли
дированной отчетности, сводных таблиц, графиков и диаграмм 
различных типов;

• интеграцию с облачными сервисами: взаимодействие с Web-ре
сурсами (YouTube и др.), облачное хранение и обработка контента (через учетную запись OneDrive и др.) — для версий 2013 и 365.
Обработка информации в среде табличного процессора осущест
вляется в электронной таблице большой размерности. В клетки электронной таблицы записываются исходные данные прикладной задачи, а также формульные зависимости, обеспечивающие вычисление 
результатов. 

Результаты решения формируются на месте записи формул. По 

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

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

Текстовая часть документов 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

пиктограмма 

файла
Формат сохранения
Версии  

сохранения

расширение 
имени файла

Книга (лист) MS Excel на основе языка XML без сохранения 
кодов макросов для приложений VBA

2007–2013 
.xlsx 

Книга (лист) MS Excel на основе языка XML с поддержкой 
макросов VBA

2007–2013
.xlsm

Книга (лист) MS Excel в двоичном формате

1997–2003
.xls

1
Документы Excel, сохраненные в форматах версий 2007–2013, не могут быть 
открыты в среде Excel более ранних версий без использования специальных 
программ-конверторов. 

2
В настройках Excel можно изменить число листов новой книги, установленное по умолчанию, выполнив команды:

Команда Файл → Параметры → Вкладка Общие →

Указать число листов при создании новой книги: от 1 до 255

Пользователь имеет возможность добавить в существующую книгу значительное число листов сверх 255, по крайней мере до 65 535 (216–1).

Шаблон MS Excel без поддержки макросов VBA

2007–2013 
.xltx

Шаблон MS Excel в двоичном 
формате

1997–2003 
.xlt

Шаблон MS Excel с под- 
держкой макросов

2007–2013
.xltm

Надстройка MS Excel на основе 
XML с поддержкой макросов

2007–2013
.xlam

Надстройка MS Excel с под- 
держкой проектов VBA, предназначенная для выполнения 
дополнительного кода 

1997–2003
.xla

Web-страница в одном файле
2007–2013
.mht

Web-страница (+папка объектов)

2007–2013
.htm
.html

Документ PDF (Adobe Reader)
2007–2013
.pdf

Документ электронной таблицы Open Document

2007–2013
.ods

Столбцы электронного листа именуются латинскими буквами (от 

A до XFD), строки — цифрами1. Это позволяет адресовать ячейки. 

1
Книга Excel в версиях 2003 и более ранних имела ограничения: 255 листов, 
каждый лист состоит из 256 столбцов (от А до IV) и 65 536 строк. Кроме того, 
инструментальные возможности предшествующих версий более скромные. 

Окончание табл. 1.1

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

Например, левая верхняя ячейка листа имеет адрес А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 и др.) любую из панелей 
инструментов можно было вызвать посредством меню команды Сервис; наиболее часто используемые — через меню команды Вид; ряд панелей появлялись на экране автоматически в зависимости от ситуации обработки данных (например, Сводные таблицы, Диаграммы).

Ниже перечня команд интерфейсной ленты Office располагаются 

инструментальные кнопки активной вкладки (в случае, если лента не 
свернута). 

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

Функциональные возможности вкладок интерфейсной ленты 

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

Рис. 1.2. Вариант панели быстрого доступа с раскрытым 

списком ее настройки