SUM関数で合計が合わない対処法！合計範囲を動的にする相対参照で！

ExcelのSUM関数で合計が合わず、確認と修正の手間を軽減したいときはないでしょうか。

けど、そんな中で悩むことは、

・ExcelのSUM関数で合計の範囲が漏れないようにする方法がわからない
・ExcelのSUM関数で確認の手間をへらす方法がわからない

ですよね。

今回はそんなお悩みを解決する

・SUM関数の合計範囲を動的にしてSUM関数で合計が合わないミスを予防する方法

についてまとめます！

SUM関数で合計が合わない原因

SUM関数で合計が合わない主な原因は合計範囲が漏れてしまっていることです。

表データの追加や、編集を繰り返すうちに合計範囲が変わってしまい、いざデータを突き合わせてみると合計が合わないといったケースが想定されます。

SUM関数で合計が合わない対処法

対処法として、SUM関数の合計範囲を動的にすることによりSUM関数の調整を不要とする方法があります。

前回以下の記事でもご紹介していますが、数式が複雑になるディメリットがありました。

SUM関数の合計範囲を自動で調整する方法！列/行両対応！

今回の方法は、1行の数式のみで実現していきます。

SUM関数の縦の合計範囲を動的にする

まずは縦方向のSUM関数の合計範囲を動的にする方法となります。

数式は以下の通りです。

=SUM(INDIRECT("R1C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),0))

1行目からSUM関数が配置されている1個上の行まで動的に範囲が設定されます。

早速配置してみましょう。

適当な数字を縦方向に入れていき、その下に上記の数式を入れます。

はい、問題なく合計がされていますね。

別の合計範囲でも試してみましょう。

はい、同じ数式ですが、合計範囲が調整され、その合計が表示されていますね。

タカヒロ

数式は一切変更していませんので、手作業で合計範囲を変更する手間が省けますね。

SUM関数の横の合計範囲を動的にする

次に横方向のSUM関数の合計範囲を動的にする方法について説明をします。

数式は以下の通りです。

=SUM(INDIRECT("R"& ROW()&"C1"&":R"& ROW() & "C" & COLUMN()-1,0))

1列目からSUM関数が配置されている1個前の列まで動的に範囲が設定されます。

早速配置してみましょう。

はい、問題なく合計がされていますね。

別の行の合計範囲でも試してみましょう。

はい、同じ数式ですが、合計範囲が調整され、その合計が表示されていますね。

縦方向に合計する数式の説明

縦方向に合計する数式について説明をします。

=SUM(INDIRECT("R1C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),0))

この式では、INDIRECT関数を使用して、合計範囲を動的に選択しています。

"R1C"&COLUMN()&"

合計範囲の1行目のセルの位置を取得します。”R”は行を、”R1C”はセルの位置を表します。COLUMN() は SUM 関数があるセルの列を取得するために使用されます。

タカヒロ

合計範囲の開始は1行目のセルに設定されていますが、2行目にしたいなど変更をする場合は”R**C”の**の部分を変更してください。

":R"&ROW()-1&"C"&COLUMN()

合計範囲の最後のセルの位置を取得します。ROW()-1 は SUM 関数があるセルの一つ上の行を取得するために使用されます。COLUMN() は SUM 関数があるセルの列を取得し、開始セルと終了セルを同じ列にすることで縦方向の合計範囲としています。

INDIRECT("R1C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),0)

INDIRECT関数を使用して、合計範囲を動的に選択します。0 を第二引数に渡すことで、R1C1 形式の相対参照を使用することができます。
参考：https://support.microsoft.com/ja-jp/office/indirect-%E9%96%A2%E6%95%B0-474b3a3a-8a26-4f44-b491-92b6306fa261

タカヒロ

INDIRECT関数の第二引数は0以外にもFalseでもOKです。なお第一引数は文字列にする必要があるので””でくくるようにしましょう。

仕上げに、SUM関数があるセルに入力することで、合計範囲が SUM関数があるセルの一つ上の行から1行目までの位置に自動的に設定されます。