Excelで特定の範囲の合計額を計算する関数として代表的なのものにSUM関数がありますが、今回はそのほかの方法としてSUM関数にOFFSET関数を組み合わせて使う方法をご紹介します。
目次
SUM関数の欠点
SUM関数は、初心者が最初に覚える関数というぐらい基本的でよく使う関数です。
ところが範囲外に行や列を”コピーして挿入”すると範囲の参照を設定し直す必要があり、計算間違いの元になるという欠点があります。
この表のD列とE列の間にC社の列コピーして挿入すると
コピーした列はSUM関数の範囲には入りません
これを回避するには、次のようにコピーではなく新規列や新規行を挿入して
数字を入力する方法をとれば、SUM関数の範囲も訂正されます
しかし、合計行のE9セルに”SUM(E3：E8)”の計算式を設定する必要があるように、挿入する列に算式が入っているセルがある場合などは、どこかの列をコピーして挿入した方が便利な場合もあります。
そこで役立つのが、今回ご紹介するOFFSET関数なのです。
OFFSET関数
説明
書式は次のようになります。
高さと幅は今回は使いませんので割愛します。
この関数は基準となるセルから、指定された行数と列数だけシフトした位置にあるセルを返します。
例２）OFFSET(C3,-1,-1)の場合 → B2
使い方
今回の設例ではE3セルに次のような計算式を入力します。
つまりSUM関数の範囲の左端と右端を”月の列のセル”と”合計の列のセル”に指定するということです。
次の表のD列とE列の間にD列をコピーして挿入すると
F3セルに、挿入した列の金額も含めた金額が表示され、計算式も自動的に変わります
F4からF9までのセルはOFFSET関数を使っていないので、挿入した列の金額は反映されていません。
デメリット
ただし次のようなデメリットもあります。
（SUM関数は”ALT＋SHIFT＋＝”のショートカットが使えて便利です）
・参照範囲が確認しづらい
特に参照範囲が確認しづらいのは少し不便です。
SUM関数だけだとわかりやすいですね
OFFSET関数を組み合わせると参照範囲はわかりづらいです
まとめ
SUM関数だけを使う場合と、SUM関数とOFFSET関数を組み合わせて使う場合とでは、どちらも一長一短があります。
私の場合、そのExcelブックを自分だけが使うのであれば、SUM関数の参照範囲が外れてしまうケースを認識しているので、SUM関数のみを使います。
一方、そのExcelブックを他人に使ってもらう場合は、SUM関数の参照範囲が外れてしまうケースを認識されていない可能性があるので、SUM関数とOFFSET関数を組み合わせる方法を採用しています。
◆編集後記◆
Excelは便利な反面、計算式の参照範囲が間違っていると大事に至る可能性があるので、そういった間違いが起こりにくい計算式の組み方が重要だなと感じています。
ただし、複雑になりすぎてもいけないので、そのあたりのバランスはいつも悩みますね。
山端一弥
最新記事 by 山端一弥 (全て見る)
- 2021年シーズン開幕！ - 2021年3月29日
- 【青の獅子標】終戦！CS進出ならずも。。 - 2020年11月10日
- 【青の獅子標】勝負の9連戦 - 2020年11月2日
- 【青の獅子標】３連覇消滅も新たな目標へ - 2020年10月26日
- 【青の獅子標】求む。打線の奮起 - 2020年10月20日
