問82
頻出度★★☆
セルB2～C8に学生の成績が科目ごとに入力されている。セルD2に計算式“IF(B2≧50,'合格',IF(C2≧50,'合格','不合格'))”を入力し，それをセルD3～D8に複写した。セルD2～D8において“合格”と表示されたセルの数は幾つか。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 氏名 | 数学 | 英語 | 評価 |
| 2 | 山田太郎 | 50 | 80 | |
| 3 | 鈴木花子 | 45 | 30 | |
| 4 | 佐藤次郎 | 35 | 85 | |
| 5 | 田中梅子 | 55 | 70 | |
| 6 | 山本克也 | 60 | 45 | |
| 7 | 伊藤幸子 | 30 | 45 | |
| 8 | 小林潤也 | 70 | 35 |
平成28年度 秋期 ITパスポート試験 問82
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 82
Incorrectア
2 is the number of students whose “both Math and English are 50 or higher” (Yamada Taro, Tanaka Umeko). This value results from misreading the formula as “Pass only if both are ≧50 (AND)”, but in reality the nested IF makes a student pass even when only one is 50 or higher, so it is wrong.
Incorrectイ
3 is the count of only those students whose English (column C) is 50 or higher (Yamada Taro, Sato Jiro, Tanaka Umeko). It focuses only on the inner IF condition (English ≧50) and overlooks that the outer condition (Math ≧50) also produces a Pass, so the count is too low.
Incorrectウ
4 is the count of only those students whose Math (column B) is 50 or higher (Yamada Taro, Tanaka Umeko, Yamamoto Katsuya, Kobayashi Junya). It looks only at the outer IF (Math ≧50) and misses Sato Jiro, who passes on an English score of 85 despite Math below 50, so it is short by one.
- エ
正解 The formula 「IF(B2≧50,'Pass',IF(C2≧50,'Pass','Fail'))」 first checks whether Math (column B) is 50 or higher; if true it returns “Pass”. If false, it then checks whether English (column C) is 50 or higher; if true it returns “Pass”, and only when both are below 50 does it return “Fail”. In other words, the logic is “Pass if either Math or English is 50 or higher” (an OR condition). When the formula in D2 is copied to D3 through D8, the relative references evaluate columns B and C of each row. Judging each student: Yamada Taro (Math 50) = Pass, Suzuki Hanako (45/30) = Fail, Sato Jiro (Math 35, English 85) = Pass, Tanaka Umeko (Math 55) = Pass, Yamamoto Katsuya (Math 60) = Pass, Ito Sachiko (30/45) = Fail, Kobayashi Junya (Math 70) = Pass. Five cells display “Pass”, so the correct answer is エ (5).
ここがポイント
- A nested IF function, IF(condition1, true, IF(condition2, true, false)), behaves like an OR test: “the result is true if either condition1 or condition2 is true”. In this problem, a student shows “Pass” if either Math or English is 50 or higher.
- When a formula is copied, the relative references automatically shift the row numbers of the referenced cells, so D3 refers to B3 and C3, D8 refers to B8 and C8, and each row's values are evaluated accordingly.
用語