【Excel(一般)】コピーで参照がずれてしまう時の対処法 － 絶対参照と相対参照について

こんにちは。新人エンジニアの前山です。

Excelでセルや表をコピーしたときに、計算式が崩れてしまったり、参照がずれてしまったりしたことはありますか？絶対参照、絶対参照という機能を理解すれば計算式の崩れや参照のズレを未然に防ぐことができます。本記事ではExcelの絶対参照、相対参照という機能の使い方をご紹介します。

絶対参照、相対参照とは

Excelで関数を使用する場合、直接数字を指定するのではなく、セルの番号(C3など)を指定する方法を参照と呼びます。参照には大きく分けて2つの種類があります。それが絶対参照と相対参照です。

参照例：ABS(A1)

絶対参照

絶対参照とは、表をコピーした際に、コピー先のセルに合わせて参照先を変更させない参照のことです。

消費税情報を固定して、各行の金額情報から税込み金額を計算したい。といった場合に使用します。

相対参照

相対参照とは、表をコピーした際に、コピー先のセルに合わせて参照先を変更させる参照のことです。

各行に複数ある金額情報の行ごとの合計を求めたい。といった場合に使用します。

複合参照

相対参照と絶対参照を組み合わせた、複合参照という参照も存在します。コピー時に行のみ、または列のみ絶対参照させたい場合に使用されます。

活用例

絶対参照は、以下の表のように目標前年比セル(C2)を固定して、営業A～Eの前年比セル(E6～E10)との比較を行う場合に有効です。

営業Aの目標達成列のセルを相対参照のままコピーすると、目標前年比セルの参照もずれてしまいますが、絶対参照ならズレずにコピーできます。

絶対参照の記述方法

単一セルの絶対参照

セルを指定する際に行や列を指定する英数字の左となりに”$”を記載すると、行や列を固定することができます。

記法と固定する行列の関係は以下となります。

また、記入方法としては、直接”$”を記入する方法のほかにも、F4キーを押すことで絶対参照に変換する方法もあります。

F4キーの押下回数と記法の変換法則は以下となります。4回F4キーが押下されると、相対参照に戻ります。

記述方法

絶対参照で記述したい参照にカーソルを合わせます。今回は前年比率なので赤字でC2と書いてある部分です。

上記の状態でF4キーを押下すると、C2が$C$2に変化します。これでC2セルは絶対参照されました。

あとはF6セルをF7～F10にコピーすれば、C2の参照をそのままに、E6~E10の比較がコピーされます。

以上が単一セルの絶対参照の使い方です。

範囲選択の絶対参照

C6:D10のような範囲選択の場合でも同じように絶対参照が利用できます。記述方法は同様ですが、F4キーを押した時の動作は以下のように始点セル、終点セルで独立しています。

※C6:D10の場合

・カーソルがC6上の時にF4キーを押すと、C6のみが絶対参照($C$6:D10)になります。

・カーソルがD10上の時にF4キーを押すと、D10のみが絶対参照(C6:$D$10)になります。