税込金額を計算する式を、下の行へコピーしたら、なぜか「0」になってしまった。そんな経験はありませんか。原因の多くは、「絶対参照」にすべきセルを、そのまま相対参照でコピーしてしまったことです。
この記事では、絶対参照とは何か、「$」の付け方、F4キーでの切り替え方、行だけ・列だけを固定する複合参照まで、図解で説明します。
絶対参照とは｜コピーしてもセルの場所が変わらない参照方法
エクセルの式は、コピーすると、参照しているセルの場所も一緒にずれます。これが「相対参照」で、普通の書き方です。一方、絶対参照は、「$」を付けて、コピーしても参照先を固定する書き方です。
| 種類 | 書き方 | コピーしたときの動き |
|---|---|---|
| 相対参照 | B2 | 参照先が一緒にずれる（B2→B3→B4） |
| 絶対参照 | $B$2 | どこにコピーしても、B2のまま変わらない |
| 複合参照（行だけ固定） | B$2 | 行は2のまま、列だけずれる |
| 複合参照（列だけ固定） | $B2 | 列はBのまま、行だけずれる |
絶対参照が必要になる場面｜税率を1つのセルにまとめる例
商品の価格に、税率を掛けて税込金額を出す表を例にします。税率をF1に入力しておき、C2に「=B2*$F$1」と入力します。F1だけを固定したいので、「$F$1」と書きます。
この式を、C3、C4にコピーすると、「B2」の部分だけが「B3」「B4」とずれ、「$F$1」の部分は、そのままF1を参照します。税率を変えたいときは、F1の数字を書き換えるだけで、すべての税込金額が変わります。
「$」の付け方｜F4キーで切り替える
「$」は、手で入力してもよいですが、式の中でセルを選んだ状態で F4キーを押すと、自動で付けられます。押すたびに、次の順番で切り替わります。
| 押した回数 | 表示 | 固定されるもの |
|---|---|---|
| 1回 | $B$2 | 行も列も固定（絶対参照） |
| 2回 | B$2 | 行だけ固定（複合参照） |
| 3回 | $B2 | 列だけ固定（複合参照） |
| 4回 | B2 | 固定なし（相対参照に戻る） |
ノートパソコンでは、F4キーに別の機能が割り当てられていて、「Fn」キーと同時に押さないと反応しないことがあります。反応しないときは、「Fn＋F4」を試してください。Macでは、「Command＋T」で切り替えられます。
手順1｜式を入力するセル（C2）を選び、「=B2*」と入力する
手順2｜税率のセル（F1）をクリックする
手順3｜そのまま F4 キーを1回押す。「$F$1」になったのを確認する
手順4｜Enter を押して確定する
手順5｜C2を選び、右下の■を、下へドラッグしてコピーする
複合参照｜行だけ・列だけを固定する
縦と横の両方に見出しがある表（九九の表など）では、行と列の片方ずつを固定する「複合参照」が便利です。B2に「=$A2*B$1」と入力して、右と下へコピーすると、全部のマスに九九の答えが入ります。
「$A2」は列だけ固定なので、右にコピーしても、A列の数字（1、2、3）を読み続けます。「B$1」は行だけ固定なので、下にコピーしても、1行目の数字を読み続けます。1つの式を書くだけで、全部のマスが埋まります。
よくある失敗｜コピーしたら「0」や同じ数字になった
絶対参照を付け忘れると、コピーしたときに、参照先が下へずれて、空のセルを見に行きます。
- 計算結果が0になる、または同じ数字ばかりになる
- 式をクリックして、参照先のセルがずれていないかを確認する
- 固定したいセルに「$」を付けて（F4キー）、もう一度コピーする
もう一歩進める｜絶対参照の応用的な使い方
範囲を固定して、順位や合計を求める
RANK.EQ関数やSUMIF関数など、「範囲」を引数に取る関数でも、絶対参照は欠かせません。たとえば、順位をつける式では、比べる範囲を「$B$2:$B$6」のように固定しないと、下へコピーしたときに、範囲がずれて、正しい順位が出ません。
名前を付けて、絶対参照にする方法
「税率」のように、何度も使うセルは、「数式」タブの「名前の定義」で、名前を付けておく方法もあります。名前を付けたセルは、自動で絶対参照として扱われるので、式が「=B2*税率」のように読みやすくなります。
別のシートのセルを固定する
別のシートの税率を使いたいときも、「=B2*設定!$B$1」のように、シート名の後ろに「$」付きのセルを書きます。シートの設定を1か所にまとめておくと、あとから変更したいときに、1つのセルだけを書き換えれば済みます。
まとめて絶対参照にしたい（一括変換）
すでに入力した式の「$」を、一つずつ付け直すのは手間がかかります。式のセルをダブルクリックして、参照部分を選び、F4キーを押せば、その場で切り替えられます。複数のセルを同時に直したいときは、「ホーム」タブの「検索と選択」→「置換」で、「F1」を「$F$1」に置換する方法もあります。
$を付けるか迷ったときの考え方
式を入力するとき、「このセルに$を付けるべきか」と迷ったら、「この式を、下や右にコピーしたとき、このセルは動いてほしいか」を考えます。
動いてほしくないセル（税率、単価、基準日など、1か所だけに入力した値）には、$を付けます。動いてほしいセル（行ごとに違うデータ）には、$を付けません。
迷ったときは、先に$なしで式を作り、1つ下へコピーして、結果を確かめる方法もあります。結果がおかしければ、ずれているセルを探して、F4キーで$を付け直します。
式を書き換えたあとの確認
$を付け直したら、もう一度、下までコピーし直します。すでにコピーした式は、元の式を直しても、自動では書き換わらないためです。
まとめ
- 絶対参照は「$B$2」と書き、コピーしても参照先が変わらない
- F4キーを押すたびに、「$B$2」→「B$2」→「$B2」→「B2」の順に切り替わる
- 税率など、1か所の値を使い回すときは絶対参照を使う
- 縦と横の表では、行だけ・列だけを固定する複合参照が便利
- コピーして0や同じ数字になったら、「$」の付け忘れを疑う
式を作ったら、必ず1つ下のセルをクリックして、式がずれていないか確認する習慣をつけておくと、ミスに早く気づけます。
コメント