For example, it is suggested to use CountIF function and Excel to count the number of students in each fraction, but it is very troublesome to write a function in each fraction.
For example, in order to statistically display the distribution of people in C2:C56 score segments with scores less than 60, between 60 and 70, between 70 and 80, between 80 and 90, and between 90 and 100, the following five formulas should be entered:
1. Enter the formula in C58 to count the number of people below 60: = countif (C2: C56, "
2. Enter the formula in C59 to calculate the number of people between 90 points and 100: =CountIF(C2:C56, "& gt=90").
3. Enter the formula in C60 to calculate the number of people between 80 and 90: =CountIF(C2:C56, "& gt=80")-CountIF(C2:C56," & gt=90 ").
4. Enter the formula in C6 1 to calculate the number of people between 70 and 80: =CountIF(C2:C56, "& gt=70")-CountIF(C2:C56," & gt=80 ").
5. Enter the formula in C62 to calculate the number of people between 60 and 70: =CountIF(C2:C56, "& gt=60")-CountIF(C2:C56," & gt=70 ").
If you want to calculate all the scores from 0 to 10, 10 to 20, 20 to 30 ... 90 to 100, write ten formulas.
In fact, Excel has provided us with a frequency array function for frequency analysis, which allows us to easily count the number of people in each score with an array formula. For example, we need to calculate the number of people in C2 for each score from 0 to 100: C56 area:
1. Input: 0, 9.9, 19.9, ... 9.9 in B58, 100:B68.
2. Select C58 to C69 with the mouse, and enter "= frequency (C2:C56, B58:B69)" in the edit field.
3. Press "Crtl+Shift+Enter" to generate the array formula "={FreQuency(C2:C56, B58:B69)}". Note that "{}" cannot be entered manually, and the key combination of "Crtl+Shift+Enter" must be pressed to be automatically generated by the system. When finished, C58:C69 will display the score distribution as shown in the figure.