Tabelle zu PivotTable

Vielspaltige Tabelle für Pivot vorbereiten

Eine „vernünftige“ PivoTabelle bedarf eines „vernünftigen“ Aufbaus. Die vorliegenden Daten sollten so wie in einer Datenbank geordnet sein. Das hört sich gewiss etwas theoretisch an und ist auch schwer nachzuvollziehen. Genauso, wie es mir schwer fällt, mit einfachen Worten das zu beschreiben, was ich veranschaulichen will. Ein Stichwort wäre vielleicht: „Design vs. Funktionalität“

Einstieg

Einer Personengruppe wurde ein Seminarangebot für verschiedene Kurse aus dem Bereich der EDV vorgelegt. Jeder der Befragten hat drei Angebote angekreuzt und mittels Buchstabenwertung die Priorität festgelegt. Ein „A“ für die höchste Priorität, gefolgt von einem „B“ und ein „C“ für die niedrigste Stufe.

Das Ganze ist natürlich nur Fiktion, um ein sinnvolles und einigermaßen realistisches Umfeld zu schaffen. Aus diesem Grunde habe ich als Datenbasis echte Namen verwendet. Es handelt sich um eine Tabelle aller Mitglieder des Deutschen Bundestages, Stand 04.05.2015 nebst Fraktionszugehörigkeit. Jedem dieser Abgeordneten habe ich per Zufallsprinzip drei Kurse „verpasst“. Natürlich soll den Betroffenen damit eine Fähigkeit zu- oder abgesprochen werden, hier war nur der Zufall am Werk.  😆 

Grundsätzlich gilt, dass ich für die ersten Abbildungen und auch Probeläufe die ersten 12 Namen verwendet habe, was den Mitgliedern mit den Namen „A…“ entspricht. Das hat rein pragmatische Gründe und stellt keine Wertung dar.

Die gekürzte Liste können Sie hier herunter laden, später werden Sie die komplette Liste einsetzen. Falls Sie erst einmal unbeschwert weiterlesen möchten, sehen Sie hier ein Bildschirmfoto der kurzen Liste:

Die kurze Liste der Interessenten

Die kurze Liste der Interessenten

Für eine tabellarische Darstellung ist das in der Form sehr gut. Und wenn Sie die Liste dann noch mittels StrgT oder StrgL als („intelligente“) Tabelle einrichten, dann lässt sich schon manches damit anfangen.

Das Ziel ist es aber eine PivotTable aus diesen Daten zu generieren. Der erste Versuch „aus dem Hndgelenk“ ist aber recht ernüchternd, denn so ist das ja keineswegs eine sinnvoll auswertbare PivotTabelle:

Der erste Versuch, alle Felder markiert

Der erste Versuch, alle Felder markiert

Zugegeben, die Daten lassen sich noch etwas anders anordnen, aber irgendwie ist es nicht das, was „man“ von einer PivotTable erwarten kann:

Trotz anderer Anordnung ein unbefriedigendes Ergebnis

Trotz anderer Anordnung ein unbefriedigendes Ergebnis

Das sieht jetzt zwar so ähnlich aus, wie in der Ursprungsliste, aber ich kann die Vorteile einer Pivot-Auswertung nicht nutzen. Die Fragestellung wäre beispielsweise: Welche Abgeordnete haben „Excel Einsteiger“ als erste Wahl (A) gekennzeichnet? Oder: Wie viele Interessenten gibt es für jedes Seminar? Mit einer „ordentlichen“ Datenbasis lässt sich das recht leicht und rasch kenntlich machen und darstellen.

▲ nach oben …

Funktionalität geht vor

Sie erkennen, dass die Daten anders aufgebaut sein müssen, wenn eine Auswertung erfolgreich sin soll. Am Beispiel der ersten beiden Namen der Aufstellung stelle ich Ihnen hier das Muster vor, wie es zielführend ist. In der Basis-Tabelle würden Herr van Anken und Herr Albani dann so dargestellt werden:

Datenbank-artiger Aufbau der Liste ist das Ziel

Datenbank-artiger Aufbau der Liste ist das Ziel

Zugegeben, die Namen sind redundant. Das liegt an Excel, welches ja keine echte Datenbank ist sondern eine Tabellenkalkulation. Aber es ist wichtig, dass in jeder Zeile die festen Werte (Name, Vorname, Fraktion) wiederholt werden und die variablen Werte (Kurs und Wertung des Kurses) in die jeweils gleiche Zeile eingetragen werden. Drei gewünschte Kurse, drei Zeilen. Das muss so sein. Dabei spielt die Anordnung der Zeilen an sich keine Rolle,  die Sortierung übernimmt Excel für Sie. Bei der Wertung von Herrn Albani ist das deutlich zu sehen.

Da Sie jetzt die Daten im korrekten Format vorliegen haben, beschreibe ich noch einmal kurz aber dennoch Schritt für Schritt den Weg, den ich danach gegangen bin. Zuerst ein Klick in den Datenbereich, hier also A1:E7. Anschließend im Menü Einfügen wählen und dort auf PivotTable klicken. Automatisch wird der zusammenhängenden Datenbereich markiert und Sie können im neuen Fenster einfach auf OK klicken, wenn Sie die PivotTabelle in einem neuen Blatt erstellen wollen.

Sofort wird ein neues Arbeitsblatt mit einer neuen PT (PivotTabelle) erstellt. Wenn Sie alle Felder mit einem Häkchen versehen, dann zeigt sich die PT so:

Der erste Eindruck ist ernüchternd

Der erste Eindruck ist ernüchternd

Die Ähnlichkeit zum ersten Versuch mit den in der Quelldatei anders angeordneten Daten ist  durchaus gegeben. Was aber auf jeden Fall auffällt: Die (Leer) – Angaben fehlen vollkommen. Um eine „ansehnliche“ Tabelle mit den gewünschten Möglichkeiten zu gestalten, bin ich diesen Weg gegangen:

  • Im Menü der PivotTable-Tools (ganz rechts) ein Klick auf Entwurf.
  • Ribbon Gesamtergebnisse die erste Auswahl Für Zeilen und Spalten deaktiviert anklicken.
  • Bei Berichtslayout wählen Sie die dritte Möglichkeit, In Tabellenformat anzeigen.
  • Die Spalte D noch in der Breite anpassen und das sieht schon richtig gut aus:

Das brauchbare Endergebnis

Das brauchbare Endergebnis

Dieses Grundprinzip werden Sie in solchermaßen gelagerten Fällen immer anwenden, um zum Ziel zu gelangen. Das „Große ABER…“: Bei diesen 6 Zeilen war das ja schon ein Riesen-Aufwand,wie soll das denn bei den über 600 Abgeordneten in vertretbarer Zeit gehen? Oder bei noch größeren Datenmengen? Eine Formel-Lösung ist zwar denkbar, aber ich bin davon überzeugt, dass die Performance des Programms ganz schön darunter leidet. Ich finde andere Lösungen einfach transparenter und auch besser nachvollziehbar. Zwei davon stelle ich Ihnen hier vor.

▲ nach oben …

Tabelle per VBA (Makro) erstellen

Ausgangslage ist hier die bereits oben angesprochene Tabelle mit allen Abgeordneten. Falls noch nicht geschehen, sollten Sie dieser jetzt herunterladen. Es ist eine ganz normale Excel-Tabelle im gleichen Format, wie Sie dieses bereits kennen gelernt haben, nur mit allen 642 Abgeordneten bis hin zu Frau Zypries aus der SPD-Fraktion.

Die kurze Liste der Interessenten (Auszug)

Die kurze Liste der Interessenten (Auszug)

Da dieses keine Lehrstunde für Makros sein soll, fallen meine Erklärungen und Kommentare vielleicht etwas knapp aus. Auf der anderen Seite habe ich versucht, den Code zugunsten der Lesbarkeit und Nachvollziehbarkeit nicht allzu sehr zu straffen. Manchmal ist weniger (verwenden von speziellen Routinen) eben doch mehr.  😉  Prinzipiell geschieht hier folgendes:

  • Erforderlichenfalls wird ein weiteres Arbeitsblatt erstellt, wo die umgestalteten Daten gespeichert werden.
  • Beginnend in Zeile 2 bis zur letzten Datenzeile wird ab Spalte D bis Spalte L überprüft, ob in der jeweiligen Zelle ein A, B oder C drin steht.
  • Da VBA zwischen Groß- und Kleinschreibung unterscheidet, wandele ich den Zellinhalt für die Prüfung erst einmal in Großbuchstaben um.
  • Ist die Prüfung positiv, dann schreibe ich in die Ziel-Tabelle in die erste leere Zeile den Namen, Vornamen, Fraktionszugehörigkeit und den Kurs sowie die Wertung.

Den Code können Sie direkt kopieren oder hier als *.zip gepackt herunter laden und in Ihre Mappe einfügen. Er gehört in jedem Fall in das Modul DieseArbeitsmappe! Wie Sie das bewerkstelligen, können Sie auch hier im Blog nachlesen.

Option Explicit

Sub DataNachListe()
   Dim wksSrc As Worksheet, wksDst As Worksheet
   Dim fRowSrc As Integer, lRowSrc As Integer, fRowDst As Integer
   Dim Ze As Integer, c As Range, rngZe As Range
   Dim Na As String, Vn As String, Frk As String, Kurs As String
   Dim wks As Worksheet, wksSrcName As String, wksDstName As String
   
   On Error GoTo ErrorHandler
   Application.ScreenUpdating = False
   wksSrcName = "Kurs-Interessen EDV 4 Pivot"
   wksDstName = "Data4Pivot (geordnet)"
   'Worksheets Existenz prüfen, löschen,
   'wieder anlegen und ObjektVariablen zuweisen
   With ThisWorkbook
      .Sheets(1).Name = wksSrcName  '1. Blatt sonst anpassen
      For Each wks In .Worksheets
         If wks.Name = wksDstName Then
            With Application
               .DisplayAlerts = False
               .Sheets(wksDstName).Delete
               .DisplayAlerts = True
            End With
            Exit For
         End If
      Next wks
      .Worksheets.Add After:=.Worksheets(Sheets.Count)
      ActiveSheet.Name = wksDstName
      Set wksSrc = Sheets(wksSrcName)
      Set wksDst = Sheets(wksDstName)
   End With
   
   With wksDst	 'Überschriften
      .Cells(1, 1) = "Name"
      .Cells(1, 2) = "Vorname"
      .Cells(1, 3) = "Fraktion"
      .Cells(1, 4) = "Kurs"
      .Cells(1, 5) = "Wertung"
   End With
   fRowSrc = 2	'1. Datenzeile Quelle / Ziel
   fRowDst = 2
   
   With wksSrc
      lRowSrc = .Cells(Rows.Count, 1).End(xlUp).Row   'Letzte Datenzeile
      For Ze = fRowSrc To lRowSrc
         Na = .Cells(Ze, 1)
         Vn = .Cells(Ze, 2)
         Frk = .Cells(Ze, 3)
         Set rngZe = .Range(.Cells(Ze, 4), .Cells(Ze, 12))
         If WorksheetFunction.CountA(rngZe) > 1 Then  'Kurs eingetragen?
            With wksDst
               For Each c In rngZe
                  Select Case UCase(c) 'Großbuchstaben
                   Case "A", "B", "C"
                     .Cells(fRowDst, 1) = Na
                     .Cells(fRowDst, 2) = Vn
                     .Cells(fRowDst, 3) = Frk
                     Select Case c.Column 'Fund-Spalte
                      Case 4
                        .Cells(fRowDst, 4) = "Windows"
                        .Cells(fRowDst, 5) = c
                      Case 5
                        .Cells(fRowDst, 4) = "Linux"
                        .Cells(fRowDst, 5) = c
                      Case 6
                        .Cells(fRowDst, 4) = "Excel, Einsteiger"
                        .Cells(fRowDst, 5) = c
                      Case 7
                        .Cells(fRowDst, 4) = "Excel, Aufbau"
                        .Cells(fRowDst, 5) = c
                      Case 8
                        .Cells(fRowDst, 4) = "Excel VBA/Makros"
                        .Cells(fRowDst, 5) = c
                      Case 9
                        .Cells(fRowDst, 4) = "Word, Einsteiger"
                        .Cells(fRowDst, 5) = c
                      Case 10
                        .Cells(fRowDst, 4) = "Word, Aufbau"
                        .Cells(fRowDst, 5) = c
                      Case 11
                        .Cells(fRowDst, 4) = "Word, VBA/Makro"
                        .Cells(fRowDst, 5) = c
                      Case 12
                        .Cells(fRowDst, 4) = "PowerPoint"
                        .Cells(fRowDst, 5) = c
                     End Select
                     Select Case c.Column
                      Case 4 To 12
                        fRowDst = fRowDst + 1
                     End Select
                  End Select
               Next c
            End With
         End If
      Next Ze
   End With

ErrorHandler:
   If Err.Number <> 0 Then MsgBox "Fehler Nr.: " & Err.Number & vbCrLf _
    & Err.Description
   With Application
      .DisplayAlerts = True
      .ScreenUpdating = True
   End With
End Sub

Nach dem Aufruf dieses Makros werden Sie die Daten aus dem ersten Tabellenblatt schön übersichtlich im letzten Tabellenblatt mit dem Namen Data4Pivot (geordnet) vorfinden. Insgesamt 1926 Datensätze plus Überschriftszeile. Damit ist die Grundlage, für die Pivot-Auswertung gelegt, die Datenbasis ist geschaffen.

Daraus die PT erstellen, wie oben beschrieben: Alle Felder markieren, dann im Menü Entwurf das Berichtslayout auf Tabellenformat ändern, Teilergebnisse und eventuell die Gesamtergebnisse nicht anzeigen lassen und vielleicht noch eine Leerzeile nach jedem Element anzeigen lassen (reine Ansichtssache  🙂 ).

Wenn sich die Grunddaten (Tabellenblatt Kurs-Interessen EDV 4 Pivot) verändern, muss das Prozedere neu durchlaufen werden. Das ist auch einer der Gründe, dass das Ziel-Tabellenblatt bei jedem Makro-Aufruf erst gelöscht wird. Danach aktivieren Sie im Menü Pivottable-Tools den Punkt Analysieren und -falls sich die Anzahl der Zeilen in den Quelldaten geändert hat Datenquelle ändern (neu einlesen)- und in jedem Fall den Punkt Aktualisieren ausführen.

Da der Zweck dieses Beitrages weder Grundlagen in Sachen Pivot noch Kenntnisse zum Thema VBA vermitteln soll, belasse ich es hierbei. Zumindest was den Part Makros betrifft. Während diese Möglichkeit bei allen Excel-Versionen (zumindest ab 2000) funktioniert, gibt es eine weitere Möglichkeit zum Lösen dieser Aufgabe für Excel, leider aber erst ab Version 2010.

▲ nach oben …

Tabelle mittels POWER QUERY erstellen

Seit Excel 2010 haben Sie die Möglichkeit, von der Microsoft-Seite Power Query herunter zu laden und in Excel zu nutzen. Was sich vielleicht auf den ersten Blick kryptisch oder schwierig klingt, ist ein riesiger Vorteil gegenüber den herkömmlichen Methoden. Nach der Installation des Tools können Sie vollkommen ohne VBA und ohne Formeln derartige Datensammlungen so aufbereiten, dass sie den Vorgaben für PivotTables genügen. Und nach kurzer Einarbeitung oder Gewöhnung ist das eine gewiss fantastische Methode!

Laden Sie das Tool bei Microsoft herunter. Beachten Sie bitte, dass sich 32/64 Bit auf die Excel-Version bezieht und nicht auf das Betriebssystem. Im Regelfall werden Sie also die 32-Bit-Version einsetzen, das selbst Microsoft derzeit vom Einsatz der 64-Bit Version der Office-Produkte abrät. – Die Installation ist einfach und selbsterklärend. Anschließend steht Ihnen im Menü-Bereich des Excel ein neuer Menüpunkt zur Verfügung, meist im Anschluss an  die Entwicklertools: Der Menüpunkt Power Query.

Öffnen Sie bitte die Arbeitsmappe, wo die „unbehandelten“ Daten enthalten sind, beispielsweise diese Datei. Normalerweise bzw. idealerweise ist das Tabellenblatt mit den Kurs-Interessenten das aktuelle Arbeitsblatt. Zu Beginn klicken Sie auf den Menüpunkt POWER QUERY und erwartungsgemäß wird sich eine neue Ribbonleiste, eine andere Symbolleiste öffnen. Achten Sie nun bitte darauf, dass eine beliebige Zelle im Datenbereich markiert, also die aktive Zelle ist. Zeigen Sie nun auf die Ribbon-Schaltfläche Von Tabelle

In der Grppe Excel-Daten "Von Tabelle" auswählen

In der Grppe Excel-Daten „Von Tabelle“ auswählen

… und klicken Sie anschließend darauf. Unter Umständen ist ein zweiter Klick erforderlich, damit sich die Tabelle folgendermaßen darstellt:

Der Datenbereich wird automatisch korrekt ausgewählt, solange der Bereich zusammenhängend ist

Der Datenbereich wird automatisch korrekt ausgewählt, solange der Bereich zusammenhängend ist

Dass die Tabelle Überschriften hat, ist bereits markiert. Und die Größe des Datenbereichs stimmt auch. Also OK und einen kleinen Moment warten. Es öffnet sich ein neues Excel-Fenster mit dem Titel Tabelle1 – Abfrage-Editor. Die Nummer der Tabelle kann natürlich variieren. – Durch die Anzeige der kursiv dargestellten null erinnert das Ganze etwas an die früheren Versuche, aber es ist wirklich anders, ganz anders!

Als erstes kommt die Überlegung, welches sind die Spalten, die in jedem Fall angezeigt werden sollen. Das sind hier: Name, Vorname, Fraktion. Das sind die Werte, die wir in der ersten Version „per Hand“ wiederholt haben, solange ein weiterer Wunsch für einen Kurs vorhanden war. Also immer drei Zeilen mit den Personendaten. 

Ich klicke nun auf den Spaltenkopf der ersten Spalte, welcher hier nicht D ist sondern wie in einer Datenbank einen Namen trägt. Hier ist es Windows:

Nach einem Klick auf den Spaltenkopf

Nach einem Klick auf den Spaltenkopf

Sofort nach dem Klick wir die ganze Spalte markiert. Nun scrolle ich erforderlichenfalls so weit nach rechts, dass die letzte Spalte der Kurse sichtbar ist. Nun die Taste Groß (Shift, Großschreibung) drücken, gedrückt halten und in den letzten Spaltenkopf klicken. Sofort ist der gesamte Bereich der Kurse markiert.

Diese Daten sollen so umgeordnet werden, dass pro Person die ausgewählten Kurse untereinander stehen und natürlich die nicht gewählten keine Lücken bilden. Das haben Sie vielleicht bereits weiter oben per VBA gemacht, aber ich hatte Ihnen ja versprochen, dass es mit Power Query ganz ohne Makros und ganz ohne Formeln geht. Klicken Sie dazu mit der rechten Maustaste in einen beliebigen Spaltenkopf der neun markierten Spalten und anschließend auf den hier markierten Menüpunkt Spalten entpivotieren. Welch Wortschöpfung … 

So werden die Daten vorbereitet …

So werden die Daten vorbereitet …

Aber der Erfolg wird sich sehr schnell zeigen und überzeugt gewiss:

In Windeseile wurde die korrekte Anordnung der Daten erstellt

In Windeseile wurde die korrekte Anordnung der Daten erstellt

Genau so soll es sein. Was wir vorher mühsam von Hand erledigt haben und und bei über 600 Datensätzen auch nicht zugemutet hätten, wurde hier mir wenigen Mausklicks elegant erledigt. Jetzt nur noch im Menüpunkt Start ein Klick auf Schließen&Laden und automatisch wird eine „intelligente“ Excel-Tabelle neu erstellt. Die Bezeichnung ist typischerweise Tabellenn, wobei das nn für eine fortlaufende Nummerierung steht.

Hieraus lässt sich nun mit Leichtigkeit eine neue PivotTabelle erstellen, die Datenbasis ist optimal. Die Abfrage, die Query kann natürlich jederzeit bearbeitet werden und mit allen denkbaren Mitteln der Datenbankabfrage bearbeitet werden. Das nun erstellte Tabellenblatt wird in späteren Beiträgen gewiss die Basis für eine Pivot-Auswertung bilden. Wenn Sie etwas experimentierfreudig sind, dann können Sie auf diesem Wege auch externe Daten im Excel, Datenbank oder CSV-Format laden und bearbeiten

▲ nach oben …

Dieser Beitrag wurde unter Mit VBA/Makro, Musterlösungen, Ohne Makro/VBA, Pivot, Power Query, Tabelle und Zelle abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.