Aさん VLOOKUP関数の検索を行なう表にデータを追加すると、いちいち数式中の「範囲」を直すのが面倒ですが、何か良い方法はないでしょうか？
森田 VLOOKUP関数でよくある困りごとですね。
制約がなければ、引数「範囲」を列で指定してしまうか、検索したい表をテーブル化した上で「範囲」に指定することがおすすめです。
今回は、制約があっても大丈夫なように、「範囲」の引数にOFFSET関数とCOUNTA関数を組み合わせるテクニックをお教えします！
3つの関数を組み合わせるので少々難しいかもですが、がんばって詳細をわかりやすく解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職の人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
VLOOKUP関数の参照先の表へデータ追加を行なうと参照範囲にできていない可能性が・・・
けっこうあるあるですが、VLOOKUP関数で参照している表にデータを追加すると、その追加したデータで検索しようとすると、範囲外で「エラー」なんてことがあります。
たとえば、以下のようなイメージですね。
VLOOKUP関数が入っているセルをダブルクリック（もしくはF2キー）すると、追加したデータのみが引数「範囲」の対象外になっていることがわかりやすいですね。
こうなると、VLOOKUP関数の引数「範囲」に指定しているセル範囲を再度手作業で設定し直さければならないので面倒です。
この面倒な事象を起こさないために、どう対応すべきかを順番に解説していきますね。
【原則】VLOOKUP関数の引数「範囲」は列で指定！
まず、特に制約がなければVLOOKUP関数の引数「範囲」は列で指定しましょう。
そうすれば、参照したい表に対してデータを追加・削除しても何ら影響ありません。
※もちろん、データ追加・削除を「列」で行わない想定です。
これで事足りれば、本記事のテクニックは不要です。
しかし、次の2つの条件にどちらも該当するなら、本記事のテクニックは非常におすすめです。
【条件その1】引数「範囲」で参照したい表の上に引数「検索値」と同じ値があって列指定できない
条件その1はイメージ湧きにくいと思いますが、つまりは以下のような状態の場合です。
たまたま、VLOOKUP関数の引数「範囲」の1番左の列に引数「検索値」と同じ値が、参照したい表の上にある場合（レアケースですが）、VLOOKUP関数の戻り値（返り値）がおかしくなる可能性があるわけです。
【条件その2】共有ブックが対象
条件その2は、ブック名の後ろに[共有]と記載されている場合です。
仮に、共有ブックでなければ、引数「範囲」で指定したい表をテーブル化するテクニックの方がお手軽でおすすめです。
【初心者向け】データ追加してもVLOOKUP関数の引数「範囲」を自動で拡張させるテクニック①【テーブル機能】 | Excelを制する者は人生を制す ～No Excel No Life～
引数「範囲」にOFFSET関数＋COUNTA関数を組み合わせる！
VLOOKUP関数の引数「範囲」をエクセル側で自動的にサイズ変更してくれるようにするために、OFFSET関数とCOUNTA関数を活用します。
OFFSET関数とは？
このOFFSET関数はVLOOKUP関数と同じく検索/行列関数の一種で、任意のセルから、指定した行数・列数分スライドし、行き着いた先のセル範囲を取得してくれる関数です。
OFFSET(参照,行数,列数,[高さ],[幅])
このOFFSET関数は引数をすべて指定すると、セル範囲を戻り値にするため、OFFSET関数単独で使用するとエラーになってしまいます。
（引数「高さ」と引数「幅」を省略すればエラーになりませんが、本記事の対象外です。詳細を知りたい方はこの記事をご覧下さい→こちら）
そこで、イメージが湧くように、簡単なSUM関数の計算範囲として、このOFFSET関数を組み合わせて使ってみます。
たとえば、OFFSET関数の5つの引数をそれぞれ次のように設定し、SUM関数の中に入れてみました。
- 参照：A1セル
- 行数：2
- 列数：1
- 高さ：4
- 幅：1
上記のB9セルのSUM関数の中にOFFSET関数が入りましたね。では、具体的にどうなっているか簡単に解説しますね。
まず、OFFSET関数の動きは以下のとおりです。
OFFSET関数の引数「参照」は起点となるセルを指定しますが、今回はA1セルでした。
そして、引数「行数」が“2”なので、A1セルの2行下のA3セルへスライドします。
次に、引数「列数」が”1”なので、A3セルの1列右のB3セルへスライドします。
最後に、引数「高さ」が”4”、引数「幅」が”1”なので、B3セルを起点とした4行×1列のセル範囲「B3:B6」がOFFSET関数の戻り値（返り値）になります。
慣れるまで頭使いますが、上記の順番で考えれば大丈夫ですよ。
OFFSET関数の戻り値（返り値）となったセル範囲の確認方法
ちなみに、OFFSET関数の戻り値（返り値）となったセル範囲の確認方法は、以下の手順で確認できます。
- OFFSET関数の入ったセルを選択
- リボン「数式タブ」を表示
- 「数式の検証」をクリック
- 「数式の検証」ダイアログの「検証」ボタンをクリック
「数式の検証」は地味に便利なので、この機会に覚えましょうね。
もっと詳細を知りたい方はこの記事もどうぞ→こちら
COUNTA関数とは？
次に、もうひとつの組み合わせる関数のCOUNTA関数は統計関数の一種で、データの入ったセルの個数をカウントする関数です。
COUNTA(値1,値2,…)
こちらもイメージが湧くように、COUNTA関数を単独で使ってみましょう。
たとえば、次の表のA2~B6のセル範囲をCOUNTA関数の引数として指定してみました。
A2~B6のセル範囲は10セルともに文字列か数値のデータが入っているので、すべてカウントされ、COUNTA関数を挿入したB9セルへ戻り値（返り値）となる”10”が表示されていますね。
このように、COUNTA関数で指定したセル範囲中のデータが入ったセルの個数を集計したい場合に役立ちます。
組み合わるとはどういうことか？
組み合わせるとは、COUNTA関数をOFFSET関数の引数「高さ」へ入れ、そのOFFSET関数（＋COUNTA関数）をVLOOKUP関数の引数「範囲」へ入れるわけです。
こうして3つの関数を組み合わせて使うことで、VLOOKUP関数の引数「範囲」の参照先の表へデータ追加を行っても、関数側で自動的に参照範囲を調整してくれます。
サンプルファイルで練習しよう！
では、実際に今回のVLOOKUP関数とOFFSET関数＋COUNTA関数の組み合わせテクニックを使ってみましょう！
サンプルの条件
今回の題材は次のとおりです。
「商品」というシートにくだものの名称・単価が一覧表になっています。
この「単価」を別シート「台帳」のC列の2行目以降に転記したいとします。
この「台帳」シートのB列には「くだもの」の名称がすでに入っているので、これをVLOOKUP関数のキーワードにしていきましょう。
実際に操作しよう！
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_VLOOKUP関数_範囲②
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「台帳」シートを選択
- C2セルへ「=VLOOKUP($B2,OFFSET(商品!$A$2,0,0,COUNTA(商品!$A$2:$A$21),2),2,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - C2セルをコピー
- C3～C6へ貼り付け（ペースト）
上記手順を行った結果は「台帳 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。
「商品」シートの7行目に新規データを追加して、そのデータもVLOOKUP関数で取得できるかも確認してみましょう。（例は以下のとおり）
ポイントの解説
最後に今回のテクニックの4つのポイントについて解説していきましょう。
【POINT1】主キーの設定
このテクニックに限らずVLOOKUP関数を使う際のマストなポイントです。
こちらは検索を行う対象の表に「主キー」が入ったものを準備しておきましょう。
この「主キー」とは、データを固有のものと識別するための値であり、かつ一意（他データと重複していない）である必要があります。
今回の例でいえば、”出席番号”が該当しますね。
こういった「主キー」があると、VLOOKUP関数で意図しない戻り値（返り値）になることを防ぐことができます。
【POINT2】OFFSET関数の引数「参照」「行数」「列数」はセル範囲の起点を最初から設定
今回のVLOOKUP関数の引数「範囲」にしたいのは、「商品」シートのA2:B6のセル範囲です。
このセル範囲の起点となるA2セルをOFFSET関数でも起点とすべく、引数「参照」にA2セルを指定します。
この時点でセル範囲の起点にしたいセルを指定できているので、OFFSET関数の引数「行数」「列数」は”0”とし、特に移動させる必要がありません。
【POINT3】OFFSET関数の引数「高さ」はCOUNTA関数と組み合わせてデータの個数に合わせてセル範囲を自動的に拡張するように設定
ここが、今回のテクニックの肝です。
OFFSET関数の引数「高さ」にCOUNTA関数を入れ、データの個数に合わせてセル範囲を自動的に拡大・縮小できるようにしておきます。
ちなみに、このときのCOUNTA関数で指定するセル範囲はややゆとりのあるセル範囲を指定すればOKです。
今回は「COUNTA(商品!$A$2:$A$21)」のように20セル分の高さになるようにしています。
当然ですが、このCOUNTA関数の参照範囲中に、まったく関係ないデータを入れてしまうと、データの個数がその分増えてしまうため、VLOOKUP関数の結果が変わる可能性も出てくるのでご注意ください。
なお、今回は「商品」シートで参照したいのはA・B列の2列分のため、OFFSET関数の引数「幅」は”2”という固定値（定数）を入れていますが、こちらも拡張性を持たせたいなら、ここにもCOUNTA関数を組んでしまうこともできますよ。
ただし、その場合は、VLOOKUP関数の引数「列番号」も追加データに対応できるようにしておく必要があるため、別記事の「列番号」のテクニックを参考に工夫してみてください。
【POINT4】絶対参照・相対参照の設定
これは、数式を扱う上で基礎中の基礎ですが、コピペを一括で行うために必要です。
今回セル参照しているのはVLOOKUP関数・OFFSET関数・COUNTA関数を合わせて3つの引数ですが、それぞれ以下の意図があって絶対参照・相対参照を設定しています。
すべて「検索」シートのC2セルをもとに説明していきますね。
VLOOKUP関数－引数「検索値」
今回は「$B2」と列のみ絶対参照にしていますが、行部分が相対参照であれば列は絶対参照でなくともOKです。
これは、C2セルのVLOOKUP関数を下方向にしかコピペしていかないため、行は固定しないようにするわけです。
OFFSET関数－引数「参照」
今回は「商品!$A$2」と行列ともに絶対参照にしています。
これは、C3セル以下にコピペ予定のVLOOKUP関数も含めて、すべてのVLOOKUP関数が参照したい表は一緒になるため、その表の起点となるA2セルは行列どちらも固定にしています。
COUNTA関数－引数「数値1」
今回は「商品!$A$2:$A$21」と行列ともに絶対参照にしています。
これは、上記のOFFSET関数の引数「参照」と同じ理由ですね。すべてのVLOOKUP関数が参照したい表は一緒になるため、その表の高さを決めるCOUNTA関数の引数「数値1」のセル範囲は行列どちらも固定にしています。
絶対参照・相対参照のまとめ
上記の説明は実際にワークシート上にさらに複数行のVLOOKUP関数が増えるとわかりやすいです。
さいごに
今回のテクニックのOFFSET関数とCOUNTA関数の組合せは、実はVLOOKUP関数以外にも、セル範囲を指定する引数を持つ関数すべてで活躍できます。
OFFSET関数がけっこう独特なので、慣れるまで時間がかかるかもしれませんが、一度理解してしまえば、上記のとおり別の関数でも役立ちますよ。
なお、その他のVLOOKUP関数関連の役立つテクニックについて、私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 OFFSET関数は私も最初は「なんだ、この関数は？」と悩まされたものです(笑)
しかし、意外と他の関数とも組み合わせて使えるので、思わぬところで活躍してくれる「いぶし銀」な存在です。
また、VBAを学んでびっくりしたのが、OFFSET関数と同じ動きはプログラム上使う頻度が高いです。（ループとか）OFFSET関数で慣れておくと、いろいろ良いことあるのでおすすめです！
