この問題の解説と、これまでの解答の記録です。
B 店で働いている A さんは，表計算ソフトを利用して当月1か月の売上分析を行い，社長に報告することにした。図1に売上分析を行うためのワークシートを示す。 A さんは，親子丼を廃止して，新たにサラダと玉子をメニューに加えることを検討するように社長から指示を受けた。サラダと玉子の売上数量のどのような組合せで，親子丼の粗利益を上回ることができるかを調べるために次のワークシートを作成した。 サラダの単価はセル B8 に，原価はセル C8 に入力する。玉子の単価はセル B9 に，原価はセル C9 に入力する。サラダの売上数量をセル C12～H12 に，玉子の売上数量をセル B13～B18 に，それぞれ 1,000 刻みで入力する。サラダと玉子の粗利益の合計が，親子丼の粗利益以上の場合は“○”，親子丼の粗利益未満の場合は“×”を，セル C13～H18 に表示する。セル C13 に入力する計算式はどれか。ここで，セル C13 の計算式は，セル C13～H18 に複写する。 なお，粗利益比率を求めているセルの表示形式はパーセント表示である。
参照の固定の仕方が逆で、複写すると別のセルを指してしまう。単価・原価が B$8, C$8, B$9, C$9 と列が相対なので、右へ複写すると C8, D8… とずれて単価・原価が読めなくなる。さらにサラダ売上数量が $C12 (列固定・行可変) なので右へ複写しても常に C 列を見てしまい、タマゴ売上数量が B$13 (行固定・列可変) なので下へ複写しても常に 13 行目を見てしまう。○ と × の並びは正しいが、参照が誤りのため不適切。
二重の誤り。まず掛け合わせが入れ替わっており、サラダの粗利益単価 ($B8-$C8) にタマゴの売上数量 B$13 を、タマゴの粗利益単価 (B$9-C$9) にサラダの売上数量 $C12 を掛けているため、粗利益の合計として意味を成さない。加えて $B8, $C8 は行が相対なので下へ複写すると B9, C9 とずれ、IF の第 2・第 3 引数が ×, ○ の順で、条件成立時 (親子丼の粗利益以上) に × を表示してしまい設問の指示と逆になる。
正解 正解は ウ。セル C13 の式は C13〜H18 に複写されるので、横 (列 C→H) と縦 (行 13→18) の両方向に動く。式の各部分が「動いてよいか/動いては困るか」を分けて考えるのがポイント。 (1) サラダとタマゴの単価・原価 ($B$8, $C$8, $B$9, $C$9) と、比較対象である親子丼の粗利益 ($F$3) は、どのセルに複写されても同じセルを見なければならない。したがって行・列とも絶対参照にする。 (2) サラダの売上数量は 12 行目に横並び (C12〜H12) なので、右へ複写したとき列は C→D→…→H と追随してほしいが、行は 12 に固定したい。よって列は相対・行は絶対で C$12 とする。 (3) タマゴの売上数量は B 列に縦並び (B13〜B18) なので、下へ複写したとき行は 13→14→…→18 と追随してほしいが、列は B に固定したい。よって列は絶対・行は相対で $B13 とする。 (4) 粗利益の合計は (単価－原価)×売上数量 の和であり、「親子丼の粗利益以上なら ○、未満なら ×」なので、条件 ≧$F$3 が真のとき第 2 引数の ○、偽のとき第 3 引数の × を返す並びにする。 以上を満たす式は IF((($B$8-$C$8)*C$12+($B$9-$C$9)*$B13)≧$F$3,「○」,「×」) であり、選択肢 ウ に一致する。
単価・原価と $F$3 の絶対参照は正しいが、売上数量の参照の固定方向が正解と入れ替わっている。サラダ売上数量を $C12 (列を C に固定・行が可変) としているため右へ複写しても C 列の値しか読まず、タマゴ売上数量を B$13 (行を 13 に固定・列が可変) としているため下へ複写しても 13 行目の値しか読まない。さらに IF の第 2・第 3 引数が ×, ○ の順なので、粗利益が親子丼以上のときに × が出てしまい表示も逆になる。