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

БИЗНЕС-АНАЛИТИКА

СРЕДСТВАМИ

EXCEL

Москва

ВУЗОВСКИЙ УЧЕБНИК

ИНФРА-М

202УЧЕБНОЕ ПОСОБИЕ

Рекомендовано

в качестве учебного пособия для студентов, 

обучающихся по направлению подготовки 38.03.01 «Экономика»

(квалификация (степень) «бакалавр»)

Я.Л. ГОБАРЕВА, О.Ю. ГОРОДЕЦКАЯ, 

А.В. ЗОЛОТАРЮК

3-е издание, переработанное и дополненное

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

Гобарева Я.Л.

Бизнес-аналитика средствами Excel : учебное пособие / Я.Л. Го-

барева, О.Ю. Городецкая, А.В. Золотарюк. — 3-е изд., перераб. 
и доп. — Москва : Вузовский учебник : ИНФРА-М, 2023. — 350 с. + 
Доп. материалы [Электронный ресурс].

ISBN 978-5-9558-0560-3 (Вузовский учебник)
ISBN 978-5-16-012770-5 (ИНФРА-М, print)
ISBN 978-5-16-103577-1 (ИНФРА-М, online)

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

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

Соответствует требованиям Федерального государственного образовательно-

го стандарта высшего образования последнего поколения.

Для студентов академического и прикладного бакалавриата направления подго-

товки 38.03.01 «Экономика», а также для магистрантов, аспирантов, преподавателей 
экономических дисциплин и профессионалов в различных экономических областях.

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

Г57

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

2017, 2018, с изменениями

А в т о р ы:

Гобарева Я.Л., канд. экон. наук, доцент, доцент Департамента анализа 

данных, принятия решений и финансовых технологий Финансового уни-
верситета при Правительстве Российской Федерации;

Городецкая О.Ю., канд. экон. наук, доцент, доцент Департамента ана-

лиза данных, принятия решений и финансовых технологий Финансового 
университета при Правительстве Российской Федерации;

Золотарюк А.В., канд. техн. наук, доцент, доцент Департамента ана-

лиза данных, принятия решений и финансовых технологий Финансового 
университета при Правительстве Российской Федерации

Р е ц е н з е н т ы:

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

ISBN 978-5-9558-0560-3 (Вузовский учебник)
ISBN 978-5-16-012770-5 (ИНФРА-М, print)
ISBN 978-5-16-103577-1 (ИНФРА-М, online)

Материалы, отмеченные знаком 
, доступны 

в электронно-библиотечной системе Znanium

ВВЕДЕНИЕ

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

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

Глава 1. 
ОСНОВЫ РАБОТЫ 
В MICROSOFT EXCEL

1.1. 
ОСНОВНЫЕ ВОЗМОЖНОСТИ ТАБЛИЧНОГО 
ПРОЦЕССОРА

1.1.1. Назначение MS Excel

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

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

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

После загрузки табличного процессора Excel пользователю для обработки 
данных предоставляется книга. По умолчанию, в зависимости 
от версии процессора, она состоит из одного или трех электронных 
листов. Каждый лист электронной книги — совокупность клеток (ячеек), 
образованных 16 384 столбцами (214) и 1 048 576 строками (220).
Число электронных листов в книге пользователем может быть 
изменено: установлением значения по умолчанию – от 1 до 255 с ис-
пользованием цепочки операций Файл → Параметры → Вкладка 
Общие → Число листов новой книги по умолчанию, либо добавлением 
листов вручную – до значения 65 535 (216–1) и более, которое огра-
ничивается только объемом доступной оперативной памяти.
Столбцы электронного листа именуются латинскими буквами 
(от A до XFD), строки — цифрами. Это позволяет адресовать ячейки. 
Например, левая верхняя ячейка листа имеет адрес А1, правая ниж-
няя ячейка — адрес XFD1048576.
Одна из ячеек листа — активная. В ней находится табличный кур-
сор. Нажатие алфавитно-цифровых клавиш на клавиатуре приводит 
к появлению данных в этой ячейке.

1.1.3. Интерфейсная лента Office

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

к данной книге, доступных через электронно-библиотечную систему 
Znanium.com.
Ниже интерфейсной ленты Office, непосредственно перед рабо-
чей областью окна Excel, располагается строка формул.
В левой части строки отражается адрес активной ячейки (той, 
в которой находится табличный курсор, определяющий место рабо-
чей области окна Excel, куда будут вводиться данные) или присвоен-
ное пользователем имя выделенного диапазона ячеек либо отдельной 
ячейки. Правее располагается небольшая кнопка fx вставки в актив-
ную ячейку формулы с использованием встроенных функций (при 
вводе формул левее этой кнопки появляются еще несколько неболь-
ших кнопок, реализующих отмену и завершение ввода данной опе-
рации).
В правом широком поле строки формул отражается содержимое 
активной ячейки — набранное числовое либо тестовое значение или 
сама формула. Отсюда, собственно, и название строки формул, по-
скольку в ячейках, как правило, отображаются только результаты 
вычисления формул, а для просмотра и редактирования их, исправ-
ления ошибок используется строка формул.

1.2. 
СТРУКТУРЫ ДАННЫХ EXCEL И ИХ ОБРАБОТКА

1.2.1. Диапазоны ячеек

В Excel допускается работа не только с данными, введенными 
в отдельные ячейки, но и с массивами данных, именуемых диапазо-
нами. 
В зависимости от расположения ячеек на электронном листе раз-
личают смежные и несмежные диапазоны. Если ячейки, входящие 
в диапазон, размещаются в аналогичном месте на нескольких листах, 
то его именуют трехмерным диапазоном. 
На листе диапазоны ячеек выделяются при нажатой левой кноп-
ке мыши (или при удерживании клавиши [Shift] и нажатии на клави-
ши перемещения курсора). Для выбора несмежных диапазонов до-
полнительно удерживают клавишу [Ctrl]. Для выделения трехмерно-
го диапазона предварительно следует выделить несколько листов 
книги, что можно сделать, выполнив щелчки левой кнопкой мыши 
по ярлычкам листов при удерживаемой клавише [Ctrl].
При записи адресов диапазонов ячеек указывают адрес левой 
верхней ячейки и адрес правой нижней ячейки, разделяя их симво-
лом : (двоеточие). Группы несмежных ячеек разделяют точкой с за-
пятой. Например, адрес диапазона, включающего все ячейки ли-
ста, — А1:XFD1048576. 

На рис. 1.1 показан несмежный диапазон A2:B6; D3:E4, в котором 
клетка Е3 является активной1.

Рис. 1.1. Несмежный диапазон ячеек

При выделении диапазона ячеек данные могут вводиться (с кла-
виатуры, как результат вычислений или копирования) не только 
лишь в активную клетку, но и во все клетки сразу выделенного смеж-
ного диапазона, в том числе трехмерного. 
Только в активную клетку диапазона данные заносятся, если 
окончание ввода фиксируется нажатием клавиши [Enter] или иным 
путем перемещения табличного курсора. 
Во все клетки сразу выделенного диапазона ячеек данные поме-
щаются, если окончание ввода задать комбинацией клавиш 
[Ctrl+Shift+Enter]. При этом, если осуществляется попытка занести 
таким образом данные в несмежный диапазон ячеек, будет заполне-
на только та его часть, в которой одна из клеток является активной.
Для любых диапазонов, в том числе несмежных, плоских или 
трехмерных, можно за один прием задать любые операции формати-
рования (изменение параметров шрифта, выравнивание, обрамление 
и заливку ячеек и т.п.).

1.2.2. Типы данных

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

1 
Обратите внимание: при указании адреса несмежные ячейки разделены сим-
волом «;» (точка с запятой). 

Текстовые значения, после их ввода в пустую и неформатирован-
ную ячейку, автоматически выравниваются по ее левой границе. 
Максимальная длина текста, который может быть введен в одну 
ячейку — 32 767 (215-1) символов, что приблизительно соответствует 
объему 12 страниц машинописного текста. Длинный текст, введен-
ный в ячейку, как бы распространяется на свободные ячейки, нахо-
дящиеся справа. Если такие ячейки заполнены, текст на экране «об-
резается». Чтобы он был виден полностью, нужно изменить ширину 
столбца или, используя команду Формат ячеек из меню правой кноп-
ки мыши, в окне на вкладке Выравнивание задать новый формат ото-
бражения информации (например, указав необходимость переноса 
слов ячейке таблицы). 
Несмотря на возможность ввода в ячейки длинного текста, 
не следует без особой необходимости помещать в одну клетку дан-
ные, которые можно расположить в нескольких соседних ячейках.
Числовой тип подразумевает представление и ввод в ячейки дан-
ных следующих форматов:
• целых чисел: 5; -32767; 2011; 
• вещественных чисел с фиксированным числом цифр в дробной 
части: 3,67803; -234,09; 5,04;
• вещественных чисел в экспоненциальной форме с плавающей 
запятой1: 2,09e4; -24,6е-5; 7,02е+22;
• чисел в виде обыкновенных дробей: 2 3/7; 0 5/11; 7 3/4 (целая 
часть от дробной отделяется одним пробелом);
• процентов: 95,06%; -2,01%; 124,23% (пробел между числом и зна-
ком % не допускается);
• денежных сумм: 720р.; $12,98; 325€.
Числовые данные без проблем могут быть преобразованы из од-
ного числового формата в другой. При задании общего числового 
формата вещественные числа с фиксированным числом цифр 
в дробной части могут отображаться в экспоненциальной форме. 
Значения типа даты и времени имеют следующие основные фор-
маты:
• даты2: 29.01.2020; 1/02/11; 18-04-2001;
• время3: 12:24; 23:12:02; 9:03.

1 
Приведенные значения соответствуют числам: 2,09 × 104; −24,6 × 10−5; 
7,02 × 1022.
2 
В стандартных настройках параметров Excel в качестве начальной даты при-
нимается 1 января 1900 г. (при преобразовании значения в числовой формат 
этой дате соответствует число 1), в качестве конечной даты — 31 декабря 
9999 г. (число 2 958 465). Для дат в диапазоне от 01.01.1930 до 31.12.2029 мож-
но указывать только 2 последние цифры года. 
 
При использовании системы дат 1904 (см.: Файл → Параметры → Дополни-
тельно → Флажок Использовать систему дат 1904) числу 0 соответствует 
1 января 1904 г., числу 1 — 2 января 1904 г. и т.д. 
3 
При преобразовании времени в числовой формат получается дробное число; 
например, времени 2 часа 24 минуты соответствует число 0,1.

Если в настройках операционной системы предусмотрено исполь-
зование американского стандарта1, в датах последовательно указы-
ваются месяц, день и год; время суток приводится в 12-часовом ди-
апазоне — до обеда (7:28a) и после обеда (3:12:54p); в числовых дан-
ных дробная часть от целой отделяется точкой, а не запятой; 
денежной единицей является доллар, а не рубль. По умолчанию 
Windows настраивается на параметры, действующие в стране, где 
происходит ее установка.
При невозможности отображения многоразрядных чисел в види-
мом размере ячейки или при задании отрицательных дат (такое мо-
жет случиться при ошибке в вычислениях разности дат) на экране 
появляются знаки ##. В первом случае следует увеличить ширину 
столбца либо уменьшить шрифт; во втором случае — обеспечить по-
ложительные значения дат.
Неправильно заданные обыкновенные дроби могут быть воспри-
няты как даты или текст. 
Отображение числовых данных в ячейке не всегда соответствует 
конкретному значению числа, оно на экране, например, может быть 
округлено. 
Данные числового типа могут содержать не более 15 значащих 
цифр в мантиссе. При попытке ввести большее количество цифр по-
следние цифры, соответствующие меньшим разрядам, заменяются 
нулями. Так, при вводе в ячейку таблицы числа, состоящего из двад-
цати девяток, — 99 999 999 999 999 999 999, оно преобразуется в число 
99 999 999 999 999 900 000 и будет отображено на экране, например, 
следующим образом: 99999999999999900000,00 или 1,00Е+20.
Для отображения в ячейке таблицы большего количества знача-
щих цифр (например, номера лицевого счета, состоящего 
из 20 цифр) перед вводом необходимо предварительно установить 
для соответствующей ячейки текстовый формат, а затем ввести в нее 
необходимое числовое значение. Второй вариант — при вводе данных 
в ячейку набрать предшествующий цифрам знак одиночного 
прямого апострофа. (Заметим: апостроф на клавиатуре есть только 
на английской раскладке; его в качестве первого символа нужно вводить 
и в случаях отображения данных других типов в качестве текста). 
И в том, и в другом случае ячейка будет иметь текстовый формат, 
что не препятствует числовой обработке таких данных. Введенный 
в качестве первого символа апостроф отображаться в ячейке не будет, 
он будет виден только в строке формул.
Логические данные могут принимать два значения — ИСТИНА или 
ЛОЖЬ.

1 
Настройки заданы в панели управления Windows (см.: Пуск → Панель управления → 
Категория Часы, язык и регион → Язык и региональные стандарты → 
Вкладка Форматы…).