\今日から出社せず退社/ 退職代行ガーディアン

絶対参照・相対参照の違いと使い分ける方法を徹底解説!

紬ちゃん

エクセルの絶対参照と相対参照の違いがよくわからないので、意味と使い分け方を教えてください。

ライクさん

絶対参照と相対参照は、計算式をコピーするときに必要になるんだ。2つの参照の違いと使い分け方をわかりやすく解説するね。

絶対参照と相対参照は、言葉は何となく知っていても、違いや使い分け方がよくわからないという方もいらっしゃるのではないでしょうか。

絶対参照と相対参照の違いがわからなければ、計算式をコピーしたときに正しい結果を表示できない可能性があります。

絶対参照と相対参照の違いと使い分け
  1. 計算式をコピーしたときにセルをずらしたくない場合は、絶対参照を使う
  2. 参照するセルをずらしてコピーしたい場合は、相対参照を使う
  3. 「絶対参照」と「相対参照」の判断がつかないときは、計算式をコピーする範囲に参照先が同じセルがあるか、ないかで判断する

本記事では、絶対参照と相対参照の違いを初心者の方にもわかるようにわかりやすく解説しています。

販売価格表、納品書を作成しながら実務に役立てる方法も紹介していますので、ぜひ参考にしてみてください。

絶対参照・相対参照の使い分け以外に、エクセルの参照に関する関連記事がありますので、こちらの記事もぜひご覧ください。

目次

エクセルの絶対参照と相対参照の違い

エクセルで計算式を挿入する場合、「=1*2」のように数字を直接入力することもできますが、一般的には「=A1*B1」のようなセル番地を使って入力するケースが多いです。

セル番地で入力した計算式はコピーが可能です。

ライクさん

計算式はコピーで入力できるんだけど、「絶対参照」と「相対参照」を使い分ける必要があるんだ。

ビジネスで計算式を使用する場合、数字を直接入力するケースはほとんどないので、絶対参照と相対参照の違いを覚えて正しい使い分けができるようにしておきましょう。

この2つの意味と違いを解説していきます。

セルを固定して絶対参照にする方法

参照しているセルを固定して計算式をコピーしたい場合は、絶対参照を使います。

紬ちゃん

セルを固定するってどういうことですか?

ライクさん

実際のデータを見て、確認してみよう。

デスクトップPCの売価を計算する
デスクトップPCの売価を計算する

これは、C列に入力されている定価に「D1」の掛け率を掛けて、売価が計算されるように作成した表です。

「D6」のセルには「=C6*D1」という計算式が挿入されています。

「D6」に挿入した計算式をオートフィルを使ってコピーしてみましょう。

計算式をコピーした後の画像
計算式をコピーした後の画像

「D6」の計算式をコピーすると、計算式の値が「0」と表示されてしまいました。

ライクさん

コピーされた計算式を、数式で表示して確認してみよう。

コピーした計算の数式
コピーした計算の数式

コピーした計算式を数式で表示してみると、掛け率が入力されている「D1」のセルがずれて、空白のセルを掛けてしまっていることがわかります。

紬ちゃん

セル参照で入力した計算式ってコピーできるんですよね?どうしてセルの位置がずれてしまうんですか?

ライクさん

計算式はオートフィルでコピーするとセルの位置が1つずつずれるという性質があるからなんだ。

掛け率が入力されている「D1」のように、計算式をコピーしてもセルがずれないようにするのが「絶対参照」です。

セルの固定
セルの固定

セルを固定したいときは、セル番地のアルファベットの前後に半角の「$」を入力します。

「D1」のセルを固定してから、計算式をコピーしてみましょう。

正しい計算結果が表示された
正しい計算結果が表示された

今度は、正しい計算結果を表示することができました。

セルを固定した計算式
セルを固定した計算式

これは、先ほどコピーした計算式を数式で表示したものです。

計算式をコピーしても、すべてのセルに掛け率が入力されている「D1」表示されており、セル位置が固定されていますね。

このように、アルファベットの前後に「$」を入力してセル番地を入力しておくとセルを固定することができます。

相対参照でセルをずらして計算式をコピーする手順

セルをずらして計算式をコピーしたいときは、相対参照を使います。

絶対参照で解説したように、オートフィルで計算式をコピーするとセルの位置が1つずつずれるという性質があります。

セルをずらして計算式をコピーしたいときは、何もつけずにセル番地を入力します。

相対参照の計算式
相対参照の計算式

これは、C列の個数とD列の単価を掛けて、E列に合計が計算されるように作成した表です。

このようなデータの場合、C列もD列も下方向にずらす必要があるため、「$」マークなどの記号は何もつけずに相対参照で入力します。

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

紬ちゃん

絶対参照と相対参照は、計算式をコピーするときにセルを固定したいのかずらしたいのかが違うっていうことはわかったんですけど、実際のデータに計算式を入力するときはどのように使い分ければいいんですか?

ライクさん

なんとなく違いがわかっても、実際のデータではどう使い分けたらいいかわからなくなってしまうこともあるよね。
「販売価格表」と「納品書」を作成して、さらに理解を深めていこう!

セルをずらしたくない場合は絶対参照を使う

紬ちゃん

絶対参照なのか相対参照なのか迷ったときは、どこを見て判断したらいいんですか?

ライクさん

どちらを使うのか迷ったときは、計算式を入力するセルに同じ参照先のセルを入れる必要があるか確認してみよう。

掛け算を例に、参照するセルを確認してみましょう。

計算に使用するセル位置
計算に使用するセル位置

左側が絶対参照を使うケースで、右側が相対参照を使うケースです。

左側の絶対参照を使うケースを見ると、定価の列は前の計算で使用したセルの1つ下にあるセルを参照する必要がありますが、掛け率の数字はすべて同じセルを参照します。

つまり、「D6からD8」のセルの計算式は、必ず「D1」を参照する必要があるということです。

計算式を挿入するすべてのセルに「参照先が同じもの」があるときは、そのセルを絶対参照にします

ライクさん

この計算式では、「D1」は絶対参照にする必要があるってことだね。

一方、相対参照を使う右側のケースを見ると、個数も単価もそれぞれ1つ下のセルを参照する必要があります。

「個数×単価」のようにセルをずらす計算の場合、両方とも絶対参照で固定してしまうとすべて同じ答えになってしまうので注意しましょう。

補足

四則計算だけではなく関数の範囲などでセル番地を使用する場合も、計算式をコピーするとセルの位置がずれてしまいます。
関数の場合は、少しわかりにくい場合があるので注意してください。

絶対参照を使って販売価格表を作成する

自社製品を販売する場合、年間の取引額によって掛け率を変更するケースがありますね。

ランク別の販売価格表
ランク別の販売価格表

そのような場合は、ランク別の販売価格表を作成しておくと便利です。

絶対参照を使って、上記のようなランク別の販売価格表を作成してみましょう。

STEP
元になる表を作成
ランク別の販売価格表の元データ
ランク別の販売価格表の元データ

「価格表」、「ランク別の掛け率」、「取引先別のランク表」を作成します。

今回は、手順がわかりやすいように同じシート内に「ランク別の掛け率の表」と「取引先別のランク表」を作成していますが、別のシートに作成しても問題ありません。

STEP
Aランクの販売価格の計算式を入力
Aランクの計算式を挿入
Aランクの計算式を挿入

「D2」をクリックして「=C2*$I$2」と入力し、Enterで確定してください。

=C2*$I$2

上記の数式をコピーすると、「I」(アイ)が「L」(エル)と誤認されてしまうことがあります。
同じ数式で動作を確認する場合は注意してください。

ライクさん

「$」は、「I2」をクリックしたあとにF4を押すと、簡単に入力できるよ!

STEP
Aランクの販売価格の計算式をコピーする
Aランクの計算式をコピーする
Aランクの計算式をコピーする

「D2」のセルをクリックしなおし、セルの右下にある「」をダブルクリックしましょう。

STEP
Aランクのすべての販売価格が表示された
Aランクのすべての販売価格が表示された
Aランクのすべての販売価格が表示された

すべてのセルにAランクの販売価格を表示することができました。

STEP
Bランクの販売価格の計算式を挿入
Bランクの販売価格の計算式を挿入
Bランクの販売価格の計算式を挿入

「E2」に「=C2*$l$3」と入力してEnterで確定してください。

=C2*$l$3
STEP
Bランクの販売価格の計算式をコピーする
Bランクの販売価格の計算式をコピーする
Bランクの販売価格の計算式をコピーする

Aランクと同様にBランクの販売価格を求める計算式を挿入します。

「E2」に挿入した計算式をコピーしてください。

STEP
Cランクの販売価格の計算式を挿入
Cランクの販売価格の計算式を挿入する
Cランクの販売価格の計算式を挿入する

「F2」を選択して「=C2*$l$4」を入力し、Enterで確定します。

=C2*$l$4
STEP
Cランクの販売価格の計算式をコピーする
Cランクの計算式をコピーする
Cランクの計算式をコピーする

「F2」に挿入した計算式をコピーしましょう。

STEP
ランク別の販売価格表を作成できた
ランク別の販売価格表
ランク別の販売価格表

ランク別の販売価格表を作成することができました。

複数のセルに特定の掛け率を使って計算式を入力するときは、掛け率が入力されているセルを絶対参照にしてから計算式をコピーします。

セルの固定に使う「$」は、セルをクリックしたあとにF4を押すと簡単に入力できるので試してみてください。

セルをずらしたい場合は相対参照にする

納品書のように、「個数」と「単価」を掛けて計算するような計算はセルをずらす必要があります。

納品書を作成するときはセルをずらす必要がある
納品書を作成するときはセルをずらす必要がある

セルをずらして相対参照にしたい場合は、「A1」のようにセル番地に何もつけずに入力します。

相対参照の性質を使って、納品書を作成してみましょう。

相対参照を使って納品書を作成する

相対参照だけではなく、仕事で応用しやすいようにIF関数を組み合わせた方法を紹介します。

少し手順が多くなりますが、この方法を覚えておくと請求書などにも応用できるのでぜひ挑戦してみてください。

STEP
納品書の元データを作成する
納品書の元データ
納品書の元データ

納品書の元データを作成します。

完成をイメージしやすいように品名・納品日・納品場所を入力してありますが、「STEP9」まではすべての項目を空欄の状態で作成してください。

ライクさん

角印は、エクセルで作成した電子印鑑を使用しているよ。

STEP
「E20」にIF関数を挿入する
IF関数を挿入する
IF関数を挿入する
  • 「E20」をクリック
  • 「=IF(C20="","",」と入力
=IF(C20="","",
ライクさん

「=IF(C20="","",」は、C20が空白のときは空白にしてねという意味なんだ。掛け算の前に上記のIF関数を挿入しておくと、単価が入力されていないときに合計のセルに「0」が表示されなくなるようにIF関数を入力しているよ。

STEP
単価のセルを選択
単価のセルをクリックする
単価のセルをクリックする
  • 単価を入力する先頭のセルの「C20」をクリック
  • 「*」を押す
STEP
数量のセルを選択
数量のセルをクリック
数量のセルをクリック
  • 数量を入力する先頭のセルの「D20」をクリック
  • 「)」を入力してEnterで確定する
STEP
数式をコピーする
数式をコピーする
数式をコピーする

「E20」をクリックしなおして、「E28」まで計算式をコピーしてください。

補足

この画像のように罫線の太さを変えている場合、オートフィルでコピーすると罫線の太さが変わってしまいます。
書式を変更している場合は、以下の方法がおすすめです。

①「E20」をクリックしてコピーする
②「E21」から「E28」の範囲を選択
③「ホーム」タブの貼り付けアイコンの下向き矢印をクリック
④「値で貼り付け」を選択

書式が崩さずに計算式がコピーできるので試してみてください。

STEP
小計にSUM関数を挿入する
SUM関数を挿入
SUM関数を挿入
  • 「E29」をクリック
  • 「Σ」を押す
  • 「E20」から「E28」の範囲を選択
  • Enterで確定
STEP
消費税の計算式を挿入
消費税の計算式を挿入
消費税の計算式を挿入

「E30」をクリックして「=E29*0.1」と入力し、Enterで確定してください。

STEP
合計の計算式を挿入
合計の計算式を挿入
合計の計算式を挿入

合計金額の「E31」をクリックして、「=SUM(E29:E30)」と入力します。

=SUM(E29:E30)
STEP
「B13」に「E31」と同じ金額が表示されるようにする
「B13」に「E31」を参照する計算式を挿入
「B13」に「E31」を参照する計算式を挿入

「B13」に「E31」を参照する計算式を挿入します。

「B13」を選択して「=E31」と入力してください。

ライクさん

この状態のデータを原本として保存しておこう!

STEP
「納品日」・「納入場所」・「品名」・「単価」・「数量」を入力
必要な項目を入力する
必要な項目を入力する

単価と数量を入力すると、「各品名の金額」、「小計」、「消費税」、「合計金額」の計算結果が表示されます。

参考

「小計」、「消費税」、「合計金額」の計算式の前に、「=IF(C20="","",」を入力し、それぞれの計算式の最後に「)」を入力しておくと、金額が入力されていないときに「0」が表示されなくなります。

白紙の状態の原本に「0」を表示させたくないときは、IF関数と組み合わせた計算式を挿入しておきましょう。

エクセルの絶対参照・相対参照に関するQ&A

エクセルの絶対参照と相対参照とは何ですか?

絶対参照と相対参照は、エクセルに挿入した計算式をコピーするときに必要な操作です。

セル参照で計算式を挿入する場合、計算に使用したいセル番地を入力しますが、「$」などの記号がついていない状態でセル番地を入力した状態が相対参照です。

セル番地のアルファベットの前後に「$」をつけてセル番地を入力することを「絶対参照」といいます。

絶対参照と相対参照はどのように使い分ければいいんですか?

計算式をコピーしたときに、セルの位置をずらしたいときは「相対参照」で入力します。

セルの位置をずらしたくないときは「絶対参照」で入力します。

絶対参照と相対参照の使い分ける方法は、絶対参照と相対参照の使い分け方で解説していますので、参考にしてみてください。

絶対参照なのか相対参照なのか判断ができないときはどうすればいいですか?

コピーを使って計算式を挿入する範囲に、同じセルを挿入する必要がある箇所があるか確認してみましょう。

例えば、定価に特定のセルの数字の掛け率を掛けて売価を計算するといった場合は、すべてのセルで掛け率が入力されているセル番地が必要になるので、掛け率のセルを絶対参照にする必要があります。

エクセルの絶対参照と相対参照の違いで具体的な数字を使って解説していますので、参考にしてみてください。

絶対参照と相対参照の意味を理解して正しく使い分けよう!

絶対参照と相対参照は、セル参照で入力した計算式をコピーするときに必要な操作です。

セル参照で入力した計算式をそのままコピーすると、セルの位置がずれるという性質があります。

そのため、コピーする計算式の内容に合わせて絶対参照と相対参照を使い分けなければ、正しい計算式が入力されない可能性があるので注意してください。

最後に、絶対参照と相対参照の使い分け方をおさらいしておきましょう。

おさらい
  1. 絶対参照は、計算式をコピーしたときにセルをずらしたくないときに使う
  2. 相対参照は、参照するセルをずらしたいときに使う
  3. 「絶対参照」と「相対参照」の判断がつかないときは、計算式をコピーする範囲に参照先が同じセルがあるか、ないかで判断する

ビジネスでは、コピーを使って計算式を入力することが多く、1つずつ計算式を入力することはほぼありません。

そのため、絶対参照と相対参照の違いと使い分け方を理解しておくことが必要です。

意味さえわかれば簡単に使い分けることができるので、ぜひマスターしておきましょう。

絶対参照と相対参照の使い分け方以外にも、エクセルの絶対参照に関連する記事があります。

こちらの記事も参考にしてみてください。

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