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

Business-Workshop zu Excel-Formularen

Am 9. Mai 2018 ist es soweit: Mein Workshop zu Excel-Formularen erlebt eine Neuauflage. Der eintägige Workshop richtet sich an alle, die eine komfortable und sichere Eingabe von Daten inkl. Auswertung brauchen, z. B.

  • Projektleiter, Projektmitarbeiter sowie Qualitätsmanager
  • Fachanwender aus allen Bereichen, die Abläufe rationalisieren wollen
  • ambitionierte Assistentinnen und Sekretärinnen
  • IT-Professionals, Supporter und Excel-Trainer

Wissenswertes zum Ablauf des eintägigen Workshops

Der Workshop geht von 9:00 bis 16:45 Uhr und findet in Fulda statt.

  • Im Workshop erfahren Sie alles Wichtige zur Formularplanung
  • Sie üben gezielt den Einsatz der einzelnen Formularwerkzeuge
  • Sie setzen Techniken ein, um jederzeit die Datenintegrität zu sichern
  • Sie nutzen Gestaltungstricks, um die Übersichtlichkeit der Formulare zu verbessern
  • Sie lernen, wie Sie Formulardaten geschickt zusammenfassen und auswerten

Hier finden Sie weitere Informationen zum Workshop und die Möglichkeit zur Anmeldung.

Der Flyer zum Workshop enthält alle Informationen. Es gibt ihn hier zum Herunterladen.

Flyer zum Workshop Excel-Formulare

 

So lassen sich Pivot-Berichte nach Wochen und ISO-Kalenderwochen gruppieren

Eine Pivot-Auswertung lässt sich im Nu nach Jahr, Quartal oder Monat gruppieren. Eine wochenweise Gruppierung hingegen bietet Excel nicht an. Zumindest nicht vorgefertigt.

In diesem Blogbeitrag auf office-kompetenz zeige ich, mit welchem Trick auch eine Gruppierung nach Wochen oder sogar nach ISO-Kalenderwochen gelingt.

Pivot-Berichte mit Gruppierung nach Wochen und Kalenderwochen

Pivot-Berichte mit Gruppierung nach Wochen und Kalenderwochen

 

Probleme bei der Datumsgruppierung in Pivot-Tabellen vermeiden

Wer in Excel 2016 ein Datumsfeld in seine Pivot-Tabelle einbaut, erhält automatisch eine Gruppierung nach Jahren, Quartalen und Monaten, wie folgendes Video zeigt.

Doch was, wenn diese Art der Gruppierung gar nicht gebraucht wird?
Was, wenn die Ergebnisse nach Jahren und Quartalen gruppiert werden sollen?
Oder nach Jahren und Monaten? Geht auch eine Gruppierung nach Tagen?

Antworten auf diese Fragen sowie Tipps, wie sich ein zusätzlicher Pivot-Cache vermeiden lässt, gebe ich in einem Beitrag im Blog von office-kompetenzHier geht’s zum Beitrag: Excel 2016: Automatische Datumsgruppierung in Pivot – So funktioniert es problemlos.

2 Spalten nach einem bestimmten Zeichen abgleichen in Excel

Wird neue Software eingeführt, ändert sich mitunter auch die Systematik der Nummern für Artikel, Kunden usw. Zeichenfolgen werden umgestellt, gekürzt oder erweitert. Sollen dann alte und neue Nummern abgeglichen werden, ist das in Excel mit wenigen Handgriffen erledigt. Hier eine Aufgabenstellung, die mir kürzlich zuging:

  • Geprüft werden soll, ob ein bestimmtes Zeichen aus Spalte B auch in Spalte C vorkommt. Hier im Beispiel geht es um die Ziffer 3.
  • In Spalte B steht sie an 6. Stelle, in Spalte C hingegen muss sie an 8. Stelle vorkommen.
  • Trifft das nicht zu, soll diese Abweichung in Spalte C gekennzeichnet werden.

Hier eine Vorschau auf die fertige Lösung. In 5 Fällen gibt es eine Abweichung.

Für diesen Abgleich wird die Funktion TEIL mit der Bedingten Formatierung kombiniert.

Weiterlesen

Mehr aus einer Pivot-Auswertung herausholen mit »Diskrete Anzahl«

Kürzlich erhielt ich die Anfrage, wie in einer Pivot-Tabelle mehrfach vorkommende Informationen nur EINMAL gezählt werden können.
Die Datenbasis für die Pivot-Tabelle ist wie unten gezeigt aufgebaut. Für verschiedene Veranstaltungen werden die teilnehmenden Personen und deren Firmen aufgelistet.

Nun soll ausgewertet werden, wie viele verschiedene Firmen an jedem Event teilgenommen haben.
Die Lösung führt über das Erstellen einer Pivot-Tabelle, deren Quelldaten dem Datenmodell hinzugefügt wurden.

Weiterlesen

2 Tipps für weniger Ärger mit Excel beim Seriendruck in Word

Im Juni bei den Office-Kompetenztagen in Fulda gab es bei meinen Seriendruck-Kursen zwei spannende Fragen aus der Praxis:

1) Wie lassen sich Euro-Beträge, die in Excel korrekt als Währung angezeigt werden, auch beim Seriendruck in Word richtig darstellen? Dort fehlen nämlich das Tausendertrennzeichen, das Komma und zwei Dezimalstellen sowie das €-Zeichen.

2) Wie werden Prozentwerte, die aus Excel übernommen werden, in Word wie gewünscht dargestellt? Sie erscheinen zunächst recht ungewöhnlich, z. B. 6,5 % als 6,5000000000000002E-2 oder 17,5 % als 0,17499999999999999.

Wie sich beide Praxisproblemen lösen lassen, erkläre ich in folgenden Blogbeiträgen:

https://www.office-kompetenz.de/word-seriendruck-euro-betraege-aus-excel-korrekt-anzeigen/

https://www.office-kompetenz.de/word-seriendruck-prozentwerte-aus-excel-korrekt-anzeigen/