Автоматизация заполнения расписания морских грузоперевозок методами VBA Excel

Определения и понятия

Расписание грузоперевозок (далее – расписание) - это основной документ управленческой отчетности логистической компании. Все производственные процессы логистического оператора основаны на расписании грузоперевозок. В рамках настоящей статьи приведено упрощенное расписание, включающее в себя следующие термины логистики:

POL – Port of Loading Порт отправления - порт, в котором судно приняло груз на борт.

TOA - Оператор приема грузоперевозок.

LINE – Оператор отправления грузоперевозок.

ETA - Estimated Time of Arrival Ожидаемое время прибытия - время, когда предположительно груз должен прибыть в место назначения.

ETD - Estimated Time of Departure Ожидаемое время отправления - время, когда предположительно груз должен покинуть место отправления. Постановка задачи автоматизации заполнения расписания Одной из главных задач логистической службы оператора является подготовка расписания грузоперевозок.

Этап 1. Внесение исходных данных

Как следствие, в режиме реального времени службой формируется аналитическая таблица исходных данных, содержащая основные показатели грузоперевозок. Таблица формируется динамически с течением времени, поэтому её обработка должна учитывать произвольное число строк. Пример таблицы с исходными данными см. на Рис.1.

 Рис. 1 – План грузоперевозок. Отправление: г. Шанхай.

Зеленой заливкой выделены ключевые данные, которые требуется внести в расписание грузоперевозок.

Этап 2. Автоматизация заполнения расписания грузоперевозок

Автоматизация заполнения расписания фактически означает внесение исходных данных ETA, LINE и TOA в формате “ LINE / TOA” в шаблон расписания на каждую дату, которая содержит сведения в плане грузоперевозок. Следует учесть, что некоторые даты содержат несколько грузоперевозок, поэтому сведения о грузоперевозках должны быть последовательно размещены в столбце расписания для соответствующей даты перевозки. Шаблон расписания предусматривает максимум до 7 грузоперевозок на каждую дату расписания (см. Рис. 2).

 Рис. 2 – Фрагмент шаблона расписания грузоперевозок.

Разработка макроса VBA Excel по заполнению расписания грузоперевозок.

Код макроса VBA Excel, позволяющего заполнить поля расписания грузоперевозок по данным плана грузоперевозок, приведен ниже.

Public Sub FillShippingCalendar()

Dim CalendarDate As Range ' ячейка с календарной датой
Dim rCell As Range ' текущая ячейка диапазона
Dim DatesCell As Range ' текущая ячейка диапазона
Dim Counter As Integer ' счетчик

Dim FlightIndex As Integer ' счетчик номера ячейки в диапазоне 1-7 
Dim EndRowCalendar As Long ' номер последней строки календаря
Dim i, j, ir, jc, EndRowData As Long ' номер последней строки данных полетов

Dim ETADatesRange As Range ' диапазон дат полетов
Dim CalendarRange As Range ' текущий диапазон календаря для заполнения данных по каждой выбранной дате

With Worksheets("Schedule") ' текущий лист
 EndRowCalendar = .Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row ' номер последней строки в A ' число строк в столбце данных
 EndRowData = .Cells(ActiveSheet.Rows.Count, 9).End(xlUp).Row ' номер последней строки в I ' число строк в столбце данных
' формируем массив дат полетов
 Set ETADatesRange = .Range(.Cells(3, 12), .Cells(EndRowData, 12))


For j = 1 To 7 ' заполняем столбцы расписания полетов для выбранной недели расписания

 For i = 3 To EndRowCalendar ' перебор строк расписания до конечной даты
 If IsDate(.Cells(i, j).Value) = True Then ' если встретилась дата, заполняем по ней календарь информацией по полетам
 Set CalendarDate = .Cells(i, j) ' устанавливаем ячейку с датой расписания, по которой нужно заполнить информацию по полетам

' формируем динамический массив календаря 7х1 по каждой дате
 Set CalendarRange = .Range(.Cells(i + 1, j), .Cells(i + 7, j))

 For Each rCell In CalendarRange.Cells ' для каждой текущей ячейки в диапазоне 7х1 календаря 
' формируем её порядковый номер для внесения записи в случае, если на дату приходится более одной ' грузоперевозки 

FlightIndex = rCell.Row - CalendarDate.Row ' определяем значение счетчика

Counter = 0 ' счетчик найденных полетов для выбранной даты
 For Each DatesCell In ETADatesRange.Cells ' по каждой дате из диапазона дат полетов
 If CalendarDate.Value = DatesCell.Value Then ' если дата расписания совпадает с датой из 'диапазона (искомая информация)
 Counter = Counter + 1 ' увеличиваем счетчик количества совпадающих с датой расписания 'дат диапазона
 If Counter = FlightIndex Then ' если счетчик равен порядковому номеру выбора информации 'по дате

 ir = rCell.Row ' фиксируем номер строки в текущей ячейке диапазона
 jc = rCell.Column ' фиксируем номер столбца в текущей ячейке диапазона
 ' записываем в ячейку информацию по грузоперевозке в формате “ LINE / TOA” 
 ActiveWorkbook.Worksheets("Schedule").Cells(ir, jc).Value = DatesCell.Offset(0, -1).Text & " / " & DatesCell.Offset(0, -2).Text
 
 Else ' иначе счетчик не равен порядковому номеру
 End If ' конец условия
 Else 'дата расписания НЕ совпадает с датой из диапазона (искомая информация)
 End If ' конец условия
 Next DatesCell ' следующая ячейка из диапазона дат полетов
 Next rCell ' следующая ячейка из столбца из 7 ячеек, относящихся к дате полета
 Else ' не дата в текущей ячейке
 End If
 Next i
 Next j
End With
End Sub

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

Рис. 3 - Расписание грузоперевозок. Отправление: г. Шанхай.

В настоящей статье описан подход методом VBA Excel к заполнению данных календаря грузоперевозок. Информация будет полезна аналитикам, которые изучают VBA Excel, а также аналитикам баз данных.