Автоматизация Excel для полиграфии: обработка заказов

Обзор кейса

Компания ID Line производит широкий спектр рекламно-полиграфической продукции, в т.ч. бейджи, значки, магниты, бирки, т.д. для розничной, туристической, медицинской, спортивной и других индустрий.

Нет необходимости говорить, что для компании, которая получает сотни объемных заказов в день, управление заказами является стратегически важным процессом. Ни один заказ не должен затеряться. Каждый должен быть выполнен в срок. При печати продукции по индивидуальным заказам, просчеты могут обойтись очень дорого. В общем, ошибки недопустимы.

ID Line применяет современные технологии как в части сопровождения клиентов, так и на производстве. Клиенты размещают заказы в онлайн системе (Badgerelease.com). Заказы, подтвержденные бухгалтерией, обрабатывают, а их спецификации направляют в производственную систему.

Чтобы перенести данные из одной системы в другую, многие компании прибегают к Excel. Excel – достаточно мощный и гибкий для этой задачи инструмент. В полиграфии ID Line Excel является общим знаменателем систем размещения заказов, бухгалтерии и производства.

Тем не менее, перенос данных – это всегда потенциально слабое место. Длительная и кропотливая обработка «съедает» время и ресурсы и всегда сопряжена с человеческим фактором. Чтобы улучшить продуктивность этого процесса и сократить число операций вручную, Пол Альбус, руководитель ИТ отдела, уже применял надстройки XLTools.

Следующим шагом была более глубокая интеграция XLTools в существующий процесс обработки данных, и мы помогли настроить кастомизированную автоматизацию Excel без VBA:

Разберем весь процесс шаг за шагом и рассмотрим, как были автоматизированы некоторые проблематичные задачи.

Подготовка подтвержденных заказов: извлечь данные из CSV в Excel с сохранением нулей

Информация о подтвержденных заказах поступает из бухгалтерской системы в формате CSV. Сложность в том, что недостаточно просто открыть файл ORDERS.CSV в Excel. Здесь мы сталкиваемся с типичной проблемой «съеденных» нулей в номерах заказов (столбец A). Поэтому данные нужно будет еще подготовить.

• Извлечь данные из “ORDERS.CSV”. Обратите внимание, файл должен быть в той же папке, что и ваш файл автоматизации.
• Извлечь диапазон A:D на листе “ORDERS”
• Назвать таблицу результата “ORDERS”
• Добавить заголовки столбцов “Confirmation#”, “Die”, “Finish”,“Sides”
• Сохранить формат ячеек (PreserveFormat)
• Поместить таблицу на новый скрытый лист “ORDERS”.

Обратите внимание: таблицы, которые мы получим в ходе промежуточных операций, здесь и далее мы будем размещать на скрытых листах.

Таким образом автоматизация будет проходить чисто, без генерации лишних промежуточных файлов.

После извлечения получим следующую таблицу в Excel:

Теперь приступим к форматированию. Мы решили использовать SQL запросы. Этот многогранный инструмент позволит быстро получить нужный результат. С XLTools вы можете создавать запросы к таблицам Excel, не покидая интерфейс Excel.

• Возвращаем лидирующие нули значениям в столбце “Confirmation#”:

  В случае (CASE WHEN), если в цепочке менее 9 знаков, добавляем десять нулей впереди. Потом извлекаем (SUBSTR) из строки 10 знаков справа.

  При работе вручную, обычно используют функцию CONCATINATE. Но для этого требуется добавить новый столбец, заменить функции на значения, т.д. С SQL запросом эту же задачу можно выполнить в разы быстрее.

• Берем столбец “Die” как есть

• Убираем лишнюю информацию из столбца “Finish”:

  Заменяем (REPLACE) ненужные нам элементы ': FPG-07|VIN15' на пустое значение ‘’.

• Берем столбец “Sides” как есть

• Сохраняем формат ячеек (PreserveFormat)

• Присваиваем таблице результата название “ORDERS”

• Помещаем результат на новый скрытый лист “ORDERS”. Обратите внимание, что этот новый лист заменит тот, что был сформирован в прошлом шаге при извлечении данных командой ExtractTable.

После выполнения этого запроса, номерам подтвержденных заказов (Confirmation#) возвращены лидирующие нули, а значения в столбце “Finish” стандартизированы:

Теперь таблицу можно использовать в дальнейших операциях.

Подготовка спецификаций заказов: автоматически объединить несколько листов в один

Спецификации заказов поступают из системы размещения заказов в файле MasterFile.xlsx. Этот сводный файл состоит из множества листов; каждый лист содержит заказы, размещенные за один день.

• Берем книгу MasterFile.xlsx
• Объединяем все листы в один сводный лист (AllIntoOne)
• Называем сводную таблицу “MASTERDATA”
• Указываем, что в таблицах есть заголовки
• Сохраняем формат ячеек
• Размещаем таблицу результата на новый скрытый лист “MASTERDATA”

После выполнения команды таблицы со всех листов объединены на одном листе под единым заголовком:

Объединить таблицы с помощью SQL запроса вместо VLOOKUP

Когда обе таблицы (ORDERS и MASTERDATA) отформатированы нужным образом, объединяем их по общему полю “Confirmation#”.

Если вам нужно дополнить таблицу MASTERDATA столбцами из таблицы ORDERS, вы могли бы применить функцию VLOOKUP. Но есть проблема: несколько записей могут иметь одинаковый номер заказа (Confirmation#). В этом случае использовать VLOOKUP будет некорректно, т.к. функция возьмет в расчет только первую запись.

Указываем, какие поля выбрать (SELECT):

• Первым делом, по просьбе Пола, нужно объединить значения по строкам в столбцах “Die”, “Sides” и “Finish”. Называем новый столбец “Die”.

  Этот запрос решает ту же задачу, что и наша надстройка Объединить данные. Значения в этих трех столбцах будут объединены в одну строку с дефисом “-“ в качестве разделителя. В то же время, если некоторые ячейки окажутся пустыми (ifnull), они будут пропущены.

• Берем несколько столбцов как есть: “Full Name”, “Title”, “Company”, “Photo”, “Art#”.

• Берем столбец “Confirmation#2” и переименовываем в “Confirmation#”.

• Берем данные из (FROM) таблицы “MASTERDATA” и объединяем (LEFT JOIN ON) с таблицей “ORDERS” по общему полю “Confirmation#”

• Добавляем условие (WHERE), что значение в “Confirmation#” в таблице 2 не должно быть пустым (NOT NULL)

• Сортируем (ORDER BY) таблицу результата по значениям в столбцах “Die” и “Confirmation#2”. Так мы применим сложную сортировку – сначала все заказы будут отсортированы по типу полиграфических работ (Die), а потом по номеру заказа (Confirmation#).

Указываем, как выгрузить результаты запроса:

• Сохранить формат ячеек
• Назвать таблицу результата “OUTPUT”
• Поместить результат на новый скрытый лист “OUTPUT”

Выполнив команду, получаем такую объединенную таблицу:

Автоматически разбить таблицу по нескольким листам на основе значений в столбце

До того, как Пол сможет направить объединенные данные о заказах на производство, эту большую таблицу нужно разбить на более удобные списки. Нужно разнести данные по типу полиграфических работ (Die) – значениям в столбце A.

• Выбираем диапазон “OUTPUT”

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

• Разбиваем таблицу по значениям в ключевом столбце “Die”

• При этом не выводим этот ключевой столбец в таблицы результата

• Указываем, что листы результата должны быть названы по ключевому значению (KeyValue), т.е. значению в столбце “Die”

• Прибавляем к названию каждого листа суффикс с сегодняшней датой. Чтобы имя вкладок формировалось как “КлючевоеЗначение_Сегодня”, вставим формулу:

  ="\_"&TEXT(TODAY(), "mmDDYY")

  Это еще одно преимущество XLTools Автоматизации. Поскольку все ячейки можно редактировать, вы можете при необходимости применять формулы Excel.

• Помещаем результат в новую книгу под названием “WORK.xlsx”. Она будет автоматически создана в той же папке, где находится файл автоматизации XLTools.

Выполнение команды займет пару секунд, и большая таблица “OUTPUT” будет разбита на 20-30 листов:

Сохранить множество листов в книге как отдельные файлы

И последний шаг: нам нужно сохранить все эти листы как отдельные CSV файлы. После этого Пол сможет выгрузить их в производственную систему.

• Берем книгу “WORK.xlsx”
• Сохраняем каждый лист в формате CSV. Обратите внимание, что можно установить и другой формат, например:

  • xlCSV – CSV формат (значения, разделенные запятыми). Как раз то, что нам нужно в этом проекте.
  • xlWorkbookDefault – XLSX формат. Это формат берется по умолчанию, если формат (FileFormat) не указан.
  • xlWorkbookNormal – XLS формат.
  • xlTextWindows – TXT формат (значения, разделенные табуляцией)
  • xlHtml – HTML формат.
  • xlXMLSpreadsheet – XML формат.
• Сохраняем эти файлы в папку “Work Files”. Папка будет автоматически создана там же, где находится файл автоматизации. Каждому файлу присваивается имя по названию его исходного листа
• Просим открыть папку по завершении операции.

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

Готово! Теперь файлы можно выгружать в производственную систему. Каждый файл содержит спецификации подтвержденных заказов для конкретного типа полиграфических работ.

Добавить кнопку на запуск всего процесса по одному клику

Мы разобрали все команды отдельно. Теперь можно выполнить всю последовательность сразу лишь по одной кнопке на вкладке XLTools.

Например, в проекте Пола, кнопка “Generate Work Files” запускает весь процесс:

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

• Теперь обработка данных занимает буквально минуту или меньше.
• Без ручной работы риск ошибок по человеческому фактору минимален.
• И самое главное: перенос данных из системы размещения заказов в производственную проходит гладко и точно.

Повысив продуктивность этого процесса, ID Line значительно повысила эффективность всего своего процесса управления заказами. Мы гордимся, что надстройки XLTools помогают ID Line обеспечивать кратчайшие сроки выполнения заказов, непревзойденные в рекламно-полиграфической индустрии.

У вас есть процесс в Excel, который нужно автоматизировать?