Mehrere Excel-Listen kombinieren und berechnen: Der Cross Join macht’s möglich
Im Beitrag vom 3. Juli 2018 habe ich beschrieben, wie Listen mit gleichen Spalten über Joins miteinander abgeglichen werden. Heute geht es um Listen, die keine Gemeinsamkeit aufweisen.
Hier im Beispiel sind es Artikel, die in unterschiedlichen Farben erhältlich sind. Je nach Farbe gibt es für jeden Artikel auf den Basispreis einen prozentualen Aufschlag. Gebraucht wird also eine Artikelliste, die für jeden Artikel in jeder Farbkombination den Preis bereithält.
Per Cross Join zwei Listen kombinieren
Das lässt sich lösen mit einem speziellen Join – dem sog. Cross Join (Kreuzprodukt). Er kombiniert alle Daten einer Tabelle mit allen Daten einer anderen Tabelle.
Allerdings ist der Cross-Join in Power Query nicht über Start > Kombinieren > Abfragen als neue Abfrage zusammenführen zu finden. Er muss über eine benutzerdefinierte Spalte herbeigeführt werden.
Excel-Listen abgleichen und verknüpfen, das geht ganz leicht: Die 6 magischen Joins von Power Query
Was Datenbanken als Standard können, was in Excel per SVERWEIS und mit umständlichem Zusammenkopieren funktioniert, lässt sich in Power Query mit ein paar Mausklicks und ohne jede Formel erledigen.
- Welche Produkte für Kunde A werden auch an Kunde B verkauft?
- Welche Artikel aus Lager A kann auch Lager B liefern?
- Welche Artikel wurden im aktuellen Jahr nicht verkauft?
- Welche Kunden haben im letzten Jahr keine Bestellung aufgeben?
Um solche Fragen zu beantworten, müssen Listen abgeglichen werden. Das erledigen in Power Query sogenannte Joins. Sie stellen die notwendigen Beziehungen zwischen den zu vergleichenden Tabellen her. So werden beispielsweise die Artikelnummern aus Lager A mit den Artikelnummern aus Lager B abgeglichen. Für diesen Abgleich stellt Power Query sechs verschiedene Join-Möglichkeiten zur Verfügung.
Doppeltes Power Query? So lassen sich Abfragen im Nachhinein analysieren
Power Query ist DAS Werkzeug zur Abfrage von Daten aus beliebigen Quellen. Selbst komplexe Abfragen mit vielen einzelnen Schritten lassen sich einfach zusammenstellen.
Was aber, wenn ich in einer bestehenden Abfrage noch mal nachschauen will, welche Schritte nacheinander durchgeführt wurden, weil ich gerade eine neue Abfrage ähnlich gestalten möchte?
Da wäre es doch praktisch, wenn ich alte und neue Abfrage nebeneinander anordnen und dann Schritt für Schritt vergleichen könnte. Doch wie bekomme ich den Abfrageeditor zweimal geöffnet? Mit der folgenden kurzen Anleitung klappt’s!
Jetzt wird es eng: Schnell einen der letzten Plätze ergattern
Wer wissen will, wie das im Video gezeigte interaktive Dashboard funktioniert und wie es aufgebaut wird, sollte sich schnell den letzten Platz für diesen Workshop sichern.
Auch für den Workshop Excel-Formulare ist noch genau ein Platz verfügbar.
Ganz 3 Plätze gibt es noch für die Excel-Kompetenztage. Hier geht es in 5 Kursreihen um Formeln und Funktionen, Diagramme, Pivot sowie die neuen BI-Tools von Excel.
Schon lange ausgebucht ist der Workshop zu Listen und Power Query.
Wer sich für weitere Events und Workshops interessiert, schreibt einfach eine E-Mai an info@office-kompetenz.de.
Daten in Power Query für Pivot-Auswertungen mit Jahr, ISO-Jahr und -Kalenderwoche vorbereiten
Pivot-Auswertungen nach ISO-Kalenderwoche werden in unterschiedlichsten Bereichen benötigt, ob für Umsätze, Bestellungen oder Produktionszahlen.
In Pivot kann für Datumsspalten eine Gruppierung nach Jahr, Quartal, Monat und/oder Tag gewählt werden, aber Kalenderwochen-Gruppierungen gibt es nicht.
Bild 1: Produktionszahlen in Pivot nach Jahr und ISO-Kalenderjahr und -woche auswerten
Wer die Daten für die Pivot-Auswertung mit Power Query vorbereitet, freut sich über die vielfältigen Möglichkeiten und die große Zeitersparnis gegenüber aufwendigen Copy&Paste- und Berechnungsaktionen in Excel.
Allerdings steht Power Query in Bezug auf Datumsfunktionen Excel noch ein wenig nach. In Excel steht für die Berechnung der ISO-Kalenderwoche seit Version 2010 die Funktion KALENDERWOCHE mit dem Parameter 21 und seit Version 2013 die Funktion ISOKALENDERWOCHE zur Verfügung.
Power Query verfügt bisher über keine solche Funktion. Aber auch dafür gibt eine Lösung.
In Diagrammen automatisch die drei niedrigsten Werte farblich hervorheben
Wer kennt sie nicht: Diagramme mit einer Vielzahl mehr oder weniger hoher Säulen. Doch was sagen sie eigentlich aus? Oder: Was sollen sie aussagen?
Sind es Umsätze, Gewinne oder Bestellungen, interessieren sich alle zuerst für die höchsten Säulen.
Bei Fehlerquoten, Reklamationen oder Kosten hingegen gilt der Blick den kleinsten Säulen: sie haben am besten abgeschnitten.
Links die Standardlösung in Grau, rechts das Diagramm, in dem die drei niedrigsten Werte dank grüner Farbe sofort erkennbar sind
Woher aber sollen die Betrachter wissen, welche Säulen wichtig sind?
Farben helfen dabei, die Aussage von Diagrammen leichter zu erfassen. Der Blick auf das Wesentliche oder auf Probleme lässt sich gezielt lenken: einfach die betreffenden Säulen farblich hervorheben. Natürlich soll das automatisch erfolgen.
Wie das geht, zeigt die folgende Schritt-für-Schritt-Anleitung.
Genial: Ein Häkchen erspart mir den SVERWEIS in Pivot-Auswertungen
Früher konnte ich beim Anlegen einer Pivot-Auswertung nur auf die Daten aus einer Tabelle zugreifen. Brauchte ich Informationen aus einer weiteren Tabelle, musste ich beide Tabellen mittels SVERWEIS kombinieren. Seit Excel 2013 geht das viel einfacher – und ganz ohne SVERWEIS!
Die Lösung bringt das Datenmodell. Mit seiner Hilfe kann ich beliebig viele Tabellen als Datenbasis für meine Pivot-Auswertungen heranziehen. Das ist einfacher und weniger fehleranfällig. Zudem eröffnet es mir neue Möglichkeiten zur Datenanalyse und -visualisierung.
Die Feldliste einer »klassischen« Pivot-Tabelle (links) erlaubt den Zugriff auf nur eine Tabelle; mit Hilfe des Datenmodells hingegen lassen sich die Inhalte mehrerer Tabellen in einer Pivot-Tabelle auswerten (rechts)
Kein Ärger mehr mit Gruppierungen in Pivot: Ein Tutorial zeigt, was zu tun ist
Probleme beim Gruppieren der Daten in Pivot-Tabellen werden häufig durch den Pivot-Cache verursacht. Das neue Tutorial zum Umgang mit dem Pivot-Cache ist ein Ratgeber für alle, die mit solchen Problemen schon konfrontiert waren oder sich wappnen möchten.
Lesen Sie. was der Pivot-Cache ist, wie sich Fallstricke vermeiden lassen und erhalten Sie wertvolle Hintergrundinformationen zum Verständnis.
Lernen Sie als Profi-Tipp eine Technik kennen, die ermittelt, ob in einer Excel-Arbeitsmappe mehr als ein Pivot-Cache vorhanden ist.
Hier geht’s zum Download des Tutorials.
Gelöst: So liefert VERGLEICH keinen Fehlerwert mehr in »intelligenten« Tabellen
Die Funktion SVERWEIS ist wohl den meisten Excel-Anwendern bekannt: Sie durchsucht die erste Spalte eines Zellbereichs nach einem Wert und gibt aus einer der Folgespalten den korrespondierenden Wert zurück. Soll die Position der Folgespalte dynamisch gehalten werden, hilft die Funktion VERGLEICH.
Doch in »intelligenten« Tabellen stößt sie an Grenzen: Dann nämlich, wenn in den Spaltenüberschriften Zahlen stehen, z. B. Jahreszahlen.
Die VERWEIS-Funktion liefert einen Fehlerwert, wenn im Kopf einer »intelligenten« Tabelle Zahlen stehen
Wo liegt die Ursache für dieses Problem? Und wie lässt es sich lösen?
weiterlesen
Pivot: So funktioniert die Datumsgruppierung nach Wochen
Im Blogbeitrag vom 7.11.2017 habe ich die automatische Datumsgruppierung ab Excel 2016 und deren Ecken und Kanten erläutert.
Dazu erhielt ich die Frage, ob eine wochenweise Gruppierung ebenfalls möglich sei.
Zwar hält Pivot standardmäßig keine Gruppierungsebene nach Wochen bereit, aber das Manko lässt sich wie folgt beheben:
- Entweder durch eine spezielle Gruppierung innerhalb der Pivot-Tabelle
- oder durch eine zusätzliche Spalte in der Datenbasis
Pivot-Berichte mit Gruppierung nach Wochen (links) und nach Kalenderwochen (rechts)
weiterlesen
