EXCELで明細行の下に合計値を表示したい時ってありますよね。
明細行が固定の行数であればSUM関数を使って、固定範囲を指定すれば良いだけですが、明細行が可変の時ってありませんか？その場合、行を追加するたびに合計を表示するセルの範囲を変更したりして非常に面倒・・・どんな経験があるはず。
今回はINDIRECTを使用した明細行が動的に変化しても最終行は修正なしで合計値を出し続ける技です。これを覚えるだけで作業効率がグっとアップする事間違いなしです。
明細行の下に合計値を表示するには？
以下、家計簿のサンプルです。
家計簿のサンプル：
収入合計
各日付で収入、支出が記入する明細行となっています。この明細行は可変で必要に応じて行を追加する様に運用しています。そして合計の行に収入の合計と支出の合計を表示しています。明細行に応じて合計する範囲が変化しますが、数式を変更しなくても対応できるようにしています。
C5のセルは以下の数式になっています。
=SUM(INDIRECT("C2:C" & ROW()-1))これで日付の行が増えても数式を変更することなく常に合計を表示してくれます。
それでは詳しく解説をしていきます。
INDIRECT関数とは？
ここでのポイントはINDIRECT関数となります。
INDIRECT関数の概要
-
役割：指定された文字列をセル参照として扱う関数。
-
基本構文：
INDIRECT(ref_text, [a1])-
ref_text：セル参照や範囲を示す文字列（例：”A1″）。
-
a1：省略可能。A1形式（TRUEまたは省略時）またはR1C1形式（FALSE）。
-
特徴
-
動的にセルや範囲を参照可能。
-
別のシートや動的な行番号にも対応。
-
参照先が変更されても数式を修正する必要がない。
この様にINDIRECT関数は与えられた文字列をセル参照として取り扱える関数です。
非常に様々な用途に使える応用が効く関数です。
INDIRECT関数を活用した動的な合計計算
文字列をセルの参照に変更する便利な関数。これは動的な範囲を指定する事が可能だと言う事です。固定された範囲を使用せず、行数が増減しても対応可能な動的数式を使用する事が可能です。今回のケーススタディにマッチする関数ですね！
セル参照や範囲を示す文字列の部分に、ROW()関数を使用する事で合計値を表示する行がどの行になっても常に自分より上にある合計値の範囲を指定する事が可能になります。
収入合計の例
=SUM(INDIRECT("C2:C" & ROW()-1))仕組み：
-
“C2:C“：収入列の開始セル（C2）を文字列として指定。
-
ROW()-1：現在のセルの直前までの行を取得し、範囲を動的に設定。
-
SUM関数：指定された範囲の合計を計算。
動的な合計計算を導入するメリット：
-
明細の行数が増減しても自動的に対応。
-
数式の修正が不要。
-
別シートや複数の条件付き計算にも柔軟に対応可能。
INDIRECT関数を使った応用例
別のシートを参照する事も可能です。この場合「Sheet2」のシート名の部分も動的に参照させる事が可能なので使い方次第でかなり柔軟なり表を組むことが可能です。
別シートの参照：
=SUM(INDIRECT("Sheet2!A1:A" & COUNTA(Sheet2!A:A)))-
-
Sheet2!：別シートのデータ範囲を参照。
-
COUNTA：非空セルの数を取得し、範囲を動的に設定。
-
使用例のまとめ
| 計算内容 | 数式 |
|---|---|
| 自分より上にある動的な範囲の合計値を表示する | =SUM(INDIRECT("C2:C" & ROW()-1)) |
| 別シート参照 | =SUM(INDIRECT("Sheet2!A1:A" & COUNTA(Sheet2!A:A))) |
INDIRECT関数を使うメリット
-
動的な範囲参照が可能：
-
明細の行数が増減する帳票に最適。
-
別シートや複数のシート間でも柔軟に対応可能。
-
-
数式の修正が不要：
-
INDIRECT関数を使うことで、セルの追加や削除が発生しても数式を変更する必要がありません。
-
-
他の関数との組み合わせが可能：
-
SUM、COUNTA、IFなどと組み合わせることで複雑な条件付き計算も簡単に実現。
-
まとめと次のステップ
INDIRECT関数は、Excelでの動的な範囲参照や自動計算に非常に強力なツールです。特に、明細行が増減する帳票や複数シートを扱う場面で、その真価を発揮します。
この記事を参考に、動的なExcel帳票を活用して作業効率を大幅に向上させましょう！
コメント