Динамічний аркуш Excel або довідкова книга

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

Довідкові комірки на іншому аркуші динамічно
Посилання на комірки в іншій книзі динамічно

Довідкові комірки на іншому аркуші динамічно

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

Загальна формула

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

1. Як показано на скріншоті нижче, по-перше, вам потрібно створити зведений аркуш, ввівши імена аркушів окремо в різні комірки, потім виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ

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

примітки: У коді:

• B3 - комірка, що містить ім’я аркуша, з якого ви будете витягувати дані;
• C3 - це адреса комірки на конкретному аркуші, для якого ви будете витягувати його дані;
• Щоб запобігти поверненню значення помилки, якщо B5 (комірка імені аркуша) або C3 (комірка, до якої ви будете витягувати дані) порожня, додайте формулу INDIRECT із функцією IF, яка відображається, як показано нижче:
  = ЯКЩО (АБО (АБО (B3 = "", C3 = ""), "", НЕПРЯМО ($ B $ 3 & "! C3"))
• Якщо в іменах аркушів немає пробілів, ви можете безпосередньо скористатися цією формулою
  = НЕПРЯМИЙ (B3 & "! C3")

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

3. Продовжуйте залучати всі продажі інших кварталів, як вам потрібно. І не забудьте змінити посилання на клітинку у формулі.

Посилання на комірки в іншій книзі динамічно

У цьому розділі йдеться про динамічне посилання на комірки в іншій книзі в Excel.

Загальна формула

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

Як показано на скріншоті нижче, дані, які потрібно повернути, знаходять у стовпці Е робочого аркуша "Загальний обсяг продажів" в окремій робочій книжці ім “Файл продажів”. Будь ласка, виконайте наступні кроки за кроком, щоб це зробити.

1. По-перше, давайте заповнимо інформацію про робочу книгу (включаючи назву книги, назву робочого аркуша та осередки посилань), до якої ви отримаєте дані на основі цієї інформації до поточної книги.

2. Виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ

=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,"")

Пов’язана функція