组中子组的唯一等级值 [英] Unique Rank value for a subgroup within a group
问题描述
我正在尝试从我的数据中的一个子组获得一个独特的排名值(例如 {1,2,3,4}
。SUMPRODUCT将生成关系{1,1,3,4}
,我试图添加COUNTIFS到最后调整重复排名。
子组
col B col M rank
LMN 01 1
XYZ 02
XYZ 02
ABC 03
ABC 01
XYZ 01
LMN 02 3
ABC 01
LMN 03 4
LMN 03 4'应为5
ABC 02
XYZ 02
LMN 01 1'应该是2
到目前为止,我已经想出这个。
= SUMPRODUCT(($ B $ 2:$ B $ 38705 = B2)*(M2> $ M $ 2:$ M $ 38705))+ countifs(B2:B38705 = B2,M2:M38705 = M2)
在这里做错了?
好消息是,你可以扔掉
I am trying to get a unique rank value (e.g. {1, 2, 3, 4}
from a subgroup in my data. SUMPRODUCT will produce ties{1, 1, 3, 4}
, I am trying to add the COUNTIFS to the end to adjust the duplicate rank away.
subgroup
col B col M rank
LMN 01 1
XYZ 02
XYZ 02
ABC 03
ABC 01
XYZ 01
LMN 02 3
ABC 01
LMN 03 4
LMN 03 4 'should be 5
ABC 02
XYZ 02
LMN 01 1 'should be 2
So far, I've come up with this.
=SUMPRODUCT(($B$2:$B$38705=B2)*(M2>$M$2:$M$38705))+countifs(B2:B38705=B2,M2:M38705=M2)
What have I done wrong here?
The good news is that you can throw away the SUMPRODUCT function and replace it with a pair of COUNTIFS functions. The COUNTIFS can use full column references without detriment and is vastly more efficient than the SUMPRODUCT even with the SUMPRODUCT cell ranges limited to the extents of the data.
In N2 as a standard function,
=COUNTIFS(B:B, B2,M:M, "<"&M2)+COUNTIFS(B$2:B2, B2, M$2:M2, M2)
Fill down as necessary.
这篇关于组中子组的唯一等级值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!