隨著 Excel 不斷的演進,不同版本可以使用的函數也不盡相同,所以在陣列公式的運算上,適用的公式做法也不一樣。

以下分別就依年和月的小計,以不同的三種作法來比較。

Excel-比較不同陣列公式的寫法(依年或月小計)

【公式設計與解析】

選取儲存格A3:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。

 

1. 依年小計

(1) 儲存格E4:{=SUM(IF(YEAR(日期)=D4,數值))}

輸入完要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

利用 YEAR 函數取得日期的「年」。

(2) 儲存格E4:=SUMPRODUCT((YEAR(日期)=D4)*數值)

(3) 儲存格E4:=SUM(FILTER(數值,YEAR(日期)=D4))

Filter函數要2021以上版本才有。

複製儲存格E4後,貼至儲存格E4:E8。

 

2. 依月小計

(1) 儲存格H4:{=SUM(IF(MONTH(日期)=ROW(1:1),數值))}

輸入完要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

利用 MONTH 函數取得日期的「月」。

ROW(1:1) 會傳回列號(=1),向下複製公式時會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(2) 儲存格H4:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*數值)

(3) 儲存格H4:=IFERROR(SUM(FILTER(數值,MONTH(日期)=ROW(1:1))),0)

Filter函數要2021以上版本才有。

複製儲存格H4後,貼至儲存格H4:H15。

學不完.教不停.用不盡文章列表

    文章標籤

    Excel 陣列公式 資料處理

    全站熱搜

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