Excelで特定の範囲の合計を計算するには〜SUM関数とOFFSET関数〜

Windows_10Excelで特定の範囲の合計額を計算する関数として代表的なのものにSUM関数がありますが、今回はそのほかの方法としてSUM関数にOFFSET関数を組み合わせて使う方法をご紹介します。

スポンサーリンク

SUM関数の欠点

SUM関数は、初心者が最初に覚える関数というぐらい基本的でよく使う関数です。

ところが範囲外に行や列を”コピーして挿入”すると範囲の参照を設定し直す必要があり、計算間違いの元になるという欠点があります。

この表のD列とE列の間にC社の列コピーして挿入するとWindows_10

コピーした列はSUM関数の範囲には入りません
Windows_10 3

これを回避するには、次のようにコピーではなく新規列や新規行を挿入して
Windows_10 4

数字を入力する方法をとれば、SUM関数の範囲も訂正されます
Windows_10 5

しかし、合計行のE9セルに”SUM(E3:E8)”の計算式を設定する必要があるように、挿入する列に算式が入っているセルがある場合などは、どこかの列をコピーして挿入した方が便利な場合もあります。

そこで役立つのが、今回ご紹介するOFFSET関数なのです。

OFFSET関数

説明

書式は次のようになります。

OFFSET(基準, 行数, 列数, [高さ], [幅])

高さと幅は今回は使いませんので割愛します。

この関数は基準となるセルから、指定された行数と列数だけシフトした位置にあるセルを返します。

例1)OFFSET(A1,1,1)の場合 → B2
例2)OFFSET(C3,-1,-1)の場合 → B2

使い方

今回の設例ではE3セルに次のような計算式を入力します。

=SUM(OFFSET(A3,0,1):OFFSET(E3,0,-1))

つまりSUM関数の範囲の左端と右端を”月の列のセル”と”合計の列のセル”に指定するということです。

次の表のD列とE列の間にD列をコピーして挿入するとWindows_10 9

F3セルに、挿入した列の金額も含めた金額が表示され、計算式も自動的に変わりますWindows_10 8

F4からF9までのセルはOFFSET関数を使っていないので、挿入した列の金額は反映されていません。

デメリット

ただし次のようなデメリットもあります。

・SUM関数だけに比べて計算式が入力しにくい
(SUM関数は”ALT+SHIFT+=”のショートカットが使えて便利です)
・参照範囲が確認しづらい

特に参照範囲が確認しづらいのは少し不便です。

SUM関数だけだとわかりやすいですねWindows_10 5

OFFSET関数を組み合わせると参照範囲はわかりづらいですWindows_10 6

まとめ

SUM関数だけを使う場合と、SUM関数とOFFSET関数を組み合わせて使う場合とでは、どちらも一長一短があります。

私の場合、そのExcelブックを自分だけが使うのであれば、SUM関数の参照範囲が外れてしまうケースを認識しているので、SUM関数のみを使います。

一方、そのExcelブックを他人に使ってもらう場合は、SUM関数の参照範囲が外れてしまうケースを認識されていない可能性があるので、SUM関数とOFFSET関数を組み合わせる方法を採用しています。

◆編集後記◆
Excelは便利な反面、計算式の参照範囲が間違っていると大事に至る可能性があるので、そういった間違いが起こりにくい計算式の組み方が重要だなと感じています。
ただし、複雑になりすぎてもいけないので、そのあたりのバランスはいつも悩みますね。

この記事が気に入ったら
いいね ! しよう

Twitter で
The following two tabs change content below.
山端一弥

山端一弥

大阪市阿倍野区の税理士です。 税理士事務所での10年間の修行を経て独立開業しました。 このブログは税務・会計・IT・趣味などについて「少しでも誰かの役に立てれば」という思いで書いています。 詳しいプロフィールはこちら