Eine „vernün­ftige” Piv­oTa­belle bedarf eines „vernün­fti­gen” Auf­baus. Die vor­liegen­den Dat­en soll­ten so wie in ein­er Daten­bank geord­net sein. Das hört sich gewiss etwas the­o­retisch an und ist auch schw­er nachzu­vol­lziehen. Genau­so, wie es mir schw­er fällt, mit ein­fachen Worten das zu beschreiben, was ich ver­an­schaulichen will. Ein Stich­wort wäre vielle­icht: „Design vs. Funk­tion­al­ität”

Einstieg

Ein­er Per­so­n­en­gruppe wurde ein Sem­i­narange­bot für ver­schiedene Kurse aus dem Bere­ich der EDV vorgelegt. Jed­er der Befragten hat drei Ange­bote angekreuzt und mit­tels Buch­staben­wer­tung die Pri­or­ität fest­gelegt. Ein „A” für die höch­ste Pri­or­ität, gefol­gt von einem „B” und ein „C” für die niedrig­ste Stufe.

Das Ganze ist natür­lich nur Fik­tion, um ein sin­nvolles und einiger­maßen real­is­tis­ches Umfeld zu schaf­fen. Aus diesem Grunde habe ich als Daten­ba­sis echte Namen ver­wen­det. Es han­delt sich um eine Tabelle aller Mit­glieder des Deutschen Bun­destages, Stand 04.05.2015 neb­st Frak­tion­szuge­hörigkeit. Jedem dieser Abge­ord­neten habe ich per Zufall­sprinzip drei Kurse „ver­passt”. Natür­lich soll den Betrof­fe­nen damit eine Fähigkeit zu- oder abge­sprochen wer­den, hier war nur der Zufall am Werk.  😆 

Grund­sät­zlich gilt, dass ich für die ersten Abbil­dun­gen und auch Pro­beläufe die ersten 12 Namen ver­wen­det habe, was den Mit­gliedern mit den Namen „A…” entspricht. Das hat rein prag­ma­tis­che Gründe und stellt keine Wer­tung dar.

Die gekürzte Liste kön­nen Sie hier herunter laden, später wer­den Sie die kom­plette Liste ein­set­zen. Falls Sie erst ein­mal unbeschw­ert weit­er­lesen möcht­en, sehen Sie hier ein Bild­schirm­fo­to der kurzen Liste:

Die kurze Liste der Inter­essen­ten

Für eine tabel­lar­ische Darstel­lung ist das in der Form sehr gut. Und wenn Sie die Liste dann noch mit­tels StrgT oder StrgL als („intel­li­gente”) Tabelle ein­richt­en, dann lässt sich schon manch­es damit anfan­gen.

Das Ziel ist es aber eine Piv­ot­Table aus diesen Dat­en zu gener­ieren. Der erste Ver­such „aus dem Hndge­lenk” ist aber recht ernüchternd, denn so ist das ja keineswegs eine sin­nvoll auswert­bare Piv­ot­Ta­belle:

Der erste Ver­such, alle Felder markiert

Zugegeben, die Dat­en lassen sich noch etwas anders anord­nen, aber irgend­wie ist es nicht das, was „man” von ein­er Piv­ot­Table erwarten kann:

Trotz ander­er Anord­nung ein unbe­friedi­gen­des Ergeb­nis

Das sieht jet­zt zwar so ähn­lich aus, wie in der Ursprungsliste, aber ich kann die Vorteile ein­er Piv­ot-Auswer­tung nicht nutzen. Die Fragestel­lung wäre beispiel­sweise: Welche Abge­ord­nete haben „Excel Ein­steiger” als erste Wahl (A) gekennze­ich­net? Oder: Wie viele Inter­essen­ten gibt es für jedes Sem­i­nar? Mit ein­er „ordentlichen” Daten­ba­sis lässt sich das recht leicht und rasch ken­ntlich machen und darstellen.

▲ nach oben …

Funktionalität geht vor

Sie erken­nen, dass die Dat­en anders aufge­baut sein müssen, wenn eine Auswer­tung erfol­gre­ich sin soll. Am Beispiel der ersten bei­den Namen der Auf­stel­lung stelle ich Ihnen hier das Muster vor, wie es zielführend ist. In der Basis-Tabelle wür­den Herr van Anken und Herr Albani dann so dargestellt wer­den:

Daten­bank-artiger Auf­bau der Liste ist das Ziel

