複数のシートからのデータ集計: INDIRECT関数とSUMIFS関数を使った効率的な方法

Excel

2023.10.18

Excelでは、複数のシートにまたがるデータの集計が必要な場合があります。この記事では、INDIRECT関数とSUMIFS関数を使って、複数のシートからデータを集計する方法を紹介します。

サンプルデータの作成

まずは、以下のような3つのシートを用意します。各シートには、商品ごとの売上データが記録されています。

Sheet1のサンプル図

集計用シートを作成します。

ここで、集計用シートのA2には集計対象のシート名（例：Sheet1）が入力されているものとします。また、集計用シートのB1には集計対象の商品名（例：商品A）が入力されているものとします(下記の「★サンプル図」を参照)。

集計用シートのB2に、以下のような式を入力します。この式は商品種類が最大10個となっています。必要に応じて拡張してください。

=SUMIFS(INDIRECT("'" & A2 & "'!$B$1:$B$10"), INDIRECT("'" & A2 & "'!$A$1:$A$10"), B1)

この式を、集計対象のシート数に応じてコピーし、最後にSUM関数を使って合計を求めます。
※コピーする場合は上記式の「A2」を「$A2」(列固定)、「B1」を「B$1」(行固定)のように調整するのがよいでしょう。下は絶対参照処理後の式です。

=SUMIFS(INDIRECT("'" & $A2 & "'!$B$1:$B$10"), INDIRECT("'" & $A2 & "'!$A$1:$A$10"), B$1)

上記の計算式をより詳しく解説します。まず、使用されている関数から説明します。

INDIRECT関数は、文字列として与えられた参照を実際のセルまたは範囲の参照に変換します。この関数は、動的な参照や異なるシートへの参照の作成に役立ちます。

SUMIFS関数は、1つ以上の条件を満たすセルの合計を計算します。この関数は、特定の条件に基づいて数値を合計する場合に使用します。

式 =SUMIFS(INDIRECT("'" & $A2 & "'!$B$1:$B$10"), INDIRECT("'" & $A2 & "'!$A$1:$A$10"), B$1) についての解説:

INDIRECT("'" & $A2 & "'!$B$1:$B$10"):
- A2には「Sheet1」、「Sheet2」、「Sheet3」という文字列が含まれています。
- 文字列を結合することで、"'Sheet1'!$B$1:$B$10", "'Sheet2'!$B$1:$B$10", "'Sheet3'!$B$1:$B$10"のような文字列が作成されます。
- INDIRECT関数を使用して、この文字列を実際の範囲の参照に変換します。
- 結果として、各シートのB1からB10までの範囲を参照します。
INDIRECT("'" & $A2 & "'!$A$1:$A$10"):
- 同様の方法で、各シートのA1からA10までの範囲を参照します。
B$1:
- これは「集計」シートのB1セルを参照します。このセルには「商品A」という文字列が含まれています。

最終的な動作:

SUMIFS関数は、指定した範囲（INDIRECT("'" & $A2 & "'!$A$1:$A$10")）で「商品A」（B$1）と一致するセルを検索します。
一致するセルが見つかった場合、対応するB列の値（INDIRECT("'" & $A2 & "'!$B$1:$B$10")）を合計します。

この方法により、「集計」シートに各シートの「商品A」または「商品B」の合計を表示することができます。