絶対参照で単一セルを固定する

多くの数式から1つの共有値を参照する必要はよくあります。たとえば、税率、為替レート、目標値、固定料金などです。

その値は1つのセルに入力され、他の数式はコピーする場所に関係なく、常にそのセルを参照し続ける必要があります。これを可能にするのが絶対参照です。

絶対参照では、列と行の両方の前にドル記号を付け、$A$1のように表します。完全に固定された参照です。

$A$1を含む数式をシート内のどこにコピーしても、その部分は常にA1セルを指します。数式内で固定されていない参照だけが移動します。

売上税率0.08がセルE1に入力され、注文金額がB列に並んでいるとします。各注文に税を加えるには、C2に次のように入力します。

=B2*$E$1

B2は相対参照なので各行に合わせて移動し、$E$1は絶対参照なので常に税率を参照します。

数式をC2からC2、C3、C4へと下方向に入力し、それぞれの部分がどう変わるか確認してください。

• B2はB3、さらにB4になる（相対参照なので移動します）
• $E$1は毎回$E$1のままになる（絶対参照なので固定されます）

そのため、3行目は=B3*$E$1、4行目は=B4*$E$1になります。税率が失われることはありません。

ドル記号を付けずに、=B2*E1と入力してみてください。下方向にコピーすると、E1はE2、E3へと移動しますが、そこは空のセルです。

税額の列にはゼロが並びます。数値が間違っているように見えますが、原因は単純です。税率の参照が絶対参照であるべきところ、相対参照になっていたのです。

E1をすばやく$E$1に変換するには、数式バー内のE1をクリックして、F4を1回押してください。Excelが2つのドル記号を自動的に挿入します。

特に長い数式では、記号を手入力するよりも、はるかに速く、入力ミスも起こりにくくなります。

$B$1に米ドルからユーロへの換算レート0.92が入力され、ドル金額がA列に並んでいるとします。それぞれの金額を次の式で変換します。

=A2*$B$1

数百行にわたってコピーしても、すべての変換で同じレートが使われます。B1のレートを1回更新するだけで、列全体がすぐに再計算されます。

両方の部分がロックされているため、絶対参照は下方向、右方向、両方向のどちらにコピーしても変わりません。

=B2*$E$1を右方向にコピーすると、=C2*$E$1になります。BはCに移動しますが、$E$1はそのままです。ロックはどの方向でも機能します。

絶対参照は関数の中でも活躍します。B1にある固定の合計に対する各値の割合を求めるには、次のようにします。

=A2/$B$1

また、D1に保存された目標値と各スコアを比較するには、次のようにします。

=IF(A2>=$D$1,"Pass","Fail")

ロックされたセルは、すべての行で参照先として固定されます。

絶対参照の本当の利点は、1か所で管理できることです。すべての数式が同じロックされたセルを参照するため、そのセルを変更するだけで列全体が一度に更新されます。

E1の税率を0.08から0.09に上げると、すべての=B2*$E$1の数式がすぐに再計算されます。数十個の数式を編集する必要はありません。1つの入力ですべてを動かせます。

熟練者は、定数やレート、目標値をシート上部の小さくラベル付けした領域にまとめ、絶対参照で参照することがよくあります。

こうすると数式が読みやすくなり、1つの入力を変更するだけでレポート全体を更新できます。すべての数式がその入力を参照し続けるのは、ドル記号のおかげです。

絶対参照を理解できているか確認しましょう。

$A$1のような絶対参照は列と行の両方をロックするため、コピーしても常に同じセルを参照します。

• レート、手数料、目標値など、共有する値に使用します
• 相対参照と組み合わせて、必要な部分だけを移動させます
• F4を押すと、2つのドル記号をすぐに追加できます

次は、複合参照を使って一度に1つの部分だけをロックします。