絶対参照は、数式をコピーしてもセルを固定したままにします。=B2*$E$1では、ドル記号がE1を固定しています。数式を下にコピーしても、B2はB3、B4と動く一方で、どの行もE1を掛け続けます。ドル記号を付けるには、数式の中の参照をクリックしてF4を押します(MacではCmd+T)。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
C2は一度入力して下にコピーしたものです。C4をクリックすると、数式は=B4*$E$1です。売上のセルは4行目に動き、率はE1のままです。E1の率を8%に変えると、すべての手数料が更新されます。
相対参照と絶対参照の違い
| 参照 | 名前 | 1行下、1列右にコピーしたとき |
|---|---|---|
A1 | 相対参照 | B2 |
$A$1 | 絶対参照 | $A$1 |
A$1 | 複合参照: 行を固定 | B$1 |
$A1 | 複合参照: 列を固定 | $A2 |
B2のような普通の参照は相対参照です。Excelはこれを「自分から見てこの位置にあるセル」として記録するので、1行下にコピーすると参照も1行下を指します。行ごとのデータにはまさにこれが必要で、これが既定の動きです。列の文字や行の番号の前に$を付けると、その部分が固定されます。
よくある間違い: $なしで下にコピーする
先ほどの手数料のシートで、C2をドル記号なしの=B2*E1にしたものです。最初の行は正しく、残りは0になります。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
C3には=B3*E2が入っています。率の参照が空のE2に下がり、空白セルは0として扱われます。ここで直してみましょう。C2をクリックし、数式を=B2*$E$1に変えてEnterを押します。C3:C6はC2のコピーなので、列全体が直ります。合計に対する割合を出す=B2/B7のように、固定したいセルが割る数のときは、同じ間違いで0ではなく#DIV/0!になります(よくある例は合計に対する割合です)。
F4キーでドル記号を付ける
数式の入力中や編集中に、カーソルを参照の中(またはすぐ後ろ)に置いてF4を押します。押すたびに次の形に変わります：
E1 -> $E$1 -> E$1 -> $E1 -> E1
多くのノートパソコンではF4が画面の明るさや音量の操作に割り当てられているので、Fn+F4を押します。MacではCmd+TかFn+F4を使います。$記号を自分で入力してもかまいません。
複合参照: 行だけ、または列だけを固定する
複合参照はドル記号が1つです。$A2は常にA列を読みますが行は動き、B$1は常に1行目を読みますが列は動きます。1つの数式に両方を入れると、表全体にコピーした1つの数式で九九の表ができます：
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
B2には=$A2*B$1が入っています。F6をクリックすると=$A6*F$1で、A列の行の数と1行目の列の数を掛けた25が表示されます。B2のドル記号を1つ外すと表は崩れます。コピーが見出しではなく隣のセルを掛け始めるからです。
同じ形で、価格のリストを複数の割引率で計算できます。A列の下方向に価格、1行目の右方向に割引率を置いて=$A2*(1-B$1)とします。
片側だけ固定した範囲で累計を出す
範囲は片方の端だけを固定することもできます。=SUM($B$2:B2)は常にB2から始まり、終わりは数式をコピーするにつれて下に動くので、各行が自分の行までのすべてを足します：
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
C6には=SUM($B$2:B6)が入っていて、5か月すべての合計2230が表示されます。同じ片側固定の範囲を使うと、=COUNTIF($A$2:A2,A2)で値がそこまでに何回出てきたかを数えられます。2回目以降の重複に印を付けるときに使う方法です。
練習: 表全体を1つの数式で
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
やってみよう: B2に、最初の商品のB1の割引後の価格を書いてください。$を使って、同じ数式をD5まで右と下にコピーしたとき表のすべての価格が出るようにします。
このシートは、フィルハンドルと同じようにあなたの数式をB2:D5のすべてのセルにコピーし、チェックで12個の結果をすべて確かめます。ドル記号が正しくないと、3行目やC列のコピーが間違った価格や割引率を読んでしまいます。
別シートや検索用の表への絶対参照
シート名が付いていても、ドル記号の働きは同じです：=B2*Settings!$B$1。特に大切なのは検索で、検索値は動いても表は固定しておく必要があります。=VLOOKUP(A2,$E$2:$F$10,2,FALSE)を下にコピーすると常にE2:F10を探しますが、=VLOOKUP(A2,E2:F10,2,FALSE)はコピーごとに表が1行ずつ下にずれ、最初の行が見つからなくなります(VLOOKUP)。固定したいセルを多くの数式で使うなら、数式 > 名前の定義で名前を付けて=B2*Rateと書くこともできます。こうして定義した名前は、$E$1と同じように、どの数式からも同じセルを指します。
よくある質問
Excelの数式の$記号はどういう意味ですか？
E1は行だけを、$E1`は列だけを固定します。
Excelの絶対参照のショートカットは？
数式の編集中に参照の中をクリックしてF4を押します(多くのノートパソコンではFn+F4)。押すたびに$A$1、A$1、$A1、A1の順に切り替わります。MacではCmd+TかFn+F4を押します。
相対参照と絶対参照の違いは何ですか？
B2のような相対参照は、数式をコピーすると動きます。1行下ではB3になります。$B$2のような絶対参照は$B$2のままです。率や合計など、すべての行で使う1つのセルには絶対参照を使います。
Excelの複合参照とは何ですか？
ドル記号が1つの参照です。$A2は列を固定して行を動かし、B$1は行を固定して列を動かします。=$A2*B$1を表の右と下にコピーすると、九九の表ができます。
数式を下にドラッグすると0や#DIV/0!になるのはなぜですか？
固定すべき参照がコピーと一緒に動いたからです。2行目が=B2/B7なら、3行目は=B3/B8になり、B8は空です。=B2/$B$7で合計を固定して、もう一度コピーしてください。
