Mehr als nur Pivot: Flexible Auswertungen mit Datenmodell, Power Pivot & DAX
Wer seine Auswertungen an stets neue Anforderungen anpassen muss, stößt mit Pivot-Tabellen mitunter an Grenzen. Typische Gründe sind:
- Die Daten liegen nicht in der passenden Form vor.
- Sie stammen aus unterschiedlichen Quellen und müssen verknüpft werden.
- Statistische Berechnungen werden benötigt, die mit Pivot-Standardfunktionen nicht machbar sind, z. B. DISTINCTCOUNT oder SUMX.
- Daten sollen über Zeitperioden verglichen werden, doch Pivot bietet keine intelligenten Datumsfunktionen wie z. B. SAMEPERIODLASTYEAR oder TOTALYTD.
Das Datenmodell und einige nützliche DAX-Measures in Power Pivot bringen hier schnell eine Lösung.
Wie sich Pivot-Tabellen mit Hilfe des Datenmodells und Power Pivot in maßgeschneiderte Auswertungen verwandeln lassen, vermittle ich in diesem Online-Kurs am 25. Mai.
Wer teilnehmen sollte
- Alle, die Daten aus unterschiedlichen Systemen zusammenführen und wichtige Geschäftskennzahlen flexibel berechnen wollen
- Fach- und Führungskräfte aus Controlling, Finanzen, Marketing/Vertrieb, IT oder Online-Dienstleister
Das folgende PDF-Booklet enthält die wichtigsten Infos zu diesem Online-Kurs.
Hier geht es zur Webseite mit allen Informationen.
Please wait while flipbook is loading. For more related info, FAQs and issues please refer to DearFlip WordPress Flipbook Plugin Help documentation.
Inhalte aus Spalten auf Zeilen verteilen ohne Copy & Paste
Wie kann ich eine Liste auswerten, in der mehrere Spalten die gleiche Art von Information enthalten? Im folgenden Beispiel wurden in der oberen Liste die Daten zu jeder Firma in einer Zeile erfasst. Vor- und Nachname des Ansprechpartners sind in zwei Spalten abgelegt. So weit, so gut. Aber: Bei weiteren Ansprechpartnern stehen diese rechts daneben in zusätzlichen Spaltensets. Das Problem: Eine solche Datenstruktur lässt sich mit Pivot nicht auswerten. Und auch für den Re-Import in eine Datenbank ist sie ungeeignet. Wie bringe ich die Daten automatisiert in die in der blauen Tabelle gezeigte passende Struktur? Dazu muss ich die Inhalte aus Spalten auf Zeilen verteilen mit Hilfe von Power Query und den Befehlen Entpivotieren und Pivotieren.
Spaltensets des gleichen Typs in einer Zeile sind für eine Weiterverarbeitung ungeeignet. Jedes Set benötigt eine eigene Zeile.
Wie ich das auch für eine variable Anzahl von Spaltensets löse, zeige ich in der folgenden Anleitung. weiterlesen
Dynamisch wachsende Auswertung mit nur 3 Formeln als Alternative zu Pivot
Sollen Listen ausgewertet werden, denken viele sofort an Pivot-Tabellen. Dass es auch ohne Pivot geht, zeigt die unten rechts gezeigte Umsatzauswertung nach Artikeln und Regionen.
Die Auswertung rechts entsteht mit nur drei Formeln
Mit nur drei Formeln wird diese Auswertung aufgebaut:
- die erste Formel listet in Spalte H die Artikel in sortierter Reihenfolge auf,
- die zweite sorgt für die Spaltenüberschriften ab Spalte I – natürlich auch sortiert,
- die dritte summiert die Umsätze aus der links gezeigten grauen Umsatzliste nach Artikeln und Regionen.
Dank der Formeln ist die Auswertung komplett dynamisch: kommen neue Artikel oder Regionen hinzu, erweitert sie sich automatisch nach unten bzw. nach rechts. Mit den folgenden Schritten wird die Auswertung erstellt.
Excel 365: Dynamische Zeitleisten statt Gantt-Diagramm mit SEQUENZ
Um Laufzeiten von Projekten, Aufgaben, Verträgen oder auch Wartungsintervallen aufzuzeigen, sind Zeitleisten ein bewährtes Mittel. Sie lassen sich per Gantt-Diagramm oder über die Bedingte Formatierung aufbauen. In Excel 365 reicht jetzt eine einzige Formel, um die erforderlichen Datumsangaben für die Zeitleisten zu erzeugen. Dazu nutze ich die neue Arrayfunktion SEQUENZ.
Bild 1: Mit nur einer Formel wird in Zeile 4 die Datumsleiste dynamisch erzeugt und ab Zeile 5 sorgt eine Formel in der Bedingten Formatierung für die korrekte Position und Länge der farbigen Balken
Die neue Funktion SORTIERENNACH: Eine Produktliste nicht alphabetisch, sondern automatisch nach Umsatz sortieren lassen
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 11.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
Wie auch »alte« Excel-Funktionen dynamisch werden
Meine Blogbeiträge vom 7.1.2020 und 18.2.2020 zeigen, 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 Arrayfunktionen ä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
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.
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.
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
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 Unterschiede es derzeit gibt, zeige ich im folgenden Kurzvideo.
Wer sein Wissen zu Power Query erweitern will: Hier geht’s zum aktuellen Kursangebot.
