Функция VLOOKUP (ВПР) в Google таблице
Функция VLOOKUP (ВПР) ищет в диапазоне таблицы информацию по заданным параметрам и выводит ее в нужную ячейку. Искомый параметр (текст или число) должен присутствовать как в листе/таблице, где, собственно, надо искать, так и на листе/таблице, по информации которой происходит событие поиска (куда мы будем выводить искомую информацию).
- Запрос (search_key) — наше искомое значение или текст (часть текста). Если ищем текст — помещаем его в кавычки. Так же, искомым значение может выступать другая функция.
- Диапазон (range) — или таблица/столбец/строка, где мы планируем искать наш запрос. Так же можно искать данные из других документов google, указывая вместо диапазона ссылку на таблицу.
- Номер_столбца (index) — порядковый номер столбца в google документе или excel таблице от столбца, по которому мы хотим отображать информацию нашего запроса .
- [Отсортировано] ([is_sorted]) — тип поиска нашего запроса: где точное совпадение искомой информации — 0 (либо FALSE («ЛОЖЬ»)); или приблизительное — 1 ( либо TRUE «ИСТИНА»)).
Как найти нужное значение или текст в Google (Excel) таблице?
Простой пример поиска данных через ВПР в Google (Excel) таблицах.
Рассмотрим реальный пример из действующего интернет-магазина. Поставщик прислал прайс лист, в котором нам нужно накидать заказ. Но у нас:
- во-первых , не все товары из прайса представлены в нашем интернет-магазине;
- во-вторых , SKU (Артикулы) / EAN / Штрихкоды могут отличаться или иметь свои внутренние дополнения. Нам нужно как-то состыковать имеющийся у нас ассортимент с ассортиментом поставщика;
- в-третьих , сортировка наименований у нас в базе магазина и у поставщика может (чаще всего так и есть) отличаться. Это когда у нас товары отсортированы по категориям, а у поставщика по алфавиту.
Сопоставляем данные
В том же самом файле поставщика вы вставляете данные своей базы склада интернет-магазина или любой другой базы данных, по которым будете сопоставлять данные поставщика. У меня это SKU (Артикул). В том случае, если в вашей базе данных склада SKU имеют внутренние дополнения — их стоит привести к виду, какой изначально вам задавал поставщик, либо наоборот.
Виды ошибок в работе с функцией VLOOKUP (ВПР)
- В первом случае (если мы ошибочно написали SKU у себя) — просто ищем и исправляем ошибку у себя (или у поставщика, если уверены, что ошибку допустил он).
- Во втором (когда у поставщика просто временно нет этого товара и он его просто не указал в файле) — внедряем в конструкцию формулы проверку на ошибку. Дописав в конструкцию: IFERROR (ЕОШИБКА).
- В третьем (когда форматы ячеек отличаются) — внедряем в конструкцию формулы параметр, преобразующий все значения в текст. TEXT (ТЕКСТ).
Давайте внедрим сразу все дополнения в формулу функции VLOOKUP (ВПР), чтобы избавиться от всех ошибок сразу. Формула будет иметь следующий синтаксис:
Получили итоговую конструкцию с пустыми ячейками напротив тех товаров, которых на данный момент в наличии у поставщика нет. Можно формировать заказ товаров и отправлять список обратно поставщику.
Сложный пример поиска данных через ВПР в Google (Excel) таблицах
Рассмотрим довольно сложную ситуацию, в которой принимает участие формула VLOOKUP (ВПР). Она является элементом сложной многомерной формулы и выводит нужный отображаемый результат в указанной ячейке.
Задача стоит следующая: по определенным параметрам из списка статусов, находящегося в специальном столбце и его (списка статусов) положения в строках — нужно отобразить название товара из соседней вкладки со списком товаров, ссылаясь на его артикул:
- формула ищет этот ключевой статус «Facebook» во вкладке склада в определяемом формулой столбце;
- узнает номер строки, на пересечении ячейки (столбца) которой стоит этот статус;
- переходит в нужную вкладку формирования фида для массового импорта товаров в магазин на Facebook;
- считывает артикул в столбце A;
- переходит обратно во вкладку со списком товаров интернет-магазина;
- и динамически отображает название товара, соответствующее считанному артикулу из вкладки фида. Т.е., если статус изменится, отображаться в ячейке будет другой товар со статусом «Facebook», находящийся выше или ниже по строкам в списке товаров в базе данных склада магазина.
В итоге формируется динамический фид данных google таблиц, который передается через API в Facebook и формирует там магазин с товарами, который, в свою очередь, передает данные в Instagram, и там тоже формируется магазин с товарами.
Синтаксический вид этой формулы может изначально озадачить, но я вас уверяю, со временем вы тоже сможете разобраться в сложных составных формулах:
Стоит помнить, что функция ВПР совершает поиск только до первого удовлетворяющего условию результата. Если искомых одинаковых запросов будет несколько — функция дойдет только до первого совпадения, а остальные будет просто игнорировать.
Формула VLOOKUP (ВПР) довольно сложная в освоении, но очень полезная для повседневной работы. С ее помощью можно найти часть текста, число или значение ячейки. Поделитесь в комментариях, получилось ли у вас воспользоваться данной функцией и при решении каких задач вы ее использовали?
Лабораторная работа по теме: Работа с электронными таблицами ( Microsoft Excel)
- Запрос (search_key) — наше искомое значение или текст (часть текста). Если ищем текст — помещаем его в кавычки. Так же, искомым значение может выступать другая функция.
- Диапазон (range) — или таблица/столбец/строка, где мы планируем искать наш запрос. Так же можно искать данные из других документов google, указывая вместо диапазона ссылку на таблицу.
- Номер_столбца (index) — порядковый номер столбца в google документе или excel таблице от столбца, по которому мы хотим отображать информацию нашего запроса .
- [Отсортировано] ([is_sorted]) — тип поиска нашего запроса: где точное совпадение искомой информации — 0 (либо FALSE («ЛОЖЬ»)); или приблизительное — 1 ( либо TRUE «ИСТИНА»)).
Примечание. Отметим, что если вы растянули недостаточно или, наоборот, слишком много, то, пока не снято выделение с ячеек, вы можете увеличить или уменьшить таблицу вычислений, взявшись за нижний правый угол всего выделения и потянув вниз или вверх.
