Wenn Pause kleiner 30 Sek dann Summe

[Reismann]

Hallo Excel Experten,

ich benötige mal wieder ein wenig Hilfe.
Da ihr mir schon mal so schön geholfen habt mein Problem zu lösen bin ich guter Dinge dass es jetzt auch wieder eine Lösung für mein Problem gibt.

Ich möchte in meine Tabelle die Summe der Dauer Berechnen wenn die Pause kleiner 10 Sekunden beträgt. Um das ganze ein wenig zu verdeutlichen habe ich ein Beispiel angehängt:

   

Es werden Ereignisse automatisch aufgelistet. (Bei der Auflistung und Sortierung wurde mir hier ja schon wunderbar geholfen. Siehe http://www.clever-excel-forum.de/Thread-...ne-Spalten)


Da die Anzahl der Einträge teilweise ziemlich hoch ist möchte ich nun eine weitere automatische Berechnung einfügen um die Auswertung zu perfektionieren.

In der Spalte "G" soll wie im Beispiel gezeigt die Gesamtdauer von allen Ereignissen, welche weniger wie 30 Sekunden auseinander liegen, berechnet werden. Das ganze sollte natürlich auch noch funktionieren wenn es mehrere solcher Einträge in der Auflistung gibt. Wenn man den Bereich, dann noch automatisch markieren könnte wäre es perfekt.

Hat da jemand eine Idee zu?

Viele Grüße,
Reismann

[atilla]

Hallo Reismann,

stell bitte einen Tabellenausschnit (kein Bild) oder eine Beispielmappe ein, dann kann Dir sicher geholfen werden.

Möchtest Du eine Formellösung oder kann VBA eingestzt werden?

[Reismann]

Den Anhang hatte ich natürlich vergessen.... Asche auf mein Haupt   Beispieldatei hängt jetzt an.

  Auswertung.xlsm (Größe: 48,17 KB / Downloads: 9)

Ob das ganze mit Formel oder VBA gelöst wird ist mir egal. Formel wäre mir lieber, muss aber nicht.

Edit: In der Mappe existieren die Spalten, in der das ganze funktionieren soll 7 mal. Für jeden Wochentag eine Gruppe von Spalten. Denke das ist aber ersichtlich wenn man die Mappe öffnet. Habe ja für den ersten Tag schon ein Beispiel mit drin.

Vielen Dank schon mal vorab!

[atilla]

Hallo Reisman,


für mich sind Deine Angaben nicht schlüssig.Einmal schreibst Du kleiner als 30 Sekunden dann wieder kleiner als 10 Sekunden.

Wenn ich kleiner als 30 Sekunden heranziehe verstehe ich die Markierung in Deinem eingestellten Bild nicht.
Denn nach meinem Verständnis erhalte ich andere Bereiche und Summen.

Schau mal:

Arbeitsblatt mit dem Namen 'Wochenübersicht'
	B	C	D	E	F	G	H
2	Datum	Start	Ende	Dauer	Pause		Summe
3	04.01.2016	12:57:53	12:58:15	00:00:22			00:18:38
4	04.01.2016	12:59:05	12:59:13	00:00:08	00:00:50
5	04.01.2016	13:19:52	13:20:09	00:00:17	00:20:39
6	04.01.2016	13:20:18	13:26:32	00:06:14	00:00:09
7	04.01.2016	13:26:40	13:28:00	00:01:20	00:00:08
8	04.01.2016	13:28:27	13:31:02	00:02:35	00:00:27	00:10:26	00:10:09
9	04.01.2016	13:33:01	13:34:34	00:01:33	00:01:59
10	04.01.2016	13:56:23	13:56:27	00:00:04	00:21:49
11	04.01.2016	16:25:49	16:26:24	00:00:35	02:29:22
12	04.01.2016	16:27:39	16:27:41	00:00:02	00:01:15
13	04.01.2016	16:37:50	16:39:40	00:01:50	00:10:09
14	04.01.2016	16:39:56	16:39:58	00:00:02	00:00:16
15	04.01.2016	16:40:10	16:40:35	00:00:25	00:00:12	00:00:27
16	04.01.2016	17:48:06	17:48:13	00:00:07	01:07:31
17	04.01.2016	17:48:22	17:48:48	00:00:26	00:00:09	00:00:26
18	04.01.2016	18:18:18	18:19:06	00:00:48	00:29:30
19	04.01.2016	18:20:28	18:21:13	00:00:45	00:01:22
20	04.01.2016	18:29:33	18:29:58	00:00:25	00:08:20
21	04.01.2016	18:30:22	18:30:44	00:00:22	00:00:24	00:00:22
22	04.01.2016	18:52:16	18:52:28	00:00:12	00:21:32
23	04.01.2016	20:47:34	20:47:37	00:00:03	01:55:06
24	04.01.2016	20:47:49	20:47:50	00:00:01	00:00:12	00:00:01
25	04.01.2016	21:58:40	21:58:42	00:00:02	01:10:50

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Kannst Du noch einmal anhand eines Beispiels genau beschreiben was Du erwartest.
Ist der Einsatz von VBA möglich?

[Reismann]

Hallo Atilla,

Da habe ich leider ein paar Zahlen in den Posts durcheinander geworfen. Im Prinzip hast du es in der von dir geposteten Tabelle aber richtig erfasst. 
Ich versuche es noch mal anhand deines Posts zu erläutern:

F6= Pause zwischen Eintrag in Zeile 5 und 6
gleiches gilt für F7 und F8 im Bezug auf Zeile 6/7 und Zeile 7/8

Da die Pausen kleiner 30 Sekunden sind soll die Dauer von den vier Einträgen (E5 bis E8) addiert werden und wenn möglich irgendwie farblich gruppiert werden damit man eine Übersicht hat. Die Summe soll dann wie bei dir im Beispiel neben dem letzten Eintrag erscheinen. (G8 in deinem Beispiel)

In der Zelle H3 berechne ich mir nur die Gesamtsumme der Spalte E. Dort muss sonst nichts berechnet werden. Wenn das, was ich in der folgenden Tabelle manuell mache automatisiert funktionieren würde, wäre mein Ziel im Prinzip erreicht.

Wochenübersicht

	A	B	C	D	E	F	G	H	I
1		Montag
2		Datum	Start	Ende	Dauer	Pause		Summe
3		04.01.2016	12:57:53	12:58:15	00:00:22			00:18:38
4		04.01.2016	12:59:05	12:59:13	00:00:08	00:00:50
5		04.01.2016	13:19:52	13:20:09	00:00:17	00:20:39
6		04.01.2016	13:20:18	13:26:32	00:06:14	00:00:09
7		04.01.2016	13:26:40	13:28:00	00:01:20	00:00:08
8		04.01.2016	13:28:27	13:31:02	00:02:35	00:00:27	00:10:26
9		04.01.2016	13:33:01	13:34:34	00:01:33	00:01:59
10		04.01.2016	13:56:23	13:56:27	00:00:04	00:21:49
11		04.01.2016	16:25:49	16:26:24	00:00:35	02:29:22
12		04.01.2016	16:27:39	16:27:41	00:00:02	00:01:15
13		04.01.2016	16:37:50	16:39:40	00:01:50	00:10:09
14		04.01.2016	16:39:56	16:39:58	00:00:02	00:00:16
15		04.01.2016	16:40:10	16:40:35	00:00:25	00:00:12	00:02:17
16		04.01.2016	17:48:06	17:48:13	00:00:07	01:07:31
17		04.01.2016	17:48:22	17:48:48	00:00:26	00:00:09	00:00:33
18		04.01.2016	18:18:18	18:19:06	00:00:48	00:29:30
19		04.01.2016	18:20:28	18:21:13	00:00:45	00:01:22
20		04.01.2016	18:29:33	18:29:58	00:00:25	00:08:20
21		04.01.2016	18:30:22	18:30:44	00:00:22	00:00:24	00:00:47
22		04.01.2016	18:52:16	18:52:28	00:00:12	00:21:32
23		04.01.2016	20:47:34	20:47:37	00:00:03	01:55:06
24		04.01.2016	20:47:49	20:47:50	00:00:01	00:00:12	00:00:04
25		04.01.2016	21:58:40	21:58:42	00:00:02	01:10:50

Formeln der Tabelle

Zelle Formel H3 =SUMME(E3:E25) F6 =C6-D5 F7 =C7-D6 F8 =C8-D7 G8 =SUMME(E5:E8) G15 =SUMME(E13:E15) G17 =SUMME(E16:E17) G21 =SUMME(E20:E21) G24 =SUMME(E23:E24)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Ich hoffe so ist es verständlicher. Sorry für die chaotische Beschreibung.

Einsatz von VBA ist möglich!

[Reismann]

Sorry für den Doppelpost, aber hat keiner eine Idee wie ich das Problem lösen könnte?

[atilla]

Hallo Reismann,

Tut mir sehr leid, Dich hatte ich ganz aus den Augen verloren.
Von daher ist es sehr gut, dass Du den Beitrag noch einmal nach oben
geholt hast.

Ich kann Dir für heute Abend keine Lösung versprechen,
aber im Laufe des morgigen Vormitags müsste ich etwas vorzeigen können.

[atilla]

Hallo Reismann,

ich habe da mal etwas zusammengebastelt.
Da die einzelnen Wochentage nicht gleich aufgebaut sind, habe ich es erst mal für den Montag aus Deiner eingestellten Datei angepasst.
Ich gehe mal davon aus, das in die anderen Wochentage noch Spalten hinzukommen. Wenn der Gesamtaufbau steht, dann kann der Code erweitert werden.

Unten stehenden Code bitte in einer Kopie Deiner Datei in das Codefenster der Tabelle mit den Wochentagen einfügen:

Code:

Option Explicit

Sub Teilsummen()
 Dim i As Long, j As Long
 Dim lngZ As Long
 lngZ = Cells(Rows.Count, 2).End(xlUp).Row
 Range(Cells(2, 5), Cells(lngZ, 6)).Interior.ColorIndex = xlNone
 i = 4
 Do
   If Cells(i, 6) < 1 / 86400 * 30 Then
     j = i
     Do While Cells(i + 1, 6) < 1 / 86400 * 30
       i = i + 1
     Loop
     Cells(i, 7) = Application.Sum(Range(Cells(j - 1, 5), Cells(i, 5)))
     Range(Cells(j - 1, 5), Cells(i, 5)).Interior.ColorIndex = 50
     Range(Cells(j, 6), Cells(i, 6)).Interior.ColorIndex = 6
     j = 0
   End If
   i = i + 1
 Loop While Cells(i, 6) <> ""
End Sub

[Reismann]

Hi Atilla,

Ich bin begeistert. Das ist schon fast Perfekt.
Wie du richtig vermutet hattes, soll die Auswertung für die restlichen Tage auch erfolgen. Die Spalten hätte ich auch direkt einfügen können um dir Arbeit zu ersparen. Kannst du mir erklären wie ich den Code auf die restlichen Tage erweitere? (Datei mit allen Spalten habe ich noch mal angehängt)

  Auswertung.xlsm (Größe: 69,42 KB / Downloads: 4)

Ein kleines Problem mit dem Code ist mir aber schon aufgefallen. Wenn sich die Datensätze ändern und ich den Code erneut ausführe, ändern sich zwar die eingefärbten Bereiche, die "alten" Berechnungen der Summe verschwinden aber nicht. In der Tabelle findest du zwei von mir rot eingefärbte Zeiten, die aus der vorherigen Berechnung mit anderen Zeiten stammt. Kann man den Code so anpassen dass die Spalte ab Zelle 3 abwärts vor Berechnung geleert wird?

Vielen Dank auf jeden Fall für die wie immer vorbildliche Unterstützung hier im Forum. Ihr alle seid der Wahnsinn! Danke!

[atilla]

Hallo Reismann,

mit dem Löschen war mir auch schon aufgefallen.

Hier der erweiterte Code für alle Tage:

Code:

Option Explicit

Sub Teilsummen()
  Dim i As Long, j As Long, t As Long
  Dim lngZ As Long
  lngZ = Cells(Rows.Count, 2).End(xlUp).Row

  For t = 6 To 54 Step 8
    lngZ = Cells(Rows.Count, 2).End(xlUp).Row
    Range(Cells(2, t + 1), Cells(lngZ, t + 1)).ClearContents
    Range(Cells(2, t - 1), Cells(lngZ, t)).Interior.ColorIndex = xlNone
    i = 4
    j = 0
    Do
      If Cells(i, t) < 1 / 86400 * 30 Then
        j = i
        Do While (Cells(i + 1, t) < 1 / 86400 * 30) And (Cells(i + 1, t - 1) <> "")
          i = i + 1
        Loop
        Cells(i, t + 1) = Application.Sum(Range(Cells(j - 1, t - 1), Cells(i, t - 1)))
        Range(Cells(j - 1, t - 1), Cells(i, t - 1)).Interior.ColorIndex = 50
        Range(Cells(j, t), Cells(i, t)).Interior.ColorIndex = 6
        j = 0
      End If
      i = i + 1
    Loop While Cells(i, t) <> ""
  Next t
End Sub