- 公開日:
- 更新日:
ExcelのVLOOKUP関数の複数の列番号を可変する(ずらす)方法
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(C10)の値が「3」、COLUMN($B10)の値が「2」なので、引き算して「1」という結果が出ました。
フィルハンドルで他のセルに反映します。
4-2=「2」、5-2=「3」という結果が出ました。
VLOOKUP関数専門の本をご紹介
VLOOKUP関数をより深く勉強したい方はこちらの本でさらに理解を深めてみてはいかがでしょうか?
スピードマスター 1時間でわかる エクセル VLOOKUP関数 [改訂2版]
\Yahoo!プレミアム会員限定 最大50%OFFクーポン/Yahoo!ショッピングで価格を見る