Использование пакета Microsoft Office Посредством написания макросов для Excel. - презентация

1 Использование пакета Microsoft Office Посредством написания макросов для Excel

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

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

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

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

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

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

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

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

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

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

12 Заполнение листа расчетов Запуск расчетов организуется при нажатии кнопки на листе формы. 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

13 Заполнение листа расчетов ' цикл прихода клиентов 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

14 Заполнение листа расчетов ' генерация времени обслуживания клиента t = Application.WorksheetFunction.RandBetween(Av2 - 8, Av2 + 8) определение времени освобождения парикмахера If time_p

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

16 Заполнение листа результатов ' определение количества клиентов за рабочий день 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 Worksheets("Фор" title="Заполнение листа результатов если время окончания обслуживания будет больше длительности рабочего дня, окончание обслуживания If Count = 0 And Worksheets("Расчеты").Cells(1 + i, 4).Value + Worksheets("Расчеты").Cells(1 + i, 5).Value > Worksheets("Фор" class="link_thumb"> 17 Заполнение листа результатов если время окончания обслуживания будет больше длительности рабочего дня, окончание обслуживания 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("Фор"> 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("Фор" title="Заполнение листа результатов если время окончания обслуживания будет больше длительности рабочего дня, окончание обслуживания If Count = 0 And Worksheets("Расчеты").Cells(1 + i, 4).Value + Worksheets("Расчеты").Cells(1 + i, 5).Value > Worksheets("Фор">

18 Заполнение листа результатов ' заполнение данных о средних показателях обслуживания клиентов 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

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

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

21 Изменение алгоритма заполнения расчетов После генерации прихода очередного клиента ' поиск парикмахера, который будет обслуживать данного клиента – первый освободившийся, т.е. тот, которому соответствует минимальное время в массиве 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

22 Изменение алгоритма заполнения расчетов фиксация времени обслуживания ' генерация времени обслуживания клиента t = Application.WorksheetFunction.RandBetween(Av2 - 8, Av2 + 8) If devices(imin)

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

24 Изменение алгоритма заполнения результатов 'заполнение результатов эксперимента 'предварительное заполнение данных о парикмахерах 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

25 Изменение алгоритма заполнения результатов в цикле анализа данных клиента 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

26 Возможные дополнения модели Отслеживание возможности обеденного перерыва, например, с определенного момент времени ( с 12.00) по одному мастеру могут на 40 минут отлучиться на обед; Можно отслеживать набор услуг, которые может оказывать каждый мастер, т.е. очередного клиента может обслуживать не каждый мастер. Т.о. очереди разбиваются с учетом нужной услуги; Можно учитывать возможность записи клиентов на определенное время.