Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1772to1776
1772to1776
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

PQ - VBA - Excel Funktion?

Forumthread: PQ - VBA - Excel Funktion?

PQ - VBA - Excel Funktion?
05.08.2020 20:06:03
Tom
Hallo zusammen,
ich möchte für die Daten im Anhang in div. Status die Nettoarbeitstage/Durchlaufzeiten berechnen lassen, habe aber damit Schwierigkeiten, da die Zeitstempel leider untereinander ausgegeben werden. Eigentlich würde ich das ganze gerne über Power Query darstellen wollen, weil die Datenbasis aus einer Excel-Tabelle und mit über 430 000 Datensätzen (ansteigend) kommt. Die Vorgänge werden wie im Anhang zu sehen mit einem Status (10 bis 180) gekennzeichnet. Der Reihenfolge der Statuswechsel ist aber nicht immer vorlaufend und fällt teilweile im Status zurück. Im PQ habe ich über die Bedingte Spalte die Zeitstempel der einzelnen Status herausgezogen (Spalten D –N), weiter habe ich aber noch keine Lösung. Die eigentliche Datenstruktur ist in der Spalte A-C zu sehen.
Hat jemand eine gute Idee dazu?
Zur Info!
In der Angebotsphase bewegt sich der Status immer zwischen 30 und 80, kann aber auch auf 10 zurückfallen. Zwischen 40 und 50 kann es auch auf 30 oder 10 zurückgehen.
Status 10 – 30 – 40 – 50 - 60 – 61 – 62 – 65 – 70 – 80 - 90 – 97 – 100
In der Auftragsphase bewegt sich der Status ab 110 bis 180 und springt dann wieder auf 110 zurück. Im Status 130 bis 160 kann er auf 110 zurückspringen.
Sobald der Status 80 überschritten wurde ist ein Rücksprung auf einen niedrigeren Status nicht mehr möglich.
Status 110 – 130 – 140 – 150 – 160 – 170 - 180
https://www.herber.de/bbs/user/139486.xlsx
Viele Grüß Tom
Anzeige

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 00:42:32
Martin
Hallo Tom,
Ich hatte da eine kleine Idee zu einem Makro bin mir aber nicht sicher was genau berechnet werden soll.
Kann man verbal sagen "gehe die Zeitenspalte von unten nach oben durch und vergleiche den Status und dessen eventuelle Änderungen... kategorisieren das und dann rechne die verweilzeiten in den Kategorien aus?
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 07:11:06
Tom
Guten Morgen Martin,
danke für Deine Antwort.
Ich möchte von einem Angebot immer die Dauer von den Unterschiedlichen Schleifen berechnen lasse. Im ersten Step ist mir aber die Schleife von 40 bis 60 die wichtigste. Hier sieht man nämlich wie lange das Angebot in der Bearbeitung/Bewertung war.
Wie meinst Du das mit der Kategorisierung?
Viele Grüße Tom
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 08:35:44
Martin
Hallo Tom,
ja so dachte ich in etwa. Also wäre es chic, wenn am Ende da stünde, dass das Angebot im Status 60 für 3 Tage 7Stunden und 24 Minuten war, für den Status 90 beispielsweise 1 Tag 2 Stunden usw...
Mit Kategorisierung meinte ich einfach tabellarisch aufzeigen
Status | Dauer
30 | 10 min
60 | 45 min
usw..
Stör dich erst mal nicht an den Ergebnissen, die sind nur aus der Luft gegriffen um das wiederzugeben was ich verstanden habe...
Martin
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 11:30:26
Martin
Hallo Tom,
ich habe da mal was zurechtgestrickt und hoffe das ist etwa das was du wolltest. Natürlich muss es jetzt noch verallgemeinert werden. Ich habe nur auf die Schnelle das Angebot 80123 gerechnet. Und käme da zu folgenden Ergebnisse in Tagen für die Kategorien: oben Status 30 unten Status 100
0
14,2684375000026
2,10115740740002
22,7727546296373
0,318692129621923
0
0
19,9139583333381
3,67417824074073
164,83966435185
0
0
0
Wenn das so richtig wäre (ich habe end und Anfangsdatum geprüft und es stimmt), dann könnten wirfix die Dezimale in .
Fragen zur Fertigstellung:
- Laufen die Aufträge immer chronologisch ab, oder kann ein Auftrag auch plötzlich wieder auftauchen?
- Wie soll die Ausgabe dann aussehen?
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 12:53:56
Tom
Hallo Martin,
es sollen keinen Summen der Dauer z.B. aller Wechsel einer Angebotsnummer zwischen 40 bis 60 gebildet werden. Ich benötige immer nur die Dauer der einzelnen Schleifen.
Zu Deinen Fragen:
Wenn ich aus der Anwendung die Daten exportiere, wird chronologisch ausgegeben. Jede Bewegung (Statuswechsel) wird unabhängig der Angebotsnummer absteigend aufgezeichnet. Die Sortierung gemäß meiner Beispieldatei, wurde von mir nachträglich nach dem Extract durchgeführt.
Die Ausgabe der Dauer sollte in etwa ähnlich meiner Beispieldatei sein.
Gruß Tom
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 13:34:21
Martin
Hallo Tom,
jetzt komm ich grad nicht mit. Ich habe nur das Angebot 80123 ausgewertet und die Tabelle zeigt in Summe an wie lange Angebot im Status 40 verweilt hat. Hier 14,26 Tage. Das ist also nicht das andere Angebot mit betreffend.
Was meinst du denn mit den einzelnen Schleifen? Meinst du etwa dort wo es so schön bunt ist, wo also ein Statuswechsel vorliegt, soll die Dauer der zurückliegenden Schleife angezeigt werden?
Ich hab mal ein Machwerk hier angefügt, schau mal im Quelltext ob da was brauchbares dabei ist.
https://www.herber.de/bbs/user/139529.xlsm
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 14:33:55
Tom
Hi Martin,
richtig, das Bunte sind jeweils die zusammengehörigen Zeitstempel (Schleifen). Jeder Status kann mehrfach erfolgen, wenn die Bewertung abgeschlossen wurde oder auch wenn der Bearbeiter zurück an den Ersteller des Angebots sendet.
hier mal beispielhaft erklärt...diese reguläre Schleife wird im Schnitt je Angebot ca. 15x mit unterschiedlichen Zeitstempeln durchlaufen.
Status 40 Angebot lag im Vorrat zur Bewertung
Status 50 Angebot wurde bewertet
Status 60 Angebot wurde bewertet
oder
Status 40 Angebot lag im Vorrat zur Bewertung
Status 50 Angebot wurde bewertet, wurde aber zurück an der Ersteller geschickt
Status 30 Angebot wurde zurück an Ersteller geschickt
oder
Status 40 Angebot lag im Vorrat zur Bewertung
Status 30 Angebot wurde zurück an der Ersteller geschickt
Gruß Tom
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 14:39:30
Tom
...in der Spalte O kannst Du auch die Formel für meine Berechnung erkennen.
Gruß Tom
AW: PQ - VBA - Excel Funktion?
07.08.2020 14:57:09
Martin
Hallo Tom,
Ahh, jetzt ja.. ich denke ich habe es!
- Laufe einmal pro Angebot durch und schaue die einzelnen Schleifen an
- gib am Ende die Zeitspanne in Nettoarbeitstagen aus die das Angebot in 40-60 verweilt hat
- gib am Ende die Zeitspanne aus (NAT)die der Auftrag in 140-160 war....
hab ich es jetzt?
ich tippe mal darauf dass Du feststellen willst wo es klemmt und wo Schwerpunkte liegen für Verbesserungen und oder die Errechnung von Bearbeitungskennzahlen im Sinne von Key Performance Indicators (KPI)
Grüße Martin
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 15:36:12
Tom
Hallo Martin,
ja und ja, genau....das wollte ich Dir die ganze Zeit mitteilen ;-) Ist für einen Außenstehenden nicht einfach zu verstehen, wenn nicht alle Informationen vorliegen.
Gruß Tom
AW: PQ - VBA - Excel Funktion?
07.08.2020 17:49:22
Martin
Hallo Tom,
Sehr gut, ich mach mal was fertig und schicke es dir, dauert aber ein wenig, weil eben WE ist und ich was vor habe. Bis wann brauchst du es?
Grüße und schönes WE
Martin
Anzeige
AW: PQ - VBA - Excel Funktion?
07.08.2020 19:58:31
Tom
Hi Martin,
mach Dir keinen Stress. Wie es Dir ausgeht.
Danke schon mal und ein schönes Wochenende ;)
Viele Grüße
Tom
AW: PQ - VBA - Excel Funktion?
10.08.2020 23:18:24
Martin
Hallo Tom,
ich habe mal was zurechtgezimmert und denke, dass ich nun jenes Makro habe was du brauchst. Das einzige was vielleicht noch geändert werden müsste wäre die Lauflänge. Wenn ich richtig liege, dann hast du ja sehr viele Datensätzen zu kämpfen. Das Makro läuft automatisch bis Zeile 10000.
Schau einfach mal ob es so passt, wenn Fragen, dann einfach melden...
https://www.herber.de/bbs/user/139569.xlsm
Martin
Anzeige
AW: PQ - VBA - Excel Funktion?
11.08.2020 12:27:12
Tom
Hallo Martin,
vielen Dank noch einmal für Deine Mühen.
Die Summen benötige ich immer nur für eine Schleife...Status 40 bis 60. Für die nächste Schleife 40 bis 60 soll eigens zusammenaddiert werden.
Was errechnet sich wie unter dem Status 40....Zelle G81 als Beispiel.
Ja, die Lauflänge umfasst ca. 430 000 Zeilen. Das muss ich noch ändern. Bin mal gespannt, wie schnell abgearbeitet wird, bei der Menge von Daten.
Gruß Tom
Anzeige
AW: PQ - VBA - Excel Funktion?
11.08.2020 14:14:48
Martin
Mhmmm. Ich weiß nicht ob wir aneinander vorbeireden oder nicht.
Ich habe Excel gesagt:
-Gehe von oben nach unten berechne jede zeitliche Statusdifferenz (und schreibe das auch in die Zeile)
-tue das alles in ein eindimensionales Array und summiere immer die zugehörigen Status auf
-Wenn die Auftragsnummer sich ändert (neuer Auftrag) schreibe die Arraysumme 40-60 und 140-160 in die letzte Zeile des Auftrages in die Spalten AA und Z (das sind Netto Arbeitstage)
-Danach leere das Array wieder und mache das selbe für alle anderen Aufträge.
Große Frage nun, ist die Aufgabenstellung nun gelöst?
Grüße
Martin
Anzeige
AW: PQ - VBA - Excel Funktion?
11.08.2020 16:01:15
Tom
Hi Martin,
so gut wie...die Summe soll aber immer nur auf einen Vorgang (40-60) gerechnet werden und nicht auf alle Vorgänge (40-60).
Das Angebot 80123 als Beispiel: Unten folgend ein Vorgang von Status 40-60. Aus diesem einen Vorgang sollten die Nettoarbeitstage berechnet werden. Das Ergebnis wäre dann in dem Vorgang in der Zelle (Z79) dann 10 Netto-AT. Für jeden weiteren Vorgang soll dann ein sep. Ergebnis in der Zeile des Status 60 in der Spalte Z stehen. Weißt Du was ich meine?
80123 60 15.02.2019 14:43
80123 50 05.02.2019 07:59
80123 40 04.02.2019 15:38
Ich lasse auch nur die Zeiten 40 bis 60 summieren. Wenn der Status auf 65 wechselt ist das außerhalb des Vorgangs. In dem Code habe ich es wie folgt geändert: 

'Aufsummieren der NAT 40 bis 60
For i = 3 To 4
NAT40_60 = NAT40_60 + Status_wert(i)
Next i
For i = 16 To 17
NAT140_160 = NAT140_160 + Status_wert(i)

Gruß Tom
Anzeige
AW: PQ - VBA - Excel Funktion?
11.08.2020 17:09:42
Martin
Hallo Tom,
ich glaube das ist das was ich nicht verstanden habe. Der Vorgang 80123 hat mehrere Sprünge (Warum auch immer) von 40 auf 60, und du hättest gerne nach jedem dieser Sprünge die Verweilzeit von zwischen 40 und 60 ausgegeben.
80123 65 20.03.2019 15:42
-
80123 60 20.03.2019 14:57 13,14513889
80123 50 08.03.2019 07:22
80123 40 07.03.2019 11:28
-
80123 65 15.02.2019 15:13
-
80123 60 15.02.2019 15:09 0,010196759
80123 50 15.02.2019 15:00
80123 40 15.02.2019 14:58
-
80123 65 15.02.2019 14:47
-
80123 60 15.02.2019 14:43 10,96462963
80123 50 05.02.2019 07:59
80123 40 04.02.2019 15:38
-
80123 30 21.01.2019 13:29
Ich habe es jetzt mal fix zu Fuß ausgerechnet. Entspricht das deinen Vorstellungen? Und dann noch die Frage, wenn ja, wo soll das Ergebnis hin? in Spalte Z? Der Weg dahin ist jetzt klein, geht aber meines Erachtens nur über Makro; zur Not auch per Excel Formel mit viel "Wenn" und "Aber".
Grüße
Martin
Anzeige
AW: PQ - VBA - Excel Funktion?
11.08.2020 17:41:47
Tom
Hi Martin,
ja, so meinte ich das. Ich denke jetzt haben wir es. Das Ergebnis sollte dann in die Spalte Z. Diese vielen Vorgänge 40 bis 60 kommen daher, weil das Angebot immer wieder geändert wird und zur Bewertung eingereicht wurde. Per Wenn Formel bei so vielen Daten, rechnet Excel schon eine kleine Ewigkeit.
Viele Grüße Tom
Anzeige
AW: PQ - VBA - Excel Funktion?
13.08.2020 20:33:26
Martin
Hallo Tom,
ich habe nochmal geforscht, aber wenig Zeit. Ich denke das Makro macht nun genau das was es soll. Weil ich neugierig war, habe die Datensätze mal mit einer Schleife vervielfältigt auf 430.000. Dann ist die Datei etwa 7,5MB groß. Das Makro mit der Berechnung läuft in 1,5 Minuten durch.
13.08.2020 20:22:05
13.08.2020 20:23:31
Die Zwischenergebnisse sind türkis, die Endergebnisse Ocker, ich habe einfach irgendeine Farbe genommen; kannst du Ändern.
Ich habe den Quelltext mal hier hingehangen... einfach Ändern und los geht es...
Beste Grüße
Martin 
Public Sub Statusdauer()
Dim Status_wert(20) As Double
Dim Status_name(20) As Integer
Dim Zelle As Range
Dim Status As Integer
Dim NAT40_60, NAT140_160 As Double
Debug.Print Now
'Status Angebot 10 – 30 – 40 – 50 - 60 – 61 – 62 – 65 – 70 – 80 - 90 – 97 – 100
'Status Angebot 110 – 130 – 140 – 150 – 160 – 170 - 180
Statusstring = "10;30;40;50;60;61;62;65;70;80;90;97;100;110;130;140;150;160;170;180"
A = Split(Statusstring, ";")
'Laden der Statusbezeichnung
For i = 1 To 20
Status_name(i) = A(i - 1)
Next i
For Each Zelle In [A2:A424983]
If IsEmpty(Zelle) = True Then Exit For
zeile = Zelle.Row
'Test ob neue Angebotsnummer vorliegend
If Tabelle1.Cells(zeile, 1).Value  Tabelle1.Cells(zeile + 1, 1).Value Then
'Aufsummieren der NAT 40 bis 60
For i = 3 To 5
NAT40_60 = NAT40_60 + Status_wert(i)
Next i
For i = 16 To 18
NAT140_160 = NAT140_160 + Status_wert(i)
Next i
'Ausgabe der Errechneten werte
Tabelle1.Cells(zeile, 26).Interior.ColorIndex = 45
Tabelle1.Cells(zeile, 27).Interior.ColorIndex = 45
Tabelle1.Cells(zeile, 26).Value = NAT40_60
Tabelle1.Cells(zeile, 27).Value = NAT140_160
'leeren der Errechneten Werte
For i = 1 To 20
Status_wert(i) = Empty
Next i
NAT40_60 = 0
NAT140_160 = 0
End If
'Prüfung ob eine 40-60 Schleife vorliegt
If Tabelle1.Cells(zeile + 1, 2).Value = 60 And Tabelle1.Cells(zeile + 2, 2).Value = 50 And  _
Tabelle1.Cells(zeile + 3, 2).Value = 40 Then
Dauer_S = Tabelle1.Cells(zeile + 1, 3).Value - Tabelle1.Cells(zeile + 3, 3).Value
Tabelle1.Cells(zeile, 26).Value = Dauer_S
Tabelle1.Cells(zeile, 26).Interior.ColorIndex = 42
End If
'Prüfung ob eine 140-160 Schleife vorliegt
If Tabelle1.Cells(zeile + 1, 2).Value = 160 And Tabelle1.Cells(zeile + 2, 2).Value = 150 And  _
Tabelle1.Cells(zeile + 3, 2).Value = 140 Then
Dauer_S = Tabelle1.Cells(zeile + 1, 3).Value - Tabelle1.Cells(zeile + 3, 3).Value
Tabelle1.Cells(zeile, 27).Value = Dauer_S
Tabelle1.Cells(zeile, 27).Interior.ColorIndex = 42
End If
Angebot = Tabelle1.Cells(zeile + 1, 1).Value
Status = Tabelle1.Cells(zeile + 1, 2).Value
Enddatum = CDbl(Tabelle1.Cells(zeile, 3).Value)
Startdatum = CDbl(Tabelle1.Cells(zeile + 1, 3).Value)
dauer = Enddatum - Startdatum
For i = 1 To 20
If Status_name(i) = Status Then Modifikator = i: Status_wert(i) = Status_wert(i) + dauer:  _
Exit For
Next i
Tabelle1.Cells(zeile + 1, 4 + Modifikator).Value = dauer
Next Zelle
Debug.Print Now
End Sub

Anzeige
AW: PQ - VBA - Excel Funktion?
18.08.2020 07:51:07
Tom
Guten Morgen Martin,
dank Dir für den Code. Ich bin gerade etwas im Arbeitsstress und konnte es bisher noch nicht testen. Ich melde mich die Tage erneut und gebe Dir bescheid.
Viele Grüße
Tom
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige