组内子组的唯一排名值 [英] Unique Rank value for a subgroup within a group

问题描述

我正在尝试从我的数据中的一个子组中获取唯一的排名值(例如 {1, 2, 3, 4}.SUMPRODUCT 将产生 ties{1, 1, 3, 4}，我正在尝试将 COUNTIFS 添加到末尾以调整重复的排名.

 子组col B col M 等级LMN 01 1XYZ 02XYZ 02ABC 03ABC 01XYZ 01LMN 02 3ABC 01LMN 03 4LMN 03 4 '应该是 5ABC 02XYZ 02LMN 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.

      

  Filtered Results

        

这篇关于组内子组的唯一排名值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！