問76
頻出度★★☆
商品の税込価格を計算する表計算のワークシートがある。セル B1には消費税率が入力されており，セル B4～B6には税抜価格が入力されている。セル C4～C6に税込価格を表示するために，セル C4に式を入力し，セル C5，C6に複写する。セル C4に入力する式はどれか。ここで，セル B1は，パーセント形式で表示している。
| A | B | C | |
|---|---|---|---|
| 1 | 消費税率 | 10% | |
| 2 | |||
| 3 | 商品名 | 税抜価格（円） | 税込価格（円） |
| 4 | 商品X | 200 | |
| 5 | 商品Y | 500 | |
| 6 | 商品Z | 100 |
令和7年度 ITパスポート試験 問76
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 76
Incorrectア
«B$4＊(1+B$1)» writes the reference to the tax-excluded price as «B$4» with the row locked. With this, even when C4 is copied to C5 and C6, the reference stays at B4 and does not move, so C5 and C6 also use Product X's tax-excluded price of 200. The tax-excluded price should reference the value in its own row, so the row must not be locked.
Incorrectイ
«B$4＊(1+B1)» writes the tax-excluded price as «B$4» with the row locked, which is the same mistake as ア (still 200 after copying); on top of that, it writes the consumption tax rate as the relative reference «B1», so when copied down the reference shifts to B2 and B3 and cannot reference the consumption tax rate correctly. It is wrong on two counts.
- ウ
正解 The tax-included price is found by «price excluding tax ×(1+consumption tax rate)». The price excluding tax is in column B of that same row (B4 for C4). When you copy the formula in C4 down to C5 and C6, you want the reference to the tax-excluded price to move down along with it, becoming B4→B5→B6, so you use the relative reference «B4» that does not lock the row. In contrast, the consumption tax rate is always in cell B1, so to keep the reference from shifting when copied, you lock the row number with «$» and use the absolute (mixed) reference «B$1». Therefore the formula to enter in C4 is «B4＊(1+B$1)», which is choice ウ. When ウ is actually copied, C5 becomes «B5＊(1+B$1)» and C6 becomes «B6＊(1+B$1)», correctly yielding the tax-included prices 220, 550, and 110.
Incorrectエ
In «B4＊(1+B1)», the tax-excluded price «B4» is a correct relative reference, but the consumption tax rate is written as the relative reference «B1». When C4 is copied to C5 and C6, the reference to the consumption tax rate shifts to B2 and B3, treating empty cells as the tax rate, so the tax-included price cannot be obtained correctly. The consumption tax rate must use «B$1» with the row locked.
ここがポイント
- Relative and absolute references in spreadsheets: for cells whose reference you do not want to change when copied, lock them by adding «$» before the row and/or column number (absolute/mixed reference); for cells you want to move along with the copy, use a relative reference without «$».
- When copying vertically (downward), the row number shifts, so for references whose row you want to keep fixed, add «$» before the row number (consumption tax rate B1 → B$1; the price excluding tax uses its own row, so it stays B4).
用語