Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Для поиска значения в большом списке можно использовать функцию просмотра. Функция ВПР часто используется, но можно задействовать и функции ГПР, ИНДЕКС и ПОИСКПОЗ.

Общий вид функции ВПР и ее аргументов:

=ВПР(<искомое значение>;<диапазон поиска>;<столбец>;<приблизительное соответствие>)

Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).

  • Первый аргумент (часть, необходимая для работы функции) — это искомое значение. Это может быть ссылка на ячейку, например B2, или значение, например "кузьмина" или 21500.

  • Второй аргумент — это диапазон ячеек, который, как вы предполагаете, содержит искомое значение.

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

  • Третий аргумент — это столбец в диапазоне поиска ячеек, содержащий значение, которое нужно найти.

Хотя четвертый аргумент не является обязательным, большинство пользователей вводят аргумент ЛОЖЬ (или 0). Почему? Потому что в этом случае функция будет искать точное совпадение. Можно ввести аргумент ИСТИНА или вообще не вводить аргумент, но если точное совпадение не будет найдено, функция вернет наиболее близкое приблизительное совпадение, а большинство людей приблизительное совпадение не устраивает.

Чтобы убедиться в том, что использование приблизительного совпадения может иметь серьезные последствия, предположим, что ищется цена детали с идентификатором 2345768, но вы перепутали две цифры и ввели их в формулу следующим образом: =ВПР(2345678;A1:E7;5). Формула возвращает цену на другую деталь, потому что функция ВПР нашла ближайшее число, меньшее или равное указанному (2345678). Эта ошибка может привести к неправильному выставлению счета клиенту.

Если для аргумента "приблизительное соответствие" указано значение ЛОЖЬ или 0, а точного совпадения нет, вместо неправильного значения формула возвращает в ячейку строку "#Н/Д". Это наилучшее решение. В данном случае "#Н/Д" не означает, что формула введена неправильно (за исключением неправильно введенного номера). Это означает, что номер 2345678 не был найден, потому что вы искали значение 2345768.

В этом примере показано, как работает функция. Если ввести значение в ячейку B2 (первый аргумент), функция ВПР выполняет поиск в ячейках C2:E7 (второй аргумент) и возвращает наиболее близкое приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).

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

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

Использование функции ГПР

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

Одновременное использование функций ИНДЕКС и ПОИСКПОЗ

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

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Использование функций ИНДЕКС и ПОИСКПОЗ для поиска значения

Еще о функциях поиска

К началу страницы

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×