Clever-Excel-Forum › Office-Anwendungen › Excel v
Lückenlose Liste erstellen

Thema geschlossen 
Baumstrukturmodus
Lückenlose Liste erstellen
Brabus Offline
pausiert
Registriert seit: 18.10.2019
Version(en): Office 2019
#1
18.10.2019, 17:04
Hallo zusammen,
ich bräuchte Hilfe bei nachfolgendem Problem:
Eine Tabelle mit 3 Spalten soll in einer Liste ohne Lücken untereinander stehen. Das klappt mit der dargestellten Formel gut.

Frage: Wenn die leeren Felder (A8:A10; B10; C7:C10) per Formel als leer (="") dargestellt werden, wird die Liste mit Leerstellen unterbrochen.
Wie lässt sich das lösen; Ziel ist die Lückenlose Liste, ohne dass ich die leeren Felder manuell leeren muss.

Danke
Gruß Brabus
Neu

ABCDE
5Überschrift 1Überschrift 2Überschrift 3Überschrift 1
6MainzKölnMannheimMainz
7WiesbadenDüsseldorfWiesbaden
8FrankfurtÜberschrift 2
9MünchenKöln
10HalleDüsseldorf
11Frankfurt
12München
13Halle
14Überschrift 3
15Mannheim
16
17
Formeln der Tabelle
ZelleFormel
E5=WENN(ZEILE(A1)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A1);1);
 WENN(ZEILE(A1) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A1)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A1) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A1)-ANZAHL2($A$5:$B$10);1);"")))
E6=WENN(ZEILE(A2)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A2);1);
 WENN(ZEILE(A2) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A2)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A2) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A2)-ANZAHL2($A$5:$B$10);1);"")))
E7=WENN(ZEILE(A3)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A3);1);
 WENN(ZEILE(A3) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A3)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A3) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A3)-ANZAHL2($A$5:$B$10);1);"")))
E8=WENN(ZEILE(A4)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A4);1);
 WENN(ZEILE(A4) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A4)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A4) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A4)-ANZAHL2($A$5:$B$10);1);"")))
E9=WENN(ZEILE(A5)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A5);1);
 WENN(ZEILE(A5) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A5)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A5) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A5)-ANZAHL2($A$5:$B$10);1);"")))
E10=WENN(ZEILE(A6)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A6);1);
 WENN(ZEILE(A6) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A6)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A6) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A6)-ANZAHL2($A$5:$B$10);1);"")))
E11=WENN(ZEILE(A7)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A7);1);
 WENN(ZEILE(A7) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A7)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A7) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A7)-ANZAHL2($A$5:$B$10);1);"")))
E12=WENN(ZEILE(A8)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A8);1);
 WENN(ZEILE(A8) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A8)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A8) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A8)-ANZAHL2($A$5:$B$10);1);"")))
E13=WENN(ZEILE(A9)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A9);1);
 WENN(ZEILE(A9) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A9)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A9) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A9)-ANZAHL2($A$5:$B$10);1);"")))
E14=WENN(ZEILE(A10)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A10);1);
 WENN(ZEILE(A10) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A10)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A10) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A10)-ANZAHL2($A$5:$B$10);1);"")))
E15=WENN(ZEILE(A11)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A11);1);
 WENN(ZEILE(A11) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A11)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A11) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A11)-ANZAHL2($A$5:$B$10);1);"")))
E16=WENN(ZEILE(A12)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A12);1);
 WENN(ZEILE(A12) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A12)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A12) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A12)-ANZAHL2($A$5:$B$10);1);"")))
E17=WENN(ZEILE(A13)<=ANZAHL2($A$5:$A$10);                                                     INDEX($A$5:$A$10;  ZEILE(A13);1);
 WENN(ZEILE(A13) -  ANZAHL2($A$5:$A$10)<=  ANZAHL2($B$5:$B$10);  INDEX($B$5:$B$10;  ZEILE(A13)-ANZAHL2($A$5:$A$10);1);
  WENN(ZEILE(A13) - ANZAHL2($A$5:$B$10)<=  ANZAHL2($C$5:$C$10);  INDEX($C$5:$C$10;  ZEILE(A13)-ANZAHL2($A$5:$B$10);1);"")))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Suchen
Top
Klaus-Dieter Offline
Excel und VBA
Registriert seit: 11.04.2014
Version(en): Office 365
#2
18.10.2019, 17:51
Hallo,


die Darstellung in Spalte E ist nicht das, was man unter einer Liste versteht, wie sie in Excel verwendet werden sollte. Wozu die Zwischenüberschriften?

Wie oft soll das gemacht werden? Wenn das eher selten vorkommt, könnte man das per kopieren und einfügen lösen. Mit Formeln würde ich das schon gar nicht lösen.
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
Homepage Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#3
18.10.2019, 18:32
(18.10.2019, 17:51)Klaus-Dieter schrieb: Hallo,
... Wozu die Zwischenüberschriften?
...Mit Formeln würde ich das schon gar nicht lösen.

Hi Klaus-Dieter, da bin ich bei dir. Das ginge (auch dynamisch) das mit Power Query...
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
LCohen Offline
Benutzer
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
#4
18.10.2019, 19:35
http://xxcl.de/0044.htm
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
[-] Folgende(r) 1 Nutzer sagt Danke an LCohen für diesen Beitrag:
  • Jockel
Suchen
Top
Brabus Offline
pausiert
ThreadStarter
Registriert seit: 18.10.2019
Version(en): Office 2019
#5
19.10.2019, 08:25
Hallo Klaus-Dieter,
erstmal danke, dass du dich mit meinem Anliegen beschäftigt hast. Ich habe hier nur eine kleine Beispieltabelle eingestellt.
Real handelt es sich um 3 mal 15 Auflistungen (Spalten) mit ca. 40 Eintragungen. Die Datenbasis ändert sich mehrfach pro Jahr.

Insofern ist für mich eine Formel-Lösung schon sinnvoll.
Die Überschriften sind deshab nötig, weil im Original später als Überschrift der Zuständigkeitsbereich angezeigt wird (mit Formel aus einer größeren Datensammlung extrahiert), darunter stehen "KostenstellenNr. / Kostenstellenbezeichnung" (ebenfalls aus der Datensammlung extrahiert). Diese 15 Auflistungen (SpaltenA:E) haben unterschiedliche Länge. Das Ganze möchte ich in einer lückenlosen Auflistung haben.

Ich muss halt bisher nur dei den leeren Zellen der Ausgangsspalten manuell löschen, um ein lückenloses Ergebnis zu bekommen. Ich versuche so was wie minus ZÄHLENWENN(Bereich;"leere Zelle") komme aber irgendwie nicht zum Ziel.

Vielleicht hat ja noch jemand eine Idee.

Gruß Brabus
Suchen
Top
RPP63 Offline
Mitten im Ruhrpott
Registriert seit: 12.10.2014
Version(en): 365 Insider (64 Bit)
#6
19.10.2019, 08:30
Steht doch im Link von LCohen!
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Suchen
Top
Brabus Offline
pausiert
ThreadStarter
Registriert seit: 18.10.2019
Version(en): Office 2019
#7
Sad  19.10.2019, 14:16
Hallo zusammen,
danke an euch alle für eure interessanten Hinweise und Links!

Leider stehe ich irgendwie auf dem Schlauch. Ich schaff es nicht das umzusetzen auf mein Beispiel für den Bereich A5:C12.
Falls jemand Zeit hat mir auf die Sprünge zu helfen wäre ich dankbar. Undecided

Ich versuchs aber weiter...
Gruß Brabus
Suchen
Top
Elex Offline
Benutzer
Registriert seit: 16.08.2017
Version(en): 2007 / 2010 / Web
#8
19.10.2019, 14:45 (Dieser Beitrag wurde zuletzt bearbeitet: 19.10.2019, 14:45 von Elex.)
Hi

Hier mal etwas anders dargestellt.

____|_______A______|_______B______|_______C______|_D|_______E______|___F___|
   1|              |              |              |  |              |       |
   2|              |              |              |  |              |       |
   3|              |              |              |  |              |       |
   4|              |              |              |  |              |       |
   5|Überschrift 1 |Überschrift 2 |Überschrift 3 |  |Überschrift 1 | 1,0005|
   6|Mainz         |Köln          |Mannheim      |  |Mainz         | 1,0006|
   7|Wiesbaden     |Düsseldorf    |              |  |Wiesbaden     | 1,0007|
   8|              |Frankfurt     |              |  |Überschrift 2 | 2,0005|
   9|              |München       |              |  |Köln          | 2,0006|
  10|              |Halle         |              |  |Düsseldorf    | 2,0007|
  11|              |              |              |  |Frankfurt     | 2,0008|
  12|              |              |              |  |München       | 2,0009|
  13|              |              |              |  |Halle         |  2,001|
  14|              |              |              |  |Überschrift 3 | 3,0005|
  15|              |              |              |  |Mannheim      | 3,0006|

Beide Formeln nach unten ziehen.
E5    =INDEX($A$1:$C$12;REST(F5;1)*10000;F5)
F5    =AGGREGAT(15;6;(SPALTE($A$5:$C$12)+ZEILE($A$5:$C$12)%%)/($A$5:$C$12<>"");ZEILE(A1))

Gruß Elex
Suchen
Top
Brabus Offline
pausiert
ThreadStarter
Registriert seit: 18.10.2019
Version(en): Office 2019
#9
Smile  19.10.2019, 15:00
Hallo Elex,
vilen lieben Dank! Klappt.

Ich habe mittlerweile auch eine Lösung mit Hilfszellen gefunden.

Lösung_2

ABC
5Überschrift 1Überschrift 2Überschrift 3
6MainzKölnMannheim
7WiesbadenDüsseldorf
8Frankfurt
9München
10Halle
11
12362
139
Formeln der Tabelle
ZelleFormel
C7=""
A8=""
C8=""
A9=""
C9=""
A10=""
C10=""
A12=ANZAHL2($A$5:$A$10)-ZÄHLENWENN($A$5:$A$10;"")
B12=ANZAHL2($B$5:$B$10)-ZÄHLENWENN($B$5:$B$10;"")
C12=ANZAHL2($C$5:$C$10)-ZÄHLENWENN($C$5:$C$10;"")
B13=SUMME(A12;B12)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

In E5: =WENN(ZEILE(A1)<=$A$12;                 INDEX($A$5:$A$10;  ZEILE(A1);1);
            WENN(ZEILE(A1) -  $A$12<=  $B$12;  INDEX($B$5:$B$10;  ZEILE(A1)-$A$12;1);
            WENN(ZEILE(A1) - $B$13<=  $C$12;   INDEX($C$5:$C$10;  ZEILE(A1)-$B$13;1);"")))

Danke und schönes WE
Brabus
Suchen
Top
Thema geschlossen 


Gehe zu:


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