Исправление ошибки #Н/Д

Исправление ошибки #Н/Д

Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.

Лучшее решение

Чаще всего появление ошибки #Н/Д обусловлено тем, что формула не может найти значение, на которое ссылается функция ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ. Например, искомого значения нет в исходных данных.

Искомого значения не существует.  Ячейка E2 содержит формулу =ВПР(D2;$D$6:$E$8;2;ЛОЖЬ).  Значение "Банан" не найдено, поэтому формула возвращает ошибку #Н/Д.
Элемент не найден в исходных данных

В данном случае в таблице подстановки нет элемента "Банан", поэтому функция ВПР возвращает ошибку #Н/Д.

Решение. Убедитесь, что искомое значение есть в исходных данных, или используйте в формуле обработчик ошибок, например функцию ЕСЛИОШИБКА. Например, формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает следующее:

  • =ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)

Вы можете указать "", чтобы не отображалось ничего, или подставить собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),"Сообщение об ошибке")

Если вы не знаете, что делать на этом этапе или какого рода помощь вам нужна, поищите похожие вопросы на форуме сообщества Excel или опубликуйте там свой вопрос.

Ссылка на форум сообщества Excel

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

Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию ВПР как число, а исходные данные сохранены как текст.

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

Решение. Убедитесь, что типы данных совпадают. Проверьте форматы ячеек. Для этого выделите диапазон ячеек, щелкните правой кнопкой мыши, выберите Формат ячеек > Число (или нажмите клавиши CTRL+1) и при необходимости измените числовой формат.

Диалоговое окно "Формат ячеек" с открытой вкладкой "Число" и выделенным элементом "Текстовый"

Совет : Если вам нужно принудительно изменить формат для целого столбца, сначала примените нужный формат, а затем выберите Данные > Текст по столбцам > Готово.

Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.

Использование функции ВПР с вложенной функцией СЖПРОБЕЛЫ в формуле массива для удаления начальных и конечных пробелов  Ячейка E3 содержит формулу {=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)}, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.

{=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)}

Примечание : Это формула массива, и для ее ввода необходимо нажать клавиши CTRL+SHIFT+ВВОД. Excel автоматически заключает формулу в фигурные скобки {}. Если вы попытаетесь ввести их вручную, Excel отобразит формулу как текст.

По умолчанию функции, которые ищут данные в таблицах, должны использовать сортировку по возрастанию. Но у функций ВПР и ГПР есть аргумент интервальный_просмотр, который сообщает функции, что нужно искать точное совпадение, даже если таблица не отсортирована. Чтобы найти точное совпадение, укажите для аргумента интервальный_просмотр значение ЛОЖЬ. Помните, что значение ИСТИНА, сообщающее функции о том, что нужно искать приблизительное совпадение, может привести к возвращению не только ошибки #Н/Д, но и ошибочных результатов, как видно в следующем примере.

Пример использования функции ВПР со значением ИСТИНА для аргумента интервальный_просмотр, при котором возможны ошибочные результаты
Функция ВПР возвращает ошибку из-за применения аргумента приблизительного совпадения в неотсортированной таблице

В этом примере возвращается не только ошибка #Н/Д для элемента "Банан", но и неправильная цена для элемента "Черешня". К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Здесь нет близкого совпадения для элемента "Банан", а "Черешня" предшествует элементу "Персик". В этом случае при использовании функции ВПР с аргументом ЛОЖЬ будет отображаться правильная цена для элемента "Черешня", но для элемента "Банан" все равно будет указана ошибка #Н/Д, потому что в списке подстановок его нет.

Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).

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

В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:

Пример формулы массива со ссылками на несовпадающие диапазоны, из-за чего появляется ошибка #Н/Д  Ячейка E2 содержит формулу {=СУММА(ЕСЛИ(A2:A11=D2;B2:B5))}, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.

{=СУММ(ЕСЛИ(A2:A11=D2;B2:B5))}

Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.

{=СУММ(ЕСЛИ(A2:A11=D2;B2:B11))}

Примечание : Это формула массива, и для ее ввода необходимо нажать клавиши CTRL+SHIFT+ВВОД. Excel автоматически заключает формулу в фигурные скобки {}. Если вы попытаетесь ввести их вручную, Excel отобразит формулу как текст.

Пример введенного в ячейки значения #Н/Д, которое не позволяет формуле СУММ получить правильный результат

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

Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите все обязательные аргументы, которые возвращают ошибку. Вероятно, для проверки функции вам потребуется использовать редактор Visual Basic. Открыть этот редактор можно на вкладке "Разработчик" или с помощью клавиш ALT+F11.

Чтобы исправить ошибку, убедитесь в том, что книга, содержащая пользовательскую функцию, открыта, а функция работает правильно.

Чтобы исправить ошибку, убедитесь в том, что аргументы функции верны и расположены в нужных местах.

Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.

Если вы не знаете точно, какие аргументы использовать, вам поможет мастер функций. Выберите ячейку в формуле, которая вызывает у вас сомнения, затем откройте вкладку Формула и нажмите кнопку Вставить функцию.

Кнопка "Вставить функцию"

Excel автоматически запустит мастер.

Пример диалогового окна мастера функций

Щелкните любой аргумент, и Excel покажет вам сведения о нем.

Значение #Н/Д может принести пользу. Значения #Н/Д часто используются в диаграммах с такими данными, как в приведенном ниже примере, поскольку эти значения не отображаются на диаграмме. В примерах ниже показано, как выглядит диаграмма со значениями 0 и #Н/Д.

Пример графика, на котором отображаются нулевые значения

В предыдущем примере значения 0 показаны в виде прямой линии вдоль нижнего края диаграммы, а затем линия резко поднимается вверх, чтобы показать итог. В следующем примере вместо нулевых значений используются значения #Н/Д.

Пример графика, на котором не отображаются значения #Н/Д

Дополнительные сведения об ошибке #Н/Д в конкретных функциях см. в следующих статьях:

К началу страницы

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Преобразование чисел из текстового формата в числовой

Функция ВПР

Функция ГПР

Функция ПРОСМОТР

Функция ПОИСКПОЗ

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах с помощью функции проверки ошибок

Сочетания клавиш в Excel для Windows

Сочетания клавиш в Excel для Mac

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×