Früher waren es Mickey-Maus Hefte. Dann folgten Yps und Bravo. Anschließend der Playboy. Und heute ist es die Apotheken-Umschau.
Einfach nicht aufgepasst!
Excel-VBA-Schulung. Eine Teilnehmerin möchte ein kleines Programm mit mir geschrieben haben: Jede Woche erhält sie eine Liste und jede Woche muss sie in dieser Liste Berechnungen durchführen. Eine bestimmte ID (beispielsweise Idefix) wird gesucht, sämtliche Werte (hier drei) werden wie folgt berechnet:
Die Anzahl der Römer wird mit der Anzahl der Piraten multipliziert und die einzelnen Produkte summiert. Das Ergebnis wird durch die Summe der Römer dividiert. Aber nur dann, wenn keine Hinkelsteine vorhanden sind. Alles klar? – Klar!
Ich beginne Schritt für Schritt. Multipliziere und addiere – hierfür bietet sich doch SUMMENPRODUKT an, oder?. Also: los geht’s:
=SUMMENPRODUKT((A:A=G5)*(B:B)*(C:C))
Ich habe drei Mal überlegen müssen, woher die Fehlermeldung rührt. Die Antwort:
Klar: ich multipliziere jede Zelle jeder Spalte. Und das funktioniert bei der Überschrift (Text!) natürlich nicht!
Ich muss ändern. Entweder so:
=SUMMENPRODUKT((A:A=G5)*1;(B:B);(C:C))
Oder indem ich auf den Bereich ohne Überschrift verweise:
=SUMMENPRODUKT((A2:A40=G5)*(B2:B40)*(C2:C40))Oder indem ich den Fehler mit WENNFEHLER abfange, oder oder oder.
Und DANN ist der Rest auch kein Problem – beispielsweise so:
=SUMMENPRODUKT((A2:A40=G9)*1;(B2:B40);(C2:C40);(D2:D40<>"Hinkelstein")*1)/SUMMEWENNS(B2:B40;A2:A40;G9;D2:D40;"<>Hinkelstein")
Gerechnet wird also:
(10 x 31 + 40 x 50) / (10 + 40) = 46,2
Und diese Formel kann man mit dem Makrorekorder aufzeichnen und über alle Zellen „laufen lassen“. Das Ganze wird in der Datei Personal.xlsb gespeichert.
Hallo René, mich würde an diesem Beispiel interessieren, wie das Ganze mit application.Worksheetfunction.sumproduct aussehen würde.
Bzw. insbesondere der Teil mit den Kriterien wie (A2:A40=G9)*1. Geht das damit überhaupt?
Danke schonmal!
Hallo Dominic,
ich habe die Formel eingetragen und dann mit Makrorekorder aufgezeichnet:
[F2]
[Enter]
Dann zeichnet Makrorekorder auf:
ActiveCell.Formula2R1C1 = _
„=SUMPRODUCT((R[-7]C[-7]:R[31]C[-7]=RC[-1])*1,(R[-7]C[-6]:R[31]C[-6]),(R[-7]C[-5]:R[31]C[-5]),(R[-7]C[-4]:R[31]C[-4]<>„“Hinkelstein““)*1)/SUMIFS(R[-7]C[-6]:R[31]C[-6],R[-7]C[-7]:R[31]C[-7],RC[-1],R[-7]C[-4]:R[31]C[-4],““<>Hinkelstein““)“
Danach ermittele ich die Anzahl der Zeilen:
CurrentRegion.Rows.Count
und lasse mit der Methode AutoFill diese Funktion über den Bereich laufen
Copy
PasteSpecial Paste:=xlValues
(als Werte einfügen).
so war die Teilnehmerin glücklich
Liebe Grüße
Rene