Office-Blog

Excel 365: So machen Auswertungen Spaß – dynamische Arrayfunktionen, die wirklich alle brauchen

19.02.2019   Dietmar Gieringer

NEIN, es ist keineswegs übertrieben: Mit den dynamischen Arrayfunktionen in Excel 365 hat Microsoft eine neue Ära eingeläutet. Auswertungen werden künftig einfacher und intuitiver. Berechnungen, die früher nur über Profitechniken und komplizierte Formeleingabe möglich waren, lassen sich jetzt mit einer einzigen und zudem verständlichen Formel erledigen.

Hinweis: Die neuen Arrayfunktionen können seit Herbst 2018 in Excel 365 getestet werden. Doch vorerst sind sie nur für die Anwender verfügbar, die den Update-Kanal »Insider« in Office 365 haben.

Hinweis auf den Platzbedarf der neuen Funktionen

Der Hinweis auf den Platzbedarf der neuen Funktion

Für alle, die neugierig sind und sich auf diese Revolution in Excel vorbereiten wollen, stellen wir die neuen Arrayfunktionen in mehreren Blogbeiträgen vor. Los geht’s mit der Funktion EINDEUTIG. Mit ihr kann ich aus beliebig vielen Einträgen im Nu eine Liste erzeugen, in der jeder Eintrag nur einmal vorkommt, also ohne Duplikate. Hier im Beispiel ist es eine Liste von Kunden.

Mit nur einer Funktion eine Liste ohne Duplikate erzeugen

Links in der »intelligenten Tabelle« habe ich Umsätze aufgeführt. Nun möchte ich herausfinden, mit wie vielen Kunden diese Umsätze erzielt wurden. Ich brauche also eine Liste, in der jeder Kunde nur einmal vorkommt. Den Befehl Duplikate entfernen verwende ich allerdings nicht mehr, denn es gibt etwas, das schneller und einfacher ist. In einer Zelle neben meiner intelligenten Tabelle trage ich wie in der folgenden Animation gezeigt die Formel ein =EINDEUTIG(tbl_Verkauf[Kunde]).

Die Funktion EINDEUTIG erzeugt im Nu die gewünschte Kundenliste ohne Duplikate

WOW, das war’s, mehr ist nicht notwendig!
  • Mit einer einzigen Funktion, die ich zudem nur in eine Zelle eingegeben habe (J6), erzeugt Excel eine Liste ohne Duplikate.
  • Die Werte werden dabei automatisch in die unten angrenzenden Zellen eingetragen: hier von J7 bis J12.
    Dieser Vorgang wird von Microsoft »Überlaufen« genannt (vgl. den eingangs gezeigten grünen Hinweis).
  • Das war bisher nur mit aufwändigen Matrixformeln möglich.

Wichtig: Die Formel selbst kann nur in Zelle J6 bearbeitet werden – zu erkennen daran, dass sie in der Bearbeitungsleiste schwarz geschrieben ist. Klicke ich auf die J7, wird die Formel in der Bearbeitungsleiste grau angezeigt, denn J7 ist nur eine »Überlaufzelle«. Oben in der animierten GIF und in der folgenden Abbildung  ist das ganz gut zu sehen.

Nur die Formel in der ersten Zelle des Überlaufbereichs kann bearbeitet werden, in den anderen Zellen ist die Formel ausgegraut

Nur die Formel in der ersten Zelle des Überlaufbereichs kann bearbeitet werden, in den anderen Zellen ist die Formel ausgegraut

Das geht auch: Die Liste gleich noch sortieren lassen

Momentan hat die generierte Liste noch einen kleinen Makel: Die Einträge erscheinen in der ursprünglichen Reihenfolge ihres Vorkommens in der Spalte »Kunde«. Um die Kundennamen von A bis Z anzuordnen, ziehe ich SORTIEREN hinzu. Das ist ebenfalls eine neue Arrayfunktion. Ich ergänze die Formel in J6 wie in der folgenden Animation gezeigt:
  • Hinter dem Gleichheitszeichen füge ich SORTIEREN ein und
  • am Ende schließe ich die Formel mit einer zweiten schließenden Klammer ab.

Die komplette Formel lautet somit =SORTIEREN(EINDEUTIG(tbl_Verkauf[Kunde]))

Die Funktion SORTIEREN bringt die Listeneinträge in die richtige Reihenfolge

Die Funktion SORTIEREN bringt die Listeneinträge in die richtige Reihenfolge

Die Kombination von EINDEUTIG und SORTIEREN liefert eine sauber sortierte Liste. Doch die kann noch mehr!

Dynamik pur: Neue Einträge werden sofort in der Liste ergänzt

Die folgende Abbildung zeigt, wie ich die ursprüngliche Tabelle um einen neuen Eintrag erweitere – hier durch Schermann KG.
  • Automatisch wird die sortierte Kundenliste ohne Duplikate um Schermann KG ergänzt.
  • Außerdem wird der neue Eintrag natürlich gleich richtig einsortiert.

WOW, das schafft nicht einmal eine Pivot-Tabelle. Denn die müsste ich nach jeder Änderung der Daten erst aktualisieren.

Bei neuen Einträgen in der Ursprungstabelle wird die Kundenliste sofort ergänzt und korrekt neu sortiert

Kommen links neue Daten hinzu, wird die Kundenliste ohne Duplikate automatisch ergänzt und korrekt neu sortiert

Ausblick

Das war der Einstieg in die neue Ära der dynamischen Array-Funktionen in Excel 365. Welche weiteren Arrayfunktionen es gibt und was sie leisten, ist bald hier im Blog zu lesen.

 

 

 

Über den Autor

Dietmar Gieringer

- Dipl.-Betriebswirt mit umfangreicher kaufmännischer Erfahrung
- Zeigt Firmen, wie sie mit Excel und den Business Intelligence-Werkzeugen
schnell und sicher auf relevante Daten zugreifen
- Entwickler von Tools zur Projekt- und Abrechnungssteuerung auf Excel-Basis
- Zertifizierter Office-Trainer mit den Schwerpunkten Excel, Word und VBA
- Projekterfahrener Dienstleister für das Entwickeln CI-gerechter Office-Vorlagen
- Berater beim Office-Rollout für mittelständische Firmen
- Autor für Microsoft Press, dpunkt.verlag und »Modernes Reporting mit Excel«

1 Kommentar bisher

MarkusPosted on  11:02 am - Feb 19, 2019

Servus Dietmar,
bei dem Thema freue ich mich schon auf Deine und Dominiks Workshops in Fulda.
Gruß ans ganze OK-Team
Markus