Excelで計算式を固定する方法|絶対参照で参照するセル番地を固定

Excelで計算式を固定する方法

Excel(エクセル)で計算式を固定する方法を紹介します。計算式を入力したセルをコピーすると、計算結果が正しく表示されなかったことがあるのではないでしょうか。それはコピーすると、計算式がずれてしまうからです。

今回は、ズレてしまう原因と対処方法、相対参照や絶対参照、複合参照について図例を用いながら解説していきます。

Excelでセルをコピーすると計算式がズレてしまう原因

Excel(エクセル)でセルをコピーすると計算式がズレてしまう原因は、計算式に含まれるセル番地が相対参照だからです。

下図のC列「値引き後の価格」は、B列「単価」からセルF3「値引額」を引いた結果です。セル「C3」に計算式「=B3-F3」を入力して正しい結果となりました。

Excelでセルをコピーすると計算式がズレてしまう原因

下図は、「C3」の計算式をセル「C4」から「C7」までコピーした結果です。計算式をコピーしたセルの結果は、値引額が引かれず正しくありません。

Excelでセルをコピーすると計算式がズレてしまう原因

コピーしたセル「C4」の計算式をみると、「=B4-F4」となっています。値引額のセル「F3」を参照していないため、正しい結果にならなかったのです。

Excelでセルをコピーすると計算式がズレてしまう原因

計算式が相対参照の方式を使用していたため、コピーとともに相対的に参照セルも移動したことがズレた原因です。下図では、常にセル「F3」の参照が必要ですが、計算式を下方向へコピーしたことで、セル「F3」も「F4」~「F7」に変わってしまいました。

Excelでセルをコピーすると計算式がズレてしまう原因

セルをコピーした時に参照セルをずらしたくない場合は、絶対参照に変更することが必要です。常に固定のセルを参照します。

相対参照と絶対参照の違い

相対参照とは、計算式のセル番地との位置関係で参照するセル番地が変化する方式です。絶対参照の表記にしない限り、計算式では相対参照が使われます。

下図のセル「C3」の計算式は、同行(3行目)のセル「B3」から「F3」を引く式です。

相対参照と絶対参照の違い

この式をセル「C4」へコピーすると、「=B4-F4」となりました。これは相対参照の位置関係で参照セルが変わるため、セル「C4」と同行の「B4」から「F4」を引く計算式に変わったのです。

相対参照と絶対参照の違い

絶対参照とは、参照セルを固定にする方式です。参照方式を絶対参照に変更すると、計算式をコピーしても参照セルは移動せず、同一セルを参照します。

下図では、値引額のセル「F3」の参照を固定にした計算式をコピーしました。コピーされたセル「C4」は、正しく値引額のセル「F3」を参照しています。

相対参照と絶対参照の違い

Excelで計算式を固定するには絶対参照で参照するセル番地を固定

Excel(エクセル)で計算式を固定するために、絶対参照でセル番地を固定にする方法を説明します。商品の単価から同一の値引額を引く例で、手順をみていきましょう。

STEP.1
セル「C3」に値引き後の価格を計算する式「=B3-F3」を入力しました。この式は相対参照です。コピーすると参照先がずれてしまうため、絶対参照に変更する必要があります。固定にしたいセル番地は、値引額のセル「F3」です。セル「F3」を絶対参照に変更します。

Excelで計算式を固定

STEP.2
絶対参照に変更する方法は、計算式「=B3-F3」のF3にドルマーク($)をつけます。下図では「=B3-$F$3」となりました。

Excelで計算式を固定

STEP.3
セル「C3」の計算式を「C4」から「C7」へコピーします。

Excelで計算式を固定

STEP.4
セル「C4」から「C7」まで正しい計算結果が表示されました。セル「C4」の計算式をみると、参照するセルがずれることなくセル「F3」を参照しています。

Excelで計算式を固定

絶対参照の「$」を配置するショートカット

Excel(エクセル)で、絶対参照のドルマーク($)の配置をショートカットキーで行う方法を説明します。

STEP.1
セル「C3」に、値引き後の価格を計算する式「=B3-F3」を入力しました。この式の「F3」にカーソルを合わせます。カーソルの位置は、「F」の前後でも「3」の後でも構いません。

絶対参照の「$」を配置するショートカット

STEP.2
カーソルを合わせた状態でファンクションキー「F4」を押すと、「F3」にドルマーク($)がついて「$F$3」に変わり絶対参照となりました。以上で完了です。

絶対参照の「$」を配置するショートカット

絶対参照の状態でファンクションキー「F4」を3回押すと、相対参照に戻ります。ファンクションキーを押すごとに、上図のセル番地で表すと「F3」→「$F$3」→「F$3」→「$F3」→「F3」の順番に変わっていきます。「F$3」や「$F3」は複合参照の方式です。

Excelの計算式で行か列だけ絶対参照する「複合参照」の方法

複合参照とは、絶対参照と相対参照を組み合わせた参照方式です。絶対参照の場合、セル番地を完全に固定するため、コピーしても常に同一のセル番地を参照します。

複合参照で計算式をコピーすると、行(または列)のどちらか一方は固定参照です。もう一方の列(または行)は、コピー先のセルと相対的に変化します。計算式を行または列のどちらか一方の方向にコピーする場合は、相対参照と絶対参照の組み合わせで問題ありません。

下図では、1の段の計算式をセル「B2」に「=$A$2*B1」を入力して、行方向(右方向)へコピーしました。1の段のセル「A2」は固定にするため、絶対参照に変更しています。その結果、セル「C2」から「J2」まで正しい計算結果となりました。

Excelの計算式で行か列だけ絶対参照する「複合参照」の方法

セル「B2」の計算式を列方向(下方向)へコピーしてみます。セル「A2」を絶対参照としているため、正しい結果になりませんでした。列方向および行方向に計算式をコピーする場合は、複合参照の方式を使用する必要があります。

Excelの計算式で行か列だけ絶対参照する「複合参照」の方法

  • 行のみ絶対参照する方法
  • 列のみ絶対参照する方法

行のみ絶対参照する方法

Excel(エクセル)で行のみ絶対参照にする方法を説明します。

STEP.1
行を固定にしたいセルの行番号前に、ドルマーク($)をつけます。下図では、1行目を固定にするため「B1」を「B$1」に変更します。

行のみ絶対参照する方法

STEP.2
セル「B2」の計算式を下方向へコピーします。

行のみ絶対参照する方法

STEP.3
正しい計算結果が表示されました。

行のみ絶対参照する方法

コピーされた計算式をみると、全て1行目のセル「B1」を参照しています。

行のみ絶対参照する方法

セル「B2」の計算式「=B$1*A2」を横方向へコピーすると、セル「C2」の計算式は「=C$1*B2」となりました。計算式の「B$1」の列「B」は相対参照のため、「B」から「C」へ変わったのです。「B1」の行「1」は絶対参照にしたため、コピーしても固定されています。

行のみ絶対参照する方法

計算式「=B$1*A2」の「A2」は相対参照のため、コピーすると「B2」に変わってしまいました。これはA列に固定する必要があります。固定にする方法は後述します。

列のみ絶対参照する方法

Excel(エクセル)で列のみ絶対参照にする方法を説明します。前述で、セル「B2」の計算式「=B$1*A2」は1行目を固定にしました。今回は、セル「A2」のA列を固定にしていきます。

STEP.1
列を固定にしたいセル番地の、列アルファベットの前にドルマーク($)をつけます。下図ではA列を固定にするため、セル「B2」の計算式「=B$1*A2」の「A2」を、「$A2」に変更します。

列のみ絶対参照する方法

STEP.2
セル「B2」を横方向へコピーします。

列のみ絶対参照する方法

STEP.3
正しい計算結果が表示されました。

列のみ絶対参照する方法

コピーされた計算式をみると、全てA列の「A2」を参照しています。

列のみ絶対参照する方法

Excelで計算式を固定する方法を改めておさらい

Excel(エクセル)で計算式を固定する方法を説明しました。Excelのセルをコピーすると計算式がずれてしまうのは、相対参照の方式を使用しているからです。参照セルを固定にする場合は、ドルマーク($)をつけて絶対参照に変更します。

行方向及び列方向に計算式をコピーする場合は、複合参照を使用しましょう。相対参照や絶対参照、複合参照を理解していれば、スムーズに計算式を活用した表を作成できるようになります。