This question's explanation and your answers so far.
部品メーカに勤める A さんは，材料費などの変動費と人件費などの固定費を把握し，表計算ソフトを活用して，売上高と利益を検討することにした。1 か月の売上高，変動費に固定費を加えた総費用を基に，図 1 に示すような売上高の損益分析を行うためのワークシートを作成した。 変動費は売上高に連動して増減する費用であり，変動費率は変動費の売上高に占める割合である。固定費とは，売上高にかかわらず発生する一定額の費用である。損益分岐点は売上高と総費用がちょうど等しくなる売上高であり，売上高が損益分岐点以下であれば赤字になり，それ以上になれば利益が出る。 変動費率と固定費は，それぞれセル B1，B2 に入力してある。 売上高に応じた総費用を求めるために, 図1のセルB5に計算式を入力して, セルB6～B15に複写する。セルB5に入力する計算式として, 適切なものはどれか。
The shape of the formula (sales revenue × variable cost ratio + fixed cost) is right, but B1 and B2 are still relative references. Copied to B6 it becomes A6*B2+B3, and in B7 it becomes A7*B3+B4, so the references keep shifting downward and end up pointing at unrelated (or empty) cells instead of the variable cost ratio and the fixed cost. Cells that must not move when copied need their row locked with $.
There is no term adding the fixed cost in B2, so it only computes the variable cost and does not give the “total cost.” On top of that, B1 is a relative reference, so it shifts to B2, B3, and so on when copied. Two errors are combined here, making it doubly inappropriate.
正解 In spreadsheet software, a “relative reference,” which has no $ on the row number or column letter, shifts according to the direction of copying, while an “absolute reference,” which has a $, does not shift. In this question the formula in cell B5 is copied vertically (downward) to cells B6 through B15, so only the row numbers shift. Total cost is obtained as “sales revenue × variable cost ratio + fixed cost.” The sales revenue must change from row to row as A5, A6, and so on, so A5 is left as a relative reference. On the other hand, B1, which holds the variable cost ratio, and B2, which holds the fixed cost, are single fixed cells at the top of the table, so their row numbers must be locked with $ as B$1 and B$2 to keep them from shifting to B2, B3, and so on when copied. Therefore the formula to enter in B5 is “A5*B$1+B$2,” and the correct answer is ウ. Copying it to B6 indeed gives A6*B$1+B$2, which correctly calculates the total cost for the sales revenue on the second row.
Locking the row with B$1 is correct, but the fixed cost B$2 is not added. What is required is the total cost, the sum of the variable cost and the fixed cost, so the variable cost part alone (sales revenue × variable cost ratio) is not enough.