2016年2月16日 星期二

Excel-如何正確取出日期中的月份(MONTH,SUMPRODUCT,N)

網友問到:在下圖中的 Excel 資料表中,如果要計算三個條件都成立的個數有幾個,該如何處理,為何下圖中的公式會『錯誤』。其中的重點是要滿足日期中符合所要月份的條件。
選取儲存格A1:C27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、項目。
Excel-如何正確取出日期中的月份(MONTH,SUMPRODUCT,N)

【公式說明】
儲存格F4:=SUMPRODUCT((人員=F1)*(MONTH(日期)=F2)*(項目=F3))
MONTH(日期)=F2:取出日期陣列中的『月份』數值。
(人員=F1)*(MONTH(日期)=F2)*(項目=F3):『*』相當於執行邏輯 AND 運算。三個條件都成立者傳回 TRUE,不成立者傳回 FALSE,在 SUMPRODUCT 函數的乘積和運算時,會將 TRUE/FALSE 轉換為 1/0

為何以下的公式會錯誤?
(X)儲存格F4:=SUMPRODUCT((人員=F1)*(VALUE(LEFT(日期,2))=F2)*(項目=F3))
以圖中的儲存格B25為例,雖然顯示04月23日,但是內部儲存為42483,當你執行:
VALUE(LEFT(日期,2))會傳回『42』(42483的前2碼),並非你所需的月份『04』。
如果你想取得儲存格B25的數值,可以使用公式:=N(B25)。

沒有留言:

張貼留言

檢視其他文章

好康東東