Practical Excel skills sharing: formula routines for ranking according to conditions

When it comes to ranking data in excel, the first thing that comes to mind is the rank function, but what if you want to rank the data according to conditions? Friends, are you confused all of a sudden? It seems that you have not heard of the function of ranking according to conditions. So today I will share with you a formula for ranking according to conditions in Excel. Let’s take a look!

Among the Excel functions, there are SUMIF that sums based on conditions, AVERAGEIF that calculates average based on conditions, and COUNTIF that counts based on conditions. In the latest version, there is even The MAXIFS function that finds the maximum value based on conditions and the MINIFS function that finds the minimum value based on conditions are provided. But there is no function that can rank by conditions.

However, problems such as ranking by conditions are indeed encountered in daily life. For example, the following problem is a typical representative of it:

We all You know that you can use the RANK function to get the ranking of a number in a set of numbers. In this example, the total ranking is obtained by using the formula =RANK(C2,$C$2:$C$19).

But what to do if you want to get the sales ranking of each store in the region? Do you need to use the RANK function to rank in each region?

Although this is also an idea, the low efficiency can be imagined. In fact, among the Excel functions, there is a function that can achieve ranking by conditions, which is SUMPRODUCT.

Before formally introducing the formula for ranking by conditions, let us first understand the operation principle of ranking by conditions.

Take store 10004 as an example. The regional ranking is 2 and the overall ranking is 10. As shown in the figure:

The reason for its regional ranking It is 2, which is easy to understand, because there are only six numbers in the same sales area (condition). Among these six numbers, only one number is greater than 56.55, which is 79.72, so its ranking in the area is 2.

The calculation principle of other rankings is the same. Thinking about it this way, achieving ranking by conditions actually involves two processes: judgment of conditions and judgment of size.

Write these two processes using formulas: $A$2:$A$19=A2 and $C$2:$C$19>C2, you can understand this with examples Two parts.

Look at the first one first, $A$2:$A$19=A2 will get a set of logical values:

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

##From this It can be seen from the results that the data in the same area as the store to be counted is TRUE.

$C$2:$C$19>C2 will also get a set of logical values:

{FALSE;TRUE;TRUE;TRUE;TRUE ;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

This result represents sales It will also get TRUE when it is larger than counting stores.

The current question is how to combine these two parts. Because these are two judgments on one data at the same time, multiply the two sets of logical values ​​to see what result is obtained:

The group of data in the picture consisting of 0 and 1 is ($A$2:$A$19=A2)*($C$2:$C$19> ;C2) The calculated result shows that in the area where store 10001 is located, there are 4 stores (4 1s) with sales higher than 14.46. You only need to sum the results to basically achieve the purpose of ranking. Therefore, there is a formula:

=SUMPRODUCT(($A$2:$A$19=A2)*($C$2:$C$19>C2))

However, there is a problem with the results obtained in this way. The ranking starts from 0. The solution is very simple. There are two methods.

Method 1: Add 1 directly after the formula, the result is as shown in the figure.

Method 2: Change the greater than sign to greater than or equal to, the result is as shown in the figure.

There is usually no difference between these two methods, and you can use either formula.

The above is a formula for ranking based on one condition. If there are two or more conditions, just expand the formula:

=SUMPRODUCT((condition area 1=condition 1)* (Conditional area 2=Conditional 2)* (Data area>data))

I will not list specific examples. I believe that after everyone understands the principle of the formula, you can combine it with specific problems to solve your own problems. There is absolutely no problem in applying it.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: formula routines for ranking according to conditions. For more information, please follow other related articles on the PHP Chinese website!