Функции и Формулы

Функция VLOOKUP (ВПР) в Google таблице

Функция VLOOKUP (ВПР) в Google таблице
12 Июл

Функция 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 (#Н/Д). Это потому, что в искомом столбце не нашлись некоторые артикулы, которые есть у нас в базе данных склада. Либо мы неверно написали артикул у себя в базе, либо у поставщика на данный момент просто такого товара нет в наличии и он его не отобразил, либо искомая информация у нас или у поставщика записана в другом формате (вместо числа — текст, или наоборот).

  1. В первом случае (если мы ошибочно написали SKU у себя) — просто ищем и исправляем ошибку у себя (или у поставщика, если уверены, что ошибку допустил он).
  2. Во втором (когда у поставщика просто временно нет этого товара и он его просто не указал в файле) — внедряем в конструкцию формулы проверку на ошибку. Дописав в конструкцию: IFERROR (ЕОШИБКА).
  3. В третьем (когда форматы ячеек отличаются) — внедряем в конструкцию формулы параметр, преобразующий все значения в текст. TEXT (ТЕКСТ).

Давайте внедрим сразу все дополнения в формулу функции VLOOKUP (ВПР), чтобы избавиться от всех ошибок сразу. Формула будет иметь следующий синтаксис:

 =ЕОШИБКА(ВПР(ТЕКСТ(G2;"#");A:A;1;0);"")
 =IFERROR(VLOOKUP(TEXT(G2;"#");A:A;1;0);"")

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

Сложный пример поиска данных через ВПР в Google (Excel) таблицах

Рассмотрим довольно сложную ситуацию, в которой принимает участие формула VLOOKUP (ВПР). Она является элементом сложной многомерной формулы и выводит нужный отображаемый результат в указанной ячейке.

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

  1. формула ищет этот ключевой статус «Facebook» во вкладке склада в определяемом формулой столбце;
  2. узнает номер строки, на пересечении ячейки (столбца) которой стоит этот статус;
  3. переходит в нужную вкладку формирования фида для массового импорта товаров в магазин на Facebook;
  4. считывает артикул в столбце A;
  5. переходит обратно во вкладку со списком товаров интернет-магазина;
  6. и динамически отображает название товара, соответствующее считанному артикулу из вкладки фида. Т.е., если статус изменится, отображаться в ячейке будет другой товар со статусом «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 (ВПР) довольно сложная в освоении, но очень полезная для повседневной работы. С ее помощью можно найти часть текста, число или значение ячейки. Поделитесь в комментариях, получилось ли у вас воспользоваться данной функцией и при решении каких задач вы ее использовали?

Tags:

Статьи по теме