Microsoft Excel

Herbers Excel/VBA-Archiv

SVERWEIS (nicht nur den erstgefundenen ausgeben)

Betrifft: SVERWEIS (nicht nur den erstgefundenen ausgeben) von: MartinFl
Geschrieben am: 05.08.2006 07:33:59

Hallo miteinander,

stehen z.B. in der Spalte A mehrere gleiche Suchkriterien, dann wird mit SVERWEIS ja nur der erste Datensatz ausgelesen.
Ich suche nach einer Möglichkeit/Funktion, welche mir nicht nur die erstgefundenen Daten ausgibt, sondern auch alle weiteren (in verschiedenen Zellen).

Mit SUMMENPRODUKT oder ANZAHL lasse ich mir im Moment die Anzahl der vorhandenen Datensätze mit dem gleichen Suchkriterium bestimmen – als eine Art Überprüfung, damit ich weiß, dass es falls es mehr als einen Datensatz gibt, auf der sicheren Seite bin.

Stelle mir das wie folgt vor:
In 5 verschiedenen Zellen werden nacheinander
erst der erstgefundene Datensatz,
dann der zweite gefundene,
dann der dritte gefundene
usw.
ausgegeben.

Mehr wie 5 gleiche Suchkriterien gibt es in meiner Datenbank nicht.
Sollten es weniger sein, dann soll in den übrigen Suchzellen #NV ausgegeben werden.

Hat hierzu jemand eine Lösung?

Besten Dank im Voraus!

Gruß
Martin

  


Betrifft: AW: SVERWEIS (nicht nur den erstgefundenen ausgeben) von: Alwin Ball
Geschrieben am: 05.08.2006 08:10:10

Hallo Martin,
Du könntest versuchen die Position des gefundenen Datensatzes mit ROW zu halten und dann ab dieser Position die Suchmatrix neu festlegen.
Nur so als Ansatz...

mfg
stormy_weathers


  


Betrifft: AW: SVERWEIS (nicht nur den erstgefundenen ausgeben) von: Dirk N.
Geschrieben am: 05.08.2006 09:02:45

Hallo Martin,

ich habe ein ähnliches Problem durch Einfügen einer Hilfsspalte (mit Bezug auf die Zeilennummer) gelöst. Hast du eine Beispielmappe?

MfG Dirk N.


  


Betrifft: AW: SVERWEIS (nicht nur den erstgefundenen ausgeben) von: Walter
Geschrieben am: 05.08.2006 09:47:51

Hallo Dirk,
Dein Tip ist sehr, sehr gut.
Frage kann man dies auch für Excel 2003anwenden?
Gruß
Rentner Walter
aus dem nördlichen Nordhessen


  


Betrifft: AW: SVERWEIS (nicht nur den erstgefundenen ausgeben) von: Dirk N.
Geschrieben am: 05.08.2006 09:59:10

Hallo Walter,

vielen Dank für die Blumen !!!
Diesen Tipp hatte ich damals auch aus diesem Forum - ist schon prima hier.
Allerdings kann ich deine Frage nicht beantworten, da ich EXCEL 2003 nicht kenne od. nutze. Doch warum sollte sich etwas verschlechtert haben? Rein vom Gefühl würde ich sagen: Funktioniert.

MfG Dirk N.


  


Betrifft: AW: SVERWEIS (nicht nur den erstgefundenen ausgebe von: Erich G.
Geschrieben am: 05.08.2006 10:50:15

Hallo zusammen,

zu mehreren Fundstellen mit SVERWEIS ist diese Seite interessant:
http://www.excelformeln.de/formeln.html?welcher=28

Noch ne Frage an Walter:
Kontest du mit meiner Mappe zu "Statistik" in
https://www.herber.de/forum/messages/787838.html
etwas anfangen?

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  


Betrifft: AW: SVERWEIS (nicht nur den erstgefundenen ausgeben) von: MartinFl
Geschrieben am: 05.08.2006 22:34:34

Hallo und guten Abend,

vielen Dank für Eure Hilfe - es war ein langes Grübeln & Probieren.

Folgendes ist nun dabei herausgekommen:

https://www.herber.de/bbs/user/35605.xls

Im hellblauen Blatt "Suchmaske" habe ich parallel die Datenbank nach dem Suchkriterium vergleichen lassen (Spalte A / mit einer einfachen WENN-DANN-Funktion).
In den Spalten C, D und E (grau hinterlegt) lasse ich dann statt mit SVERWEIS mit INDEX die Daten auslesen.
Zelle C2 z.B. heißt dann:

INDEX('Beispiel-Datenbank'!$A$1:$D$31;A2;2)

Unter dem Suchkriterium habe ich für den direkten Vergleich, dass dies funktioniert, die Datenbank hineinkopiert.

Die eigentliche Datenbank, für welche ich diese Suchaktion benötige ist wesentlich größer als diese Beispiel-Datenbank. Somit wären alle gefundenen Daten natürlich nicht auf einer Bildschirmseite ersichtlich.
Nun habe ich einen normalen Filter gesetzt - der dann alle gefundenen Daten untereinander anzeigt.

Super, vielen Dank für Eure Unterstützung!!

Ich lasse mal diesen Beitrag noch offen - vielleicht gibt es ja jemanden, jener eine noch einfachere Lösung weiß (vielleicht direkt mit einem modifizierten SVERWEIS).

Gruß
Martin


  


Betrifft: AW: SVERWEIS mehrfach von: Daniel Eisert
Geschrieben am: 06.08.2006 13:01:10

Hallo

ich habe mal was zusammengebastelt, zwar nicht mit SVerweis, sondern mit VERGLEICH und INDEX (womit sich aber ein SVERWEIS zusammnbauen läßt).

https://www.herber.de/bbs/user/35610.xls


Ausgangspunkt ist, daß ein Wert höchstens 5x auftritt.
Es wird eine Hilfsspalte verwendet, die die Position des Suchbegriffs innerhalb der Tabelle angibt.
In der ersten Zeile einer Gruppe wird für die Ermittlung der der Zeilen-Nr die gesamte Datentabelle verwendet.
in den folgenden vier Zeilen einer Gruppe wird nicht mehr die gesamte Tabelle verwendent, sondern nur der Bereich, der unterhalb der zuvor gefundenen Zeilen-Nr. liegt.

Sind in der Datentabelle weniger als 5 Begriffe vorhanden, hat die Hilfspalte einen Fehlerwert
Über die Funktion =WENN(ISTFEHLER(xxx);...) kannst du dann entscheiden, welcher was in diesem Fall als Ergebnis erscheinen soll (ich habs mal auf #NV gestellt)

Gruß, Daniel Eisert


  


Betrifft: AW: SVERWEIS mehrfach von: MartinFl
Geschrieben am: 06.08.2006 17:04:49

Hallo Herr Daniel Eisert,

vielen Dank für Ihre elegante Lösung - that's it!!!!

Kleine Frage nebenbei:

VERGLEICH(D3;INDEX(test;;1);0)

Darf man ;; bei der INDEX-Funktion so schreiben? Warum?

Auch habe ich in einem akteullen Beitrag (Summenprodukt komplex - Thomas 03.08.2006 11:11:44)folgende für mich noch unbekannten Varianten wie z.B.

summenprodukt(--((a2:a100=6)+(a2:a100=7));--((b2:b100=230)+(b2:b100=240));--(c2:c100="A");d2:d100)

gesehen. Was und warum bedeuten die -- in der SUMMENPRODUKT-Funktion?

Gruß
Martin


  


Betrifft: AW: SVERWEIS mehrfach von: Daniel Eisert
Geschrieben am: 06.08.2006 17:46:50

Hallo

zu Summenprodukt: keine Ahnung

zu Index:
wenn bei Index ein Parameter leer gelassen wird, wird die gesamte Reihe bzw. Spalte addressiert der angegeben Matrix addressiert, dh.:

Index(A1:J10;2;2) entspricht B2
Index(A1:J10;;2) entspricht B1:B10
Index(A1:J10;3;) entspricht A3:J3

Gruß, Daniel Eisert


  


Betrifft: AW: SVERWEIS mehrfach von: Erich G.
Geschrieben am: 07.08.2006 10:12:33

Hallo Martin,

Die "--" (oder mit dem selben Effekt: "* 1") dienen zur Umwandlung eines (meist booleschen) Ausdrucks in eine Zahl.

In der Summenproduktformel in E1 sind die "--" an zwei Stellen überflüssig,
da wegen der Addition ohnehin eine automatische Umwandlung vorgenommen wird.
Damit verkürzt sich die Formel zu der in F1.
Es funktioniert auch ganz ohne "--" - mit der Formel in G1.

In Zeile 2 und 3 siehst du, was "--" aus WAHR und FALSCH macht.

Grüße von Erich aus Kamp-Lintfort


  


Betrifft: AW: SVERWEIS mehrfach von: MartinFl
Geschrieben am: 09.08.2006 04:27:17

Hallo und guten Morgen,

vielen Dank für Ihre/Eure Hilfe zu SVERWEIS und SUMMENPRODUKT!

Gruß
Martin


 

Beiträge aus den Excel-Beispielen zum Thema "SVERWEIS (nicht nur den erstgefundenen ausgeben)"