Загальна формула
=INDIRECT(sheet&"!"&CELL("address",A1))
Резюме
Щоб збільшити посилання, створене як текст усередині функції INDIRECT, ви можете використовувати функцію CELL. У наведеному прикладі формула в D5 має вигляд:
=INDIRECT($B$5&"!"&CELL("address",A1))
Які прирости, коли формула копіюється вниз.
Пояснення
Розглянемо просте динамічне посилання на Sheet2, використовуючи INDIRECT у такій формулі:
=INDIRECT($B$5&"!"&"A1"))
Якщо ми змінимо ім’я аркуша в B5 на інше (дійсне) ім’я, INDIRECT поверне посилання на A1 у новому аркуші.
Однак, якщо ми скопіюємо цю формулу в стовпець, посилання на A1 не зміниться, оскільки "A1" кодується як текст.
Для вирішення цієї проблеми ми використовуємо функцію CELL для створення текстового посилання із звичайного посилання на клітинку:
CELL("address",A1)
З "адресою" як першим аргументом та А1 як другим аргументом, функція CELL повертає рядок типу "$ A $ 1". Оскільки A1 є звичайним посиланням на комірку, воно буде збільшуватися нормально, коли формула копіюється вниз у стовпець. Результат у D5: D9 - це серія таких формул:
=INDIRECT("Sheet2!$A$1") =INDIRECT("Sheet2!$A$2") =INDIRECT("Sheet2!$A$3") =INDIRECT("Sheet2!$A$4") =INDIRECT("Sheet2!$A$5")
У кожному випадку INDIRECT перетворює кожен текстовий рядок на посилання, і Excel повертає значення в даній комірці в Sheet2.
Примітка: як INDIRECT, так і CELL є мінливими функціями і перераховуються при кожній зміні робочого аркуша. Це може спричинити проблеми з продуктивністю на більш складних робочих аркушах.
