This question's explanation and your answers so far.
B 店で働いている A さんは，表計算ソフトを利用して当月1か月の売上分析を行い，社長に報告することにした。図1に売上分析を行うためのワークシートを示す。 A さんは，親子丼を廃止して，新たにサラダと玉子をメニューに加えることを検討するように社長から指示を受けた。サラダと玉子の売上数量のどのような組合せで，親子丼の粗利益を上回ることができるかを調べるために次のワークシートを作成した。 サラダの単価はセル B8 に，原価はセル C8 に入力する。玉子の単価はセル B9 に，原価はセル C9 に入力する。サラダの売上数量をセル C12～H12 に，玉子の売上数量をセル B13～B18 に，それぞれ 1,000 刻みで入力する。サラダと玉子の粗利益の合計が，親子丼の粗利益以上の場合は“○”，親子丼の粗利益未満の場合は“×”を，セル C13～H18 に表示する。セル C13 に入力する計算式はどれか。ここで，セル C13 の計算式は，セル C13～H18 に複写する。 なお，粗利益比率を求めているセルの表示形式はパーセント表示である。
The way the references are fixed is the other way around, so copying the formula makes it point to different cells. The unit prices and costs are written as B$8, C$8, B$9, C$9, whose columns are relative, so copying to the right shifts them to C8, D8, ... and the unit prices and costs can no longer be read. Moreover, the sales quantity of the salad is $C12 (column fixed, row variable), so even when copied to the right it always looks at column C, and the sales quantity of the egg is B$13 (row fixed, column variable), so even when copied downward it always looks at row 13. The order of ○ and × is correct, but the references are wrong, so this choice is inappropriate.
There is a double error. First, the multiplications are swapped: the per-unit gross profit of the salad ($B8-$C8) is multiplied by the sales quantity of the egg B$13, and the per-unit gross profit of the egg (B$9-C$9) is multiplied by the sales quantity of the salad $C12, which makes no sense as a total gross profit. In addition, the rows of $B8 and $C8 are relative, so copying downward shifts them to B9 and C9, and the second and third arguments of IF are in the order ×, ○, so when the condition holds (greater than or equal to the gross profit of the oyakodon) it displays ×, the opposite of what the question instructs.
正解 The correct answer is ウ. The formula in cell C13 is copied to cells C13 to H18, so it moves both horizontally (columns C to H) and vertically (rows 13 to 18). The key is to separate the parts of the formula that may shift from the parts that must not shift. (1) The unit prices and costs of the salad and the egg ($B$8, $C$8, $B$9, $C$9), together with the gross profit of the oyakodon used for comparison ($F$3), must refer to the same cells no matter where the formula is copied. Therefore both the row and the column are made absolute. (2) The sales quantities of the salad are lined up horizontally in row 12 (C12 to H12), so when the formula is copied to the right the column should follow along as C, D, ..., H, while the row must stay fixed at 12. Hence the column is relative and the row is absolute, giving C$12. (3) The sales quantities of the egg are lined up vertically in column B (B13 to B18), so when the formula is copied downward the row should follow along as 13, 14, ..., 18, while the column must stay fixed at B. Hence the column is absolute and the row is relative, giving $B13. (4) The total gross profit is the sum of (unit price － cost) × sales quantity, and because the question says “display ○ if it is greater than or equal to the gross profit of the oyakodon, and × if it is less”, the formula must return the second argument ○ when the condition ≧$F$3 is true and the third argument × when it is false. The formula that satisfies all of these is IF((($B$8-$C$8)*C$12+($B$9-$C$9)*$B13)≧$F$3,“○”,“×”), which matches choice ウ.
The absolute references for the unit prices, the costs and $F$3 are correct, but the direction in which the sales quantity references are fixed is swapped compared with the correct answer. The sales quantity of the salad is written as $C12 (the column is fixed at C and the row varies), so even when copied to the right it reads only the values in column C, and the sales quantity of the egg is written as B$13 (the row is fixed at 13 and the column varies), so even when copied downward it reads only the values in row 13. Furthermore, the second and third arguments of IF are in the order ×, ○, so when the gross profit is greater than or equal to that of the oyakodon it displays ×, and the output is reversed as well.