Microsoft Excel

Herbers Excel/VBA-Archiv

Tabelle transponieren für Pivot

Betrifft: Tabelle transponieren für Pivot von: Berni
Geschrieben am: 13.04.2015 17:16:04

Hallo Excellenzen!

Ich habe eine Tabelle von den Projektanten bekommen, in denen die Ist-Werte in der Spalte C aufscheinen und in den Spalten C bis O die dazugehörogen Monats-Planwerte. Leider kann ich mit dieser Darstellung keine Pivot-Tabelle drüberstülpen, da die Planwerte in den einzelnen Spalten als eigene Summenwerte dargestellt werden. Sinnvoller wäre eine Monatsspalte und daneben die Monatsplanwerte. Allerdings müssten auch die Werte in der Spalte A(PSP-Elemente) und B (Aufträge) untereinander jeweils 12 mal aufscheinen, damit die Monatspalten damit korrespondieren können.
Ich habe mal mit Index(spalte(a1),Zeile(a1)) die Monatswerte transponiert, das ist aber noch zu wenig für die Pivotdarstellung.
Vielleicht kann mir jemand da weiterhelfen.

https://www.herber.de/bbs/user/97057.xls

  

Betrifft: AW: Tabelle transponieren für Pivot von: fcs
Geschrieben am: 14.04.2015 15:16:16

Hallo Berni,

das Umstellen der Daten per Formel ist mühselig und erfordert nach meiner Einschätzung mindestens 1 Hilfsspalte, damit die Formeln nicht zu unübersichtlich werden.

HS	PSP-Element______	Arbeitsaufträge	IST	Monat	 Plan 
0	1E.00196.010.01.010	220700000	 10.041 		  

Formeln:
A2: =WENN(ZEILE()-1<=ZEILEN(Tabelle1!$A$3:$A$17);0;
BEREICH.VERSCHIEBEN(A2;-ZEILEN(Tabelle1!$A$3:$A$17);0)+1)
B2: =INDEX(Tabelle1!A$3:A$17;ZEILE()-$A2*ZEILEN(Tabelle1!A$3:A$17)-1;1)
C2: =INDEX(Tabelle1!B$3:B$17;ZEILE()-$A2*ZEILEN(Tabelle1!B$3:B$17)-1;1)
D2: =WENN(A2=0;INDEX(Tabelle1!C$3:C$17;ZEILE()-$A2*ZEILEN(Tabelle1!C$3:C$17)-1;1);"")
E2: =WENN(A2>0;BEREICH.VERSCHIEBEN(Tabelle1!$D$2;0;A2-1);"")
F2: =WENN(A2>0;INDEX(BEREICH.VERSCHIEBEN(Tabelle1!$D$3:$D$17;0;A2-1);
ZEILE()-$A2*ZEILEN(Tabelle1!E$3:E$17)-1;1);"")
Ich persönlich würde hier die Umgruppierung per Makro vorziehen.

Gruß
Franz

'Code in einem allgemeinen Modul - z.B. der persönlichen Makroarbeitsmappe
Sub Umgruppieren()
  Dim wksQ As Worksheet, wksZ As Worksheet
  Dim Zeile_T As Long, Zeile_L As Long, Spalte_Q As Long
  Dim Zeile_Z As Long, Zeile_Z1 As Long
  Dim rngZeilenT As Range, rngMonat As Range, rngPlan As Range
  Dim SpaMo As Long, SpaPlan As Long
  'Ursprungstabelle setzen
  Set wksQ = ActiveSheet
  'Zeile mit Spaltentiteln/Monaten in Quelltabelle
  Zeile_T = 2
  'Neues Blatt für umgruppierte Tabelle anlegen
  ActiveWorkbook.Worksheets.Add after:=wksQ
  Set wksZ = ActiveSheet
  'Zeile mit Spaltentiteln in umgruppierter Tabelle
  Zeile_Z1 = 1
  SpaMo = 4   'Spalte D
  SpaPlan = 5 'Spalte E
  'Tabelle unterhalb Titelzeile einfrieren
  wksZ.Cells(Zeile_Z1 + 1, 1).Select
  ActiveWindow.FreezePanes = True
  
  Zeile_Z = Zeile_Z1
  With wksQ
    'letzte Zeile mit Daten in Spalte A in Quelltabelle
    Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
    'Spaltentitel in Spalten A:C nach Ziel kopieren
    .Range(.Cells(Zeile_T, 1), .Cells(Zeile_T, 3)).Copy
    With wksZ.Cells(Zeile_Z, 1)
      .PasteSpecial Paste:=xlPasteColumnWidths
      .PasteSpecial Paste:=xlPasteAll
    End With
    'Spaltentitel für neue Spalten
    wksZ.Cells(Zeile_Z, SpaMo) = "Monat"
    wksZ.Cells(Zeile_Z, SpaPlan) = "Plan"
    Zeile_Z = Zeile_Z + 1
    'Datenbereich mit Zeilentiteln, die für jeden Monat kopiert werden sollen
    Set rngZeilenT = .Range(.Cells(Zeile_T + 1, 1), .Cells(Zeile_L, 2))
    
    'Daten in Spalten A:C nach Ziel kopieren (IST-Daten)
    '.Range(.Cells(Zeile_T + 1, 1), .Cells(Zeile_L, 3)).Copy wksZ.Cells(Zeile_Z, 1)
    'Einfügezeile für Daten des 1. Monats in Zieltabelle
    'Zeile_Z = Zeile_Z + rngZeilenT.Rows.Count
    
    'Monate in Quelle abarbeiten
    For Spalte_Q = 4 To .Cells(Zeile_T, .Columns.Count).End(xlToLeft).Column
      'Zelle mit Monat
      Set rngMonat = .Cells(Zeile_T, Spalte_Q)
      'Zellbereich mit Planwerten für Monat
      Set rngPlan = .Range(.Cells(Zeile_T + 1, Spalte_Q), .Cells(Zeile_L, Spalte_Q))
      'Monats-Plan-Daten kopieren
      With wksZ
        rngZeilenT.Copy .Cells(Zeile_Z, 1)
        rngMonat.Copy .Range(.Cells(Zeile_Z, SpaMo), _
                             .Cells(Zeile_Z + rngZeilenT.Rows.Count - 1, SpaMo))
        rngPlan.Copy .Cells(Zeile_Z, SpaPlan)
      End With
      'Einfügezeile für Folgemonat
      Zeile_Z = Zeile_Z + rngZeilenT.Rows.Count
    Next
    'letzte Datenzeile in Zieltabelle
    Zeile_Z = Zeile_Z - 1
  End With
  'Zieltabelle nachformatieren
  With wksZ
    With .Range(.Columns(SpaMo), .Columns(SpaPlan))
      .AutoFit
    End With
    With .Range(.Cells(Zeile_Z1, 1), .Cells(Zeile_Z, SpaPlan))
      .Interior.ColorIndex = xlColorIndexNone
      .Borders.LineStyle = xlLineStyleNone
    End With
  End With
End Sub



  

Betrifft: AW: Tabelle transponieren für Pivot von: Berni
Geschrieben am: 15.04.2015 10:48:21

Danke Franz!

Ein bisschen muss ich noch den Code anpassen, da noch nicht die Planmonate aufscheinen, aber das ist jetzt die Feinabstimmung. Falls ich stolpere, darf ich mich nochmals melden?

1E.00196.010.01.010 220700000 GKI Stauraum Schweiz Monat Plan
1E.00196.010.01.020 220700001 10.041 -
1E.00196.010.02 10.041 -
1E.00196.010.02.010 220700002 10.041 71.254
1E.00196.010.02.020 220700003 10.041 71.254
10.041 -
1E.00196.010.02.030 220700004 10.041 71.254
1E.00196.010.02.040 220700005 10.041 71.254
1E.00196.010.02.050 220700006 10.041 71.254
1E.00196.010.03 10.041 -
1E.00196.010.03.010 220700008 10.041 19.661.128
1E.00196.010.04 10.041 -
1E.00196.010.04.010 220700009 10.041 1.503.747
1E.00196.010.04.020 220700010 10.041 317.434
1E.00196.010.04.030 220700011 10.041 630.221
1E.00196.010.04.040 220700012 10.041 5.791.864
1E.00196.010.04.050 220700013 10.041 55.600
1E.00196.010.04.060 220700014 10.041 5.681


Danke vorerst

Berni


  

Betrifft: AW: Tabelle transponieren für Pivot von: fcs
Geschrieben am: 15.04.2015 11:39:03

Hallo Berni,

das Makro zum Umgruppieren ist bezüglich Zeile/Spalten genau auf deine Beispieldatei abgestimmt.

Die Zeile mit den Monats/Datumswerten wird in folgender Zeile vorgegeben:

  'Zeile mit Spaltentiteln/Monaten in Quelltabelle
  Zeile_T = 2

Die Start-Zeile des Zellbereichs mit den Spaltentiteln und der für jeden Monat zu kopierenden Zellebereiche ist dann jeweils relativ zur Zeile_T festgelegt.
    'Spaltentitel in Spalten A:C nach Ziel kopieren
    .Range(.Cells(Zeile_T, 1), .Cells(Zeile_T, 3)).Copy
    'Datenbereich mit Zeilentiteln, die für jeden Monat kopiert werden sollen
    Set rngZeilenT = .Range(.Cells(Zeile_T + 1, 1), .Cells(Zeile_L, 2))
      'Zellbereich mit Planwerten für Monat
      Set rngPlan = .Range(.Cells(Zeile_T + 1, Spalte_Q), .Cells(Zeile_L, Spalte_Q))

Hier muss du dann ggf. in "Zeile_T + 1" den Wert "+ 1" anpassen, damit die richtigen Zeilen kopiert werden.

In
    'Monate in Quelle abarbeiten
    For Spalte_Q = 4 To .Cells(Zeile_T, .Columns.Count).End(xlToLeft).Column

musst du ggf. dir 4 anpassen, wenn der 1. Monat nicht in Spalte D ( 4 ) steht.

Gruß
Franz


 

Beiträge aus den Excel-Beispielen zum Thema "Tabelle transponieren für Pivot"