ВПР для текста | Как использовать текст ВПР в Excel? (с примерами)

ВПР с текстом

Одним из основных критериев для работы с ВПР является то, что значение «поиска» должно быть одинаковым в ячейке результата, а также в основной таблице данных, но иногда, даже если значение поиска выглядит одинаково в любой из ячеек, мы все равно получаем значение ошибки как # N / A !. Это связано с тем, что формат значения vlookup должен быть разным в каждой ячейке. Итак, в этой статье мы покажем вам, как работать с текстовым форматом значения Vlookup.

Пример ВПР для текста

Ниже приведен пример ВПР для текста в Excel.

Вы можете скачать этот шаблон VLOOKUP для текстового Excel здесь - VLOOKUP для Text Excel Template

Иногда числа хранятся как текстовые значения, и в таких случаях мы не можем рассматривать их как числа из-за функциональности Excel. Например, посмотрите на данные ниже.

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

Причина в том, что некоторые числа хранятся как текстовые значения. Итак, как мы идентифицируем текстовые значения?

Мы можем идентифицировать текстовые значения с помощью функции Excel ISNUMBER. Функция ISNUMBER вернет ИСТИНА, если выбранное значение ячейки является числом, иначе она вернет ЛОЖЬ.

Итак, в ячейках B5 и B6 мы получили результат как FALSE, что означает, что номера ячеек A5 и A6 хранятся в виде текстовых значений.

ВПР требует точного числового формата

Например, посмотрите на данные ниже, чтобы применить функцию ВПР.

Из Таблицы 1 нам нужно применить функцию ВПР, чтобы получить столбец дохода, чтобы получить результат Таблицы 2.

Итак, примените функцию ВПР из таблицы 2.

Получаем следующий результат.

Результат функции ВПР в ячейках E6 и E7 показывает # N / A !.

Давайте посмотрим на значения поиска.

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

Примените функцию ЕЧИСЛО для определения нечисловых значений.

Как мы видим, ISNUMBER определил нечисловое значение в таблице 2.

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

Метод 1. Преобразование чисел в текстовом формате в числовые значения с помощью специальной вставки

Сначала введите цифру 1 в любую из ячеек на листе и скопируйте эту ячейку.

Теперь выберите значения идентификаторов розеток в таблице 2 и откройте диалоговое окно Специальная вставка.

Чтобы открыть специальное диалоговое окно вставки, нажмите ALT + E + S.

Получаем следующее диалоговое окно.

В специальном окне вставки выберите опцию «умножить».

Нажмите ОК, все числа в текстовом формате будут преобразованы в числовые значения, и теперь функция ВПР автоматически извлекает данные из таблицы 1.

Метод 2: преобразование с помощью функции VALUE

Функция ЗНАЧЕНИЕ используется для преобразования чисел в текстовом формате в числовые значения. Как мы видим, наша функция VLOOKUP не получила данные из-за формата значения поиска.

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

Посмотрите на приведенную выше формулу, я заключил функцию поиска с функцией VALUE. Поскольку мы применили функцию VALUE внутри функции VLOOKUP, она сначала преобразует нечисловые значения в числовые значения, а затем VLOOKUP обрабатывает их только как числовые значения.

Метод 3: что делать, если числа хранятся как текст в основной таблице

Мы видели, как преобразовывать текстовые значения в числа в таблице результатов, но числа хранятся в виде текстовых значений в самой основной таблице.

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

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

Условие ЕСЛИОШИБКА проверяет, является ли искомое значение числовым или нет, если оно числовое, мы применим обычный ПРОСМОТР, иначе мы применим функцию ТЕКСТ в Excel.

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

То, что нужно запомнить

  • Функция TRIM также преобразует текстовые значения в числовые значения.
  • Во-первых, нам нужно проверить, в каких таблицах номера хранятся в виде текста.
  • ISNUMBER возвращает TRUE, если выбранное значение ячейки является числом, иначе оно вернет FALSE.