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

БИЗНЕС-АНАЛИТИКА
СРЕДСТВАМИ
EXCEL

Москва
ВУЗОВСКИЙ УЧЕБНИК
ИНФРА-М
2021

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

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

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

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

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

Гобарева Я.Л.
Бизнес-аналитика средствами Excel : учебное пособие / Я.Л. Гобарева, О.Ю. Городецкая, А.В. Золотарюк. — 3-е изд., перераб. 
и доп. — Москва : Вузовский учебник : ИНФРА-М, 2021. — 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.com

ВВЕДЕНИЕ

В современных рыночных условиях экономист-профессионал должен 
уверенно использовать программные комплексы и системы не только в качестве инструмента, облегчающего и ускоряющего процесс решения прикладных задач, но и как средство анализа и исследования предметной 
области с целью получения объективной оценки финансово-экономической деятельности, системно учитывающей множество факторов проведения достоверного прогнозирования и планирования, а также принятия 
на их основе научно-обоснованного решения, способствующего росту 
финансового благополучия и дальнейшему развитию бизнеса.
Одним из таких программно-инструментальных средств, которое может быть применено при решении широкого класса задач финансовоэкономического характера, доступным практически любому пользователю, является табличный процессор 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 Corporation, 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 (см.: Пуск → Панель управления → Категория Часы, язык и регион → Язык и региональные стандарты → 
Вкладка Форматы…).