如图,主要是根据两个条件(一个主要判断条件,一个次要判断条件),根据这两个条件做组内的排名。各个小组的排名相互独立,互不影响。
点击下载附件:
0721多条件组内排名公式.xlsx
首先组内排名,可以想到多条件求和sumproduct函数,可以以数组形式计算数据。以D2单元格公式为例将公式抽象处理:
=SUMPRODUCT((组范围数据=A2本身)*((条件区域的每个值)>=(第一个数据条件本身)))
同时满足区域是本身所在区域,并且条件值大于等于当前条件值的个数有多少个,就代表是第几名。比如第二名,大于且等于它本身的数据就两个,一个是第1名,一个是它本身。
(若只有一个大于等于当前条件值,意味着就是它本身,则是第一名)
那么接下来,其他的两个条件要做大小判断,尤其是还有个主要/次要条件之分,所以可以利用乘法来给主要条件增加权重,再与次要条件相加,这样就只需要对相加之后的一个数字进行比较大小即可。
这里根据数据情况对主要条件进行加权,这里是乘以10来加权重(当然也可以不乘,因为本身就是相差了1个数量级了),根据数据情况,也可以对主要条件乘以100、1000等等来增加权重。
于是得到加权后的比较数值区域:
C2:C10*10+B2:B10
仍以D2公式为例,该行的数据条件本身则为:
C2*10+B2
那么将最终的两个条件写在一起可得:
=SUMPRODUCT((A2:A10=A2)((C2:C1010+B2:B10)>=(C2*10+B2)))
最后,将各个区域范围的行数(2-10行)进行绝对引用即可得:
=SUMPRODUCT(($A$2:$A$10=A2)(($C$2:$C$1010+$B$2:$B$10)>=(C2*10+B2)))