表計算のモデル設計：相対参照・絶対参照・検算セル
架空の行事予算を使い、入力・計算・検算を分けて設計する。コピーで動く参照と固定する参照を区別し、合計の取りこぼしを別経路の計算で見つける。
- 単位と前提を定め、入力セル・計算セル・検算セルの役割を分けられる
- 数式のコピー先から、相対参照・絶対参照・複合参照の変化を説明できる
- 別経路で計算する検算セルを設計し、検算が一致しても残る限界を説明できる
- 入力条件を変えたときの予想と表計算の結果を比較できる
📑 目次
なぜ「答えの表」ではなく「関係の表」を作るのか
行事の人数が変わるたびに電卓で全品目を計算し直すと、直し忘れが起こります。表計算の強みは、結果の数字を並べることではなく、「数量が変われば小計も変わる」という関係を保存できることです。料理の分量表のように、変える材料と計算の手順を分けると、条件の変更にも対応できます。
ここでは自作の架空の行事予算だけを扱います。実際の価格や税制度を示すものではありません。単価は必要な費用を含む円／個、数量は0以上の整数個とし、送料・数量割引はありません。追加出費に備えて全品目に同じ予備費率を掛けます。予備費は実支出ではなく、あらかじめ確保する金額です。
まず入力・計算・検算の住所を決める
セルの住所は列の文字と行の番号で表します。B2ならB列の2行目です。後から条件を変えやすいように、数量と単価はB・C列、全体に共通する条件はH列にまとめます。以下は同じシート内の配置です。D・E列には結果の数値を直接入力せず、これから説明する数式を入れます。
| 行 | A列：品目 | B列：数量（個） | C列：単価（円／個） | D列：基礎小計（円） | E列：予備費込み（円） |
|---|---|---|---|---|---|
| 1 | 品目 | 数量 | 単価 | 基礎小計 | 予備費込み |
| 2 | 紙セット | 20 | 120 | 計算セル | 計算セル |
| 3 | 飾りセット | 12 | 250 | 計算セル | 計算セル |
| 4 | 展示台部品 | 5 | 400 | 計算セル | 計算セル |
| 5 | 合計 | 空欄 | 空欄 | D2:D4の合計 | E2:E4の合計 |
| セル | 内容 | 入力または役割 |
|---|---|---|
| H2 | 予備費率 | 10%（内部の数値は0.10）を入力 |
| H3 | 予算上限（円） | 9000を入力 |
| H5 | 基礎小計の検算差（円） | 別経路の合計とD5との差を計算 |
| H6 | 予備費込みの検算差（円） | E5と、D5から求めた金額との差を計算 |
| H7 | 予算残額（円） | 予算上限から予備費込み合計を引く |
相対参照：同じ仕事を次の行でもする
D2にとってB2は「同じ行の2列左」、C2は「同じ行の1列左」です。この位置関係を保つのが相対参照です。D2に次の式を入れ、D3、D4へ下方向にコピーすると、各行の数量と単価を掛ける仕事が繰り返されます。数式は先頭の = から入力します。
| 数式セル | コピー後の数式 | 結果（円） |
|---|---|---|
| D2 | =B2*C2 | 2400 |
| D3 | =B3*C3 | 3000 |
| D4 | =B4*C4 | 2000 |
D5は =SUM(D2:D4) として7400円です。コロンは両端を含む連続範囲を表します。単位でも確かめると、個 × 円／個 = 円。数量と単価を取り違えず、何を合計しているのかを説明できる式になっています。
絶対参照：全行が同じ条件を見に行く
予備費率は品目ごとではなく行事全体の条件です。どの行もH2を見る必要があるため、H2まで相対的に動かしてはいけません。列と行の直前に半角のドル記号を付けると、コピー時にその部分を固定できます。これを絶対参照といいます。E2には次の式を入れ、E4まで下へコピーします。
D2はD3、D4と変わりますが、H2の参照先は変わりません。1を足す理由は、基礎小計の100%に予備費の10%を上乗せするからです。H2を10にすると1000%を意味します。10%または0.10を入力し、表示形式も確認しましょう。
上の入力でE2:E4を計算し、E5を =SUM(E2:E4)、H7を =H3-E5 とする。合計と残額はいくらか。E2のH2を固定しなかった場合、E3はどのセルを見てしまうか。
解答・解説
E2 = 2400 × 1.10 = 2640円、E3 = 3000 × 1.10 = 3300円、E4 = 2000 × 1.10 = 2200円。E5は8140円、H7は9000 − 8140 = 860円です。固定しない式 =D2*(1+H2) を1行下へコピーすると =D3*(1+H3) になり、率ではなく9000円の入力を読んでしまいます。見た目の書式だけではこの単位の誤りは防げません。
複合参照：片方の方向だけを固定する
行方向に品目、列方向に別の予備費率を並べて比べたいなら、片方向だけ固定すると便利です。これを複合参照といいます。たとえば別の小表でB10:B12に基礎小計2400、3000、2000、C9に10%、D9に20%を置きます。C10は「基礎小計はB列から」「率は9行目から」と決め、次の式を右や下へコピーします。
Bの前のドル記号は列だけを固定し、10行目は下へのコピーで変わります。9の前のドル記号は行だけを固定し、C列は右へのコピーで変わります。D11へ1列右・1行下にコピーした結果は次の式となり、3000 × 1.20 = 3600円を返します。全部固定するのではなく、「どの方向には動いてほしいか」を考えます。
検算セル：同じ道を二度通らない
合計の式を別のセルにそのまま複写しても、同じ範囲の抜けを再現するだけです。検算では別の道を用意します。H5では数量列と単価列を直接掛けて合計し、D5との差を取ります。SUMPRODUCTは同じ長さの範囲の対応する要素を掛けて足す関数です。ここでは3組の数量と単価に使います。
H6では各品目に予備費を加えてから足したE5と、合計D5にまとめて予備費を加えた値を比べます。全品目の率が同じで途中で丸めないなら、分配法則により二つは一致します。
qは数量（個）、pは単価（円／個）、rは単位のない共通の予備費率です。検算差H5・H6は原則0円を期待します。一方H7は残額なので、0円である必要はありません。正なら予算に余裕があり、負ならこの条件では予算超過です。「検算一致」と「予算内」は別の判定です。
誤ってD5だけを =SUM(D2:D3) とし、E5は正しく8140円になっているとする。H5とH6はどうなるか。どこを直すべきか。
解答・解説
誤ったD5は2400 + 3000 = 5400円です。H5は7400 − 5400 = 2000円、H6は8140 − 5400 × 1.10 = 2200円です。両方の差が0でないため不整合を疑えます。D4の2000円が合計範囲から抜けているので、D5を =SUM(D2:D4) に戻します。検算セルを0と手入力して消すのではなく、参照範囲を直します。差の値だけで原因は一意に決まらないため、実際の数式も確認します。
結果を見る前に、変化を予想して試す
- 予備費率だけを0%にする：E列はD列と一致し、合計7400円になるはず。確認後は10%へ戻す。
- 紙セットの数量だけを20個から21個にする：増加額は120 × 1.10 = 132円のはず。合計8272円、残額728円になることを確かめ、数量を戻す。
- すべての数量だけを0個にする：固定費なしという今回の前提なら合計0円、残額9000円になるはず。確認後は元の数量へ戻す。
- 表に品目を追加する場合：コピーした式だけでなく、合計範囲と検算範囲にも新しい行が含まれるか確認する。
D2の式 =B2*C2 をD4へコピーするとどうなるか。またD2の式をE2へコピーするとどうなるか。
解答・解説
D4では =B4*C4。2行下へ移ったため、両方の行番号が2増えます。E2では =C2*D2。1列右へ移ったため、両方の列が1列右になります。横へコピーしても「数量×単価」の意味が自動で保たれるわけではありません。
元の予算表でH2だけを20%に変更した。E5とH7はいくらか。検算が一致していれば予算内といえるか。
解答・解説
E5は7400 × 1.20 = 8880円、H7は9000 − 8880 = 120円。この場合は予算内ですが、それはH7が0以上だからです。検算差が0であることだけでは、予算内かどうかは分かりません。
C9が10%、D9が20%の小表で、本文のC10の複合参照式をD12にコピーする。参照する基礎小計と率、および結果を答えよ。
解答・解説
基礎小計はB12の2000円、率はD9の20%です。B列と9行目を固定し、行は2行下、率の列は1列右へ移るためです。結果は2000 × 1.20 = 2400円です。
本来C3は250円なのに、入力が300円になっていた。D・E列と合計・検算の数式はすべて正しい。H5とH6が0に近ければ、入力も正しいといえるか。
解答・解説
いえません。D3は12 × 300 = 3600円、基礎合計は8000円、予備費込み合計は8800円になります。同じ誤入力を検算にも使うのでH5・H6は理論上0のままです。元の品目・単価表との照合で入力を点検します。
問1. 数式を下へコピーしても、共通の予備費率H2の列・行をどちらも動かしたくない。適切な参照はどれか。
問2. D5の合計にD4が含まれていない誤りを、別経路で見つけやすい検算はどれか。
問3. 検算差H5・H6は許容差内で、予算残額H7は−500円だった。正しい説明はどれか。
問4. 各品目の予備費込み金額の合計と、基礎合計に一括して予備費を加えた金額が、理論上必ず一致する条件はどれか。