Office Hack

ExcelのVLOOKUP関数の複数の列番号を可変する(ずらす)方法

  • by kenji
  • Release
  • Update

VLOOKUP関数の3つ目の引数に、「列番号」を指定します。この列番号は$で固定していないにも関わらず、フィルハンドルで他のセルに反映させても列番号の数値は固定のままです。これが非常に厄介なのです。

この記事では、他のセルに関数をコピーしても列番号に変化を加える方法を2つお伝えします。

列番号が可変しないのを再現

列番号の確認

価格テーブルのデータからVLOOKUP関数を用いて、受注シートの「商品名」と「単価」を参照する仕様です。

C11セルの商品コードを検索値とし、価格テーブルの列番号2の商品名を参照しているのでD11セルに「りんご」と表示されました。ここまでは問題ありません。

フィルハンドルで反映

D11セルのフィルハンドルを使って、E11に反映させます。

同じ列番号が抽出

結果、反映させたE11セルも同じ列番号「2」を取得しているので、同じ「りんご」が抽出されました。

数式バーの赤枠を見てみると、列番号は$で固定していないにも関わらず、列番号に変化はありません。VLOOKUPはそのような仕様となっております。

列番号を可変させる方法

この問題を解決するのに2つの方法をご紹介します。VLOOKUP関数をフィルハンドルで他のセルに反映させても列番号が可変になる方法をぜひ覚えておきましょう。

テキストで列番号を用意する

こちらの方法は最も簡単な方法です。

列番号をテキストで用意

表の上に列番号を参照するために、テキストで数値を配置しました。

列番号をセル参照

D11の商品名のVLOOKUP関数の列番号は、先程作ったD9セルを参照します。

列番号のセル参照の結果

【Enter】で確定し、列番号「2」の正しい値が取得できました。

フィルハンドルで反映

フィルハンドルでE11セルに反映させてみましょう。

列番号の正しい反映

無事、列番号「3」の「単価」の正しい値が取得できました。赤枠の数式バーを見ると、先程作ったE9セルの数値を拾って来ております。

COLUMN関数で列番号を用意する

COLUMN関数を利用すれば、現在のセルが左から何列目に存在しているのか、数値で拾うことが可能です。

列番号のクリア

列番号をテキストで打たず、関数を使って列番号を指定する方法もあります。ここではCOLUMN関数を利用します。

一旦テキストで用意した列番号をクリアします。

COLUMN関数の利用

現在の列が受注日からどのくらい離れているか、COLUMN関数同士を引き算します。受注日は固定にするので$を付与します。

COLUMN関数の結果

COLUMN(C10)の値が「3」、COLUMN($B10)の値が「2」なので、引き算して「1」という結果が出ました。

フィルハンドルで反映

フィルハンドルで他のセルに反映します。

COLUMN関数のフィルハンドルの結果

4-2=「2」、5-2=「3」という結果が出ました。

VLOOKUP関数専門の本をご紹介

VLOOKUP関数をより深く勉強したい方はこちらの本でさらに理解を深めてみてはいかがでしょうか?実際の操作画面を示しながら、徹底的にわかりやすく解説したフルカラー入門書です。

VLOOKUP関数のツボとコツがゼッタイにわかる本

立山秀利(著)
206ページ

その他、様々なVLOOKUP関数の使い方

XLOOKUP関数も合わせてチェック!

VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください