Wie lässt sich eine Liste aller Produkte und deren Umsätze erstellen? Viele nutzen dafür eine Pivot-Tabelle. Soll noch nach Umsatz sortiert werden, ist das in Pivot schnell eingestellt. Allerdings muss die Pivot-Tabelle jedes Mal aktualisiert werden, wenn sich die Umsatzzahlen ändern. Dieses lästige manuelle Aktualisieren entfällt, wenn die neuen dynamischen Arrayfunktionen in Excel 365 verwendet werden.
Im Blogbeitrag vom 14.7.2021 habe ich gezeigt, wie eine alphabetisch sortierte Produktliste mit Umsatzwerten mit Hilfe der Funktionen SORTIEREN, EINDEUTIG und SUMMEWENN entsteht. Heute kommt das i-Tüpfelchen hinzu: die automatische Sortierung der Produkte nach errechneten Umsatzwerten. Möglich wird dies durch die Funktion SORTIERENNACH.
Bild 1: Produkteliste wird so sortiert, dass die Umsätze in absteigender Reihenfolge erscheinen
Die Produktliste nach errechnetem Umsatz sortieren
Der Unterschied zur bisherigen Lösung liegt einzig und allein in der Berechnung der Produktspalte. Diese wurde bisher mit der Formel =SORTIEREN(EINDEUTIG(tbl_Verkauf[Produkt])) erzeugt.
Jetzt aber wird die Produktspalte nach einer virtuell erzeugten Umsatzspalte sortiert.
- Die Berechnung des Umsatzes erfolgt – wie im Bild 1 zu sehen – genau wie im oben genannten Blogbeitrag erklärt.
- Bei der Berechnung der Produktspalte kommt statt SORTIEREN die Arrayfunktion SORTIERENNACH zum Einsatz. Sie benötigt als zweites Argument eine Matrix (gelb markierter Teil in Bild 2), nach der die Sortierung erfolgen soll. Das dritte Argument (-1) sorgt für eine absteigende statt standardmäßig eingestellter aufsteigender Sortierung.
Bild 2: SORTIERENNACH benötigt im Vergleich zu SORTIEREN eine Matrix (hier gelb markiert), nach der sortiert werden soll
Hinweis:
Als Matrix zur Sortierung dient eine virtuell innerhalb der Formel erzeugte Umsatzspalte. Sie wird mit der Funktion SUMMEWENN berechnet. Als Suchkriterium werden die Produkte verwendet, die die Funktion EINDEUTIG liefert.
In Bild 3 wird zur Veranschaulichung das Ergebnis dieser virtuellen Spalte gezeigt.
Bild 3: Mit SUMMEWENN und EINDEUTIG wird eine virtuelle Umsatzspalte für das zweite Argument der SORTIERENNACH-Funktion erzeugt
Da die komplette Auswertung nur aus Formeln erzeugt wird, ist keine Aktualisierung mehr nötig. Denn nach jeder Änderung der Umsatzzahlen berechnet Excel die zusammengefassten Ergebnisse in der Umsatzauswertung neu und sortiert sie auch gleich.
Auch dieses Beispiel belegt, dass die dynamischen Arrayfunktionen in Excel 365 mit jeder Menge Potential aufwarten und sicher in Zukunft die alten Matrixformeln ersetzen werden.
Lust auf mehr zeitsparendes Wissen?
Wer mehr zu den Arrayfunktionen wissen möchte und Auswertungen dynamisch und ohne lästige Anpassungen erstellen will, ist auf den Excel-Kompetenztagen in Fulda genau richtig. Hier gibt’s es in vier Kursreihen jede Menge Wissen, Techniken und Tipps rund um Excel.
