☰

计算日期范围列表中某个日期的出现次数

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
计算日期范围列表中某个日期的出现次数

我有一张 Excel 2010 表格，其中列出了事件，提供了事件的详细信息以及每个事件的开始和结束日期。这些事件发生在一年内。我需要创建一个包含该时间范围内所有日期的列表（已完成），并计算出该日期每天发生的事件数量。例如，2017 年 6 月 4 日，有 3 起事件。所以我的原始列表将如下所示：

Event ID  Event Name  Start Date    End Date
A12411    Event One   June 1, 2017  June 10, 2017
A45721    Event Two   June 1, 2017  June 5, 2017
A84541    Event Three June 2, 2017  June 4, 2017

ETC...

根据以上数据，我希望我的列表显示：

Date          Number of events
June 1, 2017  2
June 2, 2017  3
June 3, 2017  3
June 4, 2017  3
June 5, 2017  2
etc...

我尝试过 Countifs，包括常规版本和数组版本，但都没有成功（它似乎计算了第一个事件的发生次数，但没有计算另一个......）我尝试了 If(and()) 构造，但成功率更低（很多 #Values）。

我可以为其编写一个 VBA 函数，但这似乎有点小题大做，尤其是因为这是我第一次遇到这种情况并且不认为它会重复发生。

答案1

给定列中的一堆日期F和一个ListObject带有的“表”以及包含事件开始日期和结束日期的列，我使用以下公式得到了看似正确的From结果：ToCOUNTIFS

=COUNTIFS(Table1[From],"<="&$F2,Table1[To],">="&$F2)

FWIW我使用这个VBA函数得到了相同的结果：

Public Function CountFor(ByVal calendarDate As Date, ByVal eventDates As Range) As Long
    Dim dates As Variant
    dates = eventDates.Value

    'assert eventDates consists of 2 columns
    Debug.Assert UBound(dates, 2) = 2
    Const StartDateColumn = 1
    Const EndDateColumn = 2

    Dim result As Long

    Dim eventIndex As Long
    For eventIndex = LBound(dates, 1) To UBound(dates, 1)
        If dates(eventIndex, StartDateColumn) <= calendarDate And dates(eventIndex, EndDateColumn) >= calendarDate Then result = result + 1
    Next

    CountFor = result

End Function

使用方法如下：

=COUNTFOR($F2,Table1[[From]:[To]])

答案2

因此，如果您的事件列表在 C 列中有开始日期，在 D 列中有结束日期，则在 F2 中的日期列表中，在 G2 中使用此公式

=COUNTIFS(C:C,"<="&F2,D:D,">="&F2)

把这个公式复制到下面的列中，你就会得到所需的计数

相关内容