Tipps & Tricks zum Filter

Die Filterfunktionen in Excel sind äusserts populär und werden auf den unterschiedlichsten Tabellen angewandt. Mit einigen Tipps & Tricks kann man sich die Arbeit damit deutlich vereinfachen.

Filtern über das Kontextmenü (Rechtsklick)

Die herkömmliche Vorgehensweise, um eine Tabelle zu filtern, ist die Selektion via Überschriftszeile. Alternativ kann man jedoch auch innerhalb der Tabelle auf einen Wert klicken, nach dem man filtern möchte. So muss man nicht Werte oder Zahlen (insbesondere komplizierte) im Suchfeld des Filters selbst eingeben oder hineinkopieren. Wie im Screenshot unten ersichtlich ist, kann man so zudem nach weiteren Kriterien filtern, wie beispielsweise der Zell- oder der Schriftfarbe. Dies erlaubt es zum Beispiel, nach negativen und rot eingefärbten Zahlen zu suchen.

Wiederholt man diesen Schritt auf einer zweiten Spalte, dann wird der vorherige Filter nicht entfernt, sondern es behalten beide Kriterien Gültigkeit.

Dein Filter erkennt übrigens automatisch, ob innerhalb der Spalte Texte oder Zahlen stehen. Für Zahlen ist zu erwähnen, dass man Kriterien wie "Kleiner als..." oder auch "Zahlen über oder unter dem Durchschnitt" berücksichtigen kann:

Filter_Zahlenfilter

Der Spezialfilter

Um aus einer bestehenden Tabelle eine Unikatsliste zu erstellen, eignet sich der Spezialfilter. Damit wird bezweckt, den Input für Dropdown-Listen oder Gültigkeitsprüfungen zu generieren. Aus der ersten Abbildung oben soll beispielsweise eine Liste ohne Duplikate für die Spalte D ("Teams") generiert werden. Den Spezialfilter findest Du unter "Daten" → "Erweitert":

Filter_Spezialfilter

Sobald Du auf "Erweitert" klickst, nimm bitte folgende Einstellungen vor: Wähle als Aktion "An eine andere Stelle kopieren", damit Du die Unikatsliste von der Ursprungstabelle trennst. Im Beispiel oben sind mehrere Spalten vorhanden und wir möchten lediglich mit den Werten in Spalte "D" operieren; deshalb passen wir den Listenbereich entsprechend an. Wähle danach noch den Zielbereich, wo die Liste hinkopiert werden soll. Beachte, dass bei "Kopieren nach" häufig Schwierigkeiten auftreten, da anscheinend nicht in ein anderes Tabellenblatt geschrieben werden kann. Es tritt die Meldung auf "Nur gefilterte Daten können in das aktive Blatt kopiert werden.". Es gibt jedoch eine Möglichkeit, auf die ich gleich eingehen werde. Für den Moment führen wir die Unikatsliste rechts von der bestehenden Tabelle ein - also beispielsweise ab Zelle "Y1". Damit die Duplikate entfernt werden, ist noch der entsprechende Haken bei "Keine Duplikate" zu setzen:

Filter_SpezialfilterSettings

Zum Abschluss gehe ich hier noch darauf ein, wie der Spezialfilter auf ein anderes Tabellenblatt angewandt werden kann. Selektiere hierzu als allererstes das Ziel-Tabellenblatt - also da, wo das gefilterte Ergebnis angezeigt werden soll. Wenn Du nun auf den Spezialfilter gehst ("Daten" → "Erweitert"), dann kannst Du den Listenbereich auf das Ursprungstabellenblatt beziehen. Die Einstellungen lauten demnach wie folgt:

Filter_SpezialfilterSettings2

Das Resultat: Die gefilterten Werte werden nun korrekt auf einem separaten Blatt wie zum Beispiel "Parameter" aufgeführt.

Scroll Bars und Spin Buttons (Formularsteuerelemente)

Möchtest Du Deine Formulare und Reports um weitere Funktionalitäten aufwerten? Hier werden die zwei Schaltflächen "Scroll Bars" und "Spin Buttons" vorgestellt (Deutsch: "Scrollleisten" und "Drehfelder"). Wir verwenden hier die regulären "Formularsteuerelemente" und es werden zwei konkrete Anwendungsfälle illustriert. Es gäbe zudem die Möglichkeit "ActiveX-Steuerelemente" einzupflegen. Diese bieten noch mehr Optionen in der individuellen Gestaltung (z.B. visuelles Design oder Verhalten). Aufgrund der Komplexität beschränken wir uns in dieser Einführung jedoch auf Formularsteuerelemente und verwenden keinerlei Makros. Um ein solches Element einzufügen, gehe unter dem Menüband auf "Entwicklertools" → "Einfügen" und wähle das entsprechende Objekt aus. Das Drehfeld und die Scrollleiste findest Du hier:

ScrollBarMenu   SpinButtonMenu

Es ist durchaus möglich, dass bei Dir der Menüpunkt "Entwicklertools" noch nicht vorhanden ist. Gehe dazu in die Excel-Optionen (oder via Rechtsklick auf das Menüband → "Menüband anpassen") und selektiere bei den "Hauptregisterkarten" die Entwicklertools:

Unten werden Dir nun zwei Beispiele und mögliche Anwendungen dieser Formularsteuerelemente gezeigt.

Scrollleiste / Scroll Bar

In einem ersten Beispiel zeige ich Dir eine Scrollleiste, mit der wir mittels eines Reglers einen Prozentwert justieren können. Dadurch lassen sich beispielsweise direkt und interaktiv Auswirkungen auf Prämien und Boni veranschaulichen. Unten aufgeführt ist ein Umsatzziel von CHF 1'000'000, das es zu erreichen gibt. Mit einer Scroll Bar kann nun der tatsächlich generierte Umsatz justiert und ein "Szenario" simuliert werden; der Gewinn wird direkt unterhalb angezeigt (setze dazu einfache Formeln wie "=C8-C7"):

ScrollBarData  ScrollBarData2

Mit dem Regler in Zeile 4 kann der Prozentwert eingestellt werden. Beachte: Die Zelle "D3" wird als Hilfsfeld verwendet - hier wird der "Output" der Scroll Bar dargestellt. In Feld "B3" wird dann dieser Wert als Prozent dargestellt (=D3/100). Die Optionen und Einstellungen dieser Scrollleiste lauten wie folgt:

ScrollBarProperty

Mit "Seitenwechsel" wird konfiguriert, wie stark der Marker springt, sobald man einen der beiden Pfeile betätigt. Die "Schrittweite" betrifft die Einstellung und das Intervall, sobald man neben den Regler klickt (auf die Fläche daneben, nicht auf die Pfeile selbst).

Um Prozentwerte mit Dezimalstellen zu erlangen, nimm beispielsweise diese Optionen vor:

ScrollBarProperty2

Du siehst: Der Maximalwert ist nun auf 1'000 eingestellt. Die Formel mit Bezug auf die Hilfszelle wird ebenfalls durch 1'000 geteilt. Das Endresultat:

ScrollBarPercent

Drehfeld / Spin Button

Um ein Drehfeld zu illustrieren, gehen wir von einem Kuchenrezept aus. Ein Feld - eben ein "Drehfeld" - dient dazu, die Anzahl Personen und somit die Mengenangaben für den Kuchen zu verändern. Wir pflegen wie im Beispiel oben Hilfszellen ein (Spalte F in der Abbildung unten), wo die Mengenangaben für 1 Person angegeben werden. Innerhalb des Rezepts kann darauf referenziert werden (für Zeile 8: "=F8*$B$5").

SpinButtonData

Die Optionen dazu lauten wie folgt:

SpinButtonProperty

Die Darstellung der Zutaten als Bruch erlangst Du in der regulären Zellformatierung (wähle "Bruch" anstatt z.B. "Zahl" oder "Währung").

(Dieses Rezept stammt von Fooby)

Weitere Formatierungen

Deine Scroll Bar kann horizontal (wie oben) oder aber auch vertikal ausgerichtet sein. Achte beim Einfügen darauf, wie Du das Objekt und dessen Grösse bestimmst. Hier ein Beispiel, wie Du vertikale oder horizontale Scrollleisten einfügen kannst:

ScrollBarsOrientation

Alternativ kannst Du im Nachgang zudem über die Optionen des Steuerelements die Grösse anpassen:

ScrollBarSize

(Beachte: Du musst die Höhe oder Breite allenfalls zweimal eingeben, da die Proportionen des Objekts möglicherweise beibehalten werden.)

Mehrere Tabellenblätter auf einmal ein- und ausblenden

In Excel können Tabellenblätter ein- und ausgeblendet werden. Dies bezweckt, dass dem Benutzer der Datei nur die relevanten Inhalte angezeigt werden. Unten aufgeführt ist ein Beispiel eines Berichtes mit drei verschiedenen Tabellenblättern: Dem "Bericht" selbst sowie die diesem zugrundeliegenden "Daten/Data" und ein Steuerungs-Blatt mit dem Namen "Parameter".

TabellenblaetterAlle

Die Empfänger dieses Berichtes interessieren lediglich die aufbereiteten Informationen. Die unübersichtlichen Quelldaten oder allfällige Steuerelemente im Hintergrund müssen nur in Einzelfällen oder bei Rückfragen aufgezeigt und offengelegt werden. Deshalb sollte der- oder diejenige, die den Bericht pflegt, diese Blätter vor dem Versand ausblenden. Dieser Schritt kann mittels Rechtsklick auf ein beliebiges Tabellenblatt → "Ausblenden" vollzogen werden:

TabellenblattAusblenden

Um mehrere "Sheets" auszublenden, sind die gewünschten Tabellenblätter im Vornherein zu selektieren (mittels CTRL + anklicken - oder im Falle von einer Vielzahl: Das erste Blatt anklicken, Shift drücken und das letzte Blatt ebenfalls anklicken; damit werden alle dazwischenliegenden Sheets ebenfalls markiert). Daraufhin erfolgt ebenfalls ein Rechtsklick auf ein beliebiges Tabellenblatt → "Ausblenden".

Um ein Tabellenblatt wieder einzublenden, geht man via Rechtsklick auf ein Blatt → "Einblenden". Es erscheint dann eine Liste mit sämtlichen ausgeblendeten Tabellenblätter:

TabellenblattEinblenden

Es ist das jeweilige Blatt auszuwählen und mit "OK" wird es alsbald eingeblendet. In der obigen Liste können jedoch nicht mehrere Blätter gleichzeitig selektiert und angezeigt werden. Das kann bei grossen Dateien zeitaufwändig und nervig sein. Um jedoch direkt alle ausgeblendeten Sheets anzuzeigen, ist ein Makro notwendig: Gehe via ALT+F11 oder mittels Rechtsklick auf ein Tabellenblatt → "Code anzeigen" in die VBA-Umgebung und füge ein neues Modul ein (Rechtsklick auf der linken Seite bei den Projekten → "Einfügen" →  "Modul". Füge dort dann den nachfolgenden Code ein:

Sub Einblenden()

Dim Tabellenblatt As Object

For Each Tabellenblatt In Worksheets
Tabellenblatt.Visible = True
Next Tabellenblatt

End Sub

Sobald dieses Makro ausgeführt wird, werden alle ausgeblendeten Tabellenblätter direkt wieder eingeblendet.