Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1632to1636
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

Berechnen über VBA

Berechnen über VBA
13.07.2018 13:03:16
mm-biker
Hallo liebes Forum,
ich lese in eine Exceltabelle über VBA Makro täglich Messdaten aus diversen CSV Dateien ein.
Jede Zeile der CSV Dateien beinhaltet einen Messpunkt, ein Datum und verschiedene Messwerte. Jeder Messpunkt erzeugt in der Tabelle eine Zeile und die Messwerte werden jeweils zu einem Datum in den Spalten aufsummiert.
Nach dem Einlesen habe ich also eine Tabelle mit x-Zeilen und 365-Spalten. Die Anzahl der Zeilen wächst im Laufe des Jahres von anfänglich 5.000 Zeilen bis auf 10.000 Zeilen an.
Wenn die Daten eingelesen wurden, werden per VBA Makro in anderen Tabellenblättern Summen unter verschiedenen Gesichtspunkten gebildet. z.B. nach verschiedenen Typen der Messwerte, Monatssummen etc. Ich lese also die Tabelle noch einmal von Zeile 1-n und Spalte 1-365 in verschachtelten Schleifen durch und addiere die Werte direkt in den Zellen der anderen Tabellenblätter. Ich verwende dabei keine Variablen Arrays sondern addiere direkt in den Zellen.
Jetzt zu meinem Problem: Wenn ich in eine leere Tabelle die Werte für z.B. ein halbes Jahr einlese entstehen ca. 7.500 Zeilen zu denen in 180 Spalten Werte vorhanden sind. Die Berechnung der Summen dauert dann ca. 10 Minuten. Wenn ich das VBA-Makro zur Berechnung dann noch einmal starte, dauert es wieder ca. 10 Minuten. Wenn ich die Datei einmal speichere und wieder aufrufe dauert die Berechnung plötzlich fast 2 Stunden. Die üblichen Tricks mit ScreenUpdating=Fals, Calculation=xlCalculationManuel, EnableEvents=Fale; DisplayAlerts=False habe ich schon eingebaut. Leider ohne Erfolg. Die Datei ist mit den Werten von 6 Monaten ca. 20MB groß.
Ich vermute es hat etwas mit unterschiedlicher Nutzung des Arbeitsspeichers oder mit den Zellenformaten zu tun. Ich finde aber keinen Ansatz da etwas zu verändern. Hat jemand einen Tipp für mich?
VG
mm-biker

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA-Arrays
13.07.2018 14:23:02
Fennek
Hallo,
in so großen Dateien sind Formeln recht langsam, aber VBA ist nur dann (ca. 20-100 mal) schneller, wenn alle Berechnungen in Arrays erfolgen. Alle Formeln und Funktionen können zwar in Arrays übertragen werden, aber der Aufwand kann erheblich sein.
mfg
AW: Berechnen über VBA
13.07.2018 14:38:27
Daniel
Hi
kannst du mal eine gekürzte Beispieldatei hochladen und zeigen, was du genau rechnen willst?
grundsätzlich solltest du vermeiden, Zellen einzeln zu bearbeiten, sondern möglichst als Block.
Dabei können Formeln durchaus hilfreich sein.
weiterhin kommt es bei dieser Datenmenge schon darauf an, was genau gerechnet wird.
wenn du beispielsweise in dieser Größenordnung die Summen für ein halbes Jahr mit SummeWenns oder Summenprodukt berechnest, dann ist das sehr zeitaufwendig.
viel effektiver ist, dann:
- die Tabelle nach Datum zu sortieren (darin ist Excel sehr schnell)
- erste und letzte Zeile des gewünschten Datumbereichs zu ermitteln auch das geht in sortierten Tabellen sehr schnell
- mit Index die Summe zwsichen diesen Zeilen bilden.
Gruß Daniel
Anzeige
AW: Berechnen über VBA
13.07.2018 16:10:53
mm-biker
Hallo zusammen,
danke für die Tipps. Das löst leider mein Problem nicht. Prinzipiel ist die Verarbeitung ja schon recht performant. Mit Variablen Arrays könnte ich noch einiges beschleunigen, aber die Umstellung wäre recht aufwendig. Mein Problem liegt nur in der unterschiedlichen Verarbeitungszeit vor und nach dem ersten speichern. Vor dem ersten Speichern dauert das Berechnen ca. 10 Minuten. Danach 2 Stunden. Die Anzahl der zu verarbeitenden Sätze ist dabei gleich. Auch wird der gleiche Code durchlaufen. Beim Berechnen verwende ich keine Formeln sondern rechne direkt in und mit den Zelleninhalten.
Hier mal exemplarisch ein Codeschnipsel.
' Index läuft über alle Spalten und Zeilen der Tabelle
'
Do While intSpalte "kleiner" maxSpalte
' bis zur ersten leeren Zeile verarbeiten
Do While Worksheets("Sheet-1").Range("B" & IndexZeile) "nicht" leer
' Nur verarbeiten, wenn auch Inhalt in der Spalte ENDE vorhanden ist
If Worksheets("Sheet-1").Cells(IndexZeile, intSpalte) "nicht" leer Then
' Monat ermitteln
IndMonat = Mid(Worksheets("Sheet-1").Cells(2, intSpalte), 4, 2)
' Werte pro Job Gesamt und pro Monat aufaddieren für OK
Worksheets("Sheet-2").Cells(IndZielZeile, indMonat) =
Worksheets("Sheet-2").Cells(IndZielZeile, indMonat) +
Worksheets("Sheet-1").Cells(IndexZeile, indSpalte)
End If
IndexZeile = IndexZeile + 1
Loop
intSpalte = intSpalte + 1
Loop
Anzeige
AW: prüfe UsedRange
13.07.2018 16:24:00
Fennek
Hallo,
beim Speichern wird der UsedRange neu berechnet und i.d.R. wenn Spalten gelöscht wurden, wieder verkleinert. Sieh die doch einmal VOR und NACH dem Speichern den UsedRange an.
Der gezeigte Code ist eher ein Konzept, aber kein lauffähiger Code ("kleiner").
mfg
AW: prüfe UsedRange
13.07.2018 16:48:32
mm-biker
Hallo Fennek,
der Code zeigt das Ganze nur exemplarisch und ist stark gekürzt. Größer und Kleiner Zeichen verträgt das Forum hier nicht, daher der kleine Trick. :-)
UsedRange ist vor und nach dem Speichern gleich, da hatte ich auch schon mal drauf geschaut. Ich verwende auch ActiveSheet.Cells(Rows.Count, 1) um die belegte Anzahl an Zeilen zu ermitteln.
Ich hab gerade noch ein wenig mit der Dateigröße herumgespielt. Hab einfach Zeilen gelöscht. Wenn die Dateigröße über 5-6 MB steigt, wird die Verarbeitungszeit überproportional größer.
VG
mm-biker
Anzeige
AW: prüfe UsedRange
15.07.2018 15:04:13
mm-biker
Hi,
hab den Tread noch mal auf offen gesetzt. Hat irgend jemand eine Idee wie ich das Ganze noch positiv beeinflussen kann. Hab noch mal ein wenig mit den Dateigrößen herumgespielt. Die Probleme treten erst auf, wenn die Datei gespeichert wurde. Wenn ich die Daten in eine leere Datei vollständig eingelesen habe ist die Datei laut Windows Explorer immer noch nur 400KB groß. Vermutlich sind alle Daten dann noch im Speicher oder in den Tempdateien ausgelagert. Dann ist auch die Berechnung sehr schnell. Leider gelingt es mir nicht diesen Zustand nach dem Speichern zu erzeugen. Wenn die Datei einmal gespeichert wurde ist sie ca. 20MB groß. Vielleicht hat ja doch noch jemand einen Tipp. Wäre echt froh, wenn ich das Problem von der Backe hätte.
VG
mm-biker
Anzeige
AW: Pivot?
16.07.2018 09:58:51
Fennek
Hallo,
wenn die Datei beim Speichern so viel größer wird, KANN es daran liegen, dass mehrere Pivot-Tabellen die Datei vervielfältigen.
mfg

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige