数式をコピーしたら参照先がずれる？Googleスプレッドシートの$で固定する場所を選ぼう

同じ計算式を下の行へコピーしたら、答えがおかしくなった。計算に使うセルの場所まで一緒に動いていないか、数式を見てみよう。今回はGoogleスプレッドシートの「相対参照」と「絶対参照」を、一つの小さな表で比べるよ。

目標は、行に合わせて動かす参照と、同じ場所を使い続ける参照を選べること。実際に試す場合は、編集できる練習用のシートが必要。仕事の本番データを使わず、新しいシートに架空の数値を入れよう。

B2は、B列の2行目を指す

セルは表の一つのマス。A、B、Cという列と、1、2、3という行を組み合わせて場所を表す。B2はB列の2行目、E1はE列の1行目だよ。

数式の中で別のセルの値を使うことを、ここではセル参照と呼ぶ。参照先の値が変われば、式を保存したまま計算結果へ反映できる。

コピーすると、参照先も移動する

普通にB2と書いた参照は「相対参照」。式を一行下へコピーするとB3、さらに下ならB4というように、移動した分に合わせて参照先が変わる。

同じ行の価格を使いたいときは、便利な動きだね。一方、全行で同じ基準値を使いたい場所まで動くと、意図とは違う計算になる。

架空の配分額を計算する

次の値を、それぞれ指定したセルへ入れる。ここで使う10%は練習用の配分率で、税率など実際の制度を表す数字ではないよ。

C2へ次の式を入れよう。数式の先頭には等号の=を付ける。

=B2*$E$1

B2の100に、E1の10%を掛けるので、期待する結果は10。次にC2をコピーしてC3へ貼り付け、C3の数式を確認する。

=B3*$E$1

価格はB3へ動き、基準はE1のままなので、期待する結果は20になる。画面の表示形式によって小数点以下などの見え方が違う場合もあるので、数式と値を分けて確認してね。

$は、直後の列や行を固定する

Googleの公式ヘルプでも、行と列の固定に応じて四つの書き方を区別している。

$E$1なら、横へコピーするときも縦へコピーするときもE1を使う。今回のように下へコピーするだけなら行の固定が特に重要だけれど、表を横へ広げる予定も含めて選びたいね。

この$は、コピー時の参照を固定する指定。セルの編集を禁止したり、行や列の挿入に対して位置を永久に不変にしたりする機能とは分けて考えよう。

=B2*E1のままだと、どこが変わる？

比較する場合は、別の空いている練習セルで=B2*E1を入力し、一行下へコピーした式を見る。相対参照のE1もE2へ変わる。E2に何があるかによって結果が変わるため、答えだけで判断せず、参照しているセルを読むことが大切だよ。

元へ戻す場合は、練習用の数式を=B2*$E$1へ直し、C3にも正しい式をコピーする。コピー元の式が正しいか分からなくなったら、C2とC3を一つずつ確認しよう。広い範囲へ一気に貼り付ける前に、二行で確かめると直しやすい。

編集部では、例の数値と、一行下へ移したときの参照先を独立した計算プログラムで確認した。Googleの実アカウント上でのコピー操作を実施した記録ではないよ。手元では数式バーに出る式と、10・20という期待値の両方を確かめよう。

結果だけを残すときは値のみ貼り付け、編集を制限したいときは保護範囲と共有権限へ。