相対参照と絶対参照の使い分け

質問

とあるセルに数式を入れて率を求めるときに、相対参照を使って求めた後、
オートフィルを使って表を完成させるとき、他のセルに『＃DIV/0 !』が出てきます。
ならば最初から絶対参照を使った方がトラブルなくすすめられるんじゃないかと
思うのですが、では相対参照は何のためにあるのでしょう・・・？

また、これは相対参照でいけるとかこれは絶対参照じゃないと表が完成されない
などの見分けがつきません。どうしたらいいのでしょうか。

よろしくお願いします。

回答No.1

zap35 · ベストアンサー

こんな例ではいかがでしょう
Ａ１に消費税率1.05を入力し、Ａ２、Ａ３…Ａ１０に商品の価格を入力します
Ｂ２、Ｂ３…Ｂ１０に税込み価格を計算させる場合です。

Ｂ２の式は
　=A2*$A$1
になります。Ｂ３～Ｂ１０はＢ２をコピーします。

するとＢ３の式は
　=A3*$A$1
となっているはずです。Ａ２、Ａ３は相対参照しているためセルをコピーしても同じ行のＡ列の値を参照しています。一方消費税率はＡ１にしか入力されていませんから絶対参照にして、セルをコピー、移動しても参照するセルが変化しないように固定しています。

なおＢ２の式は列のみを固定してもいいです。
　=$A2*$A$1
となります。この場合Ｂ２を右方向に移動しても必ずＡ列の値を参照します。

お礼

絶対参照は必ず乗算しなくてはならないなどの数値があるときに
＄を用いて数式入力すると、すべてのセルに同じ効果が得られる
ということですね。
もう一度やってみます。

参考になりました。ありがとうございました。

回答No.4

tokpy

> これは相対参照でいけるとかこれは絶対参照じゃないと表が完成されないなどの見分けがつきません

普段の生活では，絶対参照と相対参照を使い分けていますよ。
例えば，誰かから住所の書いた紙を見せられて「この人のおうちはどこですか？」と聞かれたら，「そこの角から３軒隣です」と答える場合，「住所」が絶対参照に相当（世界中どこからでもその家になる）し，「３軒隣」が相対参照に相当（所変われば３軒隣も変わってくる）となります。

つまり，エクセルで相対参照というのは，そのセルから見てどこにあるか，という意味であり，例えばA1からB1を見れば「一つ右のセル」という意味になるのです。つまり，A1のセルに「=B1」という式を書いた場合，「B1」の意味は「A1の右隣のセル」という意味になるのであって「B1のセル」という意味ではないのです。

お礼

たしかに住所はどこからでも同じ場所を指しますね。
数式もセルが変わっても同じ内容を示すように設定しなくては
ならないですし、なるほど。住所と共通する点はありますね。

参考になりました。ありがとうございました。

回答No.3

Wendy02

こんにちは。

ちょっと大雑把な内容になりますが、二点間の位置関係を考えてみたらよいかと思います。

A と B の間と、C と D の間は、同じ長さですから、
A:B は相対参照にしておけば、そのまま、C:D に使えます。相対参照というのは、単に、自分と相手との距離だけなのです。それは、どの間にも使えます。

　A ----------------------- B
　|
　|
　|
　|
　|
　[C]-----------------------D

ところが、A/[C] というように、[C]が不可欠な場合は、
[C]を絶対参照にしなければなりませんね。

もし、A/C の数式を、そのまま、B に持っていくと、B/D となってしまいます。
そこで、A/[C]と、Cを絶対参照にして、Bにコピーすると、B/[C] となります。

つまり、その位置の値が絶対に動いては困るという時には、絶対参照になります。多くは、割合を出すときに、合計が必要な場合、その合計は、1つの場所にしかありません。また、順位の場合は、その範囲は動いてもらっては困る場合に、範囲を絶対参照にします。

それは「絶対参照 例：$A$20　」と表記する（通常は、その数式のセルの名前のところで、F4を押す）ことになります。それは、また、決まった数式のパターンにしか使われません。

お礼

＞つまり、その位置の値が絶対に動いては困るという時には、絶対参照になります。

このひとことでさらに感覚がつかめるようになってきました。
割合など不可変の数値に対し、固定しながら数式入力する
ということですね。

ご丁寧にありがとうございました。

回答No.2

mshr1962

基準となるセルや範囲が行列（あるいはどちらか片方）を固定したい場合に有効です。
例えばVLOOKUPという関数では、参照する範囲を固定する必要があるので
B1=VLOOKUP($A1,Sheet1!$A$1:$Z$100,2,FALSE)
のような書き方をします。これならB2にコピーしても
B2=VLOOKUP($A2,Sheet1!$A$1:$Z$100,2,FALSE)
で参照範囲が固定されます。

また合計との比率を確認するなんて場合でも
=A1/A$10
でコピーすればA10の部分を毎回設定しなくていいですよね。

お礼

＞また合計との比率を確認するなんて場合でも
=A1/A$10
でコピーすればA10の部分を毎回設定しなくていいですよね。

たしかに。絶対参照することでどのセルに対しても比率の設定が
できるということですね。

参考になりました。ありがとうございました。