VLOOKUPで範囲を固定(絶対参照)してコピーでずらさない方法【別シートも】

midSun31midSun31
IT・デジタル

VLOOKUPでよくあるトラブルの一つとして、式を設定したセルをコピーすると参照範囲がずれてしまう、という事象があります。

この記事では、ExcelやスプレッドシートのVLOOKUP関数で範囲を固定(絶対参照)してコピーでずらさない方法について解説していきます。

VLOOKUPを相対参照でコピーするとエラーが発生

例えば以下のような表を想定し、VLOOKUPで在庫を取得します。

在庫数を全て抽出するために、F4セルを縦にコピーすると一番下のセルでエラーが発生します。

商品番号は存在しているはずなのになぜでしょうか。エラーのでたF8セルの数式を確認すると、コピーしたセル数分だけ参照範囲も下にずれてしまっています。

上記のエラーが出るのは、数式のセル指定が相対参照になっていることが原因です。

絶対参照と相対参照の違い

「絶対参照」「相対参照」について簡単に説明します。

例えばB1セルにて「=A1」と入力した場合、Excelは「自身から見て一つ左に存在するセル」と認識しています。したがってB1セルをB2にコピーすると一つ左のセル(=A2)に数値がずれるのです。これを「相対参照」といいます。

対して「$」を付けて「$A$1」と入力すると、「どのセルから見てもA1セル」と認識させることができます。これを「絶対参照」といいます。

範囲を絶対参照にする方法

VLOOKUPの参照範囲を絶対参照にするには、参照範囲のセルに「$」を付けます。

=VLOOKUP(D4,A4:B12,2)  *元の数式
=VLOOKUP(D4,$A$4:$B$12,2) *訂正後

ちなみに使用しているOSがWindowsの場合は「F4」キーを使用している場合は、参照範囲の部分にカーソルを合わせて「F4」キーを押すだけで絶対参照になります。

上記の絶対参照の設定により、式を縦にコピーしても参照範囲が移動せずに、正常な値が検索できます。

補足:行/列のみ絶対参照にする

絶対参照を行、もしくは列のみ指定することもできます。コピーした際に、行はずれてほしくないけど、列は相対参照で自動的にずらしたい場合などに便利です。

  • 「行」のみ絶対参照にする
    =VLOOKUP(D4,A4:B12,2)  *元の数式
    =VLOOKUP(D4,A$4:B$12,2) *訂正後

    縦方向のコピーは選択範囲が固定されて移動しません。横方向のコピーは相対的に選択範囲が移動します。
  • 「列」のみ絶対参照にする
    =VLOOKUP(D4,A4:B12,2)  *元の数式
    =VLOOKUP(D4,$A4:$B12,2) *訂正後

    横方向のコピーは選択範囲が固定されて移動しません。縦方向のコピーは相対的に選択範囲が移動します。

別シートの範囲を絶対参照する方法

参照範囲が別シートに存在しているケースもあります。以下では在庫数を別シート「在庫一覧」シートから取得しています。

この場合も、同様に参照範囲のセルに「$」を付けることで絶対参照にすることができます。シート名は何も加工する必要がないので注意してください。

縦にコピーしても参照範囲が固定されて正しい値が検索されます。

以上、VLOOKUPで範囲を固定(絶対参照)してコピーでずらさない方法でした。ほんの一手間でよくあるトラブルを防ぐことができるのでぜひ、試してみてください。

IT・デジタル
Howpon[ハウポン]

コメント