Исправление ошибки #Н/Д
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше
Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
Лучшее решение
Чаще всего появление ошибки #Н/Д обусловлено тем, что формула не может найти значение, на которое ссылается функция ПРОСМОТРX, ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ. Например, искомого значения нет в исходных данных.
В данном случае в таблице подстановки нет элемента «Банан», поэтому функция ВПР возвращает ошибку #Н/Д.
Решение: Убедитесь, что искомое значение есть в исходных данных, или используйте в формуле обработчик ошибок, например функцию ЕСЛИОШИБКА. Например, формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает следующее:
- =ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)
Вы можете указать «», чтобы не отображалось ничего, или подставить собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение об ошибке»)
- Если вам нужна справка по ошибке #Н/Д для конкретной функции, например ВПР или ИНДЕКС/ПОИСКПОЗ, выберите один из указанных вариантов.
- Кроме того, может быть полезно узнать о некоторых распространенных функциях, вызывающих эту ошибку, таких как ПРОСМОТРX, ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ.
- Исправление ошибки #Н/Д в функции ВПР
- Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ
Если вы не знаете, что делать на этом этапе или какая помощь вам нужна, вы можете найти аналогичные вопросы в сообществе Майкрософт или опубликовать один из своих собственных.
Если вам по-прежнему нужна помощь с устранением этой ошибки, приведенный ниже контрольный список поможет вам определить возможные причины проблем в формулах.
Неправильные типы значений
Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию ВПР как число, а исходные данные сохранены как текст.
Решение: Убедитесь, что типы данных совпадают. Проверьте форматы ячеек. Для этого выделите диапазон ячеек, щелкните правой кнопкой мыши, выберите Формат ячеек > Число (или нажмите клавиши CTRL+1) и при необходимости измените числовой формат.
Совет: Если вам нужно принудительно изменить формат для целого столбца, сначала примените нужный формат, а затем выберите Данные > Текст по столбцам > Готово.
В ячейках есть лишние пробелы
Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.
Примечание: 24 сентября 2018 г. — Формулы динамического массива — Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу Enter, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)
По умолчанию функции, которые ищут данные в таблицах, должны использовать сортировку по возрастанию. Но у функций ВПР и ГПР есть аргумент интервальный_просмотр, который сообщает функции, что нужно искать точное совпадение, даже если таблица не отсортирована. Чтобы найти точное совпадение, укажите для аргумента интервальный_просмотр значение ЛОЖЬ. Помните, что значение ИСТИНА, сообщающее функции о том, что нужно искать приблизительное совпадение, может привести к возвращению не только ошибки #Н/Д, но и ошибочных результатов, как видно в следующем примере.
В этом примере возвращается не только ошибка #Н/Д для элемента «Банан», но и неправильная цена для элемента «Черешня». К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Здесь нет близкого совпадения для элемента «Банан», а «Черешня» предшествует элементу «Персик». В этом случае при использовании функции ВПР с аргументом ЛОЖЬ будет отображаться правильная цена для элемента «Черешня», но для элемента «Банан» все равно будет указана ошибка #Н/Д, потому что в списке подстановок его нет.
Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).
Формула массива ссылается на диапазон, не соответствующий по количеству строк или столбцов диапазону, содержащему формулу массива.
Чтобы исправить ошибку, убедитесь, что диапазон, на который ссылается формула массива, содержит такое же количество строк и столбцов, что и диапазон ячеек, в котором была введена формула массива. Или введите формулу массива в меньшее или большее число ячеек в соответствии со ссылкой на диапазон в формуле.
В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:
, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.» />
Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.
Примечание: 24 сентября 2018 г. — Формулы динамического массива — Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу Enter, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если, не располагая необходимыми данными, вы вручную ввели в ячейку значение #Н/Д или НД(), замените его фактическими данными, как только они станут доступны. До этого момента формулы, содержащие ссылки на эти ячейки, не смогут вычислить значения и будут возвращать ошибку #Н/Д.
В данном случае для месяцев с мая по декабрь указано значение #Н/Д, поэтому итог вычислить не удается и вместо него отображается ошибка #Н/Д.
В формуле, использующей стандартную или пользовательскую функцию, отсутствует один или несколько обязательных аргументов.
Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите все обязательные аргументы, которые возвращают ошибку. Вероятно, для проверки функции вам потребуется использовать редактор Visual Basic. Открыть этот редактор можно на вкладке «Разработчик» или с помощью клавиш ALT+F11.
Пользовательская функция, которую вы ввели, недоступна
Чтобы исправить ошибку, убедитесь в том, что книга, содержащая пользовательскую функцию, открыта, а функция работает правильно.
Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».
Чтобы исправить ошибку, убедитесь в том, что аргументы функции верны и расположены в нужных местах.
При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д
Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.
Нужна помощь по аргументам функции?
Если вы не знаете точно, какие аргументы использовать, вам поможет мастер функций. Выделите ячейку с формулой, а затем перейдите на вкладку Формулы и нажмите кнопку Вставить функцию.
Excel автоматически запустит мастер.
Щелкните любой аргумент, и Excel покажет вам сведения о нем.
Использование #Н/Д в диаграммах
Значение #Н/Д может принести пользу. Значения #Н/Д часто используются в диаграммах с такими данными, как в приведенном ниже примере, поскольку эти значения не отображаются на диаграмме. В примерах ниже показано, как выглядит диаграмма со значениями 0 и #Н/Д.
В предыдущем примере значения 0 показаны в виде прямой линии вдоль нижнего края диаграммы, а затем линия резко поднимается вверх, чтобы показать итог. В следующем примере вместо нулевых значений используются значения #Н/Д.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как заменить нд на 0 в экселе
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949 |
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071 |
Как заменить значения #N/A в Excel (с примерами)
Вы можете использовать следующий базовый синтаксис для замены значений #N/A в Excel нулями или пробелами:
#replace #N/A with zero =IFERROR(FORMULA, "0") #replace #N/A with blank =IFERROR(FORMULA, "")
В следующем примере показано, как использовать этот синтаксис на практике для замены значений #Н/Д из ВПР нулями или пробелами.
Пример: замена значений #N/A в Excel
Предположим, у нас есть следующий набор данных в Excel:
И предположим, что мы используем функцию ВПР() для поиска очков на основе названия команды:
Обратите внимание, что некоторые значения, возвращаемые функцией ВПР(), являются значениями #Н/Д.
Мы можем превратить эти значения в нули с помощью функции ЕСЛИОШИБКА() следующим образом:
#replace #N/A with zero =IFERROR(VLOOKUP( A2 , $A$1:$B$11 , 2, FALSE), "0")
На следующем снимке экрана показано, как использовать эту функцию на практике:
В качестве альтернативы мы можем превратить значения #N/A в пустые с помощью функции ЕСЛИОШИБКА() следующим образом:
#replace #N/A with blank =IFERROR(VLOOKUP( A2 , $A$1:$B$11 , 2, FALSE), "")
На следующем снимке экрана показано, как использовать эту функцию на практике:
Обратите внимание, что каждое значение, которое ранее было #N/A, теперь пустое.
Используя функцию ЕСЛИОШИБКА() , мы можем заменить значения #Н/Д любым значением, которое нам нужно.
В предыдущих примерах мы просто решили заменить значения #N/A нулями или пробелами, потому что это наиболее распространенные значения замены, используемые на практике.
Функция ЕСНД для проверки ячеек на ошибки НД в Excel
Функция ЕСНД в Excel предназначена для проверки вводимых данных (например, результатов, возвращаемых функциями) и возвращает альтернативный результат, указанный в виде второго аргумента, если формула, введенная в качестве первого аргумента, возвращает код ошибки #Н/Д, или результат выполнения этой формулы, если указанная ошибка не возникает.
Как исправить ошибки НД в ячейках таблицы Excel
В Excel есть функция ЕНД, которая также выполняет проверку данных на наличие ошибки #Н/Д. Однако, она может возвращать только одно из двух возможных значений: ИСТИНА – если ошибка #Н/Д возникла, и ЛОЖЬ, если ошибки нет. В ЕСНД предусмотрен функционал выполнения альтернативного действия, поэтому она более удобна в использовании и позволяет сократить длину записываемых формул.
Пример 1. В таблице содержится ряд некоторых числовых значений. Создать формулу для поиска любых числовых значений в данном числовом ряду, которая в случае отсутствия совпадений отобразит понятное рядовому пользователю сообщение вместо кода ошибки #Н/Д.
Вид таблицы данных:
В ячейку C2 запишем следующую формулу:
Функция ПОИСКПОЗ используется (в данном случае) для поиска точного совпадения искомого значения с имеющимся в массиве чисел. Если такое совпадение отсутствует, будет возвращен код ошибки #Н/Д. ЕСНД перехватит ошибку и вернет текстовую строку с пояснением.
Примеры поиска значений:
Теперь при условии возникновении ошибки НД формула автоматически исправляет на текстовое значение «отсутствует» в ячейке Excel. Если же значение в ячейке B2 найдено:
В итоге вычисления формулы получаем соответственный результат.
Пример исправления ошибок с кодом НД в формулах Excel
Пример 2. В столбце записаны некоторые данные, среди которых содержатся коды ошибок #Н/Д. Необходимо суммировать ячейки с ошибками #Н/Д и числовыми значениями.
Вида таблицы данных:
Для расчетов используем следующую формулу массива CTRL+SHIFT+Enter:
Функция ЕСНД просматривает массив данных (A2:A13) и при нахождении кода ошибки #Н/Д выводит число 0.
В результате получим:
Правила использования функции ЕСНД в Excel
Функция имеет следующую синтаксическую запись:
Описание аргументов (каждый обязателен для заполнения):
- значение – принимает данные, которые будут проверены на наличие ошибки #Н/Д. Может быть указан в виде ссылки на ячейку, выражения или формулы;
- значение_при_ошибке – принимает данные, которые будут возвращены в случае, если в проверяемом значении была обнаружена ошибка #Н/Д.
- Если в качестве аргумента значение рассматриваемой функции была передана ссылка на пустую ячейку, результатом выполнения функции будет числовое значение 0. Например, результатом выполнения =ТИП(ЕСНД(A1;A2)) будет число 1 (соответствует числовому типу данных), если ячейки A1 и A2 были пустыми.
- Если ЕСНД получает код ошибки #Н/Д в качестве первого аргумента и ссылку на пустую ячейку в качестве второго, результатом ее выполнения также будет число 0.
- Еще до появления данной функции в Excel приходилось использовать конструкцию формулы: =ЕСЛИ(ЕНД(проверяемое_значение);если_ошибка_есть;если_ошибки_нет).
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры