Like and see, form a habit; Peaches and plumes do not speak, yet a path is formed beneath them.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
The teaching director gave me the mid-term exam results of the whole school, and asked me to list the ranking of each student in the class and in the school.
If we do a simple ranking, such as the whole school ranking, we may think of: directly put everyone’s scores in descending order and then go from top to bottom 1,2,3… But even then the problem is that people with the same score are ranked differently. Fortunately, Excel provides a ranking function called “RANK.”
“RANK” returns a list of numbers.
“RANK” syntax: RANK(number to be ranked, list of ranked numbers, ascending or descending order);
RANK(C2,C 2:C2:C2:C12) =RANK(C2,C 2:C2:C2:C12)
RANK(C2,C 2:C2:C2:C5) =RANK(C2,C 2:C2:C2:C5)
Then do it for each class. This can be done in the end, not to mention how many times the RANK function has to be written for how many classes there are. The fatal flaw is that if we sort all the data in ascending order by school RANK, we will find that A lot of the data in the class RANK shows #N/A. Obviously there are limitations to the way you write class order.
So how do you do that? What other function can do that?
There is a special function called “SUMPRODUCT”, which is special because if you look at this function in any list of functions, you will see that it is a mathematical and trigonometric function that returns the sum of the products of the corresponding array elements. This doesn’t really make sense with ranking, but if you click on this function to see a full explanation: The SUMPRODUCT function returns the sum of the corresponding ranges or arrays. The default operation is multiplication, but you can also add, subtract, and divide.
Let’s take a look at one of the examples on the website. What does this example mean? This example uses SUMPRODUCT to return the total sales for a given item and size. Let’s look at the formula: =SUMPRODUCT((B2:B7=B10)*(C2:C7=C10)*D2:D7) Then “(C2:C7=C10)” can be read to find the row with the value of C10 in the cell area of C2:C7, that is, the row with Size column M, while “D2:D7” can only be seen as the value of D column screened out previously. As for the operation after getting the value, we can not see. The values of column D are 21 and 41 respectively. After looking at the formula in cell D10, the final result is 62. We can infer that this formula is the sum of the Sold columns that return the records of the specified Item column and Size column.
The reason for explaining all this here is to show how we can use this function flexibly to implement functions not covered on the official website.
From the above explanation, we can infer that “(B2:B7=B10)*(C2:C7=C10)” is the filtering function, and “D2:D7” is the summation function. SUMPRODUCT((B2:B7=B10)*(C2:C7=C10)) Can we think of it as “(C2:C7=C10)” and “(C2:C7>C10)”, and then think about the score. The number of points greater than the specified number can be converted into rankings.
SUMPRODUCT((A 2:A2:A2:A12=A2)*(C 2:C2:C2:C12>C2))+1 “. Plus 1 because the number greater than the current value plus 1 is exactly the ranking of the current value.
Then we carried out the ascending order of the whole school ranking, and the result is as follows:
And you can see that the results are also normal. Let’s take a look at the process:
I’m going to use this example to show you how to use this function for sorting.
We can see from this example that we can only use a function well if we really understand it.
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.