Как ссылаться на одну и ту же ячейку из нескольких листов в Excel?

Предположим, у вас есть книга, содержащая несколько листов, и теперь вы хотите извлечь данные из одной ячейки с нескольких листов в один основной лист. Например, перетащите ячейку B8 из Sheet1, Sheet2, Sheet3, Sheet4… на мастер-лист, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?

Ссылка на одну и ту же ячейку из нескольких листов в один мастер-лист с формулой

Ссылка на одну и ту же ячейку из нескольких листов в один мастер-лист с кодом VBA

Ссылка на одну и ту же ячейку из нескольких листов в один мастер-лист с удивительной функцией

Ссылка на одну и ту же ячейку из нескольких листов в один мастер-лист с формулой

Если имена ваших листов являются именами листов по умолчанию, например Sheet1, Sheet2, Sheet3 ..., вы можете использовать формулу для быстрого выполнения этого задания в Excel.

1. Во-первых, вы должны создать список вспомогательных номеров, введите 1, 2, 3, 4 ... порядковые номера, которые указывают на листы, на которые вам нужно ссылаться, см. Снимок экрана:

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

Внимание: В приведенной выше формуле E2 - это номер помощника, который вы ввели на шаге 1, и B8 это ссылка на ячейку, которую вы хотите извлечь. Эта формула работает только в том случае, если имена листов Sheet1, Sheet2, Sheet3 ...

Ссылка на одну и ту же ячейку из нескольких листов в один мастер-лист с кодом VBA

Если есть десятки рабочих листов, имена листов которых представляют собой различное содержимое, а не Sheet1, sheet2, в этом случае следующий код VBA может помочь вам заполнить одни и те же ссылки на ячейки с нескольких рабочих листов на один рабочий лист одновременно.

1. На основном листе щелкните ячейку B8, которая является ячейкой, которую вы хотите извлечь из других листов.

2. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

3. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: ссылка на одну и ту же ячейку из нескольких листов

Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub

4, Затем нажмите F5 ключ для запуска этого кода, и все значения ячейки B8 из других листов были перенесены в главный рабочий лист. Смотрите скриншот:

Примечание: Этот код VBA применяется для заполнения ячеек, в которых вы щелкаете. Например, если вы щелкните ячейку A1 на указанном листе, все значения ячейки A1 из других листов будут заполнены на этом листе.

Ссылка на одну и ту же ячейку из нескольких листов в один мастер-лист с удивительной функцией

Если вы не знакомы с кодом VBA, здесь я могу представить вам полезный инструмент - Kutools for Excel, С его Динамически обращаться к рабочим листам вы можете легко заполнять ссылки на ячейки из нескольких листов в Excel.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Щелкните ячейку B8 на основном листе, которую вы хотите извлечь из других листов.

2. Затем нажмите Кутулс > Ещё > Динамически обращаться к рабочим листам, см. снимок экрана:

3. В Заполнить рабочие листы Ссылки диалоговое окно, выберите Заполнять по вертикали ячейку за ячейкой из Заполнить заказ, и щелкните маленький замок рядом с текстовым полем формулы, и серый замок станет желтым, это означает, что формула и ссылка на ячейку заблокированы, затем вы можете щелкнуть любую ячейку, чтобы извлечь ссылки на ячейку B8 из других листов в В этом примере я щелкну ячейку B2. Затем проверьте рабочие листы, из которых вы хотите извлечь ссылки на ячейки. Смотрите скриншот:

Внимание: Если вы не заблокируете этот маленький замок, ссылка на ячейку, по которой вы щелкнули, будет изменена на выбранную ячейку.

4. Затем нажмите Диапазон заполнения , а значения ячеек ячейки B8 из других листов были перечислены вертикально на рабочем листе Master, а затем закройте это диалоговое окно. Смотрите скриншот:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!

Другие статьи: