PQ - VBA - Excel Funktion? | HERBERS Excel Forum

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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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

Beliebteste Forumthreads (12 Monate)

PQ - VBA - Excel Funktion?

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

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

20
Beiträge zum Forumthread

Beiträge zu diesem Forumthread