ВПР с текстом
Одним из основных критериев для работы с ВПР является то, что значение «поиска» должно быть одинаковым в ячейке результата, а также в основной таблице данных, но иногда, даже если значение поиска выглядит одинаково в любой из ячеек, мы все равно получаем значение ошибки как # 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.