Fr. Tandakan dari Kansas yang dihantar dalam soalan Excel minggu ini:
Jadual pangsi Excel berfungsi paling baik apabila data dipecah menjadi rekod yang paling mungkin, tetapi ini bukan cara paling intuitif untuk memuatkan data ke dalam Excel. Sebagai contoh, adalah intuitif untuk memuat data seperti Gambar 1, tetapi cara terbaik untuk menganalisis data adalah seperti pada Gambar 2.
Pertama, saya akan melalui cara Excel yang kurang sempurna untuk menangani pelbagai bidang data. Kedua, saya akan memperagakan makro sederhana dan cepat untuk menukar angka 1 ke angka 2.
Sekiranya data anda seperti gambar 1, semasa PivotTable Wizard langkah 3 dari 4, adalah mungkin untuk menyeret semua 4 suku bidang ke kawasan data jadual pangsi, seperti yang ditunjukkan di sebelah kanan.
Inilah hasil yang kurang sempurna. Secara lalai, Excel mempunyai banyak medan data di halaman. Anda boleh mengklik butang "Data" berwarna abu-abu dan menyeretnya ke atas dan ke kanan untuk menjadikan tempat melintasi halaman. Walau bagaimanapun, anda kehilangan jumlah untuk setiap wilayah, dan jumlah Quarter akan selalu kelihatan tidak sesuai.
Oleh itu, Fr. Mark memukul paku di kepala ketika dia mengatakan bahawa data benar-benar perlu dalam format Gambar 2 untuk menganalisisnya dengan betul. Di bawah ini adalah makro yang akan memindahkan data dengan cepat dalam format Gambar 1 pada Helaian1 ke Lembaran2 dalam format Gambar 2. Makro ini tidak cukup umum untuk berfungsi dengan sebarang set data. Walau bagaimanapun, agak mudah untuk menyesuaikannya dengan keadaan tertentu.
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
Setelah menjalankan makro ini, data akan berada dalam format yang lebih mudah dianalisis seperti dalam gambar 2 di atas. Sekarang, apabila anda menggunakan data ini untuk jadual pangsi, anda mempunyai kawalan penuh terhadap data seperti biasa.
