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
Hi,
Tabelle1 | A | B | C | D |
| 1 | 123 | 21 | | 21 |
| 2 | 555 | 22 | | 23 |
| 3 | 123 | 23 | | 24 |
| 4 | 123 | 24 | | |
| 5 | 888 | 25 | | |
| 6 | 999 | 26 | | |
| 7 | | | | |
| 8 | Suche | 123 | | |
| Formeln der Tabelle |
| Zelle | Formel | | 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
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.
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!
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?
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.
Vielen vielen lieben Dank! Jetzt hab ich es verstanden! :)
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?
Hallo!
Schaue Dir mal den Anhang an.
(kompletter PLZ-Bestand Deutschlands, deshalb etwas größer)
Gruß Ralf
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
Hi!
Scheue Dich nicht nachzufragen, denn dies ist (trotz meiner Erklärung in der Tabelle) kein Novizen-Thema.
Gruß Ralf