Power Query в работе экономиста по запасам

Как оптимизировать закупочный процесс?

Как составить и рассчитать заявки на материальные ресурсы?

Как бороться с избыточными запасами?

В статье рассмотрим на примерах, какие задачи может решать экономист по материально-техническому снабжению с помощью инструмента Power Query.

Что такое Power Query

Power Query — специальная надстройка для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные (Data) — группа Скачать и преобразовать (Get & Transform).

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

К сведению

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

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

Рассмотрим решение нескольких практических задач с помощью инструмента Power Query.

Оптимизируем закупочный процесс

Одна из важных задач экономиста по снабжению — оптимизация закупочного процесса.

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

Так как крупные предприятия заказывают материальные ресурсы у многих поставщиков, да и перечень номенклатурных позиций тоже большой, сходные материалы могут заказываться под различными номенклатурными названиями. Задача экономиста по материально-техническому снабжению — найти такие «задвоения» и привести закупочную кампании к более однородному ввиду.

К примеру, масло для техники предприятие закупает и в тоннах, и в упаковках по несколько литров. Оптимальный вариант — купить все у одного поставщика. Если же покупать у одного поставщика в тоннах, а у другого — в упаковках, это значительно удлинит срок закупочного процесса.

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

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

Шаг 1. Загрузите исходные данные

Загружаем в отдельный документ (назовем его «запрос 2») исходные данные:

вкладка Данные → Получить данные → Из файла → Из книги.

Указываем путь, где находится наш документ «Заявки на материалы», и загружаем его.

Справа у вас появится окно «Запросы и подключения», в котором вы увидите загруженный документ. Можно переименовать его аналогично названию загружаемого документа «Заявки на материалы» (по умолчанию название будет «Лист 1»).

Шаг 2. Преобразуйте данные

2.1. Кликаем по запросу — откроется новое окно, где непосредственно и будет происходить запись шагов.

Чтобы название загружаемого столбца стало в запросе названием столбца, на вкладе «Главная страница» нажимаем «Использовать первую строку в качестве заголовков», так как первоначально в запросе программа ставит свое название столбца «Column1» (рис. 1а, 1б).

Теперь отсортируем сходные наименования материалов — для этого воспользуемся функцией Text.Start([Название столбца], n) (это аналог функции ЛЕВСИМВ ([строка], n), которая позволяет выделить определенное количество символов (n) слева в строке).

Для этого добавим новый столбец:

вкладка Добавление столбца → Настраиваемый столбец.

В появившемся окне прописываем функцию Text.Start([Наименование], 25) (рис. 2).

У нас получится пользовательский столбец с названиями неполными — отобраны первые 25 символов. Далее мы сможем выбрать однородные названия.

2.2. Чтобы оставить только однородные повторяющиеся названия, воспользуемся командой:

вкладка Главная страница → Сократить строки → Сохранить строки → Сохранить дубликаты.

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

2.3. Делаем сводную таблицу:

вкладка Преобразование → Группировать по.

Группировать будем по столбцу с укороченными названиями («Пользовательский» по умолчанию).

В результате сформируется новый столбец, который назовем «Количество повторений», операция — «Считать строки» (рис. 4).

В итоге мы получим наименования, которые повторяются более двух раз. В нашем случае вместо списка из 26 номенклатурных названий у нас остались 3 позиции — это существенно облегчит и ускорит закупочную деятельность (рис. 5).

2.4. Формируем таблицу, где будет видно, какие наименования были заказаны под однородными позициями.

Добавим в группировку еще один столбец — «Заказанная номенклатура».

Для этого нажимаем на «шестеренку» справа шага «Сгруппированные строки» → нажимаем «Добавление агрегирования» → называем новый столбец «Заказанная номенклатура» → операция «Все строки» (рис. 6).

Появится столбец «Заказанная номенклатура». Нажмем кнопку с двумя стрелочками и выберем для вывода столбец «Наименование» (рис. 7).

Так мы получим таблицу (рис. 8), где:

• в первом столбце будут однородные названия материала;

• во втором — сколько номенклатуры изначально было заказно для однородных позиций;

• в третьем — названия заказанных материалов.

В конце для вывода в готовом виде нажимаем «Закрыть и загрузить» и наша конечная таблица выгрузится в документ Excel.