エクセルで計算式や関数を入力したセルをコピペすると、エラーになったり計算結果がズレてしまった経験はありませんか？
これは参照しているセルが固定されていないため、コピペやオートフィルを利用時に参照元のセルがズレてしまうことが原因です。
そこでこの記事ではExcel(エクセル)で計算式や関数で参照するセルを固定する方法について、詳しく解説していきたいと思います。
エクセルで数式をコピペすると参照セルがずれる
こちらの表は商品別に税込価格を表示するための簡単な計算シートになります。
消費税10%という値はF3のセルに入力されているため、商品Aの税込価格の欄には=C3*(1+F3)という計算式を入力します。
すると商品Aの税込価格を表示することができました。
商品Aの税込価格は正しく計算することができたので、この計算式をオートフィル機能を使って商品B以降にコピペしていきます。
コピペした商品B～商品Eの税込価格をみてみると、明らかに正解と異なる金額が表示されています。
実はこれはセル参照の設定ミスが原因によるもので、エクセル初心者にあるあるの間違いでもあります。
ではなぜこのような結果になるのか、まずはそのカラクリをしっかりと理解してみましょう。
キーボードのShift + Ctrl + @を同時に押してみましょう。
するとセル上に計算結果ではなく、入力している計算式が表示されるようになります。
計算式を確認してみると税金の率を参照していたF3が、1行づつずれていることがが分かります。
この貼り付けたセルの位置に合わせて参照セルがずれるのはExcelの基本仕様であり、参照セルを固定するには数式にひと手間を付け加える必要があります。
エクセルで不可欠な3つの参照形式
ではここからは、エクセルを使う上で絶対に覚えておくべきセル参照のルールについて解説をしていきます。
エクエルでセルを参照する方法として、「相対参照」「絶対参照」「複合参照」の3つの参照形式があります。
名前を覚える必要はありませんが、それぞれの仕組みや違いを理解することはエクセルを使う上では不可欠です。
一度理解してしまえば小難しい名前の割にはシンプルな仕組みなので、なるべく分かりやすく噛み砕いて解説していきたいと思います。
相対参照とは
相対参照とはエクエルの一番基本となる形式で、参照するセルが入力するセルの箇所に応じて変動するタイプの参照形式です。
分かりやすいように具体例で解説しますね。
例えば下記のような表で、セルB2に=F2と入力してみます。
するとB2のセルにはF2のセルと同じ、「1」が表示されました。
続いてB2に入力した数式をCtrl + Cでコピーして、一つ下のB3のセルにCtrl + Vで貼りつけます。
すると貼り付けたセルの位置に連動して、参照するセルも一つ下の「F3」に自動的に切り替わっています。
続いて先ほどと同じようにB2の数式をCtrl + Cでコピーして、今度は一つ右のC2のセルにCtrl + Vで貼りつけます。
するとこちらも貼り付けたセルの位置に連動して、参照するセルが一つ右の「G2」のセルに自動的に切り替わっています。
上記のような相対参照のセルの動きを、ビジュアルで表現するとこんな感じてす。
コピーするセルを基準に貼り付けたセルの位置に連動しながら参照セルを変動させたいときは、上記の相対参照を使うようにしましょう。
絶対参照とは
絶対参照とは相対参照とは逆の形式で、参照するセルを完全に固定するタイプの参照形式です。
先ほどと同じ下記のような表で、セルB2に=$F$2と入力してみます。
するとB2のセルにはF2のセルと同じ、「1」が表示されました。
続いてB2に入力した数式をCtrl + Cでコピーして、一つ下のB3のセルにCtrl + Vで貼りつけます。
すると相対参照のようにコピペ先の位置と連動することなく、コピーしたセルと同じ「F2」が参照されています。
続いてB2の数式をCtrl + Cでコピーして、今度は一つ右のC2のセルにCtrl + Vで貼りつけます。
こちらも絶対参照によってセルは完全に固定されているため、コピーしたセルと同じ「F2」が参照されています。
絶対参照によるセルの動きをビジュアルで表すと、こんな感じになります。
絶対参照の場合はセルが完全に固定されるため、どのセルにコピペしても元々のセルで参照していたセルが貼り付けられます。
参照するセルを完全に固定したい場合は、上記の絶対参照を使うようにしましょう。
複合参照とは
複合参照とは相対参照と絶対参照を組み合わせたもので、行または列のどちらかだけを固定するタイプの参照形式です。
今回は行のみを固定したケースで、具体例で解説していきます。
先ほどと同じ下記の表で、セルB2に=F$2と入力してみます。
するとB2のセルにはF2のセルと同じ、「1」が表示されました。
続いてB2に入力した数式をCtrl + Cでコピーして、一つ下のB3のセルにCtrl + Vで貼りつけます。
すると相対参照のようにコピペ先の位置と連動することなく、コピーしたセルと同じ「F2」が参照されています。
続いてB2の数式をCtrl + Cでコピーして、今度は一つ右のC2のセルにCtrl + Vで貼りつけます。
こちらも絶対参照によってセルは完全に固定されているため、コピーしたセルと同じ「F2」が参照されています。
複合参照で行のみを固定した場合、参照セルの動きを表すとこんな感じになります。
参照しているF2セルの行のみ固定しているため、横方向には連動して変動しますが、縦方向は固定されたままとなっています。
一方で列のみを固定した場合、参照セルの動きを表すとこんな感じになります。
参照しているF2セルの列のみ固定しているため、縦方向は連動して変動していますが、横方向は固定されたままとなっています。
参照したいセルか一列または一行に並んでいるときは、上記の複合参照を使うようにしましょう。
エクセルで＄でセル参照を固定する方法
エクセルで絶対参照・複合参照の方法
エクセルでセルを固定する＄マークを入力する方法
エクセルでセル参照を固定する方法を実例で解説
最後にこれまで解説してきた内容の復習がてら、実務で使うことを想定しながら相対参照・絶対参照・複合参照の使い方を解説していきます。
相対参照で参照セルを連動する方法
まずはエクセルの基本でもある相対参照の使い方を、実例とセットでご紹介していきます。
例えばこちらの表で示しているように、E列に売上Aと売上Bの合計額を入力するとします。
2015年度の合計金額は、E3のセルに=C3＋D3という計算式をE列に入力することで計算することができます。
このE3のセルに入力した計算式をCtrl + Cでコピーして、他のセルにCtrl + Vでペーストしてみます。
キーボードのShift + Ctrl + @を同時に押してコピペした計算式を表示してみると、貼り付けたセルの場所に合わせて参照セルがずれているのが分かります。
絶対参照で参照セルを固定する方法
続いて参照セルを完全に固定する絶対参照の使い方について、実例とセットでご紹介していきます。
例えばこちらの表に示しているように、商品別にクーポン券を使った際の支払い金額を入力するとします。
すべての商品でF3の値引き額を参照したいので、まずはD3のセルには=C3-$F$3という計算式を入力します。
D3のセルに入力した計算式をCtrl + Cでコピーして、他のセルにCtrl + Vでペーストしてみます。
絶対参照のため値引き額はF3に固定された状態となっており、すべての商品で正しく計算することができました。
キーボードのShift + Ctrl + @を同時に押してコピペした計算式を表示してみると、F3のセルに完全に固定されて参照されているのが分かるかと思います。
複合参照でセルを固定する方法
最後に参照セルの行または列のみを固定する複合参照の使い方について、実例とセットでご紹介していきます。
例えばこちらの表のように、クーポン1と2を使ったときの支払い額それぞれを計算したいとします。
まずは商品Aでクーポン1を使ったときの支払い額を計算するため、D3のセルに=$C3-G$3という計算式を入力して定価から値引き額を差し引きます。
C列の定価のセル参照については、固定したいので$C3と入力しします。
G列・E列のクーポン額のセル参照については、行を固定したいのでG$3と入力します。
続いてD3のセルに入力した計算式をCtrl + Cでコピーして、他のセルにCtrl + Vでペーストしてみます。
キーボードのShift + Ctrl + @を同時に押してコピペした計算式を表示してみると、クーポン1とクーポン2がそれぞれ正しく参照されているのが分かるかと思います。
最後に
この記事ではエクセルで関数や計算式で参照するセルを固定する方法として、絶対参照と複合参照のルールと使い方をご紹介しました。
セル参照のルールや使い方はエクセルを使う上で絶対に覚えておくべき必須知識なので、ぜひこの記事を読み返してマスターしてみてください。