Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1492to1496
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1492to1496
1492to1496
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

sverweis links mehrere treffer

sverweis links mehrere treffer
23.05.2016 13:18:06
Spenski
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

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: die INDEX() und AGGREGAT()-Lösung ...
23.05.2016 13:50:24
...
Hallo Christian,
... so (Formel ziehend nach unten kopieren):
 ABCD
1TextVergleichswerteSuchwertErgebnis
2Text_1abText_2
3Text_2b Text_4
4Text_3c Text_5
5Text_4b Text_9
6Text_5b  
7Text_6c  
8Text_7a  
9Text_8e  
10Text_9b  
11Text_10a  
12    

Formeln der Tabelle
ZelleFormel
D2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(B$1:B99)/(B$1:B$99=C$2); ZEILE(A1))); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
nicht performant :-(
23.05.2016 14:42:20
lupo1
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.

Anzeige
AW: nicht performant :-(
23.05.2016 14:46:31
Spenski
Danke an alle , funktioniert wie es soll
mfg
Christian

AW: nicht performant :-(
23.05.2016 15:25:25
Daniel
Aggregat rödelt immer alle angegebenen Zellen durch.
- 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.
- Verweis mit 3. Paramter = 1 verwendet aufgrund der Sortierung eine noch viel schnellere Suchmethode, die Binäre Suche.
- 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.
Gruß Daniel

Anzeige
Schön und gut: es heißt VERGLEICH, nicht Verweis
23.05.2016 15:43:13
lupo1
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.

Anzeige
AW: Schön und gut: es heißt VERGLEICH, nicht Verwe
23.05.2016 15:51:49
Daniel
egal, Hauptsache es wird klar, dass eine Aggreagt-Formel nicht immer die beste Lösung ist.
Gruß Daniel

AW: da vergleichst Du "Äpfel mit Birnen" ...
23.05.2016 15:51:45
...
Hallo lupo,
... natürlich ist Deine Formel mit der Funktion VERGLEICH() schneller als Deine AGGREGAT()-Formel, die Du zum Vergleich angesetzt hast.
Meine Formel sah hier anders aus (beschränkter Bereich und keiner über den gesamten Spaltenbereich) und gibt im vorliegenden Fall sofort die richtigen Ergebnisse aus. Deine VERGLEICH()-Formel ermittelt zunächst nur die Ergebniszeilennummern und Fehlerwerte. Natürlich kann man diese dann als Hilfsspalte nutze, aber im vorliegenden Fall mache ich das nicht.
Eine "AGGREGAT()-Formel" beruht auf ihr innewohnenden Matrixfunktionalität und berechnet die Ergebnisse analog "echter" Matrixformeln. Bei solchen oder z.B. bei einer in der Wirkungsweise ähnlichen Formel auf Basis z.B. der SUMMENPRODUKT()-Funktion kommst Du doch sicherlich auch nicht auf die Idee, den gesamten Spaltenbereich auswerten zu wollen, wenn Du die Performance im Auge hast.
Gruß Werner
.. , - ...

Anzeige
1:6 ist also kein Argument?
23.05.2016 16:05:57
lupo1
Ich habe doch extra auch das Szenario mit 8192 Datensätzen gebracht.
Und eine einspaltige Rückgabe von Ergebniswerten reicht doch nie aus. Man will die entscheidenden Daten des Datensatzes gleich sichtbar haben. Und dann geht "SVERWEIS"-AGGREGAT gegenüber "VERGLEICH"-AGGREGAT oder gar der echten VERGLEICH-Lösung mit mitgezogenem Suchbeginn nochmal mehrfach in die Knie. Nur weil man die "Hilfszellen" angeblich nicht sehen möchte! Mich interessieren aber die Datensatznummern immer, schon aus Plausi-Gründen.
Beispiel: Ich schaue beim Online-Banking nach, welche Überweisungen an Schulze gingen. Da hilft mir als Ausgabe
Schulze
Schulze
Schulze
Schulze
wenig ;) Ich möchte auch Vornamen, Datümer, Beträge und Überweisungszwecke sehen.

Anzeige
AW: war hier erstens so nicht gefragt ...
23.05.2016 16:15:46
...
Hallo,
... und zweitens würde ich dann diesbzgl. den Autofilter nehmen. Der ist garantiert noch schneller in der Auswertung.
Gruß Werner
.. , - ...

307 Forumthreads zu ähnlichen Themen

Sverweis Summe aus darüberliegenden Zeilen
Matthias

Hallo,
ich möchte mit Sverweis eine Summe aus dem Wert der verwiesenen Zelle und den
Werten aus den 4 darüberliegenden Werten der selben Spalte bilden:
z.B:
in Spalte A5 steht "x" und in Spalte BR5 der Wert 5, in BR4 steht 4 in BR3 steht 3 usw.
jetzt bräuchte ich die Funktio...
24.11.2008 13:07:49
Thread-ID: 1027283
5 Beiträge
Anzeige
sverweis o. index mit mehreren ergebnissen
Nico

Moin
Ich benötige nochmal Hilfe:
In meinem Tabellenblatt "Geschäftspartner" möchte ich den Geschäftspartner die entsprechenden Auftragsnummern zuweisen. (Im Prinzip einfach in die nächsten Spalten schreiben)
Die Auftragsnummer sind in einem Jahresüberblick zusammen mit den Geschäfts...
25.02.2009 15:54:45
Thread-ID: 1054026
3 Beiträge
ein Suchkriterium, mehrere Ergebnisse
zellner

Hallo
ich hätte ein kleines Problem und in den Archiven habe ich leider nichts passendes gefunden. Die Formel habe ich aus www.excelformeln.de (Nummer 28), ich hab aber als Ergebnis Text und keine Zahlen, ebenso ist mein Suchkriterium eine 0 aus Formeln.
Die Datei https://www.herber.de/...
18.04.2009 22:04:37
Thread-ID: 1069545
6 Beiträge
Anzeige
Sverweis und suche suche über mehrere Spalten
Sabine

Hi
Folgendes Problem. In Tabelle1 Spalte A stehen werte, die in Tabelle 2 in Spalte B-F gesucht werden sollen und dann der wert aus Spalte X ausgegeben werden.
Mit Sverweis geht das nicht, aber wie dann?
30.07.2009 18:44:34
Thread-ID: 1092620
2 Beiträge
SVERWEIS & Co mit mehreren Dateien
Mike

Hi,
ich habe ca. 1000 Kabel erfasst in mehreren Dateien (je Verteiler eine Datei)
mit den Spalten: KABELNUMMER, SCHRANKNAME, PATCHFELD, PORT
D.h ich habe bei richtig erfassten Kabeln je eine Zeile in zwei Dateien (Verteilerschränken).
und sollte in einer der neun anderen Dateien...
24.09.2009 07:43:05
Thread-ID: 1104979
2 Beiträge
Combobox Probleme mit sort. und doppelten Werten
chris

Hallo VBA experten,
ich frage hier nach weil ich einfach nichts finde das so klappt wie ich es will :(
Ich habe eine ganz einfache Form erstellt auf dieser habe ich eine Combobox.
Und diese will ich nur mit werten aus einer Spalte befüllen.
Spalte J ab Zeile 5 bis zum letzten ei...
27.01.2010 12:14:19
Thread-ID: 1133383
5 Beiträge
Sverweis mit mehreren matches.
Helge

Hi,
ich hab im Archiv zwar einige Loesungen gefunden, jedoch hab ich keins zum laufen bekommen. Denke das es am Zeit und Datumsformat liegt. Nun bin ich mal wieder auf eure hilfe angewiesen.
Hab eine kleine bsp datei vorbereitet:
Die Datei https://www.herber.de/bbs/user/67926.xls w...
11.02.2010 14:49:54
Thread-ID: 1137772
5 Beiträge
SVERWEIS mit Mehrfachtreffern
André

Moin zusammen,
ich habe mal wieder ein Problem mit einer Formel.
Gegeben ist eine Datentabelle mit zwei Spalten. In der ersten Spalte stehen Namen, in der zweiten Spalte ID-Nummern. Jeder Name kann bis zu 3 mal vorkommen, sortiert ist die Datentabelle nach der ersten Spalte.
In eine...
15.02.2010 11:51:30
Thread-ID: 1138611
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
2
2
5
4
1
1
4
4
2
6
8
3
2
2
1
2
15
9
3
7
5
3
4
6