ISERROR игнорирует #REF! сгенерировано КОСВЕННЫМ

Я пытаюсь создать ссылку на отдельный лист с помощью INDIRECT. Я также хочу проверить это на наличие ошибок, поэтому я использую ISERROR перед этим.

  • H1 - значение даты в формате «нн м.д.». В этом случае 42574 возвращает Сб, 7.23.
  • "Сб, 7.23". D2 равно 100.

Допустим, есть вкладка «Сб, 7.23», и я хотел бы получить доступ к ячейке D2. Используя INDIRECT и преобразовав форматированную дату в текст, я создаю эту формулу:

=INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2")

Другими словами, НЕПРЯМОЙ говорит мне сделать следующую ссылку:

='Sat 7.23'.D2

Когда вкладка существует, она работает отлично (возвращает 100). Но ... что, если вкладки не существует? INDIRECT возвращает #REF!, чего и следовало ожидать. Итак, я бросаю перед ним ISERROR:

=ISERROR(INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2")

Это ничего не возвращает (или я предполагаю, что FALSE), хотя INDIRECT генерирует ошибку #REF! и, следовательно, должно быть TRUE < / сильный>. Не должно ли?

Чтобы пойти дальше:

=IF(ISERROR(INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2")),0,INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2"))

В этом случае ISERROR всегда истинно, поэтому этот IF всегда переходит к своему оператору "else". Поскольку ссылка недействительна, весь оператор IF возвращает #REF!


error-handling excel excel-formula excel-indirect
person MrMusAddict    schedule 13.07.2016    source источник
comment
а) Разве это не должно быть ='Sat 7.23'!D2? (обратите внимание на восклицательный знак, а не на точку) b) Разве это не должно быть ЕСЛИ ОШИБКА, а не =IF(ISERROR(...?   -  person    schedule 13.07.2016

Ответы (3)


arrow_upward
2
arrow_downward

Я не уверен, на каком региональном языке используется nn для представления Sun - Sat в маске формата, но ddd используется в системе EN-US, а между лист и адрес ячейки.

=IFERROR(INDIRECT("'"&TEXT(H1,"ddd m.d")&"'!D2"), 0)

Это вернет ноль при копировании в одну ячейку выше (#REF! на H0 как адрес ячейки).

person Community    schedule 13.07.2016
comment
Большое спасибо! По какой-то причине ссылка работала с точкой вместо восклицательного знака, поэтому я не знаю, что с этим делать. nn m.d все еще работает, значит, форматирование не повлияло на проблему? - person MrMusAddict; 13.07.2016
comment
Я не знаю вашего регионального языка или системных настроек, но если =INDIRECT("'" & TEXT(H1, "nn m.d") & "'.D2") работает для вас, тогда вам будет больше возможностей. Это, конечно, не работает для меня, и без определенных настроек региона я не желаю повторять предположение о человеке, который задал вопрос. - person ; 13.07.2016

arrow_upward
0
arrow_downward

Некоторые предлагаемые ниже исправления помогут вам разобраться в проблеме:

  1. Измените пользовательский формат ячейки H1, которая имеет значение даты, на «ddd m.dd» без кавычек. Я вижу, вы использовали "nn m.d", и у меня это не сработало.

  2. Теперь в ячейке I1 (рядом с H1) давайте попробуем извлечь значение D2 из листа с именем «Sat 7.23», используя формулу ниже:

    = IF (ISERROR (INDIRECT ("'" & TEXT (H1, "ddd md") & "'! D2")), "лист не существует, поместите сюда ваше соответствующее текстовое сообщение", INDIRECT ("'" & ТЕКСТ (H1, "ddd md") & "'! D2"))

  3. Чтобы сделать отрицательную проверку, я рекомендую поставить другую дату «Sun 7.24» в H2 и использовать ту же формулу в I2 со ссылкой на дату в H2:

    = IF (ISERROR (INDIRECT ("'" & TEXT (H2, "ddd md") & "'! D2")), "лист не существует, поместите сюда ваше соответствующее текстовое сообщение", INDIRECT ("'" & ТЕКСТ (H2, "ddd md") & "'! D2"))

Вот как это будет выглядеть на заполненном листе:
Excel-INDIRECT Formula Usage

person Bharat Anand    schedule 13.07.2016

arrow_upward
0
arrow_downward

Я исправил аналогичную проблему, сгенерировав ошибку

=IFERROR(QUERY(INDIRECT("'"&I2&"'!B3:E", TRUE), "select E where B = '"&I5&"'),"-")

Дает # ССЫЛКА!

=IFERROR(QUERY(INDIRECT("'"&I2&"'!B3:E", TRUE), "select E where B = '"&I5&"')*1,"-")

Работает, потому что умножение REF! с 1 выдает ошибку

Если вы ожидаете число, используйте * 1

Если вы ожидаете, что строка идёт с & ''

person Tim Kozak    schedule 19.09.2020