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

Code-Optimierung

Code-Optimierung
Lena
Hallo zusammen,
ich brauche wieder mal eure Hilfe.
Ich habe eine Excel-Anwendung zur Anzeige der Daten eines Kunden / einer Branche / einer Abteilung. Welche Daten angezeigt werden sollen, bestimmt der Benutzer anhand mehrerer Auswahllisten. Die Auswahlkriterien können auch kombiniert werden, bspw. „Zeige die Summe der Umsätze, welche die Abteilung X mit der Branche Y gemacht hat.“
Die Rohdaten liegen in tabellarischer Form vor:
Kunde | Branche | Abteilung | Umsatz | …
Die Berechnung geschieht mit der Funktion mySummewenn (Code s.u., bspw.
=mysummewenn(„Umsatz“; A1:D1000;“Kunde“;“(Alle)“;“Branche“;“Branche Y“;“Abteilung X“)
Da die Rohdaten aber in letzter Zeit stark angewachsen sind und die Anzahl von Auswahlkriterien von 3 auf 6 erhöht wurde, liegen die Antwortzeiten mittlerweile bei über 1 Minute, was kaum noch akzeptabel ist.
Hat jemand eine Idee, wie man den Code optimieren kann?
Viele Grüße
--------
'Summewenn mit mehreren Kriterien.
'
' - Bereich: Rohdaten, in welchen gesucht werden soll. Die erste Zeile enthält die Spaltenüberschriften,
' die in der Funktion vewendet werden.
' - Summespalte: Die Überschrift der Spalte, die summiert werden soll
' - Parametername: die Überschrift der Spalte, über die eingeschränkt werden soll
' - Parameterwert: Einschränkungskriterium
Const alle As String = "(Alle)"
Public Function mySummewenn(ByVal SummeSpalte As String, Bereich As Range, _
Optional ParameterName_1 As String, Optional ParameterWert_1 As String, _
Optional ParameterName_2 As String, Optional ParameterWert_2 As String, _
Optional ParameterName_3 As String, Optional ParameterWert_3 As String, _
Optional ParameterName_4 As String, Optional ParameterWert_4 As String, _
Optional ParameterName_5 As String, Optional ParameterWert_5 As String, _
Optional ParameterName_6 As String, Optional ParameterWert_6 As String, _
Optional ParameterName_7 As String, Optional ParameterWert_7 As String)
Dim anzahlSpalten As Byte
Dim anzahlZeilen As Integer
Dim intHelp As Integer
Dim parameterSpaltenindex_1 As Byte
Dim parameterSpaltenindex_2 As Byte
Dim parameterSpaltenindex_3 As Byte
Dim parameterSpaltenindex_4 As Byte
Dim parameterSpaltenindex_5 As Byte
Dim parameterSpaltenindex_6 As Byte
Dim parameterSpaltenindex_7 As Byte
Dim mySumme As Single
Dim summeSpaltenindex As Byte
Dim IndexLetzteNichtleereZeile As Integer
anzahlSpalten = Bereich.Columns.Count
anzahlZeilen = Bereich.Rows.Count
mySumme = 0
On Error Resume Next
For intHelp = 1 To anzahlSpalten
If Bereich(1, intHelp) = ParameterName_1 Then parameterSpaltenindex_1 = intHelp
If Bereich(1, intHelp) = ParameterName_2 Then parameterSpaltenindex_2 = intHelp
If Bereich(1, intHelp) = ParameterName_3 Then parameterSpaltenindex_3 = intHelp
If Bereich(1, intHelp) = ParameterName_4 Then parameterSpaltenindex_4 = intHelp
If Bereich(1, intHelp) = ParameterName_5 Then parameterSpaltenindex_5 = intHelp
If Bereich(1, intHelp) = ParameterName_6 Then parameterSpaltenindex_6 = intHelp
If Bereich(1, intHelp) = ParameterName_7 Then parameterSpaltenindex_7 = intHelp
If Bereich(1, intHelp) = SummeSpalte Then summeSpaltenindex = intHelp
Next
For intHelp = 2 To anzahlZeilen
If Bereich(intHelp, parameterSpaltenindex_1) = ParameterWert_1 Or ParameterName_1 = ""  _
Or ParameterWert_1 = alle Then
If Bereich(intHelp, parameterSpaltenindex_2) = ParameterWert_2 Or ParameterName_2 =  _
"" Or ParameterWert_2 = alle Then
If Bereich(intHelp, parameterSpaltenindex_3) = ParameterWert_3 Or  _
ParameterName_3 = "" Or ParameterWert_3 = alle Then
If Bereich(intHelp, parameterSpaltenindex_4) = ParameterWert_4 Or  _
ParameterName_4 = "" Or ParameterWert_4 = alle Then
If Bereich(intHelp, parameterSpaltenindex_5) = ParameterWert_5 Or  _
ParameterName_5 = "" Or ParameterWert_5 = alle Then
If Bereich(intHelp, parameterSpaltenindex_6) = ParameterWert_6 Or  _
ParameterName_6 = "" Or ParameterWert_6 = alle Then
If Bereich(intHelp, parameterSpaltenindex_7) = ParameterWert_7  _
Or ParameterName_7 = "" Or ParameterWert_7 = alle Then
mySumme = mySumme + CSng(Bereich(intHelp, summeSpaltenindex) _
)
End If
End If
End If
End If
End If
End If
End If
Next
mySummewenn = mySumme
End Function
v

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Code-Optimierung
29.10.2009 20:29:17
Andre
Hallo Lena,
muss das alles über VBA gesteuert werden?
Es gibt doch noch andere Möglichkeiten der Datenauswertung wie z.B. mit Pivot Table
Würde Dir das weiter helfen?
Wenn ja, dann lad doch ein kleines Bsp. Deiner Datenbasis hoch.
MFG Andre
AW: Code-Optimierung
29.10.2009 21:05:26
Christian
Hallo Lena,
ohne jetzt näher in deinen Code eingetaucht zu sein schlage ich vor:
- wenn möglich arbeite mit Excelfunktionen (die sind entsprechend schneller).
- wenn vba, dann schreibe die Werte des Ranges in ein Array oder evt. besser Dictionary (sprich Hash) und arbeite in deiner Schleife mit diesem. Das ist bedeutend schneller als ständig auf das Range-Objekt zurückzugreifen.
Ob denn deine 7 verschachtelten If's so notwendig sind, habe ich jetzt nicht geprüft, evt lässt sich da auch noch Performance rausholen.
Gruß
Christian
Anzeige
und außerdem ist die Variable "alle" ...
29.10.2009 21:24:57
Christian
... nicht deklariert. Das ist dann also ein Variant!?
Für was brauchst du "alle"? - die "ParameterWert_X" prüft du ja schon jeweils auf Leerstring.
Schreib mal "Option Explicit" als erste Zeile in deine Modul und lass das noch mal laufen. Das hilft ungemein, insbesondere beim debuggen.
Gruß
Christian
btw: deine Funktion "mySummewenn" ist auch variant. Dieser übergibst du doch nur ein Single. Dann deklariere das Teil doch auch als Single.
AW: und außerdem ist die Variable "alle" ...
30.10.2009 10:36:50
Lena
Danke für Eure Rückmeldungen!
muss das alles über VBA gesteuert werden?
Es gibt doch noch andere Möglichkeiten der Datenauswertung wie z.B. mit Pivot Table

Die Pivot-Tabellen werden in der Datei ebenfalls verwendet wo möglich. Es gibt aber einige Auswertungen, die so angeordnet, formatiert und mit Grafiken versehen sein müssen, dass die Pivottabellen nicht mehr in Frage kommen.
wenn möglich arbeite mit Excelfunktionen (die sind entsprechend schneller).

Ich habe mir auch schon überlegt, auf SUMMENPRODUKT umzustellen. Folgende Probleme ergeben sich:
- die Anzahl und den Mix an Auswahlkriterien bestimmt der Benutzer über die DropDownlisten. Er kann bspw. nach Umsätzen eines Kunden, aber in allen Abteilungen suchen. Wie kann das in einer Summenprodukt-Formel abgebildet werden?
- Das Bugfixing und die Qualitättsicherung ist bei 3 bis 6 Suchkriterien in einer SUMMENPRODUKT-Formel sehr schwierig (in mySummewenn kann man sich anhand von Spaltennamen orientieren). Möglich wäre es auch, jeder Spalte über „Namen definieren“ einen eindeutigen Namen zu vergeben. Allerdings ist es bei 9 Blättern mit Rohdaten mit jeweils ca. 40 bis 100 Spalten genauso unhandlich
wenn vba, dann schreibe die Werte des Ranges in ein Array oder evt. besser Dictionary (sprich  _
Hash) und arbeite in deiner Schleife mit diesem
Klingt vernünftig!
Wie geht das? Wie sieht die Funktion dann aus?
und außerdem ist die Variable "alle" nicht deklariert.
Doch, das ist eine Konstante: s. oberhalb der Funktion:
Const alle As String = "(Alle)"
Dient dazu, um ein Suchkriterium aufzuheben. Bspw. suche nach Umsätzen aller Kunden in einer Abteilung:
=mysummewenn(„Umsatz“; A1:D1000;“Kunde“;“(Alle)“;“Branche“;“(Alle)“;“Abteilung X“)
Wie bereits erwähnt – wie viele Auswahlkriterien letztendlich verwendet werden, entscheide der Beutzer über die Dropdownlisten.
Hat jemand eine Idee, wie man die Formel optimieren könnte?
Vielen Dank im Voraus
Lena
Anzeige
AW: und außerdem ist die Variable "alle" ...
30.10.2009 12:44:38
Born
Eventuell könntest Du auch mit der DBSUMME-Funktion weiterkommen.
AW: und außerdem ist die Variable "alle" ...
30.10.2009 23:57:14
Christian
Hallo Lena,
sorry, du hast natürlich Recht, "alle" war schon korrekt deklariert (hatte ich übersehen).
Zu deinem Problem:
was die Funktion imho ausbremst ist:
- die Such nach den Spaltenüberschriften.
- die verschachtelten If's.
Bei jeder Änderung wird das Calculate-Ereignis und damit die Funktion erneut aufgerufen, d.h. es wird jedesmal (für jede Zeile?) erneut geprüft, in welcher Spalte welche Überschrift steht.
Bau das Teil zum Testen mal um und übergebe direkt die Spalten-Nummer anstatt den Namen der Überschrift und schau, wieviel das bringt.
Als Nächstes würde ich die If's in Angriff nehmen. Ich kenne deine Tabellen nicht, aber ist denn diese Abfrage-Struktur sinnvoll oder kannst du das auch einfacher gestalten?
In der Quintessenz könnte ich mir vorstellen, dass das Thema mit nem Userform und entsprechender Filterauswahl über ComboBoxes besser zu handhaben wäre. Ist natürlich viel vba, aber bei VBA-Gut ist das doch hauptsächlich routine
viele Grüße
Christian
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige