[sverweis] ziehen der funktion

[klm123]

Hallo Leute, kennt jemand eine Möglichkeit zum Ziehen der sverweis Funktion über viele Spalten. Hintergrund ist ein Datenabgleich eine Artikeldatenbank für eine Firma.
Die Funktion sieht folgendermaßen aus:

=SVERWEIS(aaa!A2;bbb!$A$2:$ED$76883;1;FALSCH)

Wenn ich die Zelle ziehe, ändert sich nur im ersten Argument der Buchstabe. jedoch soll dieser pro Zeile erhalten bleiben. Das bedeutet ich kann auch keine Dollarzeichen nutzen ($A$2) nutzen, da dadurch ja so in den Zeilen 3-50000 immer A2 stehen bleibt.

Der nächste Punkt ist die Änderung des Spaltenindexes (1). Dieser ändert sich nicht wenn ich die Formel ziehe.

Kann mir hier bitte jemand helfen?

[BoskoBiati]

Hallo,


natürlich kannst Du Dollarzeichen benutzen:

=SVERWEIS(aaa!$A2;bbb!$A$2:$ED$76883;Spalte(a1);FALSCH)

[klm123]

Fetzt, danke.

[RPP63]

Fetzt

Moin!
Ich würde eher von funktionieren als von fetzen sprechen.
Bei fast 80.000 Zeilen und "vielen" (nämlich 134) Spalten in der Verweistabelle ist diese Lösung suboptimal und eher ein Stresstest für den Prozessor.

Wenn Spalte A in bbb! aufsteigend sortiert wäre, könntest Du das vierte Argument des SVerweises auf WAHR setzen, rechnet ~40.000mal schneller!

Falls dies aus unerfindlichen Gründen nicht möglich ist, solltest Du eine Hilfsspalte in Erwägung ziehen: =VERGLEICH(A2;bbb!A:A;0)
Die 134 Spalten klapperst Du dann mit Index() ab: =INDEX(bbb!B:B);Hilfszelle mit fixierter Spalte)
Diese Formel kannst Du ebenfalls bequem ziehen.
Es muss dann nicht 134mal die gleiche Zeilennummer errechnet werden, sondern nur einmal pro Zeile.
INDEX() ist dann wieder pfeilschnell.

Gruß und two Cents
Ralf

[Ego]

Hallo k...,

bei der Suche von 50.000 Objekten in 80.000 Zeilen sollte eine VBA-Lösung schneller sein.

@ Ralf
Seit  wann sind 80.000/15 ~40.000?

[RPP63]

Moin Helmut!

bei der Suche von 50.000 Objekten in 80.000 Zeilen sollte eine VBA-Lösung schneller sein.

Das glaube ich Dir nicht.
Schließlich würde ich in VBA exakt so vorgehen wie mittels Formeln in der Datei:

• Entweder Sortieren der Suchspalte und dann .Match(,,1)
  
• oder .Match(,,0) und Zuweisen der Werte per Index
  

Seit  wann sind 80.000/15 ~40.000?

Ich gestehe, dass ich den Durchschnittswert der Binärsuche jetzt nicht errechnet habe. 
Ich schrieb ja auch von ungefähr … ;)
Ändert aber erst mal nix am Geschwindigkeitvorteil.
(Im Sortieren (was ja nur einmal durchgeführt werden braucht) ist Excel jedenfalls extrem schnell.

Gruß Ralf

[Ego]

Hallo Ralf,

Vorab: Sverweis mit 4.Parameter "wahr" oder .MATCH(,,1) liefern nur dann die gleichen Ergebnisse wie Sverweis mit 4.Parameter "falsch", wenn sichergestellt ist, dass alle 50.000 gesuchten Objekte in den 80.000 Zeilen vorhanden sind.
Ansonsten liefert "falsch" einen Fehler und "wahr" den nächst kleineren Wert.
Dann wäre aber der Faktor ~ 40.000/14.

zu:

Das glaube ich Dir nicht.

Wenn du da genau so sicher bist wie bei den 40.000 bin ich beruhigt.  

zu:

Schließlich würde ich in VBA exakt so vorgehen wie mittels Formeln in der Datei:
Entweder Sortieren der Suchspalte und dann .Match(,,1)

Nein.
Falls die 50.000 Objekte wirklich alle vorhanden sind, benötigt .Match(..1) für eine Spalte 50.000*14 = 700.000 Vergleiche.
Bei sortierten Suchspalten würde das Programm die beiden Arrays parallel durchlaufen und 80.000 Vergleiche benötigen.

zu:

Schließlich würde ich in VBA exakt so vorgehen wie mittels Formeln in der Datei:
oder .Match(,,0) und Zuweisen der Werte per Index

Nein.
Hier wären es für .Match(,,0) 50.000 *40.000 = 2.000.000.000 Vergleiche.
Ein Programm würde 80.000 Zeilennummern in ein Dictionary schreiben und davon 50.000 auslesen.
Das sollte mehr als 100 Mal so schnell wie die Formellösung sein.

[RPP63]

Das sollte mehr als 100 Mal so schnell wie die Formellösung sein.

Magst Du mir das an einem Beispiel verdeutlichen, Helmut?
(Hab leider mom. keine Zeit dazu)
Ich habe schon die tollsten Verrenkungen mit Funktionen aus einer Runtime gesehen;
plain Excel - umgesetzt in VBA - war meist besser dran.

Gruß Ralf

[lupo1]

Bei 2^20 sortierten Zeilen benötigt die Binärsuche maximal (aber in der Hälfte aller Fälle, nämlich bei allen ungeraden Positionen!) 20 Prüfungen mit ungenauem Ergebnis (wobei eigentlich ein weiteres Argument/ein weiterer Argumentwert in VERGLEICH ermöglichen sollte, entweder die Treffer-Nummer oder #NV zurückzugeben). Durchschnittlich sind es =SUMMENPRODUKT(2^(20-ZEILE(A1:A20));21-ZEILE(A1:A20))/(2^20-1) = 19 Prüfungen.

Sind sie unsortiert oder sortiert, benötigt die vollständige Suche durchschnittlich 2^19 Prüfungen.

Der Unterschied beträgt also 1 zu 2^19/19 = 27594 beim Suchen pro Vorgang.

Die Aufrüstung der Binärsuche ist nun preiswert:

D1: =WENN(SVERWEIS(A1;B:B;1)=A1;VERGLEICH(A1;B:B);"kein Exakttreffer")

Es wird einfach noch einmal 19mal geprüft (schade und unnötig, aber na ja). Das o.g. Verhältnis vermindert sich auf 1:13797.

Mit Hilfszelle bleibt es aber bei 1:27594:

C1: =VERGLEICH(A1;B:B)
D1: =WENN(INDEX(B:B;C1)=A1;C1;"kein Exakttreffer")

Leider ist mir noch kein Umbau von WENNFEHLER für diese Frage gelungen. Das geht auch nicht, denn die beiden Verweis-Funktionen geben unterschiedliche Informationen zurück. Es wäre also an Microsoft ...

[Ego]

Hallo Ralf,

ich habe die Geschwindigkeit der Indexfunktion total unterschätzt. (Meine Vorstellung war, dass die Indexfunktion mindestens so lange wie das Wegschreiben des Arrays dauert.)

Bei unsortierten Listen benötigt das Programm auf meinem Rechner ca 12 Sekunden auf einem Prozessorkern. Dein Vorschlag benötigt ca 140 Sekunden auf 4 Prozessorkernen.
Der Vorteil ist also nicht vom Faktor 100 sondern nur 40.
Ausserdem ist die Zeit für das Schreiben des Arrays in die Tabelle stärker von der Länge der Texte abhängig als die Indexfunktion. So dass bei längeren Texten auch der Faktor 8 vorkam.
Die Nutzung des Sverweises für alle Spalten dauerte ca. 130 Sekunden pro Spalte.

Wenn man Lupos Idee nutzt die binäre Suche auch einzusetzen, wenn nicht alle gesuchten Einträge vorhanden sind, dauert die Berechnung der Formeln ca 1 Sekunde auf 4 Prozessorkernen und kann von einem VBA-Programm nicht geschlagen werden, da schon das Wegschreiben des Arrays 6 Sekunden benötigt.