ВПР и числа-как-текст

Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в Microsoft Excel. Одна из подобных ситуаций - использование функции ВПР (VLOOKUP) для поиска и подстановки, когда в исходных данных есть эти пресловутые числа в текстовом формате.

Рассмотрим классический пример - подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:

Ошибка ВПР из-за чисел в текстовом формате

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

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

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

Вариант 1. Числа-как-текст в искомых значениях

Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.

Исправить ситуацию можно, если заставить Excel принудительно конвертировать все исходные артикулы с помощью выполнения над ними любой математической операции, типа:

  • умножения или деления на 1
  • прибавления или вычитания 0
  • двойного знака минус перед артикулом (равносильно двойному умножению на -1)

Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:

Числа как текст в искомых значениях ВПР

Вариант 2. Числа-как-текст в таблице, где ищем

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

Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:

Числа-как-текст в таблице, где ищет ВПР

Артикул от такой склейки никак не меняется, но вынужденно преобразовывается в текстовый формат, т.к. склейка априори возможна только для текста.

Вариант 3. Пропадание начальных нулей

Частным случаем предыдущего примера бывает сценарий, когда артикулы в прайс-листе не просто текстовые, но ещё и содержат нули в начале строк, дополняющие их до заданной разрядности. Тут поможет функция ТЕКСТ (TEXT), которая умеет любое число выводить как текст по заданному шаблону:

Добавляем нули функцией ТЕКСТ

Вариант 4. Числа-как-текст вперемешку с числами в обеих таблицах

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

Звучит страшно, но решается легко - нужно просто скомбинировать первый и второй способы, вложив их в функцию ЕСЛИОШИБКА (IFERROR). Эта функция  прокачает обе версии ВПР - "текстовую" и "числовую" - и выдаст ту, которая не приводит к ошибке #Н/Д:

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

Вот и всё - и никаких больше ошибок :)

Ссылки по теме




26.05.2020 22:47:23
можно еще так
=ЕСЛИ(ЕЧИСЛО(ВПР(B4*1;E:G;3;));ВПР(B4*1;E:G;3;);ВПР(B4&"";E:G;3;))
Можно формулу сделать универсальной , примерно так
=ЕСЛИОШИБКА(ВПР(--C6;$F$6:$H$11;3;0);ЕСЛИОШИБКА(ВПР(C6&"";$F$6:$H$11;3;0);ВПР(ТЕКСТ(C6;"00000");$F$6:$H$11;3;0)))
проверил, у меня работает
01.06.2020 19:38:05
хотел спросить,
ВВОДНАЯ: у меня есть список, адресов. в нем есть названия около 10 сел. и улицы, но преобладает в нем 1 село. назовем буквой "М". (а названия улиц неважно)
в список адресов, делали разные люди, в том числе люди, для которых ексель это шайтан машина. наверняка вы с этим сталкивались. и этот список собрали в одну таблицу.
ЗАДАЧА: необходимо чтобы, название села, было впереди адреса.
ПРОБЛЕМА: т.к. заполняли адреса разные люди, в том числе и можно сказать и предпенсионного возраста, то иногда они "забывали" писать название села. а некоторые вообще ставили название не спереди, а в середине например. названия сел одни и те же, думаю можно над этим пораскинуть мозгами, формулами, сделать так, чтобы они всегда были спереди, но это в принципе вторично.
так вот. я хочу, если в списке нет названия села, то перед ним в обязательном порядке ставился село "М", и тогда, вуаля, весь список будет иметь спереди названия сел (которых иногда люди забывали заполнить). немного логическая задача. как видите.
и еще одна проблема в решении то, что если название села есть, то ничего не надо делать.
можно ли решить на экселе данную задачу, или невозможно?
и хорошо бы названия сел или городов разместить в отдельной таблице, чтобы можно было их менять
04.06.2020 11:45:07
как Универсал, можно и так

=ПРОСМОТРX(--B4;--$E$4:$E$9;$G$4:$G$9)
05.06.2020 16:19:18
Можно попробовать мгновенное заполнение. Подробнее тут www.planetaexcel.ru/techniques/7/7751/
24.06.2020 14:09:14
... А можно - формулой массива {=ВПР(--B5;1*$E$4:$G$9;3;0)} 8-)
25.06.2020 19:06:52
Николай, спасибо! Очень помогли. Берегите себя!
11.07.2020 19:49:00
Спасибо!
20.07.2020 14:33:54
Спасибо за статью. Ежедневно сталкиваюсь с подобной проблемой) Иногда я выхожу из ситуации так : =ВПР(еслиошибка(--А1;А1);....)
Наверх