☰

在 Excel 中，如何计算大量时间戳，并按星期几排序以 5 分钟为周期的计数？

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中，如何计算大量时间戳，并按星期几排序以 5 分钟为周期的计数？

我的电子表格中有超过 300,000 行，每行都包含一个时间戳 (HH:MM:SS AM) 和一个日期 (DD/MM/YYYY)。

我想获取这些时间戳的计数，并按星期几将它们分组为 5 分钟的时间段（即，我想要周日 12:00AM 至 12:05AM、周日 12:05AM 至 12:10AM 等之间的时间戳数）。我觉得我必须以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

与 @Excellll 推荐的类似，您可以添加一个额外的列来指定星期几，即在工作表 1 中，其中存在原始数据，A 列和 B 列分别包含日期和时间，您可以在 C 列中使用此函数

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",WEEKDAY(A1))

我使用的方法完全基于函数。就像您分享的图片一样，我为 Sheet 2 自定义了公式以生成类似的结构。以下条件检查时间戳是否存在于表格第一列中声明的特定范围内，以及它是否是给定的星期几。然后它会计算所有这些值。

=COUNTIFS(Sheet1!$B:$B,">"&$A3,Sheet1!$B:$B,"<="&$A4,Sheet1!$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 不是 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 的 subtal 函数获取计数。

我将通过创建一个新的日期列（=Day（A1））和第二个时间戳列来进行四舍五入。

相关内容