Note: The other languages of the website are Google-translated. Back to English
English English

Справочник по динамическому листу или книге 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 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (1)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Пожалуйста, вернись со мной. это было некоторое время, так как я использовал ссылки cel. то, что я хочу сделать, это из столбца целых чисел, я хочу создать новый столбец и захватить каждую 100-ю строку предыдущего столбца. скажем, у меня есть ячейки, заполненные F3, F103, F203... я хочу, чтобы содержимое отображалось в G3, G4, G5. не получится сказать =(F3+100*счетчик) со счетчиком в столбце рядом с формулой.
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места