問題情境
如下樣表:
有“店長組”和“組員”兩個組別,要求保持數據順序不變,分別對“店長組”和“組員”兩個組內的成績排序。
公式實現
在E2單元格輸入公式:
=SUMPRODUCT(N(($D$2:$D$11=D2)*($C$2:$C$11)>C2))+1
確定,並往下填充,可得“店長組”和“組員”兩個組內的成績排序。
如下圖:
公式解析
$D$2:$D$11=D2:
判斷D2:D11區域內每個單元格值是否與D2相等,如果相等返回TRUE,不相等返回FALSE,所以本部分返回值為TRUE和FALSE組成的數組:
{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}(以下稱數組一)。
($D$2:$D$11=D2)*($C$2:$C$11):
數組一與C2:C11對應位置數值相乘,返回數組:
{80;0;0;70;100;0;85;0;0;70}{以下稱數組二}。
($D$2:$D$11=D2)*($C$2:$C$11)>C2:
數組二中各數值與C2比較,如果大於C2返回TRUE,否則返回FALSE,本部分返回值為:
{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}{以下稱數組三}。
N(($D$2:$D$11=D2)*($C$2:$C$11)>C2):
將數組三中的邏輯值轉換為數值,得到數組:
{0;0;0;0;1;0;1;0;0;0}{以下稱數組四}。
SUMPRODUCT(N(($D$2:$D$11=D2)*($C$2:$C$11)>C2))+1:
將數組四中數值加和再加1,得排名。
閱讀更多 韓老師講office 的文章