Excel: Repeat the position can also check the name score

Being a teacher is probably an addiction to analyzing students ' grades. This is not, originally we have the students of all disciplines of the scores, total score, ranking all out, and in accordance with the total score in ascending order, but now there is a teacher came to the request to be able to their own subject of the top 10 students name and results found. Logically speaking, this request is not very difficult, but the trouble is that students may be the same grades, so the first 10 students are not necessarily 10 people, there may be more. Each discipline has to do so, the workload is not small, so, still need to rely on functions and formulas to help.

Fig. 1 Original Score sheet

The original score table is shown in Figure 1. The name is in cell c2:c92, and the language score is in the d2:d92 range. For example, we will look for the first 10 grades and the names of the students in the Chinese language discipline. To facilitate the comparison, figure 1 has sorted the data in descending order of language results, and in practice there is no need to sort beforehand.

First, the establishment of the ranking table

As we said earlier, it's not easy to determine how many students there are in the top 10, so we need to use formulas to find them out. Of course, it is best to fill out the ranking table. The complete results are shown in Figure 2.

Figure 2 Ranking of accomplishments

Position the mouse over the X3 cell, and then in the formula bar, enter the equation =text (($D $ $D $92>=large ($D $ $D $92,row (1:1))/countif ($D $ $D $92, $D $: $D $92)), "g/General format name"), return to the "1th place" results. Select cell X3 and drag its fill handle down until "11th name" appears.

A few functions are used here, and it feels more complicated. In fact, the idea is this: "The result of ROW (1:1) is" 1 "and the result of" LARGE ($D $ $D $92,1) "is the largest number in the specified range of cells;" ($D $ $D $92>=large ($D $: $D $92, ROW (1:1)) "can be interpreted as comparing the data in a range of d2:d92 to the maximum value in the range, which is greater than or equal to and less than the value, and is saved in an array with the result of" TRUE "and" FALSE "respectively.

The "COUNTIF ($D $ $D $92, $D $ $D $92)" section of the formula counts the number of occurrences of each number in the d2:d92 range of cells, and is also saved to an array. So, in the formula we used "Sumproduct ($D $ $D $92>=large ($D $ $D $92,row (1-1))/countif ($D $: $D $92, $D $: $D $92))" When executed, you will get a similar "Sumproduct ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;..} /{1;1;2;2;1;2;2;1;2;2;2;2;1;.}) " The results. Divide the corresponding data in two arrays, and then add all the quotient, which is the rank of the score. This method does not affect the display even if the rank is tied.

As for the outermost text function, the resulting result is converted to text in the specified number format. That is, the result of the inner-layer formula operation is the number "1", now we show it as "1th place".

Second, the score of the search

Position the mouse over the Y3 cell, and in the formula bar, enter the following =index ($D $ $D $92,match (LARGE ($D $ $D $92+1/row ($D $: $D $92), ROW (1:1)), $

D$2: $D $92+1/row ($D $: $D $92), 0)) ", then press" Ctrl+sh

Ift+enter "shortcut key to complete the input of the array formula. This step is critical, otherwise there will be no correct results.

Drag the fill handle of the Y3 cell down to the last cell to complete the copy of the formula.

Let's just explain the formula in a simple way.

Because a lot of data in the d2:d92 area is duplicated, this creates difficulties for us. So we have to find a way to make every data unique. The $D $ $D $92+1/row ($D $ $D $92) in the formula is the reciprocal of the corresponding number of rows for each data in the d2:d92 area. Because each data corresponds to a different number of rows, it makes each data a unique value and is saved to an array.

The LARGE ($D $ $D $92+1/row ($D $ $D $92), ROW (1:1)) in the formula returns the maximum value in the array above. The result in this example is "{96.5}".

The formula "MATCH" ($D $ $D $92+1/row ($D $ $D $92), ROW (1:1)), $D $ $D $92+1/row ($D $ $D $92), 0) "returns the position of the maximum value just obtained in the array." LARGE The result in this example is "{1}".

In fact, the last query that Excel executes is "INDEX ($D $: $D $92,1)", which naturally returns to the first value in $d$2: $D $92 area.

Third, the name of the search

Position the mouse over the Z3 cell, and enter the formula "=index" ($C $ $C $92,match (LARGE ($D $ $D $92+1/row ($D $ $D $92), ROW (1:1)),

$D $ $D $92+1/row ($D $ $D $92)) ", also press the" Ctrl+shift+enter "shortcut key to finish entering the array formula.

Drag the fill handle of the Z3 cell down to the last cell to complete the copy of the formula. The final effect is shown in Figure 3.

Figure 3 Completing the copy of the formula (click to see larger image)

In fact, you must have understood that the name of the search and the previous score is the same search. There is no big change in the formula itself. So, to understand the previous method, it is convenient to find something else.

Other disciplines can be dealt with according to this. Just notice to change the range of cells in the formula, I will not wordy here.