sverweis links mehrere treffer

Spenski · 2016-05-23T13:18:06+00:00

Guten Tag Ich suche eine Formel die einen SVERWEIS nach links macht und alle Treffer anzeigt in Spalte B soll gesucht werden, und Spalte A soll in Spalte D untereinander ausgegeben werden Danke fürs lesen Christian

Die AGGREGAT-Lösung sieht SOOO hübsch aus. Vor allem kann man mit ihr auch den x-ten Eintrag ohne Vorgängerformeln ermitteln.
Aber es sieht performanceseitig überhaupt nicht gut aus (ich nehme vergleichshalber die VERGLEICH- statt SVERWEIS-Funktionalität, weil SVERWEIS keine "richtige" Datenverarbeitungsformel ist):
D2: =WENNFEHLER(AGGREGAT(15;6;ZEILE(B:B)/(B:B=C$2);ZEILE(A1));"") bis D101
D2: =VERGLEICH(C$2;B$1048576:INDEX(B:B;D1+1);)+D1 bis D10001 (also 100-mal so viele Rückgaben)
AGGREGAT braucht 40 Sekunden für 100 Datensatznummern aus 2^20 Datensätzen.
VERGLEICH braucht unter 2 Sekunden für 10.000 Datensatznummern aus 2^20 Datensätzen.
Das ist ein Verhältnis von 1:2000 bei 2^20 Datensätzen.
Bei 2^13 ist das Verhältnis scheinbar 1:1,5 ... aber AGGREGAT verwendet alle 4 Kerne, was VERGLEICH inhaltlich nicht kann (nur 1 Kern, weil vom Vorgänger abhängig). Somit ist das bereinigte Verhältnis immer noch 1:6.

Aggregat rödelt immer alle angegebenen Zellen durch.
Richtig.
- Verweis mit 3. Parameter = 0 bricht nach der ersten Fundstelle ab, dh je früher der Suchwert in der Liste erscheint, um so schneller ist auch die Funktion fertig berechnet.
Korrekt, es geht um nichtsortierte Daten.
- Verweis mit 3. Parameter = 1 verwendet aufgrund der Sortierung eine noch viel schnellere Suchmethode, die Binäre Suche.
Dann brauchen wir das ganze nicht, weil ab erstem Fundort jeder Satz bis zum letzten Fundort ein Treffer ist.
- Verweis hat eine Automatik eingebaut, welche dafür sorgt, dass auch bei offenen Zellbezügen (A:A) nur der tatsächlich benutze Zellbereich verwendet wird, während bei Matrixformeln, Summenprodukt und wahrscheinlich auch Aggregat diese "Überlastsicherung" fehlt und bei A:A auch die ganze Spalte verarbeitet wird.
Einerseits Binsenwahrheit bei AGGREGAT (es muss ja alles durchsucht werden kraft Logik); andererseits wäre bei VERGLEICH auch echtes A:A egal, weil der Weg zum ersten Treffer der gleiche bliebe.

	A	B	C	D
1	Text	Vergleichswerte	Suchwert	Ergebnis
2	Text_1	a	b	Text_2
3	Text_2	b		Text_4
4	Text_3	c		Text_5
5	Text_4	b		Text_9
6	Text_5	b
7	Text_6	c
8	Text_7	a
9	Text_8	e
10	Text_9	b
11	Text_10	a
12

A	B
Name	Wert
Text_1	a
Text_2	b
Text_3	c
Text_4	b
Text_5	b

Forumthread: sverweis links mehrere treffer

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

SVERWEIS nach links mit mehreren Treffern in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)