如下圖:
在E2單元格輸入公式:
=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))
確定,然後公式向下填充,即可得組內A、B、C三組組內排名。
公式解析
$C$2:$C$14=C2:
在C2:C14區域的 每一個單元格與C2相比較,如果相等返回TRUE,否則返回FALSE。本部分返回數組:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}(數組一)
$D$2:$D$14>=D2:
在D2:D14區域的 每一個單元格與D2相比較,如果大於或等於D2返回TRUE,否則返回FALSE。本部分返回數組:
{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE ;FALSE }(數組二)
COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14):
查找C列D列從第2行到第14行每一行出現的次數。本部分得數組:
{2;1;1;2;1;1;1;1;1;1;1;1;1}(數組三)
=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14)):
數組一*數組二/數組三,得到的數組{0.5;0;0;0.5;0;0;0;0;0;0;0;0;0},數組內數據加和,即得第一位的排名。
此公式修正公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2))”並列第一卻出現並列“第二”的bug。如下圖:
閱讀更多 韓老師講office 的文章