Beitrag aus SmartTools Excel Weekly
Excel-Tabellen sowohl nach Zeilen als auch nach Spalten filtern
Excel 365
Es gibt immer wieder Situationen, in denen Sie eine Tabelle nach Zeilen UND Spalten filtern möchten. Sie filtern zum Beispiel nach einem Artikelnamen, so dass nur noch die Zeilen mit diesem Artikel übrigbleiben. Gleichzeitig sollen aber nicht sämtliche Spalten der Tabelle ausgeben werden, sondern nur die, die Sie für Ihre aktuelle Auswertung benötigen – zum Beispiel nur die Spalten mit dem Kundennamen und dem Rechnungsbetrag. Wenn Sie Excel 365 einsetzen, können Sie das gleichzeitige Filtern nach Zeilen und Spalten mit den "dynamischen Arrayfunktionen" erreichen. Diese Funktionen geben Sie in eine einzelne Zelle ein, und falls es mehrere Ergebnisse gibt, werden diese im so genannten "Überlaufbereich" neben und unter der Formelzelle ausgegeben.
In unserem Beispiel kombinieren Sie die dynamischen Arrayfunktionen INDEX und FILTER. Mit der FILTER-Funktion erstellen Sie ein Array, das nur die Tabellendaten enthält, die einem bestimmten Kriterium entsprechen. Und mit der Arrayfunktion INDEX können Sie anschließend nicht nur eine einzelne Zelle aus den gefilterten Tabellendaten herausgreifen, sondern auch verschiedene Zeilen und Spalten. Dazu übergeben Sie als "Zeilen"- und "Spalten"-Argumente Zahlenarrays, mit denen Sie die gewünschten Zeilen- sowie Spaltennummern definieren.
Hier benötigen Sie für die Zeilen ein Zahlenarray, das von 1 bis zur Anzahl der gefilterten Zeilen reicht. Das erhalten Sie mit der Funktion SEQUENZ. Für die Spalten besteht das Zahlenarray aus den Nummern der auszugebenden Spalten. Wenn Sie zum Beispiel nur die Spalten 1 und 4 benötigen, geben Sie als Formelargument das einzeilige Array {1.4} ein.
Angenommen, Sie hätten eine Tabelle (Start-Formatvorlagen-Als Tabelle formatieren) mit den Spalten "Land", "Region", "Produkt" und "Umsatz". Die Tabelle hat den Namen "tblUmsatz". Nun wollen Sie an anderer Stelle eine Liste ausgeben, die nach einem Produktnamen gefiltert ist und nur die Spalten "Land" und "Umsatz" – also die erste und die vierte Spalte aus der Originaltabelle – enthält.
Das Filterkriterium – also einen Produktnamen aus der Originaltabelle – geben Sie in diesem Beispiel in Zelle H2 ein.
In Excel 365 brauchen Sie jetzt nur noch folgende Formel in eine Zelle neben der Originaltabelle einzugeben, um die Liste mit den Spalten "Land" und "Umsatz" zu erhalten:
=INDEX(FILTER(tblUmsatz;tblUmsatz[Produkt]=H2;"");
SEQUENZ(ZÄHLENWENN(tblUmsatz[Produkt];H2));{1.4})
Obwohl Sie die Formel nur in eine einzige Zelle eingeben, gibt Excel automatisch alle übereinstimmenden Werte aus der Originaltabelle aus und trägt sie in den Überlaufbereich der dynamischen Arrayformel ein.
In unserem Beispiel können Sie schnell ein anderes Filterkriterium anwenden. Sie müssen dafür nur einen anderen Produktnamen in Zelle H2 eingeben.