Сумма значений в течение периода времени

Question

У меня есть 550 строк данных, которые представляют ежедневное время полета. Каждый рейс, в зависимости от типа самолета, требует определенного количества людей для участия в полете. Эта задача занимает 45 минут. Таким образом, в любое время полета люди будут заняты этим рейсом плюс любые рейсы в течение предыдущих 45 минут. Я хочу получить общее количество людей, необходимых в любое время полета. Я также хочу обработать 45 минут как входную переменную, чтобы увидеть, как изменения в этом задании влияют на потребность в рабочей силе во время каждого полета.

Вот макет того, как выглядят данные:

     A            B         C.        D
1   2:30          4        0:45       4
2   2:45          3.                  7
3   3:15          2.                  9
4   3:30          4.                  9
5   3:35          5.                 11

Столбец А содержит время полета. Столбец B содержит количество людей, необходимых для этого полета. Ячейка C1 содержит значение для времени задачи. Я хочу заполнить столбец D совокупным количеством персонала, необходимого для каждого времени полета. Пример показан с заполненными результатами.

Пример расчета будет выглядеть следующим образом. Используйте строку 4 в качестве примера. Время полета 3:30. Исходя из 45-минутного времени выполнения задачи, для всех полетов в 2:45 или после этой даты требуются одновременно активные люди. Есть три таких полета, 2:45, 3:15 и 3:30. Штатное расписание из столбца B для этих рейсов составляет 3, 2 и 4, поэтому общая потребность в персонале во время полета в 3:30 составляет 9. Вот что идет в D4.

Если бы я изменил время выполнения задания в C1 на 30 минут, квалифицировались бы только рейсы в 3:15 и 3:30, поэтому общее количество в D4 составило бы 6.

Я пробовал SUMIF и SUMIFS . Я добавил строку с данными (время) минус C1 затем попытался суммировать все в B между этими временными диапазонами. Я не могу получить столбец B чтобы добавить данные.

Я старался

    =sumif(A:A,"<=A4",B:B)-sumif(A:A,"<=A4-C1",B:B)

Answer 1

Вот одно из решений. Это формула для D1. Введите его, а затем скопируйте для других ячеек в D:

    =SUM(INDIRECT("b"&IF(ISNA(MATCH(A1-C$1,A$1:A1,0)),IF(ISNA(MATCH(A1-C$1,A$1:A1,1)),1,MATCH(A1-C$1,A$1:A1,1)+1),MATCH(A1-C$1,A$1:A1,0))):B1)

Это в основном логика. Он находит первую строку, которая соответствует вашим критериям, а затем суммирует значения в B между ними и текущей строкой. Он находит первую строку, используя функцию MATCH для сравнения времени в столбце A минус время в C1 со значениями времени в строках до и включая текущую строку. У MATCH нет поиска GE для значений в возрастающем порядке, поэтому здесь используется комбинация LE и EQ.

• Если нет ничего LT, это означает, что строка 1 является GE, так что это первая строка.
• Если строка EQ, это начальная строка.
• Если строка является LE, но не EQ, это означает, что следующая строка является первой GE, так что это первая строка.