Excel 2003 - Berechnung der Anzahl von Zeiträumen in einem Zeitraum

Ondas_tm_86f522 · 12. November 2019 um 18:39

Hallo,
ich habe ein Problem, bei dem ich hoffe, dass mir jemand helfen kann. Ich versuche es möglichst plastisch zu beschreiben, damit klar wird, was ich brauche.
Ich habe eine Excel-Tabelle mit Zeiten, diese umfasst Startzeit und Endzeit von Fahrzeugen. Nun möchte ich wissen, wieviele Fahrzeuge in einem bestimmten Zeitraum unterwegs waren.

Beispiel
Fahrzeug1 startet um 8:00 und beendet die Fahrt um 9:00Uhr.
Fahrzeug2 startet um 8:30Uhr und beendet die Fahrt um 10Uhr.

Ich will nun wissen, wieviele Fahrzeuge waren in definierten Zeiträumen unterwegs z.B.
7:30-8:29 = 1
8:00-9:00 = 2
9:01-10:00 = 1
usw.
Ich habe folgende Formel probiert:
=SUMMENPRODUKT(((U3:U847)>=ZEIT(7;0;0))*((T3:T847)

abgemeldet5 · 12. November 2019 um 18:39

Hallo Ralf
bei mir funktioniert die Formel einwandfrei,
wobei ich jetzt direkt mit der Liste der Zeiträume vergleiche:

Schau mal

gruß Holger

Ondas_tm_86f522 · 12. November 2019 um 18:39

hm… ich komme bei einer Liste mit 833 Zeilen auf ca. 250 Zählungen. Das kann vorne und hinten nicht passen.
Insbesondere aufgrund der Tatsache, dass es häufig über einen Stundenwechsel gehen, würde ich ein Ergebnis von über 1000 erwarten.

Beispiel

=SUMMENPRODUKT(((Q2:Q834)>=ZEIT(6;0;0))*((P2:stuck_out_tongue:834)=ZEIT(7;0;0))*((P2:stuck_out_tongue:834)

abgemeldet5 · 12. November 2019 um 18:40

hm… ich komme bei einer Liste mit 833 Zeilen auf ca. 250 Zählungen. Das kann vorne und hinten nicht passen. Insbesondere aufgrund der Tatsache, dass es häufig über einen Stundenwechsel gehen, würde ich ein Ergebnis von über 1000 erwarten

OK, aber die Formel ist richtig.
Das lässt vermuten das die Uhrzeiten nicht das sind was sie scheinen.

Mal ein Vorschlag zum Test:
Du könntest die Bedingte Formatierung nutzen um die Zeilen zu markieren die richtig gezählt werden:
Markiere die kompletten Spalten zB. die Spalten P:Q
bedingte Formatierung >neue Regel >Formel zur Ermittlung …
=(($Q1)>=ZEIT(6;0;0))*(($P1)ok

So könntest du die Zeiten anpassen, und versuchen die fehlerhaften Zeilen ermitteln.
Möglicherweise auch die zeit mal über den kompletten Zeitraum erweitern.
Dann müssen ja alle Zeilen markiert werden

Gruß Holger

Thomas_Ramel · 12. November 2019 um 18:40

Grüezi Ondas

Mal abgesehen davon, dass es (für mich) ungewöhnlich ist, dass die Endzeit in der Spalte vor der Startzeit erfasst wird, passen die Bedingungen die Du gesetzt hast nicht mit deinen Vorgaben zusammen

=SUMMENPRODUKT(((Q2:Q834)>=ZEIT(6;0;0))*((P2:stuck_out_tongue:834) vorher werden nicht berücksichtigt

=SUMMENPRODUKT(((Q2:Q834)>=ZEIT(7;0;0))*((P2:stuck_out_tongue:834)vorher werden nicht berücksichtigt

Bei 1. soll er Fahrten zählen, die

vor 6uhr begannnen und mindestens bis 6Uhr, aber auch
länger, gingen

Diese Bedingung wird von deiner Formel nicht berücksichtigt, da Du auf >= 6:00 prüfst

nach 6Uhr begannen und mindestens bis 6Uhr, aber auch
länger, gingen

aber keine, die nach 7Uhr begannen

IMO müsstest Du da dann für die Startzeit prüfen ob sie **ist und die Endzeit >7:00:

=SUMMENPRODUKT(((Q2:Q834)ZEIT(7;0;0)

Prüfe bitte auch nochmal ob es den Tatsachen entspricht, dass die Endzeit in Spalte P und die Startzeit in Spalte Q steht - wenn ja, dann würde mich persönlich interessieren, warum das so ist.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -**

Thomas_Ramel · 12. November 2019 um 18:40

Grüezi Onda

Ich habe mir deine Ausführungen nochmals angesehen und komme inzwischen zu einem etwas anderen Schluss als vorhin.

Verwirrend ist (nur für mich?) nach wie vor, dass die Startzeit offenbar in einer Spalte nach der Endzeit steht, aber das nur am Rande.

Ich habe ein paar Test-Daten angelegt und eine Liste mit den Stunden dazu, diese immer von bis.

Nochmals zur Definition, da Du hier wwieder

Spalte Q wäre hier dann was genau?
Spalte P wäre hier dann was genan?

=SUMMENPRODUKT(((Q2:Q834)>=ZEIT(6;0;0))*((P2:stuck_out_tongue:834)=ZEIT(7;0;0))*((P2:stuck_out_tongue:834)Tabellenblatt: [Mappe1]!Tabelle1

│ Q │ R │ S │ T │ U │ V │ W │ X │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
1 │ Startzeit │ Endzeit │ │ │ │ Start │ Ende │ │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
2 │ 01:46:44 │ 15:33:52 │ │ │ │ 00:00 │ 01:00 │ 7 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
3 │ 01:59:53 │ 09:09:55 │ │ │ │ 01:00 │ 02:00 │ 2 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
4 │ 02:30:57 │ 07:07:05 │ │ │ │ 02:00 │ 03:00 │ 3 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
5 │ 03:39:13 │ 23:12:48 │ │ │ │ 03:00 │ 04:00 │ 5 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
6 │ 03:53:57 │ 15:51:11 │ │ │ │ 04:00 │ 05:00 │ 6 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
7 │ 04:41:19 │ 19:57:14 │ │ │ │ 05:00 │ 06:00 │ 9 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
8 │ 05:18:15 │ 17:52:54 │ │ │ │ 06:00 │ 07:00 │ 9 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
9 │ 05:28:56 │ 00:50:45 │ │ │ │ 07:00 │ 08:00 │ 9 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
10 │ 05:39:09 │ 10:28:41 │ │ │ │ 08:00 │ 09:00 │ 10 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
11 │ 08:29:13 │ 06:49:51 │ │ │ │ 09:00 │ 10:00 │ 10 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
12 │ 08:55:23 │ 11:18:05 │ │ │ │ 10:00 │ 11:00 │ 10 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
13 │ 10:05:43 │ 04:22:47 │ │ │ │ 11:00 │ 12:00 │ 13 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
14 │ 11:17:27 │ 10:28:54 │ │ │ │ 12:00 │ 13:00 │ 14 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
15 │ 11:21:08 │ 15:46:21 │ │ │ │ 13:00 │ 14:00 │ 17 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
16 │ 11:46:25 │ 21:11:04 │ │ │ │ 14:00 │ 15:00 │ 17 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
17 │ 11:48:48 │ 10:59:30 │ │ │ │ 15:00 │ 16:00 │ 20 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
18 │ 12:15:35 │ 04:04:55 │ │ │ │ 16:00 │ 17:00 │ 16 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
19 │ 12:41:14 │ 19:07:51 │ │ │ │ 17:00 │ 18:00 │ 16 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
20 │ 13:19:33 │ 07:53:25 │ │ │ │ 18:00 │ 19:00 │ 15 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
21 │ 13:34:55 │ 04:27:56 │ │ │ │ 19:00 │ 20:00 │ 15 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
22 │ 13:40:54 │ 15:12:39 │ │ │ │ 20:00 │ 21:00 │ 13 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
23 │ 15:10:36 │ 21:44:04 │ │ │ │ 21:00 │ 22:00 │ 13 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
24 │ 15:15:22 │ 04:35:14 │ │ │ │ 22:00 │ 23:00 │ 11 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
25 │ 15:18:31 │ 13:39:33 │ │ │ │ 23:00 │ 24:00 │ 11 │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
26 │ │ │ │ │ │ │ │ │
───┼───────────┼──────────┼───┼───┼───┼───────┼───────┼─────┤
27 │ │ │ │ │ │ │ Total │ 271 │
───┴───────────┴──────────┴───┴───┴───┴───────┴───────┴─────┘
Benutzte Formeln:
X2 : =SUMMENPRODUKT((Start=V2))
X27: =SUMME(X2:X25)

Festgelegte Namen:
Ende : =Tabelle1!$R$2:blush:R$32
Start: =Tabelle1!$Q$2:blush:Q$32

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

abgemeldet5 · 12. November 2019 um 18:40

ahhhh… klar… Das verdrehen von Abfahrt und Ankunft.
da habe ich jetzt gar nicht dran gedacht

Für mich war das irgendwie klar,
wenn man feststellen möchte ob eine Fahrtzeit innerhalb eines Zeitraums liegt muss
>die Abfahrtszeit vor dem Ende des Zeitraums liegen und
>die Ankunftszeit muss später wie der Beginn des Zeitraums sein.

Die Formel von Thomas zählt jetzt auch Nachtfahrten nach 24:00 Uhr.

Ich habe folgende Formel probiert:
=SUMMENPRODUKT(((U3:U847)>=ZEIT(7;0;0))*((T3:T847)

Ondas_tm_86f522 · 12. November 2019 um 18:42

Ich habe nochmal nach Deinem Posting eine Testtabelle erstellt, die Bedigungen nach Deinen Anregungen überprüft und es scheint (bei meiner Testtabelle) zu funktionieren.

Ich mache mir gleich nochmal die Arbeit und zähle die echte Tabelle durch.

Auf jeden Fall stimmt mich das schon wirklich sehr optimistisch!
Dafür auf jeden Fall schon mal ein großes Dankeschön!

Ralf

abgemeldet5 · 12. November 2019 um 18:42

Hallo Ralf
Schön das es wie gewünscht funktioniert.

Für mich war der Weg der Berechnung eigendlich immer klar.
Dann schaut man dummer weise aber gar nicht mehr so recht nach was dort bei dir in der Formel steht.

Du kannst dich bei Thomas bedanken,
das er den Dreher in deiner Formel bemerkt hat.
Und jetzt klappt es auch nach 24:00h

Grüße an alle
Holger