Microsoft Excel

Herbers Excel/VBA-Archiv

Addieren von Zeiten in anderen Zellen

Betrifft: Addieren von Zeiten in anderen Zellen von: Elias Spies
Geschrieben am: 06.10.2020 08:48:04

Servus an alle,


ich bin relativ neu bei der "höheren" Excelbedienung und habe auf der Arbeit die Aufgabe bekommen, CSV-Dateinen zu analysieren.


Das ganze besteht aus Produktions- und Ausfallzeiten und jede Menge anderem Zeugs. Nun zu meiner Frage:


In der Tabelle treten die Wörter Stillstand und Produktion mehrfach auf, jeweils mit einer Zugehörigen Zeit, welche jedoch logischerweise in einer anderen Zelle in der gleichen Zeile steht.


Gibt es eine Möglichkeit, dass wenn das Wort "Stillstand" auftritt, die Zelle mit der zugehörigen Zeit in der gleichen Zeile von der Zeit mit dem Wort "Produktion" welches als nächstes auftritt zu subtrahieren ?


Das Problem dabei ist, dass das ganze öfters auftitt und sowohl "Stillstand" und "Produktion" mehrfach auftreten.


Hier ein Bild zur genaueren Veranschaulichung:





Mein Ziel ist es, am Ende eine einzelne Produktionszeit und eine Stillstandszeit von der ganzen Tabelle zu haben, welche ich Visualisieren kann.


Wenn jemand Ideen oder Vorschläge hat immer her damit


Grüße

Betrifft: AW: (D)eine Beispieldatei wäre hilfreich(er) owT
von: neopa C
Geschrieben am: 06.10.2020 09:02:39

Gruß Werner
.. , - ...

Betrifft: AW: (D)eine Beispieldatei wäre hilfreich(er) owT
von: Elias Spies
Geschrieben am: 06.10.2020 11:04:14

Kannst du damit was anfangen ?

https://www.herber.de/bbs/user/140689.xlsm

Betrifft: AW: damit kann man die Diff.Zeit berechnen ...
von: neopa C
Geschrieben am: 06.10.2020 11:38:09

Hallo Elias,

... ich hab in der Datei die maßgeblichen Daten in die gleichen Spalten verschoben wie in Deinem Bild.

Dann folgende Formel mit Zellformat: hh:mm:ss
in K2: =WENN(I2="Produktion";A2+B2-VERWEIS(9;1/(I$1:I1="Stillstand");A$1:A1+B$1:B1);"")
und nach unten kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: damit kann man die Diff.Zeit berechnen ...
von: Elias Spies
Geschrieben am: 06.10.2020 12:29:37

Vielen lieben Dank :)

Betrifft: AW: gerne owT
von: neopa C
Geschrieben am: 06.10.2020 12:33:37

Gruß Werner
.. , - ...

Betrifft: AW: gerne owT
von: Elias Spies
Geschrieben am: 08.10.2020 16:28:53

Ich hätte nochmal eine Frage bezüglich der Formel.
Es hat soweit alles wundervoll funktioniert, ich habe nur noch ein kleines Problem mit dem ganzen, dass ich nicht gelöst bekomme.

Die Formel lässt sich nur anwenden, wenn die Wörter Produktion und Stillstand immer abwechselnd vorkommen.
Ich habe aber teilweise das Problem, dass sich diese wiederholen und damit die Berechnung zweimal abwickeln auf das jeweils obenstehende Wort:



https://www.herber.de/bbs/user/140736.xlsx

Vielen Dank im Voraus :)

Betrifft: AW: dazu nachgefragt ...
von: neopa C
Geschrieben am: 09.10.2020 10:10:52

Hallo Elias,

... kann es auch vorkommen dass nicht nur eine Wiederholung von Stillstand auftritt sondern auch mehrmals? Wie soll im konkreten Beispiel das Ergebnis ermittelt werden und wo soll es stehen?

Gruß Werner
.. , - ...

Betrifft: AW: dazu nachgefragt ...
von: Elias Spies
Geschrieben am: 09.10.2020 10:51:36

Hallo Werner,

tut mir leid für meine Ungelduld, ich wusste nicht ob es gesehen wird, wenn ich nur auf meinen Beitrag antworte.

Eine Wiederholung direkt hinter einander tritt auf und das ganz auch mehrmals.
Ich wollte das Ergebnis so berechnet haben, dass ich am Ende sozusagen bei Wiederholung nur eine Stillstandzeit ausgegeben bekomme und zwar die größste von allen.
Also in dem hier dargestellten Beispiel soll nur die untere von beiden Zeiten abgebildet werden.
Am Ende werden sowohl die Stillstandzeit als auch die Produktionszeit zusammengerechnet und abgebildet. Wo das ganze am Ende steht ist relativ egal.

Betrifft: AW: dafür dann Formelerweiterung ...
von: neopa C
Geschrieben am: 09.10.2020 13:14:40

Hallo Elias,

... in K2:
=WENN((H2="Stillstand")*WENNFEHLER((VERGLEICH(H2;H3:H81;0)>VERGLEICH("Produktion";H3:H81;0))+0; 1); A2+B2-VERWEIS(9;1/(H$1:H1="Produktion");A$1:A1+B$1:B1);"")

und nach unten kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: dafür dann Formelerweiterung ...
von: Elias
Geschrieben am: 09.10.2020 13:37:08

Wow okay vielen Dank :)



Würde es dir was ausmachen eventuell eine kleine Erklärung zu der Formel zu formuleiren ?

Ich würde aus der Sache gerne etwas mitnehmen :D



Gruß Elias

Betrifft: AW: zu Erklärung ...
von: neopa C
Geschrieben am: 09.10.2020 14:16:24

Hallo Elias,

... das die Berechnung nur in der Zeile vorgenommen wird, wo in Spalte H "Stillstand" steht und in der Spalte in den nachfolgenden Zeilen kein "Stillstand" vor "Produktion" in Spalte H steht, dafür ist der Formelteil:

((H2="Stillstand")*WENNFEHLER((VERGLEICH(H2;H3:H81;0)>VERGLEICH("Produktion";H3:H81;0))+0;1)

zuständig. Das * vor WENNFEHLER() verknüpft die beiden Bedingungsvergleiche in Form einen UND()-Beziehung. Mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=91

Zu Funktionsweise von VERGLEICH() siehe die MSO-Hilfe zur Funktion.

Zum Berechnungsformelteil: A2+B2-VERWEIS(9;1/(H$1:H1="Produktion");A$1:A1+B$1:B1):

A2+B2 und ermittelt eine Dezimalzahl aus Datums(zahl) und Uhrzeit(zahl). Mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=128

Mit A$1:A1+B$1:B1 wird das gleiche getan getan, nur eben als Matrix (ist ein eigenes Kapitel) und als auszuwertende Matrix der Funktion VERWEIS() zugewiesen, damit für die Bedingung: wo steht in Spalte H vor (oberhalb) der aktuellen Zeile "Produktion".
Mit (H$1:H1="Produktion") wird eine Matrix aus FALSCH und WAHR für die zurückliegenden Datenzeilen ermittelt und mit 1/(H$1:H1="Produktion") ausgewertet. Durch die Division ergibt sich eine Matrix aus 1(en) und Fehlerwerten (weil 1/FALSCH =1/0 ergibt und dies dann natürlich einen Fehlerwert).
Die VERWEIS()-Funktion ignoriert einfach derartige Fehler in der Teilmatrix und sucht von "unten nach oben" die erste 1, merkt sich dessen Zeilenposition in der Matrix und gibt für diese den entsprechenden Wert aus der aktuellen Matrix A$1:A1+B$1:B1 aus. In Zeile 11 lautet dieser Formelteil ja A$1:A10+B$1:B10 und gibt aus diesem der Ergebnis des VERWEIS()-Formelteils wieder. Dies wird dann dort von A11+B11 subtrahiert und als Formelergebnis ausgewiesen.

Da es möglich ist, das in Spalte H nach "Stillstand" kein "Stillstand" oder auch keine "Produktion" mehr steht, ergibt sich in entsprechender Zeile für den jeweiligen VERGLEICH()s-Formelteil ein Fehlermeldung. Dieser wird für dem Fall mit WENNFEHLER() abgefangen und dort eine 1 ermittelt, damit überhaupt ein zutreffendes Ergebnis ermittelt werden kann.

Gruß Werner
.. , - ...