✅ #Н/Д — ledi-i-sport.ru

Запись на от ledi-i-sport.ru

Ошибки #ЗНАЧ и #Н/Д в функции ВПР() Excel и как сними бороться.

В данной статье расскажу о двух ошибках которые может выдать функция ВПР() :

Перечисленные выше ошибки наиболее часто встречаться при использовании функции ВПР() и очень часто вызывают трудности с устранением у начинающих пользователей Excel .

Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().

Сообщение об ошибке Н/Д можно расшифровать как аббревиатуру (НД) – нет данных, то есть функции ВПР() нечего отобразить, и она как бы сообщает: «нет данных для отображения».

Почему возникает ошибка Н/Д (НД)?

  1. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения нет искомого функцией ВПР() значения.
  2. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения значения ячеек имеют ошибки. Иногда ошибки нельзя увидеть «не вооружённым глазом», например, если в ячейке добавлен лишний пробел или едва заметная точка. ВПР() воспринимает значение ячейки без пробела и с пробелом как совершенно разные данные и выдает ошибку «Н/Д».
  3. Ошибка может возникать потому, что в искомой ячейке уже стоит значение «Н/Д», то есть ВПР() подтягивает эту ошибку из другой ячейки (искомой).

Как исправить ошибки Н/Д?

  1. Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). Эта функция заменяет сообщение об ошибке на любое значение, которое Вы укажете.
  2. Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. Для этого нужно нажатием клавиш ctrl+H вызвать окно замены значений, потом в поле «Найти» ввести пробел или знак препинания, а в поле «Заменить на:» не вводить ничего и нажить кнопку «Заменить все».
  3. Способ №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). То есть, если поставить число «1», то допускается 1 ошибка при сравнении [ВПР(*;*;*;1)]. В таком случае строка без пробела и с одним пробелом будут считаться идентичными. Но в таком способе есть подвох — очень высока вероятность неверных результатов, например, слово «полка» и «палка» имеют отличие всего в один знак и будут восприняты функцией, как одно и то же.

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции некорректны и функция не может их обработать.

Казалось бы какие значения могут быть некорректными, если ВПР() необходимо просто сравнить одно значение с другим и присвоить ячейке данные из совпавших ячеек, но эта ошибка возникает.

Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана. Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.

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

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

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

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

Читать еще:  Как разработать вестибулярный аппарат. Как избавиться от морской болезни? Тренируем вестибулярный аппарат

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

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

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

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

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

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

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

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

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

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

= ВПР (D2; TRIM (A2: B7); 2; ЛОЖЬ)

Примечание: 24 сентября 2018 г. (формулы динамического массива): если у вас установлена текущая версия Office 365, и вы пользуетесь каналом выпуска программы предварительной оценки с ранним доступом, можно ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Читать еще:  Как правильно сушить лук после уборки с грядки и в домашних условиях на зиму. Как сушить нарезанный лук: полезные кулинарные лайфхаки

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

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

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

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

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

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

Примечание: 24 сентября 2018 г. (формулы динамического массива): если у вас установлена текущая версия Office 365, и вы пользуетесь каналом выпуска программы предварительной оценки с ранним доступом, можно ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как научится отжиматься на брусьях с нуля. Правильные отжимания на брусьях. Программа тренировок

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Функция ЕСНД для проверки ячеек на ошибки НД в Excel

Функция ЕСНД в Excel предназначена для проверки вводимых данных (например, результатов, возвращаемых функциями) и возвращает альтернативный результат, указанный в виде второго аргумента, если формула, введенная в качестве первого аргумента, возвращает код ошибки #Н/Д, или результат выполнения этой формулы, если указанная ошибка не возникает.

Как исправить ошибки НД в ячейках таблицы Excel

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

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

Вид таблицы данных:

В ячейку C2 запишем следующую формулу:

Функция ПОИСКПОЗ используется (в данном случае) для поиска точного совпадения искомого значения с имеющимся в массиве чисел. Если такое совпадение отсутствует, будет возвращен код ошибки #Н/Д. ЕСНД перехватит ошибку и вернет текстовую строку с пояснением.

Примеры поиска значений:

Теперь при условии возникновении ошибки НД формула автоматически исправляет на текстовое значение «отсутствует» в ячейке Excel. Если же значение в ячейке B2 найдено:

В итоге вычисления формулы получаем соответственный результат.

Пример исправления ошибок с кодом НД в формулах Excel

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

Вида таблицы данных:

Для расчетов используем следующую формулу массива CTRL+SHIFT+Enter:

Функция ЕСНД просматривает массив данных (A2:A13) и при нахождении кода ошибки #Н/Д выводит число 0.

В результате получим:

Правила использования функции ЕСНД в Excel

Функция имеет следующую синтаксическую запись:

Описание аргументов (каждый обязателен для заполнения):

  • значение – принимает данные, которые будут проверены на наличие ошибки #Н/Д. Может быть указан в виде ссылки на ячейку, выражения или формулы;
  • значение_при_ошибке – принимает данные, которые будут возвращены в случае, если в проверяемом значении была обнаружена ошибка #Н/Д.
  1. Если в качестве аргумента значение рассматриваемой функции была передана ссылка на пустую ячейку, результатом выполнения функции будет числовое значение 0. Например, результатом выполнения =ТИП(ЕСНД(A1;A2)) будет число 1 (соответствует числовому типу данных), если ячейки A1 и A2 были пустыми.
  2. Если ЕСНД получает код ошибки #Н/Д в качестве первого аргумента и ссылку на пустую ячейку в качестве второго, результатом ее выполнения также будет число 0.
  3. Еще до появления данной функции в Excel приходилось использовать конструкцию формулы: =ЕСЛИ(ЕНД(проверяемое_значение);если_ошибка_есть;если_ошибки_нет).

Источники:

http://ruexcel.ru/znach-i-nd/
http://support.office.com/ru-ru/article/%D0%98%D1%81%D0%BF%D1%80%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5-%D0%BE%D1%88%D0%B8%D0%B1%D0%BA%D0%B8-%D0%9D-%D0%94-a9708411-f82e-4e1b-8a7e-28c28311b993
http://exceltable.com/funkcii-excel/ispravlenie-oshibok-nd-esnd