Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Inhalte mehrerer Spalte ohne Leerzellen darstellen

Inhalte mehrerer Spalte ohne Leerzellen darstellen
hennerich
Hallo Forum,
In diesem Beispiel (http://www.excelformeln.de/formeln.html?welcher=43) kann man Spalten mit Leerzellen ohne Lücken darstellen.
Bei mir sollen es insgesamt 4 Spalten sein (I, J, K und L) und die Daten (bzw. auch Leerzellen) befinden sich in den Spalten 46 - 72. Die Ausgabespalten sollen C89 bis C101 sein.
Ich habe das Beispiel versucht an meine Bedürfnisse anzupassen (weil dort ja mit A1 begonnen wird), leider funktioniert es nicht.
Habt ihr eine Idee wie ich das richtig anstelle?
Vielen Dank für deine (eure) Hilfe.
Grüße
Henri
AW: Inhalte mehrerer Spalte ohne Leerzellen darstellen
20.12.2011 15:46:47
Christian
Hallo Henri,
für den Bereich I46-J72 sähe das so aus:
{=WENN(ZEILE(A1)>ANZAHL2($I$46:$J$72);"";WENN(ZEILE(A1)>ANZAHL2($I$46:$I$72);INDEX(J$46:J$72; KKLEINSTE(WENN(J$46:J$72<>"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$I$72)));INDEX(I$46:I$72; KKLEINSTE(WENN(I$46:I$72<>"";ZEILE($1:$27));ZEILE(A1))))) }
Erweitern auf 3 bzw. 4 Spalten ist reine Fleißarbeit.
Beachte: Max. Länge von Matrixformeln (bei XL2003 waren das 255 Zeichen)
Gruß
Christian
AW: Inhalte mehrerer Spalte ohne Leerzellen darstellen
21.12.2011 14:27:29
hennerich
Hallo Christian,
vielen Dank für dein Feedback. Ich bin noch nicht genau hinter die Funktionsweise der Formel gestiegen. Meine Frage bzg. der Fleißarbeit ist nun, muss ich nur die entsprechende Range anpassen (NZAHL2($I$46:$L$72)) oder müssen noch Teilbereiche in der Formel hinzugefügt werden (z.B. INDEX(J$46:J$72;KKLEINSTE(WENN(J$46:J$72"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$I$72)));)
Grüße
Henri
Anzeige
AW: Inhalte mehrerer Spalte ohne Leerzellen darstellen
21.12.2011 16:45:26
Christian
hi,
die Funktionsweise ist recht simple:
wenn die "aktuelle Zeile" (in Formel: Zeile(A1) ff) größer ist als Einträge in I46:J72 existieren, dann leere Zelle,
sonst:
wenn die "aktuelle Zeile" größer ist als Einträge in I46:I72 existieren, dann liste die Einträge im Bereich J46:J72,
sonst:
liste die Einträge im Bereich I46:I72.
Du musst die Formel also entsprechend erweitern, nicht nur einen Bereich anpassen.
Gruß
Christian
AW: Inhalte mehrerer Spalte ohne Leerzellen darstellen
21.12.2011 19:10:19
hennerich
Hallo Christian,
ich habe mal versucht die Formel zu erweitern und etwas übersichtlicher darzustellen:

=WENN(ZEILE(A1)>ANZAHL2($I$46:$L$72);
WENN(ZEILE(A1)>ANZAHL2($I$46:$I$72);
INDEX(J$46:J$72;KKLEINSTE(WENN(J$46:J$72"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$I$72))) _
_
WENN(ZEILE(A1)>ANZAHL2($I$46:$J$72);
INDEX(K$46:K$72;KKLEINSTE(WENN(K$46:K$72"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$J$72) _
_
));
WENN(ZEILE(A1)>ANZAHL2($I$46:$K$72);
INDEX(L$46:L$72;KKLEINSTE(WENN(L$46:L$72"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$K$  _
_
72)));
INDEX(I$46:I$72;KKLEINSTE(WENN(I$46:I$72"";ZEILE($1:$27));ZEILE(A1)))
)
)
)
)

Leider funktioniert sie so noch nicht. Ich bekomme nach dem alle Vorkommen in I und J gefunden worden #ZAHL! als Fehlermeldung. Nicht aber die Vorkommen in den Spalten K und L. Die Anzahl der Einträge inklusive der #ZAHL! Vorkommen stimmt aber mit der Anzahl der Werte in meiner Matrix überein.
Was habe ich falsch gemacht?
Grüße
Henri
Anzeige
Inhalte aus 4 Spalten lückenlos
21.12.2011 20:18:15
Erich
Hallo Henri,
zum Vergleichen:
 CDEFGHIJKL
44ohne Lücken            
45Cognac         
46Calvados     Cognac   
47Rotwein      xxx1  
48Bier       Bar 
49Ratzeputz        yyy
50xxx1     Calvadosxxx2  
51xxx2         
52xxx3     Rotweinxxx3 rrr
53xxx4     Bier Bistro 
54xxx5       Trattoria 
55asdas      xxx4  
56jjj         
57Bar     Ratzeputzxxx5  
58Bistro        fff
59Trattoria         
60yyy      asdas  
61rrr         
62fff         
63ww         
64ee         
65rr         
66tt         
67          
68          
69         ww
70         ee
71         rr
72       jjj tt
73          

Formeln der Tabelle
ZelleFormel
C45{=WENN(ZEILE(I1)>ANZAHL2(I:L); "";
WENN(ZEILE(I1)>ANZAHL2(I:K);
INDEX(L:L;KKLEINSTE(WENN(NICHT(ISTLEER(L$46:L$72)); ZEILE($46:$72)); ZEILE(I1)-ANZAHL2(I:K)));
WENN(ZEILE(I1)>ANZAHL2(I:J);
INDEX(K:K;KKLEINSTE(WENN(NICHT(ISTLEER(K$46:K$72)); ZEILE($46:$72)); ZEILE(I1)-ANZAHL2(I:J)));
WENN(ZEILE(I1)>ANZAHL2(I:I);
INDEX(J:J;KKLEINSTE(WENN(NICHT(ISTLEER(J$46:J$72)); ZEILE($46:$72)); ZEILE(I1)-ANZAHL2(I:I)));
INDEX(I:I;KKLEINSTE(WENN(NICHT(ISTLEER(I$46:I$72)); ZEILE($46:$72)); ZEILE(I1))))
)
)
)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige
AW: Inhalte aus 4 Spalten lückenlos
21.12.2011 20:50:04
hennerich
Hallo Erich,
vielen Dank für deine Antwort. Da ich nicht die ganzen Spalten verwenden möchte, habe ich noch eine Änderung an deiner Formel durchgeführt:
=WENN(ZEILE(I1)>ANZAHL2($I$46:$L$72);
WENN(ZEILE(I1)>ANZAHL2($I$46:$K$72);
INDEX(L:L;KKLEINSTE(WENN(NICHT(ISTLEER(L$46:L$72));ZEILE($46:$72));ZEILE(I1)-ANZAHL2($I$46:$ _
K$72)));
WENN(ZEILE(I1)>ANZAHL2($I$46:$J$72);
INDEX(K:K;KKLEINSTE(WENN(NICHT(ISTLEER(K$46:K$72));ZEILE($46:$72));ZEILE(I1)-ANZAHL2($I$ _
46:$J$72)));
WENN(ZEILE(I1)>ANZAHL2($I$46:$I$72);
INDEX(J:J;KKLEINSTE(WENN(NICHT(ISTLEER(J$46:J$72));ZEILE($46:$72));ZEILE(I1)-ANZAHL2($I$ _
46:$I$72)));
INDEX(I:I;KKLEINSTE(WENN(NICHT(ISTLEER(I$46:I$72));ZEILE($46:$72));ZEILE(I1)))))))
Ich war ein wenig verwirrt, als die Matrix Angabe der Index Funktion nicht mit einer eingegrenzten Range klar gekommen ist. Daher habe ich dort die ganzen Spalten (z.B.K:K) gelassen und nur den Rest eingegrenzt.
Vielen Dank!
Henri
Anzeige
AW: Inhalte mehrerer Spalte ohne Leerzellen darstellen
21.12.2011 20:46:51
Christian
Hi Henri,
mein Vorschlag:
{=WENN(ZEILE(A1)>ANZAHL2($I$46:$L$72);"";WENN(ZEILE(A1)>ANZAHL2($I$46:$K$72);INDEX(L$46:L$72; KKLEINSTE(WENN(L$46:L$72<>"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$K$72)));WENN(ZEILE(A1)> ANZAHL2($I$46:$J$72);INDEX(K$46:K$72;KKLEINSTE(WENN(K$46:K$72<>"";ZEILE($1:$27));ZEILE(A1) -ANZAHL2($I$46:$J$72)));WENN(ZEILE(A1)>ANZAHL2($I$46:$I$72);INDEX(J$46:J$72; KKLEINSTE(WENN(J$46:J$72<>"";ZEILE($1:$27));ZEILE(A1)-ANZAHL2($I$46:$I$72)));INDEX(I$46:I$72; KKLEINSTE(WENN(I$46:I$72<>"";ZEILE($1:$27));ZEILE(A1))))))) }
kommst du damit klar?
Gruß
Christian
PS: Erichs Formel knallt bei mir aufgrund zu vieler Verschachtelungen...
Anzeige
AW: Inhalte mehrerer Spalte ohne Leerzellen darstellen
21.12.2011 21:05:55
hennerich
Hallo Cristian,
das funktioniert auch. Vielen Dank!
Henri

325 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige