Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1464to1468
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA Code zur Datenauswertung

VBA Code zur Datenauswertung
13.12.2015 11:08:00
Mike

Hallo liebes Forum!
Ich sitze gerade bei einer Datenauswertung in Excel und würde eure Hilfe benötigen!
Die angefügte Exceldatei (https://www.herber.de/bbs/user/102225.xlsx) ist nur ein kleiner Auszug aus den auszuwertenden Daten, gesamt müssen ca. 800.000 Datenzeilen ausgewertet werden, weshalb ich mich für VBA entschieden habe!
Im Prinzip geht es darum, die Arbeitseinsätze anhand definierter Kriterien für weitere Operationen zu kennzeichnen. Das Problem bei der ganzen Sache ist, dass ich die Operationen für jede Anlage separat ausführen muss.
Ich wollte nun fragen, ob mir jemand eine Möglichkeit nennen könnte, die angeführten Operationen immer für eine Anlagennummer nach der Anderen auszuführen? Es sollte also gewissermaßen ein Abschnitt erstellt werden, welcher dieselbe Anlagennummer aufweist und innerhalb dieses Abschnittes sollten die bereits erstellten Bewertungen durchgeführt werden!
Ich hoffe meine Erklärung ist halbwegs verständlich!
Vielen Dank für eure Unterstützung!
Lg

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: der Link
13.12.2015 11:45:12
Mike
Hallo Gerd!
Alles klar, danke für den Hinweis!
Ich würde einen VBA-Code benötigen, der die Spalte A (Anlagennummer) durchsucht, und einen Abschnitt erstellt, solange sich die Anlagennummer nicht ändert. Ändert sich die Anlagennummer, sollte ein neuer Abschnitt erstellt werden! Dies sollte für die gesamte Spalte A durchgeführt werden.
Innerhalb der einzelnen Abschnitte möchte ich in weiterer Folge gewisse Operationen, wie Zeitdifferenz zwischen zwei Standardwartungen und dgl., berechnen können!
Lg Mike

Anzeige
AW: der Link
13.12.2015 11:48:57
Mike
... die Operationen habe ich bereits in VBA geschrieben, es geht mir in erster Linie darum, diese Operationen wirklich für jede Anlagennumer und somit für jeden "Anlagenabschnitt" getrennt auszuführen.....

Abschnitt?
14.12.2015 16:20:04
Michael
Hallo Mike,
was verstehst Du unter einem "Abschnitt"?
Du kannst ja mal damit Herumprobieren:
Option Explicit
Sub abschnitte()
Dim zeile&, bis&, oberster&, lfdNr&
Dim Anlage As Variant
Dim Abschnitt As Range
Const von = 2
bis = Range("A" & Rows.Count).End(xlUp).Row
zeile = von
oberster = zeile
lfdNr = 0
Anlage = Range("A" & zeile).Value
While zeile  Range("A" & zeile).Value Then
Set Abschnitt = Range("A" & oberster & ":E" & zeile - 1)
lfdNr = lfdNr + 1
MsgBox "Hier Aktionen mit dem " & lfdNr & ". Abschnitt: " _
& Abschnitt.Address
oberster = zeile
Anlage = Range("A" & zeile)
End If
Wend
End Sub
Schöne Grüße,
Michael

Anzeige
AW: Abschnitt?
14.12.2015 20:12:07
Mike
Hallo Michael, Guten Abend!
Vorab: Vielen, vielen Dank! Tolle Arbeit! Genau so etwas habe ich gesucht! :-)
Da ich allerdings in VBA anscheinend doch nicht so fit bin wie gedacht, hätte ich noch zwei weitere Fragen, die du mir vielleicht beantworten könntest:
- Ich möchte nun innerhalb jedes einzelnen Abschnittes die Spalte "valid" anhand vordefinierter Regeln befüllen. Habe mir dazu die Select Case Umgebung angesehen. Im Prinzip gibt es gewisse Regeln, welche entscheiden, ob ein Arbeitseinsatz als gültig gekennzeichnet wird oder nicht. In meinen Eigenen Worten würden einige dieser Regeln ungefähr so aussehen:
1) Wenn der erste Arbeitseinsatz eines neuen Abschnittes eine Standardwartung ist, soll die valid-Zelle mit 1 befüllt werden. Handelt es sich um jegliche andere Tätigkeit am Beginn eines neuen Abschnittes, sollte die valid-Zelle mit 0 befüllt werden.
2) Wenn Zelle B2 = Standardwartung UND Zelle B3 = Standardwartung Und Zeitdifferenz zwischen den beiden Arbeiten < 10 Wochen Dann E3 = 1 ~f~
3) ~f~ Wenn Zelle B3 = Standardwartung UND Zelle B2 = Teiletausch Dann E3 = 0
Diese Regeln stellen nur einen Auszug dar, ich hoffe du verstehst ungefähr was ich meine.
Kurz noch zur zweiten Frage: Wie ist es am einfachsten möglich, die Zeitdifferenz von Anfang Abschnitt bis Ende Abschnitt zu berechnen und diesen Wert jeweils in der Spalte F in der Zeile des letzten Elementes eines Abschnittes auszugeben?
Wäre für deine nochmalige Hilfe überaus dankbar!
Wünsche noch einen angenehmen Abend!
Lg Mike

Anzeige
Abschnitt!
15.12.2015 18:17:42
Michael
Hallo Mike,
ich hab Dir ein zweites, etwas abgeändertes Makro erzeugt, das zumindest mal die Differenzen in KW in die unterste Zeile des Abschnitts schreibt, nur um zu zeigen, wie das geht.
Die weiteren Regeln habe ich im Tabellenblatt zunächst mit Formeln realisiert. Ich empfehle, zuerst alle Regeln im Tabellenblatt zu erfassen, um zu sehen, ob die Logik paßt und sich nicht etwa Werte gegenseitig ausschließen.
Wenn das dann definitiv steht, kann man das entweder (außerhalb eines druckbaren Bereichs z.B.) stehenlassen, auf ein "very hidden" Tabellenblatt legen oder denn doch in VBA umsetzen.
Die Datei: https://www.herber.de/bbs/user/102277.xlsm
Paar weitere Infos direkt in der Tabelle...
Happy Exceling,
Michael

Anzeige
AW: Abschnitt!
15.12.2015 21:34:58
Mike
Hallo Michael!
Spitze, genau so habe ich mir das mit den Zeitdifferenzen vorgestellt. Besten Dank schonmal! Dass die Sache mit den Kalenderwochen noch etwas problematisch ist, ist mir bewusst! Mal sehen, ob ich genauere Datumsangaben bekomme, um eleganter damit rechnen zu können.
Du hast natürlich vollkommen Recht, werde vorher all meine Regeln mittels Formel "probieren" und erst dann in VBA umsetzen! Da ich allerdings erst am Wochenende dazu Zeit habe, wollte ich fragen, ob du mir vielleicht eine Regel beispielhaft implementieren könntest, damit ich eine Ahnung davon bekomme, wie die Umsetzung auszusehen hat?
Eine solche beispielhafte Regel wäre: [Als erste Arbeit bezeichne ich jetzt mal die Arbeit in der aktuellen Zelle und als nullte Arbeit jene Arbeit davor, da meine Regeln darauf aufbauen, dass ich checke, was vor der gerade zu beurteilenden Arbeit geschehen ist]
Wenn die "nullte Arbeit" eine Inspektion ist und die "erste Arbeit" eine Standardwartung und die Zeitdifferenz zwischen den beiden Arbeiten größer als 1 Woche ist, setze mir das valid-Feld der "ersten Arbeit" auf 1.
Vielleicht wäre die Umsetzung über eine Select Case Umgebung sinnvoll, da ich ungefähr 15 Regeln zum einbinden habe.....
Weiters hätte ich noch eine kleine Frage: Funktioniert dieses Makro auch für alle Tabellenblätter einer Datei? Ich habe nämlich pro Datei ca. 40 Tabellenblätter, die alle gleich aufgebaut und alle Spalten gleich beschriftet, nur eben mit unterschiedlichen Daten gefüllt, sind. Wäre ziemlich vorteilhaft, wenn das Makro mit einem Start die gesamte Auswertung in den gesamten Tabellenblättern ausführen könnte....
Vielen vielen Dank nochmals für deine tolle Unterstützung!
Schönen Abend noch, lg Mike

Anzeige
AW: Abschnitt!
15.12.2015 21:45:16
Mike
Ach ja, was ich noch vergessen habe:
Ich würde die Gültigkeit der einzelnen Arbeiten lieber direkt in VBA umsetzen und auch direkt in die jeweilige valid-Spalte innerhalb des Arbeitsplatzes ausgeben, da ich zum Schluss die Summe aller gültigen Arbeiten bilden möchte! Weiters wäre es sinnvoll, die "Gültigkeitswerte" direkt in die Datei zu schreiben, da somit in weiterer Folge auch immer kontrolliert werden kann, ob alles richtig abgearbeitet wurde....
Danke nochmals für die großartige Unterstützung!
Lg Mike

AW: Abschnitt!
16.12.2015 13:43:15
Michael
Hi Mike,
erst Mal vielen Dank für Deine immer freundlichen Worte... Es kostet mich durchaus Zeit, aber es macht auch immer wieder Spaß, an unterschiedlichsten Problemstellungen zu knabbern.
Ehrlich gesagt, leuchtet mir Dein zweiter, kürzerer Post nicht ganz ein. Ich habe jetzt Mal die genannte Regel eingebaut und im Code ein bißchen was erläutert:
Sub abschnitte121612()
Dim zeile&, bis&, oberster&, lfdNr&, zeileImBereich&, unterster&, i&
Dim Anlage As Variant
Dim regeln(1 To 15) As Byte
Dim kw1&, kw2&
Const von = 2
bis = Range("A" & Rows.Count).End(xlUp).Row
zeile = von
oberster = zeile
lfdNr = 0
Anlage = Range("A" & zeile).Value
While zeile  Range("A" & zeile).Value Then
unterster = zeile - 1
lfdNr = lfdNr + 1
For zeileImBereich = oberster + 1 To unterster ' ab der zweiten Zeile im Bereich
' erst mal die KW in Variablen schreiben, die brauchst Du wohl öfters
kw1 = Range("J" & zeileImBereich)
kw2 = Range("J" & zeileImBereich - 1)
' und alles auf 0 setzen
For i = 1 To 15: regeln(i) = 0: Next
' 1. Regel:
' Wenn die "nullte Arbeit" eine Inspektion ist und die "erste Arbeit"
' eine Standardwartung und die Zeitdifferenz zwischen den beiden Arbeiten
' größer als 1 Woche ist, setze mir das valid-Feld der "ersten Arbeit" auf 1.
' Achtung: das bedeutet nicht automatisch, von der Formulierung her, daß
' valid auf 0 gesetzt wird - es ist aber mit 0 vorbelegt.
' Das kann bei kollidierenden Regeln dazu führen, daß der valid-Wert einer
' Zeile durch die Abarbeitung aller Regeln einmal auf 0, einmal auf 1 gesetzt
' wird - genau das wird durch das Array vermieden, das je einen Wert pro Regel
' enthält.
' In der Konsequenz können dann Werte zwischen 0 und 15 entstehen, je nach dem,
' wie viele Regeln gerade zutreffen.
If Range("B" & zeileImBereich) = "Standardwartung" And _
Range("B" & zeileImBereich - 1) = "Inspektion" Then
If Abs(kw1 - kw2) > 1 Then regeln(1) = 1
End If
Range("E" & zeileImBereich) = WorksheetFunction.Sum(regeln)
Next
' für die KW-Differenz
Range("F" & unterster) = Range("J" & unterster) - Range("J" & oberster)
oberster = zeile
Anlage = Range("A" & zeile)
End If
Wend
End Sub
Eine Select-Case-Konstruktion halte ich hier für nicht angebracht; Du willst ja doch immer alle Regeln abarbeiten, oder?
Was ich mir eher vorstellen könnte, wäre eine geschickte Verschachtelung der If-Zweige, meinetwegen grob in Worten:
wenn erste Arbeit = Std-Wartg dann
wenn dies,
wenn das,
wenn irgendwas
else
wenn erste Arbeit = bla usw.
wenn, wenn, wenn
endif
endif
Das KANN man schon als CASE schreiben, aber gut verschachtelte IFs sind schneller.
Anbei neue Datei (mit für "alle" Tabellenblätter): https://www.herber.de/bbs/user/102300.xlsm
Schöne Grüße,
Michael

Anzeige
AW: Abschnitt!
16.12.2015 16:10:48
Mike
Hallo Michael!
Ist doch selbstverständlich! Bin für deine hilfe unendlich dankbar und natürlich verstehe ich, wieviel Zeit eine solche Tätigkeit in Anspruch nimmt! Aus diesem Grund: großen Respekt, tolle Arbeit!
Der Code sieht schon sehr gut aus! Zu den Regeln: Ich habe die Regeln so formuliert, dass immer ausschließlich eine einzige zutreffen kann! Die Spalte valid der einzelnen zeilen sollte mit keinem Wert vorbelegt werden! Da für jeden Fall eine Regel zutrifft, kann jede Zelle lediglich den Wert 0 oder den Wert 1 annehmen. Wenn ich die Regeln mittels verschachtelten If Than Else Bedingungen implementiere wird ja ohnehin immer nur eine Regel abgearbeitet, oder täusche ich mich?
Ich vertsehe leider die Aussage: "das bedeutet nicht automatisch, von der Formulierung her, daß
' valid auf 0 gesetzt wird - es ist aber mit 0 vorbelegt.
' Das kann bei kollidierenden Regeln dazu führen, daß der valid-Wert einer
' Zeile durch die Abarbeitung aller Regeln einmal auf 0, einmal auf 1 gesetzt
' wird - genau das wird durch das Array vermieden, das je einen Wert pro Regel
' enthält." nicht unbedingt!
Brauche ich das Array nun, wenn ich sicherstelle, dass jegliches Element nur von einer Regel bearbeitet wird! Bzw. welcher Wert wird in das Array gespeichert, jener der von der "letzten" Regel erstellt wird?
Wie sieht es aus, kann ich dieses Makro theoretisch auch auf das gesamte Arbeitsblatt (inkl. mehrerer Tabellenblätter) anwenden?
Vielen Dank nochmals für diene Mühen! :-)
lg Mike

Anzeige
AW: Abschnitt!
16.12.2015 16:14:38
Mike
Tschuldige, habe den Code mit den verschiedenen Tabellenblättern zuerst übersehen! Funktioniert perfekt! :-) DANKE

Regeln
17.12.2015 15:30:23
Michael
Hallo Mike,
so ein Array kannst Du Dir wie ne Pillenschachtel (diese Dinger, die man im Krhs. verwendet) vorstellen: Du hast eine Reihe "Löcher", in die Du Werte "legen" kannst. Das Array habe ich mit 15 Löchern dimensioniert, die jeweils ein byte enthalten können.
Darauf zugreifen kannst Du mir jeweiligen Nr., 1. Loch (1), 2. Loch (2) usw., so daß Du bei 15 Regeln 15 Werte hast.
Mit .Sum(regeln) werden alle summiert, d.h. daß im Fall eines Logik-Fehlers, der zweimal 1 erzeugt, insgesamt eben 2 rauskäme.
Ob Du das dann im ernsthaften Einsatz so machst, ist eine andere Sache, aber in der Entwicklungsphase finde ich so eine Konstruktion auf alle Fälle hilfreich, um zu testen, ob die Regeln richtig arbeiten.
Also gut, das ist mehr oder weniger Geschmackssache. Zum Überprüfen des Array-Inhalts kannst Du dann direkt unter die hier noch einmal mit übernommene Zeile mit den Regeln schreiben:
Range("F" & unterster) = Range("J" & unterster) - Range("J" & oberster)
For i = 1 To 15: debug.print regeln(i): Next

dann werden die 15 Zellen im Debug- bzw. Direktfenster ausgegeben (das wird im VB direkt unter Deinem Code angezeigt, wenn Du Strg+g drückst).
Sagen wir mal so: solange ich Deine 15 Regeln nicht gänzlich ausformuliert kenne, kann ich schlecht beurteilen, ob es evtl. Verbesserungsmöglichkeiten gibt.
Happy Exceling,
Michael

Anzeige
AW: Regeln
19.12.2015 10:19:17
Mike
Hallo Michael!
Jetz leuchtet mir das mit dem Array durchaus ein! Tolle Idee!
Habe nun versucht, alle Regeln in das Makro zu integrieren! Allerdings leider mit etwas wenig Erfolg!
https://www.herber.de/bbs/user/102363.xlsm
Könntest du vielleicht mal checken, wo ich einen Fehler gemacht habe! Die Auswertung der valid-Spalte entspricht nicht dem Ergebnis, welches ich mir erwünscht habe!
Vielen Dank, Lg Mike

Regeln verbessert
19.12.2015 18:29:42
Michael
Hallo Mike,
die sehr abstrakte Darstellung des Datums als Double finde ich irgendwie lustig.
Es gibt allerdings einen ganz dicken Fehler (1.), einen Knick in der Logik (2.) und mögliche Verbesserungen (3.):
1. Du solltest Dir UNBEDINGT angewöhnen, am Anfang eines Moduls option explicit zu setzen!
Anders läßt es sich nicht vermeiden, daß ein Programm wegen ein paar lausiger Tippfehler zu scheinbar irrwitzigen Ergebnissen kommt.
So Dimst Du brav dat1 und dat2, weist ihnen nach Beginn der For-Schleife Werte zu, aber danach rechnest Du IMMER mit dat (ohne 1) und dat2:
If Abs(dat2 - dat) > Td1 Then

Was dabei ohne Opt. Exp. passiert, ist, daß dat von VBA als neu eingeführte Variable betrachtet und automatisch mit 0.0 vorbelegt wird.
2. Daß die Regeln 1 und 2 nicht ausgeführt werden, ist klar, denn die If-Abfrage ist immer false, weil wir die Schleife erst ab der 2. Zeile im Bereich (oberste+1) laufen lassen.
Daß bei einem neuen Bereich der Wert in "A" der vorhergehenden Zeile anders ist, ist doch auch klar, so daß ich besagte Regeln entsprechend gekürzt vor die Schleife gesetzt habe.
3. Ich hatte in einem vorhergehenden Post bereits eine "geschickte" Strukturierung von IFs skizziert, die Du aber so nicht umgesetzt hast - das habe ich nachgeholt; schau es Dir an.
Beachte bitte, daß Deine paarweisen Abfragen
If Abs(dat2 - dat) > Td1 Then
If Abs(dat2 - dat) 

den Fall "=" nicht berücksichtigen...
Abhilfe ist ein "größergleich" bzw. "kleinergleich" bei einer der beiden Abfragen, und dann läßt sich das auch noch schöner formulieren:
' anstatt
If Abs(dat2 - dat) >= Td1 Then regeln(1, 4) = 1
If Abs(dat2 - dat) = Td1 Then regeln(1, 4) = 1 Else regeln(1, 5) = 0
Ich habe Dir noch ne "Debug-Ausgabe" eingebaut, die Du ja später auskommentieren kannst.
Viel Spaß damit: https://www.herber.de/bbs/user/102370.xlsm
Schöne Grüße,
Michael

Anzeige
AW: Regeln verbessert
20.12.2015 12:06:29
Mike
Hallo Michael!
Die Datei ist der Wahnsinn! Funktioniert alles wie gewollt! Vielen, vielen Dank!
Hast vollkommen recht, dass mit dat2 und dat sollte nicht passieren! Dass die ersten beiden Regeln nicht wie gewünscht funktioniert haben, ist mir nun auch klar! Und auch die von dir vorgeschlagene If-Struktur bzw. Verschachtelung gefällt mir sehr gut und macht genau das, was sie soll.... :-)
Hätte allerdings trotzdem noch kleine Fragen:
Ich möchte nun die Summe der als gültig markierten Arbeiten pro Abschnitt bilden. Hätte dies mit der Funktion WorksheetFunction.Sum(Range ...... probiert, allerdings schaffe ich es nicht, die Summe der gültigen Arbeiten nur innerhalb des Abschnittes zu berechnen!
Weiters wäre es noch sehr fein, wenn ich die Zeitdauer der einzelnen Abschnitte in weiterer Folge durch die Anzahl der gültigen Arbeiten dividieren könnte und diesen Wert in jener Zelle ausgeben, wo auch schon die Abschnittsdauer ausgegeben wurde, nur eben eine Spalte weiter links!
Vielleicht könntest du mir nochmals helfen, bin dir sehr sehr dankbar für deine tolle Unterstützung!
Glg Mike

AW: Regeln verbessert
21.12.2015 17:47:29
Mike
Hallo Michael!
Die Frage hat sich erübrigt, habe es nun doch noch selbst hinbekommen!
Habe das ganze nun nochmals über eine FOR-Schleife realisiert! Hatte diese zwar schon am Beginn, leider allerdings an der falschen Stelle im Code... :-)
Danke nochmals für deine großartige Hilfestellung!
Lg Mike

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige