Написание макросов в Excel

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

Универсальные языки
Универсальные языки обладают средствами генерации равномерно распределенных случайных величин на отрезке.
Например, в языке C#:
Random r = new Random();
int k = r.Next(255);
Для получения других распределений требуется писать дополнительные процедуры.

Расчетные ПО для моделирования
Для проведения расчетов по имитационным моделям могут использоваться:
Математические пакеты (Mathematica, Matlab и пр.);
Офисные пакеты (MS Office в части MS Excel).

Почему удобно использовать MS Excel
Программа MS Excel обладает:
Специальным набором функций, которые позволяют вычислять функции распределения случайных величин;
Средствами графического представления данных (построители диаграмм);
Собственным языком программирования (VBA), с помощью которого можно задавать сложные расчетные алгоритмы;
Набором элементов управления, которые можно внедрять в рабочие листы электронных таблиц;
Удобным способом сохранения данных в виде электронных таблиц;
Использование формул в ячейках для вычислимых полей.

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

Функции генерации случайных величин

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

Лист с формой
Один из листов будет задавать параметры модели:
Количество клиентов;
Среднее время интервала прихода клиентов;
Среднее время обслуживания клиентов.

Заполнение параметров
Заполнение параметров по умолчанию может быть заполнено при загрузке файла.

Еще один лист для расчетов
Для расчетов заведем отдельный лист в книге. Там для каждого клиента указываем время прибытия, время начала обслуживания и время обслуживания клиента.

Заполнение листа расчетов
Запуск расчетов организуется при нажатии кнопки на листе формы.
Private Sub Go_Click()
' генерация строк обслуживания клиентов
‘получение количества клиентов
n = Worksheets("Форма").Cells(4, 6).Value
' средний промежуток времени между приходами клиентов
Av1 = Worksheets("Форма").Cells(9, 6).Value
' среднее время обслуживания клиентов
Av2 = Worksheets("Форма").Cells(12, 6).Value
'время прихода последнего клиента
x = 0
' модельное время
time = 0
' время освобождения парикмахера
time_p = 0

Заполнение листа расчетов
' цикл прихода клиентов
For i = 1 To n ' генерация очередного промежутка
y = Application.WorksheetFunction.RandBetween(Av1 - 5, Av1 + 5)
' фиксация номера клиента
Worksheets("Расчеты").Cells(1 + i, 2).Value = I
' фиксация прихода клиента
Worksheets("Расчеты").Cells(1 + i, 3).Value = x + y
' изменение модельного времени
time = x + y
' смещение времени с учетом прихода клиента
x = x + y

Заполнение листа расчетов
' генерация времени обслуживания клиента
t = Application.WorksheetFunction.RandBetween(Av2 - 8, Av2 + 8)
‘ определение времени освобождения парикмахера
If time_p <= time Then
' парикмахер свободен
Worksheets("Расчеты").Cells(1 + i, 4).Value = time
Worksheets("Расчеты").Cells(1 + i, 5).Value = t
time_p = time + t
Else
'парикмахер занят
Worksheets("Расчеты").Cells(1 + i, 4).Value = time_p
Worksheets("Расчеты").Cells(1 + i, 5).Value = t
time_p = time_p + t
End If
Next

Лист результатов
На отдельном листе приведем расчет результатов – количество клиентов, обслуженных за рабочий день, для каждого клиента время его ожидания и общее время пребывания в парикмахерской. Можно включить в лист диаграммы изменения показателей.

Заполнение листа результатов
' определение количества клиентов за рабочий день
Count = 0
For i = 1 To n
' запись информации о клиенте
Worksheets("Результаты").Cells(1 + i, 8).Value = i ' номер клиента
Worksheets("Результаты").Cells(1 + i, 9).Value =
Worksheets("Расчеты").Cells(1 + i, 4).Value –
Worksheets("Расчеты").Cells(1 + i, 3).Value ' ожидание
' пребывание в парикмахерской
Worksheets("Результаты").Cells(1 + i, 10).Value =
Worksheets("Расчеты").Cells(1 + i, 4).Value +
Worksheets("Расчеты").Cells(1 + i, 5).Value –
Worksheets("Расчеты").Cells(1 + i, 3).Value

Заполнение листа результатов
‘ если время окончания обслуживания будет больше
‘ длительности рабочего дня, окончание обслуживания
If Count = 0 And Worksheets("Расчеты").Cells(1 + i, 4).Value +
Worksheets("Расчеты").Cells(1 + i, 5).Value >
Worksheets("Форма").Cells(2, 11).Value Then
Count = i - 1
End If
Next
If Count = 0 Then
Count = 100
End If
‘ показ на листе результатов количества обслуженных клиентов
Worksheets("Результаты").Cells(2, 13).Value = Count

Заполнение листа результатов
' заполнение данных о средних показателях обслуживания клиентов
Worksheets("Результаты").Cells(1 + Count + 2, 8) = "Среднее "
‘определение диапазонов для расчетов среднего времени
‘ожидания клиентов и вреднее время обслуживания
range1 = "=AVERAGE(I2:I" & (1 + Count) & ")"
range2 = "=AVERAGE(J2:J" & (1 + Count) & ")"
Worksheets("Результаты").Cells(1 + Count + 2, 9).Formula = range1
Worksheets("Результаты").Cells(1 + Count + 2, 10).Formula = range2
End Sub

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

Изменение алгоритма заполнения расчетов
‘количество парикмахеров
m = Worksheets("Форма").Cells(2, 6).Value
‘ заводится массив, в котором будут храниться время
‘ освобождения мастера.
‘ в начальный момент времени все мастера свободны
ReDim devices(m) As Integer
For i = 1 To m
devices(i) = 0
Next

Изменение алгоритма заполнения расчетов
‘ После генерации прихода очередного клиента
' поиск парикмахера, который будет обслуживать
‘ данного клиента – первый освободившийся, т.е. тот,
‘ которому соответствует минимальное время в массиве
Min = 60 * 24
imin = 0
For j = 1 To m
If devices(j) < Min Then
Min = devices(j)
imin = j
End If
Next
' фиксация номера парикмахера
Worksheets("Расчеты").Cells(1 + i, 6).Value = imin

Изменение алгоритма заполнения расчетов
‘ фиксация времени обслуживания
' генерация времени обслуживания клиента
t = Application.WorksheetFunction.RandBetween(Av2 - 8, Av2 + 8)
If devices(imin) <= time Then
' парикмахер свободен
Worksheets("Расчеты").Cells(1 + i, 4).Value = time
Worksheets("Расчеты").Cells(1 + i, 5).Value = t
devices(imin) = time + t
Else
'парикмахер занят
Worksheets("Расчеты").Cells(1 + i, 4).Value = devices(imin)
Worksheets("Расчеты").Cells(1 + i, 5).Value = t
devices(imin) = devices(imin) + t
End If

Изменение алгоритма заполнения результатов
Результаты дополняются данными о загрузке парикмахеров:
Общее отработанное время;
Время простоя.

Изменение алгоритма заполнения результатов
'заполнение результатов эксперимента
'предварительное заполнение данных о парикмахерах For i = 1 To m
Worksheets("Результаты").Cells(1 + i, 2) = i ' номер парикмахера
Worksheets("Результаты").Cells(1 + i, 3) = 0 ' время работы
' время простоя
Worksheets("Результаты").Cells(1 + i, 4) =
Worksheets("Форма").Cells(2, 11).Value
Next

Изменение алгоритма заполнения результатов
‘ в цикле анализа данных клиента
For i = 1 To n
. . .
‘ корректировка информации о парикмахере
' номер обслуживающего парикмахера
nom = Worksheets("Расчеты").Cells(1 + i, 6).Value
' время обслуживания клиента
t = Worksheets("Расчеты").Cells(1 + i, 5).Value
Worksheets("Результаты").Cells(1 + nom, 3).Value =
Worksheets("Результаты").Cells(1 + nom, 3).Value + t
Worksheets("Результаты").Cells(1 + nom, 4).Value =
Worksheets("Результаты").Cells(1 + nom, 4).Value – t
. . .
Next