前往
大廳
小說

【EXCEL】計算特定時間/日期儲存格的平均值

黑泥 | 2022-11-03 22:51:02 | 巴幣 0 | 人氣 392

常蒐集DATALOG的朋友們有福了,
這邊分享一個快速萃取特定時間和日期的函數。
=SUMPRODUCT((資料>=K2)*(資料<=K3)*(資料-INT(資料)>=6/24)*(資料-INT(資料)<=18/24)*E2:E33)/SUMPRODUCT((資料>=K2)*(資料<=K3)*(資料-INT(資料)>=6/24)*(資料-INT(資料)<=18/24))
對你沒看錯,他就是這麼長,且聽我大部分解。
1. 首先針對時間那欄,我的案例是A1~A33,全選後按Ctrl+Shift+F3,他會問你選取範圍建立名稱,選頂端(記得要先給好他一個好記的名字)
2.我們先從F那一欄開始介紹,在F2欄指令打
=((資料-INT(資料)>=6/24)*(資料-INT(資料)<=18/24)
這事讓他判斷"資料"那一欄,有沒有符合6點~18點這時段的判別式,那INT()是啥? 他是將小數點無條件捨去的函數,也就是說資料-INT(資料)會剩下小數點,那其實就是時間(相反整數就是日期),再利用後面判別相乘,表示兩個條件都須達到
3.再來就會複雜一點,從G欄開始,在G2欄打上
=(資料>=K$2)*(資料<=K$3)*(資料-INT(資料)>=6/24)*(資料-INT(資料)<=18/24)
相比前面多了兩個括號,也引用了"開始時間"和"結束時間",所以應該知道這欄要幹嘛了。日期的條件寫法就容易多了,直接讓他判別你要的時間。對照F欄的結果可以發現制約發揮作用了。
4.這裡我們先到I2這格,正常來說,我們如果一格一格去看條件是否有作用,那跟我直接土法煉鋼根本一樣吧? 這裡教你個函數,
SUMPRODUCT(),這函數可以讓不同欄交互相乘並加總,所以我們先驗證加了"時間"限制的格數有多少,打上
=SUMPRODUCT((資料-INT(資料)>=6/24)*(資料-INT(資料)<=18/24))
核對一下。 簡單說他直接幫我省去最後加總的時間/空間。
=SUMPRODUCT((資料>=K2)*(資料<=K3)*(資料-INT(資料)>=6/24)*(資料-INT(資料)<=18/24))
加上時間的限制同理得證,只剩8格。
5.你一定覺得很怪,啊算格數要幹嘛,我是要算平均或是加總诶!
確實,所以到H2,打上最一開始的那個很長的函數,
但這次你應該不會只想把那個函數滑掉了,比起前4步,他只是多*E2:E33以及後面/SUMPRODUCT...這一串。
前面乘的部分就是讓符合條件的格數(數值1)去乘光強度那一欄,再由函數SUMPRODUCT幫我加總,後面那個就是前面算出格數的應用變成分母來算平均值。
後記:因為光度正常來說只有早上才有,雖然季節變化會造成不同的日落時間,但統一設定會比較方便。日期限制在物候可能比較會利用這個方式,因為通常是用特定物候前30日的資料來計算,這樣你學費了嗎?

創作回應

更多創作