Задачи по программированию в Microsoft Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ 
НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ 
ТОМСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ 
ИНСТИТУТ ЭКОНОМИКИ И МЕНЕДЖМЕНТА 
 
 
 
 
 
 
 
 
 
 
 
ЗАДАЧИ ПО ПРОГРАММИРОВАНИЮ 
в Microsoft Excel 
 
 
Учебно-методическое пособие 
по курсу «Информатика» 
для студентов института экономики и менеджмента 
направлений подготовки 
38.03.01 – Экономика и 38.03.02 – Менеджмент 
 
 
 
 
 
 
 
 
 
 
 
 
Томск 
2017 

РАССМОТРЕНО И УТВЕРЖДЕНО методической комиссией института 
экономики и менеджмента. 
Протокол № 5 от 17 февраля 2017 г. 
Председатель МК ИЭМ В.В. Маковеева 
 
 
Пособие составлено в соответствии с тематикой практических занятий и программой курса «Информатика» для студентов института экономики и менеджмента направлений подготовки 38.03.01 – Экономика и 
38.03.02 – Менеджмент. 
В данном пособии приведены задачи для освоения программирования 
в Microsoft Excel 2013 на языке Visual Basic for Application (VBA). Для 
некоторых типичных задач приведены соответствующие алгоритмы и 
программы. 
Предназначено для преподавателей, аспирантов, студентов и магистрантов дневной, очно-заочной и заочной формы обучения. 
 
 
 
СОСТАВИТЕЛЬ: Лещинский Борис Семенович, к.т.н., доцент кафедры 
информационных технологий и бизнес-аналитики ИЭМ НИ ТГУ. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
© Томский государственный университет, 2017 

ПРЕДИСЛОВИЕ 
 
В данном пособии приведены задачи для освоения основных возможностей программирования на языке Visual Basic for Application (VBA) для 
Microsoft Excel. 
В первом разделе даются алгоритмы и программы типичных задач, а 
также комментарии к ним. Во втором разделе приведены контрольные 
задачи. Для каждой из предложенных задач следует написать программу 
в универсальной форме. Это означает, что программа должна обеспечивать проведение расчетов при любых допустимых исходных данных. Для 
контроля правильности написания программ в описании каждой задачи 
указаны конкретные исходные данные и в приложении А приведены соответствующие ответы. В приложении Б указаны основные операторы 
языка, которые требуются для решения предлагаемых задач. 
Описание каждой задачи состоит из следующих разделов: 
– исходные данные. В этом разделе указаны, какие данные являются 
входными для программы и каковы их условные обозначения, используемые в расчетных формулах; 
– расчет. Здесь приведены расчетные формулы, с помощью которых 
можно получить требуемый результат; 
– вывести. В этом разделе указаны величины, которые должны быть 
вычислены и выведены на экран; 
– конкретные исходные данные для проверки. Здесь указаны конкретные значения входных данных, что позволяет проверить правильность 
написания программы. 
 

1 ПРИМЕРЫ РЕШЕНИЯ ЗАДАЧ 
 
Разработчик программы, как правило, сталкивается с двумя проблемами: понимание логики решения задачи и описание этой логики средствами конкретного языка программирования. Попытка одновременного 
решения этих проблем непосредственно при написании текста программы существенно затрудняет разработку. Предпочтительно решать их последовательно. На первом этапе разрабатывается алгоритм решения задачи. При этом, внимание уделяется описанию последовательности действий, которая гарантирует получение правильного результата, безотносительно к языку программирования. На втором этапе осуществляется 
написание текста программы. Здесь уже требуется записать готовый алгоритм таким образом, чтобы текст удовлетворял синтаксическим и семантическим правилам конкретного языка программирования. 
В примерах решения задач для описания алгоритмов использован 
структурный способ (в виде блок-схемы). 
 
1.1 Диалоговый ввод и вывод данных с помощью 
стандартных функций 
 
Диалоговый ввод (или вывод) означает, что для этих целей используются диалоговые окна, в которых пользователь читает сообщения или 
указывает данные, которые хочет ввести. В VBA имеются соответствующие разнообразные возможности. Проще всего для этого использовать 
стандартные функции InputBox (диалоговый ввод данных) и MsgBox 
(диалоговый вывод). 
 
Пример 1. Ввести элемент одномерного массива вещественного типа 
(объем массива – 10 элементов), номер которого ввел пользователь. Вычислить квадрат этого элемента и вывести результат (с указанием номера 
этого элемента) в следующем виде: 
Квадрат …-го элемента (…): … 
 
Решение. 
Прежде, чем писать текст программы, проанализируем условие задачи. 
Заметим, что при вводе номера элемента (обозначим его i) пользователь может ошибиться, указав недопустимое число (меньше 1 или боль

ше 10), что приведет к невозможности дальнейших действий. Поэтому 
необходимо проанализировать полученное от пользователя число и, если 
оно недопустимо, вывести на экран сообщение об ошибке и прекратить 
выполнение программы. Эта часть алгоритма представляет собой ветвление (рис. 1): 

 
Рис. 1. Проверка корректности введенного числа. 
 
Текст программы: 
 
Sub Example_1() 
' блок 1 
Dim a(1 To 10) As Single, i As Integer 
' определение переменных 
i = InputBox("Введите номер элемента", "Ввод данных", 1) ' блок 2 
If (i < 1) Or (i > 10) Then 
' блок 3 
MsgBox "Недопустимый номер элемента", _ 
vbOKOnly + vbCritical, "Ошибка" 
' блок 4 
End 
' блок 5 
End If 
a(i) = InputBox("Введите " + CStr(i) + "-й элемент", _ 
"Ввод данных", 0) 
MsgBox "Квадрат " + CStr(i) + "-го элемента (" + _ 
CStr(a(i)) + "): " + CStr(a(i) ^ 2), _ 
vbOKOnly + vbInformation, "Вывод результата" 
End Sub 
Здесь с помощью оператора Dim описан массив a вещественного типа 
(As Single) объемом 10 элементов и простая переменная i целого типа (As 
Integer). Затем с помощью функции InputBox осуществляется ввод номера элемента с присваиванием переменной i полученной константы. Ар
начало
1 

Ввести i 
2 

Вывести сообщение
об ошибке 

4 

окончание 
5 
… 

да
(i < 1) ИЛИ (i > 10)? 

нет

3 

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

 
 
Рис. 2. Диалоговое окно ввода данных. 
 
Пользователь может сразу подтвердить ввод (если его устраивает 
число 1) или изменить число в поле ввода. В результате, значением 
функции InputBox будет константа, указанная в поле ввода. 
В следующей части программы производится проверка значения  переменной i на корректность (с помощью оператора If). Если это значение 
удовлетворит условию (i < 1) Or (i > 10), то (Then) с помощью функции 
MsgBox будет выведено на экран сообщение об ошибке (рис. 3): 
 

 
 
Рис. 3. Сообщение об ошибке ввода числа. 
 
и выполнение программы закончится (оператор End). 
Далее следует оператор ввода числа с присваиванием его элементу 
a(i) массива a. Первый аргумент (текстовый комментарий) функции 
InputBox составлен (с помощью операции «конкатенация») из трех частей: строковая константа "Введите ", значение переменной i, преобразо

ванное в строковый тип с помощью функции CStr, и строковая константа 
"-й элемент". В результате, во время выполнения программы, если пользователь введет, например, номер элемента 3, на экране появится окно 
(рис. 4): 
 

 
 
Рис. 4. Конкатенация комментария для окна ввода данных. 
 
Результат вычисления выводится на экран с помощью функции 
MsgBox. Первый аргумент этой функции, как и в предыдущем случае, 
также составлен из нескольких частей. При этом, имея в виду, что это 
аргумент строкового типа, числовые данные преобразовываются в строковые с помощью функции CStr. Заметим, что в качестве одной из частей 
этого аргумента указано и возведение в квадрат a(i). 
В итоге, например, если пользователь введет значение элемента 13,5, 
то получим (рис. 5): 
 

 
 
Рис. 5. Окно вывода результатов расчета. 
 

1.2 Цикл 
 
Пример 2. Расчет относительной экономии (перерасхода) материальных ресурсов. 
 
Исходные данные (вводятся пользователем): 
n – количество наименований продукции; 
ri – нормативный расход материала на единицу i-й (i = 1,..., n) продукции; 
rfi – фактический расход материала на единицу i-й (i = 1,..., n) продукции; 
qi – выпуск i-й (i = 1,..., n) продукции. 
 
Расчет. 
Экономия (перерасход) материальных ресурсов (в %) вычисляется по 
формуле 

100





R

R
R
F
f
r
, где 





n

i
i
fi
f
q
r
R
1
 – общий фактический расход материалов; 





n

i
i
i q
r
R
1
 
– общий нормативный расход материалов. 

 
Вывести полученные результаты (R, Rf, Fr). 
 
Конкретные исходные данные для проверки: 
n = 3; 
r1 = 230.6; 
r2 = 59.4; 
r3 = 145.4; 
rf1 = 214.3; 
rf2 = 68.5; rf3 = 154.6; 
q1 = 154; 
q2 = 316; 
q3 = 261 
 

Решение. 
Блок-схема алгоритма приведена на рис. 6. 
 

 
 
Рис. 6. Блок-схема алгоритма для примера 2. 
 
В этой задаче мы имеем массивы r, rfi, и qi, причем объемы этих массивов заранее не известны. Поэтому в программе мы сначала объявим их 
(с помощью Dim) динамическими (без указания количества элементов), а 
после ввода n доопределим с помощью оператора ReDim. 
 

6 
нет 

да 

i  n ? 

7 

Ввести ri, rfi, qi 

8 
R = R + ri×qi 

9 
Rf = Rf + rfi×qi 

10

i = i + 1 

1 
начало

2 
Ввести n 

5 
i = 1 

4 
R = 0; Rf = 0 

3 

нет 

n < 1 ? 
да 

11 

Fr = (Rf – R)/R×100 

12 
Вывести 
R, Rf, Fr 

13
окончание

Текст программы: 
 
Sub Example_2() 
' блок 1 
Dim r() As Single, rf() As Single, q() As Integer, _ 
Rrf As Single, Rr As Single, Fr As Single, _ 
n As Integer, prompt As String 
' описание переменных 
n = InputBox("Введите количество наименований продукции", _ 
"Расход материалов", 1) 
' блок 2 
If n < 1 Then End 
' блок 3 
ReDim r(1 To n), rf(1 To n), q(1 To n) 
' доопределение массивов 
Rr = 0 
' блок 4 
Rrf = 0 
For i = 1 To n 
' блоки 5 и 6 
r(i) = InputBox("Введите нормативный расход для " + _ 
CStr(i) + "-й продукции", _ 
' блок 7 
"Расход материала на одну единицу продукции", 0) 
rf(i) = InputBox("Введите фактический расход для " + _ 
CStr(i) + "-й продукции", _ 
"Расход материала на одну единицу продукции", 0) 
q(i) = InputBox("Введите количество " + CStr(i) + _ 
"-й продукции", "Выпуск продукции", 0) 
Rr = Rr + r(i) * q(i) 
' блок 8 
Rrf = Rrf + rf(i) * q(i) 
' блок 9 
Next 
' блок 10 
Fr = (Rrf - Rr) / Rr * 100 
' блок 11 
prompt = "ОБЩИЙ РАСХОД МАТЕРИАЛОВ:" + Chr(13) + _ 
"    - нормативный " + CStr(Rr) + Chr(13) + _ 
"    - фактический " + CStr(Rrf) + Chr(13) + Chr(13) + _ 
"Экономия (перерасход) материальных ресурсов: " + _ 
CStr(Fr) + "%" 
MsgBox prompt, vbOKOnly + vbInformation, _ 
' блок 12 
"Расход материалов" 
End Sub 
' блок 13 
Обратите внимание, что переменные R и Rf, используемые в описании 
задачи, в тексте программы переименованы – в Rr и Rrf соответственно. 
Это пришлось сделать, т.к. в языке программирования изменение величины знаков не изменяет их смысла (в отличие от обычного языка). Поэтому имена r и R (rf и Rrf) считаются эквивалентными, что противоречит