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

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

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

УчеБное посоБие

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

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

а.в. золотарЮк

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

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

Гобарева Я.Л., Городецкая О.Ю., Золотарюк А.В.
Бизнес-аналитика средствами Excel: Учеб. пособие. — М.: 
Вузовский учебник: ИНФРА-М, 2014. — 336 с. + Доп. материалы 
[Электронный ресурс; Режим доступа http://www.znanium.com].

ISBN 978-5-9558-0282-4 (Вузовский учебник) 
ISBN 978-5-16-006229-7 (ИНФРА-М)

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

ББК 32.973.26я73

Г57

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

А в т о р ы :
Гобарева Я.Л., канд. экон. наук, доцент кафедры «Информационные 
технологии» Финансового университета при Правительстве Российской Федерации; 
Городецкая О.Ю., канд. экон. наук, доцент кафедры «Информационные 
технологии» Финансового университета при Правительстве Российской Федерации; 
Золотарюк А.В., канд. техн. наук, доцент, профессор кафедры «Информационные технологии» Финансового университета при Правительстве Российской Федерации

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

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

ISBN 978-5-9558-0282-4 (Вузовский учебник) 
ISBN 978-5-16-006229-7 (ИНФРА-М)

Посвящается  
95-летию Финансового университета

Кафедре информационных технологий — 50 лет!

ВВедение

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

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

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

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

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

выполняется пересчет результатов электронной таблицы, изменяется вид построенных графиков и диаграмм.
Текстовая часть документов Excel готовится с использованием 
инструментальных средств, аналогичных имеющимся в текстовом 
процессоре Microsoft Word. 
Созданные в Excel документы могут быть сохранены в различных 
форматах, что обеспечивает совместимость при работе в среде предшествующих версий пакета1 либо при дальнейшей обработке документов другими программами (табл. 1.1).

1.1.2. Электронная книга Excel

После загрузки табличного процессора Excel 2010 пользователю 
для обработки данных предоставляется книга (рис. 1.1). По умолчанию она состоит из трех электронных листов2. Каждый лист электронной книги — совокупность клеток (ячеек), образованных 
16 384 столбцами (214) и 1 048 576 строками (220). 

Т а б л и ц а  1 . 1

Некоторые возможности сохранения документов MS Excel 2010

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

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

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

2007–2010 
.xlsx 

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

2007–2010
.xlsm

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

1
Документы Excel, сохраненные в формате 2007–2010, не могут быть открыты 
в среде Excel более ранних версий без использования специальных программконверторов. 
2
В настройках Excel можно изменить число листов новой книги, установленное по умолчанию, выполнив команды:

Команда Файл → Параметры → Вкладка Общие → 
Указать число листов при создании новой книги: от 1 до 255

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

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

Шаблон MS Excel в двоичном 
формате
1997–2003 
.xlt

Шаблон MS Excel с под- 
держкой макросов
2007–2010
.xltm

Надстройка MS Excel на основе 
XML с поддержкой макросов
2007–2010
.xlam

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

1997–2003
.xla

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

Web-страница (+папка объектов)
2007–2010
.htm
.html

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

Документ электронной таблицы Open Document
2007–2010
.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.

1. панель быстрого доступа
Содержит набор команд, требующих постоянного доступа вне 
зависимости от вкладки, отображаемой в данный момент на ленте 
Office. По умолчанию располагается в левой верхней части окна и 
включает кнопки Сохранить, Отменить и Вернуть, а также список 
настройки, используемый для добавления кнопок на данную панель 
и изменения места ее расположения (рис. 1.2).

2. система меню — вкладки интерфейсной ленты office
По умолчанию (см. рис. 1.1) включает команды: Файл, Главная, 
Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид, 
Надстройки. С каждой из команд связана совокупность инструментальных кнопок, разделенных на группы. 
Имеется возможность перенастройки системы команд. 

1
В предшествующих версиях MS Excel (2003, XP и др.) любую из панелей 
инструментов можно было вызвать посредством меню команды Сервис; наиболее часто используемые — через меню команды Вид; ряд панелей появлялись на экране автоматически в зависимости от ситуации обработки данных (например, Сводные таблицы, Диаграммы).

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

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