ВПР для текста

ВПР с текстом

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

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

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

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

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

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

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

  Пример 1.1

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

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

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

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

  Пример 1.3

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

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

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

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

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

  Пример 2.0.1

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

Пример 2.2.0

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

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

Пример 2.3

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

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

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

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

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

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

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

Пример 2.5.0

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

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

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

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

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

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

  Пример 2.7

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

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

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

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

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

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

  Пример 3.1

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

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

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

Пример 2.9

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

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

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

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

Пример 2.11.0

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

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

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

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.