XVERWEIS beschleunigen

[DDFonline]

Hallo!

Ich habe ein umfangreiches Excel mit über 40 Sheets (mit jeweils mehreren Hundert Zeilen und Spalten), die mit vielen Verlinkungen (INDEX, XVERWEIS, SVERWEIS) untereinander verknüpft sind. Da das Excel mittlerweile sehr langsam beim Öffnen oder Eingeben von Werten geworden ist, frage ich mich, wie man die Verlinkungen am besten organisiert. 

Beim nachfolgenden, einfachen Beispiel suche ich z.B. den Wert von A1 in der Spalte A von Sheet2, um den zugehörigen Wert in Spalte BC von Sheet 2 zurück zu erhalten. Ich sehe eine ganze Reihe von Möglichkeiten, z.B.:

1.        XVERWEIS(A1;Sheet2!A:A;Sheet2!BC:BC)
2.  XVERWEIS(A1;AAAA;BBBB)
      mit AAAA = INDIREKT(Sheet2!A1:A10000”) oder als Namen definiert und
       mit BBBB = INDIREKT (Sheet2!BC1:BC10000“) oder als Namen definiert
3.  XVERWEIS(A1;Sheet2!A1:BEREICH.VERSCHIEBEN(Sheet2!A1;9999;0); Sheet2!BC1:BEREICH.VERSCHIEBEN(Sheet2!BC1;9999;0))
4.  SVERWEIS(A1;Sheet2!A1:BC10000;3;FALSCH)
5.  SVERWEIS(A1;CCCC;55;FALSCH)
      mit CCCC = indirekt(Sheet2!A1:BC10000) oder als Namen definiert
   

Bisher habe ich die einfache Lösung von Nr. 1 verwendet und frage mich, ob das Excel mit einer Lösung nicht schneller sein könnte, bei der die Vektoren/Matrizen zwar indirekt definiert, aber deutlich kleiner sind. Die Sheets sind sehr unterschiedlich stark mit Daten gefüllt. Hat jemand Erfahrungen damit oder Empfehlungen dafür? Was könnte die schnellste Lösung sein?

[BoskoBiati]

Hi,


INDIREKT und BEREICH.VERSCHIEBEN dürften wohl das Ganze eher ausbremsen als beschleunigen.

https://online-excel.de/excel/gruselcf19.html?f=6

Beschleunigen könnte zum Einen 

Code:

=XVERWEIS(A1;Sheet2!A:.A;Sheet2!BC:.BC;"n.v.")

zum Anderen eine Zusammenfassung der Sheets, um die Anzahl der Verlinkungen und Formeln zu reduzieren.

[HKindler]

Hi,

zur Ergänzung: XVERWEIS ist wesentlich besser als SVERWEIS. Wobei "besser" sich vor allem auf die Flexibilität und erweiterte Möglichkeiten bezieht.

Wenn es schneller werden soll, lohnt es sich die Suchspalte zu sortieren und dann binär zu suchen:
XVERWEIS(A1;Sheet2!A:A;Sheet2!BC:BC;;;2)

[DDFonline]

Hallo Edgar,

danke für den Hinweis mit dem "." vor dem Spaltenbuchstaben. Und der Link auf die Grusel-Excel war auch interessant.

Wenn ich das richtig verstehe, würdest Du bei der Suche auch eher eine ganze Spalte A:A verwenden und nicht versuchen, diese auf z.B. A1:A500 zu kürzen?

Dirk

---

Hallo Helmut,

danke, der Hinweis auf die binäre Suche war gut, denn die meisten Tabellen in meinem Excel sind alphabetisch nach dem Suchtext sortiert, während die Ergebniswerte natürlich sehr unterschiedlich ausfallen. Ich nehme aber an, dass sich die ;2 nur auf die Suchspalte bezieht, oder?

Dirk

[RPP63]

Ich nehme aber an, dass sich die ;2 nur auf die Suchspalte bezieht, oder?

Moin und No, Sir!
Lies Dir mal die Hilfe zumXVERWEIS() durch:
https://support.microsoft.com/de-de/offi...eae8bf5929?

Gruß Ralf

[BoskoBiati]

Hallo,

Wenn ich das richtig verstehe, würdest Du bei der Suche auch eher eine ganze Spalte A:A verwenden und nicht versuchen, diese auf z.B. A1:A500 zu kürzen?

Nein! Der Punkt in A:.A bewirkt, dass nur die benutzten Zeilen geprüft werden. Excel Hilfe:

https://support.microsoft.com/de-de/offi...fa9564f999

[Egon12]

Hallo,
 
du hast ja die Nutzung von Funktionen mit Zugriff auf nur diesem einen Tabellenblatt "Sheet2" gezeigt um an deine gewünschten Ergebnisse zu kommen. Dies für sich betrachtet sollte eigentlich keine größeren Performanceprobleme bereiten.
 
Je nachdem was sich auf den einzelnen Tabellenblättern an Formeln und bedingten Formatierungen noch so abspielt, wäre es wahrscheinlich sinnvoller zu prüfen, wenn es zu störenden Laufzeiten bei Änderungen kommt dann eher zu passenderen Mitteln oder besser Wegen der Nutzung zu greifen.
 
Darüber lohnt es sich vielleicht mal Gedanken zu machen - die da wären:
 
- Sind 40 Tabellenblätter wirklich erforderlich.
- sind rechenintensive Vorgänge nicht vielleicht besser via PQ und das Auslösen per VBA-Ereignis auszuführen.
- Umfangreiche Such- / Filterausgaben durch alle Blätter (so überhaupt erforderlich) vielleicht besser per VBA via Array oder Recordset ausführen.
 
VBA muss natürlich am Einsatzort überhaupt ausführbar sein. Ebenso verhält es sich mit erweiterter Funktionalität PQ wo in der Firewall erweiterte Freigaben erforderlich sind.

Sorry, verstehe das bitte nicht als Vorhaltung oder Besserwisserei. Das sind so meine Gedanken, wenn ich das in #1 lese.

 
Gruß Uwe

[DDFonline]

Hallo,

danke für Eure hilfreichen Tips. Ich werde bei der ein oder anderen Sache noch einmal in mich gehen.

Viele Grüße
Dirk

[EA1950]

Hallo,

hast du den in Excel integrierten Befehl "Leistung prüfen" schon ausprobiert?

Menüband > Tabulator "Überprüfen" > Gruppe "Leistung" > Button "Leistung prüfen"

Vielleicht bewirkt dieser auch eine gewisse Optimierung.