☰

在 Excel 中，如何計算大量時間戳，並按星期幾排序以 5 分鐘為單位排列計數？

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中，如何計算大量時間戳，並按星期幾排序以 5 分鐘為單位排列計數？

我的電子表格中有超過 300,000 行，每行都包含一個時間戳記 (HH:MM:SS AM) 和一個日期 (DD/MM/YYYY)。

我想取得這些時間戳記的計數，並將它們按5 分鐘的時間段、一周中的某一天進行分組（也就是說，我想要周日12:00 到12:05 之間的時間戳數量、 12:05 及週日上午 12:10 等）。我覺得我必須COUNTIF以某種方式使用它，但我無法確切地弄清楚如何實現它。我正在嘗試將結果組織到另一張紙中，看起來像這樣。

在此輸入影像描述

每 5 分鐘週期的計數將出現在中間一列中，在一周中的某一天下方（所以這就是我輸入公式的地方）。

預先感謝您的任何幫助，非常感謝！

編輯：我可能應該包括我的數據如下所示：

在此輸入影像描述

答案1

您可以向資料新增幾個字段，然後產生資料透視表來取得計數。

首先，新增「星期幾」( DOW) 欄位。要DOW使用下面的公式，其中列B包含日期。

=CHOOSE(WEEKDAY(B2,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

接下來，新增一個欄位來表示時間戳所在的 5 分鐘時間段，Time Range。使用下面的公式來計算此值，其中時間戳位於列中A。

=TIME(HOUR(A2),ROUNDDOWN(MINUTE(A2)/5,0)*5,0)

將這些列新增至資料後，建立一個使用所有資料作為其資料來源的資料透視表。

DOW使用列標籤和Time Range行標籤設定資料透視表。將值設為Count of Time Range。

在此輸入影像描述

注意：這個範例看起來有點奇怪，因為我使用稀疏資料（一周 20 筆記錄）來產生資料透視表。它應該看起來更像您對大型數據集的想法。

答案2

與@Excelll推薦的類似，您可以添加一個額外的列來指定星期幾，即在工作表1中，其中存在您的原始數據，並且A列和B列分別保存日期和時間，您可以在C列中使用此函數

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",工作日(A1))

我使用的方法純粹是基於函數。就像您分享的圖像一樣，我為 Sheet 2 定制了公式以產生類似的結構。以下條件檢查時間戳是否存在於表第一列中聲明的特定範圍內，並且此外它是一周中的給定日期。然後它會計算所有這些值。

=COUNTIFS(工作表1!$B:$B, ">"&$A3,工作表1!$B:$B,"<="&$A4,工作表1!$C:$C,"= "&B$2)

此圖顯示如何在儲存格 B1 中輸入上述公式，並有條件檢查時間是否在 12:00 AM 到 12:10 AM 範圍內，並且日期設定為星期日或 1。

在此輸入影像描述

您可能已經注意到這裡的時差為 10 分鐘，我已將其自訂，因此您可以根據您的要求將其設定為 5 分鐘或 2 小時。

由於我們在這裡使用函數，因此資料之間是否有 4 個空行或 10 個空行並不重要。這些功能可確保僅收集非空白資料。我沒有足夠的數據，所以這個例子看起來很空。請尋找隨附的範例 xls這裡。

答案3

注意 - 今晚要對該程式碼進行故障排除，但在值清單中仍存在一些錯誤。目前不可行，只是不想重新格式化我的程式碼行。隨意玩耍。我會同時添加評論和說明。

有人提到這可以用VBA來完成。我喜歡VBA，所以我嘗試了一下。

幾個假設：

1 - 時間戳記和日期不包含在同一儲存格中（即 12:00:00 AM | 7/21/2014 NOT 7/21/2014 00:00:00）

2 - 您希望將所有時間戳記計數分組在單個天數列表中（即僅顯示一組週日到週六，而不是為每一天創建一組新的列 - 如果我們從週五開始，我們不會在週五開始分組，如果我們在兩週後的週二結束，我們就不會有16 列分組）

3 - 從第一行到最後一行的資料中沒有空白儲存格。

4 - 您的時間戳記和日期戳記資料有標題

您應該按 alt+f11，然後開啟包含資料和來源工作表的工作簿，然後輸入此代碼。然後按F5。

Public Sub PrintDateGroups()
Dim icontrol As Integer
Dim iweeknum As Integer
Dim ipasscount As Integer
Dim lngwalktimevalues As Long
Dim ipasstimecount As Integer
icontrol = 1

Do Until icontrol = -1:
If ThisWorkbook.Sheets("Data").Cells(icontrol,2).Value = "" Then
    icontrol = -1

Else

    icontrol = icontrol + 1
    iweeknum = Weekday(ThisWorkbook.Sheets("Data").Cells(icontrol, 1).Value, vbSunday)

    For lngwalktimevalues = 0 To 99999999 Step 694444.4375
        If (TimeValue(Format(ThisWorkbook.Sheets("Data").Cells(icontrol, 2).Value, "hh:mm:ss")) * 100000000) <= lngwalktimevalues Then
            If iweeknum = 1 Then
                If ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value <> "" Then
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, 2).Value + 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = 1
                    Exit For
                End If
            Else
                If ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = "" Then
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, ((3 * iweeknum) + (iweeknum - 2))).Value + 1
                    Exit For
                End If
            End If
        End If
    Next lngwalktimevalues
End If
Loop
End Sub

答案4

您應該能夠將每筆記錄四捨五入到最接近的五分鐘。然後使用excel的小計功能來獲得計數。

我將透過建立新的日期列 (=Day(A1)) 和第二列時間戳來進行舍入。

相關內容