This question's explanation and your answers so far.
セル D2 と E2 に設定した2種類の税率で，商品 A と商品 B の税込み価格を計算する。セル D4 に入力する正しい計算式は $B4*(1.0＋D$2) であるが，誤って $B4*(1.0＋$D$2) と入力した。セル D4 に入力した計算式を，セル D5，E4 及び E5 に複写したとき，セル E5 に表示される数値はどれか。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 税率 1 | 税率 2 | |||
| 2 | 税率 | 0.05 | 0.1 | ||
| 3 | 商品名 | 税抜き価格 | 税込み価格 1 | 税込み価格 2 | |
| 4 | 商品 A | 500 | |||
| 5 | 商品 B | 600 |
525 = 500×1.05 is the value of Product A (B4 = 500) multiplied by tax rate 1 (0.05), which is the number displayed in cell D4. Since E5 has its row shifted to 5 and references Product B (B5 = 600), it does not become 525, so this is wrong.
550 = 500×1.1 is the value of Product A (500) multiplied by tax rate 2 (0.1). E5 references Product B (600), and because of the mistyped formula the tax rate is also fixed at 0.05, so it does not become 550, which makes this wrong.
正解 In a cell reference, the parts marked with '$' stay fixed when the formula is copied, while parts without '$' shift their column or row according to the copy direction. When the mistyped formula $B4*(1.0＋$D$2) is copied from D4 to E5, the column moves D→E and the row moves 4→5. In the first term $B4, column B is absolute and row 4 is relative, so only the row increases by 1, pointing to $B5 (pre-tax price = 600). In the second term $D$2, both column and row are absolutely fixed, so after copying it stays $D$2 (tax rate 1 = 0.05) unchanged. Therefore E5 = $B5×(1.0＋$D$2) = 600×(1.0＋0.05) = 600×1.05 = 630, and the correct answer is 'ウ'.
660 = 600×1.1 is the value E5 would have if the correct formula $B4*(1.0＋D$2) had been used. With the correct formula the column of D$2 is relative, so when copied to column E it references E$2 (tax rate 2 = 0.1), giving 600×1.1 = 660. However, in this problem the mistyped formula fixes it absolutely as $D$2, so E5 references tax rate 1 (0.05) and becomes 630. Since 660 is the value only when no typo is made, it is wrong.