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

Helfe beim Thema Zeiten mit Bedingungen in Intervallen summieren in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von KarlheinzLaur, 18. Mai 2021.

  1. KarlheinzLaur Erfahrener User

    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
     
    KarlheinzLaur, 18. Mai 2021
    #1
  2. Hajo_Zi
    Hajo_Zi Erfahrener User
    Hallo Karlheinz,

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

    Zeiten mit Bedingungen in Intervallen summieren GrußformelZeiten mit Bedingungen in Intervallen summieren Homepage
     
    Hajo_Zi, 18. Mai 2021
    #2
  3. KarlheinzLaur Erfahrener User
    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
     
    KarlheinzLaur, 18. Mai 2021
    #3
  4. steve1da Office Guru

    Zeiten mit Bedingungen in Intervallen summieren

    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
     
    steve1da, 19. Mai 2021
    #4
  5. KarlheinzLaur Erfahrener User
    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
     
    KarlheinzLaur, 20. Mai 2021
    #5
  6. steve1da Office Guru
    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
     
    steve1da, 20. Mai 2021
    #6
  7. KarlheinzLaur Erfahrener User
    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
     
    Zuletzt bearbeitet: 20. Mai 2021
    KarlheinzLaur, 20. Mai 2021
    #7
  8. Exl121150 Erfahrener User

    Zeiten mit Bedingungen in Intervallen summieren

    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.)
     
    Zuletzt bearbeitet: 22. Mai 2021
    Exl121150, 22. Mai 2021
    #8
    1 Person gefällt das.
  9. KarlheinzLaur Erfahrener User
    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
     
    KarlheinzLaur, 23. Mai 2021
    #9
  10. Exl121150 Erfahrener User
    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.


     
    Exl121150, 23. Mai 2021
    #10
    2 Person(en) gefällt das.
  11. KarlheinzLaur Erfahrener User
    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! Zeiten mit Bedingungen in Intervallen summieren *:)*
    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
     
    KarlheinzLaur, 23. Mai 2021
    #11
Thema:

Zeiten mit Bedingungen in Intervallen summieren

Die Seite wird geladen...
  1. Zeiten mit Bedingungen in Intervallen summieren - Similar Threads - Zeiten Bedingungen Intervallen

  2. Zeit Berechnung mit mehreren kriterien (Datum/Uhrzeit)

    in Microsoft Excel Hilfe
    Zeit Berechnung mit mehreren kriterien (Datum/Uhrzeit): Hallo zusammen, leider weiss ich nicht wie ich die Start- und Endzeit von "Datum und Uhrzeit" mit mehreren kriterienin über Summewenns einbauen kann. Hab hier im Forum nach einer Lösung geschaut...
  3. Runden bei Zeiten

    in Microsoft Excel Hilfe
    Runden bei Zeiten: Hallo, ich habe folgendes Problem, wenn ich Zeiten Runden auf 2 Kommastellen gerne hätte kommt immer 00:00,00 heraus =MIN(B4:B9)/100*95 das ist die bisherige Formel, das wird zwar richtig...
  4. Bedingte Formatierung, Leere Zelle ab best. Zeit einfärben und bei Eintrag Farbe weg.

    in Microsoft Excel Hilfe
    Bedingte Formatierung, Leere Zelle ab best. Zeit einfärben und bei Eintrag Farbe weg.: Guten Tag, ich habe, eigentlich, ein simples Problem und bekomme einfach nicht die Funktion korrekt geschrieben. Ich würde gerne eine Bedingte Formatierung durchführen für eine Zelle, die Bezug...
  5. Datum und Zeit aus einer Zelle extrahieren

    in Microsoft Excel Hilfe
    Datum und Zeit aus einer Zelle extrahieren: Moin zusammen, ich habe einen Datenexport gemacht und aus diesem eine Tabelle gemacht. Jetzt habe ich aber als Datum eine Angabe die aus Datum und Zeit in einer Zelle besteht. Beispiel: 2024-01-08...
  6. Sollarbeitszeit mit SVERWEIS

    in Microsoft Excel Hilfe
    Sollarbeitszeit mit SVERWEIS: HiHo Ich stehe mal wieder vor einem (von vielen) Problem(en) bei meiner Exeltabelle. Es geht hierbei um die Sollarbeitszeit. =WENN( ODER(...
  7. Pivot Zeit Aufgaben je Arbeitszeit

    in Microsoft Excel Hilfe
    Pivot Zeit Aufgaben je Arbeitszeit: Moin zusammen, ich habe ne Tabelle wo ich verschiedene Aufgaben von Mitarbeitern habe. Da ich diese filtern und in der Darstellung variieren möchte, habe ich das ganze in eine Pivot gepackt. Nun...
  8. Zeitspannen miteinander addieren (Bedingungen)

    in Microsoft Excel Hilfe
    Zeitspannen miteinander addieren (Bedingungen): Hallo zusammen. In Zeile A habe ich die Startuhrzeit. In Zeile B die Enduhrzeit. In Zeile C errechne ich die Zeitspanne in Stunden. In Zeile D steht der Wochentag, an dem der Termin...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden