Registriert seit: 18.10.2019
Version(en): Office 2019
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 | A | B | C | D | E |
| 5 | Überschrift 1 | Überschrift 2 | Überschrift 3 | | Überschrift 1 |
| 6 | Mainz | Köln | Mannheim | | Mainz |
| 7 | Wiesbaden | Düsseldorf | | | Wiesbaden |
| 8 | | Frankfurt | | | Überschrift 2 |
| 9 | | München | | | Köln |
| 10 | | Halle | | | Düsseldorf |
| 11 | | | | | Frankfurt |
| 12 | | | | | München |
| 13 | | | | | Halle |
| 14 | | | | | Überschrift 3 |
| 15 | | | | | Mannheim |
| 16 | | | | | |
| 17 | | | | | |
| Formeln der Tabelle |
| Zelle | Formel | | 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
Registriert seit: 11.04.2014
Version(en): Office 365
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
Registriert seit: 10.04.2014
Version(en): Office 2019
(18.10.2019, 16: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
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht
"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
Folgende(r) 1 Nutzer sagt Danke an LCohen für diesen Beitrag:1 Nutzer sagt Danke an LCohen für diesen Beitrag 28
• Jockel
Registriert seit: 18.10.2019
Version(en): Office 2019
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
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
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)
Registriert seit: 18.10.2019
Version(en): Office 2019
19.10.2019, 13: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.
Ich versuchs aber weiter...
Gruß Brabus
Registriert seit: 16.08.2017
Version(en): 2007 / 2010 / Web
19.10.2019, 13:45
(Dieser Beitrag wurde zuletzt bearbeitet: 19.10.2019, 13: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
Registriert seit: 18.10.2019
Version(en): Office 2019
19.10.2019, 14:00
Hallo Elex,
vilen lieben Dank! Klappt.
Ich habe mittlerweile auch eine Lösung mit Hilfszellen gefunden.
Lösung_2 | A | B | C |
| 5 | Überschrift 1 | Überschrift 2 | Überschrift 3 |
| 6 | Mainz | Köln | Mannheim |
| 7 | Wiesbaden | Düsseldorf | |
| 8 | | Frankfurt | |
| 9 | | München | |
| 10 | | Halle | |
| 11 | | | |
| 12 | 3 | 6 | 2 |
| 13 | | 9 | |
| Formeln der Tabelle |
| Zelle | Formel | | 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
