関数やExcelの機能を使用する際に「数式は間違っていないのにエラーになる」という場合、ほとんどの場合、この「相対参照」・「絶対参照」・「複合参照」がきちんと設定されていないことが原因です。関数を覚えるにしても、Excel機能を覚えるにしても、この 「相対参照」・「絶対参照」・「複合参照」 を理解していないと上手くいかないので、この機会にマスターしましょう！
練習用Excelファイルを用意したので、必要な方は下記よりダウンロードしてくださいね。
相対参照
参照先の列・行ともに固定せず、セルの移動に合わせて列・行もして参照することを相対参照と言います。何も操作しなければ、基本的には相対参照がデフォルトになります。下記の表に入力してみましょう。
商品の単価×数量の小計をF列に表記していきます。F2セルに = D3*E3と入力した後は最下段までオートフィルで数式をコピペするので、行は動いてほしい。そして列はF列のみが対象なので列も固定する必要はないので、デフォルトの D3*E3 のまま数式完了させます。
結果が反映されたら、F3セル右下角の緑■をダブルクリックし、オートフィルで最下段までコピーします。F4セルを選択し F2 を押して数式を確認すると、相対参照にしているのできちんとD4*E4 になっているのが分かります。
このように、セル移動に合わせて参照セルも移動して欲しい時は、デフォルトの相対参照のままにしておきましょう。
=D3*E3
絶対参照
オートフィルなどで数式を入れるセルを移動させたときに、参照セルを固定したいときに使用するのが絶対参照です。数式は間違っていないのに、コピーしたら正しい結果が表示されない。エラーになる。などは、この絶対参照になっていないことが原因のケースが多いです。
絶対参照にしないとどうなるか
G列の消費税の欄に、J4に入力されている税率をかけて消費税額を表示させていきます。まずは相対参照にした場合、どのような表示になるか見てみましょう。
G3セルに = F3*J4と入力します。G3セルには正しい消費税額が表示されました。
では、G3セル右下角の緑■をダブルクリックして最下段までコピーしてみましょう。
G3セルは正しい消費税額が表示されましたが、G4より下の行は0表記となり正しい消費税額が表示されていません。
では、エラーの原因を確認するために、一度G4セルの数式を見てみましょう。G3セルには「F3*J4」と入力しましたが、G4セルには「F4*J5」と数式が入っています。
これはJ4セルを絶対参照にしていないため、オートフィルしたことで参照先も移動したことが原因です。F列はセル移動に合わせて参照先も移動して欲しいので今のままで問題ないですが、税率の参照先はJ4のままである必要がありますが、実際には参照先がJ5に移動してしまっています。J5セルには何もないっていないため、G4セルより下の行は全て0表記になってしまいました。こういうエラーを防ぐために、数式をいれているセルが移動しても参照先は移動せずに、絶対に同じセルを参照して欲しい時に絶対参照の指示を出します。
絶対参照の指示方法
G3に = を入力した後、F3*J4と入力し J4 を入力後 F4 キーを1度だけ押します。そうするとJ4の表示が「$J$4」となります。この「$」が固定の記号です。Jの前にも4の前にも「$」が表示されているということは、列も行も固定したということになります。これによりG列の数式を最下段までオートフィルでコピーしても、必ず固定したJ4セルを参照します。これを絶対参照と呼びます。後で詳しくご説明しますが、絶対参照は F4 キーを押すのは一度だけです。誤って一度以上押してしまった場合は、何度か押してみてください。F4 キーは一度押すと絶対参照、2度押すと行だけ固定の複合参照、3度押すと列だけ固定の複合参照、4度押すと相対参照、そして5度押すと絶対参照という具合にループしています。
G列に消費税額が反映されたら、G3セル右下角の緑■をダブルクリックし、最下段まで数式をコピーします。
J4を絶対参照にしているので、エラーにならず正しく表示されていますね。
では、先ほどと同じようにG4セルの数式を見てみましょう。F列はオートフィルに合わせてF3からF4に参照先も移動していますが、J4はオートフィルをしても移動せずJ4を参照しているのが分かります。
このように、オートフィルなどで数式をコピペしたときに参照先を変えたくない場合は絶対参照にて参照先を固定するのを忘れないようにしましょう。
=F3*$J$4
複合参照
オートフィルなどで数式を入れるセルを移動させたときに、参照セルの列・行のどちらかだけを固定したいときに使用するのが複合参照です。数式は間違っていないのに、コピーしたら正しい結果が表示されない。エラーになる。などは、この複合参照になっていないことが原因のケースが多いです。 下記の表を使用して、相対参照の状態でオートフィルした場合を見ていきましょう。
複合参照にしないとどうなるか
B2セルに = A2 * B1 と入力してみます。
5×1,200の計算結果 6,000が表示されました。これを全体にオートフィルしてみましょう。
エラーになりました。原因を確認するためにB3セルとC2セルの数式を見てみましょう。
まずB3セルの数式を確認します。A3とかけるセルはB2のはずですが相対参照にしているため、オートフィルしたことでセルが動き、数式が A3×B2 となり計算結果が7×6,000の42,000が表示されていることが分かります。
次にC2セルの数式を見てみましょう。A2とC1をかける式が入っているはずですが、相対参照にしているため、オートフィルしたことでセルが動き、数式が B2×C1となり計算結果が10,800,000となりセル幅に入りきらず#######と表示されていることが分かります。（セル幅を広げると10,800,000と表示されています）
このように、固定すべきところは固定し、セル移動に合わせて動かしたいところは固定を外すという指示を出す必要があります。これが複合参照です。では、複合参照を使用して同じように数式を入力しオートフィルでコピーしてみましょう。
複合参照の指示方法
まずB2セルに数式を入れていきますが、後で全体にコピペをしたいので複合参照を利用します。複合参照にすることで、セルに直接数式を入れるのはB2セルだけで良くなり、あとは全てオートフィルでコピーが可能です。
ではB2セルに数式を入れていきましょう。まず = を入力しA2と入れますが、オートフィルをするのでA列は固定したいですが2行目はセルの移動に合わせて動いてほしいです。この場合は列だけを固定します。列だけを固定するには = A2と入力した後に F4 キーを3度押して、Aの前のみに「$」を付けます。
次に * を入力後にB1を選択します。次はこの後、オートフィルに合わせてC列、D列と参照先も移動したいので、行だけ固定します。行だけを固定するには = B1と入力した後に F4 キーを2度押して、Bの前のみに「$」を付けます。
B2セルにA2×B2の計算結果である6,000が表示されました。では、オートフィルで全体に数式を反映させてみましょう。
全てのセルに数式が反映され、結果が表示されました。先ほどと同じくB3セルとC2セルの数式を見てみましょう。
まずはB3セルの数式を見てみましょう。$A3×B$1になっていますね。7×1,200の計算結果8,400が正しく表示されています。
次にC2セルの数式を見てみましょう。$A2×C$1になっていますね。5×1,800の計算結果9,000が正しく表示されています。
このように、オートフィルなどで数式をコピペしたときに、列・行のどちらかの参照先を変えたくない場合は複合参照にて、固定したい箇所（列か行）のみ参照先を固定するのを忘れないようにしましょう。
=$A2*B$1
まとめ
表などで数式を入力する際に、相対参照・絶対参照・複合参照を上手に使い分けることで、ひとつのセルに数式を入れるだけで残りはオートフィルやコピペで簡単に反映させることが可能なので、ぜひ覚えておいてくださいね。数式は間違っていないのに結果が正しく反映されない場合、まずこの相対参照・絶対参照・複合参照が正しくできているか確認してみましょう。
動画でもご紹介していますので、動画を見ながら操作を確認したい方は下記からどうぞ！
励みになります！高評価とチャンネル登録よろしくおねがいいたします。
コメント