【Excel】セルの中の”$”ってなに？　相対参照・絶対参照・複合参照

前回はINDIRECT関数の便利な使い方を紹介しました。今回は同じ表を使い、その続きの処理として、Excelの”参照機能”について紹介します。Excel中の”$”マークに見覚えがあるけれど、どういう意味なのか分からない人は、ぜひご一読ください。

相対参照と絶対参照
複合参照

相対参照と絶対参照

相対参照と絶対参照とは何か

まずは、相対参照と絶対参照について説明します。すでによく知っている人は、次の”複合参照”の項目からお読みください。相対参照と絶対参照は一緒に説明したほうが理解しやすいと思います。それぞれの意味は以下の通りです。

相対参照：相対的な位置を参照すること。”A1″のように、参照セルに”$”記号がない。
絶対参照：絶対的な位置を参照すること。”$A$1″のように、参照セルの列番号と行番号の前に”$”記号がある。

これだけだと難しいと思うので、より詳細に説明します。皆さんが普段意識せずに行っているセルの作業はすべて、”相対参照”だと思ってください。その上で、絶対参照とは何かをまず考えてみましょう。

絶対参照では“$”記号が出てきます。この記号を“碇”マークだと捉えてください。碇というのは、船が寄港している時に船体が動かないように固定するものですね。絶対参照の場合も、列と行を”$”マークで固定するのです。

相対参照の例

セルの行と列を碇で固定するとどうなるのか、以下の表を使って見てみましょう。

この表では、バナナ・イチゴ・チョコレートという商品名と、それぞれの商品の原価が掲載されています。またグレーの部分で目標粗利率35%が掲げられています。これを基にそれぞれの目標売価を計算するとします。バナナの目標売価は以下の計算となります。

=D4 / (1-F2)

次に同じ計算をイチゴとチョコレートに対しても行います。この時、オートフィルを使う人が多いかと思います。オートフィルが何かわからない人は、過去に紹介していますので、こちらをご参照ください。

さて、オートフィルをそのまま実行すると、以下のような結果が出てきます。これは果たして正しいでしょうか？イチゴもチョコレートも原価と売価が同じ数字になっているので、なんだか怪しいですね。

試しにイチゴの目標売価”300″が表示されているC5セルの数式を見てみると、以下の通り目標粗利率の35%を参照できていませんね。

こうなってしまう原因がまさに、”相対参照“です。上で相対参照の意味を”相対的な位置を参照すること”と説明しました。相対的な位置を参照するということは、C4セルに入力した”=D4 / (1-F2)”という数式を1行下のC5にコピーしたら、数式内の”D4″と”F2″もそれぞれ1行下がり、”D5″と”F3″になるのです。これが相対参照です。

絶対参照の使い方

この時、D列に関しては1行下がっても問題ないのでそのままでよいですが、F列の目標粗利率に関しては固定させたいですね。C4セルに入力した”=D4 / (1-F2)”という数式の、”F2″という参照位置を、絶対的な位置としたい。そこで、“F2″を”$”という船の碇で固定してあげることで、位置関係が絶対的なものとなります。

実際に、”F2″を”$F$2″とした状態で、目標売価を再度オートフィルで投入してみましょう。すると、以下のようになります。

それらしい数字になりましたね。試しに、もう一度イチゴの目標売価の数式を確認してみると、以下の通り目標粗利率が正しく参照されています。

ここまでで、相対参照と絶対参照の違いは理解いただけたと思います。

絶対参照のショートカット”F4″

絶対参照にはショートカットが設定されています。それはF4キーです。絶対参照させたいセルや範囲を選択した状態でF4を押すと、簡単に絶対参照化することができ、便利です。ショートカットについては過去にもまとめていますので、ぜひご参照ください。

複合参照

次は複合参照です。複合参照を説明するために、前回INDIRECT関数の説明でしたのと同じ表を使用します。

今回使用するデータについて

以下のような、エリアごとの販売実績のシートが存在し、それを集計するシートが別に存在しています。エリアは東京・大阪・愛知・神奈川・福岡です。

集計シートは以下の通りで、売価と粗利率を集計します。

前回の記事で、各エリア名のシートを効率よく参照するために以下のような数式を投入しました。$B$3:$F$6の部分に絶対参照が使われていますね。複合参照についてはまだ使用していません。

=VLOOKUP(集計!B3,INDIRECT(C2&”!$B$3:$F$6″),2,0)

複合参照がないとどうなるか

＊横方向(列方向)の問題

上述の数式ですが、そのままでは他のセルにコピーできません。試しにC5セルの数式をG5セルまでオートフィルでコピーしてみましょう。

このように、東京以外のエリアの売価がエラーとなりました。これはB3が相対参照になっていて、セルを横にコピーしたときに、B3で参照している”バナナ”の位置関係が崩れてしまうからです。例えば大阪のD3セルの数式を確認してみると、以下のようになっています。”バナナ”ではなく、横のセルを参照してしまっていますね。

このケースでの解決策として、絶対参照すればいいのではないかと考える人もいると思います。実際絶対参照を使っても、”バナナ”の売価集計では問題が起きません。上の数式を以下の通り変えて、再度オートフィルを実行してみます。

=VLOOKUP(集計!$B$3,INDIRECT(C2&”!$B$3:$F$6″),2,0)

バナナの売価は無事集計できました。しかし、絶対参照で問題になるのは他の商品の売価集計です。絶対参照してしまっているため、同じ数式をイチゴやチョコレートの行にコピーしても、”バナナ”を参照し続けてしまいます。

*縦方向(行方向)の問題

同じようなことが、縦の方向でも起きます。東京のバナナの売価集計で以下の数式が入った状態で、縦方向にオートフィルすると、どうなるでしょうか。

=VLOOKUP(集計!B3,INDIRECT(C2&”!$B$3:$F$6″),2,0)

上の通り、イチゴとチョコレートでエラーが起きました。数式を確認すると、以下の通り、”東京”を参照させたいのに、相対参照が原因で”100″を参照してしまっているためのエラーです。横方向の例と同様、これを絶対参照で解決しようとすると、東京のC列の集計だけなら問題ないですが、その数式を横方向にコピーしたときに、”大阪”などのほかのエリアが参照できません。