2015年1月31日 星期六

Excel-分日期計算不重覆項目數(SUMPRODUCT,COUNTIF,OFFSET)

在下圖中是一個 Excel 的資料表,如果要計算各個日期中不含空白的不重覆資料個數,該如何處理?

 

【設計公式】

選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

思考之後,發現得設計一個輔助欄位才能順利設計公式。

儲存格C2:=IF(ISBLANK(B2),"???",B2)

若B欄的儲存格內容為空白,則顯示「???」,這個內容不可以和B欄中的項目內容有所重覆。

儲存格F2:
=SUMPRODUCT(1/COUNTIF(OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(
日期,E2),),OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),)))-IF(COUNTIF(
OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),),"???")>=1,1,0)

公式這麼長是要嚇死人嗎?稍微來分解一下:

OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),):找出合於儲存格E2的日期儲存格範圍。(別名:日期E2)

其中 MATCH 函數找出符合儲存格E2的第一個儲存格位置,COUNTIF 函數則找出和儲存格E2內容相同的儲存格有幾個。

原公式中變化為二個部分:

(1) SUMPRODUCT(1/COUNTIF(日期E2,日期E2))

求出符合儲存格E2內容的儲存格範圍中包含「???」的不重覆個數。

(若要進一步了解可參考:Excel-計算不重覆的數值個數

(2) IF(COUNTIF(日期E2,"???")>=1,1,0)

求出符合儲存格E2內容的儲存格範圍中是否包含「???」,若是傳回 1,若否傳回 0。

將公式 (1)-(2),即為所求。

沒有留言:

張貼留言

檢視其他文章

好康東東