Office-Blog

Seit Herbst 2018 verfolge ich Berichte über Excels neue dynamische Arrayfunktionen. Diese konnten allerdings bisher nur von Anwendern mit dem Updatekanal »Insider« und »Monatlich gezielt« genutzt werden. Nun sind solche Funktionen wie EINDEUTIG, FILTER, SORTIEREN und SORTIERENNACH auch im Updatekanal »Monatlich« verfügbar.
Da das Sortieren und Filtern von Listen zu den häufigen Aufgaben in Excel gehört, habe ich zuerst die Funktionen FILTER und SORTIEREN ausprobiert. Eines kann ich schon vorwegnehmen: Ich bin erstaunt, wie einfach die Bedienung ist und bin begeistert über die vielen Möglichkeiten. Hier ein erstes Beispiel.

Vorschau auf das fertige Ergebnis mit FILTER

Geht künftig auf Knopfdruck: Gefilterte Daten auf einem separaten Blatt anzeigen und drucken

Wer seine Daten häufig filtern muss, kennt das Problem:

• Zuerst wird der Filter eingerichtet.
• Dann wird das Filterkriterium gesetzt.
• Anschließend wird der gefilterte Datenbestand auf ein separates Blatt kopiert.
• Zum Schluss wird eine Überschrift ergänzt und das Ergebnis gedruckt.

Mit der neuen Funktion FILTER geht das viel einfacher und schneller:

• In wähle nur noch in einer Zelle das gewünschte Filterkriterium aus, beispielsweise ein Produkt, einen Kunden oder eine Region.
• Die FILTER-Funktion generiert mir sofort eine Liste der zutreffenden Datensätze. FERTIG.

So geht’s: Mit FILTER nur bestimmte Datensätze auflisten lassen

Die folgende Abbildung zeigt den Datenbestand: Umsätze, die nach Datum, Artikelgruppe, Region, Kunde, Außendienstmitarbeiter (ADM) und Nettoumsatz erfasst werden.

Die Liste mit den Umsatzdaten wandele ich mit Strg + T in eine »intelligente« Tabelle um. Über die Registerkarte Tabellentools weise ich ihr den Namen tbl_Umsatz zu. Dieses Vorgehen hat zwei Vorteile:

• Das Schreiben der Formeln wird einfacher, da ich nicht mit kryptischen Zellbezügen arbeiten muss.
• Zudem wird meine Tabelle beim Eingeben und Löschen von Datensätzen automatisch angepasst und meine Auswertung greift zuverlässig auf den aktuellen Datenbestand zu.

Ausschnitt aus dem Datenbestand, der gefiltert werden soll

Ich möchte nur die Datensätze für den Kunden TerraMeda anzeigen. So gehe ich vor:

• Ich wechsle in das Arbeitsblatt Auswertung und gebe dort in Zelle E3 den Namen des Kunden ein, hier also TerraMeda.
• In Zeile 4 der Datenbasis kopiere ich die Spaltenüberschriften in den Spalten B bis G.
• Die Auflistung der Datensätze zu dem gewählten Kunden soll dann auf dem Blatt Auswertung ab Zeile 6 erfolgen. Ich markiere also Zelle B6 und beginne meine Formel mit =FILTER(
  Diese Funktion hat die drei Argumente Matrix, einschließen und wenn_leer.
• Für das erste Argument Matrix bewege die Maus wie unten gezeigt an die linke obere Ecke der Tabelle. Die Maus verwandelt sich in einen schrägen schwarzen Pfeil. Ein Klick genügt und schon sind alle Datensätze markiert.

Die Datenbasis ganz einfach markieren mit dem schrägen Pfeil

• Nach Eingabe eines Semikolons folgt nun das zweite Argument: Excel soll prüfen, ob der Name des Kunden in Zelle E3 in der Spalte mit den Kunden vorkommt. Ich markiere die zu durchsuchende Kundenspalte, indem ich in der Datenbasis einfach oberhalb des Wortes Kunde klicke (die Maus ist jetzt ein senkrechter schwarzer Pfeil). Dann gebe ich ein Gleichheitszeichen ein und klicke auf die Vergleichszelle E3.
• Die Formel lautet bisher =FILTER(tbl_Umsatz;tbl_Umsatz[Kunde]=Auswertung!E3
• Das dritte Argument brauche ich hier nicht. Also schließe ich die Formel mit einer Klammer und Enter ab.
• Das Ergebnis sieht nun fast so wie in Bild 1 aus. Nur die Zahlenformate für die Spalte Datum und Netto müssen noch angepasst werden. Das erledige ich auf der  Registerkarte Start in der Gruppe Zahl über die Liste mit den vordefinierten Formaten. Dort wähle ich Datum, kurz sowie Währung.

Die fertige FILTER-Formel

So wird es übersichtlicher: Die gefilterten Ergebnisse sortieren

Oft ist es ganz nützlich, die gefilterten Ergebnisse in einer bestimmten Reihenfolge zu zeigen, beispielsweise nach Artikel, nach Region oder nach Umsatz sortiert.
Das ist dank der neuen Arrayfunktionen mit wenigen Klicks erledigt. Dazu baue ich die eben erstellte FILTER-Funktion wie folgt in die Funktion SORTIEREN ein.

• Ich setze in der Bearbeitungsleiste den Cursor vor das Wort FILTER.
• Ich tippe Sor ein. Excel schlägt die Funktion SORTIEREN vor. Diesen Vorschlag übernehme ich durch Drücken der Tabulator-Taste.
• Ich setze den Cursor ans Ende der Formel und tippe dort ein Semikolon ein.
• Nun muss ich für das Argument Sortierindex die Nummer der Spalte eintragen, nach der die gefilterte Liste sortiert werden soll. Ich möchte nach Umsatz sortieren, also gebe ich die 6 ein, denn in Spalte 6 der Matrix stehen die Umsätze.
• Nach einem weiteren Semikolon folgt nun die Entscheidung, ob auf- oder absteigend sortiert werden soll. Ich möchte die jeweils höchsten Umätze ganz oben stehen haben, wähle also die Option -1.

Für eine absteigende Sortierung -1 wählen

Eine mögliche Fehlermeldung abfangen mit WENNFEHLER

Einen kleinen Schönheitsfehler hat meine gefilterte und sortierte Datenauswertung noch. Ist im Blatt Auswertung die Zelle E3 leer, ist also kein Filterkriterium angegeben, dann erscheint in Zelle B6 die neue Fehlermeldung #KALK!.

Die neue Fehlermeldung #KALK! in Zelle B6

Das vermeide ich, indem ich …

• in der Bearbeitungsleiste den Cursor vor das Wort SORTIEREN setze,
• WENNF eintippe und aus der Vorschlagliste von Excel die Funktion WENNFEHLER durch Drücken der Tabulator-Taste übernehme,
• den Cursor ans Ende der Formel setze und dort ein Semikolon und zwei Anführungszeichen eintippe,
• die Formel dann mit einer Klammer und Enter abschließe.

Die fertige Formel sieht wie folgt aus:

Fehlermeldungen gezielt abfangen mit der Funktion WENNFEHLER

Ausblick

Eleganter wäre es, wenn ich das Filterkriterium in Zelle E3 bequem über eine Dropdownliste auswählen könnte. Wie ich das per Datenüberprüfung und mit der neuen Funktion EINDEUTIG realisiere, beschreibe ich im nächsten Beitrag.

Und wenn die neuen Funktionen (noch) nicht verfügbar sind?

• Die neuen dynamischen Arrayfunktionen gibt es nur in Excel 365.
• Aktuell sind sie nur verfügbar, wenn als Updatekanal »Insider« oder »Monatlich gezielt« oder »Monatlich« eingestellt ist.
• Alle, die den Updatekanal »Halbjährlich« haben, müssen sich noch gedulden.
• Welcher Updatekanal eingestellt ist, lässt sich über die Befehlsfolge Datei > Konto herausfinden.

Die folgende Abbildung zeigt die Informationen, die beim Updatekanal »Monatlich« angezeigt werden. Entscheidend ist hier die Versionsnummer 1911 (also November 2019).

Die gelb markierten Informationen zu Updatekanal und Version sind entscheidend