Clever-Excel-Forum › Office-Anwendungen › Excel v
Matrixformel zur Auflistung von Zeilen

Thema geschlossen 
Baumstrukturmodus
Matrixformel zur Auflistung von Zeilen
Hawkeye Offline
pausiert
Registriert seit: 14.04.2016
Version(en): 2013
#1
14.04.2016, 12:33 (Dieser Beitrag wurde zuletzt bearbeitet: 14.04.2016, 13:22 von Hawkeye.)
Hallo zusammen

Ich verwende die untenstehende Matrixformel um aus einer Datenbank alle Einträge aufzulisten (Im Beispielcode unten aus Spalte B), welche in der Spalte S (der Datenbank) den Inhalt von Zelle D1 (aktuelle Tabelle) aufweisen. Dies funktioniert auch tadellos, jedoch möchte ich, dass auch Zeilen aufgelistet werden, welche den Inhalt von D1 als Substring enthalten, also auch noch anderen Text enthalten.
Dies versuchte ich mit diesem Code zu bewerkstelligen:
Code:
VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*")

Beim normalen SVERWEIS klappt dies, allerdings komme ich bei der Matrixformel auf keinen grünen Zweig. Ich muss auch sagen, dass ich die Matrixormel nicht zu 100% selbst entworfen habe und sie nicht restlos verstehe. Ich bin auf dem Gebiet auch ein relativer Anfänger.
Ich vermute, dass der Teil KKLEINSTE(WENN... das Problem ist, darum versuchte ich diesen WENN Teil durch den untenstehenden "Code X" zu ersetzen, was aber auch nichts gebracht hat.

Beispiel Wert Für D1: FS
In einer Zelle der Spalte S der Datenbank steht dann z.B.: XY, FS, ZU
Dann soll die zugehörige Zelle in Spalte B aufgelistet werden. Dies funktioniert wie gesagt wenn anstatt XY, FS, ZU nur FS in der Datenbank steht.

Kann mir jemand sagen, wie ich den entsprechenden Code verändern muss?

Matrixformel
Code:
{=WENN(WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!S:S;VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002=INDIREKT("'"&$D$1&"'!$D$1");ZEILE($1:$1002));ZEILE(A1))))=0;"";(WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!S:S;VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002=INDIREKT("'"&$D$1&"'!$D$1");ZEILE($1:$1002));ZEILE(A1))))))}


Code X
Code:
WENN(ISTFEHLER(FINDEN((VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002));"Falsch";ZEILE($1:$1002))


Angehängte Dateien
.xlsm   Geschäftsdatenbank.xlsm (Größe: 688,67 KB / Downloads: 9)
.xlsm   Mitarbeiterdatenbank.xlsm (Größe: 305,78 KB / Downloads: 7)
Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#2
14.04.2016, 12:48
Hi,

stell mal bitte eine Beispielmappe vor. Alleine aus deiner Beschreibun g werde ich nicht schlau, was du wo berechnet haben willst.

Hier kannst du nachlesen, wie das Hochladen geht:  http://www.clever-excel-forum.de/Thread-...ng-stellen
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
Hawkeye Offline
pausiert
ThreadStarter
Registriert seit: 14.04.2016
Version(en): 2013
#3
14.04.2016, 13:25 (Dieser Beitrag wurde zuletzt bearbeitet: 14.04.2016, 13:26 von Hawkeye.)
Hallo Günter

Sorry, hatte den Anhang vergessen Dodgy
Beide Dokumente (ohne sensitive Daten) sind nun im Originalpost angehängt. Allerdings weiss ich nicht, ob die Verknüpfungen bei Dir auch funktionieren (Anderer Pfad)
Suchen
Top
BoskoBiati Offline
नौसिखिया [vidyaarthi]

Registriert seit: 13.04.2014
Version(en): 365
#4
14.04.2016, 14:46 (Dieser Beitrag wurde zuletzt bearbeitet: 14.04.2016, 14:46 von BoskoBiati.)
Hallo,



Code:
=WENN(WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S:$S;VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN(ISTZAHL(SUCHEN(INDIREKT("'"&$D$1&"'!$D$1");[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002));ZEILE($1:$1002));ZEILE(A1))))=0;"";(WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S:$S;VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN(ISTZAHL(SUCHEN(INDIREKT("'"&$D$1&"'!$D$1");[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002));ZEILE($1:$1002));ZEILE(A1))))))
Arbeitsblatt mit dem Namen 'FS'
 ABCDE
2Eintrag Nr.Geschäftsnr.Kundennr.B777Race
3000012014/05500x
4000022014/1110x0
5000032014/13600x
6000042015/00900x
7000052'015/01000x
8000062015/01100x

ZelleFormatWert
A200000Eintrag Nr.
A3000001
A4000002
A5000003
A6000004
A7000005
A8000006

NameBezug
x=Einstellungen!$F$4:$F$5

ZelleFormel
A3=WENN([@[Geschäftsnr.]]="";"";1)
B3{=WENN(WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S:$S;VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN(ISTZAHL(SUCHEN(INDIREKT("'"&$D$1&"'!$D$1");[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002));ZEILE($1:$1002));ZEILE(A1))))=0;"";(WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S:$S;VERKETTEN("*";(INDIREKT("'"&$D$1&"'!$D$1"));"*"));"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN(ISTZAHL(SUCHEN(INDIREKT("'"&$D$1&"'!$D$1");[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002));ZEILE($1:$1002));ZEILE(A1))))))}
C3=WENNFEHLER(SVERWEIS($B3;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B:$H;2;0);"")
D3=WENNFEHLER(SVERWEIS($B3;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B:$H;7;0);"")
E3=WENNFEHLER(SVERWEIS($B3;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B:$I;8;0);"")
A4=WENN([@[Geschäftsnr.]]="";"";A3+1)
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Hawkeye
Suchen
Top
Hawkeye Offline
pausiert
ThreadStarter
Registriert seit: 14.04.2016
Version(en): 2013
#5
14.04.2016, 15:13 (Dieser Beitrag wurde zuletzt bearbeitet: 14.04.2016, 15:13 von Hawkeye.)
Hallo 

Vielen Dank, werde ich gleich testen!
Gibt es eine Variante um die ganzen Spalten zu untersuchen? Bei mir stürzt Excel immer ab, wenn ich die 1002 oder 2002 in der Formel vergrössere.
Ausserdem wüsste ich gerne, wie ich den WERT Fehler umgehen kann, wenn die Geschäftsatenbank (zweites Dokument) geschlossen ist. Bei allen anderen Verknüpfungen kriege ich Werte, ohne das Dokument zu öffnen. Bei dieser Formel geht es leider nicht...
Suchen
Top
BoskoBiati Offline
नौसिखिया [vidyaarthi]

Registriert seit: 13.04.2014
Version(en): 365
#6
14.04.2016, 15:32 (Dieser Beitrag wurde zuletzt bearbeitet: 14.04.2016, 15:32 von BoskoBiati.)
Hallo,


Deine Matrixformeln in Verbindung mit INDIREKT sind schon heftig! Ich sehe gar keinen Grund mit INDIREKT zu arbeiten. Damit das funktioniert, muß die angesprochene Datei sowieso geöffnet sein. Nach dem, was sich mir hier darstellt, ginge das auch so:

Arbeitsblatt mit dem Namen 'FS'
 ABCDE
2Eintrag Nr.Geschäftsnr.Kundennr.B777Race
3000012014/05500x
4000022014/1110x0

ZelleFormel
B3{=WENN(ZEILEN($1:1)>ZÄHLENWENN([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S:$S;$D$1);"";INDEX([Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B$1:$B$2002;KKLEINSTE(WENN(ISTZAHL(SUCHEN($D$1;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$S$1:$S$2002));ZEILE($1:$1002));ZEILE(A1))))}
C3=WENNFEHLER(SVERWEIS($B3;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B:$H;2;0);"")
D3=WENNFEHLER(SVERWEIS($B3;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B:$H;7;0);"")
E3=WENNFEHLER(SVERWEIS($B3;[Geschäftsdatenbank.xlsm]Geschäftsdatenbank!$B:$I;8;0);"")
A3=WENN([@[Geschäftsnr.]]="";"";1)
A4=WENN([@[Geschäftsnr.]]="";"";A3+1)
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Hawkeye
Suchen
Top
Hawkeye Offline
pausiert
ThreadStarter
Registriert seit: 14.04.2016
Version(en): 2013
#7
17.04.2016, 12:15
Vielen Dank für die hilfreichen Antworten, klappt jetzt super! Nur die Frage mit dem "Suchbereich" ist noch offen.
Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#8
17.04.2016, 13:29
Hallöchen,

Excel stürzt vermutlich nicht ab, wenn Du aus den 1002 1003 machst. Smile Also etwas vergrößern sollte schon gehen.

Bei einigen Funktionen hat Excel einfach mit zunehmender Zellenanzahl überproportional viel zu tun. Wenn Du wirklich so viele Daten prüfen musst und Excel stürzt ab, dann geht das nicht mit der Formel. Du müsstest austesten, wie weit Du den Bereich ausdehnen kannst, um noch passabel damit arbeiten zu können. Vor der "abstürzenden Zeilenzahl" kommt bestimmt die mangelnde Performance :22: Wenn das nicht reicht, müsste z.B. eine Datenverarbeitung mittels VBA ran.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
BoskoBiati Offline
नौसिखिया [vidyaarthi]

Registriert seit: 13.04.2014
Version(en): 365
#9
17.04.2016, 13:34
Hallo,

Da die Matrix -Formeln jetzt deutlich reduziert sind, könnte es jetzt mit einem größeren Suchbereich gehen.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Suchen
Top
Thema geschlossen 


Gehe zu:


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