目次
Excelのキホン『絶対参照と相対参照』
Excelは多くのビジネスの場で用いられます。
数式や関数に慣れている方もいれば、そうでない方もExcelに触れる機会が多いでしょう。
慣れる前に起こりがちなトラブルが、数式を参照したつもりが勝手にズレてしまうという問題。
絶対参照と相対参照という機能の使い分けができていない場合に発生します。
今回は絶対参照と相対参照について、基本的な使い方から徹底的に解説します。
まず『参照』とは何か？
最初にExcelの用語として「参照」の意味を見ていきましょう。
Excelにおけるセル参照の意味とは
参照またはセル参照とは、Excel上のセル番地を数式に指定することを意味します。
Excelでは行（縦方向）に「1,2,3…」のような数値が、列（横方向）に「A,B,C…」のようなアルファベットが羅列しています。
数値とアルファベットを組み合わせてセル番地が表現されます。
例として次のようなデータを用意しました。
緑のカーソルがある場所は「F列」の「8行目」なので「F8」のように表現されます。
絶対参照と相対参照の定義
Excelで使う用語としての意味は以下の通りです。
- 絶対参照：参照するセル番地が常時固定される参照方式
- 相対参照：参照するセル番地が数式に連動する参照方式
文章だけではわかりにくいので、実際に使って見ていきましょう。
相対参照を用いた数式の記述方法
前述したデータの仕入商品リストを完成させることを目標とします。
「購入価格」の項目を「単価」と「購入数」の乗算で表現するには、セル参照を用いて「D8」と「E8」および乗算記号「*」の組み合わせで以下のように記述します。
=D8*E8
「Enter」で数式を確定させることで計算結果が表示されます。
続けて緑枠の右下にカーソルを合わせて十字記号を表示させ、ダブルクリックします。
自動的に計算結果が下部のセルにも反映されます。
これをオートフィル機能と呼びます。
購入価格がすべて表示できました。
一番上の項目「=D8*E8」を入力しただけですが、Excelが自動的に「=D9*E9」から「=E14*E14」までの数式を計算しました。
このように、行や列がひとつズレる毎に自動的に数式も変化させる参照方式が「相対参照」です。
ここまでが相対参照の基本です。
続いて仕入商品リストの「お得意様割引」の項目を変化させてみましょう。
絶対参照と相対参照の使い分け失敗例
ここでは絶対参照を使用しなかった場合の失敗例を紹介します。
「お得意様割引」の割引率としてC3のセルに「20%」が入力された場合を考えます。
割引率を用いた価格の変化は「*(1-(割引率))」を乗算することで導出できます。
「購入価格」の項目に下記のように記述します。
=D8*E8*(1-C3)
「Enter」を押して数式を確定させます。
一見、正しく計算結果が表示されているように見えます。
ここで先ほどと同様にオートフィルを使って下部のセルに計算結果を反映させてみましょう。
「#VALUE!」のエラーメッセージが表示されてしまいました。
他の計算結果も怪しいものばかりで、明らかに失敗といえます。
なぜ失敗したのか理由を詳しく見ていきます。
「F14」のセルに記述されている内容を見ると、次のように表示されました。
=D14*E14*(1-C9)
「お得意様割引」が間違ったセルを参照していました。
セル参照が相対的に下のセルへ下のセルへと連動した結果、連動してはいけないセルまで動いてしまって計算結果がおかしなことになりました。
連動してはいけないセルはExcelが自動的に判断できないので、手作業で固定させる必要があります。
セル参照を固定させることを「絶対参照」と呼びます。
絶対参照と相対参照の正しい使い分け
絶対参照を用いてExcelを修正していきます。
数式内の固定させるセルにカーソルを合わせてキーボードの「F4キー」を押すことで絶対参照に切り替わります。
「$」が絶対参照のマークです。
今回は「お得意様割引」のセルに絶対参照を割り当てたいので以下のように記述します。
=D8*E8*(1-$C$3)
先ほどと同様にオートフィル機能を用いて下部のセルに数式を反映させます。
エラーもなく正しく割引率が適用されていることが確認できました。
以上が絶対参照と相対参照の機能です。
絶対参照は「$」マークが付きますが、アルファベットの前だけ、数字の前だけというように行・列どちらか片方だけを固定することもできます。
F4キーを押す回数で切り替えることが可能で、片方だけを固定する場合は「複合参照」と呼ばれます。
- F4キー1回：行と列が固定（絶対参照）
- F4キー2回：行が固定（複合参照）
- F4キー3回：列が固定（複合参照）
- F4キー4回：固定なし、初期状態（相対参照）
絶対参照がキッチリ使いこなせるようになれば、もう「数式がズレた！」と慌てる必要はありません。
おまけ
せっかくなので「仕入商品リスト」を完成させておきましょう。
左上の項目「仕入商品点数」と「仕入金額計」を表示させます。
どちらもSUM関数で容易に記述可能です。
仕入商品点数は「購入数」を合計すると求められるので以下の通りです。
=sum(E8:E14)同様に仕入金額計は「購入価格」の合計なので以下の通り。
=sum(F8:F14)
以上で仕入商品リストが完成しました。
まとめ
Excelの基本である絶対参照と相対参照について解説しました。
- セル参照とはExcelのセル番地を数式に割り当てること
- 相対参照は自動的に数式が連動する
- 数式内の固定したいセルはF4キーで絶対参照を用いる
当サイトでは基本的なExcelスキルを多数紹介しています。
