Эксель почему поискпоз выдает неверный результат
Перейти к содержимому

Эксель почему поискпоз выдает неверный результат

  • автор:

Эксель почему поискпоз выдает неверный результат

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

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

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 для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше

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

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

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

Искомого значения не существует. Ячейка E2 содержит формулу =ВПР(D2;$D$6:$E$8;2;ЛОЖЬ). Значение

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

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

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

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

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

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

Ссылка на форум сообщества Excel

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

Неправильные типы значений

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

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

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

Диалоговое окно

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

В ячейках есть лишние пробелы

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

Использование функции ВПР с вложенной функцией СЖПРОБЕЛЫ в формуле массива для удаления начальных и конечных пробелов. Ячейка E3 содержит формулу <=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)></p><div class='code-block code-block-4' style='margin: 8px 0; clear: both;'>
<!-- 4uvdkaluga -->
<script src=

, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.» />

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

Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)

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

Пример использования функции ВПР со значением ИСТИНА для аргумента интервальный_просмотр, при котором возможны ошибочные результаты

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

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

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

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

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

Пример формулы массива со ссылками на несовпадающие диапазоны, из-за чего появляется ошибка #Н/Д. Ячейка E2 содержит формулу <=СУММА(ЕСЛИ(A2:A11=D2;B2:B5))></p><div class='code-block code-block-6' style='margin: 8px 0; clear: both;'>
<!-- 6uvdkaluga -->
<script src=

, для ввода которой нужно нажать клавиши 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 или получить поддержку в сообществах.

Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ

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 2007 Excel для Mac 2011 Excel для Windows Phone 10 Еще. Меньше

В этом разделе описываются наиболее распространенные причины появления ошибки #N/A в результате выполнения функций INDEXили MATCH .

Примечание: Если вы хотите, чтобы функция INDEX или MATCH возвращала осмысленное значение вместо #N/A, используйте функцию IFERROR , а затем вставьте функции INDEX и MATCH в эту функцию. Замена #N/A собственным значением только идентифицирует ошибку, но не устраняет ее. Поэтому очень важно, прежде чем использовать IFERROR, убедитесь, что формула работает правильно, как вы планируете.

Проблема: Нет соответствий

Если функция MATCH не находит значение подстановки в массиве подстановки, она возвращает ошибку #N/A.

Если вы считаете, что данные присутствуют в электронной таблице, но match не может найти их, это может быть вызвано следующими причинами:

  • Ячейка содержит непредвиденные символы или скрытые пробелы.
  • К ячейке применен неправильный формат данных. Например, ячейка содержит числовое значение, но отформатирована как текстовая.

РЕШЕНИЕ. Чтобы удалить непредвиденные символы или скрытые пробелы, используйте функцию CLEAN или TRIM соответственно. Кроме того, проверьте, имеют ли ячейки правильные типы данных.

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

При использовании массива в INDEX, MATCH или сочетании этих двух функций необходимо нажать клавиши CTRL+SHIFT+ВВОД на клавиатуре. Excel автоматически заключит формулу в фигурные скобки <>. Если вы попытаетесь самостоятельно ввести в квадратные скобки, excel отобразит формулу в виде текста.

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

Проблема: Несоответствие типа сопоставления и порядка сортировки данных

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

  • Если match_type равно 1 или не задано, значения в lookup_array должны быть в порядке возрастания. Примеры: -2, -1, 0, 1, 2…; А, Б, В…; ЛОЖЬ, ИСТИНА и т. д.
  • Если match_type равно -1, значения в lookup_array должны быть в порядке убывания.

В следующем примере функция MATCH имеет значение

=ПОИСКПОЗ(40;B2:B10;-1)

Функция сопоставления excel

Аргумент match_type в синтаксисе имеет значение -1, что означает, что порядок значений в B2:B10 должен быть в порядке убывания, чтобы формула работала. Но значения находятся в порядке возрастания, что приводит к ошибке #N/Д.

РЕШЕНИЕ: Измените аргумент match_type на 1 или отсортируйте таблицу в убывящем формате. Затем повторите попытку.

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Ошибки в Excel — поиск и обработка ошибок в Excel

Опубликовано 02.07.2013 Автор Ренат Лотфуллин

Ошибки в Excel лого

Внесение даже небольшого изменения на рабочем листе Excel может привести к образованию ошибок в других ячейках. К примеру, вы можете случайно ввести значение в ячейку, которая раньше содержала формулу. Эта простая ошибка может оказать значительное влияние на другие формулы, и вы не сможете обнаружить ее, пока не сделаете какие-нибудь изменения на листе.

Ошибки в формулах делятся на несколько категорий:

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

Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.

Ошибки неправильных ссылок: Логика формул верна, но формула использует некорректную ссылку на ячейку. Простой пример, диапазон данных для суммирования в формуле СУММ может содержать не все элементы, которые вы хотите суммировать.

Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?

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

Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.

Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.

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

Ошибка #ДЕЛ/0!

Если вы создали формулу, в которой производится деление на ноль, Excel вернет ошибку #ДЕЛ/0!

Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).

Ошибка деление на ноль

Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!

Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:

Эта формула вернет пустое значение, если ячейка B4 будет пустой или содержать 0, в противном случае вы увидите посчитанное значение.

Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:

Ошибка #Н/Д

Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.

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

Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().

Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:

Ошибка #ИМЯ?

Excel может вернуть ошибку #ИМЯ? в следующих случаях:

  • Формула содержит неопределенный именованный диапазон
  • Формула содержит текст, который Excel интерпретирует как неопределенный именованный диапазон. Например, неправильно написанное имя функции вернет ошибку #ИМЯ?
  • Формула содержит текст не заключенный в кавычки
  • Формула содержит ссылку на диапазон, у которого отсутствует двоеточие между адресами ячеек
  • Формула использует функцию рабочего листа, которая была определена надстройкой, но надстройка не была установлена

Ошибка #ПУСТО!

Ошибка #ПУСТО! возникает в случае, когда формула пытается использовать пересечение двух диапазонов, которые фактически не пресекаются. Оператором пересечения в Excel является пробел. Следующая формула вернет #ПУСТО!, так как диапазоны не пересекаются.

ошибка пересечения

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! будет возвращена в следующих случаях:

  • В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
  • В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
  • Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
  • Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.

Ошибка #ССЫЛКА!

Ошибка #ССЫЛКА! возникает в случаях, когда формула использует недействительную ссылку на ячейку. Ошибка возникает в следующих ситуациях:

  • Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:
  • Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:
  • Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.
  • Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!

Ошибка #ЗНАЧ!

Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:

  • Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
  • Аргумент функции является диапазоном, когда он должен быть одним значением
  • Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
  • Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
  • Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива

Вам также могут быть интересны следующие статьи

  • Что если отобразить скрытые строки в Excel не работает
  • Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
  • Пятый урок курса по основам Excel — Печать в программе
  • Шестой урок онлайн курса по основам Excel — Управление рабочим листом
  • Четвертый урок курса по основам Excel — Изменение ячеек
  • Третий урок курса по основам Excel — Форматирование рабочих листов
  • Второй урок обучающего курса по Excel «Основы Excel»
  • Запуск новой рубрики — Школа Excel
  • Несколько условий ЕСЛИ в Excel
  • Как построить график с нормальным распределением в Excel

Рубрика: Основы | Метки: логические функции, функции | 5 комментариев | Permalink

5 комментариев

Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.

Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru

Людмила, есть вариант, что мастер диаграмм перестает быть доступен, если книга находится в общем доступе. Если это ваш случай, сделайте монопольный доступ в книге, вставляйте диаграммы и снова делайте книгу общей.

спасибо! так оно и было

Добрый день! Подскажите как решить проблему, у коллеги на компе все получается а у меня нет…. Надо завязать одну таблицу на другую, раньше все получалось самым простым способом, т.е. в нужную ячейку ставлю знак =, перехожу во вторую таблицу, выбираю нежное значение, нажимаю Ентер, и в первой таблице отображается нужное значение с адресом на вторую табл., т.е. при изменении значений во второй табл. все меняется автомат в первой. Так вот теперь у меня след.ситуация: два файла Ексель, в первом выбираю нужную ячейку, нажимаю =, перехожу во второй файл выбираю нужное значение, а оно не активно, т.е. не дает выбрать, ячейка не светится, как должна при копировании…..первый раз такое, может что-то в настройках…. Подскажите, пожалуйста.

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

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