Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1756to1760
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

Summenprodukt mit Vergleich als VBA-Code

Summenprodukt mit Vergleich als VBA-Code
14.05.2020 20:10:05
Sifi
Hallo zusammen,
dank eurer Mithilfe konnte ich eine Auswertungsdatei aufbauen, die auch von den Werten her super funktioniert und auch per Makro steuerbar ist. Mein Problem ist leider die lange Laufzeit. Alleine für den Inbound mit ca. 1700 Zeilen dauert es ungefähr 3 Minuten. In der Spitze können es aber bis zu 14000 Zeilen werden. Also würde die Auswertung lange dauern.
Es liegt an den ganzen Summenprodukt mit Vergleich Formeln, da diese sehr verschachtelt sind und einen großen Bereich haben. Ich würde gerne die Auswertung wie sie in der Beispieldatei in der Range AR4:AF10 per Makro laufen lassen, sodass hier nicht mehr die Formeln stehen, sondern das dies per Makro gemacht wird und die Ergebnisse direkt eingetragen werden. Leider sind meine VBA-Kenntnisse noch am Anfang (siehe VBA-Code in der Datei), wobei ich täglich mehr lerne. Ich hoffe ihr könnt mir bei dem Problem/Wunsch helfen.
https:\/\/www.herber.de/bbs/user/137559.xlsm
https:\/\/www.herber.de/bbs/user/137560.xlsx
Bei Fragen oder Unklarheiten helfe ich natürlich gerne weiter :)
Vielen Dank im Voraus!!!
Viele Grüße Sifi

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt mit Vergleich als VBA-Code
14.05.2020 20:33:10
onur
Wie wäre es, wenn du erklärst, wozu die Datei(en) gut ist/sind und was genau die Formeln berechnen sollen.
Dann braucht man nicht alles mühsam zu analysieren, nur um rauskriegen zu können, worum es überhaupt geht.
AW: Summenprodukt mit Vergleich als VBA-Code
15.05.2020 08:45:38
Sifi
Hallo onur,
du hast Recht. Also die eine Datei ist ein Systemabzug. Dieser wird in die andere Datei geladen. Dort sollen die Auswertungen gemacht werden. Dabei wir in den Spalten folgendes gemacht. Ich hoffe es ist jetzt verständlicher.
Grundsätzlich ist es immer auf Basis des Datums in Spalte K. In AE4 bis AE10 steht das einzelne Datum für die Übersicht
AF (Menge/Tag): Die Menge der Spalte S pro Tag zählen
AG (Pakete/Tag): Anzahl Pakete (Spalte Y) pro Tag zählen - dabei gleiche nur 1 mal zählen am Tag zählen
AH (Art. Nr/Tag): Anzahl Art. Nr. (Spalte Q) pro Tag zählen - dabei gleiche nur 1 mal zählen am Tag zählen
AI (Container/Tag): Anzahl Container (Spalte G) pro Tag zählen - dabei gleiche nur 1 mal zählen am Tag zählen
AJ (Paletten/Tag): Anzahl Paletten (Spalte Z) pro Tag zählen - dabei gleiche nur 1 mal zählen am Tag zählen
AK (Putaway/Tag): Anzahl Putaway (Spalte Z) pro Tag zählen - dabei gleiche nur 1 mal zählen am Tag zählen ABER hier als Datum die Spalte W!
AL (Ref/Tag): Anzahl Ref (Spalte T) pro Tag zählen - dabei gleiche nur 1 mal zählen am Tag zählen
AM ( 3 std?): Alles aus der Hilfsspalte AT zählen was nicht zwischen 0 und -0,125 liegt. In der Hilfsspalte AT wird Spalte V – Spalte X gerechnet.
AN (total): Spalte W zählen, wenn ein Datum drin steht.
AO (Return/Tag): Anzahl Return (Spalte Z) pro Tag zählen wenn eine Mengendiff (Spalte N-O) auftritt - dabei gleiche nur 1 mal zählen am Tag zählen
Mengendiff AO und AP:
Pro Order Nummer (Spalte A) sollen die Lines (Spalte B) nur 1 mal gezählt werden, wenn Sie die gleiche Nummer (Spalte B) haben. Zusätzlich soll durch Spalte D zwischen „Returns“ und „Rest“ (alles was nicht Retruns ist) unterschieden werden. Für jede einzelne Line soll nun die Spalte N-O gerechnet werden. Das Ergebnis für Returns kommt in Spalte AO und der Rest in AP
Line Tota AQ und ARl:
Pro Order Nummer (Spalte A) sollen die Lines (Spalte B) nur 1 mal gezählt werden, wenn Sie die gleiche Nummer (Spalte B) haben. Zusätzlich soll durch Spalte D zwischen „Returns“ und „Rest“ (alles was nicht Retruns ist) unterschieden werden. Ergebnis soll die gesamte Anzahl für den jeweiligen Tag sein. Das Ergebnis für Returns kommt in Spalte AQ und der Rest in AR
Anzeige
AW: Summenprodukt mit Vergleich als VBA-Code
14.05.2020 20:50:46
Daniel
HI
ohne VBA kannst du das so beschleunigen
1. Liste nach Relevantem Datum sortieren (ist ja offensichtlich schon
2. in zwei Hilfsspalten die erste Zeilennummer und die letzte Zeilennummer für jedes Datum ermitteln (bekommsts du das bei Excel-gut alleine hin?)
3. in den Summennprodukten, die die Auswertung pro Tag machen, gibst du jetzt die Zellbezüge nicht mehr über den ganzen Bereich ein, sondern mit Index gezielt auf die Zeilen des jeweiligen Tages
dh wenn du in AE4 das Datum stehen hast, sollte in AF4 die Startzeile (4) und in AG4 die Endzeile (1389) stehen.
dann änderst du deine Summenprodukte wiefolgt ab:
- die Prüfung auf Datum (Spalte K) kann entfallen
- alle Zellbezüge auf die Spalten werden nach diesem Schema gebildet:
statt: Y$1:Y$20000&K$1:K$20000 schreibst du Index(Y:Y;$AF4):Index(Y:Y;$AG4)
das macht die Sache schneller, weil dann ein Summenprodukt nicht mehr über alle Zeilen, sondern nur noch über die Zeilen des jeweiligen Tages juckeln muss.
Beachte allerdings, dass dir der Vergleich nicht die absolute Zeilennummer liefert, sonderen die Postition innerhalb des angebenen Zellbereichs, dh das Vergleichserbnis 1 korrespondiert dann mit der Zeilennummer aus AF4.
Dh die Prüfung Zeile() = Vergleich() muss dann so aussehen: Zeile() = (Vergleich() + $AG4-1)
Gruß Daniel
Anzeige
AW: Summenprodukt mit Vergleich als VBA-Code
14.05.2020 20:55:22
Daniel
noch ein Hinweis zu deinen Codeversuchen:
es wird nicht schneller, wenn du die exakt gleiche Berechnung der Formeln mit Hilfe von VBA durchführen lässt.
Wenn du hier was tun wolltest, müsstest du dich mal mit dem Dictionary-Objekt auseinander setzen, denn damit könntest du die Auswertung u.U. mit einem einzigen Schleifendurchlauf über die Daten durchführen, was erheblich schneller wäre.
Aber das einem VBA-Anfänger zu erklären, würde den Rahmen es Forums sprengen.
Gruß Daniel
AW: Summenprodukt mit Vergleich als VBA-Code
15.05.2020 09:36:52
Sifi
Hallo Daniel,
danke für deine Hilfe. Das Start und Enddatum bekomme ich hin. Nur bei der Formel nicht wirklich.
=SUMPRODUCT((MATCH((INDEX(Y:Y;AF4):INDEX(Y:Y;AG4));(INDEX(Y:Y;AF4):INDEX(Y:Y;AG4));0) =ROW(Y$1:Y$20001))*(K$1:K$20001=AE4))
Wie genau ist das mit dem "die Prüfung Zeile() = Vergleich() muss dann so aussehen: Zeile() = (Vergleich() + $AG4-1)"
Da fehlt es mir leider gerade.
Zu VBA:
Wollte auch nicht die Code an sich reinschreiben, dann ist es wie du sagst genau so langsam. Sondern das es alles im Hintergrund berechnet wird. Sulprobil hat ja auch was gepostet und in seinem Code ist ebenfalls "scriptingDictionary" Es scheint also das es darüber möglich ist. Nur leider versteh ich davon echt nix :(
Viele Grüße
Sifi
Anzeige
AW: Summenprodukt mit Vergleich als VBA-Code
15.05.2020 09:53:44
Daniel
Hi
1.B dass im SummenProdukt die Zellbezüge immer gleich groß sein müssen!
2. Die Datumsprüfung kannst du weglassen.
3. Damit der beim Vergleich gebildete Wahrheitswert vom SummenProdukt gezählt werden kann, muss dieser in mindestens eine Rechenoperation eingebunden sein.
Im Prinzip etwa so, wobei ich das jetzt nicht auf Rechtschreibung und Klammersetzung geprüft habe.
Also bitte nicht kopieren, sondern lesen, verstehen, selber schreiben:
=SUMPRODUCT(1*(MATCH((INDEX(Y:Y;AF4):INDEX(Y:Y;AG4));(INDEX(Y:Y;AF4):INDEX(Y:Y;AG4));0) =ROW(INDEX(Y:Y;AF4):INDEX(Y:Y;AG4))-AF4+1)))
Ja Dictionary ist eine super Funktion, wenn man solche Auswertungen effizient programmieren will.
Da solltest du dich Mal mit beschäftigen.
Gruß Daniel
Anzeige
AW: Summenprodukt mit Vergleich als VBA-Code
15.05.2020 09:42:46
Sifi
Hallo Bernd,
Danke für deine Antwort. Anscheind kann man es über "scriptingDictionary" machen. Ich habe leider keine Ahnung davon, auch nach durchsicht von deime Link.
Kannst du mir da weiterhelfen?
Viele Grüße
Sifi
Anzeige
AW: Summenprodukt mit Vergleich als VBA-Code
15.05.2020 15:21:27
Sulprobil
Hallo,
meine Webseite ist als Hilfe zur Selbsthilfe gedacht.
Ich rege an, dass Du Dich mal mit meinem Beispiel beschäftigst.
Du willst doch später unabhängig von fremder Hilfe sein?!
Viele Grüße,
Bernd P

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige