排名与排程

Rearranged your result table a bit to allow for some flexibility. See image at the bottom for cell reference and lay out. Instead of saying top rank, and 2nd rank, I put a header above that that said RANK an then under that put a number representing the rank position. I did this so you could simply drag the formula right and it would pull the rank based on the rank number in the header.

Using the reference ranges in the example below, place the following formula in J3 and copy down and right as required.

=IFERROR(INDEX($2:$2,AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)),"None")

UPDATE - The Explanation

lets break this formula up into its individual components

Lets start with the aggregate formula which has a couple of formulas buried in it:

AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)

The reason I pulled this part out is that everything that happens inside of it is array like operations. Lets burrow a little further and look at the match part and then come back up from there:

MATCH($I3,$A$3:$A$6,0)

This formula is going to return an integer representing where the value in I3 (January) is located in the list $A$3:$A$6. In other words we are trying to figure out which row in your reference table you want to be looking at. In this case its 1 for the first item in the list. If January was not in the list, then it would return an error which would propagate up through the formulas.

Next lets look at the Index formula:

INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)

INDEX(TABLE, ROWS TO GO DOWN, COLUMNS TO MOVE OVER)

Think of the index formula as define your table, go down Y rows and then go across X columns. In this case $B$3:$H$6 represents your data without header/row labels. The MATCH formula is telling us how many rows to go down. The 0 at the end is doing a neat little trick which is kind of special. Since you cannot go across 0 columns, in this special case of 0, INDEX interprets this as take all columns also known as the entire row of the defined table. on a side note similar thing happens when you use 0 for how many rows to go down.

The next step is the array operation and comparison.

INDEX() <> ""

Basically its going through each column and checking to see if the entry is blank or has something in it. If its blank it will return FALSE, and not blank will return TRUE. Now the reason we do this is that the next step it we will divide by the TRUE or FALSE value. Excel converts TRUE and FALSE to 1 and 0 respectively when they are sent through a math operation. So two important things happen here. Dividing by 1 does not change the value of anything. it essentially does nothing. Dividing by 0 causes a divide by 0 error which will propogate up. Its actually something we need. Now what are we actually dividing?

COLUMN($B$3:$H$3)

This is still in the array portion of the aggregate formula. Therefore it will build a list as the array iterates through each cell. So basically what is happening is a list of SPREADSHEET column numbers is being generated. and more importantly a list of spreadsheet column numbers divided by 1, which we want, and divided by 0 giving an error which we do not want. So now lets look at aggregate:

AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)

AGGREGATE(Function number, OPERATION #, formula, Option value)

Basically we choose function 15 which will sort through the list of results of the function and order them from smallest to largest. Function 15 also forces aggregate to perform array operations. Not all function numbers will. 6 tells aggregate to IGNORE ERROR VALUES. So all those divide by 0 errors get ignored, as does a potential match not found error from the match function. The formula we discussed above as to what its doing, and the option value in this case is what position from the top of the list do you want. 1 would return the smallest number, 2 the second smallest number and so on. In this CASE I set it to look at the number in the header instead of hard coding it into the formula. In the end AGGREGATE in this case is going to return a single integer corresponding to the SPREADSHEET column number that you are looking for. Then its back into INDEX:

INDEX($2:$2,AGGREGATE(...))

在这种情况下，我将第2行的所有内容都作为表格。由于它只有1行，因此我不需要像上表那样向下提供行，而向上提供列。它看起来像一个列表，我只需要提供我想去的列表多远。因此，由于我们拥有SPREADSHEET列号，因此我们需要查看整个电子表格行，在该行中您的标头位于此处，以便由聚集行（与索引中的speadsheet表对齐）返回的spreadhseet列号。

在找不到人的演员表中，IE没有排名1，Aggregate返回错误。为了解决这个问题，我们使用了IFERROR函数。如果没有错误，该函数将正常运行并返回其结果。如果有错误，则在我们的情况下返回“ none”。