問80
頻出度★★☆
表計算ソフトを用いて，買い物金額に応じたポイント数を計算する。買い物金額が1,000円以下では買い物金額の1%，買い物金額が1,000円を超え3,000円以下では買い物金額の2%，買い物金額が3,000円を超える場合は買い物金額の3%のポイントを付与する。ワークシートのセルA2に買い物金額が入力されるとき，ポイント数が表示されるセルB2に入る数式はどれか。ここで，ポイント数の小数点以下は切捨てとする。
| A | B | |
|---|---|---|
| 1 | 買い物金額 | ポイント数 |
| 2 | 3,350 | 100 |
平成28年度 春期 ITパスポート試験 問80
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 80
Incorrectア
The test order is “A2≧3000 → 3%”, “A2≧1000 → 1%”, so the direction of the boundaries is wrong to begin with. In the specification exactly 3,000 yen should be 2%, but with A2≧3000, 3,000 yen becomes 3%. Furthermore, it assigns 1% (INT(A2/100)) to the “more than 1,000 yen up to 3,000 yen” band and 2% (INT(A2*2/100)) to “1,000 yen or less”, so the 1% and 2% assignments are swapped.
Incorrectイ
The leading “A2＞3000 → 3%” is correct, but the following “A2＞1000 → INT(A2/100) = 1%” is wrong. The band of more than 1,000 yen and 3,000 yen or less should be 2%, yet it returns 1%. The last band (A2≦1000) is also assigned 2% (INT(A2*2/100)), so the 1% and 2% award rates are reversed.
- ウ
正解 The boundaries of the award rate are “1,000 yen or less = 1%”, “more than 1,000 yen and 3,000 yen or less = 2%”, and “more than 3,000 yen = 3%”, and in every case the key is which category each boundary value falls into. Choice ウ has the form IF(A2≦1000, INT(A2/100), IF(A2≦3000, INT(A2*2/100), INT(A2*3/100))): it first tests “A2≦1000” and returns 1%, and when this is false (i.e., more than 1,000 yen) it tests “A2≦3000” and returns 2%, and if that is also false (i.e., more than 3,000 yen) it returns 3%. Because passing the first test already fixes “more than 1,000 yen”, the second test “A2≦3000” automatically represents “more than 1,000 yen and 3,000 yen or less”, correctly matching the specification's categories. The truncation is handled by the integer-part (INT) function. Since A2＝3,350 is more than 3,000 yen, INT(3350*3/100)＝INT(100.5)＝100, which also matches B2＝100 in the table. Therefore the correct answer is ウ.
Incorrectエ
Where it should test with “or less (≦)”, it tests with “less than (＜)” — that is “A2＜1000” and “A2＜3000” — so the handling of the boundary values deviates from the specification. Exactly 1,000 yen should be 1%, but A2＜1000 is false, so it is treated as 2%. Likewise exactly 3,000 yen should be 2%, but A2＜3000 is false, so it is treated as 3%.
ここがポイント
- In multi-level conditional branching (nested IF), the outer test already fixes one end of the range, so the inner test can correctly represent an interval using only a single-sided threshold. The standard practice is to arrange the tests in order starting from one end of the range (the smaller or the larger side).
- The key is to strictly distinguish the direction of the boundaries: “or less (≦)”, “less than (＜)”, and “more than (＞)”. Which category the boundary values (1,000 and 3,000) fall into determines right from wrong. Truncation of the fractional part is done with the integer-part (INT) function.
用語