2015年11月24日 星期二

Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)

在 Excel 中有個日期和時間所構成的資料清單,如何找出在某個時間區間中的筆數?
Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)
【公式設計與解析】
選取儲存格A1:A29,按 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:資料。
因為 Excel 將 1 天(24小時)以 1 表示,也就是說 1 小時為 1/24。
(1) 時段:08:00~12:00
儲存格G2:=SUMPRODUCT((資料-INT(資料)>=8/24)*(資料-INT(資料)<12 p="">
資料-INT(資料):取得每個日期的時數部分。(INT(資料)為日期所代表的值)
(資料-INT(資料)>=8/24)*(資料-INT(資料)<12 12="" 8="" nbsp="" strong="">AND
 運算,在運算過程式中將判斷結果的TRUE/FALSE 轉換為 1/0。再由 SUMPRODUCT 函數執行乘積和,把所有的 1 加總即為所求。
(2) 時段:12:00~16:00
儲存格G3:=SUMPRODUCT((資料-INT(資料)>=12/24)*(資料-INT(資料)<16 p="">
(3) 時段16:00~20:00
儲存格G4:=SUMPRODUCT((資料-INT(資料)>=16/24)*(資料-INT(資料)<20 p="">

【延伸討論】
如果你想要使用自動篩選工具,會發現其只能處理到『日期』,而沒有辦法處理『時間』。
Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)
若使用自訂篩選也只能篩選日期:
Excel-計算某一時間區間內的數量(SUMPRODUCT,INT)

沒有留言:

張貼留言

檢視其他文章

好康東東