This question's explanation and your answers so far.
B 店で働いている A さんは，表計算ソフトを利用して当月1か月の売上分析を行い，社長に報告することにした。図1に売上分析を行うためのワークシートを示す。 粗利益合計に対するメニュー別粗利益の占める比率（以下，粗利益比率という）を求めるために，次のワークシートのセル G2 に計算式を入力して，セル G3～G5 に複写する。セル G2 に入力する計算式はどれか。 なお，粗利益比率を求めているセルの表示形式はパーセント表示である。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | メニュー | 単価 | 原価 | 売上数量 | 売上高 | 粗利益 | 粗利益比率 |
| 2 | 牛丼 | 400 | 200 | 4,800 | 1,920,000 | 960,000 | 30.9% |
| 3 | 親子丼 | 450 | 250 | 2,310 | 1,039,500 | 462,000 | 14.9% |
| 4 | カツ丼 | 500 | 350 | 5,350 | 2,675,000 | 802,500 | 25.8% |
| 5 | カレー | 400 | 150 | 3,520 | 1,408,000 | 880,000 | 28.3% |
| 6 | 合計 | 15,980 | 7,042,500 | 3,104,500 | 100.0% |
F2/E2 is “gross profit ÷ sales amount”, which gives not the share of the total gross profit but the proportion of gross profit relative to that item’s own sales amount (the gross profit margin). It yields 960,000÷1,920,000 = 50.0%, which does not match the 30.9% shown in G2 of the table. This formula mistakes the denominator of the ratio that the question asks for.
F2/F6 does return the correct value 30.9% for G2 by itself, but the denominator is left as a relative reference. When it is copied to G3 through G5, the denominator also shifts down one row at a time and points to the empty cells F7, F8, and F9, so G3 onward cannot be calculated correctly (it results in division by zero or an error). It is inappropriate for this question, which assumes the formula is copied.
正解 The gross profit ratio is obtained as “each menu item’s gross profit ÷ total gross profit”. In the row for the beef bowl, the numerator is F2 (960,000) and the denominator is F6 (3,104,500), which holds the total gross profit. Since 960,000÷3,104,500 ≈ 0.3092, the percentage display shows 30.9%, matching the value of G2 in the table. The key point here is that the formula in G2 is copied to G3 through G5. In spreadsheet software, when a formula is copied, cell addresses written as relative references shift by the amount of the movement. The numerator F2 should shift down one row at a time to become F3, F4, and F5, so it is left as a relative reference. The denominator F6, the total gross profit, must point to the same cell in every row, so a $ is placed before the row number 6 to make it F$6, fixing the row (turning it into an absolute reference). With this, G3 contains F3/F$6, G4 contains F4/F$6, and G5 contains F5/F$6, so the gross profit ratio is calculated correctly in every row. Therefore “ウ F2/F$6” is the correct answer.
In F2/sum(E$2～E$5), the denominator is 7,042,500, the total of column E (sales amount). It yields 960,000÷7,042,500 ≈ 13.6%, which does not match the 30.9% in G2. Fixing the rows with $ does survive copying, but because it divides by the total sales amount rather than the total gross profit (column F), it does not give the gross profit ratio that is required.