Перейти к содержимому

Как заменить нд на 0 в экселе впр

  • автор:

Как заменить нд на 0 в экселе впр

В этом разделе описаны наиболее распространенные причины получения ошибочных результатов при использовании функции ВПР. Предоставляются рекомендации по использованию функций ИНДЕКС и ПОИСКПОЗ вместо нее.

Совет: Кроме того, ознакомьтесь с материалом Краткая справочная карточка: советы по устранению неполадок функции ВПР. На ней указаны основные причины получения результата #Н/Д. Сведения приводятся в удобном формате PDF. Файл в формате PDF можно распечатать или предоставить другим пользователям.

Проблема: искомое значение не находится в первом столбце аргумента таблица

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

В следующей таблице нам нужно узнать количество проданной капусты.

Ошибка #Н/Д в функции ВПР: искомое значение не находится в первом столбце массива таблицы

Ошибка #Н/Д #N/A возникает, поскольку значение поиска «Капуста» находится во втором столбце (Продукты) аргумента таблица A2:C10. В этом случае Excel ищет значение в столбце A, а не в столбце B.

Решение. Чтобы исправить ошибку, измените ссылку ВПР так, чтобы она указывала на правильный столбец. Если это невозможно, попробуйте переместить столбцы. Это тоже может быть весьма неудобно при использовании больших или сложных таблиц, где значения в ячейках получены в результате других вычислений (или невозможно перемещать столбцы по другим причинам). В таком случае можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ, которые позволяют находить значения в любом столбце вне зависимости от его позиции в таблице подстановки. (см. следующий раздел).

Попробуйте использовать функции ИНДЕКС и ПОИСКПОЗ

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

Существует несколько преимуществ использования функций ИНДЕКС и ПОИСКПОЗ вместо ВПР.

При использовании функций ИНДЕКС и ПОИСКПОЗ возвращаемое значение не обязательно должно находиться в том же столбце, что и столбец подстановки. При использовании функции ВПР возвращаемое значение, напротив, должно быть в указанном диапазоне. Почему это важно? При использовании функции ВПР вам нужно знать номер столбца, содержащего значение. Это может показаться не слишком сложным, но это всерьез затрудняет работу, если используется большая таблица, в которой нужно подсчитать количество столбцов. Кроме того, если добавить или удалить столбец. придется пересчитать столбцы и изменить значение аргумента номер_столбца. При использовании функций ИНДЕКС и ПОИСКПОЗ не нужно подсчитывать столбцы.

При использовании функций ИНДЕКС и ПОИСКПОЗ можно указать строку или столбец (или и строку, и столбец) в массиве. Это означает, что значения можно искать по вертикали и по горизонтали.

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

Это позволяет использовать динамические ссылки на столбец, содержащий возвращаемое значение.Таким образом, эти функции будут работать, даже если вы добавлять столбцы в таблицу. С другой стороны, ВПР не сможет найти значение, если добавить столбец в таблицу, поскольку эта функция использует статическую ссылку на таблицу.

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

Синтаксис

Чтобы создать синтаксис для функций ИНДЕКС или ПОИСКПОЗ, необходимо вложить синтаксис функции ПОИСКПОЗ в аргумент массива или ссылки функции ИНДЕКС. Это выглядит следующим образом:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])

Заменим функцию ВПР в приведенном выше примере функциями ИНДЕКС и ПОИСКПОЗ. Синтаксис будет выглядеть следующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

=ИНДЕКС(возвратить значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Проблема: не найдено точное совпадение

Если для аргумента диапазон_поиска задано значение ЛОЖЬ, а функции ВПР не удается найти точное совпадение, возвращается ошибка #Н/Д.

Решение. Если вы уверены, что необходимые данные действительно есть в таблице, но функции ВПР не удается их найти, убедитесь, что в ячейках нет скрытых пробелов или непечатаемых символов. Кроме того, убедитесь, в ячейках выбран правильный тип данных. Например, для ячеек с числами необходимо выбрать формат Числовой, а не Текстовый.

Также можно использовать функции ПЕЧСИМВ или СЖПРОБЕЛЫ для очистки данных в ячейках.

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если для аргумента диапазон_поиска задано значение ИСТИНА, а искомое значение меньше наименьшего значения в массиве, возвращается ошибка #Н/Д. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Ошибка #Н/Д в функции ВПР, если искомое значение меньше, чем наименьшее значение в массиве

Исправьте искомое значение.

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

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если для аргумента диапазон_поиска задано значение ИСТИНА, но один из столбцов не отсортирован по возрастанию (от А до Я), возвращается ошибка #Н/Д.

Измените функцию ВПР так, чтобы искать точное совпадение. Для этого укажите для аргумента диапазон_поиска значение ЛОЖЬ. Для значения ЛОЖЬ сортировка не требуется.

Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

При наличии в ячейках значений времени или больших десятичных чисел Excel возвращает ошибку «#Н/Д» из-за точности чисел с плавающей запятой. Числа с плавающей запятой включают цифры после десятичной запятой. (Значения времени хранятся в Excel в виде чисел с плавающей запятой.) Excel не может хранить крупные числа с плавающей запятой, поэтому для правильной работы функции такие числа нужно округлять до 5 десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как избавиться от Н Д в Excel?

При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.

Как убрать отображение Н Д в Excel?

Скрытие индикаторов ошибок в ячейках Чтобы отключить его отображение, выполните указанные ниже действия. В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы. > Excel Options >Formulas. В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

Как исправить ошибку в Экселе Н Д?

Как исправить ошибки Н/Д?Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). . Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. . Способ №3 – поставить в функции ВПР() допуск ошибки.Sep 11, 2019

Почему при Впр пишет НД?

Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого. В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Как избавиться от Н Д в Excel? Ответы пользователей

Ошибка #N/A, так как искомый аргумент «Ели» отображается во втором столбце (Продукты) аргумента table_array A2:C10. В этом случае Excel искать его в столбце A, .

Например, деление на 0 не допускается, и если ввести формулу =1/0, Excel возвращает . Отображение прочерка, строки «#Н/Д» или «НД» вместо значения ошибки.

При работе с формулами в Excel можно столкнуться с ошибкой #Н/Д (частая история с функцией ВПР). В этом уроке мы разберем, как можно исправить ошибку.

Рассмотрим функцию «Excel» при помощи, которой можно убрать (спрятать) ошибки #ЗНАЧ! и Н/Д в ячейках. Эта функция обработки ошибок.

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

Если шифра нет, то выводится #Н/Д. Как от него избавиться? Вложения. Тип файла: rar, Лист Microsoft Excel.rar (103.8 Кб, 31 просмотров) .

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

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

Просто нажмите CTRL +F заменить пишем «#н/д» или «0» во втором просто пустое. и Вуаля поиск и замена по всему листу и у вас все чисто).

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

При работе с формулами в Excel можно столкнуться с ошибкой #Н/Д (частая история с функцией ВПР). В этом уроке мы разберем, как можно исправить ошибку #Н/Д в Excel. Вообще эта ошибка обозначает, что используемая формула не нашла интересующее нас значение.

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

Способ 1. Функция ЕСЛИОШИБКА()

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

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

Функция ЕСЛИОШИБКА() является универсальной функцией для обработки ошибок, во втором способе мы рассмотрим функцию, которая создана специально для обработки только ошибки #Н/Д.

Способ 2. Функция ЕСНД()

Синтаксис функции ЕСНД() аналогичен функции ЕСЛИОШИБКА(), которую мы рассматривали в способе выше, поэтому в качестве первого аргумента мы снова указываем ту функцию, в результате работы которой мы ожидаем ошибку #Н/Д, а в качестве второго аргумента то значение, которое будет выдано вместо ошибки.

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

Это были два основные способа, которые можно использовать при обработке ошибки #Н/Д в Экселе. Спасибо что причитали статью до конца.

Как выполнить ВПР и вернуть ноль вместо # N / A в Excel?

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

Возвращать ноль вместо # Н/Д при использовании ВПР

Чтобы вернуть ноль вместо # Н/Д при ВПР функция не может найти правильный относительный результат, вам просто нужно заменить обычную формулу на другую в Excel.

Выберите ячейку, в которой вы хотите использовать функцию ВПР, и введите эту формулу = ЕСЛИОШИБКА (ВПР ( A13, $ A $ 2: $ C $ 10,3,0), 0), перетащите дескриптор автозаполнения в нужный диапазон. См. Снимок экрана:

Советы:
(1) В приведенной выше формуле A13 – это значение поиска, A2: C10 – это диапазон массива таблицы, а 3 – номер столбца индекса. Последний 0 – это значение, которое вы хотите показать, когда ВПР не может найти относительное значение.
(2) Этот метод заменяет все виды ошибок числом 0, включая # DIV/0, #REF !, # Н/Д и т. Д.

Мастер условий ошибки
Вернуть ноль или другой конкретный текст вместо # N/A с помощью расширенной ВПР

С помощью группы утилит Kutools for Excels ‘ Super LOOKUP вы можете искать значения из справа налево (или слева направо), значение поиска на нескольких листах, значения поиска снизу вверх (или сверху вниз), значение поиска и сумма, а также поиск между двумя значениями, все они поддерживают замену # N/ Значение ошибки с другим текстом. В этом случае, например, я беру ПРОСМОТР справа налево..

После установки Kutools for Excel, пожалуйста, сделайте следующее: (Бесплатная загрузка Kutools for Excel сейчас!)

1. Нажмите Kutools > Super LOOKUP > LOOKUP справа налево .

2. В диалоговом окне ПРОСМОТР справа налево выполните следующие действия:

1) Выберите диапазон значений поиска и диапазон вывода, установите флажок Заменить # Н/Д значение ошибки с указанным значением установите флажок, а затем введите ноль или другой текст, который вы хотите отобразить в текстовом поле.

2) Затем выберите диапазон данных включает или исключает в заголовках укажите ключевой столбец (столбец подстановки) и столбец возврата.

3. Нажмите ОК . Значения были возвращены на основе значения поиска, и ошибки # N/A также заменены нулем или новым текстом.

Преобразовать все # N/A значение ошибки на ноль или другой текст

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

После установки Kutools for Excel, пожалуйста, сделайте следующее : (Загрузите бесплатно Kutools for Excel прямо сейчас!)

1. Выберите диапазон, в котором вы хотите заменить ошибки # N/A на 0, и нажмите Kutools > Еще > Мастер условий ошибки . См. Снимок экрана:

2. Затем во всплывающем диалоговом окне выберите Только значение ошибки # N/A из раскрывающегося списка Типы ошибок и отметьте Сообщение (текст) , затем введите 0 в следующее текстовое поле. См. Снимок экрана:

3. Нажмите ОК . Тогда все ошибки # N/A будут заменены на 0.

Совет : вместо этого эта утилита будет заменять все ошибки # N/A, равные 0, а не только #N. /A в формуле ВПР.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *