Программирование и решение сложных задач в Excel

Министерство образования и науки Российской Федерации
Уральский федеральный университет 
имени первого Президента России Б. Н. Ельцина

В. А. Петрова

ПРОГРАММИРОВАНИЕ 
И РЕШЕНИЕ СЛОЖНЫХ ЗАДАЧ 
В EXCEL

Рекомендовано методическим советом 
Уральского федерального университета 
в качестве учебного пособия для студентов, 
обучающихся по направлениям подготовки 
230700 — Прикладная информатика, 
080500 — Бизнес-информатика

Екатеринбург
Издательство Уральского университета
2016

УДК 004.91(075.8)
ББК 32.973.26-018.2я73
         П30

Рецензенты:
кафедра высшей математики Уральского государственного университета путей сообщения (
зав. кафедрой, проф., д-р физ.-мат. наук Г. А. Тимофеева);
канд. физ.-мат. наук Д. Г. Ермаков (Институт математики и механики УрО РАН)

 
Петрова, В. А.
П30    Программирование и решение сложных задач в Excel : учеб. пособие / В. А. Петрова. — 
Екатеринбург : Изд-во Урал. ун-та, 2016. — 88 с.

ISBN 978-5-7996-1949-7

Раздел 1 содержит сведения о способах обработки списков средствами программы MS Excel 
2013 и задания для выполнения лабораторной работы по данной теме.
Раздел 2 содержит основные сведения о решении оптимизационных задач в среде программы 
MS Excel 2013 и задания для выполнения лабораторной работы по данной теме.
Раздел 3 содержит сведения о решении задач с использованием финансовых функций 
MS Excel и задания для выполнения лабораторной работы по данной теме.
Раздел 4 содержит основные сведения о возможностях языка программирования Visual 
Basic for Application и задания для выполнения лабораторных работ по программированию.
Раздел 5 содержит основные сведения о работе с формами в среде программы MS Excel 
2013 и задания для выполнения лабораторной работы по разработке формы.
Пособие предназначено для студентов четвертого курса Высшей школы экономики и менеджмента. 
Составлено в соответствии с программой курса «Программирование и решение 
сложных задач в Excel» и может быть использовано для самостоятельного изучения данного 
курса.

Библиогр.: 7 назв. Табл. 5. Рис. 27.

УДК 004.91(075.8)
ББК 32.973.26-018.2я73

Учебное электронное сетевое издание

Петрова Вера Александровна

ПРОГРАММИРОВАНИЕ И РЕШЕНИЕ СЛОЖНЫХ ЗАДАЧ В EXCEL

Корректор Е. Е. Афанасьева
Верстка О. П. Игнатьевой

Подписано в печать 25.12.2016. Формат 70×100/16. Гарнитура Century Schoolbook.
Уч.-изд. л. 5,0.

Издательство Уральского университета. Редакционно-издательский отдел ИПЦ УрФУ
620049, Екатеринбург, ул. С. Ковалевской, 5. Тел.: 8(343)375-48-25, 375-46-85, 374-19-41. E-mail: rio@urfu.ru

ISBN 978-5-7996-1949-7 
© Уральский федеральный
 
    университет, 2016



Оглавление

Введение ...................................................................................................5

Раздел 1.  
Обработка табличных баз данных ..................................................6
1.1. Сортировка списка ............................................................................7
1.2. Фильтрация списка ..........................................................................9
1.3. Подведение промежуточных итогов .............................................14
1.4. Создание сводной таблицы Excel .................................................16
1.5. Практические задания «Фильтрация списков. Определение 
        промежуточных итогов. Построение сводных таблиц» .............20

Раздел 2.  
Решение оптимизационных задач  
с помощью программы «Поиск решения» ..................................25
Практические задания «Поиск решения. Разработка сценариев» ..27

Раздел 3.  
Работа с финансовыми функциями и построение  
таблицы данных .................................................................................31
3.1. Функция ЧПС (чистая приведенная стоимость инвестиции) ...31
3.2. Функция АПЛ (линейный метод) .................................................32
3.3. Функция АСЧ (метод суммы чисел) .............................................33
3.4. Функция ДДОБ (метод двойного уменьшения остатка) ............34
3.5. Практические задания «Использование финансовых 
       функций и построение таблиц данных» ......................................36

Раздел 4.  
Основы программирования на vba 
(visual basic For applications) .......................................................39
4.1. Особенности объектно-ориентированного программирования 
       на VBA в MS Excel ..........................................................................40
4.2. Типы данных. Типы процедур. Синтаксис VBA .........................43

Оглавление

4.3. Процедуры SUB ..............................................................................45
4.4. Процедуры ввода-вывода ...............................................................49
4.5. Управляющие конструкции VBA ..................................................51
4.6. Основные объекты VBA Excel .......................................................55
4.7. Практические задания «Работа с объектами Application, 
       Worksheets, Range» .........................................................................66
4.8. Практические задания «Работа с объектом Worksheets» ...........72

Раздел 5.  
Создание пользовательской формы .............................................77
5.1. Создание формы в редакторе VBA ...............................................80
5.2. Практические задания «Использование VBA  
        для разработки форм различной степени сложности» ..............83

Библиографический список ..................................................................88



Введение

В 

работе современных предприятий все большее значение при-
обретает возможность использования существующей инфор-
мации и возможность получения качественно новой инфор-
мации. Способность работников предприятия извлечь нужные данные 
и умение представить их в виде различных отчетов напрямую связа-
но с результатами, которые могут быть использованы руководством 
предприятия для текущего анализа деятельности и дальнейшего при-
нятия решений по совершенствованию работы предприятия.
Табличный процессор MS Excel является мощным инструментом 
для получения рациональных решений в тех случаях, когда требу-
ется обработка больших объемов информации, связанная с поиском, 
фильтрацией, сортировкой и получением итоговых значений с помо-
щью различных функций.
Мощный математический аппарат встроенных функций (матема-
тических, статистических, финансовых и пр.) предоставляет огром-
ные возможности по обработке данных, помещенных в ячейки ли-
стов с помощью непосредственного ввода или путем вывода из других 
офисных приложений и приложений, поддерживающих техноло-
гию COM.
Встроенный в MS Excel язык программирования VBA позволяет 
существенно улучшить качество создаваемых приложений. Легко вы-
бираемые из списка отдельные макросы позволяют автоматизировать 
выполнение часто повторяющихся операций, а наличие пользователь-
ской формы с определенным набором элементов управления предо-
ставляет дополнительные возможности, такие как проверка вводи-
мых значений в табличную базу данных, поиск нужных значений, 
быстрое удаление и обновление записей табличной базы.



Раздел 1.  
Обработка табличных баз данных

П

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

1.1.Сортировкасписка

1.1. Сортировка списка

Сортировка позволяет выстраивать данные списка в алфавитном, 
цифровом и хронологическом порядках. При этом можно задать воз-
растающий, убывающий и пользовательский порядки сортировки.
Если задан возрастающий порядок сортировки, то данные списка 
упорядочиваются следующим образом:
— числа выстраиваются от наименьшего отрицательного к наи-
большему положительному;
— значения даты и времени сортируются в хронологическом по-
рядке от самого раннего к самому позднему;
— текстовые данные сортируются по алфавиту;
— столбец логических значений будет начинаться со значения 
ЛОЖЬ и заканчиваться значением ИСТИНА;
— пустые ячейки располагаются в конце списка.
Если задан убывающий порядок сортировки, то пустые ячейки так-
же располагаются в конце списка, а данные всех остальных типов со-
ртируются в обратном описанному ранее порядку.
Пользовательский порядок сортировки заключается в сортиров-
ке по дням недели либо по названиям месяцев года. Можно создать 
свой собственный пользовательский порядок сортировки.
Поле, по которому выполняется сортировка, называется ключом 
сортировки. Для упорядочивания списка по одному ключу можно 
воспользоваться кнопками на панели инструментов: Сортировка 
по возрастанию 
, Сортировка по убыванию 
. Предваритель-
но выделяется любая ячейка того поля, по которому необходимо от-
сортировать список.
Сортировать список можно с помощью команды Сортировка 
в меню Данные. Команда Сортировка позволяет сортировать мак-
симум по трем ключам, т. е. список сортируется сначала по одному 
полю, затем внутри отсортированных значений этого поля выполня-
ется сортировка по второму полю, и аналогично внутри отсортиро-
ванным значениям второго поля можно отсортировать по третьему 
ключу. Сортировка по нескольким ключам возможна в том случае, 
если первый и второй ключи сортировки имеют повторяющиеся зна-
чения. Если ключей сортировки больше трех, команда Сортировка 

Раздел1.Обработкатабличныхбазданных

выполняется еще раз. Причем четвертый ключ имеет приоритет над 
первыми тремя, т. е. список сортируется по четвертому ключу в пер-
вую очередь.
Если в дальнейшем необходимо вернуться к первоначальному по-
рядку сортировки, следует воспользоваться индексом. Индекс — это 
поле, содержащее уникальное значение для каждой записи, например 
порядковый номер.

Порядок действий при выполнении сортировки
1. Выделите любую ячейку внутри сортируемого списка.
2. Выберите команду Данные — Сортировка. На экране отобразится 
диалоговое окно Сортировка (рис. 1.1).

Рис. 1.1. Сортировка списка по двум полям

3. В окне диалога команды заполните все области строки Сортировать 
по.

4. В области Столбец нажмите кнопку раскрывающегося списка 
и выберите имя поля, по которому необходимо отсортировать 
список.

5. В области Сортировка по умолчанию устанавливается вариант 
Значения (при необходимости выберите вариант Цвет ячейки 
или Цвет шрифта).

6. В области Порядок устанавливается тип сортировки: По возрастанию, 
По убыванию (для числового типа данных); От А до Я, 
От Я до А (для текстового типа данных).
Примечание. Вариант Настраиваемый список, выбранный в области 
Порядок, позволяет задать пользовательский порядок сортировки, 
например по дням недели.

1.2.Фильтрациясписка

7. Для сортировки списка по второму (третьему) полю в диалоге 
команды нажмите кнопку Добавить уровень и заполните все 
области строки Затем по.

8. Кнопка Копировать уровень в диалоге команды позволяет доба-
вить строку Затем по с параметрами, аналогичными установ-
ленным в предыдущем уровне.

9. Нажмите кнопку ОК.
Примечание. В диалоговом окне Параметры сортировки, отобра-
жаемом на экране нажатием кнопки Параметры диалогового окна 
Сортировка, можно потребовать учета регистра символов или задать 
сортировку данных по столбцам. Для сортировки списка по столбцам 
выберите переключатель Столбцы диапазона.

1.2. Фильтрация списка

Фильтрация — это выбор из списка записей, удовлетворяющих 
какому-либо критерию отбора. Фильтрация выполняется на вклад-
ке Данные с помощью команд Фильтр и Дополнительно (Расширен-
ный Фильтр). Команды Фильтр и Дополнительно фильтруют спи-
сок, а команда Очистить отменяет фильтрацию.

Использование команды Фильтр
Команда Фильтр (автофильтр) позволяет очень быстро, с помо-
щью элементарных действий мыши, отфильтровать список. Фильтр 
работает по принципу: «включил — выключил». После выбора ко-
манды все заголовки полей снабжаются кнопками раскрывающихся 
списков, используемых для фильтрации (фильтр — «включен»). По-
вторный выбор команды убирает кнопки раскрывающихся списков 
(фильтр — «выключен»).

Порядок действий при выполнении команды Фильтр
1. Выделите любую ячейку внутри фильтруемого списка.
2. Выберите команду Данные — Фильтр.
3. Нажмите кнопку раскрывающегося списка, соответствующую 
полю, по которому выполняется отбор записей. Выполните сле-
дующие действия:

Раздел1.Обработкатабличныхбазданных

— снимите флажок Выделить все;
— установите флажок напротив конкретного значения поля для 
вывода на экран записей, содержащих выбранное значение 
данного поля;
— нажмите кнопку ОК.

4. Для отбора записей внутри диапазона значений числового поля 
выберите команду Числовые фильтры (рис. 1.2), затем одну 
из подкоманд: равно, не равно, больше, больше или равно, мень-
ше, меньше или равно, между, Настраиваемый фильтр.
Примечание. Все перечисленные подкоманды выводят на экран 
диалог Пользовательский автофильтр, в котором необходимо сфор-
мировать условие отбора записей по активному полю.

Рис. 1.2. Работа с командой Числовые фильтры

5. Если активное поле является текстовым, то в меню будет пред-
ставлена команда Текстовые фильтры и подкоманды: начина-
ется с, заканчивается на, содержит, не содержит.

1.2.Фильтрациясписка

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

Использование команды Дополнительно 
(Расширенный фильтр)
Расширенный фильтр позволяет задавать более сложные условия 
фильтрации и выполнять копирование отфильтрованного списка. 
Критерии отбора задаются в любом месте рабочего листа и оформ-
ляются в виде отдельной таблицы. Расширенный фильтр в отличие 
от автофильтра позволяет отбирать не только записи, но и поля из ис-
ходного списка.
Перед выполнением команды Расширенный фильтр необходимо:
— составить таблицу критериев отбора;
— скопировать в любое свободное место листа заголовки тех полей 
списка, которые требуется отобрать в процессе фильтрации (для 
вывода отфильтрованной таблицы в ячейки листа, расположен-
ные вне списка).

Создание таблицы критериев
1. Поместите таблицу критериев отбора над списком в любое свободное 
место листа (между таблицей критериев и списком должна 
остаться как минимум одна пустая строка).

2. В первую строку таблицы критериев скопируйте заголовки тех 
полей списка, по которым будет выполняться отбор данных.

3. В ячейки, расположенные под заголовками, введите сами критерии (
условия отбора).

Критерии отбора
Критерии отбора могут быть как простые, так и вычисляемые. Простые 
критерии отбора — это либо конкретные значения полей, по которым 
ведется отбор, либо логические выражения, использующие 
знаки сравнения: >, і, <, Ј, <>. При задании критериев можно воспользоваться 
следующими символами шаблона: символ «*» обозначает 
произвольную последовательность любых символов, символ «?» 

Раздел1.Обработкатабличныхбазданных

обозначает любой единичный символ. Все критерии (как простые, 
так и вычисляемые), которые заданы в одной строке, выполняются 
одновременно — аналог логического оператора И. Для объединения 
критериев с помощью условного оператора ИЛИ критерии задаются 
в разных строках таблицы критериев.
На рис. 1.3 представлен результат фильтрации списка по условию, 
состоящему из трех простых критериев. Отобраны записи с данными, 
относящимися к группе ИМ-14011, в которых значения поля Сумма 
МЛ находятся в диапазоне от 100 до 150. В критерии, используемом 
для отбора значений по полю Группа, используется символ шабло-
на «*» (рис. 1.3).

Рис. 1.3. Задание простых критериев

При создании вычисляемого критерия учитывают следующие пра-
вила.

1. Заголовок вычисляемого критерия не должен совпадать ни с од-
ним заголовком поля фильтруемого списка. Можно ввести но-
вый заголовок или оставить ячейку заголовка пустой.

2. Формула критерия должна ссылаться хотя бы на одно поле спи-
ска.

3. Ссылки на ячейки, с которыми будет выполняться сравнение, 
должны быть абсолютными.

4. Ссылка на ячейку поля, значения которого будут сравниваться 
с эталонными, должна быть относительной.

5. Формула критерия является логической формулой, поэтому воз-
вращает значение ИСТИНА или ЛОЖЬ (рис. 1.4).