Здравствуйте, друзья. Сегодня объединяем две таблицы классическим способом – с помощью функции ВПР.

Пусть у нас есть две таблицы. Первая – номенклатура, перечень товаров с информацией о ценах и остатках. Вторая – заказ, в котором указаны только уникальные идентификаторы товара (ID) и количество заказанных единиц.

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

Требуется во вторую таблицу добавить из номенклатуры наименование, остатки, цену. Это легко делается с помощью ВПР: =ВПР($F3;номенклатура;2;0)

Здесь вместо диапазона с данными (A3:D21) у меня написано слово «номенклатура», и такая формула работает. Я всего лишь задал диапазону имя, чтобы формула была более читаемой. Об именовании диапазонов я рассказывал в этой статье.

простая функция ВПР

Мы получили результат, но я не очень доволен формулой. В третьем аргументе указана двойка, т.е. выводить второй столбец номенклатуры («Наименование»). Для колонок с остатками и ценой нужно вручную задавать другие номера столбцов. В нашем случае это не критично, но если будем заполнять десяток столбцов – лучше автоматизировать.

Чтобы программа сама получила номер столбца для вывода, ищем его по имени с помощью функции ПОИСКПОЗ: =ПОИСКПОЗ(G$2;шапка;0). Здесь именованный диапазон «шапка» — это интервал A2:D2 – заголовки колонок у таблицы с номенклатурой.

Эту функцию вставляем в ВПР в качестве третьего аргумента, получаем: =ВПР($F3;номенклатура;ПОИСКПОЗ(G$2;шапка;0);0). Теперь формула адаптивна, копируем ее во все пустые ячейки заказа:

ВПР с вычислением столбца

Все данные были найдены с помощью одной лишь формулы. Можно было бы на этом остановиться, но мы рассмотрим еще один момент. Если в заказе случайно появится ID, которого нет в номенклатуре, мы получим ошибку, и сломаются даже формулы суммирования:

ошибка в ВПР

Чтобы этого избежать, можно применить функции обработки ошибок и возвращать ноль в том случае, когда результат формулы – ошибка. Для этого, обернем формулу поиска в функцию ЕСЛИОШИБКА. Вот так:

=ЕСЛИОШИБКА(ВПР($F3;номенклатура;ПОИСКПОЗ(G$2;$A$2:$D$2;0);0);0)

ВПР с обработкой ошибок

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

А у меня на этом всё. Буду рад вашим вопросам и комментариям!