コピーしても崩れない数式

LWP | コピーしても崩れない数式

図解1|明細ごとの値と、全行で使う値

明細ごとの単価と数量、共通の割引率を分け、各行で割引後の金額を求める

図1|青は明細ごとの値、オレンジは全行で使う割引率です。

解説1|一つの式で、異なる二種類のデータを読む

ストーリー

営業のナマズさんは、三つの商品の見積金額をまとめています。単価と数量は商品ごとに違いますが、今回の割引率は全商品で共通です。ココさんと一緒に、下へコピーでき、割引率の変更にも対応できる式を作ります。

売上シートの一行は、一商品の明細です。B列の単価とC列の数量は、その行の商品についての値です。一方、H2は全行の計算で読む共通の割引率です。先にこの関係を分けると、式のどこを動かし、どこを固定するかが決まります。

入力例|売上シート A1:C4

行

A列

B列

C列

1

商品

単価

数量

2

商品A

1000

2

3

商品B

1500

1

4

商品C

800

3

入力例|同じ売上シート H1:H2

行

H列

1

割引率

2

10%

新しいスプレッドシートでシート名を「売上」にし、上の値を対応するセルへ入力します。行番号と列記号は位置を示す枠で、入力データには含めません。D1には「売上(割引後)」と入力し、D2:D4は後で式を入れるため空けておきます。H2には、10という数値ではなく「10%」と入力してください。

計算の考え方

割引前の金額は単価×数量です。10%引きなら支払う割合は90%なので、さらに(1−割引率)を掛けます。商品Aなら1000×2×0.9=1800です。この例では消費税・端数処理を扱わず、全商品の割引率が同じという条件にします。

図解2|コピーすると、参照も一行下へ

D2のB2とC2が、D3へのコピーでB3とC3に変わる相対参照の対応

図2|まず割引前の式で、コピー先と参照先の関係を確かめます。

解説2|相対参照は、行ごとの計算を繰り返すための仕組み

B2という参照は、式をコピーするとコピー先に合わせて変わります。たとえばD2から見たB2は「同じ行の二列左」、C2は「同じ行の一列左」です。D3へコピーしてもこの位置関係を保つため、読むセルはB3とC3になります。この指定を相対参照と呼びます。

数式例|動きを確かめるため、D2へ一時的に入力

=B2*C2

D2を選んでコピーし、D3へ貼り付けます。D3を選択して数式バーを確認すると、次の式になります。セルに表示される1500だけでなく、式がB3とC3を読んでいることまで確認しましょう。

数式例|D3にコピーされた式

=B3*C3

計算結果|この段階は割引前

入力するセル

読むセル

計算

結果

D2

B2、C2

1000×2

2000

D3

B3、C3

1500×1

1500

D4へコピーした場合

B4、C4

800×3

2400

ナマズさん:「同じ式なら、同じ場所を読むと思っていたよ。」ココさん:「同じ計算の形を、次の明細にも使うための仕組みなんです。」

読むポイント

参照が動くこと自体は誤りではありません。明細ごとに読むべき単価・数量が変わるので、この動きが役立ちます。ただし、共通の割引率まで同じように動かすと意図に合いません。次のページで、式の一部だけを固定します。ここで説明したのはコピーです。切り取り移動や行列の挿入を、同じ規則だと考えないでください。

図解3|共通の割引率は、$H$2を読む

行ごとに動くB2とC2、コピーしてもH2を読む絶対参照を一つの式で使い分ける

図3|同じ式の中で、相対参照と絶対参照を使い分けられます。

解説3|共通セルだけ、列と行を固定する

H2の割引率は、D2だけでなくD3・D4でも同じ場所から読みます。そこで、Hの前と2の前に半角のドル記号を付けて「$H$2」と指定します。これが列と行の両方を固定する絶対参照です。

数式例|D2の式を次の式へ置き換える

=B2*C2*(1-$H$2)

式の中のB2とC2にはドル記号を付けません。単価と数量は行に合わせて変わり、割引率は全行でH2を読み続ける、という役割の違いをそのまま式に表します。

式の部分と、その役割

数式の記述

意味

B2*C2

その明細の単価×数量

$H$2

全明細で共通に使う割引率

1-$H$2

割引後に支払う割合

*

左右の値を掛ける

途中の計算|商品A

H2の10%は、計算では0.1として扱われます。括弧の中は1−0.1=0.9、割引前は1000×2=2000です。最後に2000×0.9を計算し、D2は1800になります。

ココさん:「全部の参照を固定するわけではありません。」ナマズさん:「毎行の単価は変わってほしいし、割引率は共通でいいんだね。」

読むポイント

$Hは列、$2は行を固定しています。下方向へのコピーだけなら行固定だけでも成立しますが、今回は共通の一セルを指定する基本形として、両方にドル記号を付けます。片方だけを固定する複合参照は、別の用途として学べます。

図解4|コピーしたら、式と結果を照合

D2からD4までの式と計算結果を並べ、共通のH2と誤って動いたH3を比較する

図4|結果の数字と、式が読むセルの二つを確かめます。

解説4|最初の行だけ合っていても、確認は終わりません

D2の結果が1800になったら、D2をコピーし、D3:D4を選択して貼り付けます。コピー先を選ぶ際には入力済みのB・C列を含めず、結果を作るD列だけを対象にします。

数式例|D3で確認する式

=B3*C3*(1-$H$2)

数式例|D4で確認する式

=B4*C4*(1-$H$2)

計算結果|売上シート D1:D4

行

D列

1

売上(割引後)

2

1800

3

1350

4

2160

D3では単価1500、数量1、割引率10%を使うため1350です。D4は800×3×0.9=2160です。D2:D4の合計は5310になります。別のセルで合計を取るなら、SUMでこの三セルを指定できます。

合わないときに見る場所

症状

確認すること

二行目以降の割引が合わない

H2がH3・H4へ変わっていないか

全行が同じ商品の金額になる

B・Cの参照まで固定していないか

式ではなく数字だけになった

値だけ貼り付けをしていないか

ココさん:「最初と最後の式を見ると、コピーの方向や範囲の間違いも見つけやすいです。」数値がたまたま合うこともあるので、結果だけから参照の正しさを判断しないことが大切です。

図解5|割引率を変えて、全行を確かめる

H2を10パーセントから20パーセントへ変えたときの三明細と合計の再計算

図5|固定するのはコピー時の参照先です。H2の値は変更できます。

解説5|共通条件の変更を、一か所で反映する

H2を20%へ変更してみましょう。D2:D4は同じH2を参照しているので、各行の式を書き直さずに再計算されます。絶対参照はセルの編集を禁止する設定ではなく、式をコピーするときの参照先を指定する方法です。

計算結果|H2を20%に変更した後

明細

計算

期待する結果

商品A

1000×2×0.8

1600

商品B

1500×1×0.8

1200

商品C

800×3×0.8

1920

合計

1600+1200+1920

4720

ナマズさん:「商品が増えたら、D列の式も新しい行へコピーするんだね。」ココさん:「はい。新しい行の単価と数量を読み、H2を使っているか確認しましょう。」

今回の通常のコピー操作だけでは、今後追加される行まで自動的に式が入るわけではありません。入力を増やしたときは、式が入っている範囲も確認します。また、商品ごとに割引率が違うなら、全行でH2を使うという前提自体を見直す必要があります。

まとめ

明細ごとに変わる値は相対参照、全行に共通の値は絶対参照にすると、式の役割が読み取れる表になります。コピー後に参照先を確認し、共通条件を変更して結果を検算するところまでを一組にしましょう。

今回の仕掛け

B・Cの行番号を動かし、H2だけを固定しました。ドル記号を増やすことではなく、データの役割に合わせて参照を選ぶことが要点です。

出典と確認範囲

『Googleスプレッドシートの教科書』V044_261006、8.1.2〜8.1.4を基に独立例へ再構成。Google公式:絶対参照と相対参照。2026-10-13確認。数式の参照変換と計算値を検算しました。Googleスプレッドシート実画面での操作追試は行っていません。