This question's explanation and your answers so far.
セル B2～D100 に学生の成績が科目ごとに入力されている。セル B102～D105 に成績ごとの学生数を科目別に表示したい。セル B102 に計算式を入力し，それをセル B102～D105 に複写する。セル B102 に入力する計算式はどれか。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 氏名 | 国語 | 英語 | 数学 |
| 2 | 山田太郎 | 優 | 可 | 可 |
| 3 | 鈴木花子 | 良 | 不可 | 良 |
| 4 | 佐藤次郎 | 可 | 優 | 優 |
| ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
| 100 | 田中梅子 | 良 | 優 | 可 |
| 101 | 成績 | 国語 | 英語 | 数学 |
| 102 | 優 | |||
| 103 | 良 | |||
| 104 | 可 | |||
| 105 | 不可 |
The counting range is “$B2:$B100”, with the column absolute and the row relative — exactly the opposite of what is needed. Because the column is pinned to B, copying to column C or D still counts the Japanese column, so the counts are not broken down by subject. And because the rows are relative, copying downward shifts the range to B3:B101, then B4:B102, and so on, dragging in header row 101 and even the cells that hold the results. Only the condition “=$A102” is specified correctly.
The counting range “$B2:$B100” has the same flaw as in ア: the column is pinned and the rows move. In addition, the condition is “=A$102”, whose row is pinned to 102, so copying downward keeps counting Excellent and the rows for Good, Pass and Fail are never counted correctly. Moreover, because the condition's column is relative, copying to column C makes it read B$102 — the result cell itself — as the condition. Both the range and the condition are wrong.
正解 The key is to think separately about how the references move horizontally and vertically when the formula is copied. The formula in B102 is copied to B102 through D105, so each time it moves one column to the right, the “column of the subject being counted” must shift from B to C to D, and each time it moves one row down, the “grade being counted” must shift from Excellent to Good to Pass to Fail. For the counting range we want the column to move, so the column is a relative reference (just B, with no $); we always want the rows to point to the data range 2 through 100, so the rows are absolute references ($2, $100). That gives “B$2:B$100”. The condition cell, on the other hand, must look at column A, where the grade labels are, no matter which subject column the formula sits in, so its column is an absolute reference ($A), and we want its row to move from 102 to 105, so its row is a relative reference (102). That gives “=$A102”. Choice ウ, 「COUNTIF(B$2:B$100, =$A102)」, satisfies both and is correct. Copying it to C103 really does produce 「COUNTIF(C$2:C$100, =$A103)」, which counts how many students earned Good in English — exactly the intended formula.
The counting range “B$2:B$100” is correct, but the condition “=A$102” has a relative column and an absolute row — the opposite of what is needed. Since the row is pinned to 102, copying downward can only count Excellent, so the rows for Good, Pass and Fail come out wrong. Also, because the column is relative, copying to column C turns the condition into B$102 and column D into C$102, referring to the very cell where the result is written instead of to column A, which holds the grade labels.