Pr. Mark din Kansas a trimis întrebarea Excel din această săptămână:
Tabelele pivot Excel funcționează cel mai bine atunci când datele sunt împărțite în cele mai multe înregistrări posibile, dar acesta nu este întotdeauna cel mai intuitiv mod de a încărca datele în Excel. De exemplu, este intuitiv să încărcați date precum Figura 1, dar cel mai bun mod de a analiza datele este ca în Figura 2.
În primul rând, voi rula prin modul mai puțin perfect de Excel de a trata mai multe câmpuri de date. În al doilea rând, voi demonstra o macro simplă și rapidă pentru a converti figura 1 în figura 2.
Dacă datele dvs. sunt ca în figura 1, în timpul pasului 3 din 4 al PivotTable Wizard, este posibil să trageți toate cele 4 sferturi în zona de date a tabelului pivot, așa cum se arată în dreapta.
Iată rezultatul mai puțin decât perfect. În mod implicit, Excel are mai multe câmpuri de date în josul paginii. Puteți face clic pe butonul gri „Date” și trageți-l în sus și în dreapta pentru a face ca trimestrele să treacă peste pagină. Cu toate acestea, vă lipsesc totalurile pentru fiecare regiune, iar totalurile trimestriale vor părea întotdeauna deplasate.
Deci, pr. Mark a lovit unghia de cap atunci când a spus că datele trebuie într-adevăr să fie în formatul din Figura 2 pentru a le analiza corect. Mai jos este o macrocomandă care va muta rapid datele în formatul din Figura 1 din Foaia1 în Foaia2 în formatul din Figura 2. Această macrocomandă nu este suficient de generală pentru a funcționa cu orice set de date. Cu toate acestea, ar trebui să fie relativ ușor să îl personalizați în funcție de situația dvs. particulară.
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
După rularea acestei macrocomenzi, datele vor fi în formatul mai ușor de analizat prezentat în figura 2 de mai sus. Acum, când utilizați aceste date pentru un tabel pivot, aveți control deplin asupra datelor ca în mod normal.
