「Excelの数式、コピーしたら計算結果が変わった？」相対参照と絶対参照の違い

Excelで表を作成していると、同じ計算式を何行にも使いたいことがあります。

例えば、商品の単価と数量から金額を計算したり、売上金額に一定の割合を掛けたりする場合です。

最初のセルに数式を入力し、下のセルにコピーすれば簡単に計算できます。

ところが、こんな経験はありませんか？

「最初の行は正しく計算できたのに、下にコピーすると結果がおかしくなる」

「同じセルの数字を使いたいのに、コピーすると参照先が変わってしまう」

「数式に付いている『$』の意味がよく分からない」

実は、Excelには数式をコピーしたときに、参照するセルを自動的に変更する仕組みがあります。

便利な機能なのですが、知らずに使っていると計算ミスの原因になることもあります。

今回は、Excelの「相対参照」と「絶対参照」の違いについて、具体的な例を使って紹介します。

数式をコピーすると、参照先が変わる？

例えば、次のような商品管理表を作成したとします。

【2行目】

単価：1,000円  

数量：2個  

金額：2,000円

【3行目】

単価：1,500円  

数量：3個  

金額：4,500円

【4行目】

単価：2,000円  

数量：2個  

金額：4,000円

この表では、A列に単価、B列に数量、C列に金額を入力しています。

C2セルには、次の数式を入力します。

`=A2*B2`

これは、A2セルの単価とB2セルの数量を掛けるという意味です。

この数式をC3セルにコピーすると、

`=A3*B3`

さらにC4セルにコピーすると、

`=A4*B4`

となります。

このように、数式をコピーした位置に合わせて参照先が変わる仕組みを「相対参照」といいます。

行ごとに異なるデータを計算する場合には、とても便利な機能です。

相対参照では困る場合もある

では、すべての数式で参照先が変わってよいのでしょうか。

実は、そうではありません。

例えば、商品の税込価格を計算する表を作成するとします。

A列には商品名、B列には税抜価格、C列には税込価格を表示します。

そして、E1セルに税率として「10%」を入力しておきます。

この例では、すべての商品に同じ税率を適用するとします。

C2セルに次の数式を入力しました。

`=B2*(1+E1)`

この数式は、B2セルの価格にE1セルの税率を加えて計算するという意味です。

ところが、このままC3セルにコピーすると、

`=B3*(1+E2)`

となってしまいます。

本来はE1セルの税率を使いたいのに、参照先がE2セルに変わってしまうのです。

E2セルが空白の場合、意図した税込価格になりません。

このようなときに役立つのが「絶対参照」です。

絶対参照とは？

絶対参照とは、数式をコピーしても、指定したセルの参照先が変わらないようにする方法です。

先ほどの例では、E1セルを固定したいので、次のように入力します。

`=B2*(1+$E$1)`

E1の前に「$」という記号が付いています。

この「$」がポイントです。

`$E$1` と入力すると、数式をコピーしてもE列・1行目の参照が固定されます。

そのため、C3セルにコピーしても、

`=B3*(1+$E$1)`

となります。

B2はB3に変わりますが、税率を入力したE1セルは変わりません。

さらにC4セルにコピーすると、

`=B4*(1+$E$1)`

となります。

これなら、すべての商品に同じ税率を適用できます。

つまり、**行ごとに変えたい部分は相対参照、変えたくない部分は絶対参照**にするという考え方です。

なお、今回の税率は計算方法を説明するための例です。実際の業務では、対象の商品や取引に適用される税率を確認してください。

「$」は何を固定している？

Excelのセル参照には、次の4種類があります。

① A1（相対参照）

列も行も固定しません。

数式を下にコピーすると、A1はA2、A3と変わります。

② $A$1（絶対参照）

A列と1行目の両方を固定します。

数式を上下左右にコピーしても、参照先は変わりません。

③ $A1（複合参照）

A列だけを固定します。

数式を下にコピーすると、$A2、$A3と変わりますが、列はA列のままです。

④ A$1（複合参照）

1行目だけを固定します。

数式を右にコピーすると、B$1、C$1と変わりますが、行番号は1のままです。

このように、「$」が付いている部分は、数式をコピーしても変わらないと考えると理解しやすいと思います。

最初から4種類すべてを覚える必要はありません。

まずは、A1と$A$1の違いを理解するところから始めるとよいでしょう。

F4キーを使うと簡単に切り替えられる

絶対参照を設定するたびに「$」を入力するのは、少し面倒に感じるかもしれません。

そんなときには、F4キーを使う方法があります。

Windows版Excelでは、一般的に次の手順で操作できます。

① 数式を入力する

② 数式の中で固定したいセル参照にカーソルを置く

③ F4キーを押す

④ 必要な参照方法になるまで切り替える

例えば、数式の編集中にE1を選択してF4キーを押すと、$E$1に変更できます。

さらにF4キーを押すと、行だけ固定、列だけ固定といった形式に切り替わります。

ノートパソコンなどでは、機種によって「Fn + F4」の操作が必要になる場合もあります。

なお、F4キーは数式を編集中かどうかによって動作が異なるため、必ず数式内のセル参照を選択して操作してください。

数式をコピーするときに確認したいこと

相対参照と絶対参照を理解していても、数式のコピーで間違えることはあります。

特に、複数のセルを参照している数式では注意が必要です。

例えば、次のような場合です。

・商品ごとの数量や価格は、行に合わせて変更したい

・税率や係数は、特定のセルを使い続けたい

・集計範囲は、コピーしても変わらないようにしたい

このようなときには、どの参照先を変更し、どの参照先を固定するのかを考える必要があります。

数式をコピーした後は、最初の行だけでなく、途中の行や最後の行も確認してみてください。

特に、計算結果が表示されているからといって、必ずしも正しいとは限りません。

Excelでは、参照先が間違っていても、エラーが表示されずに数字だけが出る場合があります。

そのため、数式バーで参照先を確認することも大切です。

絶対参照を使えば、すべて解決するわけではない

ここも注意したいところです。

絶対参照は便利ですが、すべてのセルを固定すればよいというわけではありません。

例えば、商品ごとの価格を計算する数式で、単価のセルまで固定してしまった場合です。

下の行にコピーしても、最初の商品の単価を使い続けてしまいます。

これでは正しい計算になりません。

大切なのは、数式の中で「変わってほしい部分」と「変わってほしくない部分」を区別することです。

また、絶対参照は主に数式をコピーしたときの参照先を制御する仕組みです。

行や列を挿入・削除した場合などには、Excelが参照先を調整することもあります。

「$を付けたら、どんな操作をしても参照先が絶対に変わらない」という意味ではありません。

最後に

Excelで数式をコピーすると、参照するセルが自動的に変更されます。

これは便利な機能ですが、同じセルを参照し続けたい場合には、計算結果が意図しないものになることがあります。

そんなときに使うのが、今回紹介した絶対参照です。

最初は「$」の位置が分かりにくいかもしれません。

しかし、実際に数式をコピーしながら確認してみると、少しずつ理解できると思います。

数式をコピーしたときに、どのセルが変わり、どのセルが変わらないのか。

この違いを意識するだけでも、Excelの計算ミスを減らすことにつながります。

特に、見積書や売上管理表、集計表など、同じ数式を繰り返し使用する資料では役立つ知識です。

普段Excelを使っている方は、一度ご自身の表でも確認してみてはいかがでしょうか。

Excelの表の作成や数式の修正、既存資料の体裁調整なども承っています。計算結果が合わない、使いやすい管理表に整えたいといった場合には、お気軽にご相談ください。

【関連記事】

https://coconala.com/blogs/604725/832049

【関連サービス】

「Excelの表、見やすいのに使いにくい？」入力・更新しやすい管理表を作るために