2012年12月16日 星期日

Excel-計算符合標準者的名次(SUMPRODUCT)

在 Excel 中有一個業績報表(參考下圖),如果依照達成率來排名次,則參考下圖F欄的結果。但是,如果想要設定業績指標,超過一定值者才列入排名,參考下圖E欄的結果,該如何處m理?

【準備作業】

選取儲存格A1:D23,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、業績、目標、達成率。

選取儲存格A1:H2,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:指標。

 

這個計算要求,無法以 RANK 函數直接完成,所以要以變通方式來思考。

【輸入公式】

儲存格E2:=IF(B2>=指標,SUMPRODUCT((業績>=指標)*(達成率>D2))+1,"未達標準")

IF(B2>=指標,…,"未達標準"):如果業績未達指標者,列出「未達標準」。

SUMPRODUCT((業績>=指標)*(達成率>D2))+1:找出符合「業績>=指標」的 True/False 陣列,並且找出「達成率>儲存格D2」的 True/False 陣列,透過 SUMPRODUCT 函數,可以計算符合這兩條件的個數。這個個數也是業績比儲存格D2還要好的個數,將其加 1,即為其排名。

 

【補充資料】

詳細函數說明請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東