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 →

Per Join in Power Query zum lückenlosen Pivot-Bericht und Pivot-Diagramm

Als Datenbank-Entwicklerin liebe ich Joins. Mit ihnen gleiche ich verschiedene Listen im Nu ab. Dank Power Query gibt es Joins jetzt auch in Excel (siehe dieser Blogbeitrag).
In einem Tutorial für »office-kompetenz« zeige ich, wie per Join von zwei Abfragen eine Pivot-Tabelle entsteht, die lückenlose Einträge für ein Pivot-Diagramm liefert.

Dank Power Query zu einer lückenlosen Darstellung in Pivot

Dabei verwende ich das Beispiel einer Altersanalyse von Beschäftigten. Doch die Technik mit dem Join lässt sich auch auf andere Aufgabenstellungen übertragen.
Tutorial und Übungsdatei zum Mitmachen gibt es hier bei office-kompetenz.

 

Excel-Listen abgleichen und verknüpfen geht ganz leicht: Die 6 magischen Joins von Power Query

Was Datenbanken als Standard können, was sich in Excel mit mehr oder weniger Mühe per SVERWEIS und Zusammenkopieren erledigen lässt, lässt sich in Power Query mit ein paar Mausklicks und ohne jede Formel realisieren.

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 keine Bestellung aufgeben?

Welche Artikel wurden sowohl in Europa als auch in Asien verkauft und mit welchem Umsatz?
Listen abgleichen mit den Joins in Power Query

Um solche Fragen zu beantworten, müssen Listen abgeglichen werden. Wie Sie das mit ein paar Klicks in Power Query mit sogenannten Joins erledigen, zeige ich in diesem Blogbeitrag auf office-kompetenz.

Und wer mehr wissen möchte zu Power Query, Datenmodell, Power Pivot, DAX und M sollte sich schnell einen Platz bei den Power BI-Kompetenztagen im Oktober 2018 sichern.

Kein Ärger mehr mit Gruppierungen in Pivot-Tabellen: Praxis-Leitfaden als PDF-Download

Wer schon folgendes Pivot-Problem hatte, kann ab sofort entspannt aufatmen:

  • In einer Pivot-Tabelle wurden die Daten nach Jahr und Quartal gruppiert.
  • In einer zweiten Pivot-Tabelle wird eine Gruppierung nach Monaten vorgenommen.
  • Plötzlich sind beide Pivot-Auswertungen nach Monaten gruppiert.

Ursache für dieses seltsame Verhalten ist der Pivot-Cache, den sich beide Pivot-Tabellen „teilen“.

Der Ratgeber zum Umgang mit dem Pivot-Cache als PDF

Für alle, die mit solchen Problemen schon konfrontiert waren, habe ich ein Tutorial geschrieben. Es liefert Tipps, wie sich Fallstricke vermeiden lassen und wertvolle Hintergrundinformationen zum Verständnis.
Als Profi-Tipp zeige ich, wie ermittelt wird, ob in einer Excel-Arbeitsmappe mehr als ein Pivot-Cache vorhanden ist.

Interessenten können das Tutorial bei office-kompetenz als PDF herunterladen.

Formatierungen in Zellen in auswertbare Informationen umsetzen

Beim Überführen von Daten in ein neues System stand einer meiner Kunden vor dem Problem, Informationen aus Daten auszulesen zu müssen, die per Formatierung hinterlegt waren.
Alle Kundennamen, bei denen eine Kundendatenabfrage im Jahr 2016 durchgeführt wurde, waren per Fettschrift gekennzeichnet. Diese Information sollte nun in eine neue Spalte Aktualisierung mit dem Eintrag 2016 überführt werden.

Formatierungen in Werte umsetzen

Bild 1: Alle mit Fettschrift gekennzeichneten Namen sollen in der Spalte Aktualisierung das Jahr 2016 eingetragen bekommen

Niemand wollte dies für mehrere tausend Datensätze per Hand tun. Abhilfe schaffte die alte Excel4-Makrofunktion ZELLE.ZUORDNEN. So ließ sich das Problem auf die Schnelle lösen.

Weiterlesen →

Beiträge durchblättern:  << 1 2 3 4 5 6 7 8 9 >>