Как использовать подстановочные знаки в vlookup?

Я уверен, что вы хотите сделать VLOOKUP более мощным. Что ж, использование подстановочных знаков с VLOOKUP может сделать это за вас. Это может помочь вам найти значение, используя частичное совпадение.

СЕЙЧАС:

Допустим, у вас есть данные, подобные таблице ниже, где полные имена учащихся указаны в одном столбце, а их оценки — в другом.

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

Но если вы объедините звездочку, которая является подстановочным знаком, вы сможете получить оценки учащегося, используя лишь частичное совпадение. Итак, сегодня в этой статье я хотел бы рассказать вам, как использовать подстановочные знаки в VLOOKUP.

И для этого я перечислил 5 конкретных примеров , которые помогут вам понять это сочетание.

Типы подстановочных знаков

Дело в том, что у вас есть всего 3 подстановочных знака, которые вы можете использовать в Excel.

• Звездочка (*): находит любое количество символов после текста. Например, вы можете использовать «Ex*» для сопоставления текста «Excel» из списка.
• Вопросительный знак (?): используйте вопросительный знак, чтобы заменить его символом. Например, вы можете использовать P?inter для поиска текста «Маляр» или «Принтер».
• Тильда (~): это может свести на нет влияние двух символов выше. Например, если вы хотите найти значение «PD*», вместо этого вы можете использовать «PD~*».

Узнайте больше о подстановочных знаках в этом полном руководстве .

Здесь я перечислил 5 различных примеров, которые помогут вам понять, как работает комбинация подстановочного знака и VLOOKUP. Вы можете скачать этот образец файла здесь , чтобы следовать инструкциям.

ВПР с именем и звездочкой

Давайте продолжим пример, который я показал вам выше. Здесь у вас есть список имен (имя + фамилия), и вам нужно искать оценки учащихся, используя только имя.

Когда вы используете обычный VLOOKUP, он возвращает ошибку #N/A, что означает, что значения нет в списке, что полностью правильно.

Но если вы объедините искомое значение со звездочкой, вы получите оценки учащихся без каких-либо ошибок.

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

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

Объедините ВПР и звездочку, чтобы избежать конечных пробелов.

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

И теперь, когда вы пытаетесь найти бренды по имени, вы снова получаете ошибку #N/A.

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

Здесь вам также следует использовать ту же формулу, которую вы использовали выше. Он будет игнорировать пробелы при сопоставлении значения и возвращать отметки с именем учащегося.

Частичный поиск счета-фактуры

В обоих примерах выше вы использовали звездочку для частичного совпадения с VLOOKUP. Но здесь, в этом примере, у нас есть платежные данные с суммой продаж.

Вот поворот:

Каждый номер счета-фактуры имеет в начале префикс «INV». К сожалению, по номеру счета, который вы используете для поиска суммы, у вас нет этого текста.

А с помощью обычного ВПР невозможно получить сумму продаж.

А с использованием подстановочного знака формула будет выглядеть так:

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

В приведенной выше формуле вы объединили три знака вопроса с номером счета-фактуры в качестве префикса. Как я уже упоминал выше, знак вопроса будет обозначать символы.

Здесь эти 3 вопросительных знака представляют собой 3 символа, которые есть в начале номеров счетов.

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

Используйте вопросительный знак с помощью VLOOKUP, чтобы соответствовать идентификатору продукта.

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

Это безумие:

В этих данных вы знаете только начальный номер идентификатора продукта, а последний номер одинаков для всех идентификаторов. Но проблема в тексте средней части, которого у вас нет.

Чтобы решить эту проблему, необходимо снова объединить подстановочные знаки вопросительного знака с VLOOKUP. И формула будет выглядеть так:

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

В приведенной выше формуле эти два вопросительных знака представляют собой текст, который имеется в исходном списке идентификаторов продуктов.

Отмена эффекта подстановочных знаков в VLOOKUP

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

Вам может быть интересно:

Но когда вы попытаетесь найти значение, содержащее звездочку, Excel будет рассматривать его как подстановочный знак, а не как обычное значение.

Итак, здесь вам нужно отменить эффект этого подстановочного знака. Для этого необходимо использовать тильду. И вам следует использовать такую формулу:

Если вы используете тильду перед звездочкой и вопросительным знаком, ее эффект будет отменен, и Excel будет воспринимать ее как обычный текстовый символ.