Excel – PowerPivot – Einführung – Verknüpfung von Tabellen

PowerPivot ist ein sehr mächtiges Add-in für Excel 2010 und neuer. Allerdings steht aktuell das Add-In nur im einzeln erworbenen Excel und über die Office-Versionen Office Professional Plus und Office 365 ProPlus zur Verfügung. Um PowerPivot nutzen zu können, muss das Register PowerPivot sichtbar sein. Prüfen Sie zur Not über DATEI – Optionen – Add-Ins, ob das COM-Add-In installiert und aktiviert ist.

Bei PowerPivot geht es wie bei Pivot um die Auswertung großer Datenmengen. Daten können abgerufen, konsolidiert und über weiterer Funktionen (DAX-Funktionen in englischer Sprache) bearbeitet werden. SVERWEISE zwischen Tabellen, um Informationen für die Pivot-Auswertung zu Ergänzen sind auch nicht mehr unbedingt erforderlich.

Im Beispiel liegen zwei Datenlisten vor. In der linken stehen Vorgänge mit sechsstelligen Vorgangsnummern, in der rechten befinden sich Projekte. Die beiden ersten Nummern der Vorgangsnummern bezeichnen die Projekte. Wenn ich nun eine Auswertung zu Kostenstellen fahren möchte, müsste ich zunächst bei jedem Vorgang identifizieren, zu welchem Projekt der Vorgang gehört. Diese Arbeiten erledige ich mit PowerPivot.

Zunächst werden beide Datenlisten markiert und als Tabelle formatiert. Wahlweise über das Register START und dort Formatvorlagen – Als Tabelle formatieren oder über die Tastenkombination STRG + T.

436a

Bestätigen Sie den Datenbereich. Ihre Tabellen beinhalten Überschriften, dann bestätigen Sie auch dies. Wählen Sie eventuell noch ein ansprechendes Layout.

436b

Die Tabellen sind nun nicht nur optisch aufgehübscht und mit Filtersymbolen versehen. Die Bereiche können auch benannt und so direkt über Formeln angesprochen werden. Wechseln Sie zur Umbenennung der Tabellen in das Register TABELLENTOOLS – Eigenschaften – ENTWURF, dort können Sie die Tabellen einzeln ganz links umbenennen.

Im Beispiel wähle ich tab_Projektdaten und tab_Kostenstellen. Die Namen der Tabellen tauchen dann auch unter FORMELN – Definierte Namen – Namens-Manager auf.

436c

Klicken Sie auf eine der Tabellen. Wechseln Sie nun auf das Register POWERPIVOT – Tabellen und wählen Zu Datenmodell hinzufügen. Wechseln Sie von der PowerPivot-Ansicht auf Excel zurück und führen Sie diesen Schritt für die zweite Tabelle aus.

436d

Es befinden sich nun beide Excel-Tabellen im PowerPivot Data Model. Hier werden die Daten sehr effizient gespeichert. Die Verbindung zwischen den Tabellen ist noch nicht geschaltet.

436f

Ich formatiere zunächst die Zahlen noch so, wie ich sie später in der Auswertung sehen möchte.

436g

Wählen Sie nun Spalte hinzufügen an und wechseln anschließend in die Bearbeitungszeile. Formeln werden hier immer für alle Spalten angegeben. Einige DAX-Funktionen sind entsprechend der normalen Excel-Funktionen zu nutzen. Klammern müssen korrekt geschlossen werden.

Die DAX-Funktion =LEFT([Vorgang];2) gibt die beiden linken Zeichen der Spalte Vorgang zurück.

436h

Diese neue Spalte sollte noch umbenannt werden. CalculatedColumn1 ist ja eher ein Zustand, als ein vernünftiger Name. Klicken Sie wahlweise doppelt auf den Spaltennamen oder mit rechter Maustaste auf den Spaltennamen und dann auf Spalte umbenennen.

437k

Zuletzt müssen die beiden Tabellen noch verknüpft werden. Wechseln Sie dazu auf das Register Entwurf und dann klicken Sie auf die Schaltfläche Beziehung erstellen.

436l

Wählen Sie den Schlüssel aus, über den die beiden Tabellen korrespondieren. Wichtig an dieser Stelle: In einer Tabelle gibt es die Informationen nur einmal, in der anderen mehrfach (n) – hier wird also eine 1:n-Beziehung wie in Access oder anderen Datenbanken erstellt.

436m

Erzeugen Sie nun die PivotTable. Wechseln Sie zurück auf das Register Home und dort auf PivotTable.

436q

Wählen Sie den Ort aus, an dem die PivotTable platziert werden soll. Ich würde fast immer ein Tabellenblatt pro PivotTable bevorzugen. Es sei, Sie wollen mehrere Berichte und Diagramme auf ein Blatt bringen (Dashboard).

436r

Sie können die Felder aus beiden Tabellen zur Erstellung der PivotTable nutzen. Ziehen Sie die Feldnamen der einzelnen Tabellen auf zum Beispiel Zeilen oder Spalten bzw. zur Auswertung in die Werte.

437s

Hier geht es zur Übungsdatei:
Excel_436_PowerPivot_1

Und hier ist Video # 1

18 Kommentare

  1. Jens Uebelhack

    Lieber Andreas,
    so langsam weiß ich nicht mehr, wo ich dich erreichen kann. Mir schwirrt der Kopf: YT, Fatzebuk, Block, Google+…
    Ich denke, hier bin ich richtig!
    Ich habe mir gestern mal PowerPivot runtergeladen. Ich arbeite noch mit meiner geliebten Windows XP-Version, 32-bit, und war irrigerweise der Annahme, dass eben PowerPivot nur in Verbindung mit Windows 7 funktioniert.
    Jedenfalls bin ich begeistert von diesem Add-In.
    Nun habe ich auch noch Excel 2010 und kann diesen Datum-Schnitt nicht nutzen. Aber: mit der Funktion year(Datum) in PowerPivot habe ich eine neue Spalte angelegt und filtere dann damit meine umfangreichen Daten aus verschiedenen Jahren.
    Wenn man allerdings ein Datum mit der Funkion format(Spalte-Datum, „mmmm“) formatieren will, klappt das nicht.
    Vielleicht hast du ja eine Lösung.
    Schönen zweiten Advent wünscht dir und deiner Familie ganz herzlich

    Jens Uebelhack

    • thehosblog

      Hallo Jens,
      versuche es bitte mal mit großen M’s =FORMAT([Datum];“MMMM“)

      Die Oberfläche bei XP sieht anders aus. Ich meine, der Umfang ist nicht ganz identisch.
      Beste Grüße
      Andreas

  2. Sebastian

    Hallo Andreas,
    leider hab ich genau das selbe Problem wie der gute Jens. Also ich kann in Excel 2010 auch nicht die Zeitachse aufrufen und frage mich wie ich das jetzt für meine Version hinbekomme.
    Gruss
    Sebastian

    • thehosblog

      Hallo, die Zeitachse gibt es erst mit Excel 2013. Ich erwähne es ganz kurz um Minute 8 herum.
      Beste Grüße
      Andreas

  3. jan Wilkowski

    Hallo Andreas,

    ich bin von Deinen Videos begeistert. Guter Praxisbezug, toll erklärt, einfach nachzumachen.

    Mit Deinen letzten Videos hast Du bei mir auch die Scheu genommen, mich mit Powerpivot auseinanderzusetzen. Zu der Verknüpfung von Tabellen habe ich aber noch eine Frage oder vielleicht einen Wunsch für die Nr. 4 ;-).

    Ich habe eine relative große Tabelle mit etliche Spalten die Werte – um bei Deinem Beispiel zu bleiben – zu einzelnen Projekten gespeichert sind. Dann habe ich weitere Tabellen, in denen unterschiedliche Eigenschaften zu diesen Projekten gespeichert sind. Bspw. die Schulungsteilnehmer. Ich möchte nun innerhalb einer Pivot-Tabelle die Werte einzelner Projekte berechnen, an denen bestimmte bspw. Teilnehmer anwesend waren. Bspw. alle Schulungen an denen u.a. Herr Mustermann anwesend war, hatten insgesamt einen positiven Deckungsbeitrag etc. Da hier aber keine 1:1 sondern eine 1:n Beziehung vorliegt, läuft die vorgestellte Funktion immer auf einen Fehler und auch über eine Verknüpfung direkt in der Pivot-Tabelle komme ich derzeit nicht weiter.

    Zur Zeit setze ich mehrere Pivot-Tabellen ein und verknüpfe die Tabelle mit den Werte mittels sverweis mit diesen Tabellen die dann die bspw. Projektnr. mit den gesuchten Eigenschaften enthält. Muss dann aber bei Änderung der Filterkriterien die Pivot-Tabelle immer aktualisieren. Alles sehr unelegant und fehleranfällig.

    Ich würde mich über eine Antwort freuen.

    Beste Grüße

    Jan

    • thehosblog

      Hallo Jan,
      Danke für Dein Feedback. Deiner Problemstellung kann ich gerade gedanklich nur leider nicht folgen, vielleicht kannst Du ein Beispiel der Daten hochladen und das geplante Resultat skizzieren.
      Gruß aus dem ICE
      Andreas

      • thehosblog

        Zwei Dinge…
        Schreibe in der einen Tabelle nicht =“ja“, sondern =1
        Du willst damit ja nur etwas Zählbares erzeugen.

        Dann nimm folgende Rechnung
        =COUNTX(RELATEDTABLE(Tab_Auftrag_Suchkriterium_I);Tab_Auftrag_Suchkriterium_I[Leistung_vorhanden])

        Bei COUNTX handelt es sich um ZÄHLENWENN. Dazu guck mal in folgendes Video

  4. Jan Wilkowski

    Hallo Andreas,

    vielen Dank für Deine schnelle Antwort. Ich habe die Funktion gleich ausprobiert, sieht auf den ersten Blick genau danach aus, wonach ich gesucht habe. Leider ändert sich das Ergebnis jedoch nicht, wenn ich den Filter (Datenschnitte) auf die Leistungen ändere. Gibt es danach noch ein trick, dass in der Funktion COUNTX Filter berücksichtigt werden?

    Vielen Dank und beste Grüße aus Oldenburg

    Jan

  5. Jan

    Hallo Andreas,

    ich habe Dein Tipp auf einem Rechner und noch einmal ganz in Ruhe getestet und nun klappt es. Noch einmal vielen Dank und ich freue mich auf Deinen nächsten Videos.

    Jan

  6. Kerstin

    Hallo Andreas,

    ich hoffe, Du kannst mir helfen. Ich suche nach einer Summewenn-Funktion als Spaltenfuntkion in Power Pivot, die ich nur innerhalb einer Tabelle anwenden kann.

    Beispiel: Meine Tabelle besteht aus 3 Felder: einer ID, einer Master-ID und dem Umsatz pro ID. Ich möchte nun den Umsatz pro Master-ID errechnen. Mit der Funktion calculate komme ich nicht weiter, da ich hier konkrete Filter benennen muss. Ich habe aber sehr viele verschiedene Master-IDs, für die ich nicht regelmäßig neue Measures erstellen kann. Eine Spaltenformel wäre mir wesentlich lieber, da ich dann weitere Berechnungen in Power Pivot durchführen kann.

    LG Kerstin

    • thehosblog

      Gefunden!

      =CALCULATE(sum(Tabelle1[Wert]);ALLEXCEPT(Tabelle1;Tabelle1[Gruppe]))

      als Formel in einer Spalte

  7. Uwe Schwenzner

    Hallo Andreas, vielen Dank für Deine tollen Lehrvideos.
    Ich habe ein Problem mit den Beziehungen. Ich habe eine Tabelle mit Jahr und Beworbenen, eine Tabelle mit Jahr und Aktiven. Beide Tabellen sind über die Beziehung Jahr miteinander verbunden.
    Allerdings scheinen die Pivot-Ergebnisse davon abhängig zu sein, in welcher Reihenfolge die Beziehung erstellt wurde, d.h. welche der beiden Tabellen Basis und welche die Suchtabelle ist. Die Werte im Pivot sind nur korrekt je Jahr, wenn das Jahr aus der Suchtabelle als Zeile definiert wurde.
    Genauso liefert die Funktion Related mal Fehler, mal die korrekten Werte.
    Sehe ich das so richtig? Und wie kann man sich vor solchen Problemen schützen?
    LG Uwe

    • thehosblog

      Hallo Uwe,
      ohne einen Eindruck von den Daten zu haben, kann ich zu dieser Beziehung leider nichts sagen. Aber es ist schon richtig, dass die Reihenfolge der Verknüpfung von Bedeutung ist.

  8. Marvin

    Hallo Andreas!
    Als ich dein Video über PowerPivot gesehen habe dachte ich „wow, genau das was ich brauche!“. Aber anscheinend hab ich da was falsch verstanden bei den verknüpften Tabellen. Ich geb dir mal mein Beispiel:
    Tabelle 1………………………………………………………………..Tabelle 2
    Spalte A…….Spalte B………………………………………………..Spalte A……..Spalte B……………Spalte C
    Nummer……..Gesellschaft………………………………………….Nummer………Gesellschaft………Name
    123……………A………………………………………………………….123…………….A……………………..Hans
    456……………B…………………………………………………………..456…………….B…………………….Karl

    So, ich dachte jetzt, ich kann die beiden Tabellen schön in einer Pivot zusammenfassen mit diesen Verknüpfungen. Wenn ich aber jetzt daraus eine Pivot erstelle (über PowerPivot natürlich, verknüpft hab ich die Spalte Nummer) und beispielsweise „Nummer“ aus Tabelle 1 bei [Zeile] reinziehe und dann von Tabelle 2 Gesellschaft ODER Name auch bei [Zeile] reinziehe, dann erhalte ich:

    123
    – A
    – B
    456
    – A
    – B

    123
    – Hans
    – Karl
    456
    – Hans
    – Karl

    Und das stimmt ja nicht. 123 hat ja keinen Karl oder B.
    Mach ich das mit „Nummer“ aus Tabelle 2 + Gesellschaft aus Tabelle 2 + Name aus Tabelle 2 funktioniert das wie es soll, aber ich dachte der Sinn dahinter ist eigentlich, dass man nun aus verschiedenen Tabellen matchen kann?

    Kannst du mir bitte hierzu was sagen? Wo ist mein (Denk)Fehler?

    LG

    • thehosblog

      Hallo,

      Tabellen matchen geht. Dazu habe ich auch ein entsprechendes Video mit der SQL-Anweisung JOIN erstellt. Das ist aber bei weitem nicht der Sinn hinter PowerPivot… also eigentlich nur ein Randeffekt. Die Tabellen sollten schon möglichst komplett sein und können dann um weitere Dimensionen, Berechnungen etc erweitert werden.

      Beste Grüße
      Andreas

      PS: Vielleicht kannst Du irgendwo Beispieldaten hochladen. Ein Tabellenkonstrukt in einem Fließtext zu klären ist ein wenig schwer.