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

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 Versionsunterschiede es derzeit gibt, zeige ich im folgenden Kurzvideo.
Wer sein Wissen zu Power Query erweitern will: Hier geht’s zum aktuellen Kursangebot.

 

Die Lösung für Eilige: In 3 Schritten zur eigenen Formatvorlage für Pivot-Tabellen

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

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.

Weiterlesen →

Detaillierte Anleitung: Ampel-Diagramm in 5 Schritten

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.

Mit SUMMENPRODUKT lassen sich auch visuelle Auswertungen steuern

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 …

Weiterlesen →

Power Query: Mit einer M-Funktion die Ergebnisse einer Auswertung gruppieren

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:

  • Eine M-Funktion ist weniger fehleranfällig.
  • Sie lässt sich leicht anpassen und damit auch für andere Fälle wiederverwenden.

Nachfolgend beschreibe ich, wie eine solche Funktion erstellt und angepasst wird und für welche Zwecke sie sich noch einsetzen lässt.

Weiterlesen →

Fuzzy Matching macht’s möglich: Mit Power Query unvollständige Angaben entschlüsseln und ergänzen

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:

  • mal wurde der Bindestrich im Namen weggelassen,
  • mal wurde der Name abgekürzt,
  • mal die Bezeichnung GmbH vergessen.

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.

Weiterlesen →

Mit der neuen Array-Funktion EINDEUTIG zu einer dynamischen Dropdownliste

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.

Datenüberprüfung mit dynamischer Liste

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 →

Das Filtern wird viel einfacher mit neuen Funktionen in Excel 365

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

Vorschau auf das fertige Ergebnis mit FILTER

Weiterlesen →

Dynamische Dropdownliste zur individuellen Kalenderwochenauswahl bereitstellen

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

Weiterlesen →

Pages:  1 2 3 4 5 6 7 ... 11 12