Подсчет вхождений даты в списке диапазонов дат

Question

У меня есть таблица Excel 2010, в которой перечислены события, подробно описывающие событие, а также даты начала и окончания каждого события. Эти события происходили в течение года. Мне нужно создать список всех дат за этот промежуток времени (уже сделано) и подсчитать за каждый день, сколько событий происходило в эту дату. то есть 4 июня 2017 года было 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

так далее...

И из приведенных выше данных я хотел бы, чтобы мой список показывал:

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 (и ()), но имел еще меньший успех (много значений #).

Я мог бы написать для нее функцию VBA, но это кажется излишним, тем более, что я впервые столкнулся с этим и не вижу, что это повторяется.

Answer 1

Учитывая несколько дат в столбце F и ListObject / "table" с столбцами From и To содержащими даты начала и окончания событий, я получаю, казалось бы, правильные результаты с этой формулой COUNTIFS :

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

Я получаю идентичные результаты с этой функцией 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]])

Answer 2

Так что, если в вашем списке событий есть даты начала в столбце C и даты окончания в столбце D, тогда при вашем списке дат в F2 используйте эту формулу в G2

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

скопируйте эту формулу вниз по столбцу, и вы получите желаемое количество