Office TANAKA - ワークシート関数[OFFSET 関数]

OFFSET関数に関して、詳しい説明や具体的な使い方などを動画で解説しています。ぜひ、ご覧ください。Youtubeでは、ほかにもたくさんの動画を公開しています。チャンネル登録をお忘れなく！
Office TANAKAチャンネル

書式：OFFSET(基準,行数,列数,高さ,幅)
機能：基準セルを指定した数だけシフトしたセル範囲を返します
解説：セル範囲を可変にするときに使います

OFFSET関数の仕組み

OFFSET関数は理解しづらい関数のひとつです。しかし、その仕組みを理解できれば、これほど役に立つ関数はありません。まず、OFFSET関数は何をどうする関数なのかを理解しましょう。

OFFSET関数の構文は次の通りです。

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

OFFSET関数に指定する引数は、次の3種類に分類できます。

1.基準となるセルを指定する引数→[基準]

2.基準セルを動かす位置を指定する引数→[行数],[列数]

3.基準セルの大きさを指定する引数→[高さ],[幅]

引数[基準]は、どこか基準にするセルのアドレスを指定します。まあ、これは難しくありませんね。

最初は、基準セルを動かす位置を指定する引数[行数][列数]の使い方から解説します。下の図をご覧ください。

セルA1:C5に1から15の数値を入力してあります。OFFSET関数の[基準]はセルA1です。

セルE2に入力したのは、=OFFSET(A1,2,1,1,1) です。行数=2、列数=1というのは、基準セルから下に2行、右に1列動いた位置にあるセルに、基準セルを移動せよという意味です。図ではセルB3がそこです。

基準セルの大きさを指定する引数[高さ]と引数[幅]が、どちらも 1 を指定しているのは、基準セルの大きさを 1行×1列にせよという意味です。これは、次で詳しく解説します。

ここまでは、いいですか？続いて、基準セルの大きさを指定する引数[高さ]と[幅]を解説します。まず、1つのセルとは、1行×1列のセル範囲だという認識を持ってください。上の図のセル範囲A1:C5は、5行×3列のセル範囲ですね。このように、任意の大きさのセル範囲は、[高さ]行×[幅]列で表すことができます。引数[高さ]と引数[幅]は、この大きさを指定する引数なのです。

上図では、OFFSET(A1,2,1,3,2) というOFFSET関数を入力しました。[基準][行数][列数]は変わりません。

OFFSET(A1,2,1,1,1)は、基準のセルA1から下に2行、右に1列シフトしたセルB3を返しました。今度はそのセルB3の大きさを、高さ 3 行、幅 2 列の大きさにせよという指示です。図では水色のセル範囲が該当します。最初の例とは違い、今度はOFFSET関数が複数のセルを返すので、SUM関数で合計を求めてみました。

これがOFFSET関数の働きです。ただし、一般的な使い方では、基準セルを動かす位置を指定する引数と基準セルの大きさを指定する引数を同時に指定することは希です。もちろん指定してもかまいませんが複雑になります。そうして一気に参照範囲を変化させるのは、かなり高度な使い方と言えますね。普通は、基準セルを動かすか、基準セルの大きさを変化させるかのどちらかです。あえて言えば、後者の活躍するケースが多いでしょうか。

大きさが変化するリストから表引きする

では、このOFFSET関数をどう使うか、簡単な例を紹介しましょう。下のようなリストがあったとします。

表引きの元になるリストはセル範囲A1:C5です。セルF2に番号を入力すると、セルF3に該当する名前を表示します。セルF3には=VLOOKUP(F2,A2:C5,2)という式が入力されています。

特に難しくない使い方です。雑誌などでもよく、こうした使い方が紹介されていますね。しかし、ここで問題が。このリスト、どんどんデータが追加されていくんです。今は=VLOOKUP(F2,A2:C5,2)で良いのですが、明日は=VLOOKUP(F2,A2:C7,2)になるかもしれません。

このように可変リストのアドレスを指定するときこそ、OFFSET関数の出番なのです。

最終的な式を考えてみましょう。VLOOKUP関数で指定するA2:C5は、現在入力されているリストの大きさによって異なります。

つまり、

=VLOOKUP(F2,現在のリストの大きさ,2)
                    ↓
=VLOOKUP(F2,現在の高さ×幅,2)
                    ↓
=VLOOKUP(F2,入力されているデータの行数×幅,2)

リストの幅は3で固定ですね。したがって、上の考え方にOFFSET関数を追加すると、

=VLOOKUP(F2,OFFSET(A2,0,0,入力されているデータの行数,3),2)
                                 ↓
=VLOOKUP(F2,OFFSET(A2,0,0,A列のデータの個数-1,3),2)
                                 ↓
=VLOOKUP(F2,OFFSET(A2,0,0,COUNTA(A:A)-1,3),2)

OFFSET関数の基準はセルA2です。この基準セルは移動させませんので、[行数]と[列数]はともに 0 です。

さて、基準セルA2を、セル範囲A2:C5に広げるには、OFFSET関数の引数[高さ]に 4、[幅]に 3 を指定します。この[高さ]に現在入力されているデータの個数を指定できれば完成です。データの個数をカウントするには、COUNTA関数を使います。ポイントはCOUNTA関数に指定する引数です。どの範囲をカウントするかといえば、A列全体です。そこでCOUNTA(A:A)とします。ただし、このままでは1行目のタイトルもカウントしてしまいますので、-1します。

落ち着いて、1ステップずつ理解してください(^_^)