Excel Pivot und Power Query üben

Lernpfad «Data & Reporting» · Für: Reporting, Analysten

Pfad starten Ohne Anmeldung, du brauchst Excel 365 oder 2021

Für wen dieser Pfad gedacht ist

Der Pfad Data & Reporting richtet sich an alle, die regelmässig Daten aus einem System holen und daraus einen Report machen: Mitarbeitende im Reporting, Business Analysts, Leute in Verkauf, Marketing, HR oder Operations, die monatliche Kennzahlen liefern, und alle, die mit Exporten aus ERP, CRM oder Webanalyse arbeiten. Auch wer sich auf eine Stelle mit Datenfokus vorbereitet, findet hier die passenden Übungen.

Formeln wie SUMME und SVERWEIS solltest du kennen, Pivot und Power Query nicht. Die Schritte führen dich durch jedes Projekt. Wenn dir die Grundlagen fehlen, starte mit dem Basis-Pfad.

Wie Projekte funktionieren

Pivot-Tabellen und Power Query kann man nicht im Browser nachbauen. Deshalb sind die Übungen in diesem Pfad Projekte: Du arbeitest im echten Excel, Excel-Drills prüft das Ergebnis.

1. Datei laden. Jedes Projekt hat eine kleine Excel-Datei mit 150 bis 300 Zeilen: Bestellungen einer Schweizer Firma mit Filialen in Zürich, Bern, Basel, Luzern, Lugano und St. Gallen. Die Daten stehen als formatierte Tabelle bereit, Beträge als Zahlen, Datum als echtes Datum.
2. In Excel bauen. Die Schritte sagen dir genau, wo du klickst, zum Beispiel «Einfügen» › «PivotTable» oder «Daten» › «Aus Tabelle/Bereich». Abgehakte Schritte merkt sich die App in deinem Browser.
3. Fragen beantworten. Mit deiner fertigen Pivot oder Abfrage beantwortest du drei bis vier Kontrollfragen, etwa den Umsatz einer Region im dritten Quartal. Komma oder Punkt, mit oder ohne Hochkomma: Die App versteht beides und sagt dir pro Frage, ob die Zahl stimmt. Stimmt alles, gibt es Bronze und XP wie bei den Formel-Übungen.

Du brauchst Excel 365 oder Excel 2021 für Windows oder Mac. Die Schritte beschreiben die deutsche Oberfläche. Power Query ist in diesen Versionen eingebaut.

Typische Aufgaben im Job

Daten kommen selten sauber an. Bevor ein Report entsteht, muss die Liste bereinigt, ergänzt und verdichtet werden. Typische Aufgaben sind:

• Einen Verkaufsexport nach Region, Produktgruppe und Quartal verdichten.
• Monatszahlen aus einer Liste mit Tagesdaten bilden, ohne eine Hilfsspalte zu bauen.
• Anteile und Ranglisten für die Geschäftsleitung zeigen statt nur Franken.
• Die Marge pro Filiale ausweisen und den Report per Klick auf ein Quartal umschalten.
• Zwei Exporte zusammenführen, etwa Bestellungen mit Kundendaten, und dabei Leerzeichen und doppelte Zeilen bereinigen.

Wer diese Schritte beherrscht, spart jeden Monat Stunden an Handarbeit und liefert Zahlen, denen man vertrauen kann.

Die Projekte im Pfad

Die ersten zwei Projekte sind gratis. Sie führen dich Schritt für Schritt durch deine ersten Pivot-Tabellen. Die drei weiteren gehören zu Premium.

• Pivot: Umsatz nach Region und Quartal Die erste Pivot-Tabelle: Zeilen, Spalten, Werte und Gesamtergebnis.
• Pivot: Bestellungen nach Monat Ein Datum nach Monaten und Quartalen gruppieren, Summe und Mittelwert pro Bestellung.
• Pivot: Anteile und Rangliste Mit «Werte anzeigen als» Prozent vom Gesamtergebnis und Ränge berechnen.
• Pivot: Marge mit Datenschnitt Berechnetes Feld für die Marge, Filter auf ein Jahr und ein Datenschnitt für die Quartale.
• Power Query: Bestellungen und Kunden Zwei Tabellen zusammenführen, Leerzeichen kürzen, Duplikate entfernen und nach Segment summieren.

Fallen, wie im echten Export

Im Power-Query-Projekt sind bewusst Fehler versteckt, wie sie in echten Exporten vorkommen: Kundennummern mit einem Leerzeichen am Ende, vier doppelte Zeilen und ein Kunde ohne Bestellung. Eine schnelle Pivot über die Rohdaten liefert deshalb falsche Zahlen. Erst wenn du die Daten in Power Query kürzt, bereinigst und richtig zusammenführst, stimmen die Antworten. Genau das ist der Grund, warum sich Power Query im Reporting lohnt: Die Bereinigung ist einmal gebaut und läuft beim nächsten Export per Klick auf «Alle aktualisieren» wieder.

Premium schaltet alle Projekte und alle Übungen in den anderen Lernpfaden frei. Wer auch die Formeln für die Bereinigung üben will, findet im Pfad Accounting & Controlling Übungen zu SUMMEWENNS und Duplikaten.