Неожиданные результаты косвенной ссылки на другой лист

Question

Я не получаю ожидаемый эталонный результат в следующем сценарии

Может кто-нибудь, пожалуйста, объясните это.

Создайте рабочую книгу с 2 x WorkSheets, Sheet1 & Sheet2

Заполните Sheet2 Col A1 Долой следующее

"Заголовок", "Стр2", "Row3", "Row4", "Row5", "Row6", "Row7", "Row8", "Row9"

Создайте именованный диапазон "Заголовок", ЛОКАЛЬНЫЙ для Листа 2, ссылаясь на Лист2!A1

Заполните Sheet1 Col A1 до Row9 включительно следующим

= OFFSET(INDIRECT("Лист2" и "!Заголовок "), 0,0, COUNTA(INDIRECT(" Лист2 "и"!$ A:$ A ")), 1)

Заполните Лист1 B2 до B9 включительно следующим

= "Row is" & ROW(OFFSET(INDIRECT("Sheet2" & "!$ Header "), 0,0, COUNTA(INDIRECT(" Лист2 "и"!$ A:$ A ")), 1))

Результат, который я ожидал

Результат, который я получил

Учитывая, что никакие относительные ссылки не используются и ссылка на строку «Правильно / Ожидаемо» возвращается по формуле в Sheet1 Col B, все же получают неожиданный результат в Sheet1 Col A; в чем дело.

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

Answer 1

Функция OFFSET возвращает диапазон. С аргументами, которые вы используете, это диапазон 9 x 1 (высота = 9 строк, ширина = 1 столбец), относящийся к A1:A9 из Sheet2.

Поведение диапазонов иногда удивительно, как вы видели. Это отличается от того, как вы используете диапазон.

• Когда вы применяете функцию ROW к диапазону, он возвращает номер верхней строки диапазона - независимо от того, где вы используете функцию. Это повторная Row is 1 вы получили в столбце B.
• Но когда вы ссылаетесь на содержимое диапазона - как вы делаете в своей формуле в столбце A Листа 1 - место, где вы делаете ссылку, имеет значение. Excel вернет содержимое ячейки в пределах диапазона, который находится на той же высоте, что и ячейка, из которой вы используете ссылку.

Это также относится и к другим ссылкам на диапазоны. Например, если вы положите

=Sheet2!A:A

в ячейке D4 на Sheet1 он будет связываться с ячейкой A4 Sheet2: той, которая находится на той же высоте в пределах диапазона, к которому вы привязаны, и содержит Row4 . Или если вы создадите именованный диапазон Alldata для блока ячеек A1: A9, то в ячейке F7

=Alldata

даст вам ссылку на A7, содержащую Row7 .

Таким образом, если вы хотите сослаться только на ячейку сверху, вы можете сделать ее ссылкой на ячейку, а не на диапазон (то есть на диапазон 1x1):

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0,1,1)

или полностью пропустите размеры по высоте и ширине:

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0)

или просто ссылку на ячейку:

=INDIRECT("Sheet2"&"!Header")