Excel Zeilen ausblenden, in denen eine Formel hinterlegt ist

[eddy 92]

Guten Abend zusammen,
um mir die Arbeit mit einem großen Datenbestand etwas zu erleicherten arbeite ich an einer Excel Liste bestehend aus 4 Tabellen.

Tabelle 1 ist die Referenztabelle, in der ich alle Daten geordnet eintrage.

Tabelle 2 - 4 bilden jeweils Unterkategorien, die nur einen bestimmten Datenbereich von Tabelle 1 beinhalten sollen.
Mit verschiedenen Wenn Funktionen kann ich die Tabellen 2-4 so vorbereiten, dass sie alle relevanten Angaben aus Tabelle 1 übernehmen.
Das Problem ist jetzt, dass in den Tabellen 2-4 entsprechend viele vermeindlich leere Zeilen entstehen, wenn die Bedingungen der Formel nicht erfüllt sind. Dadurch sind diese optisch nicht so gut lesbar und auch nicht so wie von mir vorgesehen.
Vermeindlich leer sind diese Zeilen nur, weil es so aussieht, in Wirklichkeit ist da eine Formel hinterlegt.

Ich weiß, eigentlich kann man mit der Filterfunktion in Tabelle 1 viel rausholen und das ist auch meine präferierte Art. Man hat mich jedoch darum gebeten es so wie oben beschrieben, zu probieren.
Da ich mir das viele Abtippen ersparen möchte und eine optisch kohärente Tabelle haben möchte, suche ich bereits den ganzen Tag nach einer Lösung.

Gibt es eine Möglichkeit in Excel die Tabellen 2-4 so einzustellen, dass die vermeintlich leeren Zeilen automatisch ausgeblendet werden? Vielleicht habt ihr einen Tipp für mich. Ja, Zeilen markieren, Rechtsklick, Ausblenden, kenne ich schon. Ich suche nach einer eleganteren Lösung. Danke
Gi

 

[steve1da]

Hoila,
du kannst schon mit den richtigen Formeln dafür sorgen, dass in Tabelle2-4 nur die Infos aus Tabelle 1 kommen, die zutreffend für diese Tabellen sind. Dazu müsste man aber den Aufbau kennen und die eingesetzte Excel-Version.
Gruß,
steve1da

 

[eddy 92]

Ich nutze auf der Arbeit Excel 2010, im HO 2016.

Tabelle 1 enthält Angaben zur Organisation, Adresse, Art der Nutzung, Vertragsnummer, Art der Energiequelle (Strom, Gas, Wasser), Zählernummer

Tabelle 2 soll beispielsweise alles übernehmen, wenn die Bedingung Strom in der jeweiligen Zeile 1 in Tabelle 1 zutrifft.
An sich mache ich das mit einer Wenn Funktion, die ich dann für jede Zeile in Tabelle 2 anpasse und dann "runterziehe".
Die geforderten Daten aus Tabelle 1 werden übernommen, wenn die Bedingung Strom erfüllt ist, alle anderen Zeilen bleiben leer. Am Ende ist dann halt ein Flickenteppich von Angaben zu sehen, den ich vermeiden möchte.

 

[steve1da]

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$A$2:$A$1000)/(Tabelle1!$E$2:$E$1000="strom");ZEILE(A1)));"")
Hier mal das Beispiel für Strom, wenn es in Spalte E auftaucht. Hab das jetzt mal bis Zeile 1000 begrenzt, kannst du anpassen.
Die Formel nach rechts und nach unten ziehen.

 

[sierra-echo]

Versuche mal die entsprechenden Zellen mit "Bedingte Formatierung" zu formatieren. (Start --> bedingte Formatierung --> neue Regel bzw. Regeln verwalten)
Zeilen ausblenden funktioniert bei mir mit STRG-9.
Habe EXCEL 2019

 

[eddy 92]

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$A$2:$A$1000)/(Tabelle1!$E$2:$E$1000="strom");ZEILE(A1)));"")
Hier mal das Beispiel für Strom, wenn es in Spalte E auftaucht. Hab das jetzt mal bis Zeile 1000 begrenzt, kannst du anpassen.
Die Formel nach rechts und nach unten ziehen.

Danke für den Tipp.
Ich verstehe die Aggregatfunktion in diesem Zusammenhang nicht. Wie kommst du auf die Inhalte in dieser Formel?

 

[steve1da]

Hier gibt es eine Erklärung dazu.
https://thehosblog.com/2013/05/08/excel-mehrere-suchkriterien-mehrere-treffer-aggregat/
Es wird der Inhalt der kleinsten Zeile ausgegeben, wenn in dieser Zeile in Spalte E das Wort Strom steht, dann der Inhalt der 2.kleinsten Zeile, usw.

 

[eddy 92]

Sorry, aber so richtig verstehe ich das nicht.

 

[steve1da]

Die Formel sucht ab E2 die erste Zelle in der das Wort Strom steht. Wenn das Wort gefunden wirst wird der Wert aus Spalte A zurückgegeben. Dann wird weiter gesucht und der jeweilige Wert aus Spalte A zurückgegeben. Zeile(A1) fungiert dabei als Zähler. Zeile(A1) als Formel in eine Zelle eingegeben ergibt den Wert 1. Zieht man die Formel nach unten wird daraus 2, 3, 4.... Damit ermittelt man die erste, zweite, dritte, vierte.... Zelle in Spalte E, in der das Wort Strom steht. Also die kleinste Zeilennummer in Spalte E, die zweitkleinste usw. Bei Aggregat() steht die 15 für KKleinste.

 

[eddy 92]

OK, ich habe die Formel an meine Liste angepasst. Es funktioniert so wie ich mir das vorgestellt habe.

Vielen Dank.

Warum WENNFEHLER mit IDEX kombinieren?

 

[steve1da]

Wenn zB nur 5 mal Strom irgendwo in Spalte E steht, würde in der 6. Zeile mit der Formel #ZAHL erscheinen. Das wird mit Wennfehler() einfach ausgeblendet.

 

[eddy 92]

Noch eine Frage. Die Zeilen in Tabelle 1, die beispielsweise leer sind werden in Tabelle 2 nach anwenden der Funktion mit 0 ausgegeben. Kann man dies irgendwie unterbinden?

 

[steve1da]

3 Möglichkeiten.
1) =WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$A$2:$A$1000)/(Tabelle1!$E$2:$E$1000="strom");ZEILE(A1)));"")&""
2) =WENNFEHLER(WENN(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$A$2:$A$1000)/(Tabelle1!$E$2:$E$1000="strom");ZEILE(A1)))=0;"";INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$A$2:$A$1000)/(Tabelle1!$E$2:$E$1000="strom");ZEILE(A1))));"")
3) Zellen benutzerdefiniert formatieren: 0;-0;;@

Bei 1) wird dadurch allerdings alles zu TEXT.

 

[eddy 92]

Danke, ich probiere es aus.