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

Допустим, у вас есть однотипные данные на нескольких листах или в разных книгах, и вы хотите динамически собрать их на одном листе. Функция ДВССЫЛ легко справится с этой задачей!

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

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

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

Универсальная формула

=INDIRECT("'"&,sheet_name&,"'!Cell to return data from")

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

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

Примечания: В формуле:

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

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

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

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

В этом разделе объясняется, как динамически ссылаться на ячейки из другой книги в Excel.

Универсальная формула

=INDIRECT("'[" &, Book name &, "]" &, Sheet name &, "'!" &, Cell address)

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

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

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

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

Примечания:

• B3содержит Имя книги, из которого вы хотите извлечь данные;
• C3— это имя листа;
• D3— это ячейка, из которой вы будете извлекать данные;
• Значение ошибки #ССЫЛ!будет возвращено, если связанная книга закрыта;
• Чтобы избежать ошибки #ССЫЛ!, заключите формулу ДВССЫЛ в функцию ЕСЛИОШИБКА, как показано ниже:
  =IFERROR(INDIRECT(«'[»&,$B$3&,«]»&,$C$3&,«'!»&,D3),«»)

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

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

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