問71
頻出度★★☆
表計算ソフトを用いて，ワークシートに示す各商品の月別売上額データを用いた計算を行う。セル E2 に式“条件付個数(B2:D2,>15000)”を入力した後，セル E3 と E4 に複写したとき，セル E4 に表示される値はどれか。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品名 | 1月売上額 | 2月売上額 | 3月売上額 | 条件付個数 |
| 2 | 商品A | 10,000 | 15,000 | 20,000 | |
| 3 | 商品B | 5,000 | 10,000 | 5,000 | |
| 4 | 商品C | 10,000 | 20,000 | 30,000 |
令和2年度 ITパスポート試験 問71
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 71
Incorrectア
“0” is the result of calculating E3 (Product B, range B3:D3 = 5,000, 10,000, 5,000), i.e. the count when no value is greater than 15000. The question asks about E4, so this is incorrect.
Incorrectイ
“1” is the result of E2 (Product A, range B2:D2 = 10,000, 15,000, 20,000), where only 20,000 is greater than 15000, giving a count of 1 (15,000 does not satisfy “greater than”). This is not the value of E4, so it is incorrect.
- ウ
正解 CountIf(range, condition) is a function that counts how many cells within the specified range meet the condition. When the formula “CountIf(B2:D2,>15000)” in E2 is copied to E3 and E4, the row numbers of the range shift relatively, so in E4 the range becomes B4:D4. Row B4:D4 is for Product C, with the values 10,000, 20,000, and 30,000. Of these, 20,000 and 30,000 are the 2 values greater than 15000, so the value displayed in E4 is “2”, making ウ the correct answer.
Incorrectエ
“3” would be the count if all three values in B4:D4 were greater than 15000, but 10,000 does not meet the condition. Only 20,000 and 30,000, i.e. 2 values, meet the condition, so this is incorrect.
ここがポイント
- CountIf (COUNTIF) is a function that counts the number of cells within a specified range that meet a condition such as “>15000”.
- When a formula is copied, the range under relative referencing shifts along the row direction (B2:D2 in E2 becomes B4:D4 in E4), so the range being evaluated differs for each copy destination.
用語