Wie eine Produktliste nicht alphabetisch, sondern AUTOMATISCH nach Umsatz sortiert wird

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.

Wie auch »alte« Excel-Funktionen dynamisch werden

In meinen Blogbeiträgen vom 2.1.2020 und 14.4.2020 zeige ich, wie sich mit den neuen Array-Funktionen in Excel 365 Listen erzeugen lassen, die dynamisch gefiltert, sortiert und ohne doppelte Werte sind. Ohne VBA-Programmierung! Das Zauberwort heißt SPILL.
Was es damit auf sich hat, ist schnell erklärt: 35 Jahre lang galt in Excel, dass EINE Formel EINE Zelle mit einem Ergebnis füllt. Mit den neuen Array-Funktionen ändert sich das gründlich: nun kann eine Formel mehrere Zellen untereinander oder sogar mehrere Spalten nebeneinander mit Ergebnissen befüllen.
Mehr noch: selbst altbekannte Funktionen wie SUMMEWENN(S) oder ZÄHLENWENN(S) können jetzt mit nur einer Formel mehrere Zellen mit Ergebnissen liefern. Auch sie sind SPILL-fähig.

Bild 1: Sogar alte Excel-Funktionen beherrschen jetzt das SPILLING und liefern Ergebnisse für mehr als nur eine Zelle

Weiterlesen

Power Query-Abfragen schützen: Geht das?

Power Query ersetzt an vielen Stellen die teils sehr aufwendige VBA-Programmierung, insbesondere wenn es um die Aufbereitung von Daten für die Analyse geht. Das ist eine große Arbeitserleichterung und Zeitersparnis. Aber da kommt auch gleich die Frage auf: VBA-Code lässt sich per Passwort schützen, aber geht das auch bei Power Query-Abfragen?

Ist der Arbeitsmappenschutz aktiv, können die Power Query-Abfragen noch aktualisiert, aber nicht mehr bearbeitet werden

In Excel 365 kann die Bearbeitung und das Löschen der Power Query-Abfragen per Arbeitsmappenschutz verhindert werden. Weiterlesen

Daten aus zwei Listen bündeln zu einer lückenlosen Gesamtübersicht mittels JOIN

Welche Produkte bisher welchen Umsatz gemacht haben, ist auf Basis einer Umsatzliste mit Pivot schnell ermittelt. Doch wie lässt sich herausfinden, welche Produkte bis dato nicht verkauft wurden? Dazu gibt es keine Daten, denn die Umsatzliste enthält nur die verkauften Produkte. Hier hilft eine zusätzliche Liste weiter, in der alle Produkte erfasst sind.

Aus den zwei Listen für Umsätze und Produkte wird eine Gesamtliste generiert, die für alle Produkte die Umsatzzahlen zeigt, auch wenn diese bei null liegen

Wie ich mit Hilfe eines Joins eine komplette Liste aller Produkte mit allen Umsätzen erstelle, beschreibe ich in diesem Beitrag.

Weiterlesen

Erweitertes Kursangebot für Datenanalysten und alle, die es werden wollen

Das Interesse an meinen Kursen zu BI mit Excel ist überraschend stark. Daher werde ich die Kurse wiederholen und zwei neue hinzufügen. Die nächsten Termine:

Power Query-Einstiegskurs | 27. April oder 22. Juni | 9:00 bis 16:30
Mehr erfahren …  |  Zur Anmeldung …

NEU | Power Query-Vertiefungskurs zu JOINS | 15. April oder 10. Mai | 9:00 bis 12:30
Mehr erfahren …  |  Zur Anmeldung …

Power Query-Aufbauworkshop | 20. Mai | 9:00 bis 16:30
Mehr erfahren …  |  Zur Anmeldung …

NEU | Flexible Auswertungen mit Datenmodell, Power Pivot & DAX | 31. Mai | 9:00 bis 16:30
Mehr erfahren …  |  Zur Anmeldung …

 

Meine Pivot-Kurse gehen in die zweite Runde

Das Thema »Auswertungen mit Pivot« ist offenbar ein Dauerbrenner. Meine  vier ONLINE-Kurse zu Pivot im Februar und März waren jeweils ausgebucht. Daher habe ich nun drei weitere Termine im April und Juni geplant.

Pivot-Einführungskurs | Halber Tag | 19. April | 235 Euro (zzgl. MwSt.)
mehr erfahren …

Pivot-Profikurs | Ganzer Tag | 29. April oder 14. Juni | 445 Euro (zzgl. MwSt.)
mehr erfahren …

Wer in die Kurse hineinschnuppern möchte, schaut sich folgende Kurzvideos an:

Weiterlesen

Ein Trick im M-Code macht‘s möglich: Mit Power Query mehrere Tabellen in der AKTUELLEN Mappe zusammenführen

Will ich Daten aus verschiedenen Tabellen einer anderen Arbeitsmappe abrufen, geht das recht leicht, denn Power Query lässt mich gleich mehrere Tabellen einer anderen Datei zum Einlesen markieren. Deutlich weniger komfortabel und keineswegs intuitiv ist es, wenn mehrere Tabellen der aktuellen Mappe zusammenzuführen sind. Diese Aufgabe stellt sich immer dann, wenn Daten in einer Mappe auf mehrere Arbeitsblätter verteilt sind, z. B. ein Blatt pro Monat, ein Blatt pro Standort oder ein Blatt pro Abteilung.
Wie sich solche verteilten Daten durch einen kleinen Eingriff in den M-Code in einer einzigen Abfrage zusammenführen lassen, zeige ich in der folgenden Anleitung.

Das GELB markierte ist die zentrale Anweisung, um auf die gesamte aktuelle Arbeitsmappe zuzugreifen

Das GELB markierte ist die zentrale Anweisung, um auf die gesamte aktuelle Arbeitsmappe zuzugreifen

Weiterlesen

Power Query: Welche Version habe ich und wo sehe ich das?

In meinen Kursen zu Power Query kommt häufig die Frage, warum mein Power Query  anders aussieht als bei den Teilnehmenden, beispielsweise im Register Ansicht.
Der Grund: In Excel 2016, 2019, 365 sind unterschiedliche Versionen von Power Query verfügbar. Wie sich die Versionsnummer von Power Query ermitteln lässt und welche Versionsunterschiede es derzeit gibt, zeige ich im folgenden Kurzvideo.
Wer sein Wissen zu Power Query erweitern will: Hier geht’s zum aktuellen Kursangebot.

 

Die Lösung für Eilige: In 3 Schritten zur eigenen Formatvorlage für Pivot-Tabellen

Warum das Fahrrad neu erfinden? Das frage ich mich jedes Mal, wenn ich für eine fertige Pivot-Tabelle nur noch schnell die Optik verbessern will. Die vorgegebenen Formatvorlagen passen nur selten. Wenn ich dann das Dialogfeld zum Definieren einer neuen Pivot-Formatvorlage öffne, erschlägt mich die Fülle der Gestaltungsoptionen. Ich habe 25 gezählt, doch eigentlich interessieren mich nur einige davon.

Die 25 (möglichen) zu definierenden Elemente in einer neuen Pivot-Formatvorlage

Die 25 (möglichen) zu definierenden Elemente in einer neuen Pivot-Formatvorlage

Da ich kein neues Fahrrad, sondern nur einen anderen Lenker und eine schönere Klingel brauche, suchte ich nach einer pragmatischen Lösung. Ich habe sie gefunden, wie folgende Abbildung zeigt. Den Aufbau der Lösung erkläre ich gleich im Detail.

Weiterlesen

Detaillierte Anleitung: Ampel-Diagramm in 5 Schritten

Wie lassen sich die Säulen in einem Diagramm je nach Wert automatisch in 3 unterschiedlichen Farben darstellen?
Die Antwort liefert dieser Blogbeitrag auf www.office-kompetenz.de.
Als Bonus gibt es noch eine fertige Beispieldatei zum Download.

Beiträge durchblättern:  1 2 3 4 5 6 7 8 9 >>