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

"SVERWEIS" oder "VERGLEICH"

"SVERWEIS" oder "VERGLEICH"
28.06.2020 17:11:19
Martin
Hallo,
meine (hoffentlich einfache) Frage richtet sich an die Formelexperten:
Ich habe eine Tabelle mit extrem vielen SVERWEIS-Abfragen. Im Forenarchiv bin ich bereits auf einen sehr interessanten Beitrag zum Thema Rechenperformance gestoßen:
https://www.herber.de/forum/archiv/1332to1336/1335449_Welche_Formel_verbraucht_wie_viel_Rechenpower.html
Mein Frage: Was verlangt weniger Systemressourcen bei der Abfrage, ob ein gesuchte Wert vorhanden ist:
=WENN(SVERWEIS(J7;Datenquelle;1)=J7;WENN(SVERWEIS(J7;Datenquelle;2)=0;"";SVERWEIS(J7; Datenquelle;2));"") 
Oder:
=WENN(ISTNV(VERGLEICH(J32;Daten!A:A;1));"";WENN(SVERWEIS(J7;Datenquelle;2)=0;"";SVERWEIS(J7; Datenquelle;2))) 
Der Bereich "Datenquelle" im Tabellenblatt "Daten" ist aufsteigend nach dem Suchwert sortiert.
Gruß,
Martin

37
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: "SVERWEIS" oder "VERGLEICH"
28.06.2020 17:33:52
Daniel
Hi
die zweite Methode ist hier falsch programmiert, die Formel müsste lauten: IstNV(Vergleich(J32;Datenquelle;0).
den #NV-Fehler bekommst du in der Regel nur, wenn du die Suchmethode der genauen Übereinstimmung für unsortierte Daten verwendest (im Vergleich 3. Parameter = 0, im SVerweis 4. Parameter = FALSCH), und diese ist bei unsortierten Daten im statistischen Mittel erheblich langsamer als die Suche in sortierten Daten (welche allerdings keinen Fehler ausgibt, wenn der Suchbegriff nicht gefunden wird, sondern den nächst kleinern Wert als Ergebnis verwendet)
SVerweis und Vergleich verwenden die gleichen Suchalgorythmen, dh es macht keinen Unterschied, ob du SVerweis oder Vergleich verwendest.
Entscheidend ist, ob die Daten sortiert sind und die Suchmethode für sortierte Listen verwendet wird.
im Prinzip gilt folgendes:
wenn die Datenmenge mit x = 2^n beschrieben werden kann, dann ist die Suchzeit bei Suche für genaue Übereinstimmung im statistischen Mittel proportional x/2, die Suchzeit für die Suche in sortierten Daten ist proportional n.
(statistisches Mittel heißt: durchschnittliche Suchzeit, wenn jeder Listenwert einmal gesucht wird, bei der Suche in Unsortierten Daten wird die Liste von oben nach unten durchsucht, je später der gesuchte Wert in der Liste auftaucht, um so länger dauert die Suche)
Gruß Daniel
Anzeige
AW: "SVERWEIS" oder "VERGLEICH"
28.06.2020 17:55:56
Martin
Hallo Daniel,
bist du derselbe Daniel, der hier schon vor 7 Jahren geantwortet hat?
https://www.herber.de/forum/archiv/1332to1336/1335449_Welche_Formel_verbraucht_wie_viel_Rechenpower.html#1335473
Ich danke dir für deine Antwort und den Hinweis mit dem falschen Vergleichstyp-Argument. Bei der Formel mit dem Vergleich war mir beim Übertragen auch noch ein anderer Fehler unterlaufen, denn da hätte statt "J32" eigentlich "J7" stehen müssen.
Hab vielen Dank für deine Unterstützung!
Viele Grüße
Martin
Anzeige
Suchalgorithmen & Prinzip
28.06.2020 19:23:36
Luc:?
1. Ich glaube nicht, dass SVERWEIS und VERGLEICH die gleichen SuchAlgorithmen verwenden, Daniel,
höchstens ähnliche, denn immerhin hat VERGLEICH 3 Möglichkeiten im 3.Argument und SVERWEIS nur 2 im 4. Das liest sich in der Xl-Hilfe* dann auch etwas anders. Das könnte dann eher bei VERWEIS so sein.
* 1 oder fehlt: VERGLEICH sucht nach dem größten Wert, der kleiner oder gleich dem Wert für Suchkriterium ist. Die Werte im Argument Suchmatrix müssen in aufsteigender Reihenfolge angeordnet sein, z. B. ...-2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR.
0: VERGLEICH sucht nach dem ersten Wert, der mit dem Wert für Suchkriterium genau übereinstimmt. Die Werte im Argument Suchmatrix dürfen in beliebiger Reihenfolge angeordnet sein.
-1: VERGLEICH sucht nach dem kleinsten Wert, der größer oder gleich dem Wert für Suchkriterium ist. Die Werte im Argument Suchmatrix müssen in absteigender Reihenfolge angeordnet sein, z. B. WAHR, FALSCH, Z-A,...2, 1, 0, -1, -2,... usw.
SVERWEIS: Wenn Bereich_Verweis entweder WAHR oder nicht belegt ist, wird eine genaue oder ungefähre Entsprechung zurückgegeben. Wird keine genaue Entsprechung gefunden, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist.
Wichtig:  Wenn Bereich_Verweis entweder WAHR oder nicht belegt ist, müssen die Werte in der ersten Spalte von Matrix in aufsteigender Sortierreihenfolge geordnet sein, andernfalls gibt SVERWEIS möglicherweise nicht den richtigen Wert zurück.
Wenn Bereich_Verweis FALSCH ist, müssen die Werte in der ersten Spalte von Matrix nicht sortiert sein.
Ist das Argument Bereich_Verweis mit FALSCH angegeben, kann SVERWEIS nur nach einer genauen Entsprechung suchen. Wenn in der ersten Spalte von Matrix mindestens zwei Werte vorhanden sind, die dem Suchkriterium entsprechen, wird der erste gefundene Wert verwendet. Wird keine genaue Entsprechung gefunden, wird der Fehlerwert #NV zurückgegeben.
Aber VERWEIS: Wichtig: Die Werte für Suchvektor müssen in aufsteigender Reihenfolge angeordnet sein: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR. Andernfalls gibt VERWEIS möglicherweise einen falschen Wert zurück. Bei Zeichenfolgen (Text) wird nicht zwischen Groß- und Kleinbuchstaben unterschieden.
Kann die VERWEIS-Funktion keinen Wert finden, der mit dem jeweiligen Wert von Suchkriterium übereinstimmt, verwendet die Funktion den größten Wert in Suchvektor, der kleiner oder gleich dem Wert von Suchkriterium ist.
Ist der Wert von Suchkriterium kleiner als der kleinste Wert in Suchvektor, gibt VERWEIS den Fehlerwert #NV zurück.

2. Ich glaube auch nicht, dass Xl einen linearen SuchAlgorithmus verwendet, der deine Aussage, …wird die Liste von oben nach unten durchsucht, je später der gesuchte Wert in der Liste auftaucht, um so länger dauert die Suche, bestätigen würde. Sortieren und Suchen dürften ähnlich schnelle und effiziente Algorithmen verwenden, die die SuchZeit allein von der DatenMenge und nicht von der (Erst-)Position des (gefundenen) SuchBegriffs abhängig machen.
Gruß, Luc :-?
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon
Nichtsdestotrotz Durchblick verbessern mit …

Anzeige
@Luc: Was ist dein Vorschlag?
28.06.2020 19:55:01
Martin
Hallo Luc,
wenn du davon ausgehst, dass Excel bei SVERWEIS keinen linearen Suchalgorithmus verwendet, dann ist es wohl doch egal ob man eine sortierte Quelldatenliste hat und beim letzten Argument (Bereich_Verweis) "Wahr" verwendet oder eine unsortierte Quelldatenliste hat und "Falsch" beim Argument Bereich_Verweis einsetzt?
Dann wäre ich jetzt wieder bei meiner Ausgangsfrage, ob es zwischen VERGLEICH und SVERWEIS irgendwelche Performance-Unterschiede gibt.
Gruß, Martin
Nein, das ist schon ein Unterschied, ...
29.06.2020 00:44:45
Luc:?
…Martin,
sonst gäbe es diese unterschiedlichen Optionen ja nicht. Nur sind sie bei VERWEIS von vornherein beidseitig festgelegt (kleinste Differenz zum SuchBegriff), während sich die beiden anderen Verweise nur auf keine oder aufsteigende Sortierung beziehen. Nur bei VERGLEICH kann man noch die SortierRichtung angeben.
Die Daten wdn mit Sicherheit wie beim Sortieren aufgeteilt, denn dadurch wird eine hohe Geschwindigkeit erreicht. Sind die Daten unsortiert, muss jede dieser DatenGruppen (-Sequenzen) durchgegangen wdn bis der Wert gefunden wird. Dabei haben die Gruppen mit den niedrigeren Adressen Priorität. Sind die Daten aber sortiert, fallen schon bei Gruppenbildung alle Gruppen weg, deren 1.Wert größer (bzw kleiner als der SuchBegriff ist (es müsste nur deren 1.Wert temporär gemerkt wdn). Dass das dann schneller ist, liegt wohl auf der Hand. Allerdings müsstest du zuerst ggf noch sortieren und das kostet ja auch eine gewisse Zeit und ist natürlich dann besonders sinnvoll, wenn das ohnehin so gewünscht ist u/o auf die Daten oft auf diese Weise zugegriffen wdn muss.
Morhn, Luc :-?
Anzeige
Ergänzung:
29.06.2020 00:57:07
Luc:?
Bei VERGLEICH muss bei sortierten Daten natürlich der 1. bzw letzte Wert einer ansonsten entfallenden DatenSequenz temporär gemerkt wdn - je nach SortierRichtung.
Luc :-?
AW: Ergänzung:
29.06.2020 10:33:23
Martin
Hallo Luc,
meine Daten werden aus einer externen CSV-Datei importiert und sind zufällig bereits nach der Spalte für das Suchkriterium aufsteigend sortiert. Dann war Daniels Ratschlag also richtig (auch wenn die Begründung vielleicht nicht ganz korrekt war) und es lohnt sich die verwendeten Formeln umzustellen.
Hab auch du vielen Dank!
Gruß,
Martin
AW: Suchalgorithmen & Prinzip
28.06.2020 20:04:40
Daniel
Glauben kann man viel Luc.
Zu frage 1)
Ich glaube zum Beispiel nicht, dass jemand für die selbe Aufgabenstellung zwei unterschiedliche Methoden entwickelt und verwendet. Das ergibt keinen Sinn und wäre nur Verschwendung von Ressourcen.
Vergleich hat ja nur die zusätzliche Option, dass die Daten auch absteigend sortiert sein dürfen, aber das macht ja vom Prinzip keinen unterschied, man muss nur einmal ein größer gegen ein kleiner tauschen.
Zu 2)
Auch da muss man nichts glauben Luc, diese Theorie das kann man mit einem einfachen Experiment (Laufzeitmessung) selber überprüfen (zumindest für die Suche mit genauer Übereinstimmung)
Das wäre dann wissenschaftliches Arbeiten.
Gruß Daniel
Anzeige
Aber warum vermutest du dann einen linearen ...
29.06.2020 00:27:12
Luc:?
…Vgl bei unsortierten Daten, Daniel,
so dass die Laufzeit auch noch von der Position abhängig wäre, wenn du doch davon ausgehst, dass es rationeller wäre, stets den gleichen Algorithmus (ggf mit einigen zusätzlichen spezifischen Operationen) zu verwenden. Hast du die Laufzeiten gemessen? Und kannst du das Ergebnis von der reinen DatenMenge abstrahieren? Hast du nicht oft genug betont, dass die Xl-Sortierung (und letztlich damit auch ein Vgl) sehr schnell ist‽ Und das ändert auch nichts daran, dass ein prinzipiell genauso organisierter Vgl bei sortierten Daten wesentlich schneller wäre, weil von vornherein ganze Sequenzen entfallen würden.
Ich habe diverse SortierMethoden getestet (und nachpgmmiert) und bei einer Methode, die linear vorgeht, würde man bei einer größeren DatenMenge einen sehr deutlichen LaufzeitUnterschied zu rationelleren Methoden bemerken.
Und bei 1. ging es tatsächlich darum, dass ähnlich wie bei VERWEIS bei VERGLEICH nach beiden Seiten vgln wird, nur kann man das bei Letzterem festlegen, während die beiden anderen Verweise im Sortiert-Fall auf aufsteigende Sortierung orientiert sind. Das GesamtPgm dürfte dann wohl etwas anders aussehen.
Hättest du dir darüber vor dem Schreiben Gedanken gemacht, hätte ich dazu nichts mehr schreiben müssen. Aber so hielt ich denn doch eine Präzisierung für erforderlich (machst du ja auch gern :->). Und „glauben“ heißt in diesem Fall, dass es sich um eine (Hypo-)These handelt, die logischen Schlüssen und den HilfeTexten entspringt. Falsifizieren kann man die wohl nur, wenn man die OriginalPgmm kennt oder wenigstens bei µS eine weiterführende Beschreibung findet. Also kein Grund so herablassend zu antworten, denn das weißt du unter Garantie nicht… :->
Luc :-?
Anzeige
AW: Aber warum vermutest du dann einen linearen ...
29.06.2020 01:38:19
Daniel
Laufzeiten habe ich gemessen Luc, du auch?
Wenn du recht hättest, dann müsste ein:
SVerweis(A1;A1:A2;1;Falsch) schneller berechnet werden als ein SVerweis(A1;A1:A1000000;1;Falsch).
Werden beide gleich schnell berechnet, dann habe ich recht.
Zusätz prüft man dann noch die Rechenzeiten von ein paar zwischenwerten: SVerweis(A500000;A1:A1000000;1;Falsch), SVerweis(A1000000;A1:A1000000;1;Falsch) und trägt diese in ein Diagramm ein , auf der X-Achse die Position, auf der Y-Achse die Rechenzeit. Datenmenge ist ja immer die gleiche.
Anschließend wiederholt man das Spiel mit sortierten Daten und der Variante 4. Parameter = Wahr und dem Vergleich in allen Varianten. Dann hat man ein recht gutes Bild von dem, was da intern passiert.
Was das sortieren angeht, ja im Sortieren ist Excel schnell, aber der Vorteil kommt ja hauptsächlich dadurch zustande, dass man für 10.000 SVerweise nur 1x sortiert und nicht 10.000x so es getan werden müsste, wenn das Sortieren im SVerweis integriert wäre.
Damit sich das lohnt, müsste das Sortieren deutlich schneller sein, als das einmalige Suchen in der unsortierten Liste.
Außerdem: welchen Sinn würde es machen, für Vergleich und SVerweis unterschiedliche Suchmethoden zu entwickeln?
Die Aufgabenstellung und die Datenbasis ist die für beide Funktionen die gleiche. Wenn man unterschiedliche Methoden verwenden würde, hätte man nicht nur den doppelten Test- und Dokumentationsaufwand, sondern man müsste auch noch absichern, dass beide Funktionen unter allen Umständen das gleiche Ergebnis liefern.
Anzeige
Dann teste doch vielleicht mal folgendes:
29.06.2020 07:41:06
lupo1
=SVERWEIS(500000;A$1:A$999999;1;0)
und
=SVERWEIS(500000;A1:A999999;1;0)
10000mal herunterkopiert. Bei der ersten Formel nimmst Du also an, dass A$1:A$999999 von Excel nur einmal im Hintergrund sortiert, und 10000mal verwendet wird.
Bei der zweiten Formel könnte man nun folgende Umstände vermuten:
a) kein Hintergrundsortieren (wahrscheinlich)
b) ein Vorsortieren von A1:A10009998 (ziemlich unwahrscheinlich)
Vielleicht kannst Du dazu ein Ergebnis mitteilen?
AW: Dann teste doch vielleicht mal folgendes:
29.06.2020 10:09:18
Daniel
Hi
wenn du =SVERWEIS(500000;A1:A999999;1;0) nach unten kopierst, verschiebt sich der durchsuchte Zellbereich als ganzes mit jeder Formel und je weiter unten die Formel steht, um so früher wird der Suchwert gefunden.
Gruß Daniel
Anzeige
Eigentlich habe ich verständlich formuliert.
29.06.2020 10:17:37
lupo1
und ich habe verständlich geantwortet
29.06.2020 10:38:06
Daniel
Nein. Du hast zusammenhanglos geredet.
29.06.2020 10:59:18
lupo1
Ich denke, dem wird jeder zustimmen.
AW: Nein. Du hast zusammenhanglos geredet.
29.06.2020 11:04:18
Daniel
da wird jeder zustimmen, dem die geisteigen Fähigkeiten oder das notwendige Excelwissen fehlen, um die Zusammenhänge zu verstehen.
Dann wollen wir den Excel-Meister nicht stören
29.06.2020 11:19:01
lupo1
Dann hast du mich missverstanden, ...
29.06.2020 18:07:30
Luc:?
…Daniel,
nicht die Methodik ist unterschiedlich, also der GrundAlgorithmus, sondern die Ausführung, weil die Kriterien für die ErgebnisWiedergabe unterschiedlich sind. Das kann, je nach Ziel, auch etwas zusätzliche Rechenzeit bedeuten. Letztlich ergeben sich so dann tatsächlich unterschiedliche Pgmm, wobei es ja auch mehrere schnelle Methoden gibt, die, je nach Ziel und ggf DatenMenge, eingesetzt wdn können. Das würde dann schon einen MethodenUnterschied ala Hansueli bedeuten.
Und sicher wird bei deinem Bsp der eigentl Vgl für 2 Werte schneller ausgeführt als für 1 Mio. Aber weshalb hättest du bei (annähernd) gleicher Geschwindigkeit recht? Hattest du nicht behauptet, dass es länger dauert, wenn der gesuchte Wert erst gg Ende des Bereichs gefunden wird als gg Anfang‽ Und gerade das muss nicht sein, wenn schnelle Methoden verwendet wdn, die den GesamtBereich aufteilen und alle Teile parallel durchsuchen. Die MessUnterschiede dürften dann so minimal sein, dass sie mit herkömmlichen (VBA-)Methoden kaum feststellbar wären. Bei linearem Vgl innerhalb nur eines (ggf unsortierten) Bereichs, wovon du ja ausgingst, gäbe es aber deutlichere Unterschiede. Meine Haltung dazu war ja, dass nicht die Position des gefundenen Wertes entscheidend ist, sondern die DatenMenge insgesamt, obwohl das wohl auch nur bei sehr großen DatenMengen auffällig wdn könnte.
Ein Sortieren dauert bei prinzipiell gleicher Methodik natürlich etwas länger, da uU viele Daten gelesen und geschrieben wdn müssen, während sonst nur vgln, ggf gemerkt und letztlich ein Wert ausgegeben wdn muss.
Und was das evtl Vorsortieren betrifft, das könnte natürlich sein, aber es wäre ggf mehrfacher Aufwand. Es ist auch fraglich, ob die FmlOptimierung durch den FmlText-Interpreter soweit geht, einen 1× vorsortierten Bereich in allen gleichartigen Fmln zu verwenden, aber eben auch möglich. Dazu lässt sich µS in der Xl-Hilfe nicht aus. Nur eines scheint naheliegend, die Kombi von INDEX mit VERGLEICH dürfte etwas langsamer sein als S~/W~/VERWEIS.
Algorithmen wie zB der von QuickSort zeigen dir, wie das gehen kann, in ihrer VBA-Umsetzung sogar ohne ParallelVerarbeitung.
Luc :-?
Anzeige
AW: Dann hast du mich missverstanden, ...
29.06.2020 18:38:13
Daniel
Ninja Luc, es geht nicht darum, wsd theoretisch sein könnte, sondern was tatsächlich ist.
für die Suche in unsortierten Daten mit genauer Übereinstimmung gilt folgendes, von mir im Versuch ermittelt:
1. SVerweis und Vergleich sind gleich schnell
2. Die Suchzeit hängt direkt proportional von der Position des Suchwertes in der Liste ab. Die Länge der Liste hat keinen signifikanten Einfluss auf die Suchzeit.
Ein früh positionierter Suchwert wird in einer langen Liste genauso schnell gefunden wie in einer kurzen Liste an gleicher Position.
3. die Suchzeit in Sortierten Listen ist so schnell, dass sich mit den durch VBA bereit gestellten Meßmethoden keine Laufzeitunterschiede durch die Datenmenge nachweisen lassen.
Interessanterweise war die unsortierten Suche c.a. 10% schneller, wenn die Liste sortiert war, was aber nichts daran änderte, dass die Suchzeit direkt proportional zur Position des Suchwertes innerhalb der Liste war.
Das sind die von mir ermittelten Fakten, aus denen ich meine Schlüsse ziehe.
Gruß Daniel
Nun ja, unter die Ninjas bin ich zwar noch ...
01.07.2020 00:27:31
Luc:?
…nicht gegangen, Daniel,
aber dein Test deutet ja auf Folgendes hin:
1. gleiche oder ähnlich schnelle Methodik, wobei bei ~VERWEISen ja noch die Auswahl aus einer Matrix oder einem (Ko-)Vektor hinzu kommt, während VERGLEICH ja nur einen Index (lfdNr) zurückgeben muss. Aber diese Operation fällt ja nur 1× an und ihr ZeitBedarf dürfte kaum messbar sein. Folglich sollte VERGLEICH sogar etwas schneller sein als ~VERWEIS, zumal die Kombi aus INDEX und VERGLEICH lt µS nur etwas langsamer sein soll als ~VERWEISe.
2. Dem würde entgegen kommen, was µS dazu schreibt:
…the calculation time for the function is proportional to the number of cells scanned before a match is found.
Aber a) ist hierbei nicht klar, ob sich das auf frühere Versionen bezog, denn zuvor schreibt µS:
In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact match on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range. Allerdings fiele dann wohl auch Xl14/2010 unter die früheren Versionen.
Und b) ist the number of cells scanned nicht unbedingt gleichbedeutend mit der Position des gesuchten Wertes innerhalb eines (Ko-)Vektors (vgl dazu auch meinen Beitrag unten).
Die von dir festgestellte direkte Proportionalität der Laufzeit zur Position der FundStelle in den Daten könnte also auch mit der verwendeten Xl-Version zusammenhängen oder damit, dass Xl generell (noch) nicht so vorgeht wie ich annahm. Aber es könnte sich auch um einen Trugschluss handeln, der sich aus speziellen Umständen herleitet. Insofern will ich dein Ergebnis und deine daraus gezogenen Schlüsse nicht bewerten. Eine umfassende Analyse wäre mir aber auch zu zeitaufwendig und ein eindeutiges und klärendes Ergebnis möglicherweise auch nicht erreichbar. Es kann also durchaus sein, dass deine Schlüsse richtig sind, aber sie müssen es nicht zweifelsfrei sein.
Luc :-?
AW: Nun ja, unter die Ninjas bin ich zwar noch ...
01.07.2020 01:52:11
Daniel
Naja da fällt auch 2016 unter "alte Version" bzw alles was nicht im Mietmodell ist.
Dass jede Performanceaussage immer nur für eine bestimmte Version gilt und sich dass mit jeder Weiterentwicklung des Programms ändern kann, sollte auch klar sein.
So war bis 2010 die Doppelte Schleife zum Transponieren von 2d-Arrays wesentlich schneller als WorksheetFunction.Transpose, ab 2013 ist die WorksheetFunction besser.
Und wenn man in einer Liste jeden Wert mit einem Schwert verglichen muss solange bis man einen Treffer hat, gibt es da eine bessere Reihenfolge als von oben nach unten?
Man braucht ja auch eine Methode, mit der verlässlich und für den Anwender logisch nachvollziehbar vorhergesagt werden kann, welcher wert verwendet wird wenn der Suchbegriff mehrfach in der Liste vorkommt.
Momentan wird ja immer der erste Treffer von oben als Ergebnis zurück gegeben, da wäre eine Suche von unten nach oben sinnlos, weil dann immer die vollständige Liste durchsucht werden muss, wenn trotzdem der erste Treffer als Ergebnis zurückgegeben werden soll.
Dann wäre aber die Suchzeit allein abhängig von der Datenmenge und müsste bei gleicher Datenmenge auch immer gleich sein, egal an welcher Position sich der Wert befindet.
Ein solches Verhalten hat man beim Zählen- und SummeWenn.
Da muss ja immer die komplette Liste durchsucht werden.
Deswegen ist IstNV(Vergleich) auch etwas schneller als ZählenWenn, wenn man prüfen will, ob ein Wert in einer Liste vorhanden ist oder nicht.
Das würde nun wieder Hansuelis Meinung ...
02.07.2020 23:09:44
Luc:?
…- unterschiedliche Algorithmen - stützen, Daniel,
aber, wenn man die Daten in Verarbeitungsblöcke (Threads) teilt und die parallel bearbeitet, wird's sehr schnell, wobei ja beim 1.Treffer pro Block Schluss ist, aber dann noch die Treffer vgln wdn müssen, wobei irrelevante entfallen. Dann wären ebenfalls vordere Positionen schneller gefunden, allerdings wäre es dann wohl mit der Proportionalität etwas komplizierter (pro Block) bzw uneindeutiger. Aber ob µS das wirklich so organisiert hat, weiß man ja nicht.
Die SuchRichtung kann auch bei BlockAufteilung (unsortiert) von oben nach unten gehen. Es geht aber auch anders (auch ohne Multi-Threading) wie man an schnellen SortierAlgorithmen sehen kann. Aber die müssen ja ohnehin alle Werte vgln, während beim Suchen eben nur der positionsniedrigste relevant ist, nicht irgendeiner, und dann abgebrochen wdn kann. Am zeitaufwendigsten wäre dann allerdings bei unsortierten Daten eine erfolglose Suche. Aber viell wird ja gar nicht gescannt, sondern eine einfache RechenOperation ausgeführt…
Luc :-?
AW: Das würde nun wieder Hansuelis Meinung ...
03.07.2020 00:35:04
LDaniel
Blockaufteilung macht die Sache nur dann schneller, wenn die Blöcke auch wirklich parallel durchsucht werden.
Wenn sich der gesuchte Wert nicht im ersten Block befindet, muss, kann die Suche nicht vorzeitig abgebrochen werden und die Suchzeit ist unabhängig von der Position in der Liste.
Nur wenn der Suchwert im ersten Block ist, kann vorzeitig abgebrochen werden.
Solange aber jede Funktion seperat berechnet werden muss weil jeder SVerweis eine andere Suchmatrix haben könnte, würde so eine Datenaufbereitung nur dann sinnvoll sein, wenn Datenaufbereitung+Suche schneller ist als die einfache Suche von oben nach unten.
Das halte ich aber eher für unwahrscheinlich.
So eine Datenaufbereitung kostet Zeit und ist nur dann sinnvoll, wenn sie für mehrere SVerweise mit der selben Suchmatrix verwendet werden kann und dann nur einmalig für alle gleichartigen SVerweise durchgeführt werden muss.
Sowas hab ich mir tatsächlich mal programmiert, um große Listen miteinander zu kombinieren, ohne sie sortieren zu müssen.
Als universelle Formel wäre sowas am einfachsten als Matrixfunktion realisierbar, bei der man auch die Suchbegriffe als Matrix eingibt, analog Häufigkeit, das ist ja auch sehr schnell.
Eigentlich auch nicht erstaunlich, denn ...
02.07.2020 09:58:15
lupo1
... Excel ist bestimmt als Rechentool das am häufigsten genutzte der Welt.
Es kann sich leisten, jegliche Daten auf nicht nur 10, sondern auch 50 oder sogar 1000 Arten zu bewerten und dann entsprechend optimal auszuwerten. Denn als Standardprogramm handelt es mit Formeln ja vorhersehbar (was es mit VBA natürlich nie kann, wenn dort nicht auch gerade Worksheet.Formulae verwendet werden).
Liegt eine ältere Excelversion vor, so ist das überhaupt nicht schlimm. Nur hat diese vermutlich noch nicht die 1000 Rechenarten innerlich zur Auswahl, sondern weniger, z.B. 200. Und ist deswegen c.p. langsamer.
Ein bisschen erinnert mich das an Fotoprogramme in Kameras, die schon seit ca. 40 Jahren bestimmte Bildaufteilungen erkennen und entsprechend belichten oder fokussieren.
AW: Nun ja, unter die Ninjas bin ich zwar noch ...
02.07.2020 10:21:52
Daniel
"Es kann also durchaus sein, dass deine Schlüsse richtig sind, aber sie müssen es nicht zweifelsfrei sein."
das gilt eigentlich für jede Theorie.
aber jede Theorie ist solange gültig, bis die widerlegt oder durch eine bessere ersetzt wird.
nur Zweifel und Vermutungen sind da nicht ausreichend (außer, man ist Esoteriker oder Verschwörungstheortiker ;-))
Naja, VT ist ja nun ein überstrapazierter ...
02.07.2020 23:45:46
Luc:?
…und allzu häufig politisch vernutzter (Un-)Begriff, Daniel,
der, auf ein breites Cluster aus allem Möglichen angewendet, eher durch Gleichsetzung tatsächliche Zusammenhänge verschleiern und Zweifelhaftes verkleistern soll. Und daran wirken leider auch etliche Spinner nach Kräften mit, ohne zu merken, dass sie den Teil ihrer Zweifel, der berechtigt ist, so konterkarieren und damit denen nutzen, denen sie berechtigt misstrauen (mal abgesehen von denen, denen sie unberechtigt misstrauen).
Aber darüber kann man sich stundenlang wundern oder aufregen, während hier bei Xl trotz aller Geheimniskrämerei doch 'ne ganze Menge einigermaßen klar und nachvollziehbar ist, auch wenn man immer noch einiges entdecken kann.
Jedenfalls hat mich das dann doch noch auf ein paar Ideen gebracht… ;-)
Luc :-?
AW: Optimizing performance obstructions
29.06.2020 11:08:37
EtoPHG
Hallo zusammen,
Aus dem Artikel Tips for optimizing performance obstructions kann man viel über die Performanz von xVERWEIS und VERGLEICH lernen.
...und wenn man genau liest, muss man zu Schluss kommen, das für jede Funktion eigene Algorithmen eingesetzt werden.
Gruess Hansueli
Das "Genaue" habe ich dort nicht gefunden ...
29.06.2020 12:39:35
lupo1
... und würde daher Daniel's Vermutung, dass es sich bei den Lookups um die gleiche Routine handelt, erst mal stützen.
Beispiel: "INDEX(VERGLEICH( ist 5% langsamer als SVERWEIS(". Die Differenz liegt dann am doppelten Funktionsaufruf.
Ja, über die eigentliche Methodik schweigt ...
30.06.2020 23:37:04
Luc:?
…sich µS aus, Lupo,
obwohl ja auch Hinweise für VBA-Entwickler dabei sind. Aber Hansueli hat insofern recht, dass die dortigen Hinweise schon recht nützlich sind, wenn man die Performance von Fmln und Fktt einschätzen will. Dabei wird u.a. auch die Behauptung entkräftet, dass es sich beim -- nur um eine modische Marotte handelt, die 1* gleichzusetzen wäre. Es löst in Wirklichkeit eine andere, einfachere RechenOperation aus, die darin besteht, ein Vorzeichen-Bit zu setzen, was eine TextZahl zu einer echten Zahl und einen Bereichsverweis auf Zahlwerte zu einem Datenfeld macht, wodurch die ObjektBezüge verloren gehen. Dass diese Operation dann etwas schneller ist als das Addieren von 0 oder Multiplizieren mit 1, liegt wohl auf der Hand. Und auch die beiden Notationsformen von SUMMENPRODUKT wdn entsprd eingeordnet, mit dem Ergebnis, dass die ;-getrennte Notation der Faktoren-Argumente schneller ist als die durch *-Verbindung aller Faktoren provozierte vorherige Berechnung, die mitunter aber unumgänglich ist.
Außerdem ist dem Text zu entnehmen, dass immer, wenn möglich, Multi-Threading angewendet wird. Manche Fkt und mancher Fml-Kontext lässt das nicht zu. Aber es ist anzunehmen, dass vornehmlich dadurch hohe Berechnungsgeschwindigkeiten erzielt wdn, was sicher auch für Sortierung und Vergleich gilt. Demzufolge spielt bei Vgln mit unsortierten Daten die Position des gefundenen Begriffs nur dann eine signifikante Rolle, wenn auch bei einer Aufteilung auf DatenGruppen (Verarbeitungs-Threads) die Datenmenge groß genug ist, dass sich hier ein messbarer Unterschied ergibt. LaufzeitUnterschiede dürften also von beiden Größen und wahrscheinlich auch noch von anderen Faktoren* abhängen.
* Das wären die eingesetzte Methodik, die Anzahl der ggf gebildeten Gruppen (Verarbeitungs-Threads) und deren daraus und der GesamtDatenMenge resultierende Größe, sowie die Anzahl der zusätzlich erforderlichen GruppenFundstellenVergleiche zwecks Ermittlung der relevanten FundStelle (ohne Fund wdn alle GruppenDaten vgln, anderenfalls ist der Vgl beim 1.Treffer für die jeweilige Gruppe beendet). Bei sortierten Daten entfallen natürlich von vornherein alle Gruppen, deren 1. bzw letzter Wert nach bzw vor dem SuchBegriff liegt, wobei es noch darauf ankommt, ob dieser (bei nichtvorhandenem SuchWert) evtl der nächstkleinere bzw -größere Wert ist.
Das Anwenden des Unsortiert-Kriteriums auf sortierte Daten würde nahezu den gleichen Aufwand erfordern, könnte aber trotzdem etwas schneller sein. Allerdings empfiehlt µS hier die 2fache Anwendung von ~VERWEISen, falls nach genauer Entsprechung des SuchBegriffs gesucht wdn soll, um die DatenMenge für den äußeren ~VERWEIS klein zu halten. Das ist dann immer noch deutlich schneller als mit Unsortiert-Kriterium.
Gruß, Luc :-?
AW: Ja, über die eigentliche Methodik schweigt ...
01.07.2020 13:31:43
Daniel
Luc schrieb
Dabei wird u.a. auch die Behauptung entkräftet, dass es sich beim -- nur um eine modische Marotte handelt, die 1* gleichzusetzen wäre. Es löst in Wirklichkeit eine andere, einfachere RechenOperation aus, die darin besteht, ein Vorzeichen-Bit zu setzen, was eine TextZahl zu einer echten Zahl und einen Bereichsverweis auf Zahlwerte zu einem Datenfeld macht, wodurch die ObjektBezüge verloren gehen. Dass diese Operation dann etwas schneller ist als das Addieren von 0 oder Multiplizieren mit 1, liegt wohl auf der Hand.
naja, auf der Hand liegt das für mich nicht. Das müsstest du schon detaillier Begründen.
In einem System, das eine bestimmte Anzahl von Bits gleichzeitig parallel verarbeitet (heute in der Regel 32 oder 64), lässt eine darunter liegende Bit-Anzahl noch keinen Rückschluss darüber zu, dass die jeweilige Aktion schneller ausgeführt wird (bspw wird der Type Byte mit 8 Bit langsamer verarbeitet als der Datentyp Long mit 32 Bit)
und auch wenn -- schneller ist als *1, der unterschied dürfte so gering sein, dass er bei für Excel sinnvollen Datenmengen nicht spürbar wird.
Das Anwenden des Unsortiert-Kriteriums auf sortierte Daten würde nahezu den gleichen Aufwand erfordern, könnte aber trotzdem etwas schneller sein.
das hatte mich ja verwundert, dass die unsortierte Suche schneller ist, wenn die Daten sortiert vorliegen, als wenn sie unsortiert vorliegen.
Allerdings empfiehlt µS hier die 2fache Anwendung von ~VERWEISen, falls nach genauer Entsprechung des SuchBegriffs gesucht wdn soll, um die DatenMenge für den äußeren ~VERWEIS klein zu halten.
nein, so ist das nicht. Die Datenmenge ist für beide Verweise gleich.
Microsoft empfielt, bei Sortierten Daten immer die Suche für sortierte Daten anzuwenden, auch wenn genaue Übereinstimmung gefordert ist.
Das liegt daran, dass hier die Regeln der binären Suche angwendet werden, welche erheblich schneller zum Ziel kommt als die lineare Suche, so dass man es sich leisten kann, die Liste 2x zu durchsuchen, im ersten Schritt wird nur geprüft, ob der Suchbegriff in der Liste vorkommt und wenn ja, wird dann die eigentliche Suche mit der Rückgabe des entsprechenden Spaltenwertes durchgeführt.
Es findet aber keine Reduzierung der Datenmenge für den 2. Verweis statt, dieser muss genauso viele Daten durchsuchen wie der erste Verweis.
Lies dir das nochmal genauer durch.
Gruß Daniel
Mich (verwundert das) nicht ...
02.07.2020 09:38:13
lupo1
Du schriebst:
das hatte mich ja verwundert, dass die unsortierte Suche schneller ist, wenn die Daten sortiert vorliegen, als wenn sie unsortiert vorliegen.
Wenn Excel feststellt, dass die Daten sortiert sind, kann es doch ganz einfach binär suchen. Egal, ob Du es erlaubst oder nicht. Das ist jedenfalls (noch) naheliegender, als ein versteckter Sort unsortierter Daten, der sich bei mehrfachem Bezug einer Lookup-Formel auf eben solche ergibt.
Detailliertere Begründung:
03.07.2020 03:32:49
Luc:?
Ich habe mein 1.Pgm vor 50 Jahren geschrieben, Daniel;
damals hatten die (rein numerischen) PgmmierSprachen für unseren KR so lustige Namen wie GIPS, FIPS und MOS (MOPS fand man wohl zu assoziativ zu Anderem…). Da musste man auch die SpeicherBelegung planen, denn der war recht klein. Zu den möglichen Operationen gehörten etliche Bit-Manipulationen wie Rechts- und Linksverschiebung, in die eigentliche RechenOperationen aufgelöst wurden. Am aufwendigsten waren Vgle. Da ist es für mich naheliegend, dass Vorzeichen-Bit-Operationen existieren. Ein solches kann auf der letzten (kleinsten) Position eines Bytes liegen, aber auch auf der ersten (höchsten). Das muss dann nur beschrieben wdn und die Zahl ist negativ. Liegt das Bit vorne, kann es auch in die Bit-Folge einbezogen sein, zumindest wdn auch in Xl BinärFolgen (dual, oktal, hexadezimal) dann als negativ interpretiert. Ein weiterer Beweis ist, dass dieses Bit mitunter auch bei einem 0-Ergebnis erhalten bleibt, wodurch man dann ein -0 bewundern kann. Das kann man auch künstlich (per Pgm) hervorrufen. Ich hatte solche Ergebnisse schon gezeigt. Eine Addition oder Multiplikation erfasst dagg die ganze Zahl, denn es kommt zu diversen VerschiebeOperationen, die aus den binären Rechenregeln resultieren. Das wird sich wohl erst mit QBits und QuantenComputern ändern, denn die arbeiten mit mehr als 2 Zuständen.
Da µS den Geschwindigkeitsunterschied im o.g. Artikel erwähnt, wird das wohl auch stimmen und kleinste Unterschiede können sich bei exzessiver Nutzung schnell summieren. Sonst wäre ja das Abzwacken von kleinsten Rundungsdifferenzen für manchen Bank-ITler nicht so verlockend gewesen.
Ja, was ich zum Doppel-Verweis schrieb, stand schon dort, nur nicht alles an derselben Stelle (;-]). Die DatenmengeReduzierung gehörte hier nicht dazu. Allerdings schwebte mir dabei wohl eine Fml-Umsetzung vor (ggf mit integrierter KKLEINSTE-Sortierung*), denn per VBA wende ich Match nur auf kleine Mengen an und die LookUps nie bis kaum. Aber auch zu diesem If-Konstrukt habe ich keine wirkliche Beziehung, denn das lässt sich auch mit nur 1maliger Suche (Match) vermeiden, auch wenn es doppelt etwas schneller ist als INDEX mit integriertem VERGLEICH, aber ob das auch für direkte Indizierung nach 1maligem Match zutrifft…?
* Zu diesem Zweck hatte ich mal eine Sortier-UDF geschaffen, die dann auch so hieß - Sort4Match.
Morhn, Luc :-?
AW: Detailliertere Begründung:
03.07.2020 10:18:12
Daniel
naja, wenn auch MS das schreibt, wird's wohl so sein.
ich kann auch bei 1 Mio Ausführungen keinen signifikanten Unterschied zwischen -- und *1 feststellen, für die Praxis wird's egal sein ob das eine etwas schneller ist oder nicht, in der Regel kombiniert man sowas dann auch mit wesenltlich komplexeren Funktionen, die dann laufzeitbestimmend sind.
bei der Optimierung für den SVErweis hast du zwei unteschiedliche Methoden zusammengeworfen, für mich sieht das so aus, als hättest du das Prinzip immer noch nicht verstanden (du hast ja auch "keine Beziehung" dazu ;-))
Gruß Daniel
Warum sollte ich das nicht verstanden haben, ...
03.07.2020 15:18:34
Luc:?
…Daniel?
Ich versuche nur, so etwas grundsätzlich zu vermeiden und verwende meist vorgefertigte Werte im Vgl, da (wie bereits zuvor erwähnt) Vgle bei Umsetzung die meisten binären Operationen erfordern.
Geschwindigkeiten binärer Operationen wirst du wohl nur über sehr große DatenMengen vgl können, indem du bspw einen sehr großen Bereich verwendest (1. bis letzte Zelle: --bereich, 1*bereich, 0+bereich).
Luc :-?
AW: Warum sollte ich das nicht verstanden haben, ...
03.07.2020 15:26:36
Daniel
"Allerdings empfiehlt µS hier die 2fache Anwendung von ~VERWEISen, falls nach genauer Entsprechung des SuchBegriffs gesucht wdn soll, um die DatenMenge für den äußeren ~VERWEIS klein zu halten."
das ist falsch.
MS empfielt die 2-fache Anwendung des ~Verweises für sortierte Daten, weil dieser deutlich schneller ist, als die für unsortierte Daten.
die zur durchsuchende Datenmenge ist für inneren und äußeren Verweis gleich.
Das hatten wir doch schon festgestellt...!? owT
03.07.2020 21:05:54
Luc:?
:-?

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige