Fr. Mark dari Kansas mengirimkan pertanyaan Excel minggu ini:
Tabel pivot Excel bekerja paling baik saat data dipecah menjadi catatan sebanyak mungkin, tetapi ini tidak selalu merupakan cara paling intuitif untuk memuat data ke dalam Excel. Misalnya, memuat data secara intuitif seperti Gambar 1, tetapi cara terbaik untuk menganalisis data adalah seperti pada Gambar 2.
Pertama, saya akan membahas cara Excel yang kurang sempurna dalam menangani beberapa bidang data. Kedua, saya akan mendemonstrasikan makro sederhana dan cepat untuk mengubah gambar 1 menjadi gambar 2.
Jika data Anda seperti gambar 1, selama Panduan PivotTable langkah 3 dari 4, Anda dapat menyeret keempat bidang seperempat ke dalam area data tabel pivot, seperti yang ditunjukkan di kanan.
Inilah hasil yang kurang sempurna. Secara default, Excel memiliki beberapa bidang data di bawah halaman. Anda dapat mengklik tombol "Data" abu-abu dan menyeretnya ke atas dan kanan untuk membuat seperempat melintasi halaman. Namun, Anda kehilangan total untuk setiap wilayah, dan total Quarter akan selalu tampak tidak pada tempatnya.
Jadi, Fr. Mark tepat sasaran ketika dia mengatakan bahwa data benar-benar perlu dalam format Gambar 2 agar dapat menganalisisnya dengan benar. Di bawah ini adalah makro yang akan dengan cepat memindahkan data dalam format Gambar 1 di Sheet1 ke Sheet2 dalam format Gambar 2. Makro ini tidak cukup umum untuk bekerja dengan kumpulan data apa pun. Namun, harus relatif mudah untuk menyesuaikannya dengan situasi khusus Anda.
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 menjadi format yang lebih mudah dianalisis seperti yang ditunjukkan pada gambar 2 di atas. Sekarang, saat Anda menggunakan data ini untuk tabel pivot, Anda memiliki kontrol penuh atas data seperti biasa.
