Office: (Office 365) Zeitdauer berechnen Start/Ende in versch. Spalten

hallo zusammen,
Start und Ende eines Ereignisses stehen in unterschiedlichen Zeilen und ich möchte daraus mittels power query die Dauer je Ereignis in je einer Zeile berechnen. Habs mit entpivotieren nicht geschafft und wäre für guten Rat sehr dankbar! LG

 

ich hätte es über Formneln gelöst.

Tabelle1

	E	F	G	H
2	Ereignis	Anfang	Ende	Dauer
3	Ereignis 10	00:00:02	00:03:59	00:03:57
4	Ereignis 11	00:03:39	00:07:16	00:03:37
5	Ereignis 12	00:07:44	00:11:23	00:03:39
6	Ereignis 13	00:11:38	00:15:45	00:04:07
7	Ereignis 14	00:16:00	00:20:22	00:04:22
8	Ereignis 15	00:20:37	00:25:14	00:04:37
9	Ereignis 16	00:25:29	00:30:21	00:04:52
10	Ereignis 17	00:28:36	00:31:43	00:03:07
11	Ereignis 18	00:31:58	00:35:20	00:03:22
12	Ereignis 19	00:35:35	00:39:12	00:03:37
13	Ereignis 20	00:39:27	00:43:29	00:04:02
14	Ereignis 21	00:43:34	00:47:41	00:04:07
15	Ereignis 22	00:47:56	00:52:18	00:04:22
16	Ereignis 23	00:52:33	00:57:10	00:04:37
17	Ereignis 24	00:57:25	01:02:17	00:04:52
18	Ereignis 25	01:00:32	01:03:39	00:03:07
19	Ereignis 26	01:03:54	01:07:16	00:03:22
20	Ereignis 27	01:07:31	01:11:08	00:03:37
21	Ereignis 28	01:11:23	01:15:15	00:03:52
22	Ereignis 29	01:15:30	01:19:37	00:04:07
23
24	https://www.herber.de/excelformeln/pages/Formelloesungen_fuer_Spezialfilter_ohne_Duplikate.html

verwendete Formeln
Zelle	Formel	Bereich	N/A
E3	{=INDEX(Tabelle1[Ereignis];VERGLEICH(WAHR;A3:A42<>"";0))}	$E$3
E4:E22	{=WENNFEHLER(INDEX($A$3:$A$42;VERGLEICH(1;(ZÄHLENWENN($E$3:E3;A$3:A$42)=0)*(A$3:A$42<>"");0));"")}	$E$4
F3:F22	=INDEX($C:$C;VERGLEICH($E3;$A:$A;0))
G3:G22	=INDEX($C:$C;VERGLEICH($E3;$A:$A;0)+1)
H3:H22	=[@Ende]-[@Anfang]

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Zahlenformate
Zelle	Format	Inhalt
F3	'hh:mm:ss		2,31481481481481E-05
F4	'hh:mm:ss		2,53472222222222E-03
F5	'hh:mm:ss		5,37037037037037E-03
F6	'hh:mm:ss		8,07870370370371E-03
F7	'hh:mm:ss		1,11111111111111E-02
F8	'hh:mm:ss		1,43171296296296E-02
F9	'hh:mm:ss		1,76967592592593E-02
F10	'hh:mm:ss		1,98611111111111E-02
F11	'hh:mm:ss		2,21990740740741E-02
F12	'hh:mm:ss		2,47106481481481E-02
F13	'hh:mm:ss		2,73958333333333E-02
F14	'hh:mm:ss		3,02546296296296E-02
F15	'hh:mm:ss		0,033287037037037
F16	'hh:mm:ss		3,64930555555556E-02
F17	'hh:mm:ss		3,98726851851852E-02
F18	'hh:mm:ss		0,042037037037037
F19	'hh:mm:ss		0,044375
F20	'hh:mm:ss		4,68865740740741E-02
F21	'hh:mm:ss		4,95717592592593E-02
F22	'hh:mm:ss		5,24305555555556E-02
G3	'hh:mm:ss		2,7662037037037E-03
G4	'hh:mm:ss		5,04629629629629E-03
G5	'hh:mm:ss		7,9050925925926E-03
G6	'hh:mm:ss		0,0109375
G7	'hh:mm:ss		1,41435185185185E-02
G8	'hh:mm:ss		1,75231481481482E-02
G9	'hh:mm:ss		2,10763888888889E-02
G10	'hh:mm:ss		0,022025462962963
G11	'hh:mm:ss		0,024537037037037
G12	'hh:mm:ss		2,72222222222222E-02
G13	'hh:mm:ss		3,01967592592593E-02
G14	'hh:mm:ss		3,31134259259259E-02
G15	'hh:mm:ss		3,63194444444445E-02
G16	'hh:mm:ss		3,96990740740741E-02
G17	'hh:mm:ss		4,32523148148148E-02
G18	'hh:mm:ss		4,42013888888889E-02
G19	'hh:mm:ss		0,046712962962963
G20	'hh:mm:ss		4,93981481481481E-02
G21	'hh:mm:ss		5,22569444444445E-02
G22	'hh:mm:ss		5,52893518518519E-02
H3	'hh:mm:ss		2,74305555555556E-03
H4	'hh:mm:ss		2,51157407407407E-03
H5	'hh:mm:ss		2,53472222222223E-03
H6	'hh:mm:ss		2,8587962962963E-03
H7	'hh:mm:ss		3,03240740740741E-03
H8	'hh:mm:ss		3,20601851851852E-03
H9, H17	'hh:mm:ss		3,37962962962963E-03
H10	'hh:mm:ss		2,16435185185185E-03
H11	'hh:mm:ss		2,33796296296296E-03
H12, H20	'hh:mm:ss		2,51157407407407E-03
H13	'hh:mm:ss		2,80092592592592E-03
H14, H22	'hh:mm:ss		2,8587962962963E-03
H15	'hh:mm:ss		3,03240740740741E-03
H16	'hh:mm:ss		3,20601851851852E-03
H18	'hh:mm:ss		2,16435185185185E-03
H19	'hh:mm:ss		2,33796296296297E-03
H21	'hh:mm:ss		2,68518518518519E-03

Zellen mit Format Standard werden nicht dargestellt

Formatierte Tabellen (Tabelle1)
Bereich	Name	Vorlage	Vorlage orig.
$E$2:$H$22	Tabelle2	Tabellenformat - Mittel 7	TableStyleMedium7

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

 

Moin

Im Transformieren-Tab gibt es "Spalte pivotieren".

 

Hallo,

da du Excel365 hast, müsste auch folgende Lösung mit einer einzigen Formel funktionieren:

Code:

=LET(
    tb; Tabelle1;
    ee; INDEX(tb; ; 1);
    ae; INDEX(tb; ; 2);
    uz; INDEX(tb; ; 3);
    ereig; EINDEUTIG(ee);
    Anfg; NACHZEILE(
        ereig;
        LAMBDA(e; SUMMEWENNS(uz; ee; e; ae; "Anfang"))
    );
    Ende; NACHZEILE(
        ereig;
        LAMBDA(e; SUMMEWENNS(uz; ee; e; ae; "Ende"))
    );
    VSTAPELN(
        {"Ereignis". "Anfang". "Ende". "Dauer"};
        HSTAPELN(ereig; Anfg; Ende; Ende - Anfg);
        HSTAPELN("Gesamt:"; ""; ""; SUMME(Ende - Anfg))
    )
)

 

vielen Dank für die raschen Antworten! Ich würde power query bevorzugen, weil meine Datenquelle ca. 900kZeilen hat. Danke für den Tip mit pivotieren... leider bekomme ich die Uhrzeiten nicht in die Spalten...

 

Hallo,

falls es dir hilft, mein PowerQuery-Code sieht wie folgt aus:

Code:

let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Ereignis", type text}, {"Anfang/Ende", type text}, {"Uhrzeit", type number}}),
    #"Pivotierte Spalte" = Table.Pivot(#"Geänderter Typ", List.Distinct(#"Geänderter Typ"[#"Anfang/Ende"]), "Anfang/Ende", "Uhrzeit", List.Sum),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Pivotierte Spalte",{{"Anfang", type time}, {"Ende", type time}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ1", "Dauer", each [Ende]-[Anfang]),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte",{{"Dauer", type duration}})
in
    #"Geänderter Typ2"

 

Wähle "Nicht aggegieren".

 

danke Euch! Habs hinbekommen mit dem Code von Exl121150 und schau mir das noch an mit "nicht aggregieren!

 

Hallo Excel-Fan's,

schade, daß es bei der Excel-Funktion PIVOTMIT() keine Differenz-Funktion als Parameter gibt, denn dann wäre kein komplizierter Aufruf à la Exl121150 erforderlich.
Dann genügt: =PIVOTMIT(Tabelle1[Ereignis];Tabelle1[Anfang/Ende];Tabelle1[Uhrzeit];DIFFERENZ)
Eine Lambda-Funktion statt DIFFERENZ: LAMBDA(x;y;y-x) ist auch nicht möglich, da die Anfangs- und Endzeit nicht übergeben werden.

Gruß von Luschi
aus klein-Paris

 

Hallo @Luschi,

es funktioniert schon auch mit PIVOTMIT(...) - mit einem kleinen Trick: wenn man die Zeiten der Anfang-Spalte negativiert.
Allerdings muss man hinterher dafür sorgen, dass sie wieder positiviert werden, damit sie als Zeiten von Excel angezeigt werden.
=LET(tb; Tabelle1; p; PIVOTMIT(INDEX(tb; ; 1); INDEX(tb; ; 2); WENN(INDEX(tb; ; 2) = "Anfang"; -1; 1) * INDEX(tb; ; 3); SUMME; 0; 0); pdat; WEGLASSEN(p; 1); dat; HSTAPELN(INDEX(pdat; ; 1); -INDEX(pdat; ; 2); SPALTENWAHL(pdat; 3; 4)); VSTAPELN({"Ereignis"."Anfang"."Ende"."Dauer"}; dat))

 

Hallo @Luschi,

es geht noch einfacher, indem man die Dauer-Spalte mit der NACHZEILE-Funktion generiert:
=LET(tb; Tabelle1; p; PIVOTMIT(INDEX(tb; ; 1); INDEX(tb; ; 2); INDEX(tb; ; 3); SUMME; 0; 0; ; 0); pdat; WEGLASSEN(p; 1); d; NACHZEILE(pdat; LAMBDA(z; INDEX(z; 3) - INDEX(z; 2))); VSTAPELN({"Ereignis"."Anfang"."Ende"."Dauer"}; HSTAPELN(pdat; d)))

 

Weil es so schön sortiert ist .
=VSTAPELN({"Ereignis"."Anfang"."Ende".Dauer"};LET(y;ZEILENUMBRUCH(Tabelle1[Uhrzeit];2);HSTAPELN(EINDEUTIG(Tabelle1[Ereignis]);y;INDEX(y;;2)-INDEX(y;;1))))

 

Ein Anführungszeichen fehlte.
=VSTAPELN({"Ereignis"."Anfang"."Ende"."Dauer"};LET(y;ZEILENUMBRUCH(Tabelle1[Uhrzeit];2);HSTAPELN(EINDEUTIG(Tabelle1[Ereignis]);y;INDEX(y;;2)-INDEX(y;;1))))

 

Hallo Excel-365-Fan's,

danke für das Zeigen weiterer Lösungsmöglichkeiten, wobei mir die HSV-Lösung am besten gefällt, aber auch die Varianten von Exl121150 sind prima nachvollziehbar.

Gruß von Luschi
aus klein-Paris

PS: Trotzdem schade, daß 1e interne Lambda-Fkt. innerhalb von PivotMit() nicht machbar ist wegen fehlender Übergabeparameter.