Excel 函数学习记录

1. SUMPRODUCT 排名

   排名规则： 允许并列排名，排名是连续的 --中国式排名

=SUMPRODUCT((B$2:B$9>B2)*(1/(COUNTIF(B$2:B$9,B$2:B$9))))+1

分解：

  1.  B$2:B$9>B2 -->boolean 数组{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}，在B2-B9 单元格中有大于B2的为TRUE,否则为FALSE；

 2.   B$2:B$9>B5 --> {TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}

countif 条件统计函数，这个地方跟正常使用有区别，=COUNTIF(B2:B9,">80") 大于80的人数。

3.   COUNTIF(B$2:B$9,B$2:B$9) --> COUNTIF({100;100;100;13.5;5;76;23;0},{100;100;100;13.5;5;76;23;0})，第二个参数是数组,

针对第二个数组中每个数据在第一个参数出现的次数，也是应为SUMPRODUCT参数是数组，所以这个函数返回的类型是数组。

所以返回值是{3;3;3;1;1;1;1;1},100 出现3次，其他都是一次。

4.   1/(COUNTIF(B$2:B$9,B$2:B$9))-->求导 {1/3,1/3,1/3,1,1,1,1,1}

5.  SUMPRODUCT((B$2:B$9>B5)*(1/(COUNTIF(B$2:B$9,B$2:B$9))))-------针对B5

=SUMPRODUCT({TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}*{1/3,1/3,1/3,1,1,1,1,1})+1

=1(TRUE)*1/3+1(TRUE)1/3+1(TRUE)1/3+0(FALSE)*1+0(FALSE)*1+1(TRUE)*1+1(TRUE)*1+0(FALSE)*1+1

=4 --->排名4

总结：

这里用到SUMPRODUCT（array1，array2，array3），数组对应位置值乘积和，只有一个数组参数时，直接累加所有值。

COUNTIF（range,criteria）按照条件计数，当criteria 为数组时，其结果也是数组。