指定した期間の合計を求めたいときに使えるOFFSET関数

予算と実績を比較するとき、月ごとに比較することが一般的です。しかし、毎月の予実分析で、月ごとに細かく比較するのではなく、もう少し大きなくくり、例えば「前月まで」「当月」「来月以降」の３つに分けて比較したいときもありますよね。

例えば、予算額が以下のようにA1:L2セルに入力されていたとします。

予算の例

「当月」が９月であれば、「前月まで」は４月から８月まで、「来月以降」は10月から３月までを、それぞれSUM関数で合計すれば計算することができます。

しかし、翌月の10月になって、SUM関数で合計する範囲を変更するのは面倒ですよね。毎月続く作業ですし、どこかで範囲を間違えてしまう可能性もあります。せっかくなら合計する範囲を動的に変更したくなりますね。

そういった場合は、OFFSET関数が便利です。

OFFSET 関数 - Microsoft サポート

OFFSET関数を使うと、基準としたセルから指定した数のセル範囲の参照を得ることができます。例えば、今回の事例で４月から８月までの金額のセル範囲を取得するには、OFFSET関数を以下のように使います。

=OFFSET(A2,0,0,1,5)

最初のA2は、基準となるセルです。４月の金額が入力されているA2セルを基準（スタート位置）にしました。その次の０と０は、基準となるセルから上下、左右に何セル動かすか、を指定している数値です。今回は動かさないので、０と０を入力します。

その次の１は、取得したいセル範囲の行数です。今回は１行なので、１を入力します。最後の５は、取得したいセル範囲の列数です。今回は４月から８月までの５列なので、５を指定します。

まとめますと、このようにOFFSET関数の引数を指定することで、A2を基準にして（正確には、そこから上下に０、左右に０、基準を動かして）、１行、５列のセル範囲を取得する、ということになります。得られたセル範囲をSUM関数で合計することにより、４月から８月までの値を合計することができます。

今回の事例では、合計する期間を動的に変化させたいので、最後に指定した５を月に応じて変化させることを考えます。具体的には、当月が５月であれば１、６月であれば２、７月であれば３を入力させたい、となります。

そこで、まずは当月が左から何列目なのかを得る方法を考えます。当月はB6セルで選択できるようにします。B6セルを選び、ここを入力規則を使って5月から2月までリストで選べるようにします。

入力規則で当月をリストから選べるようにする

次にB6セルで当月として選んだ月が、A1:L1の範囲のどの位置にあるのか、MATCH関数で検索します。

=MATCH(B6,A1:L1,0)

MATCH 関数 - Microsoft サポート

この数式をB5セルに入力します。これにより、B6セルで８月を選ぶと５、９月を選ぶと６、といったように当月として選択した月が左から何列目なのかを得ることができます。

そして当月の位置から１を引くと、４月から前月までの列数を得ることができます。例えば、当月が８月であれば、当月の位置は５。すると５－１＝４で、前月までの列数、すなわち４月、５月、６月、７月の４列と一致します。

これで前月までの合計が以下の数式で計算できるようになりました。

=SUM(OFFSET(A2,0,0,1,B5-1))

当月の金額は、次の式で取得できます。

=INDEX(A2:L2,B5)

INDEX 関数 - Microsoft サポート

最後に、翌月以降の合計を求めます。OFFSET関数を使って、同じような方法で計算させることもできますが、簡単に年間合計から前月まで、と、当月を引くことで求めることができます。

まずD7セルに年間合計を計算させます。

=SUM(A2:L2)

そして、年間合計（D7セル）から「前月まで」（A7セル）と「当月」（B7セル）を引くことで、「来月以降」が計算できます。「来月以降」のC7セルの数式は次のようになります。

=D7-SUM(A7:B7)

これでB6セルの「当月」を5月から2月までの好きな月をリストから選ぶことで、動的に「前月まで」「当月」「来月以降」のそれぞれの合計額を計算できるようになりました。

OFFSET関数、MATCH関数、INDEX関数は、少し複雑かもしれませんが、シミュレーションなどで計算する範囲を動的に変更するときに便利な関数です。