絶対参照・相対参照・複合参照の違いと使い方【Excel】

アイキャッチ_参照

計算式をコピーした時にエラーが表示されたりおかしな数字が出てくる時があります。
それはセルの参照がずれてしまったのかもしれません。
基本の「相対参照」、セルを固定する「絶対参照」がわかるとエラーのないコピーができます。
行または列のみを固定する「複合参照」も解説していきます。

エラーのないコピーができる!?

目次

相対参照

相対参照とは、基準となるセルから見た相対的な位置による参照方法のことです。

なんだか意味がわかりません・・・

例を見てみます。

D列に合計を求めます。
価格×販売数で合計を求めたいので、【D2】の計算式は「=B2*C2」になります。

単純な掛け算

【D2】の計算式を下までコピーします。

コピーはフィルハンドルをダブルクリックするオートフィルが速いね!

オートフィル

「=B2*C2」をコピーしたのに、貼り付けたセルは「=B2*C2」ではありません。
計算式で参照しているセルがズレて変わっています。

オートフィル後

Excelでは、特に指定がない場合は「相対参照」、
つまり、基準となるセルから見た位置関係をコピーしていきます。

ここで言う位置関係とは、
(2つ左のセル)×(1つ左のセル)

どこへいっても(2つ左のセル)×(1つ左のセル)がコピーされています。
この位置関係を保持した参照が「相対参照」です。

計算式の場合は、それぞれの行や列で計算することが多いので
特に指定がなければ「相対参照」でコピーされるようになっています。

絶対参照が必要になるシーンとは

絶対参照とは、参照したセル位置を固定してコピーしてもずれないようにする参照方法のことです。

セルをロックしてしまうんだね!

まずは、通常通り計算してコピーした例を見てみます。

C列にそれぞれの商品価格に対する手数料を求めます。
手数料率は【B1】を参照します。
価格×手数料率で求めたいので、【C4】の計算式は「=B4*B1」になります。

手数料の計算

【C4】の計算式を下までコピーします。

計算式をコピー

あり得ない数字と、エラーコードが表示されました。

エラーが表示される

めちゃくちゃになってる!

【C4】の計算式は「=B4*B1」、セルの位置関係は
(1つ左のセル)×(左列の3つ上のセル)です。

相対参照でコピーした結果

コピーした【C7】を見てみると「=B7*B4」、
位置関係は、(1つ左のセル)×(左列の3つ上のセル)で同じです。

参照がずれた結果

通常通り計算式をコピーすると「相対参照」になるので、
ずらしたくない手数料率【B1】もずれていってしまい、結果おかしくなりました。

このように、コピーする際にずらしたくないセルがある場合は
セルを固定する「絶対参照」が必要になります。

コピーしてもずれないのが「絶対参照」なのか・・・

絶対参照

「絶対参照」にするには、セルに「」ドルマークをつけます。

固定したい【B1】に「$」をつけ【$B$1】にすると、
B列の1行目が固定され、絶対参照となります。

いちいち$マークつけるのはちょっと大変かも・・・

「$」はキーボードの$マークを打たなくてもショートカットで簡単に入力できます。
ショートカットキーは、キーボードの一番上に並んでいるファンクションキーの≪F4≫です。

【C4】でやってみましょう。

「=」から普段通り入力し、
最後の【B1】をクリックした直後に続けて≪F4≫を押します。

計算式入力中

【B1】が【$B$1】に変わります。

F4を押した結果

この計算式を下までコピーします。

計算式をコピー

エラーコードなどがなくなりました。
【C7】の計算式を確認してみましょう。

「=B7*$B$1」、
(1つ左のセル)×(【B1】)となっています。
絶対参照にした【B1】だけはズレずにコピーされました。

絶対参照された

【B1】の手数料率で正しく計算されたね!

「F4」で参照切り替え

絶対参照に切り替えるショートカット≪F4≫ですが、
実は押すたびに変化します。

ショートカット≪F4≫
1回押すと「絶対参照」で行も列も完全固定
2回押すと「複合参照」で行のみ固定
3回押すと「複合参照」で列のみ固定
4回押すと「相対参照」で元に戻る。

F4の循環図

複合参照ってどんな時に使うんだろう・・・

複合参照

上の図であるように、複合参照は「行のみ」または「列のみ」を固定する参照です。
知っていると、意外と使えるシーンがあります。

例を見てみます。

ホテルの夕食付宿泊料の表です。
(青の基本額)+(水色のランクによる加算分)+(緑の夕食)の3つを足して求めます。
複合参照がわかると、赤の【C3】を入れ、他はコピーで完成します。

複合参照が必要な表

1つの式のコピーで9つのセル全部正しい結果なんて出る?

答えはこちらです。
=$B$2+C$2+$B3

$がついているところは固定なのでそのまま読み上げ、
$がついていないところは相対参照なので位置関係で読み上げると以下のようになります。
=(B2)+(同じ列の2行目)+(B列の同じ行)

この式を他のセルにもコピーして使えば完成です。

「=$B$2+C$2+$B3」を入力した【C3】のフィルハンドルをダブルクリックでコピーし、

フィルハンドルでダブルクリック

範囲選択された右下フィルハンドルを【E5】までドラッグします。

フィルハンドルをドラッグ

1つのセルのコピーで表が完成しました。
【E5】の計算式を確認してみます。

複合参照の確認

=$B$2+E$2+$B5
=(B2)+(同じ列の2行目)+(B列の同じ行)
正しい計算でコピーできました。

1つの計算式でできると速いっ!

複合参照で作る九九表

九九表は複合参照を使う良い例です。
上記同様、1つの計算式ですべてのセルをコピーして完成することができます。
【B2】に入る計算式は何でしょう。

複合参照の九九表

=B$1*$A2

(同じ列の1行目)×(A列の同じ行)という位置関係をコピーすることになります。

1行目とA列をずっと掛けるからそこに$がつくんだね!

スポンサーリンク

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!
目次