昨日紹介しましたＥｘｃｅｌ関数のＯＦＦＳＥＴ関数はなかなか理解しにくい関数だと思います。
しかし、これを理解出来たらかなり便利な関数なので実例を紹介しますね・・・
下の図の場合・・・
- セルＡ1からセルＣ4迄住所録が創ってあります。・・・検索の範囲A2:C4に成ります。
- セルＦ2に郵便番号を入力しますと
- セルＦ3に氏名が表示される様にするには普通であれば・・・=VLOOKUP(F2,A2:C4,2,FALSE)
- セルＦ4に住所が表示されます。普通であれば・・・=VLOOKUP(F2,A2:C4,3,FALSE)これで良いのです。
ただ、住所録と云うものは段々と増えてきます・・・そんな時、検索範囲を直さなくては駄目ですよね
この手間を掛けずに済む方法があるんです・・・
- =OFFSET(基準,行位置,列位置,高さ,幅)
※ 基準とするセルから、指定した行数、列数だけ移動した位置にあるセルを先頭にして
指定した高さ(行数）、幅（列数）を持つセルの範囲を参照できるようにします。 - =COUNTA(セル範囲)
※ 指定した範囲で数値・文字などが入力されてるセルを数える
この二つの関数をＶＬＯＯＫＵＰ関数に組み込む訳です。
先ず、下の図をご覧ください・・・
- 検索範囲セルＡ2からセルＣ4です。これをＯＦＦＳＥＴ関数に当てはめます。・・・下図の2を参照
- ＯＦＦＳＥＴ関数の基準に成るのはセルＡ2・・・=OFFSET(A2,行,列,高さ,幅)
- この場合基準に成るセルＡ2は変わりませんから行・列は共に「0」・・・=OFFSET(A2,0,0,高さ,幅)
- 次に高さですが現在2行目から4行目の3行です・・・=OFFSET(A2,0,0,3,幅)
- 最後に幅ですがＡ・Ｂ・Ｃの3列です・・・=OFFSET(A2,0,0,3,3)
ここで住所が増えたら如何するのかですよね・・・ - ＯＦＦＳＥＴ関数の高さ換えれば良い訳です。ＣＯＵＮＴＡ関数を使います。・・・下図の3を参照
- ＣＯＵＮＴＡ関数のセル範囲にはＡ列全てです・・・Ａ：Ａ
- ＣＯＵＮＴＡ関数はＡ列全て入力されてるセルをカウントしてしまいますのでセルＡ1の項目を引いておきます
・・・=COUNTA(A:A)-1
これで高さの部分の数式が出来ましたから、ＯＦＦＳＥＴ関数の高さの箇所にはめ込みます - 下の図の4・・・=OFFSET(A2,0,0,COUNTA(A:A)-1,3)
これで、ＶＬＯＯＫＵＰ関数の範囲の部分が可変に成りましたね・・・
これを範囲の部分にはめ込みます。 - 下の図の1にはめ込んだら5の数式・・・=VLOOKUP(F2,OFFSET(A2,0,0,COUNTA(A:A-1,3)),2,FALSE)
これで、住所が増えて行っても数式の範囲を変更しなくても大丈夫に成った訳です。
ご理解頂けましたでしょうか？
この関数は、覚えてしまうと非常に便利な関数だと思いますよ。
何とか理解して下さいね！
