VBA - Sumproduct
15.10.2013 08:59:44
Dip
Gerne würde ich eine Problemstellung basierend auf das Planungsfile (siehe Datei im Anhang) einbringen, in der Hoffnung, dass mir jemand weiterhelfen kann.
https://www.herber.de/bbs/user/87528.xlsx
Es geht darum, die WBS Elemente (z.B. 1.1.1.2.1) in Spalte A, Blatt "Version VBA" in die Spalte A, Blatt "csv" zu kopieren (ohne Duplikate!), und zwar nur die entsprechende ausgewählte Stufe (mit dem Filter würde ich die gewünschte Stufe wählen).
Im Blatt "csv", Zelle B2 würde ich das entsprechende Planungsjahr auswählen.
Die Monate auf Zeile 4 würden sich entsprechend anpassen (Funktion Monatsende()).
In der Zelle D5 dachte ich, ich würde die Lösung mittels der Funktion Summenprodukt lösen. Wie Ihr aber erkennt, kann Excel aufgrund von mangelnder Ressourcen bzw. grosser Datenrange/-kalkulation den Wert nicht berechnen.
Wäre die Umsetzung der Funktion Summenprodukt in VBA möglich?
Nachfolgend meine bescheidende Lösungmöglichkeit,
Sub test_sumproduct()
Worksheets("csv").Range("D5:O10").FormulaR1C1 = _
Evaluate("=SUMPRODUCT((R[-1]C=Version_VBA!R3C6:R3C303)*(RC[-3]=Version_VBA!C1)*(Version_VBA! _
_
_
_
C6:C303))")
End Sub
leider erhalte ich in der Zelle die Fehlermeldung #WERT!...
Nachdem der VBA Code die Summenprodukte kalkuliert hat, sollte man das Blatt "csv" als csv Datei abspeichern können, mit einem bestimmten Dateiname (als Konstante festgelegt) + das Jahr des gewählten Geschäftsjahr, z.B. "Planung 2013.csv"
Somit sollte es ein Steuerelement für das Kopieren der WBS Elemente sowie Summieren deren Werte und ein Steuerelement für die Erstellung der csv. Datei geben.
Das Festlegen von Konstanten im Blatt "Version_VBA" für
Zeile mit den Monaten
Zeile mit 1. Projekt
Eingabespalte 1. Monat
Wertespalte der WBS Elemente
und im Blatt "csv" für
Eingabespalte für WBS Elemente
Zeile mit den Monaten
1. Zeile mit WBS
Eingabespalte 1. Monat
wäre sehr benutzerfreundlich und elegant
Über irgendwelche Tipps/Hilfe würde ich mich sehr freuen!
Beste Grüsse
Dip
Hallo Dip,
bevor ich damit anfange irgendetwas an VBA-Code zu generieren ein paar Anmerkungen/Fragen:
1. Die Funktion SUMPRODUCT (SUMMENPRODUKT) funktioniert hier schon.
Allerdings dürfen in dem zu summierenden Bereich keine Zellen mit Texten vorkommen.
Folgende Formel in CSV Zelle D5 funktioniert:
=SUMMENPRODUKT((D$4='Version VBA'!$F$2:$KR$2)*
(csv!$A5='Version VBA'!$A$3:$A$500)*('Version VBA'!$F$3:$KR$500))
oder englisch in R1C1-Schreibweise
=SUMPRODUCT((R4C='Version VBA'!R2C6:R2C304)*
(csv!RC1='Version VBA'!R3C1:R500C1)*('Version VBA'!R3C6:R500C304))
Außerdem müssen die Datumswerte in "Version_VBA" Zeile 2 und "CSV" Zeile 4 identisch ermittelt werden. Also z.B. immer der letzte Tag des Monats.
Die Funktion SUMMENPRODUKT ist bei entsprechender Datenmenge/Anzahl der Prüfungen nicht so schnell. Jedoch sind die Tabellenfunktionen meist schneller als vergleichbare VBA-Lösungen, so dass es hier am einfachsten ist im Blatt "csv" die Formeln im erforderlichen Bereich per Makro einzufügen und anschließend durch ihre Werte zu ersetzen.
2. Doppelte WBS-Nummern
Kommen in Spalte A von Blatt "Version_VBA" tätsächlich WBS-Nummern doppelt vor?
In deinem Beispiel ja nicht.
Falls nein, dann könnte man mit den wesentlich schnelleren Funktionen INDEX und VERGLEICH arbeiten um das Blatt CSV mit Daten zu füllen.
3. Verwaltung von bestimmten Spalten und Zeilennummern in den Blättern über Konstanten.
Für das Blatt Version_VBA hast du sie ja schon. Diese kann man für dieses Teilprojekt natürlich auch verwenden, wenn der Code ins gleiche Modul eingefügt wird oder die Konstanten Public gemacht werden.
4. Verzeichnis für die CSV Dateien
Für den Dateinamen hast du ja schon eine Vorgabe: "Text " & Jahr & ".csv"
Für das Verzeichnis muß auch eine Vorgabe erfolgen. z.B. das gleiche wie die Arbeitsmappe oder auch ein bestimmtes Unterverzeichnis davon.
5. Erstellen der csv-Datei
Dazu wird das komplette Blatt in eine neue Arbeitsmappe kopiert und dann diese Arbeitsmappe _ als CSV gespeichert.
Sub Make_CSV()
Dim strFileName As String
strFileName = ActiveWorkbook.Path & Application.PathSeparator _
& "Planung " & ActiveWorkbook.Worksheets("csv").Range("B2").Text
Sheets("csv").Copy
ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlCSV, CreateBackup:=False, _
Local:=True
Application.DisplayAlerts = False
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = True
End Sub
Gruß
Franz
Hallo Franz,
Danke für deine Anmerkungen!
1. Der Grund wieso ich als Output #WERT! erhalte hat in der Tat damit zu tun, das im
WBS Element Buchtaben vorkommen (I.S1041413.NB.1001). Ist die Sumproduct Formel nun nicht mehr anwendbar oder kennst Du eine Lösung dafür?
Den Hinweis mit dem identischen Datum werde ich berücksichtigen.
Um den Data Range einzuschränken wäre es hilfreich, die Suche im Blatt "Version VBA" auf die letzte Zeile zu beschränken.
Bzgl dem Einfügen und Ersetzen der Sumproduct Formel müsste der Code im Blatt "csv" in der Spalte A zuerst prüfen, in welcher Zelle das letzte WBS Element steht und nur bis dorthin die Formel einfügen und ersetzen, da die Anzahl WBS Elemente variieren kann.
2. In Spalte A im Blatt "csv" kommt jedes WBS Element nur einmal vor, in der Spalte A im Blatt "Version VBA" kann ein WBS Element auch mehrmals vorkommen, deshalb die Sumproduct Funktion.
3. Die neuen Konstanten betreffen das Blatt "csv", in welcher Spalte die WBS Elemente stehen, in welcher Zeile Datum, in welchem Range die Formeln eingefügt und ersetzt werden, da sich das Layout ändern kann...
4/5. Nur das Blatt "csv" müsste schlussendlich als csv Datei abgespeichert werden. Als Verzeichnispfad würde ich "M:\Upload\Planung" verwenden.
Ich hoffe meine Erläuterungen helfen Dir eine genauere Idee über mein Vorhaben zu erhalten. Bei weiteren Unklarheiten stehe ich Dir gerne zur Verfügung!
Danke für Deine Aufmerksamkeit und einen schönen Tag noch wünsche ich Dir Franz.
Beste Grüsse
Dip