Office: (Office 365) Zeiten mit Bedingungen in Intervallen summieren

Hallo Community,

ich benötige erneut einen Denkanstoß, da ich vor einem für mich unlösbaren Problem stehe.
Die Tabelle enthält viele Status, welche mit Start und Enduhrzeit geloggt wurden.

Ich muss nun für ein bestimmtes Datum N1 und zwei Status P1 und Q1 die entsprechend Sekunden summieren, die in den besagter Zeit angefallen sind, wenn der Status P1 ODER Q1 ist.

Ich habe bereits mit Summenproduct experimentiert - leider erfolglos...

Hat jemand von euch den golden Hinweis für mich, wie ich es lösen könnte?

Herzlichen Dank und viele Grüße
Karlheinz

 

Hallo Karlheinz,

was an Deiner Formel falsch ist kann ich nicht schreiben, weil Excel sie nicht finden konnte.
Wo steht Sie?

 

Hallo Hajo,

ich habe es mit einer Hilfsspalten versucht, kam dadurch allerdings nicht einmal ansatzweise in die gewünschte Richtung, daher habe ich sie entfernt.
Problem hier, dass so ledlich die Differenz der Startzeit zum Start des Intervalls ermitteln kann, siehe Anhang.

Danke und viele Grüße
Karlheinz

 

Hola,

was erwartest du denn ganz konkret für Ergebnisse? Soll der Wert aus Spalte J im Zeitraum von B und C gesucht werden?
Du hast übrigens eine Menge überflüssiger Klammern...
=SUMMENPRODUKT(($E$2:$E$241=$J2)*($A$2:$A$241=N$1)*($D$2:$D$241={"green"."yellow"})*($F$2:$F$241))
Gruß,
steve1da

 

Servus,

Danke.

Ich möchte, dass ALLE Zeiten, die sich aus den Zeiträumen aus B und C ergeben, in Abhängigkeit zum Datum in N1 in den Zeilen in K summiert werden
Irgendwie bringe ich es allerdings nicht fertig!.

Viele Grüße
Karlheinz

 

Hola,
konkret ist anders....
In Spalte J stehen Uhrzeiten. Was soll da genau abgeglichen werden?
Welche Zahl erwartest du z.B. in K2 genau?
Gruß,
steve1da

 

Servus,

ich versuche es noch mal.
J sind Intervalluhrzeiten, welche bspw. von 09:00Uhr-09:30 Uhr gehen, der nächste Intervall von 09:30Uhr-10:00Uhr usw.
In K2 sollen alle Zeiten summiert werden, welche sich aus BB und CC ergeben.

Beispiel:
B2 13:57:19 C2 14:14:17
Somit habe ich 00:02:41 im Intervall 13:30 und 00:15:43 im 14:00 Intervall.
Leider habe ich ja etliche derartige Zeiten, so dass das jeweils aufsummiert werden muss.

Hoffe, ich konnte es etwas verständlicher erklären.

Herzliche Grüße
Karlheinz

 

Hallo Karlheinz,

in der beiliegenden Exceldatei ist deine Datei in modifizierter Form enthalten:
1) Aus deiner Tabelle in A1:D241 habe ich eine strukturierte Tabelle namens "TabZeiten" gemacht;
2) Aus deiner Intervalltabelle in J1:J31 habe ich eine strukturierte Tabelle namens "TabIntervalle" gemacht.

Da ich nicht wusste, welche Funktionen du zur Verfügung hast in deiner Excel365-Version, habe ich dir mehrere Varianten eingebaut:
1) Im Bereich K2:K31 habe ich dir Formeln eingefügt, die die LET-Funktion verwenden
2) Im Bereich L2:L31 habe ich dir Formeln eingefügt, die das Gleiche tun wie 1), aber ohne LET-Funktion
3) Im Bereich M2 habe ich dir eine Formel eingefügt, die im Prinzip das Gleiche tut wie 1), aber als dynamische VBA-Array-Funktion, die ihre Ergebnisse im Bereich M2:M31 "verschüttet", wie das Microsoft formuliert.
Sie lautet: =SummeKondZeitintervalle($A$1;$J$1;$N$1;$P$1;$Q$1)
Du könntest aber auch schreiben: =SummeKondZeitintervalle(TabZeiten;TabIntervalle;$N$1;$P$1;$Q$1)
wobei eine Filterung nach Datum (N1) und nach Status (P1, Q1) der Daten aus Tabelle "TabZeiten" erfolgt.

4) Darüber hinaus habe ich dir im Bereich I2:I31 LET-Formeln eingefügt, die zwar die Aufgiederung nach "TabIntervalle" durchführen, aber keine Filterung nach N1 bzw. P1 und Q1.
5) Im Bereich N2 habe ich wieder eine dynamische VBA-Array-Funktion eingefügt, die das Gleiche wie unter 4) macht, die aber wieder ihre Ergebnisse in den Bereich N2:N31 "verschüttet".

6) Im Bereich G32:N32 sind Kontrollsummen enthalten, um die Gleichheit der Formeln in 1) bis 5) zu zeigen.
7) In Zelle N34 ist die Gesamtsumme aller Intervalle aus Tabelle "TabZeiten" enthalten:
=SUMME(REST(TabZeiten[Ende]-TabZeiten[Beginn];1))
Die REST-.Funktion war hier nötig (sie ist auch in den Formeln aus 1) bis 5) enthalten), weil einige Intervalle in Tabelle "TabZeiten" eine ENDE-Zeit aufweisen, die bereits zum folgenden Tag gehört (zB. Bereich A210:D210 oder A214:D214, etc.)

 

Servus Anton,

WOW! Vielen herzlichen Dank - das funktioniert einwandfrei.
Leider kann ich diese Lösung so nicht benutzen, da ich die Funktionsweise durch die Verwendung der Makros nicht nachvollziehen/verstehen kann. Ich habe bereits versucht, aber ich habe nahezu keine VBA Kenntnisse.
Wenn die =SUMME Formel in L ohne VBA funktionieren würde, wäre es für mich perfekt.

Ich wünsche schöne Pfingsttage und nochmals vielen herzlichen Dank.

Karlheinz

 

Hallo Karlheinz,

wenn du damit die Formeln im Bereich L2:L31 meinst, die funktionieren ja alle ohne VBA. Sie sind so gestaltet, dass sie mit Excel-Versionen ab Excel2007 funktionieren müssten. Sie funktionieren bei mir deshalb auch einwandfrei.
Dass diese Formeln eine solche Länge haben (400 Zeichen), dafür kann ich nichts. Sie wären erheblich kürzer, wären nicht im Datenbereich A1:D241 mehrere Zeilen enthalten, die sich auf 2 Tage beziehen (auf den Tag, der in Spalte A steht und auf den Folgetag), wie zB.in A71:D71 (Beginn 22:20:10, Ende=00:15:10) oder in A123:D123 (Beginn=23:12:03, Ende=00:13:03) oder etc.

Die Formeln in Spalte K (K2:K31) verwenden die LET-Funktion. Diese sind wesentlich kürzer, leisten dasselbe wie die L-Formeln und sind sicher auch schneller. Ich weiß aber nicht, ob dir die LET-Formel (in manchen Versionen ist sie eingedeutscht und heißt dann SEI(...)) zur Verfügung steht.

Die Formel in M2 (mit Rückgabewerten in M2:M31) ist eine benutzerdefinierte VBA-Array-Funktion. Wie du dich leicht überzeugen kannst, liefert sie dieselben Resultate wie die Funktionen in den beiden vorerwähnten Spalten.
Sie lautet (M2): =SummeKondZeitintervalle(TabZeiten;TabIntervalle;$N$1;$P$1;$Q$1)
Die Funktion "SummeKondZeitintervalle(...)" benötigt 5 Parameter:
Der 1.Parameter (im Beispiel "TabZeiten") ist der Name der strukturierten Tabelle "TabZeiten (Bereich A1:D241), wobei statt dessen auch eine einzelne Zelle aus diesem Bereich angegeben werden kann: zB. C17.
Der 2.Parameter (im Beispiel "TabIntervalle") ist der Name der strukturierten Tabelle "TabIntervalle" (Bereich J1:J31), wobei auch hier statt dessen auch eine einzelne Zelle aus diesem Bereich angegeben werden kann: zB: J9.
Der 3.Parameter enthält das Datum (im Beispiel "$N$1"), das sich auf die 1.Spalte von Tabelle "TabZeiten" bezieht, sodass daraus die passenden Zeilen gefiltert werden können.
Der 4. und 5. Parameter enthält einen Statuswert (im Beispiel "$P$1" und "$Q$1") . Sie beziehen sich auf die 4.Spalte der Tabelle "TabZeiten", sodass mit ihnen daraus die passenden Zeilen gefiltert werden können.
Diese Funktion würde daher auch mit folgendem Aufruf in M2 =SummeKondZeitintervalle($C$17;$J$9;$N$1;$P$1;$Q$1) die gleichen Werte in M2:M31 liefern. Dabei richtet sich die Größe des Rückgabebereiches (im Beispiel M2:M31) nach der Größe des Bereiches, der durch den 2.Parameter festgelegt wird. Enthielte also die Tabelle "TabIntervalle" nur 10 Datenzeilen (ohne Überschrift), enthielte damit auch der Rückgabebereich der Funktion "SummeKondZeitintervalle" nur 10 Zeilen.

 

Vielen Dank - ich habe es jetzt in meiner Datei angepasst. Auf den Ansatz wäre ich wahrscheinlich nie gekommen!
Die Formeln in Spalte L funktionieren - mit einer Einschränkung - einwandfrei!
Jetzt muss ich noch recherchieren, wie ich die Überhänge, die zum Folgetag gehören, auch im Folgetag dem entsprechenden Intervall zugeordnet werden können.

Herzliche Grüße
Karlheinz