在列A中使用与列B的值范围匹配的前5名范围 [英] Top 5 using range in column A that matches values range of column B
问题描述
[A] [B] [C] [D] [E]
ABC DFC 20
DE DE 12
GF 11
ABC 19
DE 17
DE 15
XE 14
ABC 10
我想使用[列B]中的[列A]中的范围在[列B]和[列C]的[列D]和[列E]中排列前5名。
I would like to put a top 5 in [column D] and [column E] of [column B] and [column C] using the range in [column A] as found in [column B].
根据上述示例,[D]和[E]中的最终结果应如下所示:
Based on this example above, the end result in [D] and [E] should look like:
[D] [E]
ABC 19
DE 17
DE 15
DE 12
ABC 10
提前感谢您的解决方案。
Thanks in advance for your solution.
编辑:我应该补充。 [列A]是动态的,可以包含行2到6(作为示例)中的值。
I should add. [Column A] is dynamic and could contain values in rows 2 to 6 (as an example).
推荐答案
在单元格E1和复制:
=IF(ROW(E1)>SUMPRODUCT(COUNTIF($B$1:$B$8,$A$1:$A$6)),"",LARGE(INDEX((COUNTIF($A$1:$A$6,$B$1:$B$8)>0)*$C$1:$C$8,),ROW(E1)))
在单元格D1中并复制:
In cell D1 and copied down:
=IF(E1="","",INDEX($B$1:$B$8,MATCH(E1,$C$1:$C$8,0)))
这两个公式都是常规公式,不需要数组输入。在这两种情况下,调整B和C列参考符合您的实际数据。不要使用整列引用。如果需要,可以使用列B和C范围引用的动态命名范围
Both formulas are regular formulas and do not require array entry. In both cases, adjust the B and C column references to fit your actual data. Don't use whole column references. If necessary, use dynamic named ranges for the column B and C range references
编辑:为了防止数值中有重复列C的前五个数字,在单元格D1中使用此公式,然后复制:
EDIT: And just in case there are duplicates in the numeric values of the top five numbers for column C, use this formula instead in cell D1 and copy down:
=IF(E1="","",INDEX($B$1:$B$8,SMALL(INDEX(($C$1:$C$8=E1)*ROW($C$1:$C$8),),COUNTIF($C$1:$C$8,"<>"&E1)+COUNTIF(E$1:E1,E1))))
这篇关于在列A中使用与列B的值范围匹配的前5名范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!