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
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.
Mit nur drei Formeln wird diese Auswertung aufgebaut:
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.
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
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
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 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.
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.
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
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.