Excel中的“多關(guān)鍵字”排名公式

在Excel中對某列進(jìn)行排名時通常使用RANK函數(shù)，但有時排名需要考慮多列的情況，例如在對B列排名時，如果B列有相同的數(shù)值，則按C列排名。通常，要獲得這樣的多列排名結(jié)果，可以在“排序”對話框中依次選擇或添加“主要關(guān)鍵字”和“次要關(guān)鍵字”，然后進(jìn)行排序即可。但在不改變表格結(jié)構(gòu)的情況下，要獲得“多關(guān)鍵字”的排名，可以用下面的一些公式。

    例如下圖為某電視節(jié)目在播出日期內(nèi)的收視率和收視份額，需要用公式返該日期內(nèi)按收視率的降序排名，在收視率的排名相同時，則按收視份額降序排名。其中D列為直接使用RANK函數(shù)對B列排名的結(jié)果，可以看到，B列數(shù)值相同時，其排名也是相同的，不符合要求。 假如數(shù)據(jù)在A1:E22區(qū)域，在E2中輸入公式：
    =RANK(B2,B$2:B$22)+SUMPRODUCT(N((B$2:B$22=B2)*(C$2:C$22>C2)))
    然后向下填充公式即可得到先按B列后按C列降序的排名結(jié)果。
    上述公式先用RANK函數(shù)獲得初步的排名，對于B列數(shù)值相同的情況，用SUMPRODUCT函數(shù)得到這些相同值對應(yīng)C列數(shù)值的“排名”，最后返回所需結(jié)果。也可使用下面的兩個公式：
   =SUMPRODUCT(N((B2*1000+C2)<(B$2:B$22*1000+C$2:C$22)))+1
    該公式將B列的數(shù)值乘以一個相對同行C列數(shù)值較大的數(shù)，如本例為“1000”，再加上同行C列的值，最后比較合計值得到排名。類似地也可以用C列除以一個較大數(shù)值：
    =SUMPRODUCT(N((B2+C2/1000)<(B$2:B$22+C$2:C$22/1000)))+1
    另外，如果用于排名的數(shù)值都為非負(fù)整數(shù)，可用“&”連接各列數(shù)值，再用SUMPRODUCT函數(shù)排名。如下圖A1:E21區(qū)域為某公司年終考核的數(shù)據(jù)，要求先按總分列降序排名，如果總分相同，則依次按“業(yè)務(wù)能力”和“工作態(tài)度”列降序排名。

在F2中輸入公式：
    =SUMPRODUCT(N(E2&B2&C2<(E$2:E$21&B$2:B$21&C$2:C$21)))+1
    然后填充公式到F21即可。