Werte mit VBA berechnen und in Tabelle ausgeben

[makoe]

Hallo,

ich habe zwei Tabellenblätter mit jeweils einer Tabelle. Nun möchte ich aus Tabellenblatt2 mit "Tabelle Zahlen" Werte in Tabellenblatt1 "Tabelle Ergebnisse" vergleichen und Ihre Vorkommen zählen. Diese Ergebnis soll dann als wert in Tabelle "Zahlen" eingetragen werden. Bisher führe ich die Berechnung mit der Formel SummeWenns durch nur dauert die Berechnung bei 140.000 Zeilen 2 Stunden. Deshalb suche ich nach einer alternativen Lösung.

Vielleicht kann mir da jemand Helfen

Hier die Formel mit SummeWenns:

"=SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z1sString];ergebnisse[Zstring];[@Z2sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z1sString];ergebnisse[Zstring];[@Z3sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z1sString];ergebnisse[Zstring];[@Z4sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z1sString];ergebnisse[Zstring];[@Z5sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z1sString];ergebnisse[Zstring];[@Z6sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z2sString];ergebnisse[Zstring];[@Z3sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z2sString];ergebnisse[Zstring];[@Z4sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z2sString];ergebnisse[Zstring];[@Z5sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z2sString];ergebnisse[Zstring];[@Z6sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z3sString];ergebnisse[Zstring];[@Z4sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z3sString];ergebnisse[Zstring];[@Z5sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z3sString];ergebnisse[Zstring];[@Z6sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z4sString];ergebnisse[Zstring];[@Z5sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z4sString];ergebnisse[Zstring];[@Z6sString])+SUMMEWENNS(ergebnisse[zZähler];ergebnisse[Zstring];[@Z5sString];ergebnisse[Zstring];[@Z6sString])"

Hier ist die Aufzeichnung des Makrorecorders (nur schreibt mir der die Formel in die Zelle und eben nicht den Wert selbst):

Sub Makro1()
'
' Makro1 Makro
'

'
ActiveCell.FormulaR1C1 = _
"=SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z1sString],ergebnisse[Zstring],[@Z2sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z1sString],ergebnisse[Zstring],[@Z3sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z1sString],ergebnisse[Zstring],[@Z4sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z1sString],ergebnisse[Zstring],[@Z5s" & _
"String])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z1sString],ergebnisse[Zstring],[@Z6sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z2sString],ergebnisse[Zstring],[@Z3sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z2sString],ergebnisse[Zstring],[@Z4sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z2sString],ergebnisse[Zstrin" & _
"g],[@Z5sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z2sString],ergebnisse[Zstring],[@Z6sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z3sString],ergebnisse[Zstring],[@Z4sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z3sString],ergebnisse[Zstring],[@Z5sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z3sString],ergebniss" & _
"e[Zstring],[@Z6sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z4sString],ergebnisse[Zstring],[@Z5sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z4sString],ergebnisse[Zstring],[@Z6sString])+SUMIFS(ergebnisse[zZ?hler],ergebnisse[Zstring],[@Z5sString],ergebnisse[Zstring],[@Z6sString])" & _
""
Range("N3").Select
End Sub

BeispielDatei: 
  ZK1 - Kopie.zip (Größe: 26,06 KB / Downloads: 8)

[Ego]

Hallo m...,

A. Aufzeichnung
Wieso wundert es dich, wenn du aufzeichnest wie du eine Formel einträgst, wenn das  Makro dann eine Formel in eine Zelle einträgt.

B. Programm mit Formeln
Selbst wenn ein Makro Werte einträgt indem es die Formeln nachbildet müste es 2.100.000 (=15*140.000) mal die Tabelle "ergebnisse" durchforsten und wäre nicht schneller (auf modernen Rechnern eher langsamer, da Excel selbst für die Berechnung der Formeln alle freigegebenen Kerne nutzen kann, aber ein VBA-Programm immer nur einen Kern)

C. Programm
Man könnte ein Programm schreiben, dass erst die Kombinationen aus "ergebnisse" zwischenspeichert und dann die die Summe aller Zeilen in "zahlen" einträgt. Ich schätze dass das Programm bei 140.000 Datensätzen in der Tabelle "Zahlen" dann wohl weniger als 1 Minute benötigen würde.
Wenn solch ein Programm für dich interessant ist must du es schreiben und noch einige Fragen beantworten.

1. Wieviel Datensätze sind den in der Tabelle "ergebnisse"

2. Sind wirklich nur die 2er Kombinationen interessant oder später auch noch ander Kombinationen?
Abhängig davon würde ich das Programm unterschiedlich strukturieren.

3. Gesucht werden immer kleinere ganze positive Zahlen (sieht nach Lottozahlen aus). Bleibt es dabei und kannst du eine Obergrenze nennen.

4. In den Beispieldaten sind in beiden Tabellen die Zahlen immer aufsteigend sortiert. Kann das Programm dies vorraussetzen.

5. Bisher benötigst du für die Bedingung immer "*" und Leerzeichen vor und hinter den Zahlen. Wird das weiterhin für andere Auswertungen genutzt?

6. Du nutzt strukturierte Tabellen (was ich generell empfehle). Man kann die Tabellen oder Spalten leicht verschieben und umbenenen ohne die Formeln mit den strukturierten Verweisen anpassen zu müssen. "Leider" (ich wüste auch nicht wie) können bei solchen Anpassungen die Makros nicht automatisch angepasst werden.
a) Woran soll das Programm die zu nutzenden Spalten erkennen, an der Position der Spalte in der Tabelle oder and dem Text der Überschrift?
b) Sollen die Tabellen auf andere Arbeitsblätter verschoben werden können oder die Arbeitsblätter umbenannt werden können?

[Ego]

Hallo m...,

in der Anlage einmal ein Beispielprogramm.

Eine Ermittlung aller 2er Kombinationen für 140.000 Zeilen bei deinen vorgegebenen "Ergebnissen" dauert auf meinem Rechner immer weit weniger als 10 Sekunden. Die Ermittlung aller 2er-6er Kombinationen immer weit weniger als 30 Sekunden.

Mit dem Button "erweitern" kann man abhängig von der vorgegebenen Anzahl zusätzliche Testdaten über Zufallszahle hinzufügen (leider wird hierbei die Tabelle zerstört, wird aber ja auch im Original nicht benötigt).
Mit dem Button "tuwat" werden die Kombinationssummen bis zur vorgegebenen Kombinationsanzahl ermittelt.