Excel(エクセル）で関数を利用していると「参照」というワードを良く見かけます。
しかし「参照」という言葉をよく分からず利用している方は多いと思います。
今回は参照の意味を改めて振り返り、数式や関数を作成する際に必ず使い分ける必要がある「絶対参照」と「相対参照」について図解で解説します。
また、参照方法を簡単に切り替える「F4」キーの使い方と、参照方法を切り替える際に必要な「$」マークの要・不要を簡単に判断する方法もご紹介しますので是非ご覧ください。
それではどうぞ！
セルの参照とは？
Excelで数式を使う時に、「＝B3+C3」など直接数字を入力せずセルを指定することがありますよね。
このように、セルの情報を使うことを「参照」もしくは「セル参照」と言います。
関数でセルを読みにいくことを「参照」という、と理解すれば分かりやすいかと思います。
例えば、下の図のようにD3セルには「＝B3+C3」が挿入されています。
このD3セルはB3セルとC3セルを参照している、と言います。
数式で利用されている、つまり参照されているB3セルやC3セルのことを「参照元」や「参照元セル」と言います。
一方、数式が挿入されたD3セルのことを「参照先」や「参照先セル」と言います。
エクセルの相対参照とは
「セル参照」には２種類が存在し、１つが「相対参照」、もう1つが「絶対参照」と言います。
まず「相対参照」とは何かについて解説します。
「相対参照」とは、数式が入力されたセルを別のセルにコピーしたときに、参照するセルが自動で相対的に移動する機能のことです。
それでは相対参照がどんな機能を持っているのか、具体的に図解で説明していきます。
相対参照の機能
下の表で相対参照の機能を説明します。
D3セルには数式「＝C4＋B4」が挿入されています。
このセルを「Ctrl＋C」キーのショートカットキーでコピーしてから、下のD4セルからD8セルに「Ctrl＋V」キーのショートカットキーで貼り付けます。
そうすると、数式を貼り付けたセルが、横のセルを正しく足し算出来ていることが確認出来ます。
試しに、一番下のD8セルの数式を確認すると、「＝C8＋B8」となっています。
このように、D3セルの数式「＝C3＋B3」をC8セルに貼り付けると「＝C8＋B8」に変わり、参照元が相対的に移動していることが分かります。
このようにExcelは数式や関数を挿入しているセルの位置によって、貼り付けた後の参照元のセルの位置を相対的に判断してくれて移動してくれます。
このような参照方法を「相対参照」と呼びます。
この機能があるおかげで、数式を別のセルに貼り付けた後にセル番号を度々修正せずに済んでるということでです。
次に見出しで紹介するけど、数式に「$」のマークが無ければ相対参照だよ。
エクセルの絶対参照とは
続いて、もう１つのセル参照である「絶対参照」について解説します。
先ほど説明したとおり、「相対参照」はコピーした後に参照セルが相対的に移動していました。
一方、「絶対参照」は、コピーしても参照セルが移動しない参照方法のことを言います。
エクセルで数式や関数を作成する時に、別のセルにコピーすることを考えて、「絶対参照」を利用する場面が多く存在します。
絶対参照の機能
それでは、具体的に「絶対参照」の機能を紹介します。
例えば、下の表のC5セルに消費税を計算するため、「本体価格×消費税率」の計算式「=B5*C2」を入力します。
そうすると、本体価格100円の消費税率10％で、消費税は「10」円と正しく算出されました。
今度は、下のセルの消費税も計算するため、このC5セルの数式「=B5*C2」を下のセルにコピペします。
そうすると、下の表のように、数式の結果が「0」や「#VALUE！」や「4000」などとなり、明らかに結果が正しくありません。
数式を貼り付けた一番下のC9セルの数式を確認すると、「=B9*C6」となっています。
一番下のセルの数式は「=B9*C2」になってほしかったですよね。
しかし、C5セルの数式をコピーして４つ下のセルに数式を貼り付けたので、参照セルも４つ下に移動したため数式が誤ったセルを参照してしまっています。
こうならないように、数式の参照セルを移動しないように固定するのが「絶対参照」です。
それでは、C5セルの数式を「絶対参照」に設定するため、移動して欲しくないセルの行番号と列番号の先頭に「$」マークを設定します。
「=B5*C2」⇒「=B5*$C$2」
絶対参照の設定方法は次の見出しで詳しく紹介するので、ここでは「$」が必要なんだ、と軽く考えてもらえれば良いです。
そして、先ほどと同様に、この数式を下のセルにコピペしてみましょう。
すると、下の図のように、全てのセルの消費税を正しく算出することが出来ました。
一番下のセルの数式を確認すると、「=B9*$C$2」となっていて、「$」マークで固定した参照セルはコピー先でも移動していないことが分かります。
このように、作成した数式を別のセルにコピーした時に動いてほしくない場合は、「絶対参照」の設定が必要です。
セルの参照方法を変更する方法
参照方法には、絶対参照と相対参照と、それらが混在した複合参照の３つが存在します。
これらの参照方法を簡単に設定する方法を紹介します。
絶対参照の「$」マークは、数式を作成し終えてから入力するわけでなく、数式を作成する過程で設定します。
ファンクションキー「F4」を利用する方法と、直接キーボードで入力する方法の２種類があります。
ただ、直接キーボードで入力する方法はお勧めしません。
今後のエクセル操作をスムーズに行うためにも、「F4」キーを使った方法を利用するようにしましょう。
ファンクションキー「F4」のショートカットキーを利用
まずはファンクションキー「F4」キーを使う方法を紹介します。
キーボードの上側にファンクションキーが並んでいますので、「F4」キーの位置を確認してください。
まず、絶対参照に設定したいC2セルまでの数式を作成します。
数式を「Enter」キーで確定せずに、「F4」キーを１回押します。そうすると、数式内の「C2」が「$C$2」と絶対参照に切り替わります。
絶対参照を設定し終えたら、「Enter」キーを押して数式を確定させます。以上で作業は完了です。
「$C$2」という絶対参照は、C2セルから動かないように固定するという状態です。
絶対参照には、他にも「C$2」「$C2」という設定も存在します。
こういった「$」マークが１つだけ設定されている絶対参照のことを「複合参照」ともいいます。(具体的な利用方法は以降の見出しで紹介）
このような「$」マークを設定する場合は、「F4」キーを１回でなく、複数回押します。
２回目の「F4」キーを押すと、「$C$2」は「C$2」に、３回目を押すと「$C2」に、４回目を押すと「C2」となり、相対参照に戻ります。
このように「F4」キーの便利なところは、「F4」キーの入力回数に合わせて「$」マークの付き方が４段階変化します。
行のみを固定する、列のみを固定する、相対参照に戻すなど、「相対参照」「絶対参照」「複合参照」を容易に切り替えることができます。
「F4」キーの機能について、以下の記事で詳しく紹介していますので、合わせて参考にしてみてください。
キーボードで「$」マークを入力
もう１つの絶対参照の設定方法は、直接キー入力で「$」マークを挿入する方法です。
ひらがなで「どる」と入力してから変換するか、「Shift」キーを押しながら「4」キーを押して「$」を直接入力します。
この際、必ずしも半角である必要はありません。エクセルが自動で全角を半角に変更してくれます。
このように、直接「$」マークを付けることが出来ますが、この方法はお勧めしません。極力「F4」キーを使うよう心がけましょう。
4つの参照方法の具体的な使い方(実例)
数式内のセル番号の前に「$」マークが付いていると絶対参照で、付いていないものが相対参照です。
そのため、組み合わせによってセルの参照方法には、以下のとおり４種類存在します。
| 番号 | 参照の種類 | セルの表示 | 「F4」キーを押す回数 |
| １ | 相対参照 | A1 | 0回、4回 |
| ２ | 「行」と「列」の両方を絶対参照 | $A$1 | 1回 |
| ３ | 「行」のみ絶対参照 | A$1 | 2回 |
| ４ | 「列」のみ絶対参照 | $A1 | 3回 |
この４つの参照方法の使い方を実例と合わせながら紹介します。
「行」と「列」を両方固定しない(相対参照）
まずは「行」と「列」も両方固定しない「相対参照」について紹介します。
相対参照のポイント
1．「F4」キーの押す回数：0回、もしくは4回
2．セルの表示例：「C3」
3．コピーした時の動く方向：「C4」「D3」「E7」など
例えば、下の図のD3セルに足し算を挿入し、G8セルにもコピーで利用したい場合、数式は以下のように「$」マークをつけずに設定しましょう。
=C3+B3
そうすることで、足し算をG8セルにコピペしても、参照セルも移動してくれるので、数式を修正する必要はありません。
「行」と「列」を両方固定する(絶対参照）
続いて、「行」も「列」も動きを固定する「絶対参照」について紹介します。
絶対参照のポイント
1．「F4」キーの押す回数：１回
2．セルの表示例：「$C$3」
3．コピーした時の動く方向：なし
例えば、下の図のC5セルに消費税を計算する数式を作成し、赤枠の点線のセル範囲にも利用したい場合、消費税率を入力した「C2」セルは動いてほしくありません。
そのため、Ｃ5セルの数式は以下のようにC2セルに「$」マークをつけて作成します。
=B5*$C$2
このようにC2セルを固定することで、赤枠のセルにコピペしても正しく計算することが出来ます。
コピーしたF7セルの数式を確認しても、「=E7*$C$2」となっていて、C2セルは固定されていることが分かります。
「行」だけを固定する(複合参照）
続いて、「行」の動きだけを絶対参照で固定する「複合参照」について紹介します。
絶対参照のポイント
1．「F4」キーの押す回数：２回
2．セルの表示例：「C$3」
3．コピーした時の動く方向：「A$3」「E$3」「F$3」
例えば、下の図のC5セルに消費税を計算する数式を作成し、赤枠の点線の真下のセル範囲にも利用したい場合、消費税率を入力した「C2」セルは上下の方向には動いてほしくありません。
そのため、Ｃ5セルの数式は以下のようにC2セルの行番号の前にだけ「$」マークをつけて作成します。
=B5*C$2
このようにC2セルの縦方向の動きを固定することで、赤枠のセルにコピペしても正しく計算することが出来ます。
コピーしたF7セルの数式を確認しても、「=E6*$B3」となっていて、参照セルB3は横方向に動いていません。
このように「行」だけ絶対参照にすることで、コピーした際に参照セルの上下の方向の動きを禁止することが出来ます。
「列」だけを固定する(複合参照）
続いて、「列」の動きだけを絶対参照で固定する「複合参照」について紹介します。
絶対参照のポイント
1．「F4」キーの押す回数：３回
2．セルの表示例：「$C3」
3．コピーした時の動く方向：「$C1」「$C5」「$C8」
例えば、下の図のC7セルに消費税を計算する数式を作成し、赤枠の点線の真横のセル範囲にも利用したい場合、消費税率を入力した「B3」セルは左右の方向には動いてほしくありません。
そのため、Ｃ7セルの数式は以下のようにB3セルの列番号の前にだけ「$」マークをつけて作成します。
=C6*$B3
このようにB3セルの横方向の動きを固定することで、赤枠のセルにコピペしても正しく計算することが出来ます。
コピーしたF7セルの数式を確認しても、「=E6*$B3」となっていて、参照セルB3は横方向に動いていません。
このように「列」だけ絶対参照にすることで、コピーした際に参照セルの左右の方向の動きを禁止することが出来ます。
絶対参照と相対参照、複合参照を正しく使い分ける方法
数式や関数を利用する際に「絶対参照」「相対参照」「複合参照」のどれを利用するか、どのように判断すればいいのでしょうか。
上の見出しでも触れましたが、考え方はシンプルです。
その関数をコピぺして利用する可能性があるか、またコピペした際に参照セルが移動して欲しいか、欲しくないかで判断してください。
参照方法を決める３つのポイント
その１．別のセルにコピペして利用するか。しなければ参照方法は気にしない。
その２．コピペした際に、参照セルが移動して欲しくないか
その３．移動して欲しくない場合、縦方向、横方向どちらの移動を固定にするか
あらためて、「相対参照」「絶対参照」「複合参照」がコピペした際にどのように動くか下の図で再確認しておきましょう。
「C5」といった「相対参照」はコピぺすると相対的な位置に移動します。移動に制限はかかっていません。
「$C$5」といった「絶対参照」はコピぺしても絶対的な位置は変わりません。つまりすべての移動に制限がかかっている(移動出来ない）ということです。
「$C5」といった「複合参照」は、コピペした際、横方向の移動が固定されています。
「C$5」といった「複合参照」は、コピペした際、縦方向の移動が固定されています。
絶対参照を設定するテクニックと注意点
参照方法を切り替える上で、知っておくと得するテクニックと注意点を１つずつ紹介します。
参照セルを指定するテクニック
数式を利用する際に、「参照セル」を設定する必要があります。
たとえば、下の図のように、B列に入力された値をD3セルで合計したい場合は、「＝SUM(B2:B7)」と入力します。
このB列に挿入した値リストがB8セル、B9セルといったように、下方向にセル範囲が広がる場合は、「＝SUM(B2:B7)」の数式を度々修正する必要があります。
そのため、セル範囲が広がることを想定して、数式を作成しておいた方が便利です。
この場合、下の図のように関数を「＝SUM(」まで作成した直後に、B列の列番号をクリックします。
そうすると、SUM関数は、「＝SUM(B:B)」と設定することが出来ます。
こうすることで、B列に新しい値を追加してもセル範囲をわざわざ修正する必要はありません。
また、セル範囲が「B:B」と設定したことにより、行番号の設定は不要なので、関数の参照方法に頭を悩ませる回数を減らすことが出来ます。
この方法は、行番号に対しても可能です。下の図のように、行番号をクリックすることで、「＝SUM(2:2)」と設定することも出来ます。
「F4」キーを利用する際の注意点
参照方法を切り替える「F4」キーを押すタイミングには注意が必要です。
たとえば、下の図のようにSUM関数を挿入してセル範囲を選択した直後に「F4」キーを押します。
そうすると、「=SUM(B2:B7」は、「=SUM($B$2:$B$7」と変わります。
このように、セルやセル範囲を選択した直後に「F4」キーを押すことが大切です。
もし、数式内をクリックしたり、関数を括弧「)」で閉じるなどした後で、「F4」キーを押すと、「=SUM(B2:$B$7」となり、先頭のセル範囲が絶対参照に切り替わりません。
このように「F4」キーを利用する時は、セルやセル範囲を選択した直後に押すよう注意が必要です
参照セルを確認する方法
先述のとおり、数式や関数を作成する際、参照方法を決めるのは大切なポイントです。
しかし、数式を作成した後で、もう一度参照方法を確認するために、数式バーを１セルずつ確認していては時間がかかります。
この見出しでは、数式を作成した後に数式を確認する便利な方法を３つ紹介します。
全てのセルの「数式を表示」する
エクセルには、ワークシートのすべての数式を一時的に表示することが可能です。
「数式」タブの「数式の表示」をクリックします。
そうすると、ワークシートのすべての数式が一時的に表示されます。
数式を確認し元に戻したい場合は、もう一度「数式の表示」をクリックします。
「参照元のトレース」を利用する
２つ目は、より視覚的に数式の参照セルを確認する方法です。
数式の参照元を確認したいセルをクリックしてから、「数式」タブの「参照元のトレース」をクリックします。
そうすると、参照元のセルと選択したセルが青色の矢印線で繋がり、視覚的にどのセルと関連しているかを確認しやすくなります。
このトレースの青線を消したい場合は、「数式」タブの「トレース矢印」の削除を押してください。
「参照先のトレース」を利用する
３つ目は、参照先のセルを視覚的にトレース線で確認する方法です。
参照先のセルを確認したいセルをクリックしてから、「数式」タブの「参照先のトレース」をクリックします。
そうすると、選択したセルが参照されているセルと青色の矢印線で繋がり、視覚的にどのセルと関連しているかを確認しやすくなります。
このトレースの青線を消したい場合は、「数式」タブの「トレース矢印」の削除を押してください。
循環参照エラーとは
今回は「絶対参照」「相対参照」「複合参照」の３つを紹介しましたが、「循環参照」というものも存在します。
この「循環参照」というのは数式で参照先を誤った時に発生するエラーです。
例えば、下のB3セルに「＝D3」という数式を挿入します。
次に、D3セルに「＝B3」という数式を挿入して、お互いのセルが参照し合うよう設定します。
「Enter」キーで数式を確定すると、「１つ以上の循環参照が発生しています。」というエラーが表示されます。
「OK」でエラー画面を閉じることが出来ますが、循環参照エラーは発生したままとなります。
このエラーはエクセルファイルを開く度に表示されエラーを消すよう注意が促されます。循環参照セルを確認するためには、「数式」タブの「エラーチェック」の「循環参照」をクリックして対象のセルを確認します。
循環参照エラーが発生しているセルには、以下のように、青色の線が表示されます。
