Office software is closely linked to the PDF,the PDF is also must have to control!
Set home Page Add to Favorites
|
, we generally sort according to performance level, and then automatically fill out according to rank sequence. If the data is relatively small, we can use manual methods to the same performance ranking officers into the same, but very cumbersome when more data. After practice, the following three ways to automate the operation of the same points with the ranking. Suppose there are 1,000 candidates, the field position as shown below:
Method 1: Use the rank function RANK
H2 cell in the input function "= RANK (G2, G $ 2: G $ 1001,0)", after the carriage return will appear in the H2 cell ranking of "1", then select H2, drag the fill handle down can be automatically filled. function description:
function is the function to return a number (the function of G2, where the relative application, fill the time changes accompanying changes) in the list of numbers (the function of G $ 2: G $ 1001, here with an absolute application does not change when filled) in the ranking. Ranking figures in the list of its size and the ratio of the other values. This function, when used, even if Total did not order, it can also be obtained directly corresponding to the total score ranking; if the score had been ranked order, then the number of ranking is its current location. The function of the last "0" means specified in qualifying, when it is 0 or omitted, Excel grid of numbers arranged according to descending. If not zero, Excel grid of numbers is arranged in ascending order. The function of the repeat number of the same rank, while the next rank will be repeated in front of the number counted.
$ show_page $
Method 2: Use the count function COUNTIF H2 cell in the input function "= COUNTIF (G $ 2: G $ 1001 ,">"& G2) +1", and then down the operation can be achieved automatically filled.
function description:
function is to calculate the function area to meet the given conditions the cell number. The function reference specified by the absolute calculation of regional G $ 2: G $ 1001, ranking G2 corresponding to the relative cell reference. That obtained in the G2: G1001 area larger than the corresponding rank score the number of cells, and then add one can draw the cell value of the ranking. Regardless of the number of repeated operations, the number is greater than it is fixed, so duplicate data is the same ranking was. As with the RANK function, regardless of whether the function to sort by total score can carry out the operation. Method 3: Using IF statement plus COUNT Function the operation and the above two methods, you must first score in accordance with the descending order. Then the first of the ranking of "1" input, then the H3 cell input function "= IF (G3 = G2, H2, COUNT ($ G $ 2: G3))", confirmed, auto-fill can be achieved down operation with the ranking with the points.
function description:
COUNT function, the function returns the specified number of regions in the number of cells. Because G is the number of cells in the whole column, so the total score in the first few on the rankings is a few. The statement means that if the current score and a score the same, then enter a total score ranking, or implementation of the COUNT statement, statistical number.
Summary: The above three methods can automatically operate the same hours the same rank. Although Mr. Kong Yiji this article on the "back" the word written four suspects, but the author's purpose is only one, that is, initiate to Excel function better for us. |
