SUMPRODUCT分組排名公式

SUMPRODUCT分組排名公式

前幾天，一篇中的公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2))”出現了bug——如果在某組中出現并列第一的情況，結果顯示為并列“第二”。

如下圖：

今天再給一個更嚴密的公式。

公式實現

在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))

確定，然后公式向下填充，即可得組內排名。

如下圖：

公式解析

$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}，數組內數據加和，即得第一位的排名。

此公式修正了并列第一卻出現并列“第二”的bug。如下圖：

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。