Функция VLOOKUP (ВПР) ищет в диапазоне таблицы информацию по заданным параметрам и выводит ее в нужную ячейку. Искомый параметр (текст или число) должен присутствовать как в листе/таблице, где, собственно, надо искать, так и на листе/таблице, по информации которой происходит событие поиска (куда мы будем выводить искомую информацию).
Синтаксис данной функции:
=ВПР(запрос; диапазон; номер_столбца; [отсортировано]) =VLOOKUP(search_key, range, index, [is_sorted])
- Запрос (search_key) — наше искомое значение или текст (часть текста). Если ищем текст — помещаем его в кавычки. Так же, искомым значение может выступать другая функция.
- Диапазон (range) — или таблица/столбец/строка, где мы планируем искать наш запрос. Так же можно искать данные из других документов google, указывая вместо диапазона ссылку на таблицу.
- Номер_столбца (index) — порядковый номер столбца в google документе или excel таблице от столбца, по которому мы хотим отображать информацию нашего запроса.
- [Отсортировано] ([is_sorted]) — тип поиска нашего запроса: где точное совпадение искомой информации — 0 (либо FALSE («ЛОЖЬ»)); или приблизительное — 1 ( либо TRUE «ИСТИНА»)).
Как найти нужное значение или текст в Google (Excel) таблице?
Чаще всего стоит задача найти в большом массиве данных конкретную информацию по заданным параметрам — нашему запросу. Это какой-нибудь SKU (Артикул) в прайс-листе или цены на конкретные товары. Есть ситуации, где поставщики часто изменяют какие-нибудь описания в товарах/услугах и т.д., и нам нужно так же быстро изменить эту информацию в нашей таблице. В этих случаях нам поможет функция VLOOKUP (ВПР).
Простой пример поиска данных через ВПР в Google (Excel) таблицах.
Рассмотрим реальный пример из действующего интернет-магазина. Поставщик прислал прайс лист, в котором нам нужно накидать заказ. Но у нас:
- во-первых, не все товары из прайса представлены в нашем интернет-магазине;
- во-вторых, SKU (Артикулы) / EAN / Штрихкоды могут отличаться или иметь свои внутренние дополнения. Нам нужно как-то состыковать имеющийся у нас ассортимент с ассортиментом поставщика;
- в-третьих, сортировка наименований у нас в базе магазина и у поставщика может (чаще всего так и есть) отличаться. Это когда у нас товары отсортированы по категориям, а у поставщика по алфавиту.
Сопоставляем данные
В том же самом файле поставщика вы вставляете данные своей базы склада интернет-магазина или любой другой базы данных, по которым будете сопоставлять данные поставщика. У меня это SKU (Артикул). В том случае, если в вашей базе данных склада SKU имеют внутренние дополнения — их стоит привести к виду, какой изначально вам задавал поставщик, либо наоборот.
В ячейке K2 пишем формулу, поставив знак «равно» («=»):
=VLOOKUP(G2;A:A;1;0)
=VLOOKUP(Что ищем? - BNH-19882 (ячейка G2) ; Где ищем? - Столбец А ; Номер столбца в таблице - 1 (столбец А первый от начала таблицы, в нем и есть интересующая нас информация), информацию по запросу которого хотим выводить ; Точное ( 0 ) - нам нужно точное совпадение или Приблизительное ( 1 ) совпадающее значение информации из столбца)
Виды ошибок в работе с функцией VLOOKUP (ВПР)
Вы заметили, что некоторые ячейки выдали ошибку #N/A (#Н/Д). Это потому, что в искомом столбце не нашлись некоторые артикулы, которые есть у нас в базе данных склада. Либо мы неверно написали артикул у себя в базе, либо у поставщика на данный момент просто такого товара нет в наличии и он его не отобразил, либо искомая информация у нас или у поставщика записана в другом формате (вместо числа — текст, или наоборот).
- В первом случае (если мы ошибочно написали SKU у себя) — просто ищем и исправляем ошибку у себя (или у поставщика, если уверены, что ошибку допустил он).
- Во втором (когда у поставщика просто временно нет этого товара и он его просто не указал в файле) — внедряем в конструкцию формулы проверку на ошибку. Дописав в конструкцию: IFERROR (ЕОШИБКА).
- В третьем (когда форматы ячеек отличаются) — внедряем в конструкцию формулы параметр, преобразующий все значения в текст. TEXT (ТЕКСТ).
Давайте внедрим сразу все дополнения в формулу функции VLOOKUP (ВПР), чтобы избавиться от всех ошибок сразу. Формула будет иметь следующий синтаксис:
=ЕОШИБКА(ВПР(ТЕКСТ(G2;"#");A:A;1;0);"") =IFERROR(VLOOKUP(TEXT(G2;"#");A:A;1;0);"")
Получили итоговую конструкцию с пустыми ячейками напротив тех товаров, которых на данный момент в наличии у поставщика нет. Можно формировать заказ товаров и отправлять список обратно поставщику.
Сложный пример поиска данных через ВПР в Google (Excel) таблицах
Рассмотрим довольно сложную ситуацию, в которой принимает участие формула VLOOKUP (ВПР). Она является элементом сложной многомерной формулы и выводит нужный отображаемый результат в указанной ячейке.
Задача стоит следующая: по определенным параметрам из списка статусов, находящегося в специальном столбце и его (списка статусов) положения в строках — нужно отобразить название товара из соседней вкладки со списком товаров, ссылаясь на его артикул:
- формула ищет этот ключевой статус «Facebook» во вкладке склада в определяемом формулой столбце;
- узнает номер строки, на пересечении ячейки (столбца) которой стоит этот статус;
- переходит в нужную вкладку формирования фида для массового импорта товаров в магазин на Facebook;
- считывает артикул в столбце A;
- переходит обратно во вкладку со списком товаров интернет-магазина;
- и динамически отображает название товара, соответствующее считанному артикулу из вкладки фида. Т.е., если статус изменится, отображаться в ячейке будет другой товар со статусом «Facebook», находящийся выше или ниже по строкам в списке товаров в базе данных склада магазина.
В итоге формируется динамический фид данных google таблиц, который передается через API в Facebook и формирует там магазин с товарами, который, в свою очередь, передает данные в Instagram, и там тоже формируется магазин с товарами.
Синтаксический вид этой формулы может изначально озадачить, но я вас уверяю, со временем вы тоже сможете разобраться в сложных составных формулах:
=ЕСЛИОШИБКА(ВПР(запрос; диапазон; номер_столбца; [отсортировано]);"")
=ЕСЛИОШИБКА(ВПР(ЕСЛИ(ЕСЛИОШИБКА(ИНДЕКС('Склад'!B:B;НАИМЕНЬШИЙ(ЕСЛИ('Склад'!AD:AD="Facebook";СТРОКА('Склад'!B:B);"");'Склад'!A3));"")="";ЕСЛИОШИБКА(ИНДЕКС('Склад'!B:B;НАИМЕНЬШИЙ(ЕСЛИ('Склад'!AD:AD="Facebook";СТРОКА('Склад'!B:B);"");'Склад'!A4));"");ЕСЛИОШИБКА(ИНДЕКС('Склад'!B:B;НАИМЕНЬШИЙ(ЕСЛИ('Склад'!AD:AD="Facebook";СТРОКА('Склад'!B:B);"");'Склад'!A3));""));'Склад'!B:G;6;0);"")
=IFERROR(VLOOKUP(search_key, range, index, [is_sorted]);"")
=IFERROR(VLOOKUP(IF(IFERROR(INDEX('Склад'!B:B;SMALL(IF('Склад'!AD:AD="Facebook";ROW('Склад'!B:B);"");'Склад'!A3));"")="";IFERROR(INDEX('Склад'!B:B;SMALL(IF('Склад'!AD:AD="Facebook";ROW('Склад'!B:B);"");'Склад'!A4));"");IFERROR(INDEX('Склад'!B:B;SMALL(IF('Склад'!AD:AD="Facebook";ROW('Склад'!B:B);"");'Склад'!A3));""));'Склад'!B:G;6;0);"")
Стоит помнить, что функция ВПР совершает поиск только до первого удовлетворяющего условию результата и только правее столбца с искомыми значениями. Если искомых одинаковых запросов будет несколько — функция дойдет только до первого совпадения, а остальные будет просто игнорировать.
Чтобы отображать данные из выбранного диапазона без позиционной привязки столбцов, можно воспользоваться функцией QUERY.
Формула VLOOKUP (ВПР) довольно сложная в освоении, но очень полезная для повседневной работы. С ее помощью можно найти часть текста, число или значение ячейки. Поделитесь в комментариях, получилось ли у вас воспользоваться данной функцией и при решении каких задач вы ее использовали?