The rank function in Excel is dedicated to data ranking scenarios, and in this section we will learn more about the complete three parameters of the rank function and its formula application.
Let's take a look at an example, the data table contains products and sales, and now we want to rank according to sales, and the ranking method is in descending order from largest to smallest, that is, the maximum value is ranked 1, and the minimum value is ranked last.
How to use the rank function to express the ranking formula, let's first look at its syntax diagram:
The expression of the rank function is:=rank(Rank Value, Rank Region, Rank Type).
Its full expression consists of three parameters, the first parameter is the numerical value to be ranked, which is usually a number;The 2nd parameter is the range of cells that will be ranked, and the 1st parameter value will be ranked relative to the size in this range;The third parameter is the way of ranking, including:Descending and ascending rankings, denoted by the digit 0 and non-zero digits, respectively
The so-called descending ranking is to rank the data in order from largest to smallest, with the maximum value being the first place and the minimum value being the last place.
On the other hand, the ascending ranking is the opposite, ranking from smallest to largest, with the largest value being the last place and the minimum value being ranked first.
At this point, we want to rank the sales volume, and in order from largest to smallest, we will enter the parameter values according to their syntax, as shown in the following figure:
If the author omits the third parameter, the default ranking is descending.
Let's run the formula and see how it works:
The above is the general expression of the rank function that omits the third parameter, and then let's look at the formula used by the rank function for ascending ranking.
The input formula is:：=rank(b2,$b$2:$b$14,2)
Here, if the third parameter of the rank function is set to a non-zero value of 2, it means that its ranking type is ascending ranking, and the ranking will be carried out in ascending order according to the order of sales from small to large.
The results of the two formulas can be checked from the sorted sales data on the right side of the figure above.
Throughout the whole text, the expression of the rank function is relatively simple, and its conventional writing only needs to contain two parameters, but according to specific needs, if you perform an ascending ranking, you need to enter the third parameter as a non-zero value. Here we will also focus on the understanding of the two ways of ranking the third parameter of the rank function.
To put it simply, the regular ranking is sorted in ascending order from 1, and when the descending ranking is performed, it means that the data is sorted in descending order and then the ranking starting from 1 is executed, and the first value is the maximum value, so its maximum value is the first place;When performing an ascending ranking, it representsThe data is sorted in ascending order, with the first value being the minimum, the maximum value is at the end of the data, so the first value is the minimum value, and the last value is the maximum value!
Workplace Skills Competition