shown in Figure 1, Excel forms to student test scores hair matrix, the column need to AA results in the corresponding subjects discharged ranking, and the results put out the corresponding AB cell.

Rank

scheduled to use the RANK function, its syntax is "RANK (number, ref, order)" where the parameter "number" is the need to find the number of ranking; the parameter "ref" is a reference list of numbers; If you omit the third parameter is the descending order, that is what we need. For example the formula "= RANK (A3, A2: A6)" means the cell is to get the data A3 A2: A6 cell data in the rankings.

Clearly, in this case to use the RANK function, but there is a problem that must be addressed. Suppose we want to score for the language subjects to the position. Total score of the students of the language found in AA4, AA12, AA20 ... ... and so the cell, in which not a continuous range of cells, each one 8-line results. How will they select and rank the regions as it?

this problem has plagued me for a long time, finally found the number of functions by solving the remaining problems can be resolved smoothly. However, some preparatory work is done.

or in the language scores of Rankings. AA

first column of data copied to another location, such as preparation work is completed before pasting back.

first select AA4 cells, enter the formula "= 1 / (MOD (ROW (), 8) -4)", Enter can be found there after the "# DIV / 0!" the error message. Drag the fill handle down to cell AA379, will start from the AA4 cells, each 8-line the same error occurs, the other lines are digital. Ribbon

Click "Start" tab, "Edit" function group "find and select the" button in the pop-up menu click on "Positioning conditions" command, open the "positioning conditions" dialog box. Select the "formula" one option, and keep only select the resulting "error" recovery option, shown in Figure 2. Can be found after determining that any error prompts the cell to be selected in the.

$ show_page $ Now click on the function of area "formula" tab, "the name of the definition of" functional group "definition name" button, open the "New Name" dialog box "Name" input box, enter "ymzf". OK to close the dialog box.

later as long as we enter in the name of the box "ymzf", Enter, you can re-select all the languages of the subjects in the AA column corresponding to the cell, and shown in Figure 3.

in accordance with the above method, as long as the make errors, respectively located in the corresponding subject line, then you can use to check their positioning conditions. Because of language subjects in which the cell line where the remainder divided by 8 to 4, so we use the formula "= 1 / (MOD (ROW (), 8) -4)" created a divisor error is 0. Then mathematics, English and other subjects you can respectively, according to the number of rows divided by the number of more than 8 different, repeat the above operation, only the denominator in the formula "-4" were turned into "-5", "-6" " -7 "," -0 "," 1 "," 2 "," 3 "on it. To the various subject areas to the respective "sxzf", "yyzf" so named.

but the preparation is not enough just to do this. Because we rank discharged should be placed out instead of AA AB column. So we have to re-use the above method in the AB column is selected in each subject area that corresponds to and respectively "ymmc", "sxmc", "yymc" so named for the future in these regions enter a different formula.

So far, our preparation is regarded as completed. Now we will be temporarily placed elsewhere in the score column pasted back to AA cell, and then the remaining thing is to use RANK function ranking problem. Zan is the first language results according to the list.

first name field type "ywmc", carriage return, the AB corresponding to the column language subjects all the selected cell at this time will be in AB372 cells was activated. As long as we enter the formula in the editorial column "= RANK (AA372, ywzf)", and press "Ctrl + Enter" can be selected in all cells enter the formula and get ranking result. The end result shown in Figure 1. Ranking of other disciplines handled according to the Act.