Use the frequency function in Excel to count the number of people in each score segment.

In the following Score list, column C indicates the score. Now we need to count the number of people in different score segments.

-----------------------------------------
Column A column B column C
1 Name student ID score
2 ***** 50
3 ***** 66

4 ***** 85.
.
.
319 ***** 93

-----------------------------------------

There are a total of 319 rows in the above Excel table. The first row is the table header, and column C stores the score, that is, C2 ~ C319 stores student scores (C2 indicates the cell in Column C of row 2nd ).

 

Count the number of such segments

 

Score segment

<60

60 ~ 69

70 ~ 79

80 ~ 89

90 ~ 100

 

That is, it is less than 60 points; 60 to 69; 70 to 79;... 90 to 100;

Perform the following steps to calculate the number of people,

(1) first, enter a series of numbers on the left of any blank cell, for example, 5th ~ Enter the following numbers (J5 ~ J9 );

Column J, column K

59
69
79
89
100

 

(2) in step 2, drag the mouse to select K5 ~ In the K9 area (column F), enter "= frequency (C2: c319, J5: j9)" in the Excel Formula window. The formula input column is as follows:

Then pressCTRL + Shift + enterKey. After the input is complete, the score is displayed in K5 ~ The K9 column is as follows:

 

59	31
69	73
79	116
89	91
100	7

The input function can only exist as an array, that is, K5 ~ The K9 area is treated as an array. Excel cannot change any of the cells. If you want to modify the cells (that is, modify the input formula), you must change them together, that is, enter a new formula in the publicity input column, and then pressCTRL + Shift + enterKey to complete the change.

 

The following describes the above example and describes the frequency function,

Frequency (C2: c319, J5: j9)

The first parameter C2: c319 of the function is the score value, that is, the score column of the score table;

The second parameter of the function, J5: j9, is the goal of statistics;

 

In the above example:
J5 = 59, indicating the number of <= J5 numbers in C2: c319, that is, the number of people whose score is <= 59, that is, <60)
J6 = 69, indicating the number of times that a number in C2: c319> J5 and <= J6 appear, that is, the number of people with a score greater than 59 and <= 69, that is, 60 ~ 70)

......

J9 = 99 indicates the number of times that a number in C2: c319> J8 and <= j9 appears, that is, the number of people whose score is greater than 89 and <= 100 (that is, 90 ~ 100)

 

(3) Finally, we select K5 ~ In the "K9" area, copy and paste the value (-> value) to another cell to remove the statistical result from the original data. Finally, the following result is obtained:

 

Score segment	Number of students
<60	31
60 ~ 69	73
70 ~ 79	116
80 ~ 89	91
90 ~ 100	7