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

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

  • автор:

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

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

Проблема: формула не была введена как массив

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

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

Если при использовании функций ИНДЕКС и ПОИСКПОЗ длина искомого значения превышает 255 символов, его необходимо вводить как формулу массива. В ячейке F3 содержится формула =ИНДЕКС(B2:B4;ПОИСКПОЗ(ИСТИНА;A2:A4=F2;0);0), которая вводится путем нажатия клавиш CTRL+SHIFT+ВВОД

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

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

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

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

Примечание: Если вы хотите, чтобы функция 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 или получить поддержку в сообществах.

Функция ПОИСКПОЗ выдает ошибку

Author24 — интернет-сервис помощи студентам

Функция ПРОСМОТР должна возвращать значение из столбца K, если в столбце L присутствует «да». Но почему-то в некоторых случаях работает корректно, а в некоторых — нет. Подскажите, пожалуйста, в чем ошибка?

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Функция БСЧЕТ выдает ошибку
Ситуация в следующем делаю задание и функция БСЧЕТ вместо числа выдает ошибку знач, все.

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

Не работает функция Смещение(ПоискПоз) после строки3845
Не работает функция Смещение(ПоискПоз) после строки3845. Помогите разобраться пожалуйста.

Функция ПОИСКПОЗ и ПРОСМОТР не работают как должны
Здравствуйте уважаемые! У меня приключилась такая беда, мне надо сравнить 2 столбца, в 1-ом.

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

Не могу понять в чем проблема. поиск по значению в столбце А = 0.2 и 0.3 выдают результат из B = 3. тоже самое в А = 1.3 и 1.4 дают B = 14

Прикрепленные файлы

  • Книга1.xlsx (8.76 КБ)

Изменено: Дмитрий — 31.10.2020 07:06:52
Пользователь
Сообщений: 8277 Регистрация: 03.12.2015
31.10.2020 08:50:47
Дмитрий, не знаю почему ) но вот так вроде правильно

=ПРОСМОТР(2;1/(A1:A31=E3);B1:B31)

Не бойтесь совершенства. Вам его не достичь.
Пользователь
Сообщений: 2 Регистрация: 31.10.2020
31.10.2020 10:56:47

Обалдеть. осталось понять что тут к чему.

и почему обычная конструкция ПРОСМОТР(E3;A1:A31;B1:B31) не корректно работает

Изменено: Дмитрий — 31.10.2020 10:57:56
Пользователь
Сообщений: 4661 Регистрация: 10.09.2017
31.10.2020 16:46:53

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

В этом мире для проверки равенства чисел нужно писать не A=B, а ABS(A-B)где EPS — подходящее для данных условий малое число.

Соответственно, Вашу формулу можно записать, например, так:

=ПРОСМОТР(E3+1E-12;A1:A31;B1:B31)

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

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