Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую, если у них есть общий критерий.
Для тех, кто ещё не знаком с функцией ВПР (VLOOKUP) ознакомьтесь с простым примером по ссылке
Очень часто вместо желаемого результата вы можете увидеть ошибку #Н/Д. Это нормально только в том случае, если искомого значения действительно нет в таблице. Но, что делать если значение абсолютно точно есть, а функция его нам не подставляет.
Тут могут быть две причины:
- Ошибки в синтаксисе функции ВПР - вы неправильно написали формулу. Тогда нужно подтянуть теорию.
- Проблемы с данными в таблицах. Вот с этим и будем разбираться.
1. Неподходящее для ВПР расположение столбцов в искомой таблице.
Функция ВПР способна осуществлять поиск только в крайнем левом столбце. Т.е. если искомое значение находится правее подставляемого, то в результате вы увидите #Н/д.
Решение. Использовать другие функции подстановки: Индекс + ПоискПоз (INDEX +MATCH) , ПРОСМОТРХ (LOOKUPX) или надстройку Power Query.
Если есть возможность, то можно поменять столбцы местами, но я бы это не советовала.
2. Несовпадение форматов данных.
Обычно это связано с копированием или выгрузкой данных из других программ. Например, если поиск идёт по артикулу, то в 1-й таблице эксель видит артикул, как число, а во второй, как текст. Результат - ошибка #Н/Д.
Решение. Вам нужно привести данные в обоих столбцах к одному формату.
Иногда достаточно просто изменить формат, а если ничего не помогает, то я советую выгрузить данные в Power Query, надстройка умеет исправлять форматы автоматически.
3. Лишние пробелы в искомом значении или в столбце поиска.
В excel вы не можете отобразить пробелы, как в Word, например. Пробел может быть в начале или в конце слова, и вы его не увидите.
Решение. Используйте функцию СЖПРОБЕЛЫ (TRIM). Она удалит все лишние пробелы, далее нужно скопировать результат и вставить, как значения. Также лишние пробелы можно легко удалить с помощью Power Query, если вы не любите писать формулы.
4. Непечатаемые символы в искомом значении или в столбце поиска
Решение. Формула ПЕЧСИМВ (CLEAN)
Что делает формула? Она удаляет непечатаемые символы в ячейке (диапазоне). Таких символов достаточно много - в основном это различные символы, которые импортируются в эксель при экспорте из различных систем вместе с остальными данными - типа, знак переноса строки, неразрывный пробел и т.п.
5. Целое и десятичное число.
Например, вы ищете число 3, а в ячейке у вас 3,5, но отображается, как 3 по причине уменьшения разрядности.
Решение. Можно использовать функции округления или с помощью инструмента excel Проверка данных можно легко обнаружить такие числа.
6. Поиск нужно осуществить по нескольким критериям, а не по одному.
Например, мы ищем не просто товар, а товар конкретного поставщика. Тогда при поиске нужно указать 2 критерия (товар и поставщик). А бывает и вообще много критериев...
Решение. В данном случае нужно использовать формулу массивов с использованием функций ПОИСКПОЗ+ИНДЕКС, искомые значения указать с помощью знака & (товар&поставщик). Есть ещё вариант решения ВПР +СЦЕПИТЬ, но это требует создание дополнительных столбцов, что не желательно в больших таблицах.
7. Ищет только первое искомое значение, а вам нужно вывести сразу все значения.
Если в исходных данных существует больше одного совпадения с искомым значением, то функция ВПР, как известно, выдает только первое. А что, если нам нужно найти их все?
Решение. Эту задачу легко можно решить с помощью надстройки Power Query.
Если вы пока не работаете с Power Query, придется привлекать тяжелую артиллерию в виде дополнительных вычисляемых столбцов, формул массива или даже макросов.
8. Горизонтальные таблицы.
Чаще всего мы имеем дело с вертикальными таблицами (шапкой является строка). Но если одна из ваших таблиц будет горизонтальной, то вы опять увидите ошибку #Н/Д. Так как функция ВПР осуществляет поиск только в вертикальных таблицах.
Решение. Используем функцию ГПР (HLOOKUP), аналог функции ВПР для горизонтальных таблиц. Также можно использовать функции ПоискПоз+Индекс, Просмотрх они осуществляют поиск и в горизонтальных и в вертикальных таблицах.
Итак, у меня получилось 8 причин ошибок Н/Д. Возможно я, что-то упустила или забыла. Дополните меня в комментариях
29 марта я завершаю набор на курс Эксперт excel +Power Query. На курсе вы научитесь использовать правильно не только функции подстановки: ВПР, ГПР, Индекс, ПоискПоз, Просмотрх. Но и все функции и инструменты для устранения ошибок с данными в таблицах включая надстройку Power Query.
Программа курса Эксперт excel +Power Query