组内子组的唯一排名值

本文介绍了组内子组的唯一排名值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试从我的数据中的一个子组中获取唯一的排名值(例如 {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

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