МЅ Excel: как использовать функцию ВПР
Функция ВПР выполняет вертикальный поиск, Поиск значения в первом столбце таблицы и возвращает значение в той же строке в позиции номер_индекса.
Функция ВПР является на самом деле очень легко использовать как только вы поймете, как это работает!
Синтаксис
Синтаксис функции ВПР в Microsoft Excel-это:
ВПР( значение, Таблица, номер_индекса, [approximate_match] )
Параметры или Аргументы
значение
Значение для поиска в первом столбце таблицы.
Таблица
Два или более столбцов данных, отсортированных по возрастанию.
номер_индекса
Номер столбца в таблице, из которой соответствующее значение должно быть возвращено. Первый столбец 1.
истина или ложь
Необязательно. Введите значение ЛОЖЬ (0), чтобы найти Точное совпадение. Введите ИСТИНА (1), чтобы найти приблизительное совпадение. Если этот параметр опущен, ИСТИНА по умолчанию.
Примечание
- Если вы укажите значение ЛОЖЬ для параметра approximate_match и точное соответствие не найдено, то функция ВПР возвращает значение #Н/А.
- Если вы укажите значение ИСТИНА для параметра approximate_match и точное соответствие не найдено, то следующее меньшее значение возвращается.
- Если номер_индекса меньше 1, функция ВПР возвращает значение ошибки #знач!.
- Если номер_индекса больше, чем количество столбцов в таблице, то функция ВПР возвращает ошибку #ссылка!.
- См. также функция ГПР выполнять горизонтальный поиск.
Пример (как функция)
ВПР как функция листа в Excel.
Nаблицы Excel примеры ВПР:
=ВПР(10251, А1:В6, 2, ложь)
Результат: «значение возвращает груши» в 2-м столбце
=ВПР(10251, А1:С6, 3, ложь)
Результат: возвращает значение $18.60 в 3-й колонке
=ВПР(10251, А1:Д6, 4, ложные)
Результат: 9 ‘возвращает значение в 4-м столбце
=ВПР(10248, А1:В6, 2, ложь)
Результат: возвращает #н/д ‘ошибки #н/д (Точное совпадение)
=ВПР(10248, А1:В6, 2, истина)
Результат: «яблоки» ‘возвращает приблизительное совпадение
Теперь, давайте посмотрим на пример =ВПР(10251, А1:В6, 2, false), которое возвращает значение «груши» и присмотреться, почему.
Первый Параметр
Первый параметр функции ВПР искомое значение в таблице данных.
В этом примере первый параметр-10251. Это значение функция ВПР будет искать в первом столбце таблицы данных. Потому что это числовое значение, вы можете просто ввести номер. Но, если искомое значение текст, необходимо положить его в двойные кавычки, например:
=ВПР(«10251», А1:В6, 2, ложь)
Второй Параметр
Второй параметр в функции ВПР-это Таблица или источник данных, где вертикальный поиск должен быть выполнен.
В данном примере второй параметр А1:В6, которые дает нам два столбца данных для использования в вертикальном поиска — А1:А6 и В1:В6. Первый столбец в диапазоне (А1:А6) используется для поиска значения порядка 10251. Во втором столбце в диапазоне (В1:В6) содержится значение, возвращаемое значение продукта.
Третий Параметр
Третий параметр-номер позиции в таблице, где возвращают данные можно найти. Значение 1 указывает, что первый столбец в таблице. Второй столбец 2, и так далее.
В этом примере третий параметр-2. Это означает, что второй столбец в таблице, где мы найдете значение возвращать. Поскольку диапазон таблицы имеет значение А1:В6, возвращаемое значение будет во второй колонке где-то в диапазоне В1:В6.
Четвертый Параметр
И, наконец, самое главное-это четвертый и последний параметр в функции ВПР. Этот параметр определяет, будет ли вы ищете точное или приблизительное совпадение.
В данном примере четвертый параметр имеет значение false. Параметр false означает, что функция ВПР ищет точное соответствие для значения 10251. Параметр True означает, что «закрыть» матч будет возвращена. С ВПР может найти значение 10251 в диапазоне А1:А6, она возвращает соответствующее значение из В1:В6, которые это груши.
Точное соответствие и приблизительное совпадение
Чтобы найти Точное совпадение, использовать ложь в качестве последнего параметра. Чтобы найти приблизительное совпадение, используйте значение True в качестве последнего параметра.
Давайте подстановки значение, которое не существует в наши данные демонстрируют важность этого параметра!
Точное Совпадение
Используйте false, чтобы найти Точное совпадение:
=ВПР(10248, А1:В6, 2, ложь)
Результат: #Н/Д
Если нет точного совпадения, #н/д возвращается.
Приблизительное Совпадение
Используйте значение True, чтобы найти приблизительное совпадение:
=ВПР(10248, А1:В6, 2, истина)
Результат: «Яблоки»
Если совпадение не найдено, то возвращает ближайшее меньшее значение которой в данном случае является «яблоки».
ВПР из другого листа
Вы можете использовать функцию ВПР для поиска значения в случае, когда Таблица находится на другом листе. Давайте изменим наш пример выше, и предположим, что Таблица находится в другой лист под названием Лист2 в диапазоне А1:В6.
Мы можем переписать наш оригинальный пример, в котором мы найти значение 10251 следующим образом:
=ВПР(10251, Лист2!А1:В6, 2, ложь)
Перед таблицы с имя листа и восклицательный знак, мы можем обновить нашу vlookup для обращения к таблице на другом листе.
ВПР из другого листа с пробелами в имени листа
Давайте добавим сюда еще одно осложнение, что произойдет, если имя листа содержит пробелы, то вам потребуется дальнейшее изменение формулы.
Предположим, что Таблица на листе под названием «лист-тест» в диапазоне А1:В6, теперь нам нужно завернуть имя листа в одинарные кавычки, как показано ниже:
=ВПР(10251, ‘лист испытаний’!А1:В6, 2, ложь)
Поместив имя листа в одинарные кавычки, мы можем обрабатывать имя листа с пробелами в функции ВПР.
ВПР из другой книги
Вы можете использовать функцию ВПР для поиска значения в другой книге. Например, если вы хотели иметь табличной части формулы ВПР, от внешней книге, мы сможем попробовать следующей формуле:
=ВПР(10251, и ‘C:[data.xlsx]Лист1’!$А$1:$В$6, 2, ЛОЖЬ)
Это будет искать значение 10251 в C:data.xlxs файл в 1 лист, где данные таблицы находится в диапазоне $A$1:$В$6.
Зачем использовать абсолютные ссылки?
Сейчас для нас важно, чтобы покрыть еще одна ошибка, которую часто совершают. Когда люди используют функции ВПР, они часто используют относительные ссылки на таблицы, как мы делали в наших примерах выше. Это вернет правильный ответ, но и что происходит при копировании формулы в другую ячейку? Диапазон Таблица будет корректироваться в Excel и изменения относительно того, где вы вставить новую формулу. Давай объясняй дальше…
Так что если вы имели следующую формулу в ячейку G1:
=ВПР(10251, А1:В6, 2, ложь)
И тогда вы скопировать эту формулу из ячейки G1 в ячейку Н2, он внесет изменения в формулу vlookup для этого:
=ВПР(10251, В2:С7, 2, ложь)
Так как ваша Таблица находится в диапазоне A1:B6 и не В2:С7, ваша формула будет возвращать неверные результаты в ячейке H2. Чтобы убедиться, что ваш выбор не меняется, попробуйте, ссылающиеся на таблицы с использованием абсолютной ссылки следующим образом:
=ВПР(10251, $А$1:$B В 6$, 2, ЛОЖЬ)
Теперь, если скопировать эту формулу в другую ячейку, диапазон таблицы будет оставаться $в$1:$В$6.
Как справиться с #Н/ошибки
Далее, давайте посмотрим на то, как обрабатывать случаи, когда функция ВПР не найдет совпадения и возвращает #н/д ошибка. В большинстве случаев, Вы не хотите видеть #Н/Д А дисплей более удобный результат.
Например, если у вас следующие формулы:
=ВПР(10248, $А$1:$B В 6$, 2, ЛОЖЬ)
Вместо отображения ошибки #н/д Если вам не удается найти соответствие, вы можете вернуть значение «не найдено». Для этого, можно изменить формулу ВПР следующим образом:
=Если(ЕОШ(ВПР(10248, $в$1:$B в 6$, 2, ложь)), «не нашли», ВПР(10248, $в$1:$В$6, 2, ложь))
Эта новая формула будет использована функция ИСНА, чтобы проверить, если функция ВПР возвращает значение #н/д ошибка. Если функция ВПР возвращает значение #н/д, тогда формула будет выход «не найдено». В противном случае он будет выполнять функцию ВПР как и раньше.
Это отличный способ, чтобы украсить вашу таблицу, так что Вы не увидите традиционных ошибок в Excel.