【Excel】3D参照（串刺し集計）で複数シートの同じセルを合計する

複数シートの同じセルを合計するには、=SUM(月別1:月別3!B2:B10) のように、最初と最後のシート名をコロン（:）でつないでからセル範囲を書きます。これが3D参照、いわゆる串刺し集計です。

シート名を1つずつ数式に並べる必要はありません。ただし、あとから足したシートがエラーも出ないまま合計から漏れることがあります。この記事では、その見分け方と直し方まで画面つきで説明します。

この記事で分かること
どんな場面で使うか
基本説明：3D参照とは
1. 使うデータ
2. 1つずつ書く方法との違い
手順
使用例：平均・件数・最大値も同じ書き方
よくあるエラー・注意点
まとめ
関連記事
サンプルファイル

この記事で分かること

複数シートの同じセルを1本の数式で合計する書き方
シートを追加したときに、集計に入るシートと入らないシートの違い
合計が合わないときの見分け方と直し方
3D参照が使える関数と、使えない関数

完成形。集計シートのB2に3D参照のSUMが入り、月別1〜月別3の合計3,008,000が出ている

集計シートの B2 に1本の数式を入れるだけで、3枚のシートの売上がまとめて合計されます。

どんな場面で使うか

「シートは違うが、セルの位置は同じ」表なら、どれも同じ書き方で集計できます。

月別の売上シートから、四半期や年間の合計を出す
部門ごとのシートを合わせて、全社の合計を出す
拠点別・担当者別のシートから、全体の件数を数える

シートごとに行数や列の並びが違う表には向きません。3D参照は、どのシートでも同じ番地を足すだけだからです。

基本説明：3D参照とは

3D参照は、数式の中で複数のシートをまとめて指定する書き方です。

通常の参照は「行と列」でセルを指します。3D参照はそこに「シート」の軸を足し、重ねたシートの同じ番地を串で刺すように指定します。

使うデータ

サンプルファイル（記事末尾）には、月別1・月別2・月別3・集計の4シートがあります。月別1〜3はどれも A列が商品、B列が売上で、データは B2:B10 の9行です。

元データ。月別1シートのA1:B10と、シート見出しの月別1・月別2・月別3・集計

3枚ともセルの位置は同じで、売上の数値だけが違います。

1つずつ書く方法との違い

書き方	数式	シートが増えたとき
シートを1つずつ並べる	`=SUM(月別1!B2:B10, 月別2!B2:B10, 月別3!B2:B10)`	数式に1つずつ足す
3D参照	`=SUM(月別1:月別3!B2:B10)`	開始と終了の間に入れれば、数式はそのまま

シートが12か月分あると、1つずつ並べる書き方は12個の参照が続きます。3D参照なら、何枚になっても数式の長さは変わりません。

手順

1. 各シートのレイアウトをそろえる

どのシートでも、同じ番地に同じ意味のデータを置きます。この記事では、売上はどのシートでも B2:B10 です。

2. 数式を入力する

集計シートを開く
B2 を選ぶ
次の数式を入力して Enter キーを押す

=SUM(月別1:月別3!B2:B10)

冒頭の画像がこの状態です。B2 に 3,008,000（3シート・27件の合計）が返ります。

シート名を打たずに、マウスで指定することもできます。

集計シートの B2 に =SUM( と入力する
月別1 のシート見出しをクリックする
Shift キーを押したまま、月別3 のシート見出しをクリックする
B2:B10 をドラッグして、Enter キーを押す

3. シートを追加するときは、開始と終了の間に入れる

集計されるのは、シート見出しの並びで開始シートと終了シートに挟まれたシートです。間に入れたシートは、数式を直さなくても集計に入ります。

外側に入れたときの症状と直し方は、下の「よくあるエラー」で画面つきで説明します。

使用例：平均・件数・最大値も同じ書き方

SUM 以外の集計関数にも、同じ範囲をそのまま渡せます。サンプルの集計シートの B2〜B5 に入っている数式です。

やりたいこと	数式	結果
合計	`=SUM(月別1:月別3!B2:B10)`	3,008,000
平均	`=AVERAGE(月別1:月別3!B2:B10)`	111,407
件数	`=COUNT(月別1:月別3!B2:B10)`	27
最大値	`=MAX(月別1:月別3!B2:B10)`	161,000

集計シートのB4を選んだ状態。数式バーにCOUNTの3D参照、B2〜B5に4つの結果

件数の 27 は、9行×3シートぶんの数値を数えた結果です。平均のセルは小数点以下を表示しない形式なので、111,407 と表示されます。

シート名にスペースや記号があるときは、=SUM('4月実績:6月実績'!B2:B10) のように、開始と終了をまとめて ' で囲みます。片方だけを囲むのではありません。

よくあるエラー・注意点

追加したシートの分が合計に入らない

原因新しいシートを、終了シート（月別3）より右に作りました。外側のシートは集計に入りませんが、数式はエラーを出しません。

次の画像は、月別3 をコピーして右側に月別4 を作った状態です。

月別3の右に月別4を追加した状態。集計シートのB2は3,008,000のまま

月別4 の売上は入らず、合計は 3,008,000 のままです。

見分け方 合計が思ったより小さいときは、シート見出しの並びを見ます。終了シートより右に、集計したいシートが無いかを確かめます。

直し方 月別4 の見出しを月別3 より左へドラッグして、範囲の内側に移します。シートを並びのとおりに残したいときは、月別4 が集計シートより左（月別3 と集計の間）にあることを確かめてから、数式の終了シート名 月別3 を 月別4 に書き換えます。集計シートの右に月別4 がある状態で書き換えると、範囲に集計シート自身が入って循環参照になります。

数式の終了シートを月別4に直した状態。集計シートのB2が4,056,000になっている

月別4 の 1,048,000 が加わり、合計は 4,056,000 になります。

集計に入れたくないシートまで入っている

原因月別1 と月別3 の間に、メモ用や作業用のシートを置きました。対象はシート見出しの並びで決まり、シート名の番号とは関係ありません。

直し方 そのシートを、開始シートより左か終了シートより右へ移します。

集計の数式があるシート自身を範囲の内側に置くのも避けます。自分のセルを自分で合計することになり、循環参照になります。

開始・終了のシートを削除・移動した

原因開始か終了のシートの片方を削除・移動しても、エラーは出ず、範囲は残ったシートの並びに合わせて変わります。そのため、気づかないうちに集計の対象が変わります（範囲に入っているシートをすべて削除したときは #REF! になります）。

直し方 数式バーで、開始と終了のシート名が意図したものかを確かめます。違っていれば、数式のシート名を書き直します。

SUMIF や VLOOKUP で使うとエラーになる

原因 3D参照に対応しているのは、主に合計・統計系の関数です。

使える：SUM AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV など
使えない：IF SUMIF SUMIFS COUNTIF VLOOKUP XLOOKUP FILTER など

直し方 条件付きの集計や検索を複数シートに対して行うときは、各シートのデータを1枚にまとめてから集計するほうが確実です。

まとめ

複数シートの同じセルは、=SUM(月別1:月別3!B2:B10) の1本で合計できる
集計されるのは、シート見出しの並びで開始と終了に挟まれたシート。名前の番号順ではない
終了シートより右に作ったシートは、エラーも出ないまま合計から漏れる。内側へ移すか、数式の終了シートを書き換える
条件付き集計（SUMIF など）や検索系の関数は、3D参照に対応していない

各シートのレイアウトさえそろえておけば、月次・部門別・拠点別の集計は数式1本で済みます。

【Excel】 INDIRECT関数を使ったセルの参照 — シート名を文字列から組み立てて参照する方法
【Excel】SUBTOTAL関数の「9」とは？集計方法の番号一覧とフィルター後の集計 — 1枚のシート内で、フィルターに連動した集計をする方法

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

3d_reference-3d-reference-sample.xlsx