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 →

Power Query-Abfragen schützen: Geht das?

Power Query ersetzt an vielen Stellen die teils sehr aufwendige VBA-Programmierung, insbesondere wenn es um die Aufbereitung von Daten für die Analyse geht. Das ist eine große Arbeitserleichterung und Zeitersparnis. Aber da kommt auch gleich die Frage auf: VBA-Code lässt sich per Passwort schützen, aber geht das auch bei Power Query-Abfragen?

Ist der Arbeitsmappenschutz aktiv, können die Power Query-Abfragen noch aktualisiert, aber nicht mehr bearbeitet werden

In Excel 365 kann die Bearbeitung und das Löschen der Power Query-Abfragen per Arbeitsmappenschutz verhindert werden. Weiterlesen →

Daten aus zwei Listen bündeln zu einer lückenlosen Gesamtübersicht mittels JOIN

Welche Produkte bisher welchen Umsatz gemacht haben, ist auf Basis einer Umsatzliste mit Pivot schnell ermittelt. Doch wie lässt sich herausfinden, welche Produkte bis dato nicht verkauft wurden? Dazu gibt es keine Daten, denn die Umsatzliste enthält nur die verkauften Produkte. Hier hilft eine zusätzliche Liste weiter, in der alle Produkte erfasst sind.

Aus den zwei Listen für Umsätze und Produkte wird eine Gesamtliste generiert, die für alle Produkte die Umsatzzahlen zeigt, auch wenn diese bei null liegen

Wie ich mit Hilfe eines Joins eine komplette Liste aller Produkte mit allen Umsätzen erstelle, beschreibe ich in diesem Beitrag.

Weiterlesen →

Erweitertes Kursangebot für Datenanalysten und alle, die es werden wollen

Das Interesse an meinen Kursen zu BI mit Excel ist überraschend stark. Daher werde ich die Kurse wiederholen und zwei neue hinzufügen. Die nächsten Termine:

Power Query-Einstiegskurs | 27. April oder 22. Juni | 9:00 bis 16:30
Mehr erfahren …  |  Zur Anmeldung …

NEU | Power Query-Vertiefungskurs zu JOINS | 15. April oder 10. Mai | 9:00 bis 12:30
Mehr erfahren …  |  Zur Anmeldung …

Power Query-Aufbauworkshop | 20. Mai | 9:00 bis 16:30
Mehr erfahren …  |  Zur Anmeldung …

NEU | Flexible Auswertungen mit Datenmodell, Power Pivot & DAX | 31. Mai | 9:00 bis 16:30
Mehr erfahren …  |  Zur Anmeldung …

 

Meine Pivot-Kurse gehen in die zweite Runde

Das Thema »Auswertungen mit Pivot« ist offenbar ein Dauerbrenner. Meine  vier ONLINE-Kurse zu Pivot im Februar und März waren jeweils ausgebucht. Daher habe ich nun drei weitere Termine im April und Juni geplant.

Pivot-Einführungskurs | Halber Tag | 19. April | 235 Euro (zzgl. MwSt.)
mehr erfahren …

Pivot-Profikurs | Ganzer Tag | 29. April oder 14. Juni | 445 Euro (zzgl. MwSt.)
mehr erfahren …

Wer in die Kurse hineinschnuppern möchte, schaut sich folgende Kurzvideos an:

Weiterlesen →

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 →

Pages:  1 2 3 4 5 6 7 ... 9 10