Case: First, numerical sorting.

1, rank function

Rank function is the main tool for Excel to calculate ordinal number. Its syntax is: RANK(number, ref, order), where number is a number or a cell containing numbers, ref is an absolute reference to the range of number cells involved in calculation, and order is a number used to explain the sorting method (if order is zero or omitted, the results are given in descending order, and vice versa).

For example, to calculate the total output value stored in cells E2, E3 and E4 in the first quarter, the method of calculating the output value ranking of each workshop is: enter the formula "=RANK(E2, $E? :$E？ ) ",press enter to calculate that the output value of foundry workshop ranks 2. Then copy the formula in F2 to the clipboard, select F3 and F4 cells and press Ctrl+V, and the output values of the other two workshops can be calculated as 3 and 1. Meiwenfang reminds everyone that if the formula entered in cell B 1 is "=RANK(E2, $E? :$E？ , 1), the calculated ordinal numbers are arranged in ascending order, that is, 2, 1, 3. The ordinal number (rank) of the same value calculated by rank function is the same, but it will lead to the ordinal number vacancy of subsequent numbers. If the value stored in cell F2 in the above example is the same as the value stored in cell F3, the rankings calculated by this method are 3, 3 and 1 respectively (in descending order).

2.COUNTIF function

The COUNTIF function can count the number of qualified cells in a certain area, and its syntax is COUNTIF(range, criteria). Where range is the range of cells participating in statistics, and criteria is a condition defined in the form of numbers, expressions or text. Numbers can be written directly, and expressions and words must be quoted.

Or take the above example, the formula entered in F2 cell is "=COUNTIF($E? :$E？ , ">" & e2)+1. The calculation method of output value ranking of each workshop is the same as above, and the results are exactly the same, 2,1,3.

The calculation process of this formula is as follows: firstly, according to the numerical value in the E2 cell, input it in the connector &; Generate a logical expression under the action of ">"; 176。 7 "、" > 167。 3 "and so on. The COUNTIF function calculates the number of qualified cells in the reference area, and the result can be ranked by adding 1. Obviously, the descending order is obtained by using the above method, and the result of repeated data calculation is the same as the rank function.

If you have essay typesetting, template downloading and essay questions, you can visit: /zt/lunwen? from=qyzd

For more WPS office software tutorials, please visit:, I wish you a happy use, thank you.

If you have any questions, please click on my avatar to ask questions. Wish you a happy life!