Microsoft Excel

трюкиприёмырешения

Примеры формул
10.04.2013 10948

Как в Excel осуществить поиск точного значения

Функции ВПР и ГПР полезны, если вам необходимо вернуть значение из таблицы (в диапазоне), выполняя поиск другого значения. Классический пример — формула, работающая с налоговыми ставками (рис. 120.1).

В перечне показаны различные налоговые ставки для разных уровней дохода. Следующая формула (в ячейке В3) возвращает ставку налога для значения дохода, которое хранится в ячейке В2: =ВПР(B2;D2:F7;3).

Рис. 120.1. Для поиска налоговой ставки применяется функция ВПР

Рис. 120.1. Для поиска налоговой ставки применяется функция ВПР

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

Чтобы найти только точное значение, используйте функцию ВПР (или ГПР), установив дополнительный четвертый аргумент как ЛОЖЬ. На рис. 120.2 показан рабочий лист с таблицей поиска, которая содержит номера (в столбце D) и имена сотрудников (в столбце Е). Формула в ячейке В2, которая приведена далее, ищет номер сотрудника, введенный в ячейку В1, и возвращает соответствующее имя сотрудника: =ВПР(B1;D2:E11;2;ЛОЖЬ).

Рис. 120.2. В этой таблице поиска требуется точное совпадение

Рис. 120.2. В этой таблице поиска требуется точное совпадение

Поскольку последний аргумент функции ВПР имеет значение ЛОЖЬ, функция возвращает значение только в том случае, если будет найдено точное совпадение. Если значение не найдено, формула возвращает #Н/Д. Это именно то, что вам нужно, поскольку приблизительный номер сотрудника не имеет смысла. Кроме того, обратите внимание, что номера сотрудников в столбце D идут не в порядке возрастания. Если последний аргумент функции ВПР — ЛОЖЬ, то значения не должны быть в порядке возрастания.

Если вы хотите увидеть что-то вместо #Н/Д, когда номер работника не найден, то можете указать функцию ЕСЛИОШИБКА для проверки на результат #Н/Д и подставить другую строку. Следующая формула отображает текст Не найден вместо #Н/Д: =ЕСЛИОШИБКА(ВПР(B1;D2:E11;2;ЛОЖЬ);"Не найден").

Функция ЕСЛИОШИБКА была введена в Excel 2007, поэтому, если ваша книга должна быть совместима с более ранними версиями, используйте такую формулу:
=ЕСЛИ(ЕОШИБКА(ВПР(B1;D2:E11;2;ЛОЖЬ));"Не найден";ВПР(B1;D2:E11;2;ЛОЖЬ)).

Top