Fr. Mark aus Kansas hat die Excel-Frage dieser Woche gesendet:
Excel-Pivot-Tabellen funktionieren am besten, wenn die Daten in möglichst viele Datensätze unterteilt sind. Dies ist jedoch nicht immer die intuitivste Methode, um die Daten in Excel zu laden. Zum Beispiel ist es intuitiv, Daten wie in Abbildung 1 zu laden, aber die beste Methode zur Analyse der Daten ist wie in Abbildung 2.
Zunächst werde ich die nicht perfekte Methode von Excel für den Umgang mit mehreren Datenfeldern durchgehen. Zweitens werde ich ein einfaches und schnelles Makro vorführen, um Abbildung 1 in Abbildung 2 zu konvertieren.
Wenn Ihre Daten wie in Abbildung 1 dargestellt sind, können Sie im Schritt 3 von 4 des PivotTable-Assistenten alle 4 Viertelfelder in den Datenbereich der Pivot-Tabelle ziehen (siehe Abbildung rechts).
Hier ist das nicht perfekte Ergebnis. Standardmäßig werden in Excel mehrere Datenfelder auf der Seite angezeigt. Sie können auf die graue Schaltfläche "Daten" klicken und sie nach oben und rechts ziehen, damit die Viertel über die Seite verlaufen. Es fehlen jedoch Summen für jede Region, und die Quartalsummen scheinen immer fehl am Platz zu sein.
Also, Fr. Mark traf den Nagel auf den Kopf, als er sagte, dass die Daten wirklich das Format von Abbildung 2 haben müssen, um sie richtig zu analysieren. Unten finden Sie ein Makro, mit dem Daten im Format von Abbildung 1 auf Blatt1 schnell auf Blatt2 im Format von Abbildung 2 verschoben werden. Dieses Makro ist nicht allgemein genug, um mit einem Datensatz zu arbeiten. Es sollte jedoch relativ einfach sein, es an Ihre spezielle Situation anzupassen.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
Nach dem Ausführen dieses Makros haben die Daten das in Abbildung 2 oben gezeigte, einfacher zu analysierende Format. Wenn Sie diese Daten jetzt für eine Pivot-Tabelle verwenden, haben Sie wie gewohnt die volle Kontrolle über die Daten.
