Office: Sverweis mehrere Ergebnisse anzeigen lassen

Hallo zusammen,

ich habe ein Problem mit dem darstellen mehrerer Ergebnisse eines Sverweis und zwar habe ich eine Quelldatei mit Artikeln und Lieferzeit, sowieso Verzugszeiten.
Dafür habe ich mithilfe eines Sverweises schon Klassen erstellt wie lange bestimmte Artikel im Verzug sind.
Nun brauche ich aber noch eine Abfrage die für eine bestimmte Klasse alle betreffenden Artikel untereinander ausgibt.
Leider gibt mir der Sverweis hier immer nur das erste Ergebnis wieder.

http://www.excelformeln.de/formeln.html?welcher=28 Diese Seite habe ich auch schon gefunden, komme ich leider überhaupt nicht mit klar.

Momentan verwende ich hierfür diese Formel:
=SVERWEIS($A$2;'Quelldaten SAP KCP15'!$A$1:$U$6149;7;FALSCH)&","&SVERWEIS($A$2;'Quelldaten SAP KCP15'!$A$1:$U$6149;8;FALSCH)&", Lieferzeit: "&SVERWEIS($A$2;'Quelldaten SAP KCP15'!$A$1:$U$6149;20;FALSCH)&"Tage, Verzug: "&SVERWEIS($A$2;'Quelldaten SAP KCP15'!$A$1:$U$6149;21;FALSCH)&"Tage"

Quelldaten ist die Tabelle wo ich meine Infos rausziehen möchte, momentan verknüpfe ich hier, die Artikelnummer, mit dem Text und der dazugehörigen Lieferzeit und Verzugszeit.

Über Hilfe anders als der oben genannte Internetlink wäre ich sehr dankbar

 

Hallo!

Hmmm, die dort angegebene Formel ist für deine Zwecke aber ideal! Etwas besseres gibt es IMHO nicht.

Aus z.B.
=SVERWEIS($A$2;'Quelldaten SAP KCP15'!$A$1:$U$6149;7;FALSCH)
wird (ungetestet)
=INDEX('Quelldaten SAP KCP15'!G:G;KKLEINSTE(WENN('Quelldaten SAP KCP15'!A1:A6149=A2;ZEILE(1:6149));ZEILE(A1)))

Achtung Matrixformel! Die Formeleingabe mit Strg+Shift+Enter abschliessen!

 

Das habe ich schon fast befürchtet.

Ich verstehe sie nur nicht, was macht das KKleinste und vor allem das Zeile am Ende?

Hab mich schon durch die Excel Hilfe gekaut, aber schlau geworden bin ich daraus trotzdem nicht

 

Hallo!

KKLEINSTE sucht den k-kleinsten Wert aus einem Bereich:
=KKLEINSTE(A1:A10;3) liefert z.B. den drittkleinsten Wert aus A1:A10

In deinem Fall sucht
KKLEINSTE(...;ZEILE(A1)) die Zeile des ersten Auftretens,
KKLEINSTE(...;ZEILE(A2)) die Zeile des zweiten Auftretens,
KKLEINSTE(...;ZEILE(A3)) die Zeile des dritten Auftretens, usw.

ZEILE() wird hier benutzt, damit du die Formel runterziehen kannst. Sonst würden stattdessen auch "normale" nummerische Werte ausreichen.

 

Schon mal danke für die Erklärung und die Hilfe!

Hab die Formel jetzt bei mir in die Tabelle eingebaut, was dann so ausschaut:
=INDEX('Quelldaten SAP KCP15'!G:G;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$2:$A$6149=$A$2;ZEILE(9:6157));ZEILE(A9)))&","&INDEX('Quelldaten SAP KCP15'!H:H;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$2:$A$6149=$A$2;ZEILE(9:6157));ZEILE(A9)))&", Lieferzeit: "&INDEX('Quelldaten SAP KCP15'!T:T;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$2:$A$6149=$A$2;ZEILE(9:6157));ZEILE(A9)))&" Tage,Verzug: "&INDEX('Quelldaten SAP KCP15'!U:U;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$2:$A$6149=$A$2;ZEILE(9:6157));ZEILE(A9)))&" Tage"

Problem ist nur wenn ich herunterziehen will wird #Zahl angezeigt und er zieht auch nicht die richtigen Werte, habe das Gefühl er gibt irgendwas wieder =(

 

Hallo!

Sorry, ich dachte, das mit den Dollarzeichen bekommst du alleine hin...

=INDEX('Quelldaten SAP KCP15'!G:G;KKLEINSTE(WENN('Quelldaten SAP KCP15'!A$1:A$6149=A$2;ZEILE($1:$6149));ZEILE(A1)))

 

Ich hab die Dollarzeichen alleine hinbekommen! Formel sieht genauso aus wie du gerade geschrieben hast, trotzdem bekomme ich zum einen die Fehlermeldung und zum anderen zieht er nicht die richtigen Ergebnisse.

Hab es jetzt hiermit gelöst:
=WENN('Quelldaten SAP KCP15'!A1=$E$2;VERKETTEN('Quelldaten SAP KCP15'!G1;" ";'Quelldaten SAP KCP15'!H1;", Lieferzeit: ";'Quelldaten SAP KCP15'!T1;" Tage, Verzug: ";'Quelldaten SAP KCP15'!U1; " Tage");" ")

und danach noch die leeren Zeilen rausgefiltert

 

Hallo,

nur mal nebenbei...hier wäre ne Pivottabelle ggf. angebrachter...aber ohne Bsp.-Datei kann ich es nicht genau sagen!

 

Hallo,

Pivot Tabelle habe ich auch schon versucht, führte aber nicht zu dem was ich brauche.

Datei sieht ungefähr so aus:

Klassen Artikelnummer Lieferzeit Verzug
Kein Verzug a 20 0
1 Woche b 5 4
2 Wochen c 33 15
3 Wochen d 15 22

und ich möchte eine Tabelle haben, die pro Klasse alle Artikel auflistet inkl. Lieferzeit usw.

 

Hallo!

Aber dann passt die Formel, die du gefunden hast, doch wie Faust auf's Auge!

	A	B	C	D	E	F	G	H	I	J
1	Klassen	ArtNr	Lieferzeit	Verzug			Klasse	ArtNr	Lieferzeit	Verzug
2	Kein Verzug	a	20	0			3 Wochen	d	15	22
3	1 Woche	b	5	4				h	77	66
4	2 Wochen	c	33	15				i	88	77
5	3 Wochen	d	15	22				#ZAHL!	#ZAHL!	#ZAHL!
6	Kein Verzug	e	44	33
7	1 Woche	f	55	44
8	2 Wochen	g	66	55
9	3 Wochen	h	77	66
10	3 Wochen	i	88	77
11

Zelle	Formel
H2	{=INDEX(B:B;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A1)))}
I2	{=INDEX(C:C;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A1)))}
J2	{=INDEX(D:D;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A1)))}
H3	{=INDEX(B:B;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A2)))}
I3	{=INDEX(C:C;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A2)))}
J3	{=INDEX(D:D;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A2)))}
H4	{=INDEX(B:B;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A3)))}
I4	{=INDEX(C:C;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A3)))}
J4	{=INDEX(D:D;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A3)))}
H5	{=INDEX(B:B;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A4)))}
I5	{=INDEX(C:C;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A4)))}
J5	{=INDEX(D:D;KKLEINSTE(WENN($A$2:$A$999=$G$2;ZEILE($2:$999));ZEILE($A4)))}

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

 

Hallo,

ja sie passt wirklich sehr gut.
Trotzdem spuckt sie bei mir nur eine Zeile aus und danach nur #Zahl, obwohl ich sie wirklich genauso übernommen habe, oder habe ich noch irgendwas übersehen? Kann es sein, dass es daran liegt, dass ich mehrere Formeln davon in einer Zeile verkette?
Sehe mittlerweile den Wald vor lauter Bäumen nicht mehr =(

=INDEX('Quelldaten SAP KCP15'!G:G;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$1:$A$6149=$C$2;ZEILE($1:$6149));ZEILE($A1)))&","&INDEX('Quelldaten SAP KCP15'!H:H;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$1:$A$6149=$C$2;ZEILE($1:$6149));ZEILE($A1)))&", Lieferzeit: "&INDEX('Quelldaten SAP KCP15'!T:T;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$1:$A$6149=$C$2;ZEILE($1:$6149));ZEILE($A1)))&" Tage,Verzug: "&INDEX('Quelldaten SAP KCP15'!U:U;KKLEINSTE(WENN('Quelldaten SAP KCP15'!$A$1:$A$6149=$C$2;ZEILE($1:$6149));ZEILE($A1)))&" Tage"

 

Hallo!

Hast du den Matrix-Hinweis beachtet?
Sonst musst du überprüfen, ob deine Suchbegriffe wirklich EXAKT übereinstimmen. Z.B. ein unsichtbares Leerzeichen hinter dem Eintrag kann dir schon einen Strich durch die Rechnung machen.

 

Hallo,

es war die Matrix-Klammer..

Vielen vielen Dank!!
Es funktioniert jetzt einwandfrei, mein Wochenende ist gerettet =)

 

Hallo zusammen,

ziehe das hier mal hoch. Habe für mein Projekt auch die Formel von:
http://www.excelformeln.de/formeln.html?welcher=28

verwendet. Das funktioniert soweit auch wunderbar, nur brauche ich die Ergebnisse in einer Zeile und nicht in einer Spalte.

Verstehe die Formel leider nicht komplett und habe es so versucht:
=WENN(ZEILEN($1:3)>ZÄHLENWENN($B:$B;$E$1);"";INDEX($A:$A;KKLEINSTE(WENN($B$1:$B$15000=$E$1;ZEILE($1:$15000));SPALTE(B1))))

bekomme hier leider sobald es kein Suchgergebnis mehr gibt eine Fehlermeldung.
Kann hier jemand helfen bitte?

BG

Lucky

 

Hallo!

ungetestet:

{=WENN(SPALTEN($A:A)>ZÄHLENWENN($B:$B;$E$1);"";INDEX($A:$A;KKLEINSTE(WENN($B$1:$B$15000=$E$1;ZEILE($1:$15000));SPALTE(A1))))}