贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有網友問到,如下圖的 Excel 資料表,如何根據下圖左的出差記錄(只顯示部分資料),摘要依假別分年/月統計次數?(參考下圖右)

【準備工作】

選取A欄至D欄中有資料的區域,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:日期、早班、午班、晚班。

【輸入公式】

儲存格H2:
=SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(早班=H$1))+
  SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(午班=H$1))+
  SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(晚班=H$1))

其中:

條件(1)  YEAR(日期)=$F2:判斷在日期陣列的年份(2014年)是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

條件(2)  MONTH(日期)=$G2:判斷在日期陣列的月份(1份)是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

條件(3)  早班=H$1:判斷在早班陣列中的內容是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

公式中的「*」會在運算時將 TRUE/FALSE 陣列轉換為 1/0 陣列。

午班=H$1:判斷在午班陣列中的內容是否和儲存格F2(午班)相同,傳回 TRUE/FALSE 陣列。

晚班=H$1:判斷在晚班陣列中的內容是否和儲存格F2(晚班)相同,傳回 TRUE/FALSE 陣列。

複製儲存格H2,貼至儲存格H2:J25。

vincent 發表在 痞客邦 留言(2) 人氣()

有網友問到:在 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 函數來完成。

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