【Excelの基本】思いのままにオートフィル（自動入力）相対参照／絶対参照／複合参照：Tech TIPS

Excelで他のセルの値を参照して計算する際、単に参照先のセルを選択しただけでは、オートフィルで数式をコピーした際に値がおかしくなってしまうことがある。例えば、消費税などを特定のセルに入力しておいて、常にそこを参照先とするような場合に発生しがちだ。このような場合、数式をコピーしても、参照先のセルが移動しないようにする必要がある。このようなセルの参照方法について整理してみた。

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2016／2019／2021／365

Excelのセルの相対参照／絶対参照／複合参照を理解する
Excelで数式の中でセルを参照する際、「相対参照」が使われることが多い。「相対参照」では、数式をコピーすると、その位置に合わせて参照しているセルの位置も移動する。しかし、場合によっては参照するセルの位置が移動してしまうと、計算がおかしくなることもある。このような場合、「絶対参照」や「複合参照」を利用するとよい。その使い方を解説しよう。

　「Microsoft Excel（エクセル）」で他のセルの値を参照して計算するのはよくあることだ。オートフィルを使えば、他のセルを参照した式をドラッグするだけで、コピーして表を埋めることができる。

　しかし、セルの参照先によっては、オートフィルでコピーすると値がおかしくなったり、セルにエラーが表示されたりしてしまう。消費税率を表の外側のセルに入力しておき、それをセル参照したら、オートフィルでコピーしたセルの値に消費税が加算されなかった、という経験はないだろうか。

　本Tech TIPSでは、Excelの基本的なテクニックであるセルの相対参照／絶対参照／複合参照について整理する。

参照位置を自動で動かしたいなら「相対参照」

　Excelでは、「相対参照」がデフォルトのため、数式の中でセル参照を行う際に多くの場合「相対参照」を使っているはずだ。「相対参照」は、数式を入力したセルをコピーすると、ペースト先のセルに合わせて自動的に参照先となるセルの位置が変更される参照方法である。

「相対参照」とは（1）
セルを参照する数式を入力する。

▼

「相対参照」とは（2）
そのセルを別のセルにコピーすると、セル内の数式で参照していたセルも、移動した位置に合わせて動いてしまう。

　例えば、下の画面のように行ごとに単価と個数を掛けて、小計を計算するような場合に使う。「D2」セルに「=B2*C2」と入力し、これをオートフィルで下の行にコピーすると、自動的に参照するセルの位置が変更されて、数式が「=B3*C3」「=B4*C4」といった具合になり、各行の小計が計算できる。

相対参照している数式をオートフィルでコピーする（1）
セルを相対参照している数式「=B2*C2」を入力する。

▼

相対参照している数式をオートフィルでコピーする（2）
数式を列方向にオートフィルでコピーすると、行の位置に合わせてセルの参照先が自動的に変わる。このようなケースでは「相対参照」であっても計算がおかしくなることはない。

特定のセルを参照したいなら「絶対参照」

　特定のセルに入力し、それをセル参照する場合、参照先のセルをマウスで選択しただけでは「相対参照」となってしまう。その数式をオートフィルでコピーすると、参照先のセルも移動するため、正しい計算結果にならない。

特定のセルを相対参照するとおかしなことになる（1）
消費税率などを特定のセルに入力しておき、常にそのセルを参照したい場合、「相対参照」でセル参照を行ってしまうと、入力したセルの計算結果は正しいが、それを別のセルにコピー＆ペーストするとおかしなことになる。

▼

特定のセルを相対参照するとおかしなことになる（2）
オートフィルで数式をコピーすると、参照先のセルの位置も移動してしまい、計算結果がおかしくなってしまう。

　このような固定したセルを参照する場合、「絶対参照」を利用する。参照先のセルをマウスで選択したら、［F4］キーを1回入力するか、行番号と列番号の前に「$（ダラー記号）」を入力する（「$H$2」のようにする）。これで、オートフィルでコピーしても、「絶対参照」とした参照先のセルは固定された状態となる。

　消費税率などを特定のセルに記入して、そこを参照する場合には、「絶対参照」を使い、オートフィルで数式をコピーした場合でも参照先のセルが移動しないようにすればよい。

特定のセルを参照したいなら「絶対参照」を使う（1）
数式で特定のセルを参照したいのであれば、「絶対参照」にすればよい。参照先のセルを選択したら、［F4］キーを1回押すと、セル番号の行と列の前に「$」が付く。

▼

特定のセルを参照したいなら「絶対参照」を使う（2）
オートフィルで数式をコピーしても、「絶対参照」しているセルは移動しない。一方、「相対参照」しているセルは移動した位置に合わせて参照するセルの位置も変わる。

▼

特定のセルを参照したいなら「絶対参照」を使う（3）
「絶対参照」しているセルは、数式をコピーしても移動しないので、異なるセルを「絶対参照」したい場合は、数式をコピーした後に参照先を変更する必要がある。

行または列のみ固定するなら「複合参照」

　下画面のように、ある商品に対して複数の値引き後の価格を計算したい場合、「相対参照」にしてオートフィルでコピーすると、2つ目の商品から計算結果がおかしくなってしまう。

「相対参照」がある数式をコピーするとおかしな計算結果になる
「相対参照」で「C1」セルに入力した値引き率をセル参照していると、オートフィルでコピーした計算結果がおかしな値になってしまう。

　値引き率（「C1」セル）を「絶対参照」とすることで、C列は正しく計算できる。

「C1」セルを「絶対参照」で指定すると正しい計算結果になる
そこで、「C1」セルを「絶対参照」すると、C列は正しい計算結果になる。

　しかし、この数式をD列やE列にオートフィルを使ってコピーすると、商品価格が入力されたセルとは異なるセルが参照されてしまう上、値引き率が「C1」セルに入力した値に固定されているため、全く異なる計算結果になってしまう。

「C2」セルを「D2」セルや「E2」セルにコピーすると……
「C2」セルを「D2」セルや「E2」セルにコピーすると、計算結果がおかしくなってしまう。「D」列や「E」列では、それぞれ「B」列を「相対参照」して、該当する値引き率のセル（「D1」セルや「E1」セル）を「絶対参照」することになるが、少々面倒だ。

　このような場合、商品価格が入力された列のみを固定（「絶対参照」）し、行は「相対参照」とする「複合参照」を利用するとよい。オートフィルでコピーすると、列番号は固定され、コピーした行に合わせて行番号は移動するようになる。

　「複合参照」を指定するには、行と列で固定したい方の前に「$」を付け、移動したい方には「$」を付けなければよい。セルを選択した際に、［F4］キーを2回押すと行番号が固定、3回押すと列番号が固定になる。

「複合参照」を使って参照する列や行を固定する（1）
このような場合、参照する列や行が固定できる「複合参照」を利用するとよい。「$B2」などとセル参照すると、参照元のセルの列が移動しても、常に「B」列が参照される。同様に「C$1」とすれば、常に「1」行が参照されるので、参照元のセルの行が移動しても列の先頭行に入力された値が参照されることになる。

▼

「複合参照」を使って参照する列や行を固定する（2）
参照元のセルの列や行が移動しても、固定した列や行が参照されるため、正しい計算結果が得られる。