複数のワークシートから同じセルを参照するにはどうすればよいですか？

複数のワークシートが含まれるワークブックがあり、これらのワークシートの同じセルからデータを抽出して1つのマスターシートにまとめたいとします。例えば、Sheet1、Sheet2、Sheet3、Sheet4… からセルB8を取り出し、次のスクリーンショットのようにマスター シートにまとめる場合です。Excelでこのタスクを迅速かつ簡単に解決するにはどうすればよいでしょうか？

数式を使用して複数のワークシートから同じセルを1つのマスターシートに参照する

VBAコードを使用して複数のワークシートから同じセルを1つのマスターシートに参照する

驚くべき機能を使用して複数のワークシートから同じセルを1つのマスターシートに参照する

数式を使用して複数のワークシートから同じセルを1つのマスターシートに参照する

ワークシート名がデフォルトのシート名（Sheet1、Sheet2、Sheet3...など）の場合、Excelでこの作業を迅速に処理するために数式を使用できます。

1. まず、ヘルパー番号のリストを作成し、参照したいワークシートに対応する連続した番号（1、2、3、4...）を入力してください。以下のスクリーンショットをご覧ください：

2. 次に、データを抽出したいセルに以下の数式を入力し、その数式を適用したいセルまでフィルハンドルをドラッグすると、複数のワークシートから同じセルの値がすべて抽出されます。以下のスクリーンショットをご覧ください：

注: 上記の数式では、E2はステップ1で入力したヘルパー番号であり、B8は抽出したいセル参照です。この数式は、シート名がSheet1、Sheet2、Sheet3...の場合にのみ正常に動作します。

VBAコードを使用して複数のワークシートから同じセルを1つのマスターシートに参照する

数十のワークシートがあり、シート名がSheet1、Sheet2ではなくさまざまな内容である場合、次のVBAコードを使用して複数のワークシートからの同じセル参照を一度に1つのワークシートに埋め込むことができます。

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の値がこのワークシートに埋め込まれます。

驚くべき機能を使用して複数のワークシートから同じセルを1つのマスターシートに参照する

VBAコードに慣れていない場合、ここでは便利なツール - Kutools for Excelをご紹介します。Dynamically Refer to Worksheetsユーティリティを使用すると、Excelで複数のワークシートからセル参照を簡単に埋め込むことができます。

Kutools for Excelをインストール後、以下のように操作してください：

1. マスターシート内で、他のワークシートから抽出したいセルB8をクリックします。

2. 次に、 Kutools > その他 > Dynamically Refer to Worksheetsをクリックします。スクリーンショットをご覧ください：

3. 「Fill Worksheets References」ダイアログボックスで、「塗りつぶし順序」から「列ごとに塗りつぶしてから行ごとに塗りつぶす」を選択し、数式テキストボックスの横にある小さなロックをクリックすると、灰色のロックが黄色のロックに変わり、数式とセル参照がロックされたことを示します。その後、他のワークシートからセルB8の参照を抽出するために任意のセルをクリックできます。この例では、セルB2をクリックします。そして、セル参照を抽出したいワークシートにチェックを入れます。スクリーンショットをご覧ください：

注: この小さなロックをロックしない場合、選択したセルに応じてクリックしたセル参照が変更されます。

4. 「範囲を塗りつぶす」ボタンをクリックすると、他のワークシートからセルB8の値がマスターシートに縦にリストされ、ダイアログボックスを閉じます。スクリーンショットをご覧ください：

今すぐKutools for Excelをダウンロードして無料でお試しください！

その他の記事：