Excel 関数

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

SUM関数の合計範囲を自動で調整する方法！

SUM関数の合計範囲を自動で調整させたいときはないでしょうか。

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

・SUM関数の合計範囲を自動で調整させたいが方法がよくわからない

ですよね。

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

・SUM関数の合計範囲を自動で調整する方法
・SUM関数の横行の合計範囲を自動で調整する方法
・SUM関数の行/列の合計範囲を自動で調整する方法

についてまとめます！

タカヒロ
タカヒロ
以下のようなSUM関数の合計範囲の漏れってほんと多いですよね！タカヒロも最近指定気を受けたところです。。。

SUM関数の合計範囲を自動で調整させるイメージ

SUM関数の合計範囲を自動で調整させるイメージについて説明をします。

Excelファイルを用意し、B～D列に適当な数値を入れていきます。

SUM関数を入力すると合計がされます。

ただ、SUM関数の合計範囲が固定であると、範囲外に追加された値は合計されません。

そこで合計範囲を自動で調整してくれる数式を追加し、合計範囲が漏れてしまうことがないようにしていきます。

結果、範囲外に値を追加しても、範囲が自動調整されることにより、漏れることなく合計がされます！

さらに横方向の行の合計範囲にも対応させていき、同じく合計範囲を自動調整していきます！

さらにさらに！行と列、つまり表の範囲を合計範囲とする場合も自動調整させていきます！

それではさっそく使ってみましょう！

SUM関数の合計範囲を自動で調整する方法

SUM関数の合計範囲を自動で調整する方法について説明をします。

表を用意する

Excelのシートへ合計するための表を用意しましょう。

サンプルはB～D列に数値を記載しています。

数式

SUM関数の合計範囲を自動で調整する数式は以下の通りです。

=LET(
開始セル,B2,
最終行,ROW()-ROW(開始セル),
SUM(OFFSET(開始セル,0,0,最終行,1)))

数式を設定する

数式を設定していきましょう。

合計値を入れたいセルを選択し、上記の数式をコピーして貼り付けます。

合計を開始したいセルを指定します。今回は1行目は項目名であるため2行目の指定となり、列はBですので、B2を指定します。

開始セル,B2,

B列の数式をC,D列にドラッグします。

はい、合計がされましたね！

値を追加する

合計範囲が自動で調整されるか確認をしてみましょう。

以下のように何行か値を追加していきます。

はい！合計範囲が自動調整され、漏れることなく合計されていますね！

タカヒロ
タカヒロ
この間、数式は一切手を加えていません。

数式の説明

数式の内容について説明をします。

LET関数で変数と数式を扱えるようにしています。今回は開始セル、最終行を変数として定義しています。

LET()

変数開始セルにB2を代入しています。

開始セル,B2,

ROW関数で合計の数式を入れてある行番号を取得し、開始セルの行番号をマイナスすることにより数値がある合計範囲を算出します。

最終行,ROW()-ROW(開始セル),

OFFSET関数でSUM関数の範囲を設定しています。

OFFSET(開始セル,0,0,最終行,1)

SUM関数を加えて完成です。

SUM(OFFSET(開始セル,0,0,最終行,1)))

 

SUM関数の横行の合計範囲を自動で調整する方法

これまでは合計範囲の対象を縦列としていましたが、次は横行を合計範囲とし、自動で横の範囲調整をする方法について説明をします。

数式

SUM関数の横行の合計範囲を自動で調整する数式は以下の通りです。

=LET(
開始セル,B2,
最終列,COLUMN()-COLUMN(開始セル),
SUM(OFFSET(開始セル,0,0,1,最終列))
)

数式を設定する

数式を設定していきましょう。

横方向の合計値を入れたいセルを選択し、上記の数式をコピーして貼り付けます。

合計を開始したいセルを指定します。今回は2行目のE2に数式を入れていますので、前回と同様に2行目開始となるB2を指定します。

B2の数式をB3以降にドラッグします。

はい、横行の合計がされましたね！

値を追加する

横行の合計範囲が自動で調整されるか確認をしてみましょう。

以下のように何列か値を追加していきます。

はい！横行の合計範囲が自動調整され、漏れることなく合計されていますね！

タカヒロ
タカヒロ
横行の合計範囲の調整も一切おこなっていません！

数式の説明

数式の内容について説明をします。

LET関数で変数と数式を扱えるようにしています。今回は開始セル、最終行を変数として定義しています。

LET()

変数開始セルにB2を代入しています。

開始セル,B2,

COLUMN関数で合計の数式を入れてある列番号を取得し、開始セルの列番号をマイナスすることにより数値がある合計範囲を算出します。

最終列,COLUMN()-COLUMN(開始セル),

OFFSET関数でSUM関数の範囲を設定しています。

OFFSET(開始セル,0,0,1,最終列)

SUM関数を加えて完成です。

SUM(OFFSET(開始セル,0,0,1,最終列))

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

次は行と列を対象とする表を合計範囲とし、自動で表の範囲調整をする方法について説明をします。

タカヒロ
タカヒロ
行と列の合計ということで表の値の総合計をする形となります。

数式

SUM関数の表の合計範囲を自動で調整する数式は以下の通りです。

=LET(
開始セル,B2,
最終行,ROW()-ROW(開始セル),
最終列,COLUMN()-COLUMN(開始セル),
SUM(OFFSET(開始セル,0,0,最終行,最終列))
)

数式を設定する

数式を設定していきましょう。

表の右下の最後のセルを選択し、上記の数式をコピーして貼り付けます。

合計を開始したいセルを指定します。前回と同様にB2を指定します。

はい、表の範囲すべてが合計されましたね！

値を追加する

表の合計範囲が自動で調整されるか確認をしてみましょう。

以下のように行と列に何個か値を追加していきます。

はい！表の合計範囲が自動調整され、漏れることなく合計されていますね！

数式の説明

数式の内容について説明をします。

LET関数で変数と数式を扱えるようにしています。今回は開始セル、最終行を変数として定義しています。

LET()

変数開始セルにB2を代入しています。

開始セル,B2,

ROW関数で合計の数式を入れてある行番号を取得し、開始セルの行番号をマイナスすることにより数値がある合計範囲を算出します。

最終行,ROW()-ROW(開始セル),

COLUMN関数で合計の数式を入れてある列番号を取得し、開始セルの列番号をマイナスすることにより数値がある合計範囲を算出します。

最終列,COLUMN()-COLUMN(開始セル),

OFFSET関数でSUM関数の範囲を設定しています。

OFFSET(開始セル,0,0,最終行,最終列)

SUM関数を加えて完成です。

SUM(OFFSET(開始セル,0,0,最終行,最終列))

さいごに

いかがでしょうか。

今回は、

・SUM関数の合計範囲を自動で調整する方法
・SUM関数の横行の合計範囲を自動で調整する方法
・SUM関数の行/列の合計範囲を自動で調整する方法

についてまとめました。

タカヒロ
タカヒロ
この方法により「SUMの合計範囲が抜けてるよ！」と注意されなくなることでしょう。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。







コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA ImageChange Image

RECOMMENDこちらの記事も人気です。