Wie kann ich in einer Excel-Tabelle entsprechend einer Auswahl nur bestimmte Einträge anzeigen lassen?
Moin,
ich habe folgende Tabelle als Beispiel:
Ganz links ist die eigentliche Tabelle, die verschiedene Veranstaltungen enthält. Als Beispiel mal Veranstaltung 1 bis Veranstaltung 10.
Die Tabelle in der Mitte soll als Hilfstabelle dienen, falls man diese überhaupt braucht. Die würde ich dann noch auf einem anderen Tabellenblatt einfügen, aber da ist mir bewusst, dass man dann Tabellenblatt! vor die Formel schreiben muss.
Bei Kategorie 1 stehen Veranstaltung 1, 2, 5 und 6, wie man sehen kann. Wenn ganz rechts über die Auswahlbox „Kategorie 1“ ausgewählt wird, dann sollen ganz links in der Tabelle nur Veranstaltung 1, 2, 5 und 6 angezeigt werden. Wählt man Kategorie 2 aus, sollen nur Veranstaltung 3, 4 und 9 angezeigt werden usw.
Notfalls würde ich es auch ohne die Auswahlbox machen, falls sich das darüber nur schwer umsetzen lässt, und Kategorie 1, 2 oder 3 einfach händisch eintippen, auch wenn es umständlicher wäre.
Man könnte ja für jeden einzelnen Eintrag in der eigentlichen Tabelle eine WENN-Funktion anlegen, dass wenn der Wert in der Auswahlbox übereinstimmt, die entsprechende Veranstaltung angezeigt wird, aber wäre natürlich sehr umständlich, immer eine WENN-Funktion anlegen zu müssen bei einer Vielzahl an Einträgen. Geht ja sicher auch anders, bloß komplizierter - für mich zumindest ^^
Vielen Dank im Voraus!
5 Antworten
Ich gehe mal davon aus, dass die Auswahl-Kategorien in J4 stehen. Das kannst Du entweder automatisieren, indem Du den Wert aus der Auswahlbox dahin schreiben lässt, oder - imho günstiger - indem Du eine Auswahl über die Datenüberprüfung direkt in der Zelle machst (Falls Du nicht weißt wie, frage nochmal nach oder google).
Also: In J4 steht "Kategorie1"oder "Kategorie2"oder "Kategorie3".
Dann habe ich mir da jetzt mal was "zusammengestrickt" über die Bedingte Formatierung:
A3:D8 markieren:
Bedingte Formatierung/ Neue Regel/ Formel zur Ermittlung der zu formatierenden Zellen verwenden/ Werte formatieren, für die diese Formel wahr ist -->:
=("Kategorie" & NICHT(ISTNV(VERGLEICH(A3;$F$4:$F$100;0)))*1+NICHT(ISTNV(VERGLEICH(A3;$G$4:$G$100;0)))*2+NICHT(ISTNV(VERGLEICH(A3;$H$4:$H$100;0)))*3)<>$J$4
Format: --> Zahlen --> Benutzerdefiniert: ;;; [nur drei Semikola]
Ok.
Jetzt werden alle Zellen leer (;;; zeigt NICHTS an, was in der Zelle steht), die nicht unter der Kategorie aufgelistet sind, die in J4 steht.
Die Formel ist etwas heftig und könnte vielleicht optimiert werden, aber das schaffe ich im Moment nicht und funktionieren sollte es.
Klappt es?
Uff, bei der Formel blicke ich überhaupt nicht mehr durch, aber ich probiere es mal ^^
Ich hatte mir von Sub folgende bedingte Formatierung schreiben lassen:
=ODER(UND(A3<>"";$K$6=1);ZÄHLENWENN(BEREICH.VERSCHIEBEN($F$3:$F$8;0;$K$6-1);A3)>0)
Wäre dann eben färben statt ausblenden, aber vermutlich einfacher.
Aber deine Variante probiere ich auch mal aus. Es wird eben so sein, dass man noch einige Kategorien hinzufügt und einige Spalten bzw. Zeilen. Da muss man in einer Formel ja sicher auch nur diverse Spalten- bzw. Zeilenangaben anpassen, richtig?
Vielen Dank!
Stimmt, wenn da "Alle" steht wird bei mir natürlich alles "gelöscht". Da könntest Du eine zusätzliche BF erstellen, die VOR der anderen in der Liste platziert ist:
Formel:
J4="Alle"
Format: --> Zahlen --> Standard
Aber wenn Du mit der WENN-Formel klar kommst, dann vergiss meine Lösung einfach. Um bei der WENN auszublenden statt zu färben reicht es vielleicht statt >0 dann =0 zu schreiben und ;;; zu formatieren. Aber mir dampft der Kopft jetzt zu viel um mich noch darin zu vertiefen.
Hatte ich gerade mal noch probiert mit der zweiten bedingten Formatierung, aber bewirkt leider nichts. Ist aber nicht schlimm.
Sorry, ich dachte ich hätte das korrigiert. Muss absolut sein:
=$J$4= "Alle"
Wen die VOR der anderen in der Liste steht müsste es eigentlich klappen.
Schau vielleicht nochmal, ob Excel nicht automatisch Gänsefüßchen hinzugefügt hat, war nämlich bei mir passiert ("=$J$4=""Alle""").
Aber wenn Du eh zufrieden bist, ist ja auch gut. 😊
Jetzt klappt es, danke :) Ich schaue mir die beiden Varianten noch mal genauer an und entscheide dann, mit welcher ich am besten zurechtkomme bzw. welche hier lieber gesehen ist.
Was hat es eigentlich mit F4:F100, G4:G100, H4:H100 auf sich? Spalte 4 kann ich mir noch denken, weil das eben jeweils das erste Feld in der Hilfstabelle ist. Aber wieso bis Zeile 100? Wird das Ganze bis Zeile 100 überprüft?
Und wenn ich eine vierte Kategorie in Spalte I hinzufügen wollen würde, müsste ich Folgendes hinzufügen, oder?
*3+NICHT(ISTNV(VERGLEICH(A3;$I$4:$I$100;0)
Am Ende steht ja )))*3)<>$J$4
Da aus der 3 dann vermutlich eine 4 machen, richtig?
Kann ich dann ja einfach mal probieren.
Habe es hinbekommen mit noch einer Kategorie :)
Alle richtig.
Ja, die 100 heißt, dass bis da überprüft wird, habe ich mal einfach so eingesetzt. Kannst auch 1000 draus machen, da merkt man in der Performance nicht. Lieber viel zu groß machen.
[MIr ist es mal - peinlicherweise - passiert, dass ich den Bereich bei einer täglichen Auswertung, die eigentlich nur kurzzeitig passieren sollte, auf 1000 Zeilen gesetzt hatte, und dann nach 3 Jahren die Beschwerden kamen, dass die Werte immer gleich blieben. Kann man natürlich auch dynamisch anlegen, aber die "Arbeit" hatte ich mir - leider - gespart]
Und ja, zum Erweitern auf die nächste Spalte:
...)*3+NICHT(ISTNV(VERGLEICH(A3;$I$4:$I$100;0)))*4)<>$J$4
Du siehst, ganz so kompliziert., wie sie aussieht, ist nicht Formel dann doch nicht.
Aber was ich noch erwähnen wollte: Du musst das nicht anpassen, ich wäre auch mit den Farben zufrieden. Das lässt sich vermutlich auch einfacher anpassen, wenn man neue Zeilen bzw. Spalten hinzufügt.
Super Trick mit den drei Semikola!
Für die Formel war mein Ansatz dieser:
=ODER(UND(A3<>"";Auswahl=1);ZÄHLENWENN(BEREICH.VERSCHIEBEN($E$3:$E$8;0;Auswahl-1);A3)>0)
Ohne "Alle":
=ZÄHLENWENN(BEREICH.VERSCHIEBEN($E$3:$E$8;0;$J$6);A3)>0
Ausblenden ist so eine Sache. Richtig ausblenden lassen sich nur Zeilen und Spalten.
Du könntest die Schriftfarbe in Abhängigkeit der Zelle auf den des Hintergrunds setzen, die mit dem Auswahlfeld verknüpft ist.
Das hat aber den Nachteil, dass wenn man die Zelle markiert oder bearbeitet, den Text trotzdem sieht. Außerdem sieht man die Formel immer.
Da wäre es ja fast schon am sinnigsten, die Originaltabelle auf einem versteckten Sheet zu halten und dann auf das Ereignis der Auswahl zu reagieren, indem man mittels VBA die Daten in das sichtbare Sheet kopiert, die dort hinein sollen.
Ich würde die Tabelle in 2 Spalten umbauen. z.B.
Spalte A - Kategorie
Spalte B - Veranstaltungen.
... danach kannst Du über Autofilter die jeweilige Kategorie der Spalte A auswählen. Dabei werden dann alle Veranstaltungen der ausgewählten (gefilterten) Kategorie angezeigt werden.
Nur wird die Tabelle dann ganz schön lang ^^ Wenn ich dich jetzt richtig verstanden habe und alle Veranstaltungen untereinander in einer Spalte stehen und daneben jeweils Kategorie 1, 2 oder 3.
genau.
Wenn die Tabelle Dir zu lang wird, kannst Du die Zeilen, dle vergangene Veranstaltungen betreffen, ja ausblenden
geh mal oben links auf filtern sortieren, erste zeile erst makieren und dann auf filtern sortieren/ filtern
Das machst Du über abhängige Dropdown-Felder, einfach mal danach googlen, da gibt es tausende Anleitungen!
Es klappt fast :) Bei „Alle“ zeigt er mir das von Kategorie 1 an, bei Kategorie 1 das von Kategorie 2 usw. Aber ich sehe gerade, dass du auch gar nicht wissen konntest, dass ich noch „Alle“ als ersten Eintrag habe. Darüber sollen eben anfangs alle Einträge angezeigt werden, damit man bei Bedarf auch eine Gesamtübersicht hat und nicht direkt beim Öffnen der Tabelle eine Auswahl getroffen wurde. Das hatte ich vorhin noch eingefügt, als ich mich für die Wenn-Funktionen entschieden habe.