【Excel】エクセルで串刺し集計（複数シート：複数ファイル：VBA：ブックごと）する方法

Excelで複数の支店や部門ごとに同じフォーマットのデータシートを管理していると、全体を集計したい場面が頻繁に訪れます。

各支店の売上データが別々のシートに入っており、それらを合計して全社の売上を算出したい、月ごとのシートから年間集計を作成したい、一つずつ数式を作成して集計していては膨大な時間がかかり、シートが増えるたびに数式を修正する手間も発生してしまいます。

手作業で集計すると入力ミスのリスクが高まり、データの整合性を保つことも困難になります。特に複数のブックにまたがるデータを集計する場合、作業はさらに複雑化します。

Excelには、複数シートのデータを効率的に集計する「串刺し集計」という機能があります。

同じ位置にあるセルを複数シートから一度に参照して計算できるため、支店別・部門別・月別などの同一フォーマットのデータを素早く集計できます。

本記事では、串刺し集計の基本的な方法から、複数シートの合計や平均の算出、複数ブックにまたがる集計、VBAを使った自動化まで、実務で役立つテクニックを網羅的に解説します。

複数シートのデータ集計を効率化したい方は、ぜひ最後までお読みください。

です。

それでは詳しく見ていきましょう。

串刺し集計の基本（同一ブック内の複数シート）

それではまず、最も基本的な同一ブック内の複数シートを串刺し集計する方法を確認していきます。

串刺し集計とは何か

串刺し集計とは、複数のシートにある同じ位置のセルを一度にまとめて集計する機能です。

例えば、「東京支店」「大阪支店」「名古屋支店」という3つのシートがあり、それぞれのB2セルに売上金額が入っているとします。

これらを「全社集計」シートのB2セルで合計したい場合、串刺し集計を使えば一つの数式で3つのシートのB2セルを参照して合計できます。

串刺しという名前の由来は、焼き鳥の串のように複数のシートを一本の串で刺し通すイメージから来ています。

同じ位置のセルを複数シートから参照するため、各シートが同じフォーマット（レイアウト）であることが前提となります。

支店別、部門別、月別など、同じ構造のデータを複数シートで管理している場合に非常に効果的です。

SUM関数による串刺し集計の基本

串刺し集計を行う最も基本的な方法は、SUM関数でシート範囲を指定することです。

例えば、「東京支店」から「名古屋支店」まで3つのシートがあり、それぞれのB2セルを合計したい場合、集計シートのB2セルに次の数式を入力します。

「=SUM(東京支店:名古屋支店!B2)」

この数式の意味は、「東京支店シートから名古屋支店シートまでの間にあるすべてのシートのB2セルを合計する」となります。

「シート名:シート名!セル番地」という形式で、開始シートと終了シートをコロン（:）で区切って指定します。

この範囲に含まれるすべてのシートの指定セルが自動的に合計されます。

数式	意味	対象シート
=SUM(東京支店:大阪支店!B2)	東京支店と大阪支店のB2を合計	2シート
=SUM(Sheet1:Sheet3!B2)	Sheet1からSheet3のB2を合計	3シート
=SUM(1月:12月!B2)	1月から12月までのB2を合計	12シート
=SUM(東京:福岡!B2:B10)	各シートのB2からB10を合計	複数シート×範囲

範囲で指定することもできます。

「=SUM(東京支店:名古屋支店!B2:B10)」と入力すれば、各シートのB2からB10までの範囲がすべて合計されます。

この場合、東京支店のB2～B10、大阪支店のB2～B10、名古屋支店のB2～B10がすべて足し合わされます。

AVERAGE、MAX、MINなど他の関数での串刺し集計

串刺し集計はSUM関数だけでなく、AVERAGE、MAX、MIN、COUNTなど様々な関数で使用できます。

複数シートの平均を求めたい場合は「=AVERAGE(東京支店:名古屋支店!B2)」、最大値を求めたい場合は「=MAX(東京支店:名古屋支店!B2)」となります。

基本的な構文は同じで、関数名を変えるだけで様々な集計が可能です。

例えば、各支店の売上平均を算出したい場合はAVERAGE関数、最も売上が高かった支店の金額を知りたい場合はMAX関数、データが入力されているシート数を数えたい場合はCOUNTA関数を使用します。

業務の目的に応じて適切な関数を選択することで、多様な分析が可能になります。

関数	数式例	計算内容	用途
SUM	=SUM(Sheet1:Sheet3!B2)	合計	売上合計、数量合計
AVERAGE	=AVERAGE(Sheet1:Sheet3!B2)	平均	平均売上、平均単価
MAX	=MAX(Sheet1:Sheet3!B2)	最大値	最高売上、最大在庫
MIN	=MIN(Sheet1:Sheet3!B2)	最小値	最低売上、最小在庫
COUNT	=COUNT(Sheet1:Sheet3!B2)	数値の個数	データ入力済みシート数

統合機能を使った串刺し集計

続いては、シート間でセルの位置が異なる場合や、より柔軟な集計が必要な場合に使える統合機能を確認していきます。

統合機能とは

Excelの「統合」機能は、複数の範囲のデータを様々な方法で集計できる強力な機能です。

串刺し集計と異なり、各シートでセルの位置が異なっていても、項目名を基準に自動的に対応付けて集計できます。

「データ」タブの「データツール」グループにある「統合」ボタンから実行します。

統合機能の最大の特徴は、「位置」だけでなく「項目名」を基準に集計できることです。

例えば、各支店のシートで商品の並び順が異なっていても、商品名が同じであれば自動的に集計してくれます。

SUM、COUNT、AVERAGE、MAX、MINなど、様々な集計方法を選択でき、さらに複数の統合を同時に実行することも可能です。

統合機能の基本的な使い方

統合機能を使用する手順は次の通りです。

まず、集計結果を表示したいセル（通常は左上隅）を選択します。

「データ」タブの「統合」ボタンをクリックすると、「統合の設定」ダイアログボックスが表示されます。

「集計の方法」で、合計、個数、平均、最大値、最小値などから選択します。

「統合元範囲」の参照ボタンをクリックし、最初のシートの集計したいデータ範囲（項目名を含む）を選択して「追加」ボタンをクリックします。

同様に、他のシートの範囲も次々と追加していきます。

「統合の基準」セクションで、「上端行」（列見出し）や「左端列」（行見出し）にチェックを入れると、項目名を基準に自動的に対応付けてくれます。

すべての範囲を追加したら、「OK」をクリックすると統合結果が表示されます。

統合機能は数式ではなく値として結果を貼り付けるため、元データを変更しても自動更新されません。

設定項目	内容	用途
集計の方法	合計、個数、平均、最大値、最小値など	計算方法の選択
統合元範囲	集計したいデータ範囲のリスト	複数シートの範囲を追加
上端行	1行目を項目名として扱う	列見出しで対応付け
左端列	A列を項目名として扱う	行見出しで対応付け
元データへのリンク	元データ変更時に自動更新	動的な統合（アウトライン形式）

元データへのリンクを作成する

統合機能で「元データへのリンク」にチェックを入れると、元データが変更されたときに統合結果も自動的に更新されます。

このオプションを有効にすると、統合結果がアウトライン形式で表示され、各項目の左側に「+」「-」のボタンが表示されます。

これをクリックすると、どのシートからどの値が集計されているかを展開・折りたたみして確認できます。

ただし、このモードでは統合結果のセルに直接編集を加えることができなくなります。

また、元のシートの範囲を変更した場合は、再度統合を実行する必要があります。

定期的に更新されるデータを集計する場合に便利な機能です。

複数ブックにまたがる串刺し集計

続いては、複数の異なるExcelファイル（ブック）のデータを集計する方法を確認していきます。

外部参照による複数ブックの集計

複数のブックにまたがって串刺し集計を行うには、外部参照という仕組みを使用します。

外部参照とは、他のブックのセルを参照する数式のことで、「=[ブック名]シート名!セル番地」という形式で記述します。

例えば、「東京支店.xlsx」というブックのSheet1のB2セルを参照する場合、「=[東京支店.xlsx]Sheet1!$B$2」となります。

複数ブックを串刺し集計する場合、各ブックへの参照をSUM関数で足し合わせます。

「=SUM([東京支店.xlsx]Sheet1!$B$2,[大阪支店.xlsx]Sheet1!$B$2,[名古屋支店.xlsx]Sheet1!$B$2)」という形式です。

ただし、この方法では参照するブックがすべて開いている必要があり、ブックが閉じられていると正しく計算されないことがあります。

参照の種類	数式の形式	特徴
同一シート内	=B2	最も基本的な参照
別シート	=Sheet1!B2	同一ブック内の別シート
外部参照（開いている）	=[ブック名.xlsx]Sheet1!B2	他のブックを参照
外部参照（閉じている）	=’C:\フォルダ\[ブック名.xlsx]Sheet1′!B2	フルパスが必要

INDIRECT関数を使った動的な参照

複数ブックの集計をより柔軟に行うには、INDIRECT関数を組み合わせる方法があります。

INDIRECT関数は、文字列で指定されたセル参照を実際の参照に変換する関数です。

ただし、INDIRECT関数は外部ブックが開いていないと機能しないという制限があります。

例えば、ブック名をA列に一覧で入力しておき、INDIRECT関数でそれを参照することで、ブック名が変わっても数式を修正しやすくなります。

ただし、実務では外部ブックへのINDIRECT参照は制限が多いため、VBAを使った方法の方が安定して動作します。

Power Queryによる複数ブックの統合

Excel 2016以降では、Power Query（パワークエリ）という強力なデータ統合機能が利用できます。

Power Queryを使えば、複数のブックのデータを取り込んで結合・集計することができ、元ファイルが閉じていても動作します。

「データ」タブの「データの取得」から、ファイルやフォルダからデータをインポートできます。

特に便利なのは、フォルダ内のすべてのExcelファイルを一度に読み込む機能です。

同じ構造のファイルが複数ある場合、フォルダを指定するだけで全ファイルのデータを統合できます。

データの更新も「更新」ボタンをクリックするだけで、最新の状態に更新されます。

まとめ　エクセルで串刺し集計（複数シート：複数ファイル：VBA：ブックごと）する方法

エクセルの串刺し集計方法をまとめると

・基本の串刺し集計：「=SUM(開始シート:終了シート!セル番地)」で複数シートの同じ位置を一度に集計、AVERAGE、MAX、MINなど様々な関数で応用可能、シートの並び順が重要

・統合機能：「データ」→「統合」で項目名を基準にした柔軟な集計、セル位置が異なっても項目名で自動対応、「元データへのリンク」で自動更新も可能

・複数ブック集計：外部参照「=[ブック名]シート名!セル」で他ブックを参照、ブックを開いておく必要あり、Power Queryで閉じたファイルも統合可能

これらの方法を状況に応じて使い分けることで、効率的なデータ集計が実現できます。

同一ブック内の定型集計には串刺し集計、柔軟な集計には統合機能、大規模データにはPower Queryという使い分けが効果的です。

ただし、集計の自動化を進めすぎると、データ構造の変更時に影響範囲が大きくなります。

シート構造やフォーマットを標準化し、できるだけシンプルな集計方法を選択することで、長期的なメンテナンス性が向上します。

エクセルの串刺し集計機能を適切に活用して、効率的なデータ管理を実現していきましょう!