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

Предположим, у вас есть книга, содержащая несколько листов, и вы хотите извлечь данные из одной и той же ячейки на нескольких листах в один основной лист. Например, извлечь ячейку B8 с Листа1, Листа2, Листа3, Листа4… в основной лист, как показано на следующем скриншоте. Как можно быстро и легко решить эту задачу в Excel?

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

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

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

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

Если названия ваших листов являются стандартными именами, такими как Лист1, Лист2, Лист3..., вы можете использовать формулу для быстрого выполнения этой задачи в Excel.

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

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

Примечание: В приведенной выше формуле E2 — это вспомогательный номер, который вы ввели на шаге 1, а B8 — это ссылка на ячейку, которую вы хотите извлечь. Эта формула работает хорошо только если названия листов — Лист1, Лист2, Лист3...

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

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

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

2. Удерживайте клавиши ALT + F11, и откроется окно Microsoft Visual Basic for Applications.

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 для Excel, с его функцией Автоматическое инкрементирование ссылок на листе, вы сможете легко заполнять ссылки на ячейки из нескольких листов в Excel.

После установки Kutools для Excel сделайте следующее:

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

2. Затем нажмите Kutools > Дополнительно > Автоматическое инкрементирование ссылок на листе, см. скриншот:

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

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

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

Нажмите, чтобы скачать Kutools для Excel и попробовать бесплатно прямо сейчас!

Больше статей: