Excel instance: ranking in interval data

An Excel form is used to make a summary of the student's test scores, as shown in Figure 1. You need to rank according to the results of the corresponding account in the AA column and place the results in the corresponding cells in the AB column.

Figure 1

Rank to use the rank function, whose syntax is "rank" where the parameter "number" is a digit that needs to be found, whereas parameter "ref" is a reference to the list of numbers, and the third argument, if omitted, is in descending order, which is exactly what we need. Number,ref,order For example, the formula "=rank (A3,A2:A6)" means to get the ranking of A3 cell data in a2:a6 cell data.

Obviously, the rank function is used in this example, but there is one problem that must be addressed. Suppose we want to arrange for the score of the Chinese discipline. The students ' Chinese total score is distributed in AA4, AA12, AA20 ... Cells, where the range of cells is not contiguous, there is one result per 8 rows. How can they be selected and used as the ranking area?

This problem has been bothering me for a long time, finally found that the use of the remainder function can be solved successfully. However, some preparatory work is to be done.

Or the ranking of Chinese grades as an example.

First copy the data of AA column to another location, and then paste it back when the preparation work is done.

First select the AA4 cell, enter the formula "=1/(MOD (), 8)-4", you can find the "#DIV/0!" after the carriage return The error message. Drag the cell fill handle down to AA379, starting with the AA4 cell, with the same error prompt on every 8 lines, and the other rows as numbers.

Click on the "Start" tab of the Ribbon "edit" feature Group "Find and select" button, click "Locate Condition" command in the pop-up menu, and open the "Locate Condition" dialog box. Select the formula single option and leave only the "error" options that appear later, as shown in Figure 2. When you are sure, you will find that the cells that are prompted by the error are in the selected state.

Figure 2

Now click on the "Define name" feature Group definition names button in the Ribbon "Formula" tab and enter "Ymzf" in the name input box of the Open New Name dialog box. OK closes the dialog box.

In the future, as long as we enter "Ymzf" in the Name box, return, you can select the AA column in all the language of the corresponding cell, as shown in Figure 3.

Figure 3

According to the above method, you can use the positioning criteria to select them as long as you can make the error prompts appear in the respective disciplines. Because the language discipline is in the same row divided by 8 of the remainder is 4, we use the formula "=1/(MOD (), 8)-4)" To create a divisor of 0 error hint. Then other disciplines, such as mathematics and English, can differ according to the remainder of the row number divided by 8, repeat the above procedure, only the formula in the denominator "-4" into "5", "6", "7", "0", "1", "2", "3" on it. Each subject area to be named "Sxzf", "Yyzf", and so on.

But it's not enough to do the prep work. Because the rank we discharge should be placed in the AB column instead of the AA column. So we're going to use it again.

Methods the corresponding regions of each subject were selected in AB column and named "Ymmc", "SXMC", "YYMC" and so on, so as to enter different formulas in these areas in the future.

So far, our preparations have been completed. Now we can paste the total score from the temporary place back into the AA column cell, and the rest is the question of ranking with rank. Let's first rank according to Chinese grades.

First in the name bar type "YWMC", carriage return, the AB column of the language of the corresponding cells are selected, when the AB372 cell will be in the active state. We simply enter the formula "=rank (AA372,YWZF)" In the edit bar and press "Ctrl+enter" to enter the formula in all selected cells and get the result of the ranking. The final results are shown in Figure 1.

The ranking of other disciplines is dealt with in this way. Easy enough, huh?

At this point, our work on the ranking of the various disciplines is done.