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
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 Unterschiede 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
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.
Ampel-Diagramm: Säulen je nach Wert automatisch in einer von 3 Farben anzeigen
Ein Besucher unseres Blogs stellte heute die Frage, was einzustellen ist, damit die Säulen eines Diagramm je nach Wert automatisch eine von 3 Farben annehmen.
- Farbe 1 sollen alle Säulen mit Werten kleiner 6 bekommen,
- Farbe 2 soll bei Werten zwischen 6 und 15 zugewiesen und
- Farbe 3 soll bei Werten größer 15 verwendet werden.
Vorschau auf das Ampel-Diagramm, in dem die Säulen je nach Wert automatisch 3 unterschiedliche Farben erhalten
In der folgenden Anleitung beschreibe ich, wie sich diese Aufgabe in 5 Schritten mit Hilfe eines gestapelten Säulendiagramms lösen lässt.
Excel: Schnellbereinigung von störenden führenden Leerzeichen
Adresslisten mit führenden Leerzeichen im Nachnamen oder Vornamen sind in der Weiterverarbeitung immer ein Hindernis. Wie sich solche Adresslisten ohne jegliche Formel auf die Schnelle bereinigen lassen, zeige ich in diesem Beispiel.
Störende führende Leerzeichen lassen sich auch ohne Formel mit der Blitzvorschau bereinigen
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
Die Säulen in einem Diagramm mit einer Trennlinie voneinander abgrenzen
Bei den Kompetenztagen vom 13. bis 15. September in Fulda steht am Dienstag die Tabellen- und Diagramm-Werkstatt auf dem Kursplan. Ein Hauptthema dabei: Wie werden Diagramme und Tabellen übersichtlicher? In diesem Blogbeitrag gebe ich einen Vorgeschmack auf eines der Diagrammbeispiele.
Es ist keine Seltenheit, dass die Ergebnisse der letzten 12 bis 18 Monate als Säulen gezeigt werden, damit neben der Entwicklung im aktuellen Jahr auch der Vergleich zum Vorjahr möglich ist. Das Problem bei so vielen Säulen: die Übersicht geht verloren. Daher wäre es hilfreich, die Säulen des aktuellen Jahres von denen des Vorjahres optisch zu trennen. Mit einer senkrechten Linie, die per Hand eingefügt wird? Das wäre zwar eine Option, aber besser ist es, wenn die senkrechte Trennlinie fester Bestandteil des Diagramms ist.
Vorschau auf das Diagramm mit gut sichtbarer senkrechter Trennlinie zwischen den beiden Jahren
Wie im abgebildeten Diagramm die dicke farbige Linie als Trenner genau zwischen den beiden Jahren erscheint, erkläre ich in der folgenden Anleitung. Die Lösung basiert darauf, dass die senkrechte Größenachse, die normalerweise ganz links steht, in eine Trennlinie umfunktioniert wird, die sich flexibel verschieben lässt.
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 sich in Power Query jeder Umsatz einer der fünf Preiskategorien (A bis E) zuordnen lässt.
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.
Lern-Snack | Pfade NICHT in Links umwandeln
Die Excel-Tabelle mit Laufwerkspfaden wäre so praktisch, würde Excel nur nicht jeden Pfad in einen Hyperlink umwandeln. Kein Problem, stellen Sie sich einfach Excel so ein, wie Kai Schneider und Markus Hahner es hier zeigen. Dann bleiben Ihre Pfad-Eingaben unverändert. Der Tipp gilt übrigens auch für Word und PowerPoint!
Wenn Ihnen dieser Lern-Snack gefallen hat, dann freuen wir uns über Feedback. Sie haben Wünsche und Anregungen für weitere Lern-Snacks? Lassen Sie es uns wissen. Sie erreichen uns per E-Mail, auf unserer Facebook-Seite oder in unserem Blog (hierzu bitte den Titel des Lern-Snacks anklicken und am Ende der dann angezeigten Seite die Kommentarfunktion nutzen).
Übrigens: Die Lern-Snacks zeichnen wir mit der Liveereignis-Funktion von Microsoft Teams auf.
Excel-Formeln übersichtlicher und schneller machen mit der neuen Funktion LET
Als Office-Insider steht mir seit kurzem die Excel-Funktion LET zur Verfügung. Mit ihr lassen sich Formeln lesbarer machen und Berechnungen in Formeln werden beschleunigt. Wie ich mit LET die Funktion ISOKALENDERWOCHE optimiere und einen gewichteten Mittelwert ohne Hilfsspalte berechne, zeige ich in diesem Beitrag.
