2015年3月11日 星期三

Excel-計算文字或數字日期之符合特定年月的總和(SUMPRODUCT)

在 Excel 的資料表中,如果要符合特定年月日期的總和,通常你會使用 SUMPRODUCT 函數。因為在符合雙條件之下要計算總和,使用 SUMPRODUCT 函數以乘積和的概念來計算,讓公式顯的簡單易懂。

網友問到:日期由於是輸入或由其他來源導入,所以有可能以數字型態呈現,也可能以文字型態呈現。公式應該如何來設計呢?

以下圖為例,日期是數字型態。先選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數字日期、數值。

儲存格E3:=SUMPRODUCT((YEAR(數字日期)=2014)*(MONTH(數字日期)=4)*數值)

以下圖為例,日期是文字型態。先選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:文字日期、數值。

儲存格E3:=SUMPRODUCT((YEAR(文字日期)=2014)*(MONTH(文字日期)=4)*數值)

和數字型態的公式比較,其公式是可以相通的,也就是不管日期是數字或文字,相同公式都可以取得結果。

如果使用另一個公式,也可以得到結果:

儲存格E3:=SUMPRODUCT((LEFT(文字日期,7)="2014/04")*數值)

但是如果在數字日期中使用相同公式,則所得結果皆為 0:

儲存格E3:=SUMPRODUCT((LEFT(數字日期,7)="2014/04")*數值)

沒有留言:

張貼留言

好康東東