Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1660to1664
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 Summenprodukt 2 Kriterien (Monat + Wert)

VBA Summenprodukt 2 Kriterien (Monat + Wert)
05.12.2018 23:32:30
Wolfgang
Hallo,
ich habe folgende Schwierigkeit die ich leider nicht selbst gelöst bekomme:
Ich arbeite mit zwei Arbeitsblättern (AB) in Excel 2010 wobei AB1 die Ergebnisse und AB2 die Basistabelle darstellt. Die Basistabelle besteht aus 33 Spalten und kann bis zu ca. 100.000 Zeilen enthalten.
Die Basistabelle hat immer die gleiche Struktur (Lieferantenname, Lieferantennummer, Bestelldatum, Bestellwert) lediglich der Inhalt variiert, weil neu geladen werden kann.
Nun möchte ich über das Summenprodukt in AB1 pro Lieferantennummer und Monat die Summen ermitteln und ein kleines immer gleiches Dashboard kreeieren
AB2. Spalte G = Lieferantennummer
AB2.Spalte B = Bestelldatum
AB2.Spalte X = Bestellwert
Im AB1 stehen die Suchbegriffe Lieferantennummer und Monat(als Zahl 1-12)
Hier mein Code:
Sub test()
Dim WS1, WS2 As Worksheet
Set WS1 = Worksheets("PO-Dashboard")
Set WS2 = Worksheets("PO-Data-Source")
Dim SumProdZeile, SumProdZeileEnde, SumProdSpalte As Integer
Dim x,z  As Long
SumProdZeileEnde = 500 + lzNoDup - 1	' lzNoDup = letzte Zeile ohne
Duplikate der Suchwerte, wird
vorher brechnet
'--------------Ergebniszellen in WS1 -------------------------Supplier-Nr.------------------ _
-------Monat----------------------------------Summe PO Daten--------------
'WS1.Cells(SumProdZeile, SumProdSpalte)=SUMMENPRODUKT((E11='PO-Data-Source'!G3:G50000)*(G8= _
MONAT('PO-Data-Source'!B3:B50000))*('PO-Data-Source'!X3:X50000))
For SumProdZeile = 500 To SumProdZeileEnde  ' Zeilenschleife (Werte in "G")
For SumProdSpalte = 7 To 18     		' Spaltenschleife (Monate)
x = WS1.Cells(SumProdZeile, 5).Value
z = WS1.Cells(8, SumProdSpalte).Value
WS1.Cells(SumProdZeile, SumProdSpalte).Formula = Evaluate("=SUMPRODUCT((" & x & " =  _
WS2.Range(WS2.Cells(3,7),WS2.Cells(lz,7)) * (" & z & " = MONTH(WS2.Range(WS2.Cells(3,2),WS2.Cells(lz,2))) * (WS2.Range(WS2.Cells(3,24),WS2.Cells(lz,24)))")
Next SumProdSpalte
Next SumProdZeile
End Sub
Fehlermeldung:
Leider wird im Excel immer nur 0 angezeigt und meine Ergebnistabelle sieht leider so aus. Ergebnistabelle sieht leider so aus:
Monate
Lieferant L-Nummer 1 2 3 4 5 …
abc GmbH 1234567 #WERT! #WERT! #WERT! #WERT! #WERT! #WERT!
bcd GmbH 2345678 #WERT! #WERT! #WERT! #WERT! #WERT! #WERT!
cde GmbH 3456789 #WERT! #WERT! #WERT! #WERT! #WERT! #WERT!
efg GmbH 4567890 #WERT! #WERT! #WERT! #WERT! #WERT! #WERT!
fgh GmbH 5678901 #WERT! #WERT! #WERT! #WERT! #WERT! #WERT!
ghi GmbH 6789012 #WERT! #WERT! #WERT! #WERT! #WERT! #WERT!
… … … … … … … …
Frage:
Wie bekomme ich das geändert, so dass die Berechnung funktioniert?
Wie würde die Sumproduct Formel aussehen wenn noch mehr Suchbegriffe hinzukommen?

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

Betreff
Datum
Anwender
Anzeige
AW: VBA Summenprodukt 2 Kriterien (Monat + Wert)
06.12.2018 10:54:57
Rudi
Hallo,
#Wert lässt darauf schließen, dass in AB2!X Texte vorkommen. Damit kann Summenprodukt nicht umgehen.
Gruß
Rudi
AW: VBA Summenprodukt 2 Kriterien (Monat + Wert)
06.12.2018 11:55:09
Rudi
evtl. so:
Sub test()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim objWerte(1 To 12) As Object, oObj
Dim lngI As Long, intMonate As Integer
Dim arrWS2, arrOut(), iCounter As Integer
Set WS1 = Worksheets("PO-Dashboard")
Set WS2 = Worksheets("PO-Data-Source")
For intMonate = 1 To 12
Set objWerte(intMonate) = CreateObject("scripting.dictionary")
Next intMonate
'Liferantennummern aus Dashboard sammeln
With WS1
For lngI = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
For intMonate = 1 To 12
objWerte(intMonate)(.Cells(lngI, 2).Value) = 0
Next intMonate
Next lngI
End With
'Daten aus WS2 in Array
arrWS2 = WS2.Cells(1, 1).CurrentRegion.Resize(, 24)
'Werte sammeln
For lngI = 2 To UBound(arrWS2)
objWerte(Month(arrWS2(lngI, 2)))(arrWS2(lngI, 7)) = _
objWerte(Month(arrWS2(lngI, 2)))(arrWS2(lngI, 7)) + arrWS2(lngI, 24) * 1
Next lngI
'AusgabeArray
ReDim arrOut(1 To oObj(1).Count, 1 To 13)
For Each oObj In objWerte(1)
iCounter = iCounter + 1
arrOut(iCounter, 1) = oObj
For intMonate = 1 To 12
arrOut(iCounter, intMonate + 1) = objWerte(intMonate)(oObj)
Next intMonate
Next oObj
'Array in Tabelle schreiben
WS1.Cells(2, 2).Resize(UBound(arrOut), UBound(arrOut, 2)) = arrOut
End Sub

Gruß
Rudi
Anzeige
AW: VBA Summenprodukt 2 Kriterien (Monat + Wert)
06.12.2018 20:46:39
Wolfgang
Hallo Rudi,
vielen Dank für deinen Ansatz. Leider bekomme ich eine Typenunverträglichkeitsmeldung Laufzeitfehler 13 an folgender Stelle:
For lngI = 2 To UBound(arrWS2)
objWerte(Month(arrWS2(lngI, 2)))(arrWS2(lngI, 7)) = _
objWerte(Month(arrWS2(lngI, 2)))(arrWS2(lngI, 7)) + arrWS2(lngI, 24) * 1

Next lngI
Ich habe leider vom data dictionary keine Ahnung. Woran könnte es liegen?
Merci Wolfgang
AW: VBA Summenprodukt 2 Kriterien (Monat + Wert)
07.12.2018 10:43:29
Rudi
hallo,
lad mal einen Auszug aus deiner Datei hoch
AW: VBA Summenprodukt 2 Kriterien (Monat + Wert)
07.12.2018 11:10:57
Rudi

Sub test()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim objWerte(1 To 12) As Object, oObj
Dim lngI As Long, intMonate As Integer
Dim arrWS2, arrOut(), iCounter As Integer
Dim intMonat As Integer, dblWert As Double, vntLIEF
Set WS1 = Worksheets("PO-Dashboard")
Set WS2 = Worksheets("PO-Data-Source")
For intMonate = 1 To 12
Set objWerte(intMonate) = CreateObject("scripting.dictionary")
Next intMonate
'Liferantennummern aus Dashboard sammeln
With WS1
For lngI = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
For intMonate = 1 To 12
objWerte(intMonate)(.Cells(lngI, 2).Value) = 0
Next intMonate
Next lngI
End With
'Daten aus WS2 in Array
arrWS2 = WS2.Cells(1, 1).CurrentRegion.Resize(, 24)
'Werte sammeln
For lngI = 2 To UBound(arrWS2)
vntLIEF = arrWS2(lngI, 7)
If objWerte(1).exists(vntLIEF) Then         'nur Lieferanten aus Dashboard
If IsNumeric(arrWS2(lngI, 24)) Then       'in X steht eine Zahl
dblWert = CDbl(arrWS2(lngI, 24))
intMonat = (Month(CDate(arrWS2(lngI, 2))))
objWerte(intMonat)(vntLIEF) = objWerte(intMonat)(vntLIEF) + dblWert
End If
End If
Next lngI
'AusgabeArray
ReDim arrOut(1 To objWerte(1).Count, 1 To 13)
For Each oObj In objWerte(1)
iCounter = iCounter + 1
arrOut(iCounter, 1) = oObj
For intMonate = 1 To 12
arrOut(iCounter, intMonate + 1) = objWerte(intMonate)(oObj)
Next intMonate
Next oObj
'Array in Tabelle schreiben
WS1.Cells(2, 2).Resize(UBound(arrOut), UBound(arrOut, 2)) = arrOut
WS1.Activate
End Sub

Anzeige
AW: VBA Summenprodukt 2 Kriterien (Monat + Wert)
08.12.2018 20:36:22
Wolfgang
Hallo Rudi,
ich habe einen Auszug hochgeladen: https://www.herber.de/bbs/user/125974.zip
In der Schleife soll die gleiche Rechnung für die Spalten im Case durchgeführt werden. Später würde ich gerne noch weitere Spalten hinzufügen und für zusätzliche Kriterien die Summenprodukte berechnene wollen.
Danke für Deine Hilfe.
Wolfgang

304 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige