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