Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1416to1420
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

Verweis mit 2 Kriterien viel zu langsam für 150k

Verweis mit 2 Kriterien viel zu langsam für 150k
24.03.2015 22:51:27
Max
Hallo liebe Community,
ich habe leider ein Problem und würde mich freuen wenn ihr mir helfen könntet :)
Und zwar hab ich eine Excel Datei mit 2 Arbeitsblättern die nach
1. Übersicht (hat 14 Spalten und 150 tausend Zeilen)
2. Vorgaben2 (hat 6 Spalten und 37094 Zeilen)
benannt wurden.
Ich muss nach 2 Kriterien aus 'Übersicht' in 'Vorgaben2' finden und den Wert in einer bestimmten Spalte dann in 'Übersicht' übertragen.
Dies habe ich mit der folgenden Formel gelöst:
=VERWEIS(2;1/(Vorgaben2!$E$2:$E$37094=K2)/(Vorgaben2!$A$2:$A$37094=H2);Vorgaben2!$G$2:$G$37094)
Doch nun soll das natürlich für alle 150k Zeilen der einen Spalte gemacht werden.
Ich habe 2 optionen:
a)Schleife mit: =VERWEIS(2;1/(Vorgaben2!E:E=K" + CStr(i) + ")/(Vorgaben2!A:A=H" + CStr(i) + ");Vorgaben2!G1:G37094)
b)Sowas: Selection.AutoFill Destination:=Range("M2:M" + CStr(letzteZeile)), Type:=xlFillDefault
Zu a) Es dauert einfach zuuuuu lange, obwohl ich das nur ein einziges mal machen muss....(habe screenupadting etc auch mit false versucht, vergebens...)
Zu b) So klappt es, dass ich für alle Zeilen die richtigen Formeln drin habe, aber ich muss es nachrechnen lassen mit sowas: Columns(2).Calculate
Aber das dauert noch länger, bzw. es hängt sich auf (Excel), keine Fehler oderso, der Code ist ja korrekt nur die 150k sind wahrscheinlich einfach zu viel.
Ich muss das ganze für nur 3 Spalten machen und das nur einmalig, wenn es bei einem mal funktioniert, werde ich den Code einfach für die 2 anderen Spalten anpassen, aber es muss erst einmal funktionieren. Ich bitte um eure Hilfe, vielen Dank im Voraus! :)

1
Beitrag zum Forumthread
Beitrag zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Ein paar Tips zum Umgang mit grossen Tabellen
24.03.2015 23:42:15
Daniel
Hi
immer gleiche Formeln schreibt man in alle Zellen gleichzeit, dh ohne Schleife und ohne Autofill:
Range("A2:A" & Cells(Rows.Count, 11).end(xlup).Row).FormulaLocal = "=VERWEIS(2;1/(Vorgaben2!$E$ _
2:$E$37094=K2)/(Vorgaben2!$A$2:$A$37094=H2);Vorgaben2!$G$2:$G$37094)"

die relativen Zellbezüge werden schon richtig übernommen.
wenn du da sicher gehen willst, nimmst du die R1C1-Schreibweise für Formeln, da ist dann auch die ausgeschriebene Formel für jede Zeile gleich, weil die relativen Zellbezüge auch relativ beschrieben werden:
Range("A2:A" & Cells(Rows.Count, 11).end(xlup).Row).FormulaR1C1 ="=LOOKUP(2;1/(Vorgaben2!R2C5:R37094C5=RC11)/(Vorgaben2!R2C1:R37094C1=RC8);Vorgaben2!R2C7:R37094C7)"
Beschleunigen kannst du das ganze wahrscheinlich noch mit ein bisschen Vorarbeit:
1. füge in beiden Tabellen in der ersten Spalte eine Hilfsspalte ein, in der du die beiden Suchbegriffe zu einem Wert zusammenfasst.
du kannst dann normal mit dem SVerweis arbeiten
2. Sortiere die Tabelle Vorgaben2 nach der Hilfsspalte mit den Zusammengefassten Suchbegriffen aufsteigend.
du kannst dann den SVerweis mit 4. Parameter = WAHR verwenden, welcher sehr schnell ist.
3. Sortiere auch die Tabelle Übersicht nach der Hilfsspalte aufsteigend.
da diese Tabelle grösser ist, ist wahrscheinlich dass mehrere Zeilen die gleichen Suchbegriffe haben.
du musst dann die zweitauwendige SVerweissuche nicht mehrfach ausführen, sondern kannst einfach den Wert aus der Zeile obendrüber nehmen.
die Formel sieht schematisch so aus (Spalte A sei die Spalte mit den zusammengefassten Suchbegriffen, spalte X die Spalte mit der Formel:
=Wenn(A2=A1;X1;SVerweis(...))
Da Excel beim Sortieren auch mit grossen Datenmengen sehr schnell ist, kann man das gut ausnutzen um solche Aufgaben zu vereinfachen und zu beschleunigen.
Wenn die Werte wieder zurücksortiert werden müssen, muss man die Formeln vorher durch ihre Werte ersetetzen (was bei dieser Datenmenge sowieso ratsam ist)
Gruß Daniel
Anzeige

315 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige