P. Mark dal Kansas ha inviato la domanda su Excel di questa settimana:
Le tabelle pivot di Excel funzionano meglio quando i dati vengono suddivisi nel maggior numero di record possibile, ma questo non è sempre il modo più intuitivo per caricare i dati in Excel. Ad esempio, è intuitivo caricare dati come nella Figura 1, ma il modo migliore per analizzare i dati è come nella Figura 2.
Innanzitutto, esaminerò il modo meno che perfetto di Excel di gestire più campi di dati. In secondo luogo, mostrerò una macro semplice e veloce per convertire la figura 1 nella figura 2.
Se i tuoi dati sono come nella figura 1, durante il passaggio 3 di 4 della Creazione guidata tabella pivot, è possibile trascinare tutti i 4 campi trimestrali nell'area dati della tabella pivot, come mostrato a destra.
Ecco il risultato tutt'altro che perfetto. Per impostazione predefinita, Excel ha più campi di dati che scendono nella pagina. È possibile fare clic sul pulsante grigio "Dati" e trascinarlo verso l'alto e verso destra per far scorrere i quarti sulla pagina. Tuttavia, mancano i totali per ogni regione e i totali del trimestre sembreranno sempre fuori luogo.
Quindi, p. Mark ha colpito nel segno quando ha affermato che i dati devono essere nel formato della Figura 2 per poterli analizzare correttamente. Di seguito è una macro che sposterà rapidamente i dati nel formato della Figura 1 su Sheet1 in Sheet2 nel formato di Figura 2. Questa macro non è sufficientemente generale per funzionare con qualsiasi set di dati. Tuttavia, dovrebbe essere relativamente facile personalizzarlo in base alla tua situazione particolare.
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
Dopo aver eseguito questa macro, i dati saranno nel formato più facile da analizzare mostrato nella figura 2, sopra. Ora, quando usi questi dati per una tabella pivot, hai il pieno controllo dei dati come al solito.
