Tutorials

Die Altersstruktur auf einen Blick darstellen? Geht ohne Formel mit Pivot und zwei Joins in Power Query

19.12.2018   Hildegard Hügemann

Die Altersstruktur der Beschäftigten einer Firma lässt sich über ein Pivot-Diagramm übersichtlich darstellen.

  • Doch wie wird die zugrundeliegende Pivot-Tabelle so aufgebaut, dass im Diagramm alle gewünschten Altersgruppen angezeigt werden?
  • Wie wird sichergestellt, dass bei Zu- und Abgängen in der Belegschaft die Pivot-Auswertung stets auf die aktuellen Daten zugreift?
  • Wie wird das Alter der Beschäftigten ermittelt?

Das Tutorial zeigt anhand zahlreicher Bildschirmfotos, wie die Lösung aufgebaut wird. Alle, die die Schritte gern selbst nachvollziehen wollen, können sich außerdem die Excel-Übungsdatei herunterladen.

 

 

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

4 Comments so far

Jörg TiedemannPosted on  9:54 pm - Jul 8, 2019

… Fortsetzung:

Wenn ich die Beschäftigtentabelle in eine neue Excelmappe kopiere, dann funktoniert auch die Gruppierung ohne Probleme. Auch das zusätzliche Einblenden der Jahre ohne Belegung ist nicht erforderlich.

Mit freundlichem Gruß
Jörg Tiedemann

Jörg TiedemannPosted on  9:45 pm - Jul 8, 2019

Hallo Frau Hügemann,

Ergänzung zu meiner Nachricht von vorhin:

Das Gruppierungsproblem scheint etwas mit der von Ihnen an anderer Stelle beschriebenem PivotCache zu tun zu haben.

Ich habe zunächst die via Power Query erzeugte Tabelle in Excel angezeigt und darauf basierend nochmals eine PivotTable erzeugt. Auch hier funktionierte die Gruppierung nicht. Den PivotTable-Assistenten konnte ich aus der PivotTable via Alt-N, P nicht aufrufen.

Danach habe ich außerhalbe der PivotTable nochmals den Assistente via Alt-N, P aufgerufen und die PivotTable nochmals erstellt. Hierin konnte ich dann die Altersgruppierung vornehmen.

Mit freundlichem Gruß
Jörg Tiedemann

Jörg TiedemannPosted on  9:26 pm - Jul 8, 2019

Guten Abend Frau Hügemann,

vielen Dank für diese sehr gut nachvollziehbare Schritt-für-Schritt-Anleitung für eine verbreitete praktische Anforderung.

Für die Anzeige der nicht belegte Jahre musste ich in den PivotTable-Optionen noch ‚Anzeige – Elemente ohne Daten in den Zeilen anzeigen‘ und ‚Format – Für leere Zeilen anzeigen: 0‘ aktivieren.

Ein Problem bleibt jedoch, ich kann die Altersstruktur nicht gruppieren (Meldung: Kann den markierten Bereich nicht gruppieren). In der auszuwertenden Spalte ‚Alter‘ gibt es weder Textwerte noch Leerfelder.

Haben Sie eine Idee, was das schief gegangen ist. Vielen Dank für eine Antwort.

Frank Arendt-TheilenPosted on  11:50 pm - Dez 20, 2018

Hallo Hildegard, wieder einmal ein gelungenes Tutorial.
Die Altersberechnung von Power Query ist ziemlich „krude“, einfach platt durch 365 zu teilen, nee, aber Deine Korrektur mit 365,25 hilft hier weiter. Ansonsten liesse sich mit der Funktion Date.IsInYearToDate() feststellen, ob ein Gebursttag bereits stattgefunden hat, dann gibt die Funktion true zurück, ansonsten false. Damit käme eine Excel-ähnliche Berechnung zur Anwendung:
.
if Date.IsInYearToDate(#date(Date.Year(DateTime.FixedLocalNow()),Date.Month([#“Geb.-Datum“]),Date.Day([#“Geb.-Datum“]))) then Date.Year(DateTime.FixedLocalNow()) – Date.Year([#“Geb.-Datum“]) else Date.Year(DateTime.FixedLocalNow()) – Date.Year([#“Geb.-Datum“]) – 1)
.
Hieraus liesse sich dann auch eine Power Query-Funktion schneidern:
.
// fxAltersberechnung
(Datum as date) as number =>
if Date.IsInYearToDate(#date(Date.Year(DateTime.FixedLocalNow()),Date.Month(Datum),Date.Day(Datum))) then
Date.Year(DateTime.FixedLocalNow()) – Date.Year(Datum) else
Date.Year(DateTime.FixedLocalNow()) – Date.Year(Datum) – 1
.
Mit freundlichem Gruß
Frank Arendt-Theilen