Horizontale Spaltenfilterung in Excel

Wenn Sie kein ganz unerfahrener Benutzer sind, dann müssen Sie bereits bemerkt haben, dass 99% von allem in Excel darauf ausgelegt sind, mit vertikalen Tabellen zu arbeiten, in denen Parameter oder Attribute (Felder) durch die Spalten gehen und Informationen zu Objekten oder Ereignissen angeordnet sind in den Zeilen. Pivot-Tabellen, Zwischensummen, Kopieren von Formeln per Doppelklick – alles ist speziell auf dieses Datenformat zugeschnitten.

Allerdings gibt es keine ausnahmslosen Regeln und ziemlich häufig werde ich gefragt, was zu tun ist, wenn in der Arbeit eine Tabelle mit horizontaler semantischer Ausrichtung oder eine Tabelle, bei der Zeilen und Spalten das gleiche Bedeutungsgewicht haben, auftaucht:

Und wenn Excel noch weiß, wie man horizontal sortiert (mit dem Befehl Daten – Sortieren – Optionen – Spalten sortieren), dann ist die Situation beim Filtern schlimmer – es gibt einfach keine integrierten Tools zum Filtern von Spalten, nicht von Zeilen in Excel. Wenn Sie also vor einer solchen Aufgabe stehen, müssen Sie sich Workarounds unterschiedlicher Komplexität einfallen lassen.

Methode 1. Neue FILTER-Funktion

Wenn Sie die neue Version von Excel 2021 oder ein Excel 365-Abonnement verwenden, können Sie die neu eingeführte Funktion nutzen FILTER (FILTER), die die Quelldaten nicht nur nach Zeilen, sondern auch nach Spalten filtern kann. Um zu funktionieren, benötigt diese Funktion eine zusätzliche horizontale eindimensionale Array-Zeile, in der jeder Wert (TRUE oder FALSE) bestimmt, ob wir die nächste Spalte in der Tabelle anzeigen oder umgekehrt ausblenden.

Lassen Sie uns die folgende Zeile über unserer Tabelle hinzufügen und den Status jeder Spalte darin schreiben:

• Nehmen wir an, wir möchten immer die erste und letzte Spalte (Überschriften und Summen) anzeigen, also setzen wir für sie in der ersten und letzten Zelle des Arrays den Wert = TRUE.
• Für die verbleibenden Spalten ist der Inhalt der entsprechenden Zellen eine Formel, die die Bedingung überprüft, die wir mithilfe von Funktionen benötigen И (UND) or OR (Oder). Zum Beispiel, dass die Summe im Bereich von 300 bis 500 liegt.

Danach bleibt nur noch die Funktion zu verwenden FILTER um Spalten auszuwählen, über denen unser Hilfsarray einen TRUE-Wert hat:

Ebenso können Sie Spalten nach einer bestimmten Liste filtern. In diesem Fall hilft die Funktion COUNTIF (ZÄHLENWENN), die die Anzahl der Vorkommen des nächsten Spaltennamens aus dem Tabellenkopf in der zulässigen Liste prüft:

Methode 2. Pivot-Tabelle anstelle der üblichen

Derzeit verfügt Excel nur in Pivot-Tabellen über eine integrierte horizontale Filterung nach Spalten. Wenn es uns also gelingt, unsere ursprüngliche Tabelle in eine Pivot-Tabelle umzuwandeln, können wir diese integrierte Funktionalität verwenden. Dazu muss unsere Quelltabelle die folgenden Bedingungen erfüllen:

• eine „korrekte“ einzeilige Kopfzeile ohne leere und verbundene Zellen haben – sonst funktioniert es nicht, eine Pivot-Tabelle zu erstellen;
• enthalten keine Duplikate in den Beschriftungen von Zeilen und Spalten – sie „kollabieren“ in der Zusammenfassung in eine Liste mit nur eindeutigen Werten;
• enthalten nur Zahlen im Wertebereich (am Schnittpunkt von Zeilen und Spalten), da die Pivot-Tabelle definitiv eine Art Aggregationsfunktion auf sie anwendet (Summe, Durchschnitt usw.) und dies mit dem Text nicht funktioniert

Wenn alle diese Bedingungen erfüllt sind, muss diese (die ursprüngliche) von der Kreuztabelle in eine flache (normalisierte) erweitert werden, um eine Pivot-Tabelle zu erstellen, die wie unsere ursprüngliche Tabelle aussieht. Und der einfachste Weg, dies zu tun, ist das Power Query-Add-In, ein leistungsstarkes Datentransformationstool, das seit 2016 in Excel integriert ist. 

Diese sind:

1. Lassen Sie uns die Tabelle in einen „intelligenten“ dynamischen Befehl umwandeln Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren).
2. Laden in Power Query mit dem Befehl Daten – Aus Tabelle/Bereich (Daten – Aus Tabelle/Bereich).
3. Wir filtern die Zeile mit den Summen (die Zusammenfassung hat ihre eigenen Summen).
4. Klicken Sie mit der rechten Maustaste auf die erste Spaltenüberschrift und wählen Sie aus Blenden Sie andere Spalten aus (Pivotierung anderer Spalten aufheben). Alle nicht ausgewählten Spalten werden in zwei umgewandelt – den Namen des Mitarbeiters und den Wert seines Indikators.
5. Filtern der Spalte mit den Summen, die in die Spalte eingeflossen sind Attribut.
6. Wir bauen eine Pivot-Tabelle gemäß der resultierenden flachen (normalisierten) Tabelle mit dem Befehl Home — Schließen und laden — Schließen und laden in… (Home — Schließen & Laden — Schließen & Laden nach…).

Jetzt können Sie die in Pivot-Tabellen verfügbaren Spalten filtern – die üblichen Häkchen vor den Namen und Elementen Signaturfilter (Etikettenfilter) or Filtert nach Wert (Wertfilter):

Und natürlich müssen Sie beim Ändern der Daten unsere Abfrage und die Zusammenfassung mit einer Tastenkombination aktualisieren Ctrl+Andere+F5 oder Team Daten – Alle aktualisieren (Daten — Alle aktualisieren).

Methode 3. Makro in VBA

Wie Sie leicht sehen können, filtern alle vorherigen Methoden nicht genau – wir verstecken die Spalten in der ursprünglichen Liste nicht, sondern bilden eine neue Tabelle mit einem bestimmten Satz von Spalten aus der ursprünglichen. Wenn es erforderlich ist, die Spalten in den Quelldaten zu filtern (auszublenden), ist ein grundlegend anderer Ansatz erforderlich, nämlich ein Makro.

Angenommen, wir möchten spontan Spalten filtern, in denen der Name des Managers im Tabellenkopf die in der gelben Zelle A4 angegebene Maske erfüllt, beispielsweise mit dem Buchstaben „A“ beginnt (also „Anna“ und „Arthur " als Ergebnis). 

Wie bei der ersten Methode implementieren wir zunächst eine Hilfsbereichszeile, in der in jeder Zelle unser Kriterium durch eine Formel überprüft wird und die logischen Werte WAHR oder FALSCH für sichtbare bzw. ausgeblendete Spalten angezeigt werden:

Dann fügen wir ein einfaches Makro hinzu. Klicken Sie mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie den Befehl aus Quelle (Quellcode). Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das sich öffnende Fenster ein:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Nächste Zelle End If End Sub  

Seine Logik ist wie folgt:

• Im Allgemeinen ist dies ein Ereignishandler Arbeitsblatt_Ändern, dh dieses Makro wird automatisch bei jeder Änderung an einer beliebigen Zelle auf dem aktuellen Blatt ausgeführt.
• Der Verweis auf die geänderte Zelle befindet sich immer in der Variablen Target.
• Zunächst prüfen wir, ob der Benutzer genau die Zelle mit dem Kriterium (A4) geändert hat – dies übernimmt der Operator if.
• Dann beginnt der Kreislauf Für jeden… über graue Zellen (D2:O2) mit TRUE / FALSE-Indikatorwerten für jede Spalte zu iterieren.
• Wenn der Wert der nächsten grauen Zelle TRUE (true) ist, dann wird die Spalte nicht ausgeblendet, ansonsten blenden wir sie aus (property versteckt).

•  Dynamische Array-Funktionen von Office 365: FILTER, SORT und UNIC
• Pivot-Tabelle mit mehrzeiliger Kopfzeile mit Power Query
• Was sind Makros, wie werden sie erstellt und verwendet?