810 |
如何計算符合日期的筆數與總和 |
||||
示範檔 |
無 |
範例檔 |
810.XLSX |
結果檔 |
810F.XLSX |
其實子老師用了年的時光一直努力在寫Excel教學文了,普遍都是因為在這個世界中找到成就感,今天剛好又有學生來問了一個計算的問題?
1.要怎麼計算符合日期範圍的筆數有多少?
2.符合條件相對應的欄位加總是多少?
這樣的描述不知大家是否有看懂,沒懂沒關係,往下來看看我的做法比較快。
如何計算符合日期的數量或欄位總和?
首先來看看下圖這張表格列出來的需求,我們主要的條件就是「日期」,需求有二個,一個是計算 1~12 月分別有多少數量,另外是分別計算出 1~12月各有多少收入,我們往下來看看怎麼做。
首先第一個是符合日期條件的筆數各有多少筆,因此我們可以使用Countifs函數來完成就好,公式如下:
1.在H2儲存格輸入公式「=COUNTIFS(A:A,">=2018/1/1",A:A,"<=2018/1/31")」後,按Enter鍵完成輸入。
簡單的解說一下,A:A 就是A整欄去做判斷,只要日期區間在2018/1/1 ~ 2018/1/31 就符合條件列入計算,因此可以得出一月份有多少筆,2~12 月只要再修改日期區間就可以了,結果如下圖所示,很簡單吧。
第二個需求是,分別計算出1~12月收入加總,因此在公式我們必須要符合「日期範圍」後,去加總對應的欄位,這時我們可以來用大家較不使用的SUMPRODUCT函數,這個函數可以做的事情真的很多,用在這地方算是很簡單的用法,公式如下:
1.在I2儲存格輸入公式「=SUMPRODUCT((A$2:A$21>=D2)*(A$2:A$21<=E2)*B$2:B$21)」後,按Enter鍵完成輸入。
這樣的意思是,先把符合的條件的儲存格內容放入陣列,同樣是符合日期區間後,再把對應的B2~B21 之間符合條件的儲存格內容加總起來,因此可以得到1月的總收入有多少。依此方式再修改日期區間就可以得到2~12月各別的數字,結果如下圖所示。
最後一欄的平均收入,就用總收入除以筆數(=IF(ISERROR(I2/H2),"",I2/H2))就好了,簡單的加減乘除,完全不需要用函數來做,結果如下圖所示。
留言列表