問55
頻出度★★☆
セル D2 と E2 に設定した 2 種類の仮の消費税率でセル A4 と A5 の商品の税込み価格を計算するために，セル D4 に入れるべき計算式はどれか。ここで，セル D4 に入力した計算式は，セル D5，E4 及び E5 に複写して使うものとする。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 消費税率1 | 消費税率2 | |||
| 2 | 税率 | 0.1 | 0.2 | ||
| 3 | 商品名 | 税抜き価格 | 税込み価格1 | 税込み価格2 | |
| 4 | 商品A | 500 | 550 | 600 | |
| 5 | 商品B | 600 | 660 | 720 |
平成22年度 春期 ITパスポート試験 問55
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 55
Incorrectア
B4*(1.0+D2) uses only relative references, so both references shift when the formula is copied. Copied to E4 it becomes C4*(1.0+E2), which reads the empty cell C4 as the pre-tax price. Copied to D5 it becomes B5*(1.0+D3), which refers not to the tax rate but to D3, the heading cell containing “Tax-incl. price 1”. Neither the column nor the row that should be fixed is fixed at all.
Incorrectイ
In B$4*(1.0+D$2) the tax-rate part D$2 is correct, but the pre-tax price is fixed the wrong way round. It fixes the row of B instead of the column, so copied to E4 it becomes C$4 and refers to the empty column C, and copied to D5 it stays B$4 and refers to Product A’s 500, so Product B’s 660 cannot be obtained.
- ウ
正解 The correct answer is ウ, “$B4*(1.0+D$2)”. In spreadsheet software, a reference without $ (a relative reference) changes automatically to match the direction and distance the formula is copied, while a part marked with $ (an absolute reference) does not change when copied. In this question, the formula in D4 is copied into three cells: D5 (one row down), E4 (one column right), and E5 (down and to the right). First, the pre-tax price must always be read from column B no matter which column the formula is copied to, but the row must change between Product A (row 4) and Product B (row 5). So only the column is fixed, giving $B4. Next, the tax rate must always be read from row 2 no matter which row the formula is copied to, but the column must change: D2 for Tax-incl. price 1 and E2 for Tax-incl. price 2. So only the row is fixed, giving D$2. Combining the two gives $B4*(1.0+D$2); entering it in D4 and copying it produces D4 = $B4*(1.0+D$2) = 500*1.1 = 550, D5 = $B5*(1.0+D$2) = 600*1.1 = 660, E4 = $B4*(1.0+E$2) = 500*1.2 = 600, and E5 = $B5*(1.0+E$2) = 600*1.2 = 720, matching all four values shown in the table in the figure.
Incorrectエ
$B$4 fixes both the row and the column, so even when copied to D5 or E5 it keeps referring to Product A’s 500, and Product B’s 660 and 720 cannot be calculated. Also, $D2 fixes the column and leaves the row relative, so copied to E4 it stays $D2 (tax rate 0.1) and Tax rate 2 is not reflected, while copied to D5 it refers not to the tax rate but to $D3, the heading cell containing “Tax-incl. price 1”. The two mistakes are different in nature: compared with the correct $B4, $B$4 over-fixes by locking the row as well, while compared with the correct D$2, $D2 puts the $ on the wrong side.
ここがポイント
- Spreadsheet references come in two kinds: relative references (B4) that shift as the formula is copied, and absolute references ($B$4) that are fixed with $; a reference that fixes only the column or only the row, such as $B4 or D$2, is called a mixed reference. It is easier to judge if you remember that $ fixes whatever symbol comes immediately after it ($B4 fixes column B, D$2 fixes row 2).
- For a table where the formula is copied both downward and to the right, the standard approach is to treat the two directions separately: put $ on the row for what must not change when copying downward (row 2, which holds the tax rates), and put $ on the column for what must not change when copying to the right (column B, which holds the pre-tax prices). Finally, apply the formula to every destination cell and check that the results match the values shown in the figure; this pins the answer down reliably.
用語