Главная » Статьи » Excel » Формулы рабочего листа

Создание базового списка

Чем люди занимаются в офисах? Довольно типовой вариант: собрать из нескольких источников некие данные, произвести некоторые расчёты, проконтролировать какие-то товарно-денежно-сырьевые операции или запланировать эти операции по результатам расчётов, ну и, само собой, взять ответственность за свои расчёты, контроли, прогнозы. В подавляющем большинстве случае всё это делается в Excel.

Так вот - в самом начале этих типовых операций, в процессе объединения нескольких источников данных, как правило, возникает задача выработать базовый список чего-то (материалов, товаров, операций), от которого потом приходится "плясать". Приведу пример из моей недавней статьи, где мы планировали снабжение производства расходными материалами. Для планирования закупок расходных материалов мы должны совместить данные из производственного плана и данные со складов расходных материалов. Эти два источника данных в реальной жизни никогда не совпадут друг с другом. Всегда будут какие-то позиции, которые есть на складах, но они пока (или уже) не участвуют в производстве, а также возможны ситуации, что появился новый расходный материал, который ещё ни разу не закупался, но включен в производственный план. То есть нам позарез нужен базовый список расходных материалов, который бы включал в себя все варианты материалов, которые есть в обоих источниках. Без повторений, отсортированный - всё как положено.

Совершенно точно вам заявляю, что подобные базовые списки формируются в подавляющем большинстве случаев в полуручном режиме. Обычно происходит так: в начале процесса вы при помощи сводных таблиц или руками формируете стартовый базовый список, потом его при помощи формул ВПР выверяете, дополняете, и вот - у вас есть СТАТИЧЕСКИЙ базовый список, который вы постоянно теперь будете сверять с источниками данных на предмет, а не появились ли новые позиции. Вот на такие бессмысленные вещи люди и тратят порой до трети своего рабочего времени...

К слову сказать, я считаю, что один из главных убийц офисной продуктивности, помимо совещаний, носит неброское имя - сверка :)

Автоматизация создания базового списка

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

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

У нас будет 3 источника данных, на основе которых мы будем строить базовый список. Данные текстовые. В каждой таблице - один единственный столбец, содержащий мужские имена. Во всёх трёх таблицах разное количество строк, имена пересекаются произвольно и могут произврольно повторяться в любых сочетаниях. Мы не предъявляем никаких предварительных условий, кроме того, что данные текстовые и, что мы примерно знаем, с каким количеством строк мы будем иметь дело (формулы должны быть протянуты на достаточную длинну с запасом).

Файл примера

Скачать


Шаг 1 - Оформляем исходные данные

Как обычно, все три исходные таблицы на листе Data делаем умными и даём им идентификаторы tblOne, tblTwo и tblThree.

Шаг 2 - Создаём объединенную таблицу

На листе Trick создаём объединенную умную таблицу tblCommon. Она состоит из трёх столбцов: таблица, строка, значение. Индекс таблицы для столбца Таблица - число от 1 до 3, - извлекаем из вспомогательной таблицы tblStep при помощи формулы ВПР:

=ЕСЛИОШИБКА( ВПР( СТРОКА(1:1); tblStep; 2 ); 0 )

Конструкцию СТРОКА(1:1) мы уже обсуждали тут. Данное использование ВПР для диапазонного поиска обсуждалось здесь. В случае, если ВПР вернёт #Н/Д, то ЕСЛИОШИБКА подставит ноль.

Таблица tblStep адресует к диапазону E1:F5, о котором стоит рассказать подробно. Дело в том, что нам для создания объединенной таблицы - кровь из носа - необходимо знать количество строк в исходных таблицах. Их я легко получаю в ячейках E2:E4 при помощи формулы ЧСТРОК. Для E2 это, к примеру:

=ЧСТРОК( tblOne )

Далее на ячейку E2 я должен был бы сослаться в таблице tblStep в столбце Порог. Однако, если мы так сделаем, то наша таблица будет так тормозить, что между калькуляциями формул можно будет выкуривать по маленькой цигарке. Дело в том, что формула ЧСТРОК летучая (volatile) и пересчитывается при любом нашем шевелении в Excel. А поскольку на E2:E4 завязано буквально всё, то в Excel при любом действии начинается цепная реакция пересчётов формул, что реально занимает массу времени, и с файлом работать при автоматическом пересчёте формул становится практически невозможно.

Поэтому мы идём на хитрость. Для ячеек E2:E4 (заголовок Живые) я создаю двойников в F2:F4 (заголовок Мёртвые). Если на E2 у меня ссылается именованный диапазон rngLiveCountOne, то на F2 - ИД rngDeadCountOne. Двойники не содержат формул, поэтому необходимо следить, чтобы значения в Мёртвых (Dead) диапазонах всегда были равны значениям из Живых (Live) диапазонов. Сами мы следить не хотим, значит поручим это макросу (ниже будет листинг). Ссылаясь в формулах на мёртвые ячейки, я изолирую летучие ячейки, и мой файл начинает при пересчётах вести себя адекватно. Запомните этот приём, он может вам пригодиться.

Формула для столбца Строка вычисляет номер строки таблицы:

=ЕСЛИОШИБКА( СТРОКА(1:1) - ВПР( СТРОКА(1:1); tblStep; 1 ) + 1; 0 )

Теперь мы знаем, из какой таблицы мы будем извлекать данные и из какой строки этой таблицы. Напоминаю, что на этом этапе мы просто берём 3 исходные таблицы tblOne, tblTwo и tblThree и складываем из них одну большую таблицу. Формула для строки Значение:

=ЕСЛИ( [@Таблица] > 0; ИНДЕКС( ВЫБОР( [@Таблица]; tblOne; tblTwo; tblThree); [@Строка] ); "" )

ВЫБОР( [@Таблица]; tblOne; tblTwo; tblThree) - возвращает нам нужную таблицу по её индексу в поле [@Таблица]. Если вы не знакому со структурными ссылками умных таблиц (то что вы видите в квадратных скобках), то почитать о них можно тут. По формуле ИНДЕКС у меня также есть исчерпывающая статья.

Проследнее, что необходимо сделать, это накинуть ИД на получившийся диапазон столбца Значение таблицы tblCommon. Это будет ИД rngCommon:

=tblCommon[ [#Заголовки]; [Значение]]:ИНДЕКС( tblCommon[Значение]; СЧЁТЕСЛИ( tblCommon[Значение]; "*?") )

Данный ИД нам нужен с заголовком, так как мы будем на его основе строить сводную таблицу, а также нам не нужны пустые значения снизу таблицы. Примите во внимание, что конструктция СЧЁТЕСЛИ(tblCommon[Значение];"*?") работает только с текстовыми значениями, для чисел используйте нечто типа СЧЁТЕСЛИ(tblCommon[Значение];">0")+1. СЧЁТЗ использовать нельзя, так как в ячейках везде формулы.

Шаг 3 - Перекладываем на сводную таблицу основную работу

Скомпоновав все таблицы в одну, мы со спокойным сердцем натравливаем на неё сводную таблицу, чтобы она нам сделала группировку, сортировку и фильтрацию (если бы нам это надо было). Кто-то скажет, что тоже самое можно было бы сделать при помощи формул-массивов. Наверное, можно. Я не фанат формул-массивов, так как вещь это очень специфическая и на больших объёмах данных так и вовсе бесполезная. Я, в первую очередь, как тренер, не испытываю никакого энтузиазма, глядя на формулу размером в полстраницы, потому что я знаю, что ЕЁ я никогда и никому не смогу объяснить (да и сам забуду через неделю).

Сводная таблица элементарна - единственное поле в области СТРОКИ в макете сводной таблицы. Кроме этого надо отключить ИТОГИ:

На сводную таблицу я также привычно накидываю ИД rngResult, так как именно этот ИД и будет нашим конечным продуктом.

=Trick!$H$2:ИНДЕКС( Trick!$H:$H; СЧЁТЗ( Trick!$H:$H ) )

Стандартнейшая формула.

Шаг 4 - Небольшой макрос

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

Данный макрос следит, чтобы наши "мёртвые" диапазоны всегда были в точности равны "живым" диапазонам. Кроме этого, макрос обновляет сводную таблицу. То есть, переходя на лист Result, вы можете быть уверены, что ваш базовый список содержит корректные данные.

Наслаждаемся результатом

На листе Result построена умная таблица tblWork при помощи ИД rngResult. Столбец Flag сигнализирует нам о том, не кончились ли строки в базовой таблице (значение 1 - означает, что не кончились, и его удобно проверять в формулах =ЕСЛИ([@Flag];"Do something";"Do nothing"). Формула для столбца Name проста и изящна:

=ЕСЛИ( [@Flag]; ИНДЕКС( rngResult; СТРОКА(1:1) ); "" )

а столбцы One, Two, Three просто иллюстрируют, как наш базовый список связан с исходными таблицами. Я использовал формулы вида:

=ЕСЛИ( [@Flag]; СЧЁТЕСЛИМН( tblOne; [@Name] ); "" )

Если вы не знакомы с великолепными формулами СЧЁТЕСЛИМН, СУММЕСЛИМН, СРЗНАЧЕСЛИМН, то очень рекомендую их изучить.

Общая схема трансформации данных:

Выводы

Самое главное, чему мы сейчас научились - это использовать сводную таблицу в качестве мощного промежуточного инструмента, не требующего никакого внимания от конечного пользователя. Лист Trick даже может быть скрыт без каких-либо последствий. Мы формулами подготовили ИД, на основе которого строилась сводная таблица, и мы формулами же сфотографировали через другой ИД (+ немного VBA) результат деятельности сводной таблицы. Такое использование сводных таблиц открывает перед нами богатейшие возможности по манипулированию данными.

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


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

Категория: Формулы рабочего листа | Добавил: dsb75 (30.03.2015) | Автор: Батьянов Денис E W
Просмотров: 6791 | Комментарии: 2 | Теги: выбор, СЧЁТЕСЛИ, сводные таблицы, базовый список, ЧСТРОК, ЕСЛИОШИБКА, умные таблицы, впр, базовая таблица, Индекс | Рейтинг: 5.0/1
Всего комментариев: 2
0
1 deepfox   (01.04.2015 07:10) [Материал]
Замечательно! Только файл примера не скачивается: ошибка 404

0
2 dsb75   (01.04.2015 08:29) [Материал]
Пардон. Исправил.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика