【解決】スプレッドシートで数式をコピペした時にセル参照がずれる原因と対策

Googleスプレッドシートで数式を入力し、他のセルにコピーやオートフィルで反映させたときに、計算結果がずれてしまったり、エラーが表示されたりしたことはありませんか。

コピーした途端にエラー（#REF!や#VALUE!など）が出たり、数値が「0」になったりすると、その後のデータ修正に多くの時間がかかってしまいます。

この「コピーで数式がずれる問題」は、スプレッドシートの参照の仕組みである「相対参照」と「絶対参照」の違いを理解することで解消できます。

この仕組みをマスターすれば、複雑な表でもミスなく効率的に数式をコピーできるようになり、データ入力や集計にかかる時間を短縮できます。スプレッドシートのセル参照のルールを確認していきましょう。

記事のポイント

スプレッドシートで数式をコピペした時にセル参照がずれる原因と仕組み

スプレッドシートで数式をコピーして他のセルに貼り付けたとき、数式内のセル番地（A1やB2など）が自動的に変わってしまうのは、スプレッドシートが壊れているわけではありません。

これは「コピペ先の位置に合わせて、参照元のセルも同じようにスライドさせる」という、スプレッドシートに標準で備わっている便利な機能が働いているためです。

Googleスプレッドシートの初期設定では、数式内のセル指定はすべて「相対参照（そうたいさんしょう）」になっています。

KYO

昔、請求書のテンプレートを作成した際、消費税率を記載したセルを絶対参照にし忘れたことがあります。そのまま何十人分もの請求金額が消費税0円で計算され、顧客に送付する直前のダブルチェックで発覚して全員で慌てて再作成する大トラブルになりました…。

相対参照とは、数式を入力したセルから見た「相対的な位置関係（例：自分から見て2つ左のセルと1つ左のセルを足す、など）」をベースに計算を行う仕組みです。

例えば、セルC2に =A2+B2 という数式を入力して下にコピーすると、自動的に「=A3+B3」へと変化するのですね。

補足

相対参照は「同じ計算ルールを複数のデータ行に順番に適用したいとき」に抜群の威力を発揮します。しかし、参照するセルを動かしたくない場合（固定の税率や単価などを参照する場合）には注意が必要です。

実務の現場では、すべての計算で「特定の1箇所にあるセル」を共通して参照したい場面が多々あります。

これを防ぐために、数式内のセル指定を固定する「絶対参照（ぜったいさんしょう）」という指定方法が必要になります。

絶対参照にするには、固定したいセルの列アルファベットと行番号の前に、それぞれ「$（ドルマーク）」を付けます。例えば、セルE2を完全に固定したい場合は「$E$2」と記述しますよ。

数式のズレを防ぎ、常に特定のセルを固定して参照するためには、スプレッドシートの超重要機能である「絶対参照（ドルマーク固定）」をマスターするのが一番の近道です。

書き方はシンプルで、列文字の前と行番号の前の両方にドルマーク（$）を付けるだけです。

例えば、セル「A1」を完全に固定したい場合は、以下のように書き換えます。

「$A1」や「A$1」のようにドルマークを片方だけに付けると、列だけ、あるいは行だけが固定される「複合参照」になります。慣れないうちは列も行も両方固定する「$A$1」を基本形として覚えるのが安全ですよ。

絶対参照は便利ですが、「毎回シフトキーを押して『$』を手入力するのは面倒」と感じますよね。そこで便利なのが、Windowsは「F4」キー、Macは「Fn + F4」キーを押すだけのショートカットです。

ポイント

F4キーを押す回数によって、$A$1（絶対参照）→ A$1（行のみ固定）→ $A1（列のみ固定）→ A1（相対参照）と切り替わります。これで入力スピードが格段にアップします。

数式のコピペで参照先がずれる問題は、「絶対参照」か「相対参照」かの二択だけではありません。さらに一歩進んだテクニックをご紹介します。

数式をコピペしたときに「縦方向には動いてほしいが、横方向には動いてほしくない」という場面で役立つのが「複合参照」です。

例えば九九の表を作るような場合、=$A2*B$1 のように、かける数には列の固定（$A）、かけられる数には行の固定（$1）を組み合わせることで、1つの数式を全マスにコピペするだけで完成させることができますよ。

「データが追加されるたびに数式をコピペするのが面倒」という悩みを解決してくれるのが、スプレッドシート独自機能の「ARRAYFORMULA関数」です。

=ARRAYFORMULA(A2:A * B2:B) のように範囲同士を計算させる数式を一番上のセル（C2など）に1つ入力するだけで、下の行まで一気に計算結果が自動展開されます。

コピペ作業自体が不要になるため、物理的に数式がずれる心配がゼロになりますよ。最新の仕様については公式サイトもご確認くださいね。