運用在二維交叉統計的陣列公式
在儲存格範圍A1:C20記載了多日來各類別的金額,如今若要進行摘要統計每一天各類別的金額加總,這時候,先建立好二維報表的表頭標題欄名,例如:垂直的日期(E3:E6)以及水平的類別名稱(F2:H2),接著,只要使用Excel 2007以後新增的SUMIFS函數便可迎刃而解。當然,在建立結構性資料表與公式標準化的觀念下,若針對資料表欄名,或者是範圍名稱的訂定,將對於公式的輸入、維護與解讀,會有著莫大的助益~
以下的範例中,儲存格A2:A20已命名為「日期」;儲存格B2:B20已命名為「類別」;儲存格C2:C20已命名為「金額」;因此,根據SUMIF的語法,可以在選取儲存格範圍F3:H6後,輸入公式於左上角首格(F3):
=SUMIFS(金額,日期,$E3,類別,F$2)
然後,按下Ctrl+Enter按鍵,便可以獲得結果:
個人認為,使用Excel建立公式來解決問題,並沒有一定的解法,也沒有非得使用哪一個函數不可!端賴個人的喜愛、熟悉度。基本上,能夠解決當下的問題與困擾才是最重要的,等到技術成熟了,再去研究與討論哪一種方式最適合各種版本的Excel(不一定一味地追求最新版本的要求),以及最符合效率與效能。例如:剛剛的這個範例,只要使用SUM與IF的組合,配合陣列公式的技巧,就不一定要使用SUMIFS函數喔!
例如:作用儲存格停在儲存格F3後,輸入公式:
按下Ctrl+Shift+Enter後,完成陣列公式的輸入:
也就是垂直的日期範圍(E3:E6)和水平的類別名稱(F2:H2)之每一個儲存格內容的串接(&運算)結果,與日期欄位每一筆內容(A2:A20)和類別欄位每一筆內容(B2:B20)之串接($運算)結果,使用IF函數相比較,若相等,則傳回的金額即為陣列的內容,最後再以SUM函數對整個陣列的內容進行加總。
再將這個結果,往下拖曳填滿控點,然後,再往右拖曳填滿控點,完成整個交叉統計的運算:
以此例而言,E3與F2的串接結果為「20191/1/交通」,透過IF函數,與日期欄的第1個日期(A2)和類別欄的第1個類別(B2)之內容串接結果「2019/1/1餐飲」相比較,不相等就沒有作為,但是,若與日期欄的第2個日期(A3)和類別欄的第2個類別(B3)之內容串接結果「2019/1/1交通」相比較是相等的,因此,金額欄的第2個金額(C3),就成為陣列的元素。依此類推,完成每一個欄位成員的串接結果相互比較,這個IF函數的陣列公式應用結果,就會包含2個金額元素的陣列,最後透過SUM進行該陣列元素的加總:
對於E3與G2的串接結果,應該是「20191/1/餐飲」,透過IF函數,與日期欄的第1個日期(A2)和類別欄的第1個類別(B2)之內容串接結果「2019/1/1餐飲」相比較,因為相等,因此,金額欄的第1個金額(C2),就成為陣列的元素。但是,若與日期欄的第2個日期(A3)和類別欄的第2個類別(B3)之內容串接結果「2019/1/1交通」相比較,並不相等就沒有作為,依此類推,完成每一個欄位成員的串接結果相互比較後,19筆資料便經過19次的IF函數比對,藉由陣列公式應用的結果,所傳回的陣列將會有3個金額元素,最後透過SUM進行該陣列元素的加總:
對於SUM與IF函數的組合,並以陣列的形式完成運算,是不是很有意思呢~
註:此Excel陣列函數系列文章內文實作活頁簿檔案載點。