公式拉取前25名的所有数据 [英] Formula to pull all data for Top 25
问题描述
我有以下数据集,我已经使用Index-Large函数
= LARGE(INDEX(($ A $ 2 $ A $ 99)$($ B $ 2:$ B $ 99 = {x,y}),,),ROW(1:1))
pre>
...找出x和y的前25名合并。如何拉列出列出水果的列?我不能使用Large,因为它只适用于Numbers。我还有20个列,只有文本。有什么想法吗?
苹果100 x
香蕉50 y
葡萄6 z
西瓜89 x
哈密瓜5 x
橙色24 y
解决方案您需要首先调整您为正确列提供的公式。
= LARGE(INDEX(($ B $ 2:$ B $ 99)*($ C $ 2:$ C $ 99 = {x,y}),,),ROW(1:1))
当这样做完成后,它可以作为匹配条件,在更大的公式中也可以匹配x的列C。
= IFERROR(INDEX(A $ 2:A $ 99,MIN(INDEX(ROW($ 1:$ 98)+(($ C $ 2:$ C $ 99 LT;> { X, Y})+($ B $ 2:$ B $ 99 LT;>轻便(INDEX(($ B $ 2:$ B $ 99),*($ C $ 2:$ C $ 99 = {x,y}),,),ROW(1:1))))* 1E + 99,,))),)
pre>
在下面的示例图中,该公式将进入E4。
如果 nmbr 列中的值可能重复(同时仍然匹配{x,y}),则一个公式不能用于整个查找表。在F4中使用以下内容。
= LARGE(INDEX(($ B $ 2:$ B $ 99)*($ C $ 2: $ C $ 99 = {x,y}),,),ROW(1:1))
$ b $ e4将是,= IFERROR(INDEX(A $ 2:A $ 99,SMALL(INDEX(ROW :$ 98),+(($ C $ 2:$ C $ 99 LT;> { X, Y})+($ B $ 2:$ B $ 99 LT;>轻便(INDEX(($ B $ 2:$ B $ 99)*($ C $ 2:$ C $ 99 = {x,y}),,),ROW(1:1))))* 1E + 99,,),COUNTIF($ F $ 4: $ F4,$ F4)),)
将E4复制并粘贴到G4,然后选择E4 :G4并填写。
A2中的位置: A99,而不是工作表上的实际行。
I've following data set and I've used Index- Large function
=LARGE(INDEX(($A$2:$A$99)*($B$2:$B$99={"x","y"}), , ), ROW(1:1))
... to find out Top 25 of x and y combined. How would I pull the Column that has listed fruits? I can't use Large since it's for Numbers only. I've 20 more columns that have text only. Any thoughts?
apple 100 x banana 50 y grapes 6 z watermelon 89 x cantaloupe 5 x orange 24 y
解决方案You need to adjust the formula you provided for the correct columns first.
=LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))
When that is done, it can be used as matching criteria in a larger formula that also matches column C for x, y.
=IFERROR(INDEX(A$2:A$99, MIN(INDEX(ROW($1:$98)+(($C$2:$C$99<>{"x","y"})+($B$2:$B$99<>LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))))*1E+99, , ))), "")
In the sample image below, that formula would go into E4. Fill both right and down as necessary.
If the values in the nmbr column may be duplicated (while still matching {x, y}) then one formula cannot be used for the entire lookup table. Use the following in F4.
=LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))
E4 would be,
=IFERROR(INDEX(A$2:A$99, SMALL(INDEX(ROW($1:$98)+(($C$2:$C$99<>{"x","y"})+($B$2:$B$99<>LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))))*1E+99, , ), COUNTIF($F$4:$F4, $F4))), "")
Copy and paste E4 to G4 then select E4:G4 and fill down.
Note that when transcribing the formula for your own use, ROW(1:98) is the position within A2:A99, not the actual row on the worksheet.
这篇关于公式拉取前25名的所有数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!