☰

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

tag-icon • tag-icon microsoft-excel-2010
Суммируйте значения в течение определенного периода времени

введите описание изображения здесьУ меня есть 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

Столбец A содержит время полета. Столбец 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)

решение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, то есть это первая строка.

Связанный контент