Zugegeben, die Namen sind redun­dant. Das liegt an Excel, welch­es ja keine echte Daten­bank ist son­dern eine Tabel­lenkalku­la­tion. Aber es ist wichtig, dass in jed­er Zeile die fes­ten Werte (Name, Vor­name, Frak­tion) wieder­holt wer­den und die vari­ablen Werte (Kurs und Wer­tung des Kurs­es) in die jew­eils gle­iche Zeile einge­tra­gen wer­den. Drei gewün­schte Kurse, drei Zeilen. Das muss so sein. Dabei spielt die Anord­nung der Zeilen an sich keine Rolle,  die Sortierung übern­immt Excel für Sie. Bei der Wer­tung von Her­rn Albani ist das deut­lich zu sehen.

Da Sie jet­zt die Dat­en im kor­rek­ten For­mat vor­liegen haben, beschreibe ich noch ein­mal kurz aber den­noch Schritt für Schritt den Weg, den ich danach gegan­gen bin. Zuerst ein Klick in den Daten­bere­ich, hier also A1:E7. Anschließend im Menü Ein­fü­gen wählen und dort auf Piv­ot­Table Klick­en. Automa­tisch wird der zusam­men­hän­gen­den Daten­bere­ich markiert und Sie kön­nen im neuen Fen­ster ein­fach auf OK Klick­en, wenn Sie die Piv­ot­Ta­belle in einem neuen Blatt erstellen wollen.

Sofort wird ein neues Arbeits­blatt mit ein­er neuen PT (Piv­ot­Ta­belle) erstellt. Wenn Sie alle Felder mit einem Häkchen verse­hen, dann zeigt sich die PT so:

Der erste Ein­druck ist ernüchternd

Die Ähn­lichkeit zum ersten Ver­such mit den in der Quell­datei anders ange­ord­neten Dat­en ist  dur­chaus gegeben. Was aber auf jeden Fall auf­fällt: Die (Leer) – Angaben fehlen vol­lkom­men. Um eine „ansehn­liche” Tabelle mit den gewün­scht­en Möglichkeit­en zu gestal­ten, bin ich diesen Weg gegan­gen:

• Im Menü der Piv­ot­Table-Tools (ganz rechts) ein Klick auf Entwurf.
• Rib­bon Gesamtergeb­nisse die erste Auswahl Für Zeilen und Spal­ten deak­tiviert anklick­en.
• Bei Bericht­slay­out wählen Sie die dritte Möglichkeit, In Tabel­len­for­mat anzeigen.
• Die Spalte D noch in der Bre­ite anpassen und das sieht schon richtig gut aus:

Das brauch­bare Endergeb­nis

Dieses Grund­prinzip wer­den Sie in solcher­maßen gelagerten Fällen immer anwen­den, um zum Ziel zu gelan­gen. Das „Große ABER…”: Bei diesen 6 Zeilen war das ja schon ein Riesen-Aufwand,wie soll das denn bei den über 600 Abge­ord­neten in vertret­bar­er Zeit gehen? Oder bei noch größeren Daten­men­gen? Eine Formel-Lösung ist zwar denkbar, aber ich bin davon überzeugt, dass die Per­for­mance des Pro­gramms ganz schön darunter lei­det. Ich finde andere Lösun­gen ein­fach trans­par­enter und auch bess­er nachvol­lziehbar. Zwei davon stelle ich Ihnen hier vor.

▲ nach oben …

Tabelle per VBA (Makro) erstellen

Aus­gangslage ist hier die bere­its oben ange­sproch­ene Tabelle mit allen Abge­ord­neten. Falls noch nicht geschehen, soll­ten Sie dieser jet­zt herun­ter­laden. Es ist eine ganz nor­male Excel-Tabelle im gle­ichen For­mat, wie Sie dieses bere­its ken­nen gel­ernt haben, nur mit allen 642 Abge­ord­neten bis hin zu Frau Zypries aus der SPD-Frak­tion.

Die kurze Liste der Inter­essen­ten (Auszug)

Da dieses keine Lehrstunde für Makros sein soll, fall­en meine Erk­lärun­gen und Kom­mentare vielle­icht etwas knapp aus. Auf der anderen Seite habe ich ver­sucht, den Code zugun­sten der Les­barkeit und Nachvol­lziehbarkeit nicht allzu sehr zu straf­fen. Manch­mal ist weniger (ver­wen­den von speziellen Rou­ti­nen) eben doch mehr.  😉  Prinzip­iell geschieht hier fol­gen­des:

• Erforder­lichen­falls wird ein weit­eres Arbeits­blatt erstellt, wo die umgestal­teten Dat­en gespe­ichert wer­den.
• Begin­nend in Zeile 2 bis zur let­zten Daten­zeile wird ab Spalte D bis Spalte L über­prüft, ob in der jew­eili­gen Zelle ein A, B oder C drin ste­ht.
• Da VBA zwis­chen Groß- und Klein­schrei­bung unter­schei­det, wan­dele ich den Zellinhalt für die Prü­fung erst ein­mal in Großbuch­staben um.
• Ist die Prü­fung pos­i­tiv, dann schreibe ich in die Ziel-Tabelle in die erste leere Zeile den Namen, Vor­na­men, Frak­tion­szuge­hörigkeit und den Kurs sowie die Wer­tung.

Den Code kön­nen Sie direkt kopieren oder hier als *.zip gepackt herunter laden und in Ihre Mappe ein­fü­gen. Er gehört in jedem Fall in das Mod­ul DieseAr­beitsmappe! Wie Sie das bew­erk­stel­li­gen, kön­nen Sie auch hier im Blog nach­le­sen.

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 wer­den Sie die Dat­en aus dem ersten Tabel­len­blatt schön über­sichtlich im let­zten Tabel­len­blatt mit dem Namen Data4Pivot (geord­net) vorfind­en. Ins­ge­samt 1926 Daten­sätze plus Über­schrift­szeile. Damit ist die Grund­lage, für die Piv­ot-Auswer­tung gelegt, die Daten­ba­sis ist geschaf­fen.

Daraus die PT erstellen, wie oben beschrieben: Alle Felder markieren, dann im Menü Entwurf das Bericht­slay­out auf Tabel­len­for­mat ändern, Tei­l­ergeb­nisse und eventuell die Gesamtergeb­nisse nicht anzeigen lassen und vielle­icht noch eine Leerzeile nach jedem Ele­ment anzeigen lassen (reine Ansichtssache  🙂 ).

Wenn sich die Grund­dat­en (Tabel­len­blatt Kurs-Inter­essen EDV 4 Piv­ot) verän­dern, muss das Prozedere neu durch­laufen wer­den. Das ist auch ein­er der Gründe, dass das Ziel-Tabel­len­blatt bei jedem Makro-Aufruf erst gelöscht wird. Danach aktivieren Sie im Menü Piv­ot­table-Tools den Punkt Analysieren und ‑falls sich die Anzahl der Zeilen in den Quell­dat­en geän­dert hat Daten­quelle ändern (neu ein­le­sen)- und in jedem Fall den Punkt Aktu­al­isieren aus­führen.

Da der Zweck dieses Beitrages wed­er Grund­la­gen in Sachen Piv­ot noch Ken­nt­nisse zum The­ma VBA ver­mit­teln soll, belasse ich es hier­bei. Zumin­d­est was den Part Makros bet­rifft. Während diese Möglichkeit bei allen Excel-Ver­sio­nen (zumin­d­est ab 2000) funk­tion­iert, gibt es eine weit­ere Möglichkeit zum Lösen dieser Auf­gabe für Excel, lei­der aber erst ab Ver­sion 2010.

▲ nach oben …

Tabelle mittels Power Query erstellen

Seit Excel 2010 haben Sie die Möglichkeit, von der Microsoft-Seite Pow­er Query herunter zu laden und in Excel zu nutzen. Was sich vielle­icht auf den ersten Blick kryp­tisch oder schwierig klingt, ist ein riesiger Vorteil gegenüber den herkömm­lichen Meth­o­d­en. Nach der Instal­la­tion des Tools kön­nen Sie vol­lkom­men ohne VBA und ohne Formeln der­ar­tige Daten­samm­lun­gen so auf­bere­it­en, dass sie den Vor­gaben für Piv­ot­Ta­bles genü­gen. Und nach kurz­er Einar­beitung oder Gewöh­nung ist das eine gewiss fan­tastis­che Meth­ode!

Laden Sie das Tool bei Microsoft herunter. Beacht­en Sie bitte, dass sich ³²⁄₆₄ Bit auf die Excel-Ver­sion bezieht und nicht auf das Betrieb­ssys­tem. Im Regelfall wer­den Sie also die 32-Bit-Ver­sion ein­set­zen, das selb­st Microsoft derzeit vom Ein­satz der 64-Bit Ver­sion der Office-Pro­duk­te abrät. – Die Instal­la­tion ist ein­fach und selb­sterk­lärend. Anschließend ste­ht Ihnen im Menü-Bere­ich des Excel ein neuer Menüpunkt zur Ver­fü­gung, meist im Anschluss an  die Entwick­ler­tools: Der Menüpunkt Pow­er Query.

Öff­nen Sie bitte die Arbeitsmappe, wo die „unbe­han­del­ten” Dat­en enthal­ten sind, beispiel­sweise diese Datei. Nor­maler­weise bzw. ide­al­er­weise ist das Tabel­len­blatt mit den Kurs-Inter­essen­ten das aktuelle Arbeits­blatt. Zu Beginn Klick­en Sie auf den Menüpunkt POWER Query und erwartungs­gemäß wird sich eine neue Rib­bon­leiste, eine andere Sym­bol­leiste öff­nen. Acht­en Sie nun bitte darauf, dass eine beliebige Zelle im Daten­bere­ich markiert, also die aktive Zelle ist. Zeigen Sie nun auf die Rib­bon-Schalt­fläche Von Tabelle …

In der Grppe Excel-Dat­en „Von Tabelle” auswählen

… und Klick­en Sie anschließend darauf. Unter Umstän­den ist ein zweit­er Klick erforder­lich, damit sich die Tabelle fol­gen­der­maßen darstellt:

Der Daten­bere­ich wird automa­tisch kor­rekt aus­gewählt, solange der Bere­ich zusam­men­hän­gend ist

Dass die Tabelle Über­schriften hat, ist bere­its markiert. Und die Größe des Daten­bere­ichs stimmt auch. Also OK und einen kleinen Moment warten. Es öffnet sich ein neues Excel-Fen­ster mit dem Titel Tabelle1 – Abfrage-Edi­tor. Die Num­mer der Tabelle kann natür­lich vari­ieren. – Durch die Anzeige der kur­siv dargestell­ten null erin­nert das Ganze etwas an die früheren Ver­suche, aber es ist wirk­lich anders, ganz anders!

Als erstes kommt die Über­legung, welch­es sind die Spal­ten, die in jedem Fall angezeigt wer­den sollen. Das sind hier: Name, Vor­name, Frak­tion. Das sind die Werte, die wir in der ersten Ver­sion „per Hand” wieder­holt haben, solange ein weit­er­er Wun­sch für einen Kurs vorhan­den war. Also immer drei Zeilen mit den Per­so­n­en­dat­en. 

Ich klicke nun auf den Spal­tenkopf der ersten Spalte, welch­er hier nicht D ist son­dern wie in ein­er Daten­bank einen Namen trägt. Hier ist es Win­dows:

Nach einem Klick auf den Spal­tenkopf

Sofort nach dem Klick wir die ganze Spalte markiert. Nun scrolle ich erforder­lichen­falls so weit nach rechts, dass die let­zte Spalte der Kurse sicht­bar ist. Nun die Taste Groß (Shift, Großschrei­bung) drück­en, gedrückt hal­ten und in den let­zten Spal­tenkopf Klick­en. Sofort ist der gesamte Bere­ich der Kurse markiert.

Diese Dat­en sollen so umge­ord­net wer­den, dass pro Per­son die aus­gewählten Kurse untere­inan­der ste­hen und natür­lich die nicht gewählten keine Lück­en bilden. Das haben Sie vielle­icht bere­its weit­er oben per VBA gemacht, aber ich hat­te Ihnen ja ver­sprochen, dass es mit Pow­er Query ganz ohne Makros und ganz ohne Formeln geht. Klick­en Sie dazu mit der recht­en Maus­taste in einen beliebi­gen Spal­tenkopf der neun markierten Spal­ten und anschließend auf den hier markierten Menüpunkt Spal­ten ent­piv­otieren. Welch Wortschöp­fung … 

So wer­den die Dat­en vor­bere­it­et …

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

In Winde­seile wurde die kor­rek­te Anord­nung der Dat­en erstellt

Genau so soll es sein. Was wir vorher müh­sam von Hand erledigt haben und und bei über 600 Daten­sätzen auch nicht zuge­mutet hät­ten, wurde hier mir weni­gen Mausklicks ele­gant erledigt. Jet­zt nur noch im Menüpunkt Start ein Klick auf Schließen&Laden und automa­tisch wird eine „intel­li­gente” Excel-Tabelle neu erstellt. Die Beze­ich­nung ist typ­is­cher­weise Tabellenn, wobei das nn für eine fort­laufende Num­merierung ste­ht.

Hier­aus lässt sich nun mit Leichtigkeit eine neue Piv­ot­Ta­belle erstellen, die Daten­ba­sis ist opti­mal. Die Abfrage, die Query kann natür­lich jed­erzeit bear­beit­et wer­den und mit allen denkbaren Mit­teln der Daten­bank­abfrage bear­beit­et wer­den. Das nun erstellte Tabel­len­blatt wird in späteren Beiträ­gen gewiss die Basis für eine Piv­ot-Auswer­tung bilden. Wenn Sie etwas exper­i­men­tier­freudig sind, dann kön­nen Sie auf diesem Wege auch externe Dat­en im Excel, Daten­bank oder csv-For­mat laden und bear­beit­en