Summieren von Werten innerhalb eines Zeitrahmens

Summieren von Werten innerhalb eines Zeitrahmens

Bildbeschreibung hier eingebenIch habe 550 Datenzeilen, die die täglichen Flugzeiten darstellen. Für jeden Flug ist, je nach Flugzeugtyp, eine bestimmte Anzahl von Personen erforderlich. Diese Aufgabe dauert 45 Minuten. Zu jeder beliebigen Flugzeit sind also Personen mit diesem Flug sowie allen Flügen innerhalb der vorherigen 45 Minuten beschäftigt. Ich möchte eine Gesamtanzahl der für jede Flugzeit erforderlichen Personen erhalten. Außerdem möchte ich die 45 Minuten als Eingabevariable behandeln, um zu sehen, wie sich Änderungen dieser Aufgabenzeit auf den Personalbedarf zu jeder Flugzeit auswirken.

Hier ist ein Modell, wie die Daten aussehen:

     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

Spalte A enthält die Flugzeiten. Spalte B enthält die Anzahl der für diesen Flug benötigten Personen. Zelle C1 enthält den Wert für die Aufgabenzeit. Ich möchte Spalte D mit der Gesamtzahl der für jede Flugzeit benötigten Mitarbeiter ausfüllen. Das Beispiel wird mit den ausgefüllten Ergebnissen angezeigt.

Eine Beispielberechnung würde so aussehen. Verwenden Sie Zeile 4 als Beispiel. Die Flugzeit beträgt 3:30. Basierend auf einer Aufgabenzeit von 45 Minuten würden alle Flüge ab 2:45 gleichzeitig aktive Personen erfordern. Es gibt drei solcher Flüge: 2:45, 3:15 und 3:30. Die Personalstärke aus Spalte B für diese Flüge beträgt 3, 2 und 4, sodass der Gesamtpersonalbedarf zum Zeitpunkt des Fluges um 3:30 9 beträgt. Das kommt in D4.

Wenn ich die Aufgabenzeit in C1 auf 30 Minuten ändern würde, würden nur die Flüge um 3:15 und 3:30 qualifiziert sein, die Gesamtzahl in D4 wäre also 6.

Ich habe es versucht SUMIFund SUMIFS. Ich habe eine Zeile mit den Daten (Zeit) minus hinzugefügt und dann versucht, alles in diesem Zeitbereich C1zu summieren . Ich kann die Spalte scheinbar nicht dazu bringen, die Daten hinzuzufügen.BB

Ich habe es versucht

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

Antwort1

Hier ist eine Lösung. Dies ist die Formel für D1. Geben Sie sie ein und kopieren Sie sie dann nach unten für andere Zellen in 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)

Dies ist im Grunde die Logik. Es findet die erste Zeile, die Ihren Kriterien entspricht, und summiert dann die Werte in B zwischen dieser und der aktuellen Zeile. Die erste Zeile wird gefunden, indem die MATCH-Funktion verwendet wird, um die Zeit in Spalte A minus der Zeit in C1 mit den Zeitwerten in den Zeilen bis einschließlich der aktuellen Zeile zu vergleichen. MATCH verfügt nicht über eine GE-Suche für Werte in aufsteigender Reihenfolge, daher wird eine Kombination aus LE und EQ verwendet.

  • Wenn dort nichts LT steht, bedeutet das, dass Zeile 1 GE ist, also ist das die erste Zeile.
  • Wenn eine Zeile EQ ist, handelt es sich um die Startzeile.
  • Wenn eine Reihe LE, aber nicht EQ ist, bedeutet das, dass die nächste Reihe die erste GE ist und somit die erste Reihe.

verwandte Informationen