Office: MATRIX-Formel über VBA -> zu langsam

Hallo und guten Abend,

konnte bis jetzt noch nicht testen *bawling … die Arbeit hat mich voll im Griff. *smashing
Melde mich so bald wie möglich ... und sorry an alle Helfer ... das war so nicht geplant! *nene

Danke für Eure Geduld!

 

Hallo IHR freundlichen Helfer,

da bin ich wieder *Smilie … und kann jetzt endlich (m)einen Report abliefern.

Aber vorher gibt es erst mal ein freudiges DANKESCHÖN an Euch! Die unterschiedlichen Lösungen funktionieren alle prima und die Erläuterungen respektive Beschreibungen sind echt *top

Ab zum Report:
Habe alle Varianten (mit Testdaten > 20.000) getestet. Die Ergebnisse sind überall gleich, Unterschiede gibt es nur in der Verarbeitungszeit.
_EarlFred < 1 Sekunde (Variante aus # 5)
_Eberhard 3-4 Sekunden
_Ingolf 4-5 Sekunden

@Eberhard
Deine SQL-Variante ist sehr ausführlich Dokumentiert (Danke dafür) und hilft somit den VBA´lern sicher weiter. Für mich ist SQL absolutes Neuland und sehr schwer zu verstehen. Wie hast du es nett formuliert

Nur leider habe ich diese Zeit momentan nicht, sorry.
Trotzdem DANKE für deine Hilfestellung.

@Ingolf
Auch diese Variante ist für mich nur schwer nachvollziehbar.
Da hab ich das gleiche Problem wie schon bei Eberhard beschrieben.
Mein Dank sei Dir aber sicher!

@EarlFred
Deine Variante kann ich so einigermaßen nachvollziehen (ist auch mit Abstand die Schnellste *wink.gif* ).
Hierauf aufbauend habe ich den CODE an meine Datei angepasst bzw. erweitert.
Wie schon in #11 beiläufig erwähnt, gibt es mehr als 1.-TB zum auslesen. Daher sieht (m)eine jetzige Lösung wie folgt aus. Die Laufzeit beträgt produktiv < 3 Sekunden.
Code:

Pro & Contra sind immer willkommen! Wenn es was zu verbessern gibt, nur keine Scheu, ich lerne gerne dazu!

Mit diesem Report verabschiede ich mich in den Feierabend und wünsche allen ein *Sun Wochenende

 

\@Dirk: Da bin ich aber traurig, dass meine Variante nicht zur Auserwählten wurde und zudem einen Geschwindigkeitsnachteil von über 300% zum Spitzenreiter hat. Wenigstens konnte sie die Ausgangsaufgabenstellung - Laufzeit < 3/4 Tag - erfüllen.

Dazu aber noch zwei Bemerkungen:
- Der SVerweis auf zwei Felder ist noch recht simpel als Aufgabenstellung. Wenn in den auszuwertenden Sheets z.B. die Kombination PersNr und Datum mehrfach auftreten würde, also zusammengefasst werden müsste und überdies vielleicht nur Mindestwerte übertragen oder nur bestimmte Maximalwerte anerkannt werden müssten, wäre der Aufwand bei SQL ein vergleichbarer.
- In den Exceltabellen ist eine Indexierung, so wie sie im Dictionary angewandt wird, nicht nutzbar. In einer Jet-Tabelle (Access) sind demgegenüber bis zu 30 Indizes pro Tabelle möglich. Dass die Verwendung eines Index (Inhaltsverzeichnis) gegenüber dem jedesmaligen Scan der ganzen Tabelle vorteilhaft ist, ist sicher unzweifelhaft.
Insofern sind meine Versuche in Excel auch als Testballon zu verstehen, um eine Vergleichbarkeit gegenüber Gewohntem herstellen zu können.

Zum Code: Objektreferenzen wie ActiveWorkbook, ThisWorkbook u.ä. werden bei mir i.d.R. bei mehr als einfacher Verwendung in einer Objektvariablen abgelegt, die dann nachfolgend genutzt wird.
- Dies ist zu einem geringfügig schneller, zumindest bewirkt in Access die Verwendung von CurrentDb eine jedesmalige Neuinstanzierung des DB-Objektes (die Zeit benötigt), während die Objektvariable die Referenz auf die verwendete Speicheradresse enthält. (Oftmalige Wiederholungen können geringfügige Zeitdifferenzen auf spürbare Werte summieren.)
- Die Objektvariable erhöht Zugriffssicherheit. Bei der gleichzeitigen Verwendung von mehreren Arbeitsmappen und Mehrnutzerbetrieb (ich denke da auch an einen Fernzugriff aus anderen Anwendungen heraus) könnte im Moment ActiveWorkbook etwas anderes sein als was man sich eigentlich vorstellte.

 

Hallo Eberhard,

bitte nicht traurig sein!
So wie es momentan (Stand heute Morgen) aussieht, soll ich die “Auswertungen“ nicht nur für 2010, sondern rückwirkend ab 2008 erstellen.
Dann werden es definitiv mehr als 100.000 Zeilen sein ... mal schauen, wie sich die verschiedenen Varianten dann verhalten.
Werde dieses Thema jetzt schießen, denn die nächste Herausforderung wartet schon auf mich *bye

 

Hallo Eberhard,

die Einfachheit des Problems ist sicher gegeben, aber Lösungen unter Zuhilfenahme von Dictionarys können schon noch etwas bis deutlich mehr an Komplexität bewerkstelligen.

Für mich stehen neben den paar Sekunden des Codedurchlaufs vor allem auch folgende Fragen im Vordergrund: Wie einfach kann ein Code erstellt werden? Wie einfach gestaltet sich seine Wartung bei geänderten Anforderungen / Tabellenaufbau?
Das große Kapital eines Dictionarys ist in meinen Augen seine Geschwindigkeit und seine Flexibilität. Diese erfordert mitunter jedoch einen recht hohen Programmieraufwand, wodurch bei zunehmender Komplexität auch die Überschaubarkeit des Codes leidet. Steigt da nach ein paar Jahren noch einer durch (Kommentierungen hin oder her)?

SQL erscheint mir da auf alle Fälle übersichtlicher (bei gleicher Komplexität der Aufgabe), so dass es in vielen Fällen eine ernsthafte Alternative darstellen könnte (ich vermute bisher, dass es so ist). Ich werde das auf alle Fälle vertiefen und danke nochmals für die tollen Erläuterungen und Deine und Ingolfs Beispiellösungen, die in einigen Details doch interessante Unterschiede aufweisen.

Mit der Frage der vorausgesetzten Datensortierung werde ich sicher anfangen...

Grüße
EarlFred

 

\@EarlFred: Dieser Zusammenfassung kann ich mich vorbehaltlos anschließen.

Ergänzen bzgl. SQL möchte ich der Vollständigkeit halber noch, dass man damit auch prächtig Gruppieren und Aggregieren (Zählen, Summieren, Mittelwerte, ...) kann, dabei im gleichen Schritt die Ausgangsdaten vorher und die Ergebnisdaten danach filtern kann, und das gerne auch im Kombination mit der oben verwendeten Verknüpfung mehrerer Tabellen.