Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

Динамический лист 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"))
  • Если в именах ваших листов нет пробелов, вы можете напрямую использовать эту формулу
    = INDIRECT (B3 & "! C3")

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

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


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

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

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

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

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

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

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

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

Примечания:

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

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

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


Родственная функция

НЕПРАВИЛЬНАЯ функция
Функция Microsoft Excel INDIRECT преобразует текстовую строку в допустимую ссылку.


Инструменты для повышения производительности Excel

Лента Excel (с установленным Kutools для Excel)

Расширенные функции 300 помогут вам выделиться из толпы

Хотели бы вы завершить свою ежедневную работу быстро и идеально? Kutools for Excel предоставляет мощные расширенные функции 300 (объединение рабочих книг, суммирование по цвету, разделение содержимого ячеек, конвертирование даты и т. д.) и экономию времени 80%.

  • Предназначен для рабочих сценариев 1500, помогает решать проблемы 80% Excel.
  • Ежедневно уменьшайте тысячи нажатий клавиш и клавиатуры, снимайте усталость с глаз и рук.
  • Станьте экспертом по Excel за минуты 3. Больше не нужно помнить какие-либо болезненные формулы и коды VBA.
  • 60-день неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 лет.

Снимок экрана Excel (с установленной вкладкой Office)

Приносит эффективные вкладки в офис (включая Excel), так же, как Chrome, Firefox и новый IE

  • Office Tab Повышает вашу производительность на 50% при просмотре и редактировании нескольких документов.
  • Сократите сотни щелчков мышью для вас каждый день, попрощайтесь с рукой мыши.
  • Одна секунда, чтобы переключаться между десятками открытых документов!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.