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 Super-Moderator

    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 Super-Moderator
    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. E-Mails werden nach gewisser Zeit in Spam verschoben

    in Microsoft Outlook Hilfe
    E-Mails werden nach gewisser Zeit in Spam verschoben: Hallo Zusammen, ich erhalte E-Mails die zu Beginn in meinem Posteingang zu sehen sind und automatisch nach einer gewissen Zeit (ca. 15 min.) in meinem Spamordner verschoben werden. Es ist keine...
  3. Datei schließen nach bestimmter Zeit.

    in Microsoft Excel Hilfe
    Datei schließen nach bestimmter Zeit.: Hallo Zusammen, habe ein paar Dateien die ich auch gerne von zu Hause bearbeiten möchte. In diese Dateien habe ich ein Makro aus dem Netz eingebaut. Es gibt auch noch ein Makro welches alle 30...
  4. Outlook Kalender Zeiten ausblenden

    in Microsoft Outlook Hilfe
    Outlook Kalender Zeiten ausblenden: Hallo Zusammen, gibt es die Möglichkeit im Outlook Kalender "nicht benötigte Zeiten" auszublenden? Bsp: Ich arbeite normalerweise zwischen 7 und 18 Uhr deswegen interessiert mich das Zeitfenster...
  5. Zeitüberschneidungen verschiedener Tage automatisch erkennen

    in Microsoft Excel Hilfe
    Zeitüberschneidungen verschiedener Tage automatisch erkennen: Hallo ihr Lieben, meine Mutter trägt ihre Arbeitszeiten regelmäßig händisch in eine Excel-Tabelle ein. Dabei steht in Spalte A das Datum, in Spalte B die Anfangszeit des Arbeitspakets und in...
  6. Nach Zeit X auf Blatt springen

    in Microsoft Excel Hilfe
    Nach Zeit X auf Blatt springen: Hallo Zusammen, in einer xlsm Datei habe ich etwa 10 Tabellenblätter. Wenn z.B Tabellenblatt 4 offen ist werden die zeit gesteuerten Makros nicht gestartet. Wenn ich dann auf Tabellenblatt 1...
  7. Bei Videokonferenzen mit Teams hängt sich der Laptop nach kurzer Zeit auf (Bild friert ein)

    in Microsoft Teams Hilfe
    Bei Videokonferenzen mit Teams hängt sich der Laptop nach kurzer Zeit auf (Bild friert ein): Liebe Alle! Ich bin Lehrerin und habe folgendes Problem: Bei Videokonferenzen bei Teams hängt sich jedes Mal mein Laptop auf. Zuerst frieren die Bilder der Schüler ein, dann kann ich auch meine...
  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