Представьте, что вы планируете закупки расходных материалов для производства. К вам стекаются 2 потока данных: производственные планы и прогноз по наличию производственных материалов на складах. У вас несколько заводов, много видов материалов. На выходе вы обязаны предоставлять информацию по тому, какие материалы в каких количествах и когда следует закупать и куда отправить.

Рассматривая этот пример, мы научимся нескольким довольно оригинальным и эффектным приёмам, которые вы потом сможете применять для своих задач.

1. Вы узнаете универсальный метод совмещения данных из двух (и более) таблиц, имеющих разные форматы

2. Вы узнаете, как использовать сводные таблицы для получения отчёта с нарастающим итогом

3. Мы будем использовать: умные таблицы, именованные диапазоны, формулы ИНДЕКС (INDEX), ЕСЛИ (IF), ПОИСКПОЗ (MATCH), СТОЛБЕЦ (COLUMN), СТРОКА (ROW), ЧСТРОК (ROWS) и сводные таблицы

4. Вы увидите отличную иллюстрацию синтеза вышеперечисленных инструментов Excel для достижения впечатляющих результатов

Данные на входе

Лист REQ содержит планы использования материалов (компоненты) для производства конечной продукции.

Например, компонент P49 потребуется на зводе L01 в количестве 58 235 штук к 26 мая 2015 года. Обратите внимания, что суммы отрицательные, в отличие от следующей таблицы. Это нам пригодится.

Лист STK отражает процесс поступления материалов на склады заводов.

Например, материал P97 в количестве 229 784 штук 7 апреля 2015 года поступит на склад завода L01, так как есть соответствующий контракт с производителем этого материала.

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

Задача

Итак, у нас с вами есть поток событий, которые уменьшают запасы материалов на складах (производство) и поток событий, которые увеличивают запасы (закупки). Всё что нам надо - это выстроить эти события на одной временной шкале и следить, чтобы уровень складских запасов не становился отрицательным. Отрицательный уровень запасов говорит о том, что для производства не хватает материалов. Многие крупные компании имеют штат людей, которые занимаются примерно такой работой, которую я сейчас описал. В данном случае моя задача, показать пути, как это можно делать в Excel с минимальным количеством усилий и с известной долей изящества.

Файл примера

Скачать

Объединяем таблицы

Объединять таблицы будем.... формулами. То есть в ячейках нашей объединенной таблицы будут такие формулы, которые сначала выведут все строки таблицы REQ, а затем все строки таблицы STK. И всё это будет сделано с учётом того, что у всех таблиц разная структура. На этом этапе мы совершенно не будем заботиться о сортировке строк - пусть идут, как идут.

1. Исходные таблицы оформляем в виде умных таблиц, присваивая им соответствующие идентификаторы: лист REQ - умная таблица tblREQ, лист STK - tblSTK.

2. Теперь перейдём на лист Combine. Наша объединенная таблица должна состоять из следующих столбцов: Компонент, Завод, Срок, Кол-во, где Срок - это либо дата производства, либо дата поступления материала на склад. Кроме этого добавляем 2 вспомогательных столбца: Таблица и Строка. Если ячейка столбца Таблица содержит 1, то данные извлекаются из таблицы tblREQ, если 2 - то tblSTK. Ячейки столбца Строка будут подсказывать, из какой строки соответствующей таблицы брать данные.

3. Формула для колонки Таблица выглядит так:

   =ЕСЛИ( СТРОКА(1:1) <= ЧСТРОК(tblREQ); 1; ЕСЛИ( СТРОКА(1:1) <= ЧСТРОК(tblSTK) + ЧСТРОК(tblREQ); 2; 0) )

   Тут интересна конструкция СТРОКА(1:1). Если вы никогда её не применяли, то срочно возьмите на заметку - это счётчик строк. Применяйте её, когда вам необходим в первой строке столбца с формулой получить 1, во второй строке - 2 и так далее. То же самое для счётчика столбцов СТОЛБЕЦ(A:A).

   Таким образом, мы сравниваем счётчик с количеством строк в каждой таблице. Помните, что ссылка на умную таблицу возвращает массив данных БЕЗ заголовков, нам как раз это и надо. Если мы вышли за границы обеих таблиц, то формула возвращает ноль.

4. Формула для колонки Строка весьма похожа:

   =ЕСЛИ( СТРОКА(1:1) <= ЧСТРОК(tblREQ); СТРОКА(1:1); ЕСЛИ(СТРОКА(1:1) <= ЧСТРОК(tblSTK) + ЧСТРОК(tblREQ); СТРОКА(1:1) - ЧСТРОК(tblREQ); 0) )

5. Также на листе Combine создана вспомогательная умная табличка tblMap (базируется на диапазоне L1:M5), которая нужна как справочник, чтобы поставить в соответствие номера столбцов объединенной таблицы с номерами столбцов исходных таблиц. К примеру, в обединенной таблице второй столбец это Завод, - смотрим ВТОРУЮ строку tblMap и видим, что первая колонка отсылает нас к номеру 3 - то есть поле завод в таблице tblREQ стоит на третьей позиции, вторая колонка отсылает к номеру 6 - поле завод в таблице tblSTK стоит на шестой позиции.

6. Теперь у нас есть всё, чтобы прописать формулу для столбцов Компонент, Завод, Срок, Кол-во объединенной таблицы. Известное изящество заключается в том, что формула будет совершенно одинаковой, как для строки, так и для всех столбцов! Вот наша красавица:

   

   тут:

   • Первый слой, состоящий из формулы ЕСЛИ следит за тем, чтобы мы не пытались извлечь данные за пределами таблиц, когда поле Таблица содержит ноль

   • Для извлечения данных вполне естественно используется супер-формула ИНДЕКС. Кто не читал ещё мою статью про эту формулу, настоятельно рекомендую это сделать.

   • ВЫБОР( $E2; tblREQ; tblSTK ) возвращает нам нужную таблицу в зависимости от селектора $E2 (колонка Таблица). Эту конструкцию также полезно будет запомнить на будущее.

   • Вторая формула ИНДЕКС используется для извлечения правильного номера столбца. Как видите она достаточно хитрая, в том плане что ищет необходимый столбец (либо T1, либо T2) в заголовках таблицы tblMap через ПОИСКПОЗ и конкатенацию буквы "T" с селектором таблицы.

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

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

=Combine!$A$1:ИНДЕКС( Combine!$F:$F; СЧЁТЕСЛИ( Combine!$F:$F; ">0" ) + 1 )

Это стандартный подход, рассматренный тут.

Сводная таблица

Вот сейчас будет важно, очень многие этого не понимают:

Всё, что может быть сделано при помощи сводных таблиц, должно быть сделано при помощи сводных таблиц.

Это вопрос ваших трудозатрат, эффективности вашей работы. Сводные таблицы - ключевой инструмент Excel. Инструмент чрезвычайно мощный и простой ОДНОВРЕМЕННО. Понимаете, одновременно!

Итак, сводную таблицу строим на основе ИД rngCombined. Настройки все стандартные:

Поле Кол-во я переименовал в Запасы. Операция по этому полю само-собой суммирование плюс вот такая настройка:

Этим мы получаем нарастающий итог по запасам материала в разрезе Компонент-Завод. И всё, что нам остаётся делать - это отслеживать и не допускать появления отрицательных запасов. Например, смотрим отрицательное значение в строке 34 сводной таблицы. Оно означает, что на заводе L02 2 июня 2015 года запланировано производство с участием материала P97 и, учитывая объём запланированного производства, нам не хватит 22 584 штук материала P97. Смотрим в таблицу REQ и убеждаемся, что действительно 2 июня завод L02 хочет производить что-то с использованием 57 646 штук P97, а на складах у нас на этот день такого количества не будет. В финансах это называется "кассовый разрыв". Вещь очень печальная :)

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

Читайте также:

• 7 причин полюбить именованные диапазоны

• В помощь аналитику (часть 1)

• В помощь аналитику (часть 2)

• В помощь аналитику (часть 3)

• Введение в сводные таблицы

• Наиважнейшая формула Excel

• Усовершенствованные динамические именованные диапазоны

• Ценные советы начинающим аналитикам