数式を下までコピーしたら、参照するセルまでずれてしまいました……。同じセルを使い続ける方法はありませんか？
Excelの数式で参照するセルは、コピー先に合わせて自動的に変わります。
同じセルを使い続けたい場合は、セル参照に「$」を付けて、行や列を固定しましょう。
数式をコピーしたときに行と列が変わる方法を「相対参照」、行と列の両方を固定する方法を「絶対参照」、どちらか一方だけを固定する方法を「複合参照」といいます。
「＄」を使って参照セルの固定を設定しましょう。
この記事では、相対参照・絶対参照・複合参照の違いと、「$」の付け方を解説します。
数式をコピーするときに、参照先を変えるセルと固定するセルを判断できるようになりましょう。
数式をコピーする方向に合わせて、相対参照・絶対参照・複合参照を使い分けられるようになる
絶対参照・相対参照・複合参照の違い
相対参照・絶対参照・複合参照は、数式をコピーしたときに、参照するセルの行や列を変えるか固定するかが異なります。
「$」が付いていない行や列はコピー先に合わせて変わり、「$」が付いている行や列は固定されます。
A1：相対参照
行と列の両方が変わる
$A$1：絶対参照
行と列の両方を固定する
$A1：複合参照
列を固定し、行は変わる
A$1：複合参照
列は変わり、行を固定する
「$」は、直後にある列または行を固定する記号です。
たとえば「$A1」はA列を固定し、「A$1」は1行目を固定します。「$」を付ける位置を確認すれば、どの行や列が固定されるか判断できます。
「$」が付いている方を固定すると考えれば、見分けやすそうですね！
セル参照は数式で使用するセルの場所を表す
セル参照とは、数式で使用するセルの場所を列番号と行番号で指定することです。
たとえば、A列と2行目が交わるセルは「A2」、B列と2行目が交わるセルは「B2」と表します。
=A2*B2この数式は、セルA2の値とセルB2の値を掛けています。
数式でセル参照を使用すると、参照先の値を修正したときに計算結果も自動で更新されます。
「=100*5」のように数値を直接入力すると、元の数量や単価が変わるたびに数式を修正する必要があります。
「=A2*B2」のようにセルを参照すれば、A2やB2の値を修正するだけで計算結果が更新されます。
セル参照を使った数式を別のセルへコピーすると、通常はコピー先に合わせて参照する行や列も変わります。
次は、参照先が自動的に変わる「相対参照」の動きを確認しましょう。
相対参照は数式のコピー先に合わせて変わる
相対参照は、数式をコピーした方向や距離に合わせて、参照するセルが自動的に変わる方法です。
数式へ入力したセル参照に「$」が付いていない場合は、相対参照として扱われます。
数式を効率よくコピーする方法は、「Excelのオートフィルとは？連番・日付・数式を自動入力する方法」で詳しく解説しています。オートフィルの操作に不安がある場合は、先に確認してください。
数式を下方向へコピーすると行番号が変わる
商品ごとの金額を求めるため、セルC2に次の数式を入力します。
コピー前の数式
=A2*B2セルC2では、同じ行にあるA2の数量とB2の単価を掛けています。
セルC2の数式をC3へコピーすると、参照する行番号もコピー先に合わせて変わります。
コピー後の数式
=A3*B3数式を1行下へコピーしたため、A2はA3に、B2はB3に変わりました。
さらにC4までコピーすると、数式は「=A4*B4」に変わります。このように、相対参照を使えば、商品ごとに数式を入力し直す必要はありません。
数式を右方向へコピーすると列が変わる
相対参照、数式を下方向へコピーした場合だけでなく、右方向や左方向へコピーした場合も変わります。
たとえば、セルB3に「=B2」と入力し、数式をセルC3へコピーすると、参照先はC2に変わります。
コピー前の数式
=B2
コピー後の数式
=C2数式を1つ右へコピーしたため、参照先のB列も1列右のC列へ移動しました。
相対参照では、数式をコピーした方向に合わせて、行番号だけでなく列も変わります。
行ごとの数量と単価を掛けるなど、数式のコピー先に合わせて参照するセルも変えたい場合は相対参照を使います。
相対参照は、行ごとや列ごとに異なるセルを使って計算する場合に便利です。
一方、税率や割引率のように、数式をコピーしても同じセルを使い続けたい場合は、参照先を固定する必要があります。
絶対参照は数式をコピーしても参照先を固定する
絶対参照は、数式をコピーしても参照するセルを変えない方法です。
税率や割引率のように、すべての計算で同じセルを使い続ける場合は、セルの列と行を絶対参照で固定します。
相対参照のままコピーすると参照先も変わる
商品ごとの税込金額を求めるため、B列の商品金額に、セルF2の税率を掛けます。
セルC2に、次の数式を入力します。
コピー前の数式
=B2*F2セルC2では、B2の商品金額にF2の税率を掛けています。
しかし、この数式をC3へコピーすると、参照先は「B3」と「F3」に変わります。
コピー後の数式
=B3*F3商品金額は次の行に合わせてB2からB3へ変わってよい参照先です。
一方、税率はF2にだけ入力されているため、F3へ変わると正しく計算できません。商品金額の参照先は変え、税率の参照先だけを固定する必要があります。
固定する列と行の前に「$」を付ける
税率が入力されたF2を固定する場合は、列を表すFと行を表す2の前に、それぞれ「$」を付けます。
絶対参照へ変更した数式
=B2*$F$2「$F$2」では、「$F」でF列を固定し、「$2」で2行目を固定しています。
列と行の両方を固定しているため、数式をどの方向へコピーしても、税率の参照先はF2から変わりません。
絶対参照を使った数式をコピーする
セルC2に入力した数式をC3へコピーすると、数式は次のように変わります。
コピー前の数式
=B2*$F$2
コピー後の数式
=B3*$F$2商品金額を参照するB2は、コピー先に合わせてB3へ変わりました。
絶対参照にした「$F$2」は、コピー後も変わりません。これにより、すべての商品金額へ同じ税率を掛けられます。
税率や割引率など、複数の数式で同じセルを使い続ける場合は、列と行の両方に「$」を付けて絶対参照にします。
変えたい商品金額は相対参照のままにして、同じ税率を使い続けるF2だけを絶対参照にするんですね！
絶対参照は、行と列の両方を固定する場合に使います。
数式を縦方向と横方向へコピーする表では、行または列の片方だけを固定する「複合参照」が必要になる場合があります。
複合参照は行または列の片方だけを固定する
複合参照は、列または行のどちらか一方だけを固定する方法です。
数式を縦方向と横方向の両方へコピーするときに、特定の列または行だけを使い続けたい場合に使用します。
「$A1」は列だけを固定する
「$A1」は、列を表すAの前に「$」が付いているため、A列だけを固定する複合参照です。
行を表す1には「$」が付いていないため、数式を下方向へコピーすると行番号が変わります。
$A1「$A1」を1行下へコピーすると、「$A2」に変わります。
A列は固定されたままですが、行番号はコピー先に合わせて1から2へ変わります。
コピー後の参照
$A2「A」は行だけを固定する
「A$1」は、行を表す1の前に「$」が付いているため、1行目だけを固定する複合参照です。
列を表すAには「$」が付いていないため、数式を右方向へコピーすると列が変わります。
A$1「A$1」を1列右へコピーすると、「B$1」に変わります。
1行目は固定されたままですが、列はコピー先に合わせてA列からB列へ変わります。
コピー後の参照
B$1$A1
A列を固定し、行番号はコピー先に合わせて変える
A$1
1行目を固定し、列はコピー先に合わせて変える
複合参照を使って縦横へ数式をコピーする
複合参照は、縦に並んだ金額と横に並んだ割合を掛けるような表で役立ちます。
A列に商品金額、1行目に10％・20％・30％の割合が入力されているとします。表の各セルで金額と割合を掛けるには、次の数式を入力します。
=$A2*B$1「$A2」は、商品金額が入力されているA列を固定しています。
数式を右方向へコピーしても商品金額の参照先はA列のままです。下方向へコピーすると、商品の行に合わせてA2からA3、A4へ変わります。
「B$1」は、割合が入力されている1行目を固定しています。
数式を下方向へコピーしても割合の参照先は1行目のままです。右方向へコピーすると、割合の列に合わせてB列からC列、D列へ変わります。
数式を1列右へコピーすると、次の数式に変わります。
=$A2*C$1商品金額を参照するA列は固定されたままですが、割合の列はB列からC列へ変わりました。
元の数式を1行下へコピーすると、次の数式に変わります。
=$A3*B$1商品金額の行は2行目から3行目へ変わりましたが、割合を参照する1行目は固定されたままです。
「$A1」と「A$1」は、固定する場所が異なります。
列を固定する場合は列を表すアルファベットの前、行を固定する場合は行番号の前に「$」を付けてください。
縦横をコピーするときは、動かしたくない列や行だけに「$」を付ければよいんですね！
相対参照・絶対参照・複合参照は、「$」を入力する位置によって切り替えられます。
「$」をキーボードから直接入力することもできますが、F4キーを使うと参照方法を簡単に切り替えられます。
F4キーで参照方法を切り替える
数式内のセル参照を選択してF4キーを押すと、相対参照・絶対参照・複合参照を切り替えられます。
「$」を1つずつ入力する必要がないため、参照方法をすばやく変更できます。
数式内のセル参照を選択してF4キーを押す
セルF2を絶対参照へ切り替える場合は、次の手順で操作します。
- 数式を入力するセルを選択する
- 数式内の「F2」をクリックするか、文字の間へカーソルを置く
- F4キーを1回押す
- 「F2」が「$F$2」に変わったことを確認する
- Enterキーを押して数式を確定する
操作前の数式
=B2*F2
操作後の数式
=B2*$F$2F4キーを1回押すと、F列と2行目の両方に「$」が付き、F2が絶対参照へ変わります。
数式を確定する前に、「$F$2」と表示されていることを確認してください。
キーボードの設定はパソコンによって異なります。
FnキーとF4キーを同時に押しても切り替わらない場合は、数式へ「$」を直接入力してください。
F4キーを押すたびに「$」の位置が変わる
F4キーを繰り返し押すと、「$」が付く位置が切り替わります。
セルA1を選択した状態では、次の順番で参照方法が変わります。
F4キーを押す前
→A1：相対参照
F4キーを1回押す
→$A$1：列と行を固定する絶対参照
F4キーを2回押す
→A$1：行だけを固定する複合参照
F4キーを3回押す
→$A1：列だけを固定する複合参照
F4キーを4回押す
→A1：相対参照に戻る
F4キーを押す回数は、最初に選択した参照方法によって変わる場合があります。
押した回数だけで判断せず、数式に表示された「$」の位置を確認しましょう。
複数のセル参照がある場合は固定する参照を選ぶ
1つの数式に複数のセル参照がある場合、F4キーで切り替わるのは、カーソルを置いたセル参照です。
=B2*F2この数式で税率のF2だけを固定する場合は、数式内のF2を選択してからF4キーを押します。
B2まで絶対参照にすると、数式を下方向へコピーしても商品金額の参照先が変わりません。固定する必要があるセル参照だけを切り替えてください。
絶対参照は、数式内のすべてのセルへ指定するものではありません。
数式をコピーしたときに変えたい参照は相対参照のままにし、変えたくない参照だけを絶対参照または複合参照にします。
F4キーを使えば、数式内のセル参照を簡単に切り替えられます。
ただし、参照方法を正しく選ぶには、数式をどの方向へコピーし、どの行や列を固定したいかを先に考える必要があります。
参照方法は数式をコピーする方向から選ぶ
相対参照・絶対参照・複合参照のどれを使うかは、数式をコピーする方向と、参照先を変えたいかによって判断します。
数式を作る前に、参照するセルごとに「コピー先に合わせて変えるか」「同じ行や列を使い続けるか」を考えましょう。
- 数式をコピーする方向を確認する
- コピー先に合わせて変えるセルを確認する
- 同じ行や列を使い続けるセルを確認する
- 固定する行や列の前に「$」を付ける
- 数式を1つコピーして参照先を確認する
下方向だけにコピーする場合
数式を下方向だけにコピーする場合は、参照する行番号を変えるか固定するかを確認します。
商品ごとの数量や金額など、行ごとに参照先を変えるセルは相対参照のままにします。
税率のように、どの行でも同じセルを使う場合は、絶対参照で行と列を固定します。
=B2*$F$2この数式では、商品金額を参照するB2はコピー先の行に合わせて変わり、税率を参照するF2は固定されます。
縦方向と横方向へコピーする場合
数式を縦方向と横方向の両方へコピーする場合は、列と行を別々に考えます。
固定したい列には列を表すアルファベットの前、固定したい行には行番号の前に「$」を付けます。
=$A2*B$1この数式では、商品金額が入力されたA列と、割合が入力された1行目を固定しています。
右方向へコピーすると割合の列が変わり、下方向へコピーすると商品金額の行が変わります。
迷ったらコピー後の数式を確認する
参照方法を選んだら、数式を1つだけコピーして、参照先が想定どおりに変わったか確認しましょう。
下方向へコピーした場合は行番号、右方向へコピーした場合は列を確認します。
最初から表全体へ数式をコピーするよりも、隣のセルで正しく変化することを確認してから広げる方が、参照ミスを見つけやすくなります。
まとめ｜動かしたくない行や列に「$」を付けよう
相対参照・絶対参照・複合参照は、数式をコピーしたときに、参照する行や列を変えるか固定するかが異なります。
数式をコピーする方向を確認し、動かしたくない行や列の前に「$」を付けましょう。
行と列の両方をコピー先に合わせて変える
→A1：相対参照
行と列の両方を固定する
→$A$1：絶対参照
列だけを固定し、行はコピー先に合わせて変える
→$A1：複合参照
行だけを固定し、列はコピー先に合わせて変える
→A$1：複合参照
数式を1つコピーし、変える予定だった参照先だけが変わっていれば、参照方法を正しく指定できています。
想定していない行や列まで変わった場合は、数式内の「$」の位置を確認してください。
「$」の位置を暗記するだけでなく、数式をコピーする方向から固定する場所を判断できるようになりました！
絶対参照や複合参照は、関数をオートフィルするときにも使用します。
Level3では、関数の基本から学び、目的に応じて計算・集計・判定・検索ができる表を作成します。関数を順番に学びたい場合は、Level3攻略フローから進めてください。
コメント