機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > この関数はこう使え

OFFSET関数



書式 :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ステップずつ理解してください(^_^)



このエントリーをはてなブックマークに追加