ВПР и числа-как-текст
Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в Microsoft Excel. Одна из подобных ситуаций - использование функции ВПР (VLOOKUP) для поиска и подстановки, когда в исходных данных есть эти пресловутые числа в текстовом формате.
Рассмотрим классический пример - подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:
Как и множество других функций и инструментов Excel, ВПР считает числа как текст не равноценными этим же числам в нормальном виде, поэтому выдаёт ошибку #Н/Д, сигнализируя, что искомое значение не обнаружено, хотя визуально оно, вроде бы, есть.
Давайте рассмотрим различные сценарии, с которыми мы здесь можем столкнуться и решения для каждого из них.
Примечание: Различить нормальные и текстовые числа не всегда легко. Иногда на таких ячейках появляется зелёный уголок-индикатор, иногда - нет. В этой статье, для наглядности, я буду выравнивать числа-как-текст по левому краю, а нормальные числа - по правому.
Вариант 1. Числа-как-текст в искомых значениях
Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.
Исправить ситуацию можно, если заставить Excel принудительно конвертировать все исходные артикулы с помощью выполнения над ними любой математической операции, типа:
- умножения или деления на 1
- прибавления или вычитания 0
- двойного знака минус перед артикулом (равносильно двойному умножению на -1)
Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:
Вариант 2. Числа-как-текст в таблице, где ищем
Может встретиться и обратная сторона той же медали, когда псевдочисла будут не в заказе, а в артикулах прайс-листа. Здесь поможет та же логика: для принудительного преобразования исходных числовых артикулов в текстовые, над ними нужно выполнить любую безобидную не математическую, а текстовую операцию.
Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:
Артикул от такой склейки никак не меняется, но вынужденно преобразовывается в текстовый формат, т.к. склейка априори возможна только для текста.
Вариант 3. Пропадание начальных нулей
Частным случаем предыдущего примера бывает сценарий, когда артикулы в прайс-листе не просто текстовые, но ещё и содержат нули в начале строк, дополняющие их до заданной разрядности. Тут поможет функция ТЕКСТ (TEXT), которая умеет любое число выводить как текст по заданному шаблону:
Вариант 4. Числа-как-текст вперемешку с числами в обеих таблицах
Ну, и на десерт, давайте рассмотрим самый "веселый" случай, когда псевдочисла у нас перемешаны с нормальными числами и встречаются в артикулах обеих таблиц.
Звучит страшно, но решается легко - нужно просто скомбинировать первый и второй способы, вложив их в функцию ЕСЛИОШИБКА (IFERROR). Эта функция прокачает обе версии ВПР - "текстовую" и "числовую" - и выдаст ту, которая не приводит к ошибке #Н/Д:
Вот и всё - и никаких больше ошибок :)
Ссылки по теме
- Как выполнить левый ВПР
- Многоразовый ВПР, извлекающий сразу все значения
- Функция ПРОСМОТРХ как преемник ВПР
=ЕСЛИ(ЕЧИСЛО(ВПР(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)))
проверил, у меня работает
ВВОДНАЯ: у меня есть список, адресов. в нем есть названия около 10 сел. и улицы, но преобладает в нем 1 село. назовем буквой "М". (а названия улиц неважно)
в список адресов, делали разные люди, в том числе люди, для которых ексель это шайтан машина. наверняка вы с этим сталкивались. и этот список собрали в одну таблицу.
ЗАДАЧА: необходимо чтобы, название села, было впереди адреса.
ПРОБЛЕМА: т.к. заполняли адреса разные люди, в том числе и можно сказать и предпенсионного возраста, то иногда они "забывали" писать название села. а некоторые вообще ставили название не спереди, а в середине например. названия сел одни и те же, думаю можно над этим пораскинуть мозгами, формулами, сделать так, чтобы они всегда были спереди, но это в принципе вторично.
так вот. я хочу, если в списке нет названия села, то перед ним в обязательном порядке ставился село "М", и тогда, вуаля, весь список будет иметь спереди названия сел (которых иногда люди забывали заполнить). немного логическая задача. как видите.
и еще одна проблема в решении то, что если название села есть, то ничего не надо делать.
можно ли решить на экселе данную задачу, или невозможно?
и хорошо бы названия сел или городов разместить в отдельной таблице, чтобы можно было их менять
=ПРОСМОТРX(--B4;--$E$4:$E$9;$G$4:$G$9)
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 = за февраль.
Возможно есть более Элегантное РЕШЕНИЕ, которое будет искать предыдущее значение вне зависимости от количества пропуска строк.