在没有VBA的情况下为列表中的每个组提取前5个值 [英] Extract Top 5 Values for Each Group in a List without VBA

问题描述

我想开发一个公式来构造一个表，其中包含多个组中的前5个标题/值，最好没有VBA.我附了一个例子.由于我是该论坛的新手，并且需要至少10个信誉点才能发布图像，因此我已将屏幕截图上传到:

我已经发现并查看了以下线程:提取excel中的前5个最大值(基于组).使用该公式，我能够从整个列表中拉出前5名，但是我无法合并IF子句以按组将排名分开.

提前感谢您的时间和考虑！如果我的要求不清楚和/或您有任何具体问题，请告诉我.感谢任何支持！

解决方案

尝试一下:

  = Large(If(C:C ="Group 1"，A:A)，Row(1:1)) 

这将进入第1组结果的右上角单元格(F3?).这是一个数组公式，必须使用Ctrl-Shift-Enter确认.

对于E3中的项目:

如果值是唯一的；没有关系:

  = INDEX(B:B，MATCH(F3，IF(C:C ="GROUP 1"，A:A)，0)) 

如果有平局的可能，那么事情就会变得复杂一些.

  = INDEX(B:B，MATCH(1，IF(C:C ="GROUP 1"，IF(A:A = F3，IF(COUNTIF(B:B，$ E $ 2:$ E2)= 0,1,0)，0)，0)，0)) 

都是数组公式，必须使用Ctrl-Shift-Enter确认.然后复制下来.

注意:这些数组公式将遍历整个列，这将花费一些时间.将整个列范围限制为实际的绝对数据范围将是有利的.例如，如果最后一行是1000，则将C:C更改为$ C $ 3:$ C $ 1000.

---

有关数组公式的更多信息，请参见http://i.imgur.com/v1LAkYk.png * Thanks @Scott Craner for adding the image to the post!

I have already discovered and reviewed the following thread: Extracting top 5 maximum values (based on group) in excel. Using the formula I was able to pull out the top 5 from the entire list as a whole, but I have not been able to incorporate an IF clause to separate the rankings by group.

Thanks in advance for your time and consideration! Please let me know if my request is unclear and/or if you have any specific questions. Appreciate any support!

解决方案

Try this:

=Large(If(C:C = "Group 1",A:A),Row(1:1))

This would go into the top right cell of Group 1 results(F3?). It is an array formula and must be confirmed with Ctrl-Shift-Enter.

For the Items In E3:

If the values are unique; No ties:

=INDEX(B:B,MATCH(F3,IF(C:C = "GROUP 1",A:A),0))

If there is possibility of a tie then it gets a little more complicated.

=INDEX(B:B,MATCH(1,IF(C:C = "GROUP 1",IF(A:A = F3,IF(COUNTIF(B:B,$E$2:$E2) = 0,1,0),0),0),0))

Both are array formulas and must be confirmed with Ctrl-Shift-Enter. then copied down.

Note: These array formulas will iterate through the entire column, which will take time. It would be advantageous to limit the full column ranges to the actual absolute data range. For example change C:C to $C$3:$C$1000, if 1000 was the last row.

---

For more information on Array Formulas, See THIS

这篇关于在没有VBA的情况下为列表中的每个组提取前5个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！