Office: (Office 2016) SVERWEIS mit INDIREKT ersetzen durch INDEX VERGLEICH INDIREKT

Hallo zusammen,

bitte schlagt mich nicht für die Frage, Danke.

Ich habe eine große Tabelle, ca 50000 mit Formeln gefüllt Zellen in einem Tabellenblatt. Diese Formeln fragen eine externe Tabelle mit Werten ab.

Durch den SVERWEIS gibt es große Performance Probleme. Zumindest habe ich gelesen, das der SVERWEIS die Abfrage sehr verlangsamt. INDEX VERGLEICH INDIREKT soll hier schneller sein. Bitte korrigiert mich, falls das nicht stimmt.

Anbei der SVERWEIS: WENNFEHLER(SVERWEIS($C11;(INDIREKT("'"&$A$3&"'!$B:$GH"));SPALTE(E1);FALSCH);0)

A3 bei Indirekt ist der gesamte Bezug zum Tabellenblatt der externen Tabelle. Dieser kann über Dropdownfelder bestimmt werden.

Nun bekomme ich nach etlichem Probieren die entsprechende Formel für INDEX VERGLEICH INDIREKT nicht hin.

Vielleicht bin ich auch auf dem Holzweg und das geht überhaupt nicht.

Eine Beispieltabelle kann ich leider nicht anhängen, die Daten sind zu sensibel. Wenn es unbedingt von Nöten ist, muss ich extra eine aufbauen dafür, was es mir natürlich wert ist für Hilfe.

Vielen Dank

:)

 

Zu Deiner Datei kann ich nichts schreiben, was wohl daran liegt das ich nicht auf fremde Rechner schaue. Ich baue keine Datei nach.

Sollte die Datei verlinkt werden?

Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender*, UserForm* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.

Das ist nur meine Meinung zu dem Thema.


Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
http://www.ms-office-forum.de/forum/...d.php?t=322895
änderrn.

Das ist nur meine Meinung zu dem Thema.


Bitte berücksichtige das Themen die Erledigt sind als Erledigt markiert werden können. Also mache es unten links mit Klick auf den Schalter "als erledigt setzen", falls Problem gelöst.
Der Zustand des Beitrages wird dann in der Übersicht angezeigt und man braucht sich diese Beiträge nicht mehr ansehen.





Meine Homepage Homeseite - Hajo's Excelseiten

 

Moin,

eine Beispieldatei wäre hilfreich. Geht es nur um Zahlenwerte? Dann könnte Summewenn vielleicht schneller sein. Generell gilt aber: 50.000 Lookups dauern eben solange, wie 50.000 Lookups dauern. Wenn du zusätzlich Indirekt (eine sog. volatile Funktion, die bei jedem Pups neuberechnet wird) einsetzt, sind das eben 50.000 Lookups für jede beliebige Zelländerung. Die Umstellung auf Index und Vergleich wird dir daher wahrscheinlich keinen nennenswerten Zeitvorteil bringen.

Viele Grüße
derHöpp

 

Es sind reine Zahlenwerte. Ich hole aus einer externen Tabelle, um diese Werte dann weiter zu verarbeiten. Da es unterschiedliche Tabellen gibt, die diese Werte nutzen, nutze ich eine Exeldatei wie eine Datenbank.

Die Antwort war hilfreich, danke. Dann wenn das keinen signifikanten Vorteil in der Geschwindigkeit gibt, ist es die Mühe nicht wert, das alles umzubauen.

Wäre interessant gewesen, wie die Formel die ich gepostet habe, aufbereitet ausgesehen hätte, um evtl. einen Geschwindigkeitsvorteil zu gewinnen.

 

Hi nochmal,

es ist natürlich etwas schwierig aus einer Formel ohne Beispieldatei eine andere Formel zu fertigen, aber ich versuchs mal (einfach runtergetippt, ohne Test):

Mit Index(Match()):
Code:

Mit Summewenn:

Code:

=SUMMEWENN(INDIREKT("'"&$A$3&"'!$B:$B");$C11;INDEX(INDIREKT("'"&$A$3&"'!$B:$GH");0;SPALTE(E1)))
Jetzt, wo ichs so sehe, könnte man vermuten, dass der SVERWEIS sogar die schnellste Variante ist, weil INDIREKT nur einmal vorkommt.

Viele Grüße
derHöpp

 

Hallo,

vermutlich könntest du die externe Tabelle mit Power Query importieren und dann auf INDIREKT verzichten.

 

Mit Summewenn:

Code:

=SUMMEWENN(INDIREKT("'"&$A$3&"'!$B:$B");$C11;INDEX(INDIREKT("'"&$A$3&"'!$B:$GH");0;SPALTE(E1)))
Jetzt, wo ichs so sehe, könnte man vermuten, dass der SVERWEIS sogar die schnellste Variante ist, weil INDIREKT nur einmal vorkommt.

Viele Grüße
derHöpp

Guten Morgen,

vielen Dank für die Hilfe. Ich werde es jetzt testweise mal ausprobieren. Der Hinweis zu mehreren INDIREKT, das es das weiter verlangsamt, leuchtet mir ein.

[quote] von [b]WS-53[/b]  

Hallo,

vermutlich könntest du die externe Tabelle  mit Power  Query  importieren und dann auf INDIREKT verzichten.

[/quote]Guten Morgen,

die Idee ist gut. Ich habe aber leider das Problem, dass es jeden Tag eine neue Datei ist. Immer gleich aufgebaut. Aber aus Gründen der Archivierung, müssen wir jeden Tag eine neue Datei anlegen. Und genau dazu habe ich keine Lösung gefunden, daher hatte ich Power Query verworfen.

 

Hallo,

wenn du mit Power Query aus einem Ordner imortierst, kannst du als einen Schritt den Ordnerinhalt nach Datum absteigend sortieren und dann immer auf die Erste Datei filtern, um somit jeden Tag die neueste zu importieren.

 

Das wäre jetzt mal der nächste Schritt den ich probiere. Ein Umbau der Formeln hat eher zu einer Verschlechterung der Geschwindigkeit geführt. Ob der Rechner weiter so in die Knie geht bei den Berechnugen prüfe ich noch.

Erstmal Danke an alle bis hier her für die Hilfe.