やりたいこと・現在の悩み
合計する範囲を毎月変更したい。SUM関数で毎月、合計範囲を変更するのは手間だし、変更忘れや範囲指定間違いの心配がある。月が更新されるごとに自動的に合計範囲を変更したい。
想定する場面
実績予算対比で累計実績や累計予算を表示させる。
実績予算対比では、単月の対比だけでなく、累月の対比もしますよね。4月から始まる会社では6月の経理レポートは4月、5月、6月の3ヵ月分の累計数字を表示させる必要があります。
Excelで累計の予算や実績をはどのような関数を使えばよいでしょうか？
一番簡単な方法は、毎月合計範囲を変更していくことです。5月であれば、=SUM(B3:C3)、6月であれば、=SUM(B3:D3)というようにです。
変更箇所が少なければこの方法でもよいでしょう。
でも、通常経理レポートでは表示項目が多岐に渡ることが多いのではないでしょうか。売上1つを取っても、○○売上、▲▲売上、○▲売上などと細目を作っている会社もあるでしょう。
そのような時、毎月合計範囲を変更するのは手間ですし、時には一部分だけ変更を忘れたというような間違いをしやすくなります。
そこでお勧めしたいのがOFFSET関数を使った、合計範囲の自動変更です。
手順
OFFSET関数を初めて聞いたという人も多いでしょう。Excelの中でも知名度の低い関数ですが、実はめちゃくちゃ使い勝手のよい関数です。特に経理関係の仕事にはうってつけの関数です。
今回の関数式のゴール
最初に、今回使用する関数の最終形を提示します。最終形を最初に見ておくことで、次からの説明が何のためにやっているのか、わかりやすくなると思います。
=SUM(OFFSET(B3,0,0,1,$E$9))
今回使うのはSUM関数とOFFSET関数です。
SUM関数は =SUM(A1:B6)などの使い方で指定した範囲の数値を合計する関数です。通常、SUM関数では合計範囲が固定されています。
今回の目標は合計範囲を月によって自動的に変動させることです。つまりA1:B6という範囲を月によってA1:C6などと変更させることです。
OFFSET関数は範囲を指定する関数です。OFFSET関数を使うことによって、( )の範囲の部分を条件によって変動させることができます。
OFFSET関数の構造
OFFSET関数は次のような形になっています。
=OFFSET(参照,行数,列数,高さ, 幅)
全部で5つの要素があります。要素が5つもあって複雑そうに見えますが、慣れれば簡単です。
今回の事例である予算シート使いながら説明します。4月～6月までの売上高予算合計をB10セルに表示させたいとします。
=SUM(OFFSET(B3,0,0,1,$E$9))
参照
スタート位置となるセルです。今回の例では4月の売上高予算値が入力されているセル「B3」が参照セルとなります。
行数・列数
行数と列数はセットなので一緒に説明します。
結論から言えば今回の事例では行数・列数とも0（数字のゼロ）にします。やや複雑なので、今回は行数・列数を共に0にするという理解だけで大丈夫です。
興味があれば下記の参考をお読み下さい。
行数・列数は基準となるセル位置を決定するための要素です。例えば参照セルをA1、行数を2、列数を3と指定したとします。その場合、基準セルはA1から2行下に移動、3列下に移動したD3となります。
この行数・列数を使うと、より高度な集計ができるようになりますが、今回は参照セル＝基準セルとなるため、行数・列数に0を指定します。
高さ・幅
高さと幅はセットなので一緒に説明します。
基準セルからの範囲を指定するための要素です。今回は行数・列数が0で基準セルは参照セルB3と同一です。
B3の4月売上高予算からB6の6月売上高予算までを合計範囲としたいので、幅（合計する列数）は3とします。高さ（合計する行数）は1です。
仕上げ
ここまでの手順をまとめると、4月～6月の売上高予算を集計する数式は
=SUM(OFFSET(B3,0,0,1,3))
となります。
ただ、このままだと翌月になって7月までの累計予算を集計するのが面倒です。上記の幅「3」を月によって変えられるようにパラメータ化します。
=SUM(OFFSET(B3,0,0,1,$E$9))
図表のE9の数字を変更すれば自動的に合計範囲を変更することができます。4月～7月までの累計であれば幅が4ですので、E9セルに4を入れます。
この方法のメリット
- セル1つの数字変更で累計範囲を自動的に変更できる。
- 前の月やもっと前の月に戻りたいと思ったときでも簡単に戻れる。
- 一度フォーマットを作ってしまえば、Excelが苦手な人（SUM関数がわからない）という人でも更新できる。
