Power Query: Listen mit Kundennummern abgleichen und alle Treffer mit „X“ kennzeichnen

Kunden, die an einer Online-Umfrage teilgenommen haben, wurden in einer Liste erfasst. Wie die darin enthaltenen Kundennummern aufbereitet werden, habe ich im Beitrag vom 4.1.2023 gezeigt.
Nun sollen alle Kunden, die sich an der Online-Umfrage beteiligt haben, in der Kundenstammliste gekennzeichnet werden. Dazu gleiche ich beide Listen mit Hilfe eines Joins in Power Query ab, füge der Kundenstammliste eine Spalte hinzu und vermerke dort die Treffer mit einem „X“.

Kundenstammliste automatisch angereichert um Information aus Online-Umfrage

Weiterlesen

Chaos bei Kundennummern nicht manuell, sondern automatisiert beheben – in nur 2 Minuten

Kurz vor Jahresende erhielt ich den Hilferuf eines Kunden. Eine Liste mit Online-Daten sollte mit den bestehenden Kundendaten abgeglichen werden. Das Problem: in der Liste mit den Kundenstammdaten stehen die Kundennummern ordnungsgemäß einzeln untereinander, in der Online-Liste hingegen befinden sich teilweise mehrere Kundennummern in einer Zelle – durch Komma getrennt. Der Versuch, die Daten der Online-Liste mit dem Befehl Text in Spalten in einzelne Kundennummern zu trennen, bringt nicht das gewünschte Ergebnis. Mit einer Abfrage in Power Query ist das Problem in zwei Minuten gelöst.

Die Originaldaten links mit Hilfe von Power Query in die erforderliche Form bringen

Die Originaldaten links mit Hilfe von Power Query in die erforderliche Form bringen

Weiterlesen

Eine mit ZEILENUMBRUCH erzeugte Liste nach Datum sortieren

In meinem Blogbeitrag zur Funktion ZEILENUMBRUCH vom 22.10.2022 waren die umzuwandelnden Daten in Spalte B nach Datum sortiert. Was aber, wenn die Ausgangsdaten nicht chronologisch dastehen? Wie lassen sich die Einträge in der dreispaltigen Liste, die mit ZEILENUMBRUCH entsteht, nach Datum sortieren? Dafür kombiniere ich ZEILENUMBRUCH mit SORTIEREN. Ich zeige hier zwei Lösungsvarianten.

Die gestapelten Daten in Spalte B sind nicht nach Datum sortiert

Die gestapelten Daten in Spalte B sind nicht nach Datum sortiert

Weiterlesen

Excel 365: Mit ZEILENUMBRUCH die Daten aus EINER auf DREI Spalten verteilen

Kürzlich erhielt ich folgenden Hilferuf: „Meine importierten Daten sind zerschossen. Sie stehen alle in einer Spalte untereinander, werden aber in drei Spalten gebraucht.“

Als ich mir das Problem anschaute, dachte ich zunächst an eine Lösung mit Power Query. Doch dann fiel mir die neue Funktion ZEILENUMBRUCH in Excel 365 ein. Mit ihr löse ich das Problem mit einer einfachen Formel innerhalb von Sekunden wie folgt.

Weiterlesen

Smarte Pivot-Technik für Projektauswertungen

Wer mit langen Listen arbeitet, hat oft den Wunsch, eine zusammenfassende Übersicht mit den wichtigsten Kennzahlen zu erstellen. Diese Aufgabe lösen Pivot-Tabellen. Kaum bekannt ist dabei eine Funktion, die Pivot-Auswertungen informativer und Zusammenhänge anschaulicher macht, nämlich »Werte anzeigen als«. Anhand eines kleinen Beispiels zeige ich hier den Nutzen dieser Funktion.

Das leistet »Werte anzeigen als«

Mit Hilfe der Funktion kann ich beispielsweise ohne jegliche Formel ermitteln,

  • welche Kosten je Abteilung, Projekt und Leistung angefallen sind – absolut und prozentual,
  • wie sich die kumulierten Kosten Monat für Monat in jedem Projekt entwickeln,
  • welche Differenz die Kosten vom aktuellen Monat zum Vormonat aufweisen, und zwar in absoluten Zahlen wie auch als prozentuale Veränderung.

Die folgende Übersicht zeigt, welches Potenzial die Funktion »Werte anzeigen als« hat, um Pivot-Auswertungen noch mehr Aussagekraft zu geben.

Ein Beispiel für den Nutzen von »Werte anzeigen als«

In der folgenden Aufstellung sind Projektkosten nach Datum, Projekt, Leistung und Abteilung erfasst.

Mit Hilfe einer Pivot-Tabelle werte ich die angefallenen Kosten nach Abteilungen und Leistung aus und erhalte die unten links gezeigte Übersicht.

Noch besser vergleichbar werden die Werte aber, wenn zu sehen ist, wie viel Prozent der Kosten auf die einzelnen Abteilungen entfallen.
Dies geschieht in der rechts gezeigten Pivot-Tabelle. Hierzu ziehe ich die Kosten zum zweiten Mal in den Wertebereich und verwende dann per Rechtsklick die Funktion »Werte anzeigen als« mit der Option »% des Gesamtergebnisses«.

Weitere Berechnungen ganz ohne Formel mit »Werte anzeigen als«

Die animierte GIF zu Beginn des Beitrages zeigt, dass »Werte anzeigen als« viele unterschiedliche Berechnungen ermöglicht, beispielsweise

  • Wie hoch sind die Kosten pro Monat pro Projekt? Dies lässt sich mit einer Pivot-Tabelle leicht errechnen, indem die Datumsspalte nach Monat gruppiert und die Kosten summiert werden.
  • Interessant ist auch immer, welche Kosten pro Projekt bis zu einem bestimmten Zeitpunkt aufgelaufen sind. Wie war der Stand beispielsweise bis Ende März? »Werte anzeigen als« bietet hier die Möglichkeit der kumulierten Berechnung, und zwar in absoluten wie auch in prozentualen Werten.
  • Oft ist auch der Vergleich zum Vormonat wichtig oder die Abweichung von Soll- und Ist-Werten. Hier kann ich mit der Option Differenz sowohl absolute wie auch prozentuale Kennzahlen erzeugen.

Eine ausführliche Demonstration von »Werte anzeigen als« gebe ich Anfang November in einem Fachartikel  für das »projektmagazin«.

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.

Bild 1: 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.

Weiterlesen

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

Weiterlesen

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

Weiterlesen

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