ВПР и ЕСЛИ в EXCEL для подстановки нужной таблицы для поиска не полного совпадения

 

=ВПР(значение;если(логическое выражение ;таблица 1;таблица 2);номер столбца;НЕ точное совпадение)

Связанные формулы

ВПР с числами и текстом
ВПР с двумя или более критериями
ВПР с индексом вычисляемого столбца
ВПР без ошибки #н/д

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

В примере, показанном формула в ячейке E8:

=ВПР(D8;ЕСЛИ(C8<2;TABLE1;TABLE2);2;истина)

В этой формуле используется число лет, id продавца и процент премии за объём продаж  в компании, чтобы определить, какую премию начислять используем  Таблицы 1 и 2 с тарифами.

Как эта формула работает

Если функция в этой формуле, используется как «табличный_массив» аргумент ВПР, выполняет логическую проверку на значение в столбце C «Год». Если С8 меньше 2, то таблица 1 возвращается как значение если «ИСТИНА». Если С8 больше 2, таблица 2 возвращается как значение если «ЛОЖЬ».

Иными словами, если лет меньше, чем 2, используется таблица 1 и, если нет, то используется таблица 2.

Альтернативный синтаксис

Если таблицы подстановки требуют различных правил обработки, затем вы можете обернуть двумя функциями ВПР внутри функции «ЕСЛИ» следующим образом:

=Если(логическое выражение ,ВПР (значение;таблица 1;колонка;матч),ВПР (значение;таблица 2;коль;матч))

Это позволяет настроить входы для каждого ВПР по мере необходимости.

 

Частичное-совпадение-с-ВПР-DEVSAP

 

=ВПР(значение&»*»;таблица;колонка;ЛОЖЬ)

Получить информацию с помощью ВПР

Если вы хотите извлечь информацию из таблицы на основе частичного совпадения, это можно сделать с помощью ВПР в точном режиме поиска то-есть ЛОЖЬ.

В показанном примере, формула ВПР выглядит так:

=ВПР($H$5&»*»,таб2,2,ложь)

В этой формуле,  именованный диапазон, который ссылается на B5:Е8. Без именованных диапазонов, формула может быть записана следующим образом:

=ВПР($H$5&»*»,$В$5:$Е$8,2,ЛОЖЬ)

Как эта формула работает

ВПР поддерживает подстановочные знаки, но только в режиме «Точное совпадение». Чтобы задать точное соответствие, убедитесь, что вы поставить 4й параметр как ЛОЖЬ или 0.

В этом случае, мы поставляем подстановки значения как $H$5&»*», поэтому если мы наберем в строке «10» в именованный диапазон ($H$5), мы даем ВПР «10*» в качестве подстановки значения.

Это вызовет в ВПР  поиск по первому столбцу B, который начинается с «10».

Шаблоны-это удобно, поскольку вам не придется вводить полное имя, но учтите, что вы должны быть осторожны, дубликатов или дублей. Например, в таблице есть «10» и в других строках и вам выдаст первый результат поиска.

Примечание: важно установить точное соответствие с использованием ложь или 0.

Файл EXCEL Частичное-совпадение-с-ВПР-devsap

Функции-Excel-ВПР-DEVSAP

Функции-Excel-ВПР-DEVSAP

Формула примеры

Получить первое текстовое значение в списке

Текст карты к номерам

Динамические таблицы подстановки с косвенным

ВПР с числами и текстом

ВПР с двумя или более критериями

ВПР без ошибки #н/д

Автономные ВПР

Построить гиперссылку с ВПР

Номера групп с ВПР

Получить информацию о сотрудниках с ВПР

Цель

Поиск значения в таблице путем сопоставления на первый столбец

Возвращаемое значение

Соответствующим значением из таблицы.

Синтаксис

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

Аргументы

значение — искомое значение в первом столбце таблицы.

Таблица — Таблица, из которой, чтобы получить значение.

Номер столбца — столбца в таблице, из которой нужно извлечь значение.

Правило сравнения — [необязательно] правда = приблизительное совпадение (по умолчанию). Ложь = Точное совпадение.

Примечания

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

Использовать ВПР, когда значения подстановки расположены в первом столбце таблицы с информацией, организованной по вертикали. Использование ГПР, когда значения подстановки расположены в первой строке таблицы, и каждая «запись» отображается в новой колонке.

Правило сравнения управляет значение должен точно соответствовать или нет. Значение по умолчанию-ИСТИН = разрешить не Точное совпадение.

Набор Правило сравнения ЛОЖЬ, чтобы требовать точного соответствия и значение ИСТИНА, чтобы разрешить не-Точное совпадение.

Если Правило сравнения имеет значение ИСТИНА (по умолчанию), не Точное совпадение вызовет функцию ВПР, чтобы соответствовать ближайшему значению в таблице, что по-прежнему меньше, чем значение.

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

Если Правило сравнения является значение ИСТИНА (по умолчанию) удостовериться, что поиск значения в первой строке таблицы сортируются в порядке возрастания. В противном случае функция ВПР может вернуть неправильное или непредвиденное значение.

Если Правило сравнения ЛОЖЬ (требуется Точное совпадение), значения в первом столбце таблицы не должны быть отсортированы.

Файл Функции-Excel-ВПР-devsap

Поиск по возрастным ВПР и формирование групп-DEVSAP

=ВПР(критерий поиска;таблица;колонка поиска;ИСТИНА)

Связанные формулы

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

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

В показанном примере, формула в E5 является:

=ВПР(D5;$G$5:$H$8;2;ИСТИНА)

Как эта формула работает

Эта формула использует значение в ячейке D5 и значения подстановки, именованный диапазон «$G$5:$H$8» для таблицы подстановки, 2, чтобы указать «2-й столбец», и последний аргумент указывает приблизительное совпадение.

Примечание: последний аргумент является необязательным и по умолчанию имеет значение ИСТИНА, но я хотел, чтобы явно установить соответствующий режим.

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

 

ФАЙЛ EXCEL Поиск-по-возрастным-ВПР-и-формирование-групп-devsap

Двусторонний поиск с ВПР и ПОИСКПОЗ-DEVSAP

=ВПР(критерий поиска;таблица поиска;ПОИСКПОЗ(критерий;диапазон поиска;0);0)

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

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

=ВПР($H$5;таб2;ПОИСКПОЗ(H6;B4:E4;0);0)

Н5 поставляет подстановки значений для строки, и H6 поставляет подстановки значений для столбца.

Как эта формула работает

Это стандартный точная формула ВПР  + ПОИСКПОЗ с одним исключением: индекс столбца снабжен функцией ПОИСКПОЗ.

Обратите внимание, что массив подстановок даны в соответствии с (B4:E4), представляющая заголовки столбцов намеренно включает ячейку В4. Это сделано для того, что число возвращаемых ПОИСКПОЗ в синхронизации с таблицей используется функция ВПР. Другими словами, нужно дать диапазона, который охватывает одинаковое число столбцов с помощью ВПР в таблице. В примере (для Месяц2) ПОИСКПОЗ возвращает значение 3, чтобы после ПОИСКПОЗ, работает формула ВПР выглядит так:

=ВПР(Н5,В5:Е8,3,0)

Что возвращает продажи для Капуста (строка 3) в Месяц2 (столбец 3), который составляет 2311 Руб.

 

Файл Excel Двусторонний-поиск-с-ВПР-и-ПОИСКПОЗ-devsap

 

ВПР-с-числами-и-текстом-исправление-ошибки DEVSAP

 

=ВПР(критерий;таблицы;столбец;0)

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

В любом случае, функция ВПР вернет #н/д ошибка, даже когда представляется, совпадение. В приведенном примере, каждый товар имеет свой идентификатор в зависимостями. В ячейке H6 у нас есть простая формула ВПР берет номер 1001 из ячейки Н5. Результат такой ошибки #н/д, хотя 1001 явно в таблице присутствует.

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

=ВПР(Номер;товар;2;0) // оригинал
=ВПР(Номер&»»;товар;2,0) // пересмотренный

Как эта формула работает

Когда вы объединить пустую строку («») в номер, он преобразует число в строку. Вы можете сделать то же самое используя функцию текст :

=ВПР(ТЕКСТ(Номер;»@»);товар;2;0)

Если у вас есть цифры такие-же как текст

Если Вы не уверены, что все будет верно используйте ВПР с iferror :

=Функции iferror(ВПР(Номер;Товар;2;0),ВПР(Номер&»»;товар;2;0))

Формула ВПР предполагает, что обе подстановки это цифры. Если выходит ошибка, можно подправить  формулы.

 

Файл EXCEL ВПР-с-числами-и-текстом-исправление-ошибки-devsap

МЅ 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.

 

ВПР без ошибки #н/д

ВПР без ошибки #н/д

=Функции ЕСЛИОШИБКА(ВПР(значение,Таблица поиска,2,ложь),»»)

Если вы хотите, чтобы ВПР не выдовал  #н/д,  когда он не может найти знач

ение, следует использовать функцию ЕСЛИОШИБКА, чтобы поймать эту ошибку и вернуть любое значение или любое Вам нужное.

Как работает формула

Если ВПР не может найти значение в таблице подстановки, он возвращает #н/д ошибка. Функцию ЕСЛИОШИБКА позволяет отлавливать ошибки и вернуть свои собственные значения, когда есть ошибка. Обычно, если функция ВПР возвращает значение ошибки нет и значение возвращается. Если функция ВПР возвращает значение ошибки #н/д, функции ЕСЛИОШИБКА затем принимает и возвращает значение.

Если у вас есть подстановки значений в ячейки А1 и поиск значений в диапазон с именем таблицы, и вы хотите, чтобы ячейки были пустыми, если не поиска, не найдено, вы можете использовать:

=Функции ЕСЛИОШИБКА(ВПР(I13;$E$8:$F$15;2;0);»нет»)

Если вы хотите вернуть сообщение «нет», когда нет совпадения, используйте:

=Функции ЕСЛИОШИБКА(ВПР(I13;$E$8:$F$15;2;0);»нет»)

В предыдущих версиях Excel, отсутствие функцию ЕСЛИОШИБКА, вам потребуется повторить ВПР внутри функции ЕСЛИ:

=ЕСЛИ(ЕОШ(ВПР(I13;$E$8:$F$15;2;0));ВПР(I13;$E$8:$F$15;2;0);»»)

 

Файл ВПР_без_ошибки_Н/Д

 

 

Вычислить-рейтинг-с-неполным-совпадением-ВПР-DEVSAP

=ВПР(критерий;таблица;2;истина)

Связанные формулы

ВПР без ошибки #н/д

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

Эта Таблица должна быть отсортирована по возрастанию, и ВПР должен быть настроен сделать «приблизительное соответствие».

В показанном примере, формула ВПР выглядит так:

=ВПР($F$6;$C$6:$D$17;2;1)

Как эта формула работает

В данном случае $F$6-результат преобразовать в  (34 в примере) $C$6:$D$17-это таблица с оценкой и рейтингом, состоящий из 2-столбец таблицы 2 говорит ВПР, чтобы получить данные из колонки 2 (рейтинг), и верно говорит ВПР сделать «приблизительное соответствие».

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

Иными словами, ВПР  поиск последнего значения, которое меньше или равно значению поиска.

Если первое значение в таблице меньше значения, ВПР вернет #н/д ошибка.

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

 

 

Файл EXCEL Вычислить-рейтинг-с-неполным-совпадением-ВПР-devsap