Excelで表を作っているときによくあるのが、1行目に関数を入れて、残りの行も全部同じにするためにドラッグすると…

この図では「1個あたり価格」をVlookUpで商品一覧表から取得しますが、確かにみかんは商品一覧にあるハズなのに、エラーになってしまいます。

 

D3セルの関数は合っていそうですが、なぜD5セルはエラーになってしまうのでしょうか?原因は、関数の指定範囲を固定していないことにより、範囲がずれてしまうからです。

 

 


スポンサーリンク

関数の指定範囲を固定しないと、別セルにコピーするときに指定範囲もずれる

表を作成するときには、まずこのように一番上の行に関数を入力しますね。ここまでは合っているように見えます。

 

一番上の行のセルに関数を入力したら、このようにドラッグしていると思います。

 

しかしそうすると、最初の図のように「みかん」の列でエラーになってしまいます。実は各セルの関数をよく見てみると…

 

 

右側の商品一覧表の参照範囲がずれていました。バナナはずれていてもたまたま参照範囲に入っていたのでエラーになりませんでしたが、みかんは参照範囲に含まれておらず、エラーになってしまいました。

 

 

相対参照とは

これはエクセルの仕様で相対参照といいます。もともとの関数は以下のようなものでした。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列全体を参照します。こうすると縦方向にずれることはありません。

 

今回は以上です。Excelの絶対参照と相対参照を使いこなせるようになると、表の作成がとても簡単になります。$マークをつけたりつけなかったり、いろいろ試してみてください。Excelのスキルがまた1つ上がります。【転職 正社員でも派遣でも】事務職として働くならExcelは絶対覚えておいたほうがいい理由



スポンサーリンク


Facebook いいね!/Twitterフォローお願いします