What functions can be used to complete such a ranking method in Excel? Example questions are in add

The easiest way to do this is to first sort the data by grade......Then judge and calculate the "ranking" ...... you needIn fact, this kind of ranking is of little significance......After sorting, enter the following formula......(Assuming an English score in column C.......)D2 Enter 1 ......

d3 =if(c3=c2,d2,d2+1) and then copy ...... downOnce you have the result, you can copy the result and then Paste in Place to Paste the value ......Permanently pinned down.

When you sort by other columns, this ranking doesn't change ......

Suppose the English score is in column A.

Rank=rank(a1,$a$1:$a$10), drop-down formula.

It's just that those with the same rank will jump one place after the row.

Your sorting is in descending English order, ascending order in name, and if you don't use a function, you can just use the sort command.

rank function, which is a function that specializes in ranking. The specific usage will be clear at a glance, and according to my experience, I should pay attention to two points.

Suppose the score is C2:C100, you can enter the formula in D2 and drag it down.

rank(c2,$c$2:$c$100)

I use the rank function to rank, but I don't use the function if I want to sort. It seems that it can only be done with sorting!

1. Rank function: the ranking of the number of repetitions is the same, if there are two people in the rank of 2, the ranking Li is the second and second respectively;

2. Function: If multiple values have the same ranking, the average ranking will be returned, such as the answer with rank 2 has two people who are late in the first branch, and their rankings are the first and first respectively.

Enter the formula =, and enter the first parameter in the formula, the grade cell to be ranked. Then enter the second parameter, which grades are ranked, i.e., ranking areas, and absolutely use citations. Finally, after the formula is entered, hit enter to get the ranking result.

Tools Raw Materials:

Computer: Dell notebook, system: windows10, 64-bit.

Software: Excel 365 Home Edition.

1. Enter the formula = in the ranking column first.

2. Then enter the first parameter in the formula, the score cell to be ranked.

3. Then enter the second parameter is which grades are ranked, i.e., ranking regions, and absolutely use citations.

4. After the final formula is entered, hit enter to get the ranking result.

If we want to use the ranking function in excel, the specific operation method is as follows:

Open [Excel], select the relevant cell, and then enter the ranking function rank function in the cell;

First enter the cell position to be ranked, and then enter the ranking range, note that you must add a symbol when entering the range, otherwise the ranking will fail;

The last input function is.

rank(b2,$b$2:$b$8,0)], after entering, directly click the [enter] key on the keyboard, so that the relevant results can be calculated;

Then select this cell, place the cursor in its lower right corner, and when the mouse turns into a cross, hold down the left mouse button and drag it down;

Then release the mouse, and the other rankings will be automatically filled.

Excel** uses the rank function to rank the second method 2:

1. If the data is vertical, we can select the first column of the ranking (as shown in the figure is C2) and type the formula"=rank(b2,$b$2:$b$6)"Then press enter. formula"b"refers to the column where the ranking data (score 1) is located, "b2" refers to the position of the B2 data corresponding to the C2 grid, and "$b $2:."

"b$6" refers to the data range from b2 to b6"The use of $ is to lock the following characters.

Of course, we can also write ** as **2 style"=rank(b2,b$2:b$6)"The difference from the above is that the ** only locks the "2" and"6", there are no locked columns"b".This is the key to a formula for multi-row (column) sorting.

2. After the first grid ranking is displayed, we can fill the formula with C2 to C6 grids by copying, dragging, double-clicking in the lower right corner, etc., so that the ranking will automatically come out.

3. Now the point is here. For our "Score 2" ranking, we only need to do the following: copy the position of the "Rank 1" column, then select the "Score 2" score area, right-click, and select 'Paste' (note that it is not 'Paste Special').

4. The rankings have been arranged. If you used the first formula in step 1, this step will be wrong. Because the first method locks the "b" column, it still uses b2 to b6 as the ranking area after copying it.

The second method does not lock the "b" column, so the copied formula automatically changes the "b" to "d", which is exactly the range of "score 2".

The ranking function excel formula is as follows:

Equation 1: rank function.

First of all, we will introduce a special function for calculating ranking in Excel - the expression of the function is: =rank (value, reference area, ranking method). Among the three parameters of the rank function, the last parameter can be omitted, and the default parameter is 0, that is, ascending sorting.

According to its expression, you can enter the formula: =rank(f3,$f$3:$f$40).

Equation 2: countif function.

The countif function is actually a counting function that counts the number of cells in a region that meet the specified conditions. Its expression is: =countif(region, condition).

Equation 3: sumproduct function

The sumproduct function, also known as the product function, should be the product summation function, because its function is to carefully sum the product of the cell. Enter the formula: =sumproduct(($f$3:

f$40>f3)*(1/countif($f$3:$f$40,$f$3:$f$40)))1。

Equation 4: Rank function for the reciprocal rank.

The fourth formula, in fact, only needs to set the ranking method of the third parameter of the rank function to achieve the effect of reciprocal ranking. Enter the formula: =rank(f3,$f$3:$f$40,1).