今回は、Excelで複数のワークシートにある同じ位置のセルをまとめて集計できる3-D参照について、基本の書き方、作成手順、シートを追加・移動したときの動き、使いこなすためのコツを紹介します。
この記事の操作説明は、Windows版のExcel（Microsoft 365のExcelを含むデスクトップ版）を前提にしています。Mac版やWeb版では、画面の表示や操作が異なる場合があります。
3-D参照とは：複数シートの同じセルをまとめて指定する方法
月ごとや部署ごとにシートを分けて、同じレイアウトの表を作っているブックはよくあります。こうしたブックで「全シートの合計を出したい」と考えたとき、各シートのセルを一つずつ足していく数式は長くなりがちで、シートが増えるたびに修正も必要になります。
3-D参照は、複数のシートにまたがる同じセルやセル範囲を、一つの参照としてまとめて指定する方法です。表の形がそろったシートを集計する場面に向いています。
3-D参照の書き方
3-D参照は、「最初のシート名:最後のシート名!セル番地」の形で書きます。たとえば、「4月」から「6月」までのシートにある各セルB3を合計する場合は、次のように入力します。
- =SUM(‘4月:6月’!B3)
セル範囲を指定することもできます。「Sheet2」から「Sheet6」までのA2からA5をすべて合計するなら、次のようになります。
- =SUM(Sheet2:Sheet6!A2:A5)
ここで大切なのは、3-D参照が「名前を挙げたシートだけ」ではなく、最初のシートと最後のシートの間に並んでいるすべてのシートを対象にするという点です。シート見出しの並び順がそのまま集計範囲になります。
シート名の扱いで気を付けたいこと
シート名が数字で始まる場合や、スペースを含む場合などは、シート名の部分を半角の引用符（’）で囲む必要があります。手入力するとこの部分で間違えやすいため、次に紹介するシート見出しをクリックして作る方法を使うと、Excelが必要な記号を自動で補ってくれます。
3-D参照を作成する手順
数式を手入力する代わりに、シート見出しとセルをクリックして作るのが確実です。
- 集計結果を表示したいセルを選択します。
- 「=」に続けて関数名と左かっこを入力します（例：=SUM( ）。
- 集計したい最初のシートの見出しを選択します。
- Shiftキーを押しながら、最後のシートの見出しを選択します。
- 集計したいセルまたはセル範囲を選択します。
- 数式を完成させてEnterキーを押します。
集計結果用のシート（「合計」シートなど）は、集計対象のシート範囲の外側に置いておきます。範囲内に入れてしまうと、集計結果のセル自身が計算対象に含まれ、循環参照などの意図しない状態になるおそれがあります。
3-D参照で使える主な関数
3-D参照はどの関数でも使えるわけではありません。Microsoftのサポート情報では、次のような関数が対象として案内されています。
- SUM：数値の合計
- AVERAGE、AVERAGEA：平均
- COUNT、COUNTA：数値や空白でないセルの個数
- MAX、MAXA、MIN、MINA：最大値、最小値
- PRODUCT：数値の積
- STDEV、STDEVA、STDEVP、STDEVPA：標準偏差
- VAR、VARA、VARP、VARPA：分散
- VSTACK、HSTACK：配列を縦方向・横方向に連結
VSTACK関数とHSTACK関数は、Microsoft 365など新しいバージョンのExcelで使える関数です。たとえば「=VSTACK(‘4月:6月’!A2:C10)」のように指定すると、各シートの同じ範囲を縦に積み重ねた一覧を作れます。使っているExcelにこれらの関数がない場合は、SUMなど従来の関数で集計します。
シートを追加・移動・削除したときの3-D参照の動き
3-D参照は、シートの並び順で範囲が決まります。そのため、シートを操作したときに集計対象がどう変わるかを知っておくことが大切です。ここでは「=SUM(Sheet2:Sheet6!A2:A5)」を例に整理します。
- 範囲の間にシートを挿入・コピーした場合：Sheet2とSheet6の間に追加したシートのA2からA5も、自動的に計算に含まれます。
- 範囲の間のシートを削除した場合：削除したシートの値は計算から外れます。
- 範囲の間のシートを範囲外へ移動した場合：移動したシートの値は計算から外れます。
- 端のシート（Sheet2やSheet6）を移動した場合：新しい位置に合わせて範囲が調整されます。ただし、端のシートの順序を入れ替えるような移動をすると、範囲の端になるシートが変わります。
- 端のシートを削除した場合：そのシートの値は計算から外れます。
コツ：「開始」「終了」の目印シートを用意する
この仕組みを活用すると、シートを追加するだけで集計対象を増やせるブックを作れます。
- 集計したいシート群の前に「開始」という空のシート、後ろに「終了」という空のシートを置きます。
- 集計用の数式は「=SUM(開始:終了!B3)」のように、目印のシートを端にして作ります。
- 新しい月や部署のシートは、「開始」と「終了」の間に挿入します。
こうしておけば、シートを増やすたびに数式を直す必要がありません。目印のシートは何も入力しないままにしておくと、集計結果に影響しません。シートの見出しに色を付けておくと、目印であることが分かりやすくなります。
3-D参照に名前を付けて数式を読みやすくする
同じ3-D参照を何度も使う場合は、名前を定義しておくと数式が短くなり、内容も把握しやすくなります。
- [数式]タブの[定義された名前]グループで、[名前の定義]を選択します。
- [新しい名前]ダイアログボックスの[名前]ボックスに、分かりやすい名前（例：売上合計範囲）を入力します。
- [参照範囲]ボックスに入っている参照をBackspaceキーで消し、「=」だけが残る状態にします。
- 参照したい最初のシートの見出しを選択します。
- Shiftキーを押しながら、最後のシートの見出しを選択します。
- 参照したいセルまたはセル範囲を選択し、[OK]を選択します。
名前を定義したあとは、「=SUM(売上合計範囲)」のように入力するだけで集計できます。数式を見たときに何を集計しているのかが伝わりやすくなり、ほかの人とブックを共有する場合にも役立ちます。
3-D参照を使うときの注意点とコツ
- 表のレイアウトを全シートでそろえる：3-D参照は「同じ位置のセル」を集めるため、シートごとに行や列がずれていると、違う項目を足してしまいます。新しいシートは既存のシートをコピーして作ると、レイアウトを保ちやすくなります。
- シートの並び順を意識する：シート見出しをドラッグして並べ替えると、集計範囲が変わることがあります。並べ替えたあとは、集計結果が想定どおりかを確認します。
- 集計用シートは範囲の外に置く：合計や平均を表示するシートは、先頭か末尾など、範囲から外れた位置に配置します。
- 作業グループの解除を確認する：Shiftキーで複数のシート見出しを選んだまま入力を続けると、選択中のすべてのシートに同じ内容が入力されます。数式を確定したあと、ほかのシート見出しを選択してグループが解除されているかを確認すると安心です。
- 条件付きの集計は別の方法も検討する：SUMIFなど、案内されている一覧にない関数は3-D参照の対象外です。条件を付けて複数シートを集計したい場合は、各シートの結果を個別に求めてから合計するなど、別の組み立て方を考えます。
まとめ
Excelの3-D参照を使うと、「=SUM(Sheet2:Sheet6!A2:A5)」のような短い数式で、複数シートの同じセルや範囲をまとめて集計できます。作成するときは、最初のシート見出しを選んだあとShiftキーを押しながら最後のシート見出しを選ぶ方法を使うと、入力ミスを防げます。
3-D参照は、シートの並び順で集計範囲が決まるのが特徴です。範囲の間にシートを追加すれば自動的に計算に含まれ、範囲の外に移動すれば除外されます。「開始」「終了」の目印シートを用意しておけば、月や部署が増えても数式を直さずに集計を続けられます。
表のレイアウトをそろえ、集計用シートを範囲の外に置くといった基本を押さえておくと、複数シートの集計作業がシンプルになります。