VLOOKUP

 

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

Функция ВПР в BigQuery

Поиск по первому столбцу. Возвращает значения из позиции в столбце, для которой найдено совпадение в столбце поиска.

Пример использования

ВПР("Яблоко"; название_таблицы!фрукты; название_таблицы!цена)

Синтаксис

ВПР(запрос; диапазон; индекс; отсортировано)

• запрос – значение, по которому выполняется поиск по столбцу.
• столбец_поиска – столбец, в котором выполняется поиск.
• столбец_результата – столбец вывода результата.
• отсортировано – режим поиска соответствия для аргумента запрос (необязательная функция).
  • ЛОЖЬ – рекомендуемое значение, используемое для поиска точного соответствия.
  • ИСТИНА – значение, используемое для поиска приблизительного соответствия. Оно указывается по умолчанию, если значение аргумента отсортировано не задано.
    Совет. Прежде чем использовать функцию ВПР для поиска приблизительного соответствия, выполните сортировку по столбцу запроса в порядке возрастания. В противном случае вы можете получить неверный результат. Подробнее о том, почему функция может возвращать неверное значение…

Совет. Функция XLOOKUP позволяет использовать более гибкие запросы для поиска по базе данных в BigQuery.

Синтаксис

=ВПР(запрос; диапазон; индекс; [отсортировано])

Аргументы функции

1. запрос – значение, по которому выполняется поиск в первом столбце диапазона.
2. диапазон – верхнее и нижнее значения диапазона, в пределах которого осуществляется поиск.
3. индекс – номер столбца, в котором подбирается результат. Этот номер должен быть положительным целым числом.
4. отсортировано – необязательный аргумент, который может принимать одно из следующих значений:
   • ЛОЖЬ – рекомендуемое значение, используемое для поиска точного соответствия.
   • ИСТИНА – значение, используемое для поиска приблизительного соответствия. Оно задается по умолчанию, если значение аргумента отсортировано не задано.
     Внимание! Прежде чем использовать функцию ВПР для поиска приблизительного соответствия, необходимо выполнить сортировку по столбцу запроса в порядке возрастания. В противном случае вы можете получить неверный результат. Подробнее о том, почему функция может возвращать неверное значение…

Возвращаемое значение

Первое соответствие в выбранном диапазоне, который определяется аргументом диапазон.

Технические сведения

Пример:

=ВПР(G9; B4:D8; 3; ЛОЖЬ)

=ВПР("Яблоко"; B4:D8; 3; ИСТИНА)

Аргументы функции	Описание
запрос	Значение, по которому выполняется поиск в первом столбце диапазона, определяемого аргументом диапазон. Значение запроса должно находиться в первом столбце диапазона. В качестве запроса можно использовать ссылку на ячейку. Как проверить себя: если значение аргумента запрос находится в ячейке B3, то диапазон должен начинаться со столбца B.
диапазон	диапазон – это диапазон, для которого: В первом столбце выполняется поиск по заданному запросу. Функция ВПР возвращает значение из столбца, указанного в аргументе индекс. Также вы можете задать для нее именованный диапазон. Искомое значение должно находиться в первом столбце диапазона, заданного аргументом диапазон. В противном случае будет возвращена ошибка. Как проверить себя: если значение аргумента запрос находится в ячейке B3, то диапазон должен начинаться со столбца B.
индекс	Номер столбца в пределах диапазона, в котором содержится искомый результат. Минимальное значение – 1. Максимальное значение равно количеству столбцов в пределах диапазона. Если столбец имеет номер 1, то функция ВПР выполняет поиск по столбцу, к которому относится запрос, а также по столбцам справа от него. Совет. Когда вы работаете с функцией ВПР, представляйте себе, что столбцы в диапазоне пронумерованы слева направо, начиная с 1.
отсортировано	Это необязательный аргумент, который обозначает, что данные отсортированы. Он может принимать два значения: ИСТИНА и ЛОЖЬ. Если для аргумента отсортировано выбрано значение ИСТИНА, то функция ВПР выполняет поиск приблизительного соответствия. Внимание! Прежде чем использовать функцию ВПР для поиска приблизительного соответствия, необходимо выполнить сортировку по столбцу запроса в порядке возрастания. В противном случае вы можете получить неверный результат. Подробнее о том, почему функция может возвращать неверное значение… Если для аргумента отсортировано выбрано значение ЛОЖЬ, то функция ВПР выполняет поиск точного соответствия. Если для аргумента отсортировано значение не задано, то он по умолчанию принимает значение ИСТИНА. Чтобы результаты были качественными, мы настоятельно рекомендуем: выбирать значение ЛОЖЬ для аргумента отсортировано независимо от того, отсортированы ли значения в столбце запроса; всегда указывать аргумент отсортировано, чтобы системе было проще считывать данные, хотя он не является обязательным.

 

Результаты

Описание

Возвращаемое значение

Значение, которое возвращает функция ВПР на основе аргументов, которые вы для нее задали. В каждом случае функция ВПР возвращает только одно значение. Если запросу соответствует несколько вариантов, то значение в столбце результатов будет соответствовать первому значению аргумента из столбца запросов. Если функция возвращает ошибку #Н/Д, значит искомое значение не найдено. Если при выполнении функции возвращается ошибка #Н/Д или #ЗНАЧ!, советуем ознакомиться с разделом Рекомендации по использованию и устранение неполадок. Если вы хотите, чтобы вместо ошибки #Н/Д возвращалось другое значение, узнайте, как использовать функцию ЕСНД() вместе с функцией ВПР().

Примеры использования функции ВПР

Результаты при различных запросах

Найдите цену апельсина и яблока при помощи функции ВПР.

Попробовать

Описание

Функцию ВПР можно использовать с различными значениями запроса, например "Apple" ("Яблоко") или "Orange" ("Апельсин").

Значение, получаемое с помощью запроса, должно находиться в первом столбце диапазона. В качестве запроса можно использовать адрес ячейки, например "G9".

Для запроса указано значение "Апельсин".	=ВПР("Апельсин"; B4:D8; 3; ЛОЖЬ) Возвращаемое значение – "1,01 долл. США".
Для запроса указано значение "Яблоко".	=ВПР("Яблоко"; B4:D8; 3; ЛОЖЬ) Возвращаемое значение – "1,5 долл. США".
В качестве значения запроса используется адрес ячейки "G9", в которой написано "Яблоко".	=ВПР(G9; B4:D8; 3; ЛОЖЬ) Возвращаемое значение – "1,5 долл. США".

Как функция ВПР работает с разными номерами столбцов

При помощи функции ВПР можно узнать количество апельсинов, которое указано во втором столбце.

Попробовать

Описание

При работе с функцией ВПР представляйте себе, что столбцы диапазона пронумерованы слева направо, начиная с 1. Чтобы найти искомую информацию, необходимо указать номер столбца. Например, чтобы узнать количество, нужно выбрать значение "2" для номера столбца.

С помощью аргумента индекс для номера столбца задано значение "2". Узнайте количество апельсинов, указанное во втором столбце диапазона.

=ВПР(G3; B4:D8; 2; ЛОЖЬ) Возвращаемое значение – "5".

Точное или приблизительное соответствие

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

Попробовать

Описание

Чтобы выполнить поиск максимально близкого, но не точного соответствия, задайте значение ИСТИНА для аргумента отсортировано.

Если вы хотите найти идентификатор, близкий по значению к 102, которого нет в таблице, то возвращаемое значение будет равно 101. Это ближайшее значение к искомому, которое меньше 102.

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

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

При поиске точного соответствия необходимо выбрать значение ЛОЖЬ для аргумента отсортировано. Предположим, название фрукта под идентификатором 103 – "Banana" ("Банан"). При отсутствии точного соответствия функция вернет ошибку #Н/Д. Поскольку результаты точного соответствия более предсказуемы, мы рекомендуем использовать этот вариант.

Точное соответствие	=ВПР(G6; A4:D8; 2; ЛОЖЬ) Возвращаемое значение – "Яблоко".
Приблизительное соответствие	=ВПР(G3; A4:D8; 2; ИСТИНА) ИЛИ =ВПР(G3; A4:D8; 2) Возвращаемое значение – "Банан".

Распространенные варианты использования функции ВПР

Замена значения, возвращаемого в результате ошибки

Если вы хотите, чтобы вместо ошибки, возникающей при отсутствии искомого запроса (#Н/Д), функция ВПР возвращала другое значение, вы можете воспользоваться функцией ЕСНД(). Подробнее о функции ЕСНД()…

Попробовать

Ошибка #Н/Д при выполнении функции ВПР возникает из-за того, что запрос "Pencil" ("Карандаш") отсутствует в столбце "Fruit" ("Фрукт"). Функция ЕСНД() позволяет заменить #Н/Д другим значением. В нашем случае это фраза "NOT FOUND" ("НЕ НАЙДЕНО").

=ЕСНД(ВПР(G3; B4:D8; 3; ЛОЖЬ);"НЕ НАЙДЕНО") Возвращаемое значение – "НЕ НАЙДЕНО".

Совет. Если вы хотите заменить значение, возвращаемое при ошибке #ССЫЛ!, ознакомьтесь с информацией о функции ЕСЛИОШИБКА().

Как функция ВПР работает с несколькими критериями

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

Попробовать

1. Создайте столбец "Helper" ("Помощник") и при помощи оператора "&" объедините в нем имя и фамилию.	В ячейке B4 укажите формулу =C4&D4 и протащите ее до ячейки B8.
2. В качестве запроса используйте адрес ячейки B7 – "John Lee".	=ВПР(B7; B4:E8; 4; ЛОЖЬ) Возвращаемое значение – "Support" ("Поддержка").

Подстановочные знаки и частичные соответствия

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

• знак вопроса "?" соответствует любому символу;
• звездочка "*" соответствует любой последовательности символов.

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

Попробовать

Сочетание символов "St*" можно использовать для любых слов, начинающихся на "St", независимо от их длины, например "Steve", "St1", "Stock" или "Steeeeeeve".

=ВПР("St*"; B4:D8; 3; ЛОЖЬ) Возвращаемое значение – "Marketing" ("Маркетинг").

Рекомендации по использованию и устранение неполадок

Неверное значение результата

• Неверное значение результата. Если для аргумента отсортировано выбрано значение ИСТИНА, но первый столбец диапазона не отсортирован в порядке возрастания, измените значение аргумента на ЛОЖЬ.

• ВПР возвращает только первое соответствие. Если вашему запросу соответствует несколько значений, то возвращаемое значение может не совпадать с тем, которое вы ожидаете получить.
• Неточные данные. Значения, содержащие лишние пробелы, рассматриваются функцией ВПР как отличные от запроса, даже если на первый взгляд они выглядят одинаково. Например, следующие значения воспринимаются функцией ВПР как различные:
  • " Яблоко"
  • "Яблоко "
  • "Яблоко"

В связи с этим мы рекомендуем перед использованием функции ВПР проверять данные на отсутствие лишних пробелов.

Более подробная информация представлена в нашем разделе с рекомендациями.

#Н/Д

• Таким будет результат выполнения функции ВПР при поиске приблизительного соответствия (или если для аргумента отсортировано выбрано значение ИСТИНА) в случае, когда значение запроса в функции ВПР меньше минимального значения в первом столбце.
• Таким будет результат выполнения функции ВПР при поиске точного соответствия (или если для аргумента отсортировано выбрано значение ЛОЖЬ) в случае, когда значение запроса не найдено в первом столбце. Если вы хотите, чтобы вместо ошибки #Н/Д возвращалось другое значение, вы можете воспользоваться функцией ЕСНД().

#ССЫЛ!

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

• сосчитайте столбцы выбранного диапазона, а не всей таблицы;
• начинайте отсчет с номера 1, а не 0.

#ЗНАЧ!

Ошибка #ЗНАЧ! может возникнуть по следующим причинам:

• вы неверно указали номер столбца в аргументе индекс;
• значение аргумента индекс меньше 1. Значение аргумента индекс должно быть не меньше 1 и не больше числа столбцов в диапазоне. Если значение аргумента индекс равно 1, то функция ВПР выполняет поиск только по столбцу запроса. В противном случае поиск выполняется в столбцах справа от него.

Внимание! Значением аргумента индекс может быть только число.

#ИМЯ?

• Эта ошибка может возникнуть, если вы указываете запрос с помощью аргумента запрос в виде текста и забыли заключить этот текст в кавычки.

Рекомендации

 

Что нужно предпринять	Причина
Используйте абсолютные ссылки при указании диапазона.	Рекомендуется: использовать абсолютные ссылки при указании диапазона для функции ВПР; применять синтаксис ВПР(G3; $B$3:$D$7; 3; ЛОЖЬ). Не рекомендуется: использовать синтаксис ВПР(G3; B3:D7; 3; ЛОЖЬ). Это позволит избежать непредвиденного изменения диапазона при его копировании или перетаскивании.
Отсортируйте диапазон по первому столбцу в порядке возрастания перед выполнением поиска приблизительного соответствия (при котором для аргумента отсортировано выбрано значение ИСТИНА).	Прежде чем использовать функцию ВПР для поиска приблизительного соответствия (когда для аргумента отсортировано выбрано значение ИСТИНА), отсортируйте данные в столбце запроса в порядке возрастания. В противном случае вы можете получить неверный результат. Подробнее о сортировке…
Перед использованием функции ВПР проверяйте данные на отсутствие лишних пробелов.	Прежде чем использовать функцию ВПР, проверьте данные на отсутствие лишних пробелов. Наличие лишних пробелов может привести к неверному результату. Часто встречающиеся ошибки в данных: лишний пробел перед словом: " яблоко"; лишний пробел после слова: "яблоко "; несовпадающее число пробелов: "" и " ". Чтобы очистить данные от лишних пробелов перед словом и после него, перейдите в раздел Данные Очистка данных Удалить пробелы.
Не указывайте числовые значения и даты в текстовом формате.	Убедитесь, что числовые данные и даты в первом столбце диапазона ВПР указаны не в текстовом формате. В противном случае вы можете получить неверный результат. В верхней части Таблиц выберите столбец запроса. Перейдите в меню Формат Число. Выберите вариант в соответствии с типом ваших данных: Дата. Число.