Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
Лучшее решение
Чаще всего появление ошибки #Н/Д обусловлено тем, что формула не может найти значение, на которое ссылается функция ПРОСМОТРX, ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ. Например, искомого значения нет в исходных данных.
В этом случае в таблице подстановки нет "Banana", поэтому функция ВПР возвращает ошибку #N/A.
Решение: Убедитесь, что искомое значение есть в исходных данных, или используйте в формуле обработчик ошибок, например функцию ЕСЛИОШИБКА. Например, формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает следующее:
- =ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)
Вы можете указать "", чтобы не отображалось ничего, или подставить собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА();"Сообщение об ошибке")
Примечание
Если вы не знаете, что делать на этом этапе или какая помощь вам нужна, вы можете найти аналогичные вопросы в сообществе Майкрософт или опубликовать один из своих собственных.
Если вам по-прежнему нужна помощь с устранением этой ошибки, приведенный ниже контрольный список поможет вам определить возможные причины проблем в формулах.
Неправильные типы значений
Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию ВПР как число, а исходные данные сохранены как текст.
Решение. Убедитесь, что типы данных совпадают. Вы можете проверка форматы ячеек, выбрав ячейку или диапазон ячеек, а затем щелкните правой кнопкой мыши и выберите команду Формат числа ячеек> (или нажмите клавиши CTRL+1) и при необходимости измените числовой формат.
Совет
Если необходимо принудительно изменить формат для всего столбца, сначала примените нужный формат, а затем можно использовать текст данных>в столбцы>Готово.
В ячейках есть лишние пробелы
Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.
=ВПР(D2;TRIM(A2:B7),2;FALSE)
Примечание
Формулы динамического массива . Если у вас есть текущая версия Microsoft 365 и вы используете канал предварительной оценки, вы можете ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД , чтобы подтвердить формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)
По умолчанию функции, которые ищут данные в таблицах, должны использовать сортировку по возрастанию. Но у функций ВПР и ГПР есть аргумент интервальный_просмотр, который сообщает функции, что нужно искать точное совпадение, даже если таблица не отсортирована. Чтобы найти точное совпадение, укажите для аргумента интервальный_просмотр значение ЛОЖЬ. Помните, что значение ИСТИНА, сообщающее функции о том, что нужно искать приблизительное совпадение, может привести к возвращению не только ошибки #Н/Д, но и ошибочных результатов, как видно в следующем примере.
В этом примере "Banana" не только возвращает ошибку #N/A, но и "Pear" возвращает неправильную цену. К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Здесь нет близкого совпадения для элемента "Банан", а "Черешня" предшествует элементу "Персик". В этом случае при использовании функции ВПР с аргументом ЛОЖЬ будет отображаться правильная цена для элемента "Черешня", но для элемента "Банан" все равно будет указана ошибка #Н/Д, потому что в списке подстановок его нет.
Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).
Формула массива ссылается на диапазон, не соответствующий по количеству строк или столбцов диапазону, содержащему формулу массива
Чтобы исправить ошибку, убедитесь, что диапазон, на который ссылается формула массива, содержит такое же количество строк и столбцов, что и диапазон ячеек, в котором была введена формула массива. Или введите формулу массива в меньшее или большее число ячеек в соответствии со ссылкой на диапазон в формуле.
В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:
=СУММ(ЕСЛИ(A2:A11=D2;B2:B5))
Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.
=СУММ(ЕСЛИ(A2:A11=D2;B2:B11))
Примечание
Формулы динамического массива . Если у вас есть текущая версия Microsoft 365 и вы используете канал предварительной оценки, вы можете ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД , чтобы подтвердить формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если, не располагая необходимыми данными, вы вручную ввели в ячейку значение #Н/Д или НД(), замените его фактическими данными, как только они станут доступны. До этого момента формулы, содержащие ссылки на эти ячейки, не смогут вычислить значения и будут возвращать ошибку #Н/Д.
В этом случае May-December имеют значения #N/A, поэтому итог не может вычислить и вместо этого возвращает ошибку #N/A.
В формуле, использующей стандартную или пользовательскую функцию, отсутствует один или несколько обязательных аргументов.
Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите все обязательные аргументы, которые возвращают ошибку. Вероятно, для проверки функции вам потребуется использовать редактор Visual Basic. Открыть этот редактор можно на вкладке "Разработчик" или с помощью клавиш ALT+F11.
Пользовательская функция, которую вы ввели, недоступна
Чтобы исправить ошибку, убедитесь в том, что книга, содержащая пользовательскую функцию, открыта, а функция работает правильно.
Выполняемый макрос использует функцию, которая возвращает значение "#Н/Д".
Чтобы исправить ошибку, убедитесь в том, что аргументы функции верны и расположены в нужных местах.
При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д
Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.
Нужна помощь по аргументам функции?
Если вы не знаете точно, какие аргументы использовать, вам поможет мастер функций. Выберите ячейку в формуле, которая вызывает у вас сомнения, затем откройте вкладку Формула и нажмите Вставить функцию.
Excel автоматически загрузит мастер:
Щелкнув каждый аргумент, Excel предоставит вам соответствующую информацию для каждого из них.
Использование #Н/Д в диаграммах
Значение #Н/Д может принести пользу. Значения #Н/Д часто используются в диаграммах с такими данными, как в приведенном ниже примере, поскольку эти значения не отображаются на диаграмме. В примерах ниже показано, как выглядит диаграмма со значениями 0 и #Н/Д.
В предыдущем примере вы увидите, что значения 0 были начертированы и отображаются в виде плоской линии в нижней части диаграммы, а затем выполняется съемка вверх, чтобы отобразить итог. В следующем примере вместо нулевых значений используются значения #Н/Д.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.
См. также
Преобразование чисел из текстового формата в числовой
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул