Excelで数式をコピーしたとき、「計算結果がずれる」「値が変わってしまう」という経験はありませんか？
その原因は、セル参照が“相対参照”になっているためです。
本記事では、Excel初心者が必ず押さえておきたい
相対参照・絶対参照の違いと使い方、$マークでセルを固定する方法をわかりやすく解説します。
この記事を読めば、より数式が崩れにくい表作成ができるようになります。
相対参照とは？
相対参照とは、数式をコピーしたときに参照先のセルが自動でずれる（相対的に変わる）設定のことです。
Excelの大きな特徴でもあり、大量のデータを効率的に処理できるメリットがあります。
例：相対参照の動き
セルB1に次の式を入力します。
=A1*10
これを下の行にコピーすると…
- B2 ⇒ A2*10
- B3 ⇒ A3*10
- B4 ⇒ A4*10
- B5 ⇒ A5*10
このように、参照セルが下方向に1つずつずれて更新されます。
数式が入力されたセルを別のセルにコピーする際、コピー＆ペーストで１つずつ複製することもできますが、マウス操作で簡単に行うこともできます。
１．複製したいセルを選択します。
２．マウスポインタ―をセルの右下に合わせて、マウスポインタ―をプラスマーク＋にします。
３．その状態で、コピーしたい方向にドラッグすると選択したセルが、ドラッグ範囲に複製されます。
４．この時最初に複数のセルを選択した場合、選択範囲のルールに従ってコピーされます。
例えば『1』をマウス操作でコピーすると、ドラッグ範囲が全て『1』になります。
一方で『1、2』をマウス操作でコピーすると、ドラッグ範囲が全て『1、2、3、４・・・』と連番になります。
相対参照が向いている場面
- 縦に並んだデータに同じ計算式を適用したいとき
- 同じパターンの計算を行う表を作るとき
Excelではデフォルトが相対参照なので、自然に使っている人がほとんどです。
相対参照の場合、コピー＆ペーストとカット＆ペースト（または行列の挿入）で、挙動が異なります。
カット＆ペーストの場合
『B1 ⇒ A1*10』の数式が入力された状態で、B1をC1にカット＆ペーストすると、『C1 ⇒ A1*10』と同じ計算結果がC列に移動します。
コピー＆ペーストの場合
『B1 ⇒ A1*10』の数式が入力された状態で、B1をC1にコピー＆ペーストすると、『C1 ⇒ B1*10』と同じ位置関係であらたな計算結果がC列に移動します。
今回の例を縦方向ではなく、横方向にコピー＆ペーストした場合、相対参照では位置関係がコピーされ、以下のようになります。
- C1 ⇒ B1*10
- D1 ⇒ C1*10
- E1 ⇒ D1*10
絶対参照とは？
絶対参照とは、どれだけコピーしても参照先のセルを固定する設定のことです。
Excelでは、セル番地に $（ドルマーク） を付けることで固定できます。
例：絶対参照の動き
セルC1に次の式を入力します。
=$A$1*B1
セルA1には『10』が入っているとします。
この式を下の行へコピーしても…
- C2 ⇒ $A$1*B2
- C3 ⇒ $A$1*B3
- C4 ⇒ $A$1*B4
$A$1 の部分だけは固定されたままです。
絶対参照が向いている場面
- すべての行で 同じセルの値 を使いたい
- 消費税、定数、割合など 変わらない値を参照 するとき
$マークの付け方（絶対参照にする方法）
絶対参照は、セル参照に $（ドルマーク） を付けることで設定できます。
操作手順
１．数式の中のセル番地（例：A1）を選択します。セル番地の選択はドラッグする必要はなく、セル番地にカーソルがあれば大丈夫です。
２．F4キー を押します。
３．A1 ⇒ $A$1に変わります。
数式の編集は数式バーで行うこともできます。
数式バーの表示は
『表示』タブをクリック⇒『表示』グループ内にある数式バーに☑マークを入れることで表示できます。
行絶対参照と列絶対参照
エクセルでは行番号（1,2,3・・・）と列番号（A,B,C・・・）にそれぞれ$マークをつけて固定できます。
F4キーを押すたびに次のように切り替わります。
| 参照形式 | 表記 | 意味 |
|---|---|---|
| 絶対参照 | $A$1 | 行も列も固定 |
| 行絶対参照 | A$1 | 行のみ固定 |
| 列絶対参照 | $A1 | 列のみ固定 |
| 相対参照 | A1 | どちらも固定しない |
行絶対参照の使い方例
行絶対参照は行位置は固定され、列順は相対参照になる設定です。以下のような場合に便利です。
１．１行目には各列（B1～E1）に税率0～10%を入力します。
２．A列には各行に税抜き価格を入力します。
３．B2に『=$A2*(1+B$1/100)』と入力し税込み価格計算をします。この時『$A2』は列絶対参照で『B$1』は行絶対参照です。
４．B2の数式を列方向（C2,D2,E2）にコピーします。
すると消費税率の行は『１行目』で固定され、列は相対参照で、列番号に応じた税率で計算されます。
列絶対参照の使い方例
列絶対参照は列位置は固定され、行番号は相対参照になる設定です。
先ほどの例で、引き続き行方向（下方向）にコピーを行うと、以下のように計算されます。
『税抜き価格』の参照において、列は固定されているのですべてのセルの数式でA列を参照します。
一方で行は相対参照になっているので、３行目ではA3を、４行目ではA4というように、各セルの行数に応じて数式が更新されます。
F4がうまく効かない場合の対処法
Fnキーと同時に押す
ノートPCの場合、Fnキーと併用が必要なことがあります。
その場合はFnキー＋F4キーを試してください。
Fnキーを１回押して、ショートカット機能をOFFにする
またキーボードによっては音量調節や画面の明るさ調節のショートカットがF1~F12キーに割り当てられていることがあります。
多くの場合、そのショートカット機能のON/OFFは、Fnキーで切り替えができますので、試してみてください。
さいごに
- 相対参照：コピーすると参照セルが変わる
- 絶対参照：コピーしても参照セルが固定される
- 絶対参照の設定：セルに 『＄』 を付ける、または F4キーで切り替え
- 固定したい値（税率・定数など）には絶対参照が便利
絶対参照と相対参照を理解すると、
複雑な表でもミスのない数式が作れるようになります。
反対に相対参照となってほしいのに絶対参照があるせいで、思うように計算がされない場合もあります。
その場合は『Ctrl＋F』の検索機能で＄マークを検索してみましょう。
Excelの検索機能については以下の記事も参考にしてみてください。
皆さんのお仕事に少しでも役立てれば幸いです。ではまたﾉｼ
コメント