絶対参照と相対参照をもっとわかりやすく説明【Excel】

絶対参照$ってなに? Excel

計算式をコピーした時、変な数字になることはない?
それは、セルの参照がおかしいのかもしれない。
Excelはなにもしなければ相対参照で計算しているんだよ。

そもそも相対参照ってなに?

Excelの相対参照とは、セルとセルの位置関係を保持した参照方法のこと。
まずは簡単な計算式で見てみよう。

D列に価格×販売数の合計を入れたい。
【D2】の計算式は、「=B2*C2」になる。

Excel_乗算

これを下までコピーする。
オートフィルで一気にコピーしよう。
【D2】の右下にあるフィルハンドルを下までドラッグするか、そこでダブルクリックだよ。
オートフィルについてはこちらから

Excel_相対参照オートフィル

結果はこうなる。

Excel_相対参照オートフィル後

この時、Excelがコピーしたものは、

(2つ左のセル)×(1つ左のセル)という位置関係の計算。

これをずーっと下までコピーしているので、どこへいっても
(2つ左のセル)×(1つ左のセル)の結果が出てくる。

この位置関係を保持した参照を、相対参照という。

絶対参照はどういう時に必要なの?

では、下のような時はどうなる?

これは手数料を出す計算。

C列の手数料には、価格×手数料率で出た金額を入れたい。
【C4】の計算式は、「=B4*B1」となる。

Excel_手数料

これを下までコピーする。
【C4】の右下にあるフィルハンドルを下までドラッグするか、そこでダブルクリック。

Excel_手数料オートフィル

おかしな数字とエラーコードが出てきた!

Excel_手数料オートフィル後

計算式を見てみよう。

Excel_手数料ずれて計算

Excelがコピーした計算式は?

(1つ左のセル)×(1つ左で3つ上のセル)

Excel_手数料相対参照1

【C7】を見てみると・・・
(1つ左のセル)×(1つ左で3つ上のセル)

Excel_手数料相対参照2

(1つ左のセル)の部分はそれぞれの価格だからいいよね?
問題は(1つ左で3つ上のセル)の部分。
ここが手数料率からずれた部分で計算をしてしまっている。

手数料率だけは位置が変わってほしくない。
ずっと固定して計算してもらいたい。

それが絶対参照だよ。
絶対そこを参照して計算するんだっていう強いやつ。

ちなみに、式をコピーする必要がなければ絶対参照も必要ないからね。
時々考え過ぎちゃう人がいるけど、コピーしてみて「あ、おかしいや。」って気付けばOK!
そこで直せば全然大丈夫。

絶対参照はどうやって設定するの?

絶対参照は、固定したいセルに「$」ドルマークをつけるんだ。

固定したい【B1】に$をつけると【$B$1】となる。
これで、B列の1行目を固定してくれる。

$はキーボードの$マークを打たなくてもショートカットがあるよ。
一番上に並んでいるファンクションキーの≪F4≫だ。

【C4】でやってみよう。

” = ”の次に【B4】をクリックし
” * ”(アスタリスク)を入力し、
【B1】をクリックした直後!ここっ!

Excel_絶対参照F4つける

そのまま続けて≪F4≫を押してみて。
ほらっ。【$B$1】になった!

Excel_絶対参照F4押す

オートフィルで下までコピーしてみよう。

Excel_絶対参照オートフィル

【C7】の計算式を確認してみる。

Excel_絶対参照オートフィル後

今度は【B1】の手数料率でちゃんと計算している。
絶対参照のお陰だね。

≪F4≫は絶対参照だけじゃない、便利なショートカット!

ショートカット≪F4≫は押すたびに微妙に変化するよ。

1回押すと「絶対参照」で完全固定
もう1回押すと「複合参照」で行のみ固定
更に押すと「複合参照」で列のみ固定
またまた押すと「相対参照」に戻ってくる。

Excel_参照F4循環

通り越してしまったらまた≪F4≫を頑張って押せばいい。

複合参照ってどんな時に使う?

九九表で見てみよう。これは完成図。
この九九表は計算式1つだけで作ることができるんだ。

Excel_九九表完成

計算式を入れるセルは【B2】
ある計算式を入れたら、オートフィルで下までコピー。

Excel_九九表オートフィル

そのまま(【B2】~【B9】が選択された状態)で、右下(【B10】の右下位置)の
フィルハンドルを右方向にドラッグしてオートフィル。

Excel_九九表オートフィル2

すると全ての九九が埋まる。

Excel_九九表完成

はい、これやってみよう。

計算式を入れるのは【B2】だけだからね。

これは絶対参照ではできないよね?
だって、同じセルをずっと掛けるわけじゃないから。

相対参照もおかしくなる。
どこまでいっても(上のセル)×(左のセル)では間違いだ。

そこで登場するのが複合参照。

では、正解いくよ?
【B2】の計算式はこちら。

=B$1*$A2

1行目と、A列はずっと変わらず計算しないといけないよね。
だから、Aの前と、1の前に$が必要になる。

もちろん、複合参照しなくても計算式をいっぱいつくれば完成するよ。

でも、これが理解できれば参照の上級者だ。

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