Vielfach liegen auszuw­er­tende Dat­en in der Form vor, dass wir User einen raschen Überblick haben. Typ­is­cher­weise ist das eine XY-Matrix mit den entsprechen­den Beze­ich­n­ern links und ober­halb der eigentlichen Dat­en. Als Beispiel soll ein Arbeits­blatt dienen, welch­es ich schon in sehr vie­len Excel-Schu­lun­gen einge­set­zt habe, die Bäck­erei Klein­brot:

Die orig­i­nale Kreuzta­belle

Die Arbeitsmappe liegt hier zum Down­load bere­it. Sie sehen, in Zeile 4 sind die Pro­duk­t­beze­ich­nun­gen und in Spalte A des Daten­bere­ichs die Monate. Es sind hier zwar schon ver­schiedene Auswer­tun­gen gemacht wor­den, aber in dieser Form ist das nun wirk­lich keine Grund­lage für eine Piv­ot­Table. Da müssen die Dat­en in ein­er anderen Ord­nung vor­liegen. Jew­eils drei rel­e­vante Dat­en in ein­er Zeile, beispiel­sweise so:

Sie erken­nen, dass die Sum­men­berech­nun­gen sowie die anderen Auswer­tun­gen nicht mit eingeschlossen sind; das kann eine Piv­ot­Ta­belle viel bess­er!  😉 Das Ziel ist es also, in einem neuen Arbeits­blatt eine Tabelle zu erstellen, welche die eben genan­nten Kri­te­rien erfüllt.

Es gibt mehrere Wege zum Ziel, den­noch sind einige grundle­gende Hin­weise zu den gesamten Dat­en wichtig:

• Die bei­den Über­schrift-Zeilen sind nicht über die Schalt­fläche zen­tri­ert. Diese Form der Zen­trierung macht mehr Ärg­er als gut ist. Wie ich das gemacht habe, kön­nen Sie beispiel­sweise hier im Blog nach­le­sen.
• Die Leerzeile (Zeile 18) ist nur okay, weil die Dat­en darunter nicht zu den auszuw­er­tenden Dat­en gehören. Leerzeilen haben in ein­er (1) ordentlichen Tabelle/Liste nichts zu suchen. Punkt!
• Falls auch Sie die auszuwen­den­den Dat­en mehrfach ver­wen­den wollen, dann lohnt es sich, dem Bere­ich A4:G16 einen Bere­ich­sna­men zu geben. Da dieser möglichst aus­sagekräftig sein sollte, ver­wende ich den Namen Dat­en und ver­wende ihn auch in den Beispie­len.
• Egal, welche der hier vorgestell­ten Vari­anten Sie wählen: Die Zelle A4 sollte nicht leer bleiben. Dort gehört eine Über­schrift hinein. Ich wäh­le in diesem Fall Monat.

Dieser Beitrag ste­ht im Kapi­tel „Es führen viele Wege nach Rom”, darum werde ich Ihnen hier auch die wichtig­sten Möglichkeit­en vorstellen. Sie kön­nen dann anhand Ihrer Fähigkeit­en und Gegeben­heit­en entschei­den. Und für alle vorgestell­ten Möglichkeit­en gilt: Auss­chließlich die Dat­en im Blatt Tabelle1 wer­den zur Auswer­tung ver­wen­det, die umgestell­ten, anders ange­ord­neten Dat­en befind­en sich in je einem neu durch Sie erstell­ten Blatt.

▲ nach oben …

Umstellung per Formel

Ein ein­fach­es Transponieren geht (natür­lich) nicht, das wäre ja zu ein­fach. 😛 Im Prinzip sind es drei unter­schiedliche Formeln, die zum Ziel führen. Zumin­d­est in meinem Lösungsvorschlag, es gibt gewiss noch weit­ere Wege. Diese Formeln finde ich (noch) einiger­maßen nachvol­lziehbar …

• Leg­en Sie erst ein­mal ein neues Arbeits­blatt (Reg­is­ter) an, geben Sie ihm beispiel­sweise den Namen Formel.
• Tra­gen Sie in A1:C1 diese Über­schriften ein: Monat | Pro­dukt | Umsatz
• Schreiben Sie in A2 diese Formel: =INDEX(Daten; AUFRUNDEN((ZEILE()-1)/6; 0)+1; 1)
• In B2 kommt diese Formel: =INDEX(Daten; 1; REST(ZEILE(A2)-2; 6)+2)
• Und C2 wird mit dieser Formel verse­hen: =INDEX(Daten; AUFRUNDEN((ZEILE()-1)/6; 0)+1; REST(ZEILE(A2)-2; 6)+2)
• Acht­en Sie darauf, dass eine beliebige Zelle im Bere­ich A1:C2 markiert ist und StrgL oder StrgT oder gehen Sie über das Menü, um die Dat­en Als Tabelle zu for­matieren.

Jet­zt ist etwas Rechenar­beit ange­sagt. Es ist nicht viel und auch nicht schwierig, also wer­den Sie gewiss auch ohne Excels Hil­fe auskom­men.  😉 Es sind 12 Monate zu je 6 Pro­duk­ten. Dazu kommt noch die Über­schrift. Ziehen Sie die Tabelle bis zu der eben berech­neten Zeile nach unten. Und Sie sehen, dass das Wun­schergeb­nis erre­icht ist. In der let­zten Zeile muss übri­gens Dezem­ber | Son­stiges | 804,75 ste­hen.

▲ nach oben …

Umstellung per VBA (Makro)

Es gibt ver­schiedene Gründe, warum Sie statt der Formeln lieber mit einem Makro arbeit­en. Vielle­icht haben Sie (wie auch ich) eine Abnei­gung gegen Formeln, die länger sind als 50 Zeichen. Die Formel in Spalte C hat übri­gens 67 Zeichen, ohne das führende Gle­ich­heit­sze­ichen. 😉 Oder aber Sie wollen dieses Pro­cedere auf ver­schiedene Dat­en anwen­den und nicht jedes Mal die Formeln neu ein­tra­gen. Vielle­icht find­en Sie auch, dass ein VBA-Code ein­fach flex­i­bler ist, weil Änderun­gen rasch­er und leichter durchzuführen sind. – Na dann wird Ihnen dieser Beispiel­code gewiss helfen. In einem neuen Tabel­len­blatt, dessen Name beispiel­sweise VBA oder Makro ist, tra­gen Sie in das Mod­ul DieseAr­beitsmappe fol­gen­den Code ein:

Option Explicit

Sub DataBlockTranspose()
   Dim wksSrc As WorkSheet, wksDst As WorkSheet
   Dim rngProd As Range, rngData As Range, rngUms As Range
   Dim i As Integer, k As Integer, m As Integer, lRow As Long
   Dim aUms
   
   Set wksSrc = Sheets("Tabelle1")  'eventuell anpassen
   Set wksDst = Sheets("Makro")     'oder VBA oder anpassen
   Set rngData = wksSrc.Range("Daten")
   Set rngProd = wksSrc.Range("B4:G4")
   
   Application.ScreenUpdating = False  'Kein Bildschirmflackern + schneller
   With wksDst
      'Erst einmal alles suaber machen
      .Cells.ClearContents
      'Überschriften schreiben
      .Cells(1, 1) = "Monat"
      .Cells(1, 2) = "Produkt"
      .Cells(1, 3) = "Umsatz"
      'Monatsnamen eintragen
      For i = 0 To 11
         For k = 1 To 6
            Cells(i * 6 + k + 1, 1) = Format(CDate("1." & i + 1), "MMMM")
         Next k
      Next i
      'Produkte  in Spalte B
      For i = 2 To 12 * 6 + 1 Step 6
         .Range(.Cells(i, 2), Cells(i + 5, 2)) = WorksheetFunction.Transpose(rngProd)
      Next i
      'Umsätze in Spalte c
      For i = 2 To 13
         lRow = .Cells(Rows.Count, 3).End(xlUp).Row
         aUms = WorksheetFunction.Transpose(Range(rngData(i, 2), rngData(i, 7)))
         .Range(.Cells(lRow + 1, 3), .Cells(lRow + 7, 3)).Resize(UBound(aUms), 1) = aUms
      Next i
      lRow = .Cells(Rows.Count, 1).End(xlUp).Row
      '... und eine Intelligente Tabelle daraus machen
      .ListObjects.Add(xlSrcRange, Range("A1:C" & lRow), , xlYes).Name = "tbl_Daten_2"
   End With
End Sub

Ich habe in dem Code ganz bewusst ver­schiedene Stile ver­wen­det. So haben Sie die Möglichkeit, einen für sich sel­ber passenden Code-Stil her­auszuar­beit­en und dann ins­ge­samt zu ver­wen­den. – Als Namen der Liste/Intelligenten Tabelle habe ich tbl_Daten_2 genom­men, damit Sie die Daten­quellen bei ein­er Piv­ot-Auswer­tung klar definieren kön­nen (auch wenn es eigentlich egal ist, denn die Dat­en sind ja iden­tisch).

▲ nach oben …

Ab Excel ²⁰¹⁰⁄₂₀₁₃: Power Query

Wenn Sie eine neueres Excel haben, dann kön­nen Sie auch Pow­er Query ein­set­zen, sofern es sich um ein Win­dows-Excel han­delt. Hier im Blog bekom­men Sie mehr Infor­ma­tio­nen zu dem Add-In. Sie müssen in jedem Fall in den 2010^er/2013^er-Ver­sion das Add-In instal­liert haben, in der 2016^er-Ver­sion ist die Funk­tion­al­ität bere­its inte­gri­ert. Die Anweisun­gen hier beziehen sich auf Excel 2013, ab der 2016^er Ver­sion ist nach dem Aufruf über das Menü Dat­en vieles gle­ich. – Ach ja, die eine oder andere Anweisung wird hier wieder­holt; ich kann nicht sich­er sein, dass Sie den oberen Teil auch so durchgear­beit­et haben oder vielle­icht für diese Auf­gabe wiederum die leere Mappe geladen haben.

Wählen Sie zuerst das Menü Pow­er Query, um das dazu gehörige Menüband angezeigt zu bekom­men. Falls Sie Excel 2016 ver­wen­den, mehr dazu hier. Sie brauchen kein neues Arbeits­blatt anzule­gen, das erledigt Pow­er Query für Sie. Wech­seln Sie erforder­lichen­falls zu Tabelle1 und acht­en Sie darauf, dass eine beliebige Zelle der Dat­en markiert ist. Hier nun in Stich­worten das weit­ere Vorge­hen:

• Markieren Sie A4:G16 und for­matieren Sie den Bere­ich als (Intel­li­gente) Tabelle (geht auch mit StrgT oder StrgL).
• Ändern Sie in A4 die Über­schrift auf Monat.
• Acht­en Sie darauf, dass eine beliebige Zelle (und zwar nur 1) der auszuw­er­tenden Dat­en (Liste) oder der gesamte auszuw­er­tende Bere­ich markiert ist.
• Gruppe Excel-Dat­en, Sym­bol Von Tabelle. (In den Excel-Ver­sio­nen teil­weise unter­schiedliche Beze­ich­nun­gen, ist aber auffind­bar. 😎 )
• Im Abfrage-Edi­tor die Markierung der ersten Spalte belassen, erforder­lichen­falls nur diese Spalte durch Klick in die Über­schrift markieren.
• Menüpunkt Trans­formieren auswählen.
• In der Gruppe Beliebige Spalte auf den Drop­Down-Pfeil  beim Punkt Spal­ten ent­piv­otieren Klick­en.
• Andere Spal­ten ent­piv­otieren auswählen.
• Recht­sklick in die Über­schrift Attrib­ut und im Kon­textmenü Umbe­nen­nen… dann Pro­dukt schreiben (die Über­schrift-Markierung wird automa­tisch erset­zt).
• Klick in die Über­schrift Wert, F2 und die Beze­ich­nung auf Umsatz ändern.
• Menüpunkt Start, dann in der Gruppe Schließen auf Schließen und laden klick­en oder via Menü | Datei Schließen und laden.

Automa­tisch wird ein neues Tabel­len­blatt erstellt und die Dat­en sind in exakt der gewün­scht­en Form als Liste einge­tra­gen wor­den. Zur besseren Iden­ti­fika­tion benenne auch die Tabelle tbl_PowerQuery und das Arbeits­blatt beispiel­sweise Pow­er Query. In dieser Muster-Mappe (mit dem Ergeb­nis des Pow­er Query) habe ich die Tabelle bere­its im Abfrage-Edi­tor umbe­nan­nt.