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

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

  • автор:

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

Всем здравствуйте. Подскажите как спомощью формул в диапазоне A2:AE2 найти номер столбца справа от которого только нули, т.е. номер первого столбца значение которого больше нуля если смотреть справа налево.

Пример.rar (1.6 Кб, 20 просмотров)

Форумчанин
Регистрация: 24.02.2012
Сообщений: 160

=ПРОСМОТР(2;1/A2:AE2;СТОЛБЕЦ(A2:AE2))

Форумчанин
Регистрация: 09.05.2009
Сообщений: 369
ShAM66 спасибо. То, что надо.
Форумчанин
Регистрация: 09.05.2009
Сообщений: 369

Кто нибудь объясните пожалуйста логику работы функции ПРОСМОТР. Почему во вложении =ПРОСМОТР(1;A3:AE3;A4:AE4) выдает 16, а не другое?

Пример2.rar (2.5 Кб, 31 просмотров)

Форумчанин
Регистрация: 25.02.2012
Сообщений: 166
нет тут логики. и быть не должно.
читаем справку по функции:

Важно. Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат.

Пользователь
Регистрация: 02.06.2010
Сообщений: 57
Слишком долго отправлял ответ Копия предыдущего.
Последний раз редактировалось Михаил С.; 05.07.2012 в 20:44 .
Форумчанин
Регистрация: 09.05.2009
Сообщений: 369

Просто хочется понять как работает =ПРОСМОТР(1;1/A2:AE2;A1:AE1). Или нельзя объяснить логику и формула получена случайно. Хотя работает стабильно правильно.

Пример3.rar (1.6 Кб, 16 просмотров)

Форумчанин
Регистрация: 24.02.2012
Сообщений: 160

Я просто подсмотрел на форумах и использую.
Хотя, тоже интересно.
Почему с нулями-единицами не работает объяснили (не упорядочено).
Но в таком варианте =ПРОСМОТР(1;1/A2:AE2;A1:AE1) тоже не упорядочено, но работает.

Форумчанин
Регистрация: 26.11.2006
Сообщений: 584
agregator,
Синтаксис

ПРОСМОТР(искомое_значение; просматриваемый_вектор;вектор_результатов)

в данном примере:
искомое_значение = 1 — число, заведомо большее, чем просматриваемый_вектор и сортировка поэтому не нужна.
просматриваемый_вектор =1/A2:AE2=
Не найдя 1 в просматриваемом_векторе, функция останавливается на последнем значении 0,00263852242744063, которое является 14-ым по счету и выдает 14-ое по счету из вектора-реультатов.

Последний раз редактировалось ZORRO2005; 06.07.2012 в 16:42 .
Форумчанин
Регистрация: 09.05.2009
Сообщений: 369

ZORRO2005, спасибо за объяснение. Понял, что лучше применять =ПРОСМОТР(9E+307;1/A2:AE2;A1:AE1). Кто нибудь может объяснить почему формула выдает результат 11 в прилагаемом примере4, т.к. если я правильно понял логику, результат должен быть 13, ну хотя бы 12, но 11

Пример4.rar (1.9 Кб, 27 просмотров)
Страница 1 из 3 1 2 3 Следующая >
Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
выяснить, является ли последовательность цифр натурального числа при просмотре их справа налево возрастающей последовательностью Al’f!ra Паскаль, Turbo Pascal, PascalABC.NET 2 05.03.2012 19:13
Вывод списка символов справа налево 5 раз подряд и исключить вывод цифр Gareek Помощь студентам 2 23.12.2011 21:41
Возможен ли поиск справа налево в Exel? Dima_com Microsoft Office Excel 9 30.11.2011 18:03
Как сделать так, чтобы когда вводишь символы, текст двигался справа налево? gylayko Помощь студентам 7 03.11.2011 20:56
Найти числа,которые читаются слева-направо и справа-налево одинаково(например, 1001). Паскаль NikLik Помощь студентам 8 22.11.2007 23:00

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

Argument ‘Topic id’ is null or empty

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

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

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

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

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

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Еще. Меньше

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Использование функции ВПР с вложенной функцией СЖПРОБЕЛЫ в формуле массива для удаления начальных и конечных пробелов. Ячейка E3 содержит формулу <=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)></p>
<p>, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.» /></p>
<p><b>Примечание:</b> 24 сентября 2018 г. — Формулы динамического массива — Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу <b>Enter</b>, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши <b>CTRL+SHIFT+ВВОД</b> для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.</p>
<p>Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)</p><div class='code-block code-block-12' style='margin: 8px 0; clear: both;'>
<!-- 12joomlaumnik -->
<script src=

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

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

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

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

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

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

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

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

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

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