Программирование и решение сложных задач в 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).