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