如何用Excel按時間區間匯總交易量

昨天寫了一篇如何在Excel打開超過1G的CSV文件，有個分析任務還沒完成，就是要把一天的交易數量按每小時匯總，每兩小時匯總，或者按照不規則的時間區間匯總交易量。

這樣的分析應該在大多數的交易平臺，電商平臺都要做的，觀察交易數量的變化，尖峰時段，低峰時段，看趨勢變化。原始數據通常都是以毫秒記錄的，甚至高頻的達到微秒級別。這樣的粒度，很精細，數據太多用Excel無法畫圖，得借用其他工具才能畫出趨勢圖。為了利於分析，掌握總體的數據分布，我們可以把粒度調粗，把數據按小時總結，減少數據數量。

如果每個區間都是相同的小時數，那有些低峰期的區間數據就會很稀疏，我們可以考慮把這些這些區間合併起來組成一個大區間，比如凌晨時段00-06為低峰期，那就把這六個小時歸為一區間，剩餘時間平均分區間。

常用的把時間合併成區間的方法有FLOOR, TRUNC,VLOOKUP可參見以下介紹：https://www.excelcampus.com/charts/group-times-in-excel/

然鵝，這三種函數在大量的數據面前就不是那麼直觀好用了。本文將只介紹如何把時間平均劃分區間，非均勻區間的，留在下一篇解決。

還是老規矩，先聽一首歌，最近經常聽到這一首：

進入正題，首先還是把超過1G的CSV文檔打開，打開方式參考如何在Excel打開超過1G的CSV文件。

打開之後，在菜單欄目找到"Data"欄目，點擊一個小的綠色圖標"go to power pivot window"，打開Power Pivot 窗口.

先做數據清理，這裡要清理的數據是時間戳，時間戳的格式是這樣的，

01/30/2021 05:00:08.743000 HKT +0800

我們要把這些時間戳按小時歸在一個區間，要用到的函數Floor。

舉個慄子，比如時間為02/03/2021 12:18:00 AM, 那麼它在12:00 AM這個區間，02/03/2021 01:20:00 AM, 在1:00 AM這個區間。

這樣我們就會得到一天24小時，每一小時作為一個區間的交易數量匯總。

那首先我們把我們的時間戳變成標準格式 "MM/DD/YYYY HH:MM:SS AM/PM" 的格式，把後面的「HKT +0800」 去掉。用到的函數是:

=FORMAT(LEFT([TIME],19),"mm/dd/yyyy hh:mm:ss AM/PM")
把數據變成想要的格式之後，就要歸成我們想要的區間了，用到的函數是：
=FLOOR(Number,Significance)
具體操作是，在表格最後添加一列，可自由命名，這裡按一小時劃分區間，可以命名為「1H interval」。 如果每兩小時一區間， 就命名為」2H interval「，如此類推。
最後的函數為:
=FLOOR(FORMAT(LEFT([Time],19),"mm/dd/yyyy hh:mm:ss AM/PM"),"2:00")
但實際操作上，為了避免錯誤，可以把每個函數單獨一列，更方便debug。
最後的數據處理結果如下，最後的列是我們要用到的。
LEFT : =LEFT([TIME],19)
FORMAT: =FORMAT(LEFT,"mm/dd/yyyy hh:mm:ss AM/PM")
FLOOR: =FLOOR(FORMAT,"2:00")
2H interval: =FORMAT([FLOOR],"HH:MM:SS AM/PM")
最後的步驟就是畫圖了。
回到主菜單欄，找到pivot table，打開新的窗口。
然後就可以把新建的列【2H interval】」選擇作為行，用訂單號來計算交易數量，因為訂單號是唯一不重複的。
最後的趨勢圖為如下：可以看出，在下午4點的交易量是最多的。
如下圖顯示，可以選擇多個篩選器，比如信息類型，用戶類型，居住地作為篩選條件，分析不同特徵數據的趨勢變化。
這個辦法有個缺點是區間行在pivot table裡不是按時間順序排序的，因為排序功能沒有按時間智能排序。
所以我新建了一個表格，如上圖綠色的，手動的把時間區間按時間順序排序，然後用Vlookup把對應的數據填上，這樣在變換篩選器的時候，數據會相應變化。然後圖表是用這個新建的表格畫出來的線型圖。
最後，如果有很多天的數據，我們可以暫且把這所有天數的同一時間段的數據都匯總起來，但這麼做有個缺點是，如果每天的走勢是不一樣的，那麼這樣簡單疊加出來的數據就無法看出實際的走勢。更好的處理辦法可以留在再下一篇解決。
今天就分享到這，晚安，好眠。