えー…、今回はワタシのちょっと苦手な関数でして…。
とはいえ、世の中INDEX関数だけで済む話ばかりでもなく、仕方なくこれも覚えているけれど、頻繁に書くわけじゃないから「どうやって書くんだっけなー」と失敗しつつ組んでいる、という関数です。
その名はOFFSET関数。
便利なんですよ。便利なんですけど、ただワタシがいまだに慣れないだけで…。
OFFSET関数｜参照範囲を自在に動かせる
とりあえず、OFFSET関数の書き方を見てみましょうか。
OFFSET関数
=OFFSET(基準となるセル, 行方向に動かす数, 列方向に動かす数, 範囲の行数（高さ）, 範囲の列数（幅）)
ワケわかんないでしょ？
最後２つの引数は省略可能です。とりあえず前半から見てみましょう。
上の画像で基準となるセルをK2として、そこから下に3行、横に3列動かすとします。
この場合「ここを参照」となっているN5セルは、OFFSET関数で次のように表記できます。
=OFFSET(K2,3, 3)
更に行数（高さ）や列数（幅）を指定してやることで、ひとつのセルではなく範囲の指定をすることも可能です。
=OFFSET(K2,3, 3, 2, 3)
と、ここまで見てもこれの何が便利かよくわからないと思いますので、例を見てみましょう。
OFFSET関数の例①
次のように、月別の経費実績データがあるとします。
毎月実績が出たら入力していくから、11月の実績が出た時点では12月以降は空欄です。んで、翌月になると12月まで埋まって1〜3月が空欄になるというデータです。
これの、実績が出た月までの合計を出してみましょう。
…この例だとB6:M6をSUMで足してやりゃ済むのですが。
ま、あくまでも例ね。例。
ほら、実績が出ていない月は計画値が入っているけど実績だけを合計したいとか、そんな場合を想定してください。
4月から11月までの合計を出したければ、次の数式でOKです。
=SUM(B6:I6)
が、月が変わったらいちいち数式を修正するのが面倒ですよね。
そういう、範囲がころころ変わる場合にOFFSET関数は役に立ちます。
例の場合は、基準となるセルを4月の実績が入ったB6とします。
行方向にも列方向にも動かさなくていい（基準となるB6を範囲に含めたい）ので、動かす行と列の数は0（または空欄）にします。
合計したい範囲は1行分の高さなので、範囲の行数は1。
また、列数は4月から11月の8カ月分ということで、範囲の列数は8。
こうやってOFFSETで表記した範囲をSUM関数で囲ってやれば、4月から11月までの合計が出ます。
=SUM(OFFSET(B6, 0, 0, 1, 8)) または
=SUM(OFFSET(B6, , , 1, 8))
でもこれだと、月が変わったら選択したい範囲の列数の数字を変えてやらなきゃいけないんでしょ？
ということで、こんな工夫をしてみます。
どの月まで合計したいかというのはいちいち指定してやらなきゃいけませんが、4月からの合計月数は数式で表せます。
当月が4より大きい（1〜3月ではない）のであれば、当月から4を引いて1を足す、1〜3月であれば12を足してから4を引いて1を足す。
という具合に、4月からの月数を出しています。3月決算だとこういうときに面倒ですな。
あとはこのセルを範囲の幅として指定してやれば、当月のみの指定で更新されます。
OFFSET関数の例②
お次はこちらの例。為替レートです。これの平均を出してみましょう。
いきなり回答から。
=AVERAGE(OFFSET(B1,1,O1-B1,1,IF(O2>4,(O2-O1),(O2+12-O1))+1))
平均はAVERAGE関数でしたね。ワタシがあんまり使わないヤツ。
平均を出したい範囲を4月から11 月とします。
上の例では基準となるセルを「4」が入ったB1にしています。
計算対象の行はその1行下だから、行方向に動かす数は1。
列方向は、範囲の始まりの月と基準セルの月の引き算で決めています。この例では4月始まりなので0、つまり列方向には動かしません。
計算対象の範囲は1行なので、高さは1。
列範囲は、例①と同じ式で何カ月分かを算出しています。
月を計算したいときは、文字列ではなく数値に
ところで、例①も②も月を表示している列には「4月」「5月」ではなく「4」「5」という表記にしていました。
こうしておかないと月数を計算できなくなるからです。
が、パッと見では「4月」「5月」などの表記の方がわかりやすいと思いますので、数値としての「4」「5」を保持しつつ表記を変えたい場合は、書式設定のユーザー設定から「G/標準”月”」を追加して設定してやってください。
「G/標準」は何が入るかわからんセルに対して、数値とか日付とか適当に判断してくれる設定です。
今回の場合は数値として認識してほしいから「0」でも構いません。
その後に”月”と追加すると、「G/標準で表記させたモノに、表示上は「月」を追加」という表記にしてくれます。
ワタシは月は何がなんでも数値にしておきたい派なので「G/標準”月までの累計”」とかこの表示形式の弄りは多用しています。
…この表示形式のユーザー設定の追加はweb版では使えないので、Microsoftのばーかばーかと思っています。
おわりに
- 計算したい範囲が変動するときにはOFFSET関数で範囲を指定
- OFFSET関数は別の関数を組み合わせてなんぼ
ワタシは大抵のことはINDEX+XMATCH関数を使って力技で解決してしまうのですが、このように範囲が変わるときにはどうにもならなくてOFFSET関数のお世話になります。
OFFSET関数そのものはセルや範囲を指定するだけの関数なので、SUMだのAVERAGEだの別の関数を組み合わせてこそ真価を発揮します。
OFFSETを知らないと困る場面もまあまああるので、ぜひいろいろ試して慣れてください（と苦手な人に言われても、ねえ）。
