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

Функция ВПР - учимся применять на примерах.

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

Строчные и прописные буквы в ВПР (VLOOKUP)

Мы упоминали выше, что при поиске текстового значения ВПР (VLOOKUP) не различает регистр букв.

Что может произойти? К примеру, если в диапазоне поиска у вас есть два наименования, одно из которых начинается с прописной буквы, а второе – со строчной, то найдено будет всегда то, которое стоит первым.

примеры функции ВПР

Как видите точное совпадение с учетом регистра находится в строке 9, но оно найдено не будет, так как в строке 5 уже найдено совпадение без учета регистра.

В итоге может быть выбрано неверное значение цены.

Для поиска текстовых значений можно использовать данные нескольких ячеек.

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

пример объединения ячеек поиска в ВПР

Заменим строку поиска выражением. В нём мы объединим имя и фамилию, а между ними вставим пробел.

Самый простой способ объединить текстовые значения - это использовать символ амперсанда (&). Попробуйте, например:

="текст 1"&"текст 2"

Так же мы можем объединять строки, ссылаясь на ячейки:

=D2&" "&E2

=ВПР (D2&" "&E2;$A$2:$B$21;2;ЛОЖЬ)

Как видите, всё просто.

Используем неточное совпадение при поиске.

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

Нечёткий поиск часто применяют для распределения значений по каким либо диапазонам. Например, рассмотрим пример вычисления значения скидки для клиента в зависимости от количества заказов. Как видите, эта задача идеально вписывается в возможности нечёткого поиска ВПР.

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

В ячейке E2 напишем

=ВПР(E2;$A$2:$B$7;2;ИСТИНА)

или

=ВПР(E2;$A$2:$B$7;2).

неточное совпадение в функции ВПР

Как только функция находит значение (50), превышающее критерий поиска (45), она останавливается и возвращает значение из предыдущей строки (из 5й строки).

Не забывайте только про сортировку первого столбца при использовании неточного совпадения!

Использование именованных диапазонов упростит работу с ВПР.

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

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

Как создать именованный диапазон?

Выделите диапазон, который вас интересует, затем  нажмите

Меню – Данные – Именованные диапазоны.

Укажите имя для вашего именованного диапазона и нажмите OK.

Помните, для указания диапазона поиска, находящегося на другом листе, мы использовали формулу.

=ВПР(A3;'прайс-лист'!$A$3:$B$21;2;ЛОЖЬ)

А теперь эта формула выглядит так:

=ВПР(A3;цены;2;ЛОЖЬ)

создание именованного диапазона

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

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

Какие здесь преимущества?

  1. У вас не рябит в глазах от букв, цифр и знаков доллара в обычных адресах диапазонов?

Формула с именованным диапазоном выглядит намного более дружественно, наглядно и понятно. Вместо скучных и безликих координат вы видите идентификаторы, которые рождают у вас некоторые ассоциации. Согласитесь, “цены” – это наверняка информация о ценах.

  1. Если по каким-то причинам вам необходимо будет изменить координаты диапазона поиска, который вы использовали в большом количестве формул – вам нужно корректировать каждую формулу или пользоваться функцией “Найти и заменить”? Согласитесь, это очень долго, трудоемко, возможны ошибки.

Используя именованный диапазон, просто нажмите

Меню – Данные – Именованные диапазоны.

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

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

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

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

Эти таблицы могут находиться на одном листе либо на разных листах. А если использовать в добавок функцию IMPORTRANGE - то даже в разных файлах.

Для этого просто объединим несколько таблиц в массив при помощи фигурных скобок. Выглядеть это будет примерно так:

=ВПР(A3,{'прайс-лист1'!$A$3:$B$21;'прайс-лист2'!$K$3:$L$21;'прайс-лист3'!$C$3:$D$21};2;ЛОЖЬ)

Как видите, таблицы могут находиться на разных листах и даже в разных столбцах.

Единственное требование - структура таблиц должна быть одинаковой.

Что это значит? Применительно к нашему случаю, нас интересует только 2 смежных столбца каждой таблицы. В первом находится то, что мы будем искать (например, наименование товара), а во втором - необходимое нам значение (например, цена этого товара). То есть, во всех таблицах наименование и цена товара должны находиться рядом в соседних столбцах.

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

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

Предположим, у нас есть таблица с оценками учеников. Нам необходимо выбрать все оценки по конкретному ученику. Формы исходной таблицы и таблицы с результатами запроса будут одинаковы. Чтобы не вводить вручную фамилию и имя и не делать при этом ошибок, сформируем в ячейке I2 выпадающий список с фамилиями учеников. Для этого воспользуемся Меню - Данные _Проверка данных. Дальнейший процесс создания списка подробно описан в нашем материале о создании выпадающих списков в Google таблицах.

Затем нам нужно определить номер столбца, в котором будет выводиться результат запроса. Для этого можно использовать функцию

=СТОЛБЕЦ()

Введя эту функцию в ячейку J2, получаем значение 10. То есть, наше значение находится в 10 столбце с начала листа. Но нас интересует номер столбца в нашей таблице. Поскольку перед таблицей у нас есть еще 8 столбцов от A до H, то преобразуем формулу в ячейке J2

=СТОЛБЕЦ()-2

Получаем значение 2. То есть, второй столбец с начала таблицы.

Теперь в ячейке J2 вводим функцию ВПР.

=ВПР($I$2;$A$2:$G$5;СТОЛБЕЦ()-8;0)

Все в этой формуле нам уже знакомо. Только вместо номера столбца 2 мы вставили формулу. И обязательно не забываем об абсолютных ссылках!

пример функции ВПР в Google таблицах

Затем просто копируем формулу в остальные ячейки нашей строки путём перетаскивания за правый нижний угол ячейки.

Теперь при выборе нового ученика его оценки автоматически меняются.

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

Надеемся, что применение функции ВПР (VLOOKUP) в Google таблицах упростит вашу работу и повысит её эффективность.