Функция ВПР (VLOOKUP)

Функция ВПР (Вертикальный ПРосмотр) для многих (но, надеюсь, не для вас) является вершиной эволюции в Excel. Что ж, есть много ситуаций, когда эта формула может оказаться полезной. ВПР имеет следующий синтаксис:

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

1. искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать

2. массив - диапазон ячеек, состоящий, как минимум, из двух столбцов (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества строк

3. номер_столбца - номер столбца, указанного масива, из которого будут возвращаться данные

4. тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.

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

Схема

Некоторые замечания о ВПР

Пример	Замечание
	Искомое значение ищется в ПЕРВОМ столбце массива. Это фундаментальное ограничение ВПР, которое серьёзно вредит универсальности данной функции. Многие пользователи, прикипев к ВПР, и, не зная более гибких альтернатив, вынужденно подстраивают свои таблицы под её возможности, делая нужный столбец первым.
	Поиск оснанавливается на первом совпадении результата, если тип поиска = 0 (точное совпадение) и на последнем, если тип поиска = 1 (примерное совпадение). Таким образом у вас есть возможность при помощи ВПР возвращать либо первую совпавшую строку, либо последнюю. Однако имейте в виду, что при нечётком поиске (тип поиска = 1) первый столбец массива должен быть отсортирован по возрастанию.
	ВПР поддерживает использование символов подстановки (* и ?) в параметре искомого значения. "?" - заменяет собой любой один символ, а "*" - заменяет любое количество любых символов. Если используете символы подстановки, то четвёртый параметр должен быть 0 (ЛОЖЬ).
	Нечёткий поиск часто применяют для распределения значений по каким либо диапазонам. Например, рассмотрим классический пример вычисления значения скидки для клиента в зависимости от заказанного количества товара. Как видите, эта задача идеально вписывается в возможности нечёткого поиска ВПР. Не забывайте только про сортировку.
	При поиске текстовых строк ВПР не видит разницы в регистре букв.
	Если значение не найдено, то формула возвращает код ошибки #Н/Д (#N/A). При помощи дополнительной функции ЕСЛИОШИБКА (IFERROR) эти ошибки из эстетических или иных соображений можно перехватывать и подставлять, как в этом примере, пустую строку (или что угодно другое) - в этом случае пользователи не тревожатся попусту, разглядывая загадочные #Н/Д, и, как следствие значительно реже, отвлекают квалифицированных людей от важного.

Функция ГПР (HLOOKUP)

Функция ГПР (Горизонтальный ПРосмотр) - это тот же самый ВПР, в котором строки заменили на столбцы и наоборот. ГПР имеет следующий синтаксис:

= ГПР ( искомое_значение ; массив ; номер_строки ; тип_поиска )

1. искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать

2. массив - диапазон ячеек, состоящий, как минимум, из двух строк (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества столбцов

3. номер_строки - номер строки, указанного масива, из которой будут возвращаться данные

4. тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.

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

Схема

Некоторые замечания о ГПР

Пример

Замечание

У функции ГПР один недостаток - она никому не нужна. Дело в том, что она противоречит концепции Excel: ведь при грамотном использовании таблиц искать надо в основном в вертикальном столбце, а не в горизонтальной строке. А в тех не частых случаях, когда всё-таки надо что-то найти в строке, есть более удобоваримая и универсальная функция ПОИСКПОЗ (MATCH). За сим рассмотрение ГПР заканчиваю. Хотя, собственно, все замечания справедливые для ВПР, справедливы и для ГПР.