Office: Probleme mit Sverweis in eine andere Datei/mehrere Ergebnisse

Hallo zusammen,

ich weiß, dass der Sverweis ein leidiges und schon oft behandeltes Thema ist. Meist wird auf diesen Link verwiesen: http://www.excelformeln.de/formeln.html?welcher=28
Ich habs leider trotzdem nicht auf die Reihe bekommen *redface.gif* und möchte deshalb die Experten unter euch bei folgendem Problem um Hilfe bitten:

Ich habe folgende Formel erstellt: (SVERWEIS(B28;'[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$CL$999;27;FALSCH)
Im Ansatz funktioniert das auch. Wenn das Suchkriterium (Kundennummer) gefunden wird, liefert der Sverweis mir eine Zahl und wenn nicht ein #NV.
Das Problem ist, pro Suchkriterium kann es unterschiedlich viele Treffer geben und die möchte ich mir alle anzeigen lassen (möglichst hintereinander in einer Zeile).

Wie müsste meine Formel aussehen, damit das funktioniert?

Vielen Dank schonmal an alle die mir mit meinem Problem helfen wollen!

:)

 

Hi ?,
warum gehst du bei der SVERWEIS-Matrix bis Spalte CL? Ab Spalte B brauchst du als 27. Spalte nur AC.

In Zeile 24 steht das einfache SVERWEIS,
in Zeile 26 eine Formel ohne Fehlerabfänger (deshalb #ZAHL!),
in Zeile 28 eine Formel mit Fehlerabfänger.
Reicht dir das aus?

Einzelaufstellung

	B	C	AA	AB
3	1	2	26	27
4	x			y
5	x			y
6	suchwert			zum 1. Treffer
7	x			y
8	x			y
9	x			y
10	suchwert			zum 2. Treffer
11	x			y
12	suchwert			zum 3. Treffer
13	x			y

Tabelle1

	B	C	D	E	F
24		zum 1. Treffer
25
26		zum 1. Treffer	zum 2. Treffer	zum 3. Treffer	#ZAHL!
27
28	suchwert	zum 1. Treffer	zum 2. Treffer	zum 3. Treffer

Zelle	Formel
C24	=SVERWEIS(B28;Einzelaufstellung!B4:AB999;27;0)
C26	{=INDEX(Einzelaufstellung!$AB:$AB;KKLEINSTE(WENN(Einzelaufstellung!$B:$B9=$B28;ZEILE(Einzelaufstellung!$B:$B9));SPALTE(A28)))}
C28	{=WENN(SPALTE(A1)>ZÄHLENWENN(Einzelaufstellung!$B:$B9;$B28);""; INDEX(Einzelaufstellung!$AB:$AB;KKLEINSTE(WENN(Einzelaufstellung!$B:$B9=$B28;ZEILE(Einzelaufstellung!$B:$B9));SPALTE(A28))))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! Excel Jeanie HTML 4

 

Hi,

danke für die schnelle Antwort! Zu deiner 1. Frage, ich weiß nicht warum ich in der Matrix so weit gegangen bin, es schadet aber nicht oder?

Was ich bräuchte ist deine Formel aus C28, aber fehlt da nicht der Bezug auf die andere Datei?

Müsste das dann so aussehen?:

{=WENN(SPALTE(A1)>ZÄHLENWENN('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$B$999;$B28);""; INDEX('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$AB:$AB;KKLEINSTE(WENN('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$B$999=$B28;ZEILE('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$B$999));SPALTE(A28))))}

Und was haben diese Werte SPALTE(A1) am Anfang und SPALTE(A28) am Ende der Formel zu bedeuten? Mein Suchwert befindet sich doch in Spalte B.

Ansonsten werde ich das am Montag bei der Arbeit testen, habe die Dateien nicht zu Hause.

Gruß,

Krazmov

 

Hi,
die Matrix willkürlich zu vergrößern (hier Spalte CL statt AB) kostet vielleicht Rechenzeit
(wenn die Formel oft in der Tabelle steht).
Und es fördert keinesfalls die Übersicht.

Den Bezug auf die andere Datei habe ich absichtlich weggelassen - du hast diese Datei, ich nicht.
Das zu ergänzen war ja auch kein Problem für dich. :-)

Welchen Wert haben die Ausdrücke SPALTE(A1) bzw. SPALTE(A28)?
Das ist immer die Nummer der Spalte A, also 1.

Wenn du die Formel nach rechts kopierst (um weitere Treffer anzuzeigen), wird
aus der Spalte A die Spalte B, dann Spalte C usw., die Werte sind dann 2, 3 usw.

Diese Ausdrücke haben überhaupt nichts damit zu tun, in welcher Spalte sich der Suchwert befindet.
Es sind nur Zähler für den 1., 2., 3. usw. Treffer, also so etwas wie laufende Nummern.

Geschickter wäre gewesen, einheitlich SPALTE(A28) zu schreiben und nicht SPALTE(A1).
Rechnerisch macht das ja keinen Unterschied.

Viel Erfolg beim Testen, davor ein schönes Wochenende!

 

Okay, dann teste ich das Montag mal.

Dir vielen Dank und auch ein schönes Wochenende!

 

Guten Morgen,

es klappt noch nicht ganz.
Wenn der Suchwert nicht gefunden wird, spuckt die Formel nichts aus, das ist gut so.

Wenn er gefunden wirt treten jedoch Probleme auf.

=WENN(SPALTE(A59)>ZÄHLENWENN('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$B$999;$B59);"";INDEX('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$AC:$AC;KKLEINSTE(WENN('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$B$999=$B59;ZEILE('[SIRP1307 Rückstandsliste aufsteigend.xls]Einzelaufstellung'!$B$4:$B$999));SPALTE(A59))))

1. In der Datei in der gesucht wird taucht der Wert in Zeile 453-455 auf. Ich bekomme jedoch den Wert aus Zeile 59 der Suchdatei geliefert. Aufälligerweise genau die Zeile in der die Formel in meiner Basisdatei steht *wink.gif*

2. Wenn ich die Formel nacht rechts ziehe um mir die weiteren Ergebnisse anzeigen zu lassen steht da nur: #ZAHL

Hast du eine Ahnung woran das liegt?

Gruß,

Krazmov

 

Hi,
ja, ich ahne schon, woran das liegt. Das stand unter meinen Formeln:

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Link dazu:

 

Hallo Erich,

du hast Recht und jetzt funktioniert es *Smilie Vielen Dank, das hat mir echt geholfen!

Eine Kleinigkeit funktioniert jedoch noch nicht ganz so wie gewollt. Wenn ich die Basisdatei öffne werden die Berechnungen nur korrekt durchgeführt, wenn vorher die Suchdatei schon geöffnet ist oder dann noch geöffnet wird.

Geht das ganze auch ohne die Suchdatei zu öffnen?