В этой статье я хотел бы поднять вопрос обработки большого объема информации с помощью функции в excel, которая называется ВПР. Показать на примере ситуации в которых уместно применять эту функцию, и наоборот, ситуации, в которых применения этой функции приведет к потере целостности данных.
Все рассматриваемые примеры, являются реальными практическими задачками, связанными с учетом складских запасов в крупной российской государственной компании, в которой я проработал порядка 10 лет.
К делу, задача:
Требуется определить, заявлены ли материалы (перечень кодов товара – Лист №1), необходимые к поставке для обеспечения бесперебойного производственного процесса (заявка – Лист №2).
Решение:
В перечне кодов, которые требуется найти (Лист №1), встаем на ячейку B2 и вписываем формулу:
=ВПР(A2;'Заявка в которой ищем'!$D$2:$L$392;2;0)
Разберем эту функцию, по параметрам:
1. А2 – первый параметр, здесь мы указываем код товара, который будем искать (в данном случае это код – 3414700001)
2. 'Заявка в которой ищем'!$D$2:$L$392 – второй параметр, здесь мы указываем диапазон, в котором мы будет искать первый параметр.
Очевидно, 'Заявка в которой ищем'! – это название Листа №2, а $D$2:$L$392 – это диапазон, в котором ищем. Диапазон указан со знаком $, чтобы его зафиксировать (кнопка на клавиатуре F4).
3. 2 – третий параметр, здесь мы указываем столбец, по порядку, начиная с первого в диапазоне, в котором находится искомая информация, которую надо вывести (в данном случае это столбец – D ).
4. Последний параметр указываем 0 (значение этого параметра, как правило, всегда указываем равным 0, другие более частные случае разберем в другой статье)
В ячейке C2, вписываем формулу:
=ВПР(A2;'Заявка в которой ищем'!$D$2:$L$392;3;0)
В формуле заменяем номер столбца (3-ий параметр) на значение 3. И так далее с остальными столбцами.
Результат:
Не трудно сделать вывод, что строки (материалы), в которых отобразились данные (наименование, чертеж и прочее), имеются в наличии. А материалы со значениями "Н/Д" в заявке отсутствуют и их требуются дозаказать.
Выводы:
- В данном конкретном примере, где необходимым и достаточным было ответить на вопрос – «Имеются ли материалы в заявке? - Да/Нет» функция ВПР является наиболее предпочтительной.
- Если бы в задаче требовалось определить наличие материалов в заявке и вывести их количество, зная, что в заявке много повторяющихся строк с данными кодами (разные периоды, разные регионы и т.д.), функция ВПР вывела бы первое найденное значения, а остальные совпадения были бы проигнорированы.
В данном случае требовалось бы консолидировать заявку (к примеру, с помощью сводной таблицы) и работать только с уникальными кодами, но об этом мы поговорим в другой статье.
- Или другая задачка, определить наличие материалов в заявке и вывести их количество по конкретному региону или периоду.
При решении данной задачки, одним из множеств решений было бы формирование составного уникального ключа для каждой строки, детально данный метод обсудим в другой статье.
