使い方

エクセル【絶対参照・相対参照・複合参照】の使い方をマスターしよう

エクセル初心者
エクセル初心者
単価×個数の計算式を入力しているのだけど、単価だけを固定して計算式を作れないかな。項目の数だけ単価を手入力するのは大変なんだよねー。
エクセル初心者
エクセル初心者
値を固定するには数式に『$』を入力するといいみたいよ。でも『$』をどこに入力するのかな?
Dr.オフィス
Dr.オフィス
それは、『絶対参照』のことだね。数式に入力する『$』の位置で、結果も全く違うものになってしまうから、絶対参照・相対参照・複合参照の違いを知っておくといいよ!

今回は、絶対参照・相対参照・複合参照について解説します。

単価と個数の計算式で単価が固定の場合は絶対参照、それぞれ単価がある場合は相対参照、また絶対参照と相対参照を組み合わせたものを複合参照と言います。

絶対参照・相対参照・複合参照を使う簡単ステップ
  1. 数式内で、固定する参照先のセル番号をクリックする
  2. 『F4』キーを押し、参照方法を決める
    (絶対参照、相対参照、複合参照)

以上の2ステップで絶対参照・相対参照・複合参照を使い分けます。

絶対参照・相対参照・複合参照を使い分けると計算式の作成にかかる時間が短縮され、編集も楽になりますよ。

Dr.オフィス
Dr.オフィス
『絶対参照』は『行と列』を同時に固定。
『相対参照』は基準セルをもとに参照。
『複合参照』は『行だけ』、『列だけ』を固定。
詳しく解説していくから覚えておいてね!

便利な『3D参照』で串刺し計算もできるので、興味のある方は≫【エクセル3D参照】複数シートの同じ位置の値を集計するも参考にされてみてくださいね。

タイトル
【エクセル3D参照】複数シートの同じ位置の値を集計するエクセルの3D参照を使って、各シートの同じ位置にある値を集計する方法を解説します。3D参照するデータは、同じフォーマットのデータを使います。3D参照は、シートを追加、移動、削除することも可能です。しかし、3D参照する範囲を気を付けて確認していないと、3D参照の範囲から外れてしまうこともあります。...

次項より、絶対参照について詳しく解説するので、ぜひ読み進めてみてください。

※本記事は『OS:Windows10』画像は『Excelのバージョン:2016』を使用しています。

エクセル【絶対参照】とは?

エクセルの絶対参照について『ジャガイモ地域別仕入表』で解説します。

C列で金額が計算される計算式の表示仕入金額の計算

↑セルA2の単価200円とB列の個数により、C列で仕入金額が計算されます。

数式の表示数式の表示

↑数式で見てみましょう。この場合のセルC6の計算式は「=A2(単価)*B6(個数)」になりますね。1度きりの計算であればこれでいいのかもしれませんが、この計算式が100行ある場合や、さらに1000行の追加入力を依頼されてしまった場合はどうしますか?

そこで活躍するのが絶対参照です。しかも『$』を追加するだけです!!

セルA2をに絶対参照にするセルA2をに絶対参照にする

今回は、セルC6の単価セルA2を絶対参照にします。この場合セルA2の『A』と『2』の前に『$』付けます。

セルC6に数式『=A2*個数』とする場合、『A』と『2』の前に『$』を追加して、『=$A$2*B6(個数)』とします。

実際の入力方法は、セルC6に『=』を入力してセルA2を選択し『F4』キーを押すと、『A2』が『$A$2』となりますので、その後に『*個数』とします。

絶対参照は『行と列』を同時に固定絶対参照は『行と列』を同時に固定

↑セルA2を絶対参照($A$2)にする=『行と列』を同時に固定、つまり『2行目A列』のセルを固定して参照するという意味になります。もっと簡単に言うと、『絶対参照を使った数式「=$A$2(単価)*個数」の単価の部分は必ずセルA2を参照してね。必ずセルA2だよ!!』という意味になります。

エクセル初心者
エクセル初心者
んー、まだちょっとわからないかも。
Dr.オフィス
Dr.オフィス
では、もう少し考えてみましょう!
セルC12の数式『=A2*個数』をセルC21までコピー数式をコピーする

↑絶対参照を使う前の計算式、セルC12の数式『=A2*個数』をセルC21までコピーすると、エラーが出てしまいます。

コピーされた数式の確認コピーされた数式の確認

↑単価の参照先がA2からA3,A4・・・A11と動いてしまい、計算式も結果も成立しないものになってしまいますね。

絶対参照を使ったセルC6の数式『=$A$2*個数』をセルC21までコピー絶対参照を使ったセルのコピー

↑絶対参照を使ったセルC6の数式『=$A$2*個数』をセルC21までコピーすると正しく計算されます。

絶対参照を使った数式の確認絶対参照を使った数式の確認

↑そこで、単価の参照先を絶対参照にしていれば、下方向にコピーをしても参照先が下に移動することなくコピーでき、単価の変更があった場合でも単価の参照先のみ変更すれば、計算式の全てに即座に反映されます。

最初は、絶対参照を使った計算式と使っていない計算式を横に並べ、下方向へのコピー結果を比べて、セルの動きを確認してみると分かりやすいです。

Dr.オフィス
Dr.オフィス
『絶対参照』は参照先の『行と列』を同時に固定するということだよ!
タイトル
【エクセルのショートカットキー】数式に絶対参照を設定する「F4」エクセルで数式を入力するときに、常に動かしたくない引数があったりします。 そんな時は、その引数を固定しましょう。 固定するに...

次項では、『相対参照』について解説します。



エクセル【相対参照】とは?

相対参照は、絶対参照と違い『$』は使いません。

数式の結果数式の結果

↑B列に単価、C列に個数を入力するとD列で合計金額が計算される計算式を作成したとしましょう。セルD6に『=B6*C6』と入力してエンターで確定すると合計金額が計算されました。

この数式をセルD6から下方向3つ目のセルD9にコピーしてみましょう。正しく計算されました。

数式の確認数式の確認

↑セルD6を基準として下方向3つ目のセルに数式をコピーしましたので、単価はセルB6を基準セルとして下方向3つ目のセルB9、個数はセルC6を基準セルとして下方向3つ目のセルC9を参照することになります。数式の中身を確認すると、セルD9は『=B9*C9』ですので、それぞれの基準セルから下方向3つ目のセルを参照していますね。

相対参照相対参照

↑このように、参照セルが同じように移動することを相対的に移動すると言います。

エクセル初心者
エクセル初心者
その計算式なら、セルD6の数式をD12までオートフィルでいつもコピーしているよ!
Dr.オフィス
Dr.オフィス
そうです!それを相対参照と言います。相対参照は、意識しないまま使っているということになるね。
『相対参照』は基準セルをもとに参照するということだよ!

次項では、『複合参照』について解説します。

エクセル【複合参照】とは?

絶対参照と相対参照を解説してきましたが、その2つを組み合わせたものを複合参照と言います。

『=A$1』の確認『=A$1』の確認

↑セルB1をみてみましょう。セルA1の『行』を絶対参照にして『列』を相対参照とする場合は、『A1』の『1』の前に『$』を付け『=A$1』とします。

『=$A1』の確認『=$A1』の確認

↑セルB1をみてみましょう。セルA1の『列』を絶対参照にして『行』を相対参照とする場合は、『A1』の『A』前に『$』を付け『=$A1』とします。

それでは、仕入表から複合参照を使って計算してみましょう。

北海道ジャガイモの仕入数は、全体の仕入合計の何割でしょうか。

数式の結果数式の結果

↑割合を出す計算式は、『北海道ジャガイモの仕入数÷ジャガイモの仕入合計』ですね。セルE7をコピーすると『#DIV/0!』が表示され正しく計算ができません。

数式の確認数式の確認

↑セルE7に入れる数式は、『=B7/B14』になります。このセルE7の数式をセルE8にコピーすると数式が相対参照のため『=B8/B15』になり、ジャガイモの仕入合計のセルB14が参照されないため正しく計算ができません。

数式の確認数式の確認

↑そこで、セルE7に入れる数式のB14を絶対参照にしてみましょう。

セルE7に入れる数式は、『=E7/$B$14』になります。セルE7の数式をセルE13までコピーしてみましょう。セルE8の数式が『=B8/$B$14』になります。相対参照の時とは違って、セルE7からセルE13まで正しい数式になりましたね。

『よしできた!!』と思いきや、この先が問題です。

数式の結果数式の結果

↑このセルE7の数式をセルF9までコピーしてみましょう。北海道りんごの仕入割合を出したいのに、りんごの仕入合計が参照されず正しい結果が出ません。

数式の結果数式の結果

↑では、どうしたらいいのかというと、『行だけ』を固定します。行を固定する場合は『B14』の『14』の前に『$』を付けます。

セルE7を選択します。『=』を入力してセルB7を選択し『/』を入力してB14を選択します。続けて『F4』キーを2回押すと『B14』が『B$14』となります。

セルE7の数式を確認すると『=B7/B$14』になりましたね。セルE7の数式をセルG13までコピーし、セルの数式を確認してみてください。

数式の確認数式の確認

↑セルG13を見てみます。沖縄県みかんの仕入数の割合ですので、数式『=D13/D$14』は、正しいセルを参照していることが分かります。

セルE7をセルG7までコピーすると、『B$14』は、『行だけ』固定しているため『C$14』『D$14』と列だけ右方向に移動して参照します。セルE7をセルE13まで下方向にコピーをしても数式の『B$14』は変わらず、参照先の『行』は14行目のまま参照します。

『行』は絶対参照、『列』は相対参照、その2つを組み合わせているので、複合参照と言います。

複合参照になれるまでは、『行』を固定するのか『列』を固定するのか正直分かりにくいです。複合参照を使った時は、まず下方向に1つ右方向に1つコピーして、数式がどのように変化しているのか確認していくといいでしょう。

複合参照の計算式を作成できるようになれば、編集も楽になって時短にもつながります。まずは、簡単な数式に複合参照を使ってセルの動きを確認していきましょう。

Dr.オフィス
Dr.オフィス
『複合参照』は『行だけ』、または『列だけ』を固定することです!

次項では、参照方法の切り替えについて解説します。

F4キーで参照方法を切り替え

参照方法は、『F4』キーで簡単に切り替えができます。

セルB1を選択し『=』を入力します。そしてセルA1を選択すると『=A1』と表示されます。この状態で、下記①~⑤の確認をしましょう。

『F4』キーを1回押す『F4』キーを1回押す

『F4』キーを2回押す『F4』キーを2回押す

『F4』キーを3回押す『F4』キーを3回押す

『F4』キーを4回押す『F4』キーを4回押す

  1. 『F4』キーを1回押すと『A1』が『$A$1』と表示されます。
  2. 『F4』キーを2回押すと『A1』が『A$1』と表示されます。
  3. 『F4』キーを3回押すと『A1』が『$A1』と表示されます。
  4. 『F4』キーを4回押す『$A1』が『A1』と表示されます。
  5. ④以降も『F4』キーを押すと、①~④を繰り返します。
Dr.オフィス
Dr.オフィス
数式を確定した後にも切り替えは可能だよ!
確定した数式を選択確定した数式を選択
数式バーを選択数式バーを選択
参照方法の切り替え参照方法の切り替え

↑確定した数式を選択し、数式バーに表示されたセル番号をクリックして『F4』キーを押すと同じように参照方法が切り替えられます。エンターキーで確定します。



まとめ|エクセルの参照

今回は、絶対参照・相対参照・複合参照について解説しました。

セルの参照方法には絶対参照・相対参照・複合参照があり、それぞれの参照方法で計算式の意味と結果が変わることが確認できましたね。

絶対参照・相対参照・複合参照のまとめ
  • 常に同じセルを参照する方法を『絶対参照』 (入力例) $A$1
  • 相対的にセルを参照する方法を『相対参照』 (入力例) A1
  • 『絶対参照』と『相対参照』の2つを組み合わせて参照する方法を『複合参照』 (入力例) A$1、$A1
  • 『$』を入れるには『F4』キーを押す

絶対参照と相対参照を個別に使った数式は、苦手意識を持たずに取り入れることが出来ると思います。

複合参照を使った数式は、慣れるまで時間がかかると思いますが、『行』と『列』をしっかり理解できていれば、繰り返し練習することで『どっちを固定するんだっけ?』と迷わなくなりますよ。

結果を表示結果を表示
数式で表示数式で表示

↑絶対参照と複合参照の結果を作成しました。セルの値がどうなるかを見比べてみてください。

まずは、簡単な数式からチャレンジしてみてくださいね。

ABOUT ME
Dr.オフィス
Dr.オフィス
エクセルの使い方や応用法を日々研究しています。 初心者から上級者までわかりやすく解説していきます。 エクセルについてわからないことや気になることなどございましたら是非、コメントやお問い合わせからご質問ください。