How to rank with Excel

The three parameters of the rank function, which value (the first parameter), in which region (the second parameter), and in descending order (0) or ascending order (1).

Example: Sort the values based on column A in descending order.

Steps: Enter the formula =rank(a2,$a$2:$a$11,0) in cell B2

Drag the formula or quickly fill.

This table is too messy, isn't it? Hehe.

You can also learn from Excel's built-in help.

Ranking can be achieved with the function f(x)=rank(cell, ranking area). However, the ranking area is an absolute value, for example, to find the numerical ranking of cells in the region (a1:a6), the function is "=rank(cell,$a$1:

a$6), and you can pull it down after losing.

rank(b2,$b$2:$b$8,1)

Adding a 1 to the end is ascending, and not adding or adding a 0 is descending.

Assuming that A50 does not participate in the ranking, the formula is as follows:

rank(a1,($a$1:$a$49,$a$51:$a$100))

How to use excel (rank function) to rank student grades.

It is often necessary to rank students' grades in the process of grading grades, and if you use the rank function, it is very simple:

rank function.

Main function: Returns the ranking of a certain value in a column of values relative to other values.

Use the format: rank(number,ref,order).

Parameter description: number represents the numeric value to be sorted; ref represents the range of cells where the sorted values are located;

order represents the sorting parameter (if it is "0" or ignored, it will be ranked in descending order, that is, the higher the value, the smaller the ranking result;

If it is a non-"0" value, it will be ranked in ascending order, that is, the higher the value, the larger the ranking result value; ）。

Example: If you enter the formula: =rank(b2,$b$2:$b$31,0) in cell C2, you can get the ranking result of the results in cell B2 to B31 after confirmation.

Special reminder: In the above formula, we let the number parameter take the form of relative reference, and let the ref parameter take the form of absolute reference (add a "$" symbol), after setting it, select the C2 cell, move the mouse to the lower right corner of the cell, and when it is in the shape of a thin crosshair (usually called "fill handle"), hold down the left button and drag down, you can quickly copy the above formula to the cell below column C, and complete the ranking statistics of other students' language scores.

1. First of all, list the name and grade of each subject in **, add a total column, and use the sum function to sum it.

2. Then click cell h2, click "fx" to insert the function, and select the "rank" function.

3. In the function parameter settings, add the " absolute reference symbol according to the parameters shown in the following figure.

4. Click "OK" to generate the ranking result, and the drop-down formula will form the ranking of the corresponding total score.

5. Then select all the data cells and click "Custom Sort" to sort them in ascending order.

6. You can get a ranking table based on the total score, showing the corresponding names and scores for other subjects.

If the score is in column B, then enter the following formula in cell C2, and then fill in the formula down to get the score ranking.

rank(b2,b$2:b$10)

or =rank(b2,b:b).

See the attached drawings for example.

1. First of all, we open the excel file of the Ichichu Hayano transcript;

2. Then we select the area where the results are sold, and then we click on the data;

3. Then we click on descending;

4. Pop up the interface carefully, we click to sort;

1. First of all, list the name and the grades of each subject in **, add a total score column and use the sum function to sum the operation.

2. Then click to the H2 cell and click "FX" to insert the function, and select the "Rank" function.

3. In the function parameter setting, set according to the parameters in the following figure, you need to add the "$" absolute reference symbol.

4. Click OK to generate the ranking result and pull down the formula to form the ranking of the corresponding total score.

5. Then select all the data cells and click "Custom Sort", and set them to be sorted in ascending order according to the ranking.

6. You can get a ranking table based on the total score, and show the corresponding name and the results of other subjects.

1. First, double-click the table to grid.

2. After opening **, you need to rank the sales volume, and then see a Du tied ranking.

3. Then enter the function dao=rank(b2,$b$2:$b$14).

4. After confirmation, you can see the ranking results.

5. Then pull down the copy function to get all the results.

6. Finally, save the file.

For example, d2 to d9 are the sources you want to sort.

cell. Write =rank(d2,$d$2:$d$9) in c2, and then pull to c9, which is your unprocessed sort, which may have a tie in it.

Write =countif($c$2:C2,C2) in b2, and then pull to b9, at this time you can find that all the numbers that are sorted side by side are not 1, note that the starting cell C2 should be locked with F4, and the second C2 should not be locked, so that there are several same rankings from top to bottom).

Write if(b2=2, c2+1,if(b2=3,c2+2,c2)) in a2.

The following is how to reorder the rankings that may have 4 positions of the same rank, there will be no ranking of the same rank in column B, but the order of the same rank will be in the order of priority.

The following methods can be used.

Solve DU (assuming the data is in a1:zhia100):

1、=sumproduct((a$1:a$100>a1)/countif(a$1:a$100,a$1:a$100))+1

Wherein: countif(a$1:a$100,a$1:a$100) counts the number of times the DAO value of each cell is repeated;

a$1: a$100>a1 to get all the answers that are greater than the current cell value;

After the above two parts are divided by each other, no matter how many times the value of each cell is repeated, its sum value is 1, and then the current value is added to get the ordinal number.

2、=sumproduct(n(if(frequency(a$1:a$100,a$1:a$100),a$1:a$100,0)>=a1))

Wherein: frequency(a$1:a$100,a$1:a$100) marks the position of the cell that does not appear repeatedly, and then cooperates with the if function to pick out the unique value, and the ordinal number of the current value is obtained by judging the size with the current value.

1. First of all, I bai

We open an excel file of a DU transcript;

2. Then we select it as dao

performance area, after which we click on the plate to hit the number of rights.

Occupy; 3. Then we click on descending;

4. In the pop-up interface, we click Sort;

In the sorting window, select the main keyword as the total score, the secondary keyword as language, and the third keyword as mathematics.

1. Open Excel and enter data in Excel.

2. Enter the data =rank() in the cell where you want to get the result.

3. Enter the cells participating in the ranking, the numerical region of the ranking and the sorting method in parentheses, 0 represents descending order, and 1 represents ascending order.

4. Press the enter key to get the corresponding results, and get the results of other cells according to the same method.

Ming or brigade 5, press alt+d+f+f to open the ranking column in ascending or descending order.

This article is based on the Dell Inspiron 5000 brand, Windows 10 system, Microsoft Office Excel 2019 version.

Here's how to use the excel ranking function rank:

Tools Ingredients: HP P214, Windows 10, Excel20161, Open Excel**, enter some numbers as examples.

2. Then enter the formula "=rank(a2,$a$2:$a$10)" to calculate the ranking of A2 in the A2-A10 region.

3. Finally, press Enter to get the result, and then move the mouse to the lower right corner of the A2 cell, and pull down to see other rankings.