This question's explanation and your answers so far.
ラーメンチェーン店の本部で働いているAさんは、表計算ソフトを活用して全店における1日のラーメンの売上分析を行い、社長に報告することにした。図1にラーメンの売上分析を行うためのワークシートを示す。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | メニュー | 単価 | 原価 | 売上数量 | 売上高 | 粗利 | 売上比率 | 粗利率 |
| 2 | しょう油ラーメン | 500 | 350 | 525 | 262,500 | 78,750 | 55.8% | 30.0% |
| 3 | みそラーメン | 550 | 380 | 78 | 42,900 | 13,260 | 9.1% | 30.9% |
| 4 | チャーシューメン | 800 | 430 | 75 | 60,000 | 27,750 | 12.8% | 46.3% |
| 5 | 味玉ラーメン | 700 | 400 | 150 | 105,000 | 45,000 | 22.3% | 42.9% |
| 6 | 合計 | 828 | 470,400 | 164,760 | 100.0% | 35.0% |
図1 ラーメンの売上分析を行うためのワークシート メニュー別の粗利を求めるために，図1のセルF2に計算式を入力して，セルF3〜F5に複写する。セルF2に入力する計算式として，適切なものはどれか。
B2*D2 omits subtracting the cost (column C), so it becomes selling price × quantity sold = revenue. Because the cost is not subtracted, it does not yield gross profit, so it is wrong.
正解 Gross profit is calculated as (selling price − cost) × quantity sold. From the form of the formulas in the choices, column B is the selling price, column C is the cost, and column D is the quantity sold. Choice イ, (B2-C2)*D2, multiplies the per-unit gross profit (selling price − cost) by the quantity sold to compute the gross profit for each menu item. Moreover, B2, C2, and D2 are all relative references, so after entering the formula in F2 and copying it to F3 through F5, the references automatically shift to each row (rows 3, 4, and 5), and each menu row references its own selling price, cost, and quantity sold and calculates correctly. Therefore choice イ is appropriate.
(B2-C2)/D2 divides where it should multiply by the quantity sold. Dividing the per-unit gross profit (selling price − cost) by the quantity sold does not produce a meaningful value, so it cannot obtain the gross profit for each menu item, so it is wrong.
(B$2-C$2)*D$2 is a mixed reference with $ placed only on the row numbers (the row is locked). Even when copied to F3 through F5, the referenced rows stay fixed at row 2, so every menu item displays the gross profit of the first menu item (row 2). For copying row by row to calculate each menu item separately, a relative reference is needed, so it is inappropriate.