Excelで表を作っているときによくあるのが、1行目に関数を入れて、残りの行も全部同じにするためにドラッグすると…
この図では「1個あたり価格」をVlookUpで商品一覧表から取得しますが、確かにみかんは商品一覧にあるハズなのに、エラーになってしまいます。
D3セルの関数は合っていそうですが、なぜD5セルはエラーになってしまうのでしょうか？原因は、関数の指定範囲を固定していないことにより、範囲がずれてしまうからです。
Contents
関数の指定範囲を固定しないと、別セルにコピーするときに指定範囲もずれる
表を作成するときには、まずこのように一番上の行に関数を入力しますね。ここまでは合っているように見えます。
一番上の行のセルに関数を入力したら、このようにドラッグしていると思います。
しかしそうすると、最初の図のように「みかん」の列でエラーになってしまいます。実は各セルの関数をよく見てみると…
右側の商品一覧表の参照範囲がずれていました。バナナはずれていてもたまたま参照範囲に入っていたのでエラーになりませんでしたが、みかんは参照範囲に含まれておらず、エラーになってしまいました。
相対参照とは
これはエクセルの仕様で相対参照といいます。もともとの関数は以下のようなものでした。D3セルに、このように書かれています。
=VLOOKUP(B3,H3:I7,2,0)
実は、これは本当の意味で「B3セル、H3:I7」セルを参照しているわけではありません。
本当の意味は、
「同じ行の2列左のセル、同じ行の4列右のセル：4行下で5列右のセル」です。
なので、この関数を別のセルに貼り付けると、例えばD4セルに貼り付けると、D4セルから見て「同じ行の2列左のセル、同じ行の4列右のセル：4行下で5列右のセル」を参照することになります。
$マークをつけて絶対参照にし、表がずれないようにする
今回は、「同じ行の4列右のセル：4行下で5列右のセル」の表ではなく、どこから見ても「H3:I7セル（商品一覧）」を参照したいです。
どこから見ても同じところを見る（絶対参照）ためには、アルファベットや数字の前に$マークをつけます。
※アルファベットの前につけると、列が固定されます。数字の前につけると、行が固定されます。今回は行も列もどちらも固定させたいので、両方に$記号をつけます。
先ほどと同じようにドラッグしてみましょう。
すると、今度はエラーになっていません。
関数の中身を見てみると、商品一覧の参照範囲がずれずにコピーされていました。
今回題材にしたVLOOKUPの解説はこちら。一番分かりやすいVLOOKUPの使い方（初心者向け）
スポンサーリンク
相対参照をつかって、指定範囲をわざとずらすのも便利
相対参照は必ずしも悪いことばかりではありません。実はこの時点で、すでに相対参照を使いこなしています。
=VLOOKUP(B3,$H$3:$I$7,2,0)
この関数ですが、B3には$記号をつけませんでした。これにもし$マークをつけていると、このようになってしまいます。
バナナとみかんがどちらも100円になってしまっています。実際に関数を見てみると、バナナの列なのにりんごを参照しています。
これは、B3を絶対参照で固定したことにより、「つねにりんごを参照する」状態になってしまっているからです。
ここはりんごではなく、「自分の2列左」を参照したいところなので、$マークをつけずに相対参照にしています。先ほどのうまくいった例を見てみると、$マークをつけていないのでしっかりとみかんを参照できていますね。
補足：行全体、列全体を参照させることもできる
ちなみに、今回の場合は$マークをつけずとも、以下のように書けばOKです。
=VLOOKUP(B3,H:I,2,0)
H：Iと数字が書いてありませんが、このように書くとH列とI列全体を参照します。こうすると縦方向にずれることはありません。VLOOKUPだろうが、Index＋Matchだろうが、縦方向にはずれません。
今回は以上です。Excelの絶対参照と相対参照を使いこなせるようになると、表の作成がとても簡単になります。$マークをつけたりつけなかったり、いろいろ試してみてください。Excelのスキルがまた1つ上がります。レベル別 仕事に必要なエクセルの能力 事務職を目指すなら、エクセルをできるようになろう！
