#Н/Д (по-английски #N/A) означает «нет данных»: поиск вроде ВПР, ПРОСМОТРX или ПОИСКПОЗ не нашёл искомое значение. Ниже =ВПР(E2;A2:B6;2;ЛОЖЬ) возвращает #Н/Д, потому что Kiwi в списке нет. Замените E2 на Pear, и формула вернёт 1.5. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, как здесь.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | Price | ||
| 2 | Apple | 1.2 | Kiwi | #N/A | ||
| 3 | Pear | 1.5 | ||||
| 4 | Plum | 0.8 | ||||
| 5 | Bread | 2.4 | ||||
| 6 | Milk | 1.1 |
#N/A Искомого значения нет в диапазоне поиска.В русском Excel: =ВПР(E2;A2:B6;2;ЛОЖЬ)Когда значения действительно нет, #Н/Д это правильный ответ, и ЕСНД (ниже) превращает его в сообщение. Исправлять стоит те случаи, когда значение есть, а поиск всё равно не срабатывает. Таблицы на этой странице показывают ошибку под английским именем #N/A; в русском Excel это #Н/Д, та же самая ошибка.
#Н/Д после протягивания формулы поиска
Самая частая причина в настоящих таблицах: в первой строке формула работает, а в некоторых строках ниже показывает #Н/Д, хотя товары в списке есть.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Order | Price | ||
| 2 | Apple | 1.2 | Apple | 1.2 | ||
| 3 | Pear | 1.5 | Plum | 0.8 | ||
| 4 | Plum | 0.8 | Pear | #N/A | ||
| 5 | Bread | 2.4 | Milk | 1.1 | ||
| 6 | Milk | 1.1 | Apple | #N/A |
#N/A Искомого значения нет в диапазоне поиска.В русском Excel: =ВПР(E4;A4:B8;2;ЛОЖЬ)Щёлкните F4: её диапазон таблицы A4:B8, на две строки ниже, чем у F2. При протягивании формулы диапазон сдвинулся вместе с ней, и Pear (строка 3) и Apple (строка 2) из него выпали. F3 и F5 работают только потому, что Plum и Milk всё ещё внутри своих диапазонов. Щёлкните F2 и замените диапазон на $A$2:$B$6: весь столбец изменится следом, и появятся все цены. Знаки $ закрепляют диапазон, см. абсолютные ссылки.
#Н/Д из-за лишних пробелов
"Pear " с пробелом в конце и "Pear" для Excel разные значения. Пробелы появляются в данных, набранных вручную, скопированных с веб-страниц или выгруженных из других систем, и в ячейке их не видно.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | Price | Length of A2 | |
| 2 | Pear | 1.5 | Pear | #N/A | 5 | |
| 3 | Apple | 1.2 | ||||
| 4 | Plum | 0.8 |
#N/A Искомого значения нет в диапазоне поиска.В русском Excel: =ВПР(D2;A2:B4;2;ЛОЖЬ)E2 возвращает #N/A. F2 показывает причину: в Pear 4 буквы, а в A2 5 символов. Удалите пробел в A2, и поиск заработает. Три способа исправить это надолго:
- Очистите столбец: поставьте
=СЖПРОБЕЛЫ(A2)во вспомогательный столбец, протяните вниз, затем скопируйте его и выберите Главная > Вставить > Значения поверх исходного. - Обрезайте пробелы в искомом значении, если пробелы в том, что вы вводите:
=ВПР(СЖПРОБЕЛЫ(D2);A2:B4;2;ЛОЖЬ). - Обрезайте пробелы во всём столбце поиска прямо в формуле (Excel 2021 или Microsoft 365):
=ПРОСМОТРX(D2;СЖПРОБЕЛЫ(A2:A4);B2:B4).
Текст, вставленный с веб-страниц, может содержать неразрывный пробел, который СЖПРОБЕЛЫ не удаляет. На странице СЖПРОБЕЛЫ показано, как заменить его через ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ").
#Н/Д, когда значение не в первом столбце
ВПР ищет только в первом столбце своего диапазона и возвращает столбец правее него. Поиск значения из любого другого столбца возвращает #Н/Д, даже если оно есть в таблице.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Code | Price | Code | VLOOKUP | XLOOKUP | |
| 2 | Apple | A-17 | 1.2 | P-22 | #N/A | Pear | |
| 3 | Pear | P-22 | 1.5 | ||||
| 4 | Plum | P-31 | 0.8 | ||||
| 5 | Bread | B-05 | 2.4 | ||||
| 6 | Milk | M-40 | 1.1 |
#N/A Искомого значения нет в диапазоне поиска.В русском Excel: =ВПР(E2;A2:C6;1;ЛОЖЬ)Коды стоят в столбце B, поэтому ВПР по A2:C6 ищет P-22 среди названий товаров и не находит. Вернуть название товара, которое стоит левее кода, она тоже не может. ПРОСМОТРX принимает столбец поиска и столбец результата отдельно и находит Pear. В Excel 2019 и старше то же делает =ИНДЕКС(A2:A6;ПОИСКПОЗ(E2;B2:B6;0)).
#Н/Д из-за чисел, сохранённых как текст
Номер заказа, введённый как текст ('1001 или импортированный из CSV), никогда не совпадает с числом 1001, и наоборот. Обе ячейки показывают 1001, поэтому такую причину трудно заметить. В Excel:
A2:B6 holds order numbers stored as text, E2 holds the number 1001
=VLOOKUP(E2,A2:B6,2,FALSE) #N/A
=VLOOKUP(E2&"",A2:B6,2,FALSE) found: E2&"" turns the number into text
A2:B6 holds real numbers, E2 holds "1001" as text
=VLOOKUP(E2,A2:B6,2,FALSE) #N/A
=VLOOKUP(--E2,A2:B6,2,FALSE) found: -- turns the text into a number
В русском Excel исправленные формулы записываются как =ВПР(E2&"";A2:B6;2;ЛОЖЬ) и =ВПР(--E2;A2:B6;2;ЛОЖЬ). =ЕТЕКСТ(A2) показывает, какая сторона текстовая, а маленький зелёный треугольник в углу ячейки отмечает число, сохранённое как текст. Как преобразовать целый столбец, описано на странице текст в число.
ЕСНД или ЕСЛИОШИБКА: сообщение, когда ничего не найдено
Когда значения может законно не быть, покажите что-то полезнее, чем #Н/Д. Используйте ЕСНД (по-английски IFNA), а не ЕСЛИОШИБКА:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | IFNA | IFERROR | ||
| 2 | Apple | 1.2 | Pear | #REF! | Not found | ||
| 3 | Pear | 1.5 | |||||
| 4 | Plum | 0.8 | |||||
| 5 | Bread | 2.4 | |||||
| 6 | Milk | 1.1 |
#REF! Формула ссылается на ячейку, которой не существует.В русском Excel: =ЕСНД(ВПР(E2;A2:B6;3;ЛОЖЬ);"Not found")Обе формулы просят столбец 3 у диапазона из двух столбцов, это ошибка. ЕСНД пропускает #REF! (по-русски #ССЫЛКА!), поэтому вы видите ошибку в формуле. ЕСЛИОШИБКА её прячет и пишет "Not found" для Pear, хотя он есть в списке. Замените обе тройки на 2: теперь каждая формула показывает 1.5, а если в E2 ввести Kiwi, каждая покажет "Not found". У ПРОСМОТРX сообщение встроено: =ПРОСМОТРX(E2;A2:A6;B2:B6;"Not found"). Подробнее о разнице на странице ЕСЛИОШИБКА.
Исправить поиск, сломанный пробелами
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | Price | ||
| 2 | Apple | 1.2 | Plum | |||
| 3 | Pear | 1.5 | ||||
| 4 | Plum | 0.8 | ||||
| 5 | Bread | 2.4 | ||||
| 6 | Milk | 1.1 |
Ваша очередь: Каждый товар в списке импортирован с пробелом в конце, поэтому =VLOOKUP(E2,A2:B6,2,FALSE) возвращает #N/A. Напишите в F2 формулу, которая всё равно возвращает цену товара из E2.
=ПРОСМОТРX(E2;СЖПРОБЕЛЫ(A2:A6);B2:B6) обрезает пробелы в списке прямо в формуле. =ВПР(E2&" ";A2:B6;2;ЛОЖЬ) здесь тоже работает, но только пока у каждого товара ровно один пробел в конце; очистка столбца функцией СЖПРОБЕЛЫ это исправление, которое сохранится.
Часто задаваемые вопросы
Что означает #Н/Д в Excel?
Н/Д значит «нет данных». Функция поиска (ВПР, ГПР, ПРОСМОТРX, ПОИСКПОЗ, ПОИСКПОЗX) не смогла найти переданное ей значение. =НД() тоже возвращает эту ошибку, причём намеренно, например чтобы диаграмма пропустила точку, а не нарисовала её как 0.
Почему ВПР возвращает #Н/Д, если значение есть?
Два значения не совпадают в точности. Обычные причины: пробел в конце одного из них, число, сохранённое как текст с одной стороны и как число с другой, или диапазон таблицы без $, который сполз вниз при протягивании формулы, так что строка со значением в него больше не входит.
Почему ВПР возвращает #Н/Д в одних строках, а в других нет?
Диапазон таблицы не закрепили перед протягиванием формулы, поэтому в каждой строке он начинается на строку ниже: A2:B6 в первой строке через две строки превращается в A4:B8, и значения выше диапазона больше не находятся. Закрепите его знаками $: =ВПР(E2;$A$2:$B$6;2;ЛОЖЬ).
Почему ПРОСМОТРX возвращает #Н/Д?
Значения нет в массиве поиска, или оно отличается пробелом или тем, что записано текстом, а не числом. По умолчанию ПРОСМОТРX ищет точное совпадение, поэтому похожее значение не подойдёт. Её четвёртый аргумент заменяет ошибку: =ПРОСМОТРX(E2;A2:A6;B2:B6;"Not found").
Почему ПОИСКПОЗ возвращает #Н/Д?
С типом сопоставления 0 значения нет в диапазоне, как и в случае ВПР. С типом 1 или без этого аргумента диапазон должен быть отсортирован по возрастанию, а значение не должно быть меньше его первого элемента; для точного совпадения используйте =ПОИСКПОЗ(E2;A2:A6;0).
