在 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,即為其排名。
【補充資料】
詳細函數說明請參閱微軟網站:
SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。 |
語法:SUMPRODUCT(array1, [array2], [array3], ...) array1:要求對應元素乘積和的第一個陣列引數。 array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
留言列表