组中子组的唯一等级值 [英] 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.

      

  Filtered Results

        

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