The range B$2 ~ B$36 is correct, but the condition is written as A$40, making the row absolute and the column relative. As a result, when the formula is copied downward the reference stays at A40 and never moves, so B41 through B50 also count «the number of students with 0 points», and the rows for 1 to 10 points all show the same value. Furthermore, when copied to the right only the column shifts, giving B$40, C$40, …, G$40, so the condition refers to cells of the distribution table itself instead of column A, where the scores are. The $ marks are placed the opposite way round (vertically vs. horizontally) from the correct answer.
○イ
正解The correct answer is イ, COUNTIF(B$2 ~ B$36, = $A40). In spreadsheets, $ means «make that part an absolute reference (it does not move when the formula is copied)», while a part without $ is a relative reference and shifts automatically by the direction and distance of the copy. In this question, the formula entered in B40 is copied both to the right (column B → column G) and downward (row 40 → row 50), so for the range and for the condition you only need to ask «in which direction do I want it to shift?». The first argument, the range to be counted, is rows 2 to 36 of columns B to G, where each student's score for each class is stored. When copied to the right, the column should shift as class P (column B) → class Q (column C) → … → class U (column G), so the column is relative (it stays as B); when copied downward, the target must always stay in rows 2 to 36, so the rows are absolute ($2, $36) ⇒ B$2 ~ B$36. The score referred to by the second argument, the condition, is in rows 40 to 50 of column A. When copied downward, the row should shift as 0 points → 1 point → … → 10 points, so the row is relative (40); even when copied to the right, the score must always be read from column A, so the column is absolute ($A) ⇒ $A40. The formula is therefore COUNTIF(B$2 ~ B$36, = $A40). For example, copying this formula to D42 gives COUNTIF(D$2 ~ D$36, = $A42), which correctly counts how many students in class R (column D) scored 2 points.
×Incorrectウ
Both the range and the condition have their $ marks the wrong way round, so it is doubly wrong. In the range $B2 ~ $B36 the column is absolute, so even when copied to the right it keeps counting class P (column B); and because the rows are relative, copying downward gives $B3 ~ $B37, …, and at row 50 it becomes $B12 ~ $B46, so the target rows shift, pulling in row 37 and beyond (and even the area of the distribution table itself if copied far enough) and breaking the counting range. In the condition A$40 the row is also absolute, so every row can only count the number of students with 0 points, and copying to the right shifts the column so that it refers to cells of the distribution table itself.
×Incorrectエ
The condition $A40 is correct, but the $ marks in the range $B2 ~ $B36 are placed wrongly. Because the column is absolute, even when copied to column C and beyond it keeps counting the scores of class P (column B), so the columns for classes Q to U all show the same value as class P. In addition, because the rows are relative, copying downward shifts the counting range one row at a time to $B3 ~ $B37, …, and at row 50 to $B12 ~ $B46, so students in the upper rows are missed while row 37 and beyond (and even the area of the distribution table itself if copied far enough) get pulled in.
ここがポイント
Absolute vs. relative references: in a cell reference, the part marked with $ (the column letter or the row number) stays fixed when the formula is copied, while a part without $ shifts by the direction and distance of the copy. B$2 means «the column moves, the row does not», and $A40 means «the column does not move, the row does».
How to decide where the $ goes from the copy direction: for a formula that is copied both horizontally and vertically, judge each reference separately by asking «may the column shift when copied to the right?» and «may the row shift when copied downward?». Leave off the $ on the side you want to move, and put the $ only on the side you want to fix. COUNTIF (called 条件付個数 in the exam notation) is a function that returns the number of cells within the search range given as the first argument that satisfy the condition given as the second argument, and it is used to build frequency distribution tables.