Excel makes the score table automatically to achieve the same position

Source: Internet
Author: User

In Excel statistical results, we will generally according to the results of the ranking, and then automatically fill the rankings by sequence. This approach to the position and total score is not associated, even if the results are the same, will be filled with different rankings.

If the data is low, we can use the manual method to change the people with the same results to the same position, but the data is very troublesome. After practice, the following three methods can automatically achieve the same ranking of the operation. Assuming there are 1000 candidates, the field position is shown in the figure above.

First, use the Rank function rank

Enter the function "=rank (g2,g\$2:g\$1001,0)" in cell H2, the rank "1" appears in the cell after the carriage return, and the H2 is selected, and the fill handle is dragged down to fill it automatically.

The function is to return a number (the G2 in the above function, which varies with the relative application and the padding) in the list of numbers (the g\$2:g\$1001 in the above function, where the absolute application is used and the padding does not change). A number's rank is the ratio of its size to other values in the list. The function is used, even if the total score is not sorted, it can also directly find the corresponding score of the ranking, if the total score has been ordered, the number of the ranking is its current position. The last "0" in the above function indicates how the rank is arranged, and when it is 0 or omitted, Excel ranks the digits in descending order. If it is not zero, Excel arranges the digits in ascending order. The function has the same rank as the number of duplicates, and the next position counts the number of previous repeats.

Second, use the counting function countif

Enter the function "=countif (g\$2:g\$1001," > "&g2) +1" in cell H2, and then automatically fill down to implement the operation.

The function is to calculate the number of cells in a range that meet a given condition. The above function uses the absolute reference to specify the computed area g\$2:g\$1001, and the corresponding cell G2 of the rank takes the relative reference. That is to find the number of cells in the g2:g1001 area that are larger than the rank, and then add 1 to get the value of the cell. The operation has a few duplicates, but the number greater than it is fixed, so the rank of the duplicate data is the same. Like the rank function, the function can do this regardless of whether it has been sorted by total score.

Using the IF statement plus the Count function

This operation differs from either of these methods, and the total score must first be sorted in descending order. Then the first name of the ranking "1" input, and then in the H3 cell input function "=if (G3=g2,h2,count (\$G \$2:g3))", determined after the downward automatic filling can also achieve the same rank of the operation.

The function of the Count function is to return the number of number cells in the specified range. Because the G column is full of numeric cells, so the total score in the first few on the ranking is a few. The above statement means that if the current total score is the same as the previous score, enter the rank of the last total score, otherwise execute the Count statement and count the numbers.

All of the above three methods can automatically achieve the same rank operation. Although this article has the word "back" words have four kinds of writing suspicion, but the author's purpose is only one, that is, let Excel function better for our services.

Related Keywords:
Related Article E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD