Выполненные проекты на Excel — VBA.

Здесь я расскажу о некоторых проектах, которые я реализовал в процессе своей работы. Некоторые проекты довольно крупные и разрабатывались не одну неделю. Благодаря им, удалось избавиться от большой части ручной и рутинной работы. Постараюсь подробно описать каждый из проектов, кое-где добавлю скриншоты (но не везде, так как часть информации не для публичного использования).

Автоматизированный план производства.

Данный проект используется для расчета плана производства на различные периоды: строятся графики работы на каждом из заводов, оценивается объем полуфабрикатов для производства той или иной продукции, рассчитывается процент загрузки каждого из оборудования и т.д. Принцип работы данного инструмента заключается заключается в следующем:

  1. В файл проекта (далее АПП) импортируется прогноз продаж: на неделю, месяц, три месяца или год
  2. АПП проверяет все SKU прогноза продаж на наличие в справочнике НСИ (нормативно-справочная информация).
    • Артикул и категория
    • Заводы, линии и оборудование, на которых может производиться данная SKU
    • Используемые полуфабрикаты и их коэффициенты
    • А также различные коэффициенты распределения для используемого оборудования
  3. Если SKU отсутствует, АПП предлагает добавить ее в справочник, заполнив все ее характеристики.
    • Можно все заполнить вручную, а можно выбрать определенный шаблон: если есть подобная SKU — все данные можно подставить автоматически
  4. После импорта прогноза продаж, рассчитываются дополнительные объемы полуфабрикатов согласно справочнику
  5. Строится график работы для каждого завода и категории продукции:
    • Выбирается период для плана производства
    • Учитываются мойки оборудования, планово-предупредительные ремонты (ППР), технологические и производственные испытания (ТПИ) и коэффициенты перенастройки оборудования.
  6. Рассчитывается максимальная мощность, т.е. производительность всего оборудования:
    • Для обычного оборудования суточная мощность умножается на количество рабочих дней.
    • Для адаптивного оборудования по специальным формулам (некоторое оборудование может производить продукцию различных форматов, и в зависимости от формата меняется суточная мощность).
  7. Рассчитывается загрузка всех типов оборудования на каждом заводе (Запускается распределение объемов плана продаж):
    • Вариант «Всего понемногу» — когда все подобные SKU распределяются одинакового по всем заводам и по всем оборудованиям, на которых может производиться.
    • Вариант «По приоритетам/по порядку» — когда одно определенное оборудование загружается полностью сначала объемом одной подобной позиции, затем другой и т.д., а когда будет загружено полностью — идет переход на другое оборудования для подобной позиции.
    • Вариант «По коэффициентам» — все SKU распределяются по заводам/линиям/оборудовании по заранее заданным коэффициентам (в справочнике, см п.2), с возможностью последующих корректировок в режиме реального отслеживания изменений загрузки оборудования.
  8. План производства подтверждается или перестраивается в зависимости от полученных результатов. Автоматически строятся гистограммы загруженности оборудования и различные дэшборды.

Это очень мощный инструмент, работа над которым длилась довольно долго.

Инструменты прогнозирования

Самый основной инструмент моей работы — инструмент прогнозирования. Основан на моей же карте данных (или, возможно, карта данных основана на нем). В него загружается история продаж, затем он запускается и после работы с некоторыми настройками — строит прогноз. Да, можно настроить SQL-подключение к базе данных, для автоматической выгрузки истории продаж, но я данную функцию пока что не добавлял.

Вообще, инструментов разработано несколько. Есть старые и новые версии, есть инструменты для недельного прогноза и месячного. Какие-то постоянно обновляются, где-то расширяется функционал, а в каких-то алгоритмы работы становятся более оптимизированными. Но есть «основной» функционал, который есть в каждом из инструменте:

  1. Подготовка корректной истории продаж:
    • Консолидация данных.
    • Очистка от ошибок и пустот.
    • Сглаживание in-out’ов.
  2. Сглаживание промо с помощью различных экспертных настроек.
  3. Встроенный инструмент работы с сезонностью (присутствует преимущественно в месячных инструментах прогнозирования).
  4. Различные проверки с индикаторами для отображения «нестабильных продаж»:
    • Проверка последнего месяца или недели продаж.
    • Проверка нулевых продаж при стабильном тренде или высоких значениях в истории продаж.
    • Проверка и игнорирование SKU без продаж N периодов (где N устанавливается вручную).
    • Проверка продаж на стабильность.
  5. Построение прогноза, также, с помощью различных экспертных настроек.
  6. Конечная корректировка значений прогноза:
    • Округления до объемов, кратных коробке.
    • Очистка от позиций, выведенных из ассортимента.
    • Наложение сезонности, если это необходимо.
    • Наложение Трейд-Маркетинговой активности (промо, листинги) на общий прогноза.
  7. Ручные корректировки нестабильных значений.

А также еще некоторое количество функций и настроек, в зависимости от инструмента, с которым работаю (скоро про один из них подробно расскажу, здесь же добавлю ссылку на статью). В некоторые актуальные инструменты я периодически вношу изменения, добавляя что-то новое: например, недавно в один из инструментов добавил встроенный инструмент XYZ-анализа (по всем клиентам и номенклатурам).

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

Инструмент для распределения промо-объемов

Данный инструмент не может похвастаться огромным функционалом, но его наличие очень облегчает работу с промо-объемами. Суть его заключается в том, что основываясь на графике отгрузок/доставок и механике промо (что такое механика промо — я писал здесь), инструмент выбирает оптимальные дни доставки промо-объемов клиенту, показывает, в какие дни должна осуществляться отгрузка от поставщика и какой объем попадет на этот день . Все очень просто. Алгоритм работы такой:

  1. В инструмент добавляется текущая диспо-схема (то есть график отгрузок и доставок) по клиентам, у которых будет промо.
  2. Если есть понимание механики выборки промо-объемов — также вносится в инструмент, в противном случае — распределение идет по «стандартной» схеме (но по сути, стандартной схемы не существует, так как у каждого клиента механика своя. Это схема максимально приближенная к очень хорошим результатам).
  3. Затем идет поиск оптимального дня, расчет пропорции объема и визуальное отображение на неком подобии календаря. А после все сохраняется в отдельную таблицу.

Надстройка S.H.T.E.M. для Excel

Данная надстройка расширяет функционал Excel, добавляя различные функции. Присутствует инструмент XYZ и ABC-анализа, а также многофункциональный менеджер листов. Более подробно написано на отдельной странице для надстройки: вот здесь. Работа с данной надстройкой ведется постоянно (по мере своих возможностей, разумеется): добавляются новые инструменты и функции для комфортной работы.

На момент написания данной статьи, надстройка выглядит вот так:

Проекты: Надстройка VBA для Excel

Инструмент оптимизации маршрутов по заданным условиям (Тестовая версия)

Данный инструмент был разработан при подготовке к внедрению 1C:TMS в познавательных целях. Было интересно, как работает оптимизация маршрутов, при условии, что наложены ограничение на время прибытия в тот или иной город.

Алгоритм работы инструмента следующий:

  1. В инструмент вносятся первичные данные:
    • Города, участвующие в построении маршрута (макрос строит таблицу расстояний, основываясь на данных из интернета), но не больше 9, не включая город отгрузки.
    • Средняя скорость фуры (можно установить один раз и не изменять).
    • Среднее время разгрузки на одной точке.
    • Начальное время отгрузки со склада.
    • Временные рамки прибытия в тот или иной город (например: Курск, время прибытия с 28.06.2019 10:00:00 до 28.06.2019 12:00:00).
  2. Инструмент запускается, пользователь выбирает, сколько точек (из заданных 9) участвуют в построении маршрута и сколько максимум точек может быть в одном маршруте.
  3. Строятся все возможные маршруты из заданного количества точек/город.
  4. Рассчитываются расстояния маршрутов и время прибытия в каждую точку/город.
  5. Отбираются самые короткие маршруты, которые соответствуют заданным условиям.

В общем-то и все. Список всех маршрутов построен, его можно просмотреть. Оптимальные маршруты тоже построены. Время отработки инструмента колеблется, в зависимости от заданных параметров (точки участвующие в маршруте, максимум точек в одном маршруте): учитывая то, что в среднем маршрут содержит 5 городов для разгрузки, а максимум в инструменте используется 9 городов, то все маршруты (а их 15120) строятся за 20-30 секунд. А для такого инструмента это довольно быстро.

В данный инструмент, я планирую добавить еще несколько функций:

  • Ввод в инструмент количество разгрузок в одном городе (сейчас инструмент считает, что 1 город = 1 разгрузка).
  • Автоматическое достраивание маршрутов: при выборе оптимального маршрута, маршруты на другие точки/города, будут достраиваться автоматически.

Понимаю, что нет особого смысла доделывать данный инструмент, так как внедряется 1C:TMS, но работа с ним довольно интересна, да к тому же лишняя практика в VBA не помешает.

Проекты на VBA: Итоги

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