「$」の意味は?【Excel】セルの参照(相対参照、絶対参照、複合参照)について

PC

今回は、セルの参照についてです。セルの参照には、相対参照、絶対参照、複合参照があります。それぞれの参照について理解するとよりExcelを効率的に使用することができるようになります。

セルの参照(Cell References)

セルの参照とは、セルの内容を参照することです。言葉では難しいと思うので、例を挙げて見ていきたいと思います。

表1のような場合を考えます。

ABCD
1商品名税抜価格税率(×)税込価格
2りんご1001.1
表1
tayuyu
tayuyu

食品は、軽減税率8%の対象なんですけど…とかは言わないでね。

D2セルにりんごの税込価格を計算します。このような場合、税抜価格が入力されているB2セルと税率が入力されているC2を利用して、次のような数式をD2セルに入力します。

=B2*C2

もちろん、D2セルに「=100*1.1」と数値を入力しても問題はないですが、B2セルとC2セルに欲しい情報があるので、Excelでは、これを利用(参照)するのが通常です。

=B2*C2」の「B2」はB2セルの内容(値)を参照してねということになります。C2も同様です。

セルの参照を使う主なメリット

セルの参照を使うことで、あるセル(参照されるセル)の値が変更になっても、そのセルを参照しているセルの数式は変更せずに済みます。

例えば、表1の例において、りんごが不作となり100円から150円に高騰してしまったとしましょう。このとき、B2セルの値であるりんごの税抜価格「100」を「150」に変更すれば、自動的にD2セルの税込価格も変わります。

もし、D2セルに「=100*1.1」と直接数値を入力していたら、B2セルの税抜価格の内容とD2セルの税込価格の内容の両方を変更しなければなりません。そうなると面倒ですよね。

相対参照(Relative Reference)

セルの参照方法のひとつに相対参照というものがあります。相対参照は、セルを相対的に参照することですが、これも言葉では難しいので例を挙げて見たいと思います。

図1のような場合を考えます。図1では、D2セルに「=B2*C2」が入力されています。(図1赤枠)

図1:相対参照(例)

この状態で、D2セルを選択し、図1の青丸部分をD4セルまでドラッグします。(※この機能をオートフィルと言います。基準のセルや範囲の内容を元に、自動的に数式や値などを作成してくれる機能です。)

今回は、オートフィルを使いましたが、D2セルをコピーして、D3セル、D4セルに貼り付けをしても、同様に表2の状態になります。

すると、表2のように、D3セルとD4セルに税込価格が表示されます。

ABCDD列のセルの内容
1商品名税抜価格税率(×)税込価格税込価格
2りんご1001.1110=B2*C2
3いちご5001.1550=B3*C3
4ぶどう10001.11100=B4*C4
表2:相対参照

D3セルとD4セルの内容を見てみると、D3セルは「=B3*C3」、D4セルは「=B4*C4」となっていることがわかります。D3セルとD4セルは、オートフィルによってD2セルを基準に作られたので、ここでは、D2セルを基準として考えてみましょう。

D3セルは、基準となったD2セルの1つ下のセルです。そして、D3セルの内容である「=B3*C3」のB3とC3も、基準となったD2セル「=B2*C2」のB2とC2の1つ下のセルです。

D2=B2×C2
↓ひとつ下(相対参照)↓ひとつ下(相対参照)↓ひとつ下(相対参照)
D3=B3×C3
表3

このように、D3セル、D4セルの内容は、D2セルの内容を基準として相対的に参照(作成)されていることがわかります。

tayuyu
tayuyu

普段、何気なく利用しているやり方が相対参照だったんだね。どこのセルを基準としても相対的に数式が作成されるよ。

絶対参照(Absolute Reference)

次は、絶対参照です。絶対参照は、セルを絶対的に参照することですが、こちらも言葉ではわかりにくいので、例を挙げて見てみましょう。

図1の例で、C列の税率(×)は、どの商品も「1.1」という同じ値でした。同じ値を商品毎に毎回入力することは面倒なので、図2のように税率をE2セルにまとめてみることにしましょう。そして、C2セルには、「=B2*E2」と入力しました。

図2:絶対参照(例)

この状態で、C2セルを選択し、図2の青丸部分をD4セルまでドラッグします。すると、表4のように、
C3セルとC4セルに税込価格が表示されます。

ABCC列のセルの内容DE
1商品名税抜価格税込価格税込価格税率(×)
2りんご100110=B2*E21.1
3いちご5000=B3*E3
4ぶどう10000=B4*E4
表4:相対参照のまま

税込価格のC3セルとC4セルを見てみると「0」となっています。C3セルとC4セルの内容を見てみると、C3セルが「=B3*E3」、C4セルが「=B4*E4」です。C2セルを基準セルとしたため、C3セルやC4セルの内容が相対的に変わってしまっています。

tayuyu
tayuyu

ん~、相対参照で書かいてるからE2が変っちゃうのかあ。

本来であれば、C3セルが「=B3*E2」、C4セルが「=B4*E2」のように、「E2」の部分を「E2」のまま固定したいです。

このように、あるセルを固定したいときに、絶対参照の登場です。図3のように、C2セルの数式を変更(図3赤枠)、選択し、図3の青丸部分をD4セルまでドラッグします。(C2セルをコピーして、C3セル、C4セルに貼り付けても同様となります。)

図3:絶対参照に変更

図2と違うところは、C2セルの内容が次のようになっています。

=B2*$E$2

E2」が「$E$2」になっています。また、C3セル、C4セルの内容を見てみると、表4のようにC3セルが「=B3*$E$2」、C4セルが「=B3*$E$2」となっています。

ABCC列のセルの内容DE
1商品名税抜価格税込価格税込価格税率(×)
2りんご100110=B2*$E$21.1
3いちご500500=B3*$E$2
4ぶどう10001100=B4*$E$2
表4:絶対参照
C2=B2×$E$2
↓ひとつ下(相対参照)↓ひとつ下(相対参照)固定(絶対参照)
C3=B3×$E$2
表5

絶対参照にしたい場合は、「$A$1」のようにセルの番地の英字(列)と数字(行)の前に「$」を付ける。

複合参照(Mixed Reference)

最後に、複合参照について見ていきたいと思います。

絶対参照のイメージは、行と列どちらも固定するというものでした。実は、行または列のみを固定する複合参照という参照もあります。今回も例を見ていきましょう。

tayuyu
tayuyu

複合参照は、ちょっと複雑になるけど、基本の考え方は相対参照や絶対参照と同じだよ。

図4のような場合を考えます。図4には、各商品の定価、各月の値引き率、そして、月毎の各商品の販売価格の表があります。

図4

E2セルの内容は、りんごの定価×(1-値引き率)、すなわち、「=B3*(1-A9)」です。

この状態でE2セルを選択し、図4の青丸部分を青印の方向であるG3セルまでドラッグします。F2セル、G2セルの内容はどうなるでしょうか。E2セルの内容が相対参照で書かれているので、F2セルは「=C3*(1-B9)」、G2セルは「=D3*(1-C9)」のように意図しない部分を参照してしまっています。

また、図4の青丸部分を緑矢印の方向であるE5セルまでドラックします。E4セル、E5セルはどうなるでしょうか。こちらも相対参照で書かれているので、E4セルは「=B4*(1-A10)」、E5セルは「=B5*(1-A11)」のようにこちらも意図しない部分を参照してしまっています。

そこで、定価が入力されているB列と値引き率が入力されている9行のみ固定すると意図した数式となります。

次のような数式をE2セルに入力します。

=$B3*(1-A$9)

$B3」は、B列のみ固定というイメージになります。また、「A$9」は9行のみ固定というイメージになります。この状態で、図5の青丸部分を青矢印や緑矢印の方へドラッグすると、B列や9行の部分のみ固定され、その以外の部分は相対的に参照されます。

図5:複合参照

この数式を用いれば、どのような形でオートフィルやコピー&ペーストを使用しても、E3セルからG5セルの内容は、図6のようになります。図6のように、相対参照と絶対参照が混合(複合)したような参照をしているので、複合参照と呼ばれていることがご理解いただけたと思います。

図6:各セルの数式の表示
  • 行を固定したい場合は、「A$1」のようにセルの番地の数字(行)の前に「$」を付ける
  • 列を固定したい場合は、「$A1」のようにセルの番地の英字(列)の前に「$」を付ける

補足

ショートカットキー

tayuyu
tayuyu

数式の入力中に「F4」キーを押すと、選択中のセル番地について、相対参照、絶対参照、複合参照が切り替わるよ。

例えば、A1セルの場合は、次のような感じです。

「A1」→「$A$1」→「A$1」→「$A1」→「A1」→ …

コメント

タイトルとURLをコピーしました