「支店ごと」「月ごと」に同じフォーマットのシートが並んでいて、それらを串刺しでまとめて集計したい、という場面はよくあります。シートを1枚ずつSUMするのは面倒ですし、シート数が増減するたびに数式を書き直すのも手間です。
こうしたときに便利なのが、Excelの「3D参照」という機能です。SUM(Sheet1:Sheet3!A1)のように、開始シートから終了シートまでをまとめて指定できます。この記事では、3D参照の基本と、それをVBAから自動設定する方法、シートが増減しても数式が自動で追従するテクニックまで解説します。
3D参照とは
3D参照とは、複数のシートの同じセル（または同じ範囲）をまとめて計算対象にする参照方法です。たとえば「Sheet1」から「Sheet3」までのA1セルを合計したい場合、次のような数式になります。
=SUM(Sheet1:Sheet3!A1)
Sheet1:Sheet3 の部分が「Sheet1からSheet3までの、間にあるすべてのシート」を意味します。SUM以外にも、AVERAGE・COUNT・MAX・MINなど多くの関数で同じ書き方が使えます。
VBAから3D参照の数式を設定する
3D参照は、VBAのRangeオブジェクトとして直接扱うことはできません（Worksheets("Sheet1:Sheet3")のような指定はエラーになります）。そのため、VBAから3D参照を使う場合は、数式の文字列を組み立ててRange.Formulaに代入するのが基本の方法です。
Sub SetSumFormula3D()
Range("B1").Formula = "=SUM(Sheet1:Sheet3!A1)"
End Sub
シート名を固定で書く代わりに、実行時にシート名を取得して動的に組み立てることもできます。
Sub SetSumFormula3DDynamic()
Dim firstSheet As String
Dim lastSheet As String
Dim formulaStr As String
firstSheet = Worksheets(1).Name
lastSheet = Worksheets(Worksheets.Count).Name
formulaStr = "=SUM('" & firstSheet & ":" & lastSheet & "'!A1)"
Range("B1").Formula = formulaStr
End Sub
シート名の前後を単一引用符'で囲んでおくと、シート名にスペースや日本語が含まれていても正しく認識されるため、動的に組み立てる際は常にこの書き方をしておくと安全です。
3D参照の限界と、VBAでの代替手段
3D参照は数式としては便利ですが、次のような制約があります。
- シートの「並び順」で範囲が決まるため、開始シートと終了シートの間に、集計対象にしたくないシートがあると誤って含まれてしまう
- 配列数式や一部の関数（
VLOOKUPなど検索系の関数）では3D参照が使えない
このような場合は、数式を使わずVBAだけでシートを1枚ずつ処理して集計したほうが確実です。
Sub SumAcrossSheetsVBA()
Dim ws As Worksheet
Dim total As Double
total = 0
For Each ws In Worksheets
If ws.Name <> "集計" Then
total = total + ws.Range("A1").Value
End If
Next ws
Worksheets("集計").Range("B1").Value = total
End Sub
集計シート自身を除外しながら全シートのA1を合計する形にしているため、集計先のシート名を条件から外すだけで、シートが何枚追加されても正しく動作します。
シートの増減に自動で追従させるテクニック
3D参照は「開始シートから終了シートまで」を対象にするという性質を逆手に取ると、シートの増減に自動で追従する集計表を作れます。あらかじめ集計対象の前後に、空の「境界シート」を用意しておく方法です。
- 「開始」という名前の空シートを一番左に置く
- 集計したいデータシート（支店A、支店B…）を並べる
- 「終了」という名前の空シートを一番右に置く
この状態で数式を次のようにしておきます。
=SUM('開始:終了'!A1)
「開始」「終了」自体は空シートなので合計には影響しませんが、間にシートを追加すれば自動的に集計対象へ含まれます。この境界シート方式は、VBAで新しいシートを追加する処理と組み合わせると特に効果的です。
Sub AddBranchSheetBeforeEnd()
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(Before:=Worksheets("終了"))
newSheet.Name = "支店C"
End Sub
「終了」シートの直前に新しいシートを挿入することで、3D参照の集計範囲に自動的に組み込まれます。
注意点
- 非表示シートも集計対象になる：3D参照は、開始〜終了の間にあるシートが非表示になっていても集計対象に含まれます。意図せず非表示にしたシートが混ざっていないか確認しましょう。
- シートの並び替えに注意：シートの順序をドラッグで変更すると、3D参照の集計範囲も一緒に変わってしまいます。境界シート方式を使う場合は特に、シートの並び順を崩さないよう運用ルールを決めておくと安全です。
- 検索系関数では使えない：
VLOOKUPやMATCHなど、範囲を検索する関数では3D参照は使用できません。あくまで集計系の関数（SUM・AVERAGE・COUNTなど）専用の機能です。
まとめ
3D参照を使うと、同じフォーマットの複数シートをSheet1:Sheet3!A1のような書き方でまとめて集計できます。VBAからはRange.Formulaに数式文字列を代入して設定するのが基本で、シート名を動的に組み立てれば、シート構成が変わっても対応できる集計表が作れます。境界シート方式と組み合わせれば、支店や月が増えるたびに数式を書き直す手間からも解放されます。ぜひ実務の集計マクロに取り入れてみてください。
コメント