この問題の解説と、これまでの解答の記録です。
A さんは，製品 N の売上の動向を分析するため，昨年と今年の売上高の推移を基に，表計算ソフトを用いて図1のワークシートを作成した。 図1の D 列の移動合計欄には，当該月を含む過去12か月の売上の合計を，E 列の売上累計欄には今年の売上高の累計を計算する式が入力されている。また，製品 N の売上の動向を視覚的に確認するため，図2の売上高の推移グラフを作成した。 図1のセルD3に入力されている計算式として，適切なものはどれか。ここで，セルD2には計算式“B14+C2-B2”が入力されており，セルD3の計算式はセルD4～D13に複写して利用する。
※ この問題は、解説作成時の検算で公表解答を確認しきれませんでした。判定は IPA 公表の解答どおりです。
B3+C3 は前年2月の売上高と当年2月の売上高を単純に足しただけで、2か月分の値にすぎない。移動合計に必要な直近12か月という範囲がどこにも表現されておらず、前の行の移動合計 D2 も参照していないため、累積を引き継ぐ更新になっていない。D4〜D13 に複写しても各行が同じ行の B と C を足すだけなので、D 列が表すべき移動合計の意味とまったく合わない。
正解 正解はイ「D2+C3-B3」。図1 の D 列は移動合計、すなわちその月を含む直近12か月の売上高の合計を並べた列である。B 列には前年の各月の売上高、C 列には当年の各月の売上高が入り、行2から行13が1月から12月、行14が年間合計にあたる。移動合計は毎回12か月分を足し直さなくても、前の行の移動合計に当年の当月の売上高を加え、12か月前にあたる前年の同じ月の売上高を差し引けば更新できる。ここが本問の勘所である。最初の行である D2 だけは一つ上に移動合計がないので、前年の年間合計を基点にして当年1月分を足し前年1月分を引くという初項専用の特別な式になる。しかし D3 以降は一つ上の行にすでに移動合計があるので、D3 は前の行の移動合計 D2 に当年2月の C3 を足し前年2月の B3 を引く、つまり D2+C3-B3 でよい。さらにこの式は D4〜D13 に複写して利用すると条件にあるので、参照はすべて相対参照でなければならない。イはすべて相対参照なので、D4 に複写すると自動的に D3+C4-B4、D5 では D4+C5-B5 と一行ずつずれ、どの行でも「前の行の移動合計＋当年当月−前年当月」という同じ意味の式になる。意味が正しく、かつ複写に耐えるのはイだけなので、イが適切である。
これは D2 に入っている初項の式をそのまま D3 に流用し、複写しても基点がずれないように B14 を絶対参照にした、という発想の罠であり、最も選ばれやすい誤答である。しかし B14 は前年の売上高の年間合計であって、移動合計の出発点である D2 を作るためだけに使う基点にすぎない。D3 以降が基点にすべきなのは一つ上の行の移動合計 D2 であって B14 ではない。実際この式を D4 に複写すると $B$14+C4-B4 となり、どの行も前年の年間合計に当月1か月分を足して前年同月1か月分を引くだけになるため、月をまたぐ差し替えが累積せず、直近12か月の合計にはならない。絶対参照は複写しても同じセルを指し続けるという性質が、ここでは裏目に出ている。
合計(B4〜B13)+合計(C2〜C3) は、D3 の行だけを見れば前年3月から12月までの10か月分と当年1月から2月までの2か月分で、たしかに合計12か月分を拾えている。しかし D3 の計算式は D4〜D13 に複写して利用するという条件を満たせない。範囲の指定が相対参照なので、D4 に複写すると 合計(B5〜B14)+合計(C3〜C4) のように範囲ごと一行ずれてしまい、月ではない年間合計の行である B14 を範囲に巻き込んでしまうからである。一行分だけ正しくても複写に耐えない式は不適切であり、前の行の結果を受け継ぐ更新式にしておけば、どの行に複写しても意味が保たれる。