Динамическая ссылка на лист или книгу Excel

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

Динамическая ссылка на ячейки другого листа
Динамическая ссылка на ячейки другой книги

Динамическая ссылка на ячейки другого листа

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

Общая формула

=INDIRECT("'"&sheet_name&"'!Ячейка для возврата данных")

1. Как показано на скриншоте ниже, сначала вам нужно создать сводный лист, вводя названия листов по отдельности в разные ячейки, затем выберите пустую ячейку, скопируйте следующую формулу в неё и нажмите клавишу Enter.

=INDIRECT("'"&B3&"'!C3")

Примечания: В коде:

• B3 — это ячейка, содержащая название листа, из которого вы будете получать данные;
• C3 — это адрес ячейки в конкретном листе, из которого вы будете получать данные;
• Чтобы предотвратить возвращение ошибочного значения, если либо B5 (ячейка с названием листа), либо C3 (ячейка, из которой вы будете получать данные) пустые, заключите формулу INDIRECT в функцию IF, как показано ниже:
  =IF(OR(B3="",C3=""),"",INDIRECT($B$3&"!C3"))
• Если в названиях ваших листов нет пробелов, вы можете использовать эту формулу напрямую
  =INDIRECT(B3&"!C3")

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

3. Продолжайте извлекать все продажи других кварталов по мере необходимости. И не забудьте изменить ссылку на ячейку в формуле.

Динамическая ссылка на ячейки другой книги

Этот раздел рассказывает о динамической ссылке на ячейки в другой книге в Excel.

Общая формула

=INDIRECT("'[" & Название книги & "]" & Название листа & "'!" & Адрес ячейки)

Как показано на скриншоте ниже, данные, которые вы хотите вернуть, находятся в столбце E листа «Total sales» в отдельной книге под названием «SalesFile». Пожалуйста, выполните следующие шаги, чтобы достичь этого.

1. Сначала заполним информацию о книге (включая название книги, название листа и ссылочные ячейки), на основе которой вы будете извлекать данные в текущую книгу.

2. Выберите пустую ячейку, скопируйте следующую формулу в неё и нажмите клавишу Enter.

=INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3)

Примечания:

• B3 содержит название книги, из которой вы хотите извлечь данные;
• C3 — это название листа;
• D3 — это ячейка, из которой вы будете извлекать данные;
• Значение ошибки #REF! вернется, если ссылочная книга закрыта;
• Чтобы избежать значения ошибки #REF!, заключите формулу INDIRECT в функцию IFERROR следующим образом:
  =IFERROR(INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3),"")

3. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.

Совет: Если вы не хотите, чтобы возвращаемое значение становилось ошибкой после закрытия ссылочной книги, вы можете прямо указать название книги, название листа и адрес ячейки в формуле следующим образом:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")

Связанные функции