導入: なぜこの技術が重要なのか
実務において、複数のシートやバラバラに管理された表からデータを集計する場面は非常に多いものです。Excelの「3D集計」や「統合」機能は便利ですが、セルの配置が固定されていたり、更新のたびに手間がかかったりと、柔軟性に欠けることが多々あります。
今回紹介する「SUMIF関数」と「INDIRECT関数」を組み合わせる手法を覚えれば、シート名や列の範囲が動的に変わるようなデータに対しても、数式をコピーするだけで瞬時に集計できるようになります。業務効率を劇的に改善したい方は、ぜひ習得してください。
基礎知識: SUMIFとINDIRECTの役割
SUMIF関数は、「特定の条件に一致するセルだけを合計する」関数です。基本中の基本ですが、範囲指定を工夫することで応用範囲が大きく広がります。
一方、INDIRECT関数は「文字列で指定したセル参照を、実際のセル参照として機能させる」関数です。例えば、セルA1に「Sheet2」と入力されており、別の場所でINDIRECT(A1 & “!B2”)と書くと、Excelは「Sheet2のB2セル」を見に行きます。これらを組み合わせることで、数式を書き換えることなく、参照先を自由自在に操れるようになります。
実装/解決策: 数式を使い回す仕組みづくり
集計のコツは、「数式を固定せず、参照値をセルに持たせる」ことです。
例えば、シート名が「1月」「2月」と続く場合、各シートの指定セルを参照する際、シート名を直接数式に打ち込むのではなく、セルに入力した文字列をINDIRECT関数で読み込みます。こうすることで、オートフィルを使って一気に数式をコピーし、複数のシートや項目を横断的に集計することが可能になります。
サンプルプログラム: VBAで動的に集計範囲を生成
VBAを使って、このロジックをさらに自動化するコード例を紹介します。セルに数式を入力する際、動的な範囲指定を自動生成します。
Sub SetDynamicSumif()
' 目的: 指定したシートの範囲に対してSUMIF関数をセルにセットする
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("集計表")
' 例: A列に科目名、B列に合計値を出力する場合
' INDIRECT関数を使って、別シートの範囲を動的に参照させる
' 数式:=SUMIF(INDIRECT("データシート!A:A"), A2, INDIRECT("データシート!B:B"))
Dim targetSheetName As String
targetSheetName = "データシート"
' セルB2にSUMIF関数を入力
ws.Range("B2").Formula = "=SUMIF(INDIRECT(""" & targetSheetName & "!A:A""), A2, INDIRECT(""" & targetSheetName & "!B:B""))"
' セルB2の数式を下の行へコピー
ws.Range("B2").AutoFill Destination:=ws.Range("B2:B10")
MsgBox "集計用数式の入力が完了しました!"
End Sub
応用・注意点: 現場で役立つポイント
1. シート名の参照: シート名にスペースが含まれる場合は、INDIRECT関数内で `’`(シングルクォーテーション)で囲む必要があります。これを忘れるとエラーになるため注意してください。
2. 再計算の重さ: INDIRECT関数は「揮発性関数」と呼ばれ、シートが更新されるたびに再計算が走ります。データ量が数万行を超えるような巨大なブックで使用すると動作が重くなる可能性があるため、必要な範囲に限定して使用することをお勧めします。
3. エラー回避: 参照先のシートが存在しない場合、INDIRECT関数はエラーを返します。実務ではIFERROR関数と組み合わせて、「=IFERROR(SUMIF(…), 0)」のように、エラー時に0を表示させる工夫をすると、表の見た目が格段に美しくなります。
