Справочник по динамическому листу или книге Excel
Предположим, у вас есть данные в одном и том же формате на нескольких листах или книгах, и вам необходимо динамически переносить данные из этих листов или книг на другой лист. Функция КОСВЕННО может помочь вам быстро сделать это.
Связывание ячеек на другом листе динамически
Ссылки на ячейки в другой книге динамически
Связывание ячеек на другом листе динамически
Предположим, есть четыре рабочих листа, содержащих разные продажи за квартал для четырех продавцов, и вы хотите создать сводный рабочий лист, чтобы динамически получать квартальные продажи на основе соответствующего продавца. Чтобы заставить его работать, может помочь следующая формула.
Общая формула
=INDIRECT("'"&sheet_name&"'!Cell to return data from")
1. Как показано на скриншоте ниже, во-первых, вам нужно создать сводный рабочий лист, введя имена листов отдельно в разные ячейки, затем выберите пустую ячейку, скопируйте в нее формулу ниже и нажмите Enter .
=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)
Как показано на скриншоте ниже, данные, которые вы хотите вернуть, находятся в столбце E рабочего листа. "Тотальная распродажа" в отдельной книге под названием «Файл продаж». Пожалуйста, сделайте следующее шаг за шагом, чтобы сделать это.
1. Во-первых, давайте заполним информацию о книге (включая имя книги, имя рабочего листа и ссылочные ячейки), из которых вы будете извлекать данные на основе этой информации в текущую книгу.
2. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter .
=INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3)
Заметки:
- B3 содержит имя книги, из которой вы хотите извлечь данные;
- C3 имя листа;
- D3 это ячейка, из которой вы будете извлекать данные;
- Это #REF! значение ошибки будет возвращено, если указанная книга закрыта;
- Чтобы избежать #REF! значение ошибки, пожалуйста, заключите формулу КОСВЕННО с функцией ЕСЛИОШИБКА следующим образом:
= ЕСЛИОШИБКА (КОСВЕННАЯ ("'[" & $ B $ 3 & "]" & $ C $ 3 & "'!" & D3), "")
3. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
Наконечник: Если вы не хотите, чтобы возвращаемое значение превращалось в ошибку после закрытия указанной книги, вы можете напрямую указать имя книги, имя рабочего листа и адрес ячейки в формуле следующим образом:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")
Связанная функция
КОСВЕННАЯ функция
Функция Microsoft Excel INDIRECT преобразует текстовую строку в действительную ссылку.
Лучшие инструменты для работы в офисе
Kutools for Excel - Помогает вам выделиться из толпы
Kutools for Excel Имеет более 300 функций, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Office Tab - Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
