コピー時に参照が調整される仕組み

数式に=A2と入力しても、スプレッドシートが覚えているのは「セル A2」そのものではありません。「自分から左に2列離れたセル」のような、方向と距離を覚えています。

これを相対参照と呼びます。参照は数式自体の位置を基準に表されるため、数式を別の場所にコピーすると、参照もそれに合わせて調整されます。この動作があるからこそ、オートフィルがうまく機能します。

数値を2列用意します。

• A2 = 100、B2 = 50
• A3 = 200、B3 = 80

C2に=A2+B2と入力します。150と表示されます。次にC2をC3へ下方向にコピーします。数式が=A3+B3に変わるため、結果は280になります。

何も編集していません。参照が数式と一緒に1行下へ移動したのです。

数式を下方向にコピーすると、変わるのは行番号だけです。列の文字は変わりません。

=A2+B2から始めると、次のようになります。

• 1行下では=A3+B3になります
• 2行下では=A4+B4になります
• 3行下では=A5+B5になります

横方向には移動していないため、A列とB列は変わりません。

数式を横方向にコピーすると、列の文字が変わり、行番号はそのままです。

B2に=B1*2が入っているとします。これを右方向にコピーしてC2とD2に入れると、次のようになります。

• C2は=C1*2になります
• D2は=D1*2になります

下方向には移動せず横方向にだけ移動したため、行番号は1のままです。縦横の両方向に同時に移動すると、両方の部分が一緒に変わります。

相対参照を理解するには、数式セルからのオフセットとして考えると便利です。

C2に=A2が入っている場合、オフセットは「同じ行で2列左」です。この数式をC5にコピーすると、新しい位置を基準にオフセットが適用されます。C5の2列左で同じ行にあるセル、つまりA5が参照されます。

数式と参照先を結ぶ矢印は、常に同じ長さと方向を保ちます。

1,000行ある請求書を想像してください。各行で数量と単価を掛ける必要があります。相対参照がなければ、1,000個の異なる数式を手作業で入力しなければなりません。

相対参照があれば、=A2*B2を一度入力して下方向にオートフィルするだけです。各行が自動的に、その行の数量と単価を参照します。1つの数式で、1,000件の正しい結果を入力ミスなく得られます。

相対参照を使えば、面倒な作業が1回のダブルクリックで済みます。

実際に確認してみましょう。オートフィルした後、コピーされたセルのいずれかをクリックし、上部にある数式バーを見てください。

=A2+B2を5行下までコピーしてから、最後のセルをクリックします。数式バーには=A2+B2ではなく=A7+B7と表示されます。各セルには、それぞれの位置に合わせて調整された数式が入っています。

列内のセルを順番にクリックし、数式バーを読むことが、コピーの仕組みを直感的に理解する最もよい方法です。

相対参照による調整は通常便利ですが、常に必要とは限りません。たとえば、すべての行で、E1セルにある1つの税率を掛けるとします。

=C2*E1と入力して下方向にオートフィルすると、E1の部分がE2、E3、E4へと移動し、空のセルを参照してしまいます。その結果、正しく計算できなくなります。

C2は下方向に移動させたい一方で、E1は固定したいはずです。そのためには絶対参照が必要になります。絶対参照については次のコースで扱います。

解決方法を少しだけ紹介します。参照の一部の前にドル記号を付けると、その部分が固定され、移動しなくなります。

=C2*$E$1と入力して下方向にオートフィルします。するとC2はC3、C4へと移動しますが、$E$1は毎回、税率が入ったセルとして固定されます。

このドル記号の使い方は、まもなく詳しく学びます。今は、この仕組みがあることだけ覚えておいてください。

ここで学ぶ内容は、Microsoft ExcelとGoogle Sheetsで同じように使えます。どちらもデフォルトでは相対参照を使用し、下方向にオートフィルすると行が変わり、横方向にオートフィルすると列が変わります。

ドル記号で参照を固定する構文も、どちらも同じです。一方のアプリで身につけた数式のコピー方法は、もう一方でもそのまま活用できます。

オートフィルした列の数値が間違っている場合、ほとんどの場合は、固定すべき参照まで移動してしまったことが原因です。

正しく動作していないセルをクリックし、数式バーを読みます。「この参照先は期待どおりになっているか」と確認してください。固定すべき参照が移動していれば、原因が見つかったことになります。ドル記号を付ければ解決できます。

オートフィルの仕組みを理解できました。

• 参照はデフォルトで相対参照になり、数式セルからのオフセットとして保存されます。
• 下方向へのオートフィルでは行番号が変わり、横方向へのオートフィルでは列の文字が変わります。
• この自動調整により、1つの数式で何千行ものデータを処理できます。
• 参照を固定する必要がある場合は、ドル記号で固定します。この方法は次のコースで学びます。

オートフィルした列の結果がおかしいときは、数式バーを確認してください。