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.
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
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.
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.
SUMMENPRODUKT gehört zu meinen Lieblingsfunktionen. Ich nutze sie häufig zum Auswerten von Listen. SUMMENPRODUKT bietet einfach mehr Flexibilität als ZÄHLENWENNS, SUMMEWENNS oder Pivot-Tabellen, wenn spezielle Kriterien zu berücksichtigen sind. Jetzt habe ich eine weitere Einsatzmöglichkeit für SUMMENPRODUKT entdeckt: ich nutze sie auch bei visuellen Auswertungen.
Hier das Beispiel: Welche Umsätze wurden im letzten Monat mit sog. Auslaufartikeln erwirtschaftet? Dazu sollen in der Umsatzaufstellung automatisch alle Zeilen farbig hinterlegt werden, in denen ein Auslaufartikel steht. Das erledige ich mit einer Bedingten Formatierung, die von SUMMENPRODUKT gesteuert wird.
Links die automatische Kennzeichnung von Umsätzen mit Auslaufartikeln und rechts die zugehörige Liste mit dem Bereichsnamen Auslaufartikel
Diese Regel erstelle ich mit den folgenden Schritten …
Einer meiner Kunden möchte seine Umsätze nach Preiskategorien auswerten. Die Umsatzdaten werden aus einer SQL-Datenbank mittels Power Query abgerufen und aufbereitet. Die Frage lautet nun, wie in Power Query jeder Umsatz einer der fünf Preiskategorien (A bis E) zugeordnet werden kann.
Klingt nach einem ungefähren SVERWEIS in Power Query. Wie das durch Anfügen von Abfragen und anschließendes Sortieren realisiert werden kann, habe ich am 26.2.2019 im Blogbeitrag Ergebnisse in einer Auswertung gruppieren: Wie ich einen ungefähren SVERWEIS in Power Query realisiere gezeigt.
Eine Alternative zu diesem Vorgehen ist das Erstellen einer M-Funktion in Power Query. Das bietet zwei Vorteile:
Nachfolgend beschreibe ich, wie eine solche Funktion erstellt und angepasst wird und für welche Zwecke sie sich noch einsetzen lässt.
Mit einer noch recht neuen Funktion in Power Query konnte ich kürzlich ein verzwicktes Datenproblem bei einem meiner Kunden lösen. Und zwar sollten die Außendienstmitarbeiter zusammentragen, welche Kunden sie in den letzten zwei Wochen besucht haben. Eigentlich eine einfache Sache. Doch beim Sichten der abgegebenen Listen wurde schnell klar, dass die eingegebenen Kundennamen von denen im firmeneigenen CRM-System zum Teil abwichen:
Die unvollständig eingegebenen Firmennamen links verursachen Probleme beim Zuordnen zu den Stammdaten im CRM-System
Wie lassen sich solche unvollständigen Angaben den Kundendaten im CRM-System zuordnen? Wie können die korrekten Kundennamen und die zugehörigen Kundennummern ermittelt werden?
Ich löste die Aufgabe in 4 Schritten mit einem Fuzzy-Join in Power Query.
In meinem Blogbeitrag »Das Filtern wird viel einfacher mit den neuen Funktionen in Excel 365« habe ich gezeigt, wie mit der neuen Array-Funktion FILTER auf einfache Weise eine gefilterte Liste generiert werden kann. Es ging darum, die Liste nach einem bestimmten Kunden zu filtern und das Ergebnis an separater Stelle aufzulisten.
Im heutigen Beitrag beschreibe ich, wie auch die Auswahl des Filterkriteriums vereinfacht werden kann und zwar durch das Einrichten einer dynamischen Dropdownliste.
Vorschau auf eine Datenüberprüfung mit dynamischer Liste
Wie stelle ich der Datenüberprüfung eine stets aktuelle Liste aller vorhandenen Kunden zur Verfügung? Das sind die Schritte: Weiterlesen
Seit Herbst 2018 verfolge ich Berichte über Excels neue dynamische Arrayfunktionen. Allerdings konnten sie bisher nur von Anwendern mit dem Updatekanal »Insider« und »Monatlich gezielt« genutzt werden. Nun sind Funktionen wie EINDEUTIG, FILTER, SORTIEREN und SORTIERENNACH auch im Updatekanal »Monatlich« verfügbar.
Da das Sortieren und Filtern von Listen zu den häufigen Aufgaben in Excel gehört, habe ich zuerst die Funktionen FILTER und SORTIEREN ausprobiert. Eines kann ich schon vorwegnehmen: Ich bin erstaunt, wie einfach die Bedienung ist und bin begeistert über die vielen Möglichkeiten. Hier ein erstes Beispiel.
Vorschau auf das fertige Ergebnis mit FILTER
Diese Aufgabe erhielt ich kürzlich: Die Außendienstmitarbeiter sollen beim Erfassen ihrer Besuchsberichte in einem Excel-Formular neben den stattgefundenen Besuchen auch die Besuchstermine vermerken, die für die kommenden 6 Kalenderwochen vorgesehen sind. Da bei der Planung die konkreten Termine noch nicht vorliegen, soll zunächst die Kalenderwoche plus Jahresangabe im Format 02/2019 erfasst werden.
So weit, so gut. Doch wie kann sichergestellt werden, dass die geplanten Termine in der richtigen Form eingeben werden, denn später sollen alle Plantermine automatisch ausgewertet werden? Und wie lässt sich die für die Außendienstler aufwändige Tipparbeit vermeiden? Ein Dropdown scheint die richtige Lösung zu sein. Aber wie muss es beschaffen sein, damit stets die kommenden 6 Kalenderwochen zur Auswahl stehen (Bild 1)? Dazu kann der Dropdownliste eine dynamisch berechnete Datenquelle zugeordnet werden.
Bild 1: Per dynamischem Dropdown fehlerhafte Kalenderwochen-Eingaben vermeiden
