Функции впр и гпр в excel как работать

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

Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.

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

Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.

Примечание: Мастер подметок больше не доступен в Excel.

Ниже в качестве примера по выбору вы можете найти пример использования в этой области.

В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "кузьмина" или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.

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

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

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

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

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

Использование индекса и MATCH вместо ВРОТ

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

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

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

Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.

Пример работы с ВЛОКОНПОМ

Скопируйте следующие данные в пустую таблицу.

Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку "Перенос текста" (вкладка "Главная", группа "Выравнивание").

Функции впр и гпр в excel как работать

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

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

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

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

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

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

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

Схема

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию