Ivan Afonin
За время работы я зачастую сталкиваюсь с тем, что при работе с различными книгами MS Excel (полученных из разных баз, от разных пользователей и проч. и проч.) возникает проблема: как сделать ту или иную информацию однородной. В то же время перейти на единый классификатор возможности есть не всегда.
В этой статье я хотел бы поделиться с читателями своим решением данной проблемы. Статья также затрагивает вопрос о работе с массивами информации: укрупнение, классификация, выборка и др.
Небольшое введение
В начале оговорюсь о термине из названия статьи - «пользовательском справочнике». Пользовательским справочником я называю некий массив данных, расположенный на отдельном листе или в отдельной книге (для удобства). Этот массив данных содержит информацию о степени группировки данных, об параметрах отображения информации и проч. Пользовательский справочник имеет сходство со справочниками 1С (как мне кажется), хотя и отдаленное.
Итак, теперь обо всем по порядку.
1. Справочник с целью унифицирования информации
Проблема: Есть данные из разных баз, от разных пользователей. Номенклатура, по сути, одна, а наименования написаны по разному. Цель данного справочника - унифицировать номенклатуру, чтобы информация стала однородной и можно было применять знакомые всем функции (СУММЕСЛИ, СЧЁТЕСЛИ, ВПР, ГПР и др.).
Например, есть данные из реестра приемо-сдаточных актов (Таблица № 1)
Таблица № 1
Реестр пса
Из таблицы № 1 сразу видна проблема - одна и та же номенклатура записана по-разному. А значит, функции СУММЕСЛИ, СЧЁТЕСЛИ корректно применить не удасться.
Решение: Создаем справочник номенклатуры, где в одном столбце перечисляем всю исходную номенклатуру, а в столбце рядом - ту же номенклатуру, но с такими названиями, с которыми мы хотели бы ее видеть.
Краткий порядок действий:
- копируем лист с исходными данными в отдельную книгу;
- создаем в этой же книге лист "справочник", где, например, в столбце A будем перечислять имеющуюся номенклатуру, а в столбце B - "правильное название", которое мы хотели бы видеть. Справочник пока не заполняем.
- на листе с исходными данными правее всех исходных данных создаем столбец, шапка которого называется "Номенклатура общая" (где будет отображаться "правильное название"), а под шапкой пишем и растягиваем вниз до конца таблицы с исходными данными формулу =ВПР (B4;справочник!A:B;2;0) (B4 - в данном примере ячейка, содержащая исходную номенклатуру). После растягивания формул до конца получим, что все значения столбца "Номенклатура общая" содержат ошибку Н/Д (мы ведь еще не заполняли справочник!);
- ставим автофильтр на столбец "Номенклатура общая" с условием Н/Д.
- начинаем заполнять лист справочник, копируя с листа с исходными данными значения столбца "Номенклатура", а напротив в ручную проставляя "правильные значения" до тех пор, пока все ошибки Н/Д не уберутся. Если на лист справочник скопировать только значения столбца "Номенклатура" (в столбец A), не проставляя "правильных значений" (в столбец B), то значение функции ВПР в данном случае стареет равно 0. Здесь есть небольшая хитрость - каждый раз при добавлении в справочник "Номенклатуры" (особенно при создании первого справочника), удобно каждый раз, заходя на лист с исходными данными обновлять автофильтр на условие Н/Д, хотя и не обязательно, т. к. значения Н/Д по мере заполнения справочника будут изменяться на "правильные значения номенклатуры", а Excel автоматически автофильтр не обновляет.
Для нашего примера, справочник может выглядеть следующим образом (Таблица № 2).
Таблица № 2
Справочник сырья
Из таблицы № 2 видим, что в 1 столбце стоят наименования сырья (по сути, одного вида), полученные из разных источников. В столбце 2 объединяем эти виды сырья в один.
Создание первого справочника обычно занятие трудоемкое. Дальше (по мере обновления рабочей книги) проще, т. к. базы и пользователи меняются не часто и количество «неправильно» введенных данных резко уменьшается.
В результате редактированный лист "Исходные данные" будет выглядеть так, как представлено в таблице № 3.
Таблица № 3
Реестр пса с добавлением столбца "Номенклатура общая"
Теперь, используя столбец "Номенклатура общая", можно корректно применять функции, подобные функциям СУММЕСЛИ и СЧЁТЕСЛИ, например, для расчета средневзвешенной цены за месяц.
2. Справочник с целью группировки
Похож на предыдущий справочник, но создается с целью группировки информации.
Проблема: если есть сырье нескольких видов (в нашем примере это А и Б) и каждый из этих видов имеет класс (цифры после букв). Для некого анализа нас не интересует класс, а интересует только вид сырья. Полученная в предыдущем разделе таблица этого сделать не позволяет.
Решение: эту задачу также решаем с помощью "Пользовательского справочника"
Краткий порядок действий:
- на исходном листе правее столбца "Номенклатура общая" создаем столбец с шапкой "Вид общий", под шапкой пишем формулу =ВПР (B4;справочник!A:C;3;0) и растягиваем вниз до конца таблицы. В данном случае значения функций ВПР будут равны нулю (ведь столбец C - не заполнен);
- на уже созданном листе "справочник" в столбце C добавляем "Вид сырья", которую заполняем вручную (также удобно пользоваться автофильтром с условием ноль, как и в прошлом разделе с условием Н/Д.
В результате перечисленных действий получим следующий результат (таблица № 4).
Таблица № 4.
Реестр пса с добавлением столбца "Вид общий"
Теперь, используя столбец "Номенклатура общая", можно корректно применять функции, подобные функциям СУММЕСЛИ и СЧЁТЕСЛИ, но уже группируя сырье по видам.
Небольшое замечание: в данном случае, при создании столбца "Вид общий" можно в функции ВПР опираться не на столбец B, а на столбец "Номенклатура общая", тогда в справочнике нужно правее создать связку Номенклатура общая - Вид общий. Это немного сэкономит время, т. к. разных значений в столбце "Номенклатура общая" меньше (а зачастую на порядок!), чем в столбце "Номенклатура". Эту связку нужно располагать на листе справочник ПРАВЕЕ (а не в коем случае не внизу) связки Номенклатура - Номенклатура общая (например, в столбце D (для удобства отображения оставляя столбец C пустым).
3. Справочник с целью отображения и учета информации
Проблема: нужны данные о поступлении сырья из реестра пса только за конкретный период, например, декаду. Опять же использование функции СУММЕСЛИ не возможно, т. к. хотя номенклатура у нас унифицирована, в случае ее применения, результат будет средний за месяц.
Решение: создаем справочник, который будет учитывать соотношение дата - декада с признаком учитывать - не учитывать.
Краткий порядок действий:
- на исходном листе правее столбца "Вид общий" создаем столбец с шапкой "Декада", под шапкой пишем формулу =ВПР (A4;справочник!E:F;2;0), где A4 - исходная дата из реестра пса и растягиваем вниз до конца таблицы. В данном случае значения функций ВПР будут равны ошибке Н/Д;
- на листе "справочник" создаем в столбцах E и F связку Дата - Декада. Такой справочник создается достаточно быстро, т. к. каждая дата элементарно привязывается к одной из 3-х декад. Можно, естественно, эту процедуру еще больше упростить, используя функцию ЕСЛИ и ДЕНЬ, хотя это и не обязательно.
- на исходном листе правее столбца "Декада" создаем столбец с шапкой "Отображать декаду", под шапкой пишем форулу =ВПР (значение декады; справочник!H:I;2;0), где значение декады - значение ячейки напротив в столбце "Декады";
- на листе "справочник" создаем в столбцах H и I связку Декада - Отображать декаду. Такой справочник создается элементарно, т. к. имеет только 3 строки и 2 столбца. По умолчанию, ставим в столбце "Отображать декаду" везде 1.
- теперь на исходном листе в графе "Отображать декаду" везде стоят значение "1".
- правее столбца "отображать декаду" делаем графы "Отображать количество" (перемножение соответствующих ячеек столбца "количество" и столбца "отображать декаду") и "Отображать Всего с НДС, руб." (перемножение соответствующих ячеек столбца "Всего с НДС, руб." и столбца "отображать декаду").
- в случае, если необходимы данные за определенную декаду, ставим на листе "справочник" напротив всех не нужных декад "0", а напротив нужной декады оставляем "1".
В результате выполнения вышеуказанных действий получаем следующие результаты (таблица № 5)
Таблица № 5
Реестр пса с подекадной разбивкой и параметрами отображения
Теперь, используя справочник отображения декады можно вывести средневзвешенную цену за определенную декаду с помощью все той же функции СУММЕСЛИ, или отследить количество поставок за декаду с помощью функции СЧЁТЕСЛИ.
Подведение итогов
Итоговый справочник на основании 3-х разделов будет выглядеть следующим образом (таблица № 6).
Таблица № 6
Итоговый справочник
С указанным в разделах 1-3 примером можно ознакомиться в формате Excel, перейдя по ссылке.
