この問題の解説と、これまでの解答の記録です。
セル B2～D100 に学生の成績が科目ごとに入力されている。セル B102～D105 に成績ごとの学生数を科目別に表示したい。セル B102 に計算式を入力し，それをセル B102～D105 に複写する。セル B102 に入力する計算式はどれか。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 氏名 | 国語 | 英語 | 数学 |
| 2 | 山田太郎 | 優 | 可 | 可 |
| 3 | 鈴木花子 | 良 | 不可 | 良 |
| 4 | 佐藤次郎 | 可 | 優 | 優 |
| ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
| 100 | 田中梅子 | 良 | 優 | 可 |
| 101 | 成績 | 国語 | 英語 | 数学 |
| 102 | 優 | |||
| 103 | 良 | |||
| 104 | 可 | |||
| 105 | 不可 |
集計範囲が「$B2〜$B100」で、列が絶対参照・行が相対参照と、必要な指定が逆になっています。列が B に固定されるため C 列や D 列へ複写しても国語の列を数え続け、科目別になりません。さらに行が相対なので下へ複写すると範囲が B3〜B101、B4〜B102 …とずれて、見出し行 101 や集計結果のセルまで巻き込んでしまいます。条件の「=$A102」だけは正しい指定です。
集計範囲「$B2〜$B100」がアと同じ誤りで、列固定・行可動になっています。加えて条件が「=A$102」で行が 102 に固定されているため、下へ複写しても数える成績が「優」のままで、良・可・不可の行が正しく集計されません。しかも条件の列が相対なので C 列へ複写すると B$102 (集計結果セルそのもの) を条件に見てしまいます。範囲・条件の両方が誤りです。
正解 複写 (コピー) したときに参照がどう動くかを、縦横それぞれで考えるのがポイントです。B102 の式は B102〜D105 に複写されるので、横に 1 列動くたびに「集計する科目の列」が B→C→D と移り、縦に 1 行動くたびに「数える成績」が 優→良→可→不可 と移らなければなりません。集計範囲は列が動いてほしいので列は相対参照 (B のまま $ を付けない)、行は常に 2〜100 のデータ範囲を指してほしいので行は絶対参照 ($2、$100) にします。つまり「B$2〜B$100」です。一方、条件のセルは、どの科目の列でも成績の見出しがある A 列を見てほしいので列は絶対参照 ($A)、行は 102→105 と動いてほしいので行は相対参照 (102) にします。つまり「=$A102」です。両方を満たすウ「条件付個数(B$2〜B$100，=$A102)」が正解です。実際に C103 へ複写すると「条件付個数(C$2〜C$100，=$A103)」となり、英語の列で「良」の人数を数える、という狙いどおりの式になります。
集計範囲「B$2〜B$100」は正しいのですが、条件が「=A$102」で列相対・行絶対と、必要な指定が逆です。行が 102 に固定されるので下へ複写しても「優」しか数えられず、良・可・不可の行が正しくなりません。また列が相対なので C 列へ複写すると条件が B$102、D 列では C$102 となり、成績見出しのある A 列ではなく集計結果を書き込むセル自体を条件として参照してしまいます。