Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

SVERWEIS - mehrere Treffer anzeigen lassen
#1
Hallo, ich danke schon mal allen für ihre Hilfe,

ich muss folgende Sache mit dem SVERWEIS lösen: der SVERWEIS soll eine Zahl bekommen und diese Zahl dann in einer Matrix suchen. Anschließend gibt er den Wert in der nächsten Spalte aus. Jetzt das Problem: Diese Zahl kommt in der Matrix eben öfter vor! D.h. er soll jedesmal einen Wert ausgeben, wenn diese Zahl vorkam. Nach all meinen Versuchen, habe ich es nicht geschafft, über mehr als eine Ausgabe zu kommen. D.h. ich kriege einmal einen Wert, allerdings kam die Zahl mehrmals vor und die restlichen wurden nicht ausgegen. Weiß jemand, wie über mehrere Zeilen alle Treffer angegeben werden? Das sieht in etwa so aus:

     A    B  
1 123 21
2 555 22
3 123 23
4 123 24
5 888 25
6 999 26

Ausgegeben soll folgendes werden:
21
23
24

mit =SVERWEIS(123;A1:B6;2) wird nur 24 ausgegen
Antworten Top
#2
Hi,

Tabelle1

ABCD
11232121
25552223
31232324
412324
588825
699926
7
8Suche123
Formeln der Tabelle
ZelleFormel
D1{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(A$1:A$6=$B$8;ZEILE($1:$6));ZEILE(A1)));"")}
D2{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(A$1:A$6=$B$8;ZEILE($1:$6));ZEILE(A2)));"")}
D3{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(A$1:A$6=$B$8;ZEILE($1:$6));ZEILE(A3)));"")}
D4{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(A$1:A$6=$B$8;ZEILE($1:$6));ZEILE(A4)));"")}
D5{=WENNFEHLER(INDEX(B:B;KKLEINSTE(WENN(A$1:A$6=$B$8;ZEILE($1:$6));ZEILE(A5)));"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#3
WillWissen vielen Dank für deine Hilfe! Das sieht schon einmal sehr gut aus. Könntest Du mir aber bitte noch mit eigenen Worten erklären, was bei "B:B" , "A$1:A$6=$B$8" , "$1:$6" und "A1" eingegeben werden muss? Damit ich auch bei anderen Aufgaben das selbe Schema verwenden kann. "ZEILE($1:$6)" versteh ich jetzt auf anhieb zum Beispiel nicht.
Antworten Top
#4
Hallo Klaus,

Deine Fragen zeigen, dass Du Dich etwas mehr mit Excel befassen mußt! Das was Du nachfragst sind alle Zellbereiche, eine Spalte, ein Vergleich eines Zellebereichs mit einer einzelnen Zelle und eine Zeille.
Eingeben mußt Du nur etwas in den genannten Bereichen, den Rest macht die Formel. Am besten Du probierst das an Deiner Tabelle aus und nutzt die Formelauswertung und die Excel-Hilfe!
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#5
Hi Klaus,


Zitat:Könntest Du mir aber bitte noch mit eigenen Worten erklären, was bei "B:B" , "A$1:A$6=$B$8" , "$1:$6" und "A1" eingegeben werden muss?

Huh Huh Huh

Was meinst du mit "was muss eingegeben werden"?

Bei der Matrixformel handelt es sich um die Funktion INDEX(). Schau dazu auch mal in die Hilfe von Excel. Dort ist die Funktion sehr gut erklärt. Die Syntax lautet: =INDEX(Matrix;Zeile;[Spalte])

Der Parameter "Matrix" ist der Bereich, aus dem das Ergebnis ausgelesen werden soll - in deinem Fall die Spalte B. Um die richtige Zelle zu finden muss die Zeile angegeben werden, in der sich der gesuchte Wert befindet. Dies wird mit der WENN-Abfrage in Verbindung mit KKLEINSTE erreicht. Es wird abgefragt, ob sich in Spalte A1:A6 der Suchbegriff aus der Zelle B8 befindet. Da der Wert mehrfach vorhanden ist, müssen die Zeilen, in denen der Wert vorkommt angegeben werden. Das geschieht mit dem Parameter k bei KKLEINSTE. Mit einer 1 wird der kleinste Wert - in diesem Fall die niedrigste Zeilennummer, mit 2 die zweitniedrigste Zeilennummer usw. ermittelt. Damit das dynamisch geschehen kann (sonst müsstest du jede Formel separat mit den entsprechenden Parametern versehen) wird ZEILE(A1) angegeben. Durch das Runterziehen verändert sich das A1 in A2, A3 usw, was den kleinsten, zweitkleinsten, drittkleinsten usw. Wert angibt. $1:$6 gibt an, in welchen Zeilen (1-6) gesucht werden soll.

Übrigens, es gibt auch eine Formelauswertung. Nimm dir diese mal vor. Hier wird dir angezeigt, was die einzelnen Formelteile bewirken.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#6
Vielen vielen lieben Dank! Jetzt hab ich es verstanden! :)
Antworten Top
#7
Hallo,

ich habe ein Ähnliches Problem, bei der eingabe einer PLZ in A1 soll in A2 der zutreffende Ort eingefügt werden. funktioniert mit SVERWEIS und einem zweiten Datenblatt mit allen PLZ und zugehörigen Orten tadellos.
Nun gibt es aber PLZ-Bereiche denen mehrere Orte zugehören. In diesem Fall hätte ich gerne dass nach der Eingabe der PLZ in A1 eine Dropdown-Liste mit allen zu diesem PLZ-Bereich gehörigen Orte auftaucht und ich so den gewünschten Ort aus der Liste auswählen kann...

Hat jemand eine Idee wie man das umsetzen könte?


Huh
Antworten Top
#8
Hallo!
Schaue Dir mal den Anhang an.
(kompletter PLZ-Bestand Deutschlands, deshalb etwas größer)

Gruß Ralf


Angehängte Dateien
.xlsm   PLZ-Ort per Array (final).xlsm (Größe: 349,87 KB / Downloads: 210)
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antworten Top
#9
Hi Ralph,

erst mal vielen Dank, genau das ist es... Bin noch nicht ganz durchgestiegen wie Du es umgesetzt hast, aber das find ich noch raus... DANKE Thumbsupsmileyanim
Antworten Top
#10
Hi!
Scheue Dich nicht nachzufragen, denn dies ist (trotz meiner Erklärung in der Tabelle) kein Novizen-Thema.

Gruß Ralf
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste