問98
頻出度★★☆
表計算ソフトを用いて，二つの科目X，Yの成績を評価して合否を判定する。それぞれの点数はワークシートのセルA2，B2に入力する。合計点が120点以上であり，かつ，2科目とも50点以上であればセルC2に“合格”，それ以外は“不合格”と表示する。セルC2に入れる適切な計算式はどれか。
| A | B | C | |
|---|---|---|---|
| 1 | 科目X | 科目Y | 合否 |
| 2 | 50 | 80 | 合格 |
平成31年度 春期 ITパスポート試験 問98
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 98
- ア
正解 A pass requires all three conditions — the total score (A2+B2) is 120 points or higher, subject X is 50 points or higher, and subject Y is 50 points or higher — to hold together (all of them), so the conditions are joined with a logical AND. Because the IF function is written in the order IF(condition, value when true, value when false), you place 'Pass' when the condition is true and 'Fail' when it is false. Therefore IF(AND((A2+B2)≧120, A2≧50, B2≧50), 'Pass', 'Fail') is correct. In fact, in the example A2=50, B2=80, the total 130≧120 and both subjects are 50 points or higher, so the AND is true, C2 displays 'Pass', which matches the figure.
Incorrectイ
Joining the conditions with a logical AND is itself correct, but the second and third arguments of IF are swapped, so it displays 'Fail' when the condition is true and 'Pass' when it is false. Because it outputs 'Fail' for a passer who satisfies all three conditions, the judgment result is reversed.
Incorrectウ
Because it uses a logical OR, it judges 'Pass' if even one of the three conditions holds. For example, even if one subject is below 50 points, it becomes a pass as long as the total is 120 points or higher, so it cannot express the AND condition that both subjects are 50 points or higher.
Incorrectエ
In addition to joining the conditions with a logical OR, it also has the display positions reversed — 'Fail' when true and 'Pass' when false — so there is a double error in both the type of the logical operator and the display order.
ここがポイント
- 'AND (satisfy all conditions)' is expressed with a logical AND, and 'OR (satisfy any one condition)' with a logical OR. The 'total 120 points or higher AND both subjects 50 points or higher' in the question is a logical AND.
- The IF function follows the order IF(condition, value when the condition is true, value when false). The true result and the false result must be placed in the correct positions.
用語