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

Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в 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);....)
29.06.2022 15:37:52
Хорошо бы:
1) писать формулы так, чтобы они были совместимы с Excel (2003, Google-табл и суррогатом типа LibreOfficeCalk), -- АНТИСАНКЦИОННЫЙ вариант.
Excel2003 неплохо работает Portable-версия, запускается с флешки. Быстрее и удобнее (нагляднее) создавать/ проектировать таблицы используя Список, Именованные ДИАПАЗОНЫ, даже Динамические ДИАПАЗОНЫ, правда с некоторыми ограничениями
2) Тарифы ЖКХ -- парсинг для места пребывания, преобразовать в Excel-читаемые таблицы, где по УслугеЖКХ: ХВС, ГВС, ВО, эл-Т1 и др. на текущую дату до 25-го числа каждого месяца с помощью ВПР, ГПР или Индекс получить действующий тариф.
Простые формы ввода, вводить можно с телефона, одновременно осуществляя фото-фиксацию показаний Индивидуальных Приборов Учёта (ИПУ), слишком много злоупотреблений со стороны управляющих компаний (УК)...
3) Учёт курса валют (база: USD, EUR, ПИК) или др. находясь в отпуске или командировке необходимо контролировать и оплачивать.
Простая операция проверки предыдущего значения =МАКС(B4-B3;0), где b4 = за январь, b3 = за декабрь предыдущего года -- не работает, если есть пропуск измерения/ считывания показаний ИПУ, частичное решение =МАКС(B5-ЕСЛИ(ЕПУСТО(B4);B3;B4);0), где b5 = за февраль.
Возможно есть более Элегантное РЕШЕНИЕ, которое будет искать предыдущее значение вне зависимости от количества пропуска строк.
[img]file:///C:/Users/pvv-Vaio/AppData/Local/Temp/msohtml1/01/clip_image001.gif[/img]
МесХВ-ПУГВ-ПУЭл-ПУХВрГВрВОрЭл-РУчОплата
дек.2112961386Упл!25.12.2021
янв.22135664666.0 м³5.0 м³11.0 м³80.0 м³Упл!25.01.2022
фев.22140705465.0 м³4.0 м³9.0 м³80.0 кВ-чУпл!25.02.2022
мар.22144746264.0 м³4.0 м³8.0 м³80.0 кВ-чУпл!25.03.2022
апр.22148787064.0 м³4.0 м³8.0 м³80.0 кВ-чУпл!25.04.2022
май.22152807864.0 м³2.0 м³6.0 м³80.0 кВ-чУпл!25.05.2022
июн.22156828644.0 м³2.0 м³6.0 м³78.0 кВ-чУпл!25.06.2022
июл.220.0 м³0.0 м³0.0 м³0.0 кВ-чГотовь!25.07.2022
Итог3.9 м³3.0 м³6.9 м³68.3 кВ-ч
29.06.2022 16:52:13
Чем заменить Функцию =ЕСЛИОШИБКА в Excel2003&
Наверх