2014年8月12日 星期二

Excel-找出日期清單中星期幾的業績最好(SUMPRODUCT)

有網友問到:在 Excel 中有一個日期清單,其中含有每天的業績,如何判定星期幾的業績最好呢?(參考下圖,其中有許多列的資料被隱藏了。)

【準備工作】

選取儲存格A1:C182,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、業績。

【輸入公式】

先算出各星期幾的業績總和:

儲存格F2:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*業績)

WEEKDAY(日期,2):判斷在「日期」陣列中,使用 WEEKDAY 函數傳回一個數值,其中參數 2 表示星期一傳回 1、星期二傳回 2、…、星期日傳回 7。

WEEKDAY(日期,2)=ROW(1:1):ROW(1:1)=1,為判斷 ROW(1:1) 是否和 WEEKDAY 函數的傳回值1 相等。將公式往下複製時,會產生ROW(2:2)=2、ROW(3:3)=3、…、ROW(7:7)=7。本式會傳回 TRUE/FALSE 的陣列。

(WEEKDAY(日期,2)=ROW(1:1))*業績:其由「*」運算,可以將 TRUE/FALSE 陣列,轉換為 1/0 陣列,再與「業績」相乘。

最後再由 SUMPRODUCT 函數傳回「乘積和」,即為所求。

複製儲存格F2,貼至儲存格F2:F8。

關於名次的運算,可以使用 RANK 函數來完成。

沒有留言:

張貼留言

檢視其他文章

好康東東