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

Ошибка #N/A обычно указывает на то, что формула не может найти то, что ей было предложено найти.

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

Наиболее распространенная причина ошибки #N/A связана с функциями ПРОСМОТРX, ВПР, ГПР, LOOKUP или MATCH, если формула не может найти указанное значение. Например, значение подстановки не существует в исходных данных.

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

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

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

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

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

Примечания:

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

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

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

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

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

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

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

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

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

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

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

Примечание: Формулы динамического массива Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу ENTER, чтобы подтвердить формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

В этом примере "Banana" не только возвращает ошибку #N/A, но и "Pear" возвращает неправильную цену. К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Нет близкого совпадения для "Банана", и "Груша" приходит перед "Персик" в алфавитном порядке. В этом случае использование VLOOKUP с аргументом FALSE вернет правильную цену для "Pear", но "Banana" по-прежнему приводит к ошибке #N/A, так как в списке подстановок нет соответствующего "Banana".

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

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

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

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

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

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

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

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

В этом случае May-December имеют значения #N/A, поэтому итог не может вычислить и вместо этого возвращает ошибку #N/A.

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

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

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

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

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

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

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

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

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

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