Konten
Rumusan masalah
Tabel pivot adalah salah satu alat paling menakjubkan di Excel. Namun sejauh ini, sayangnya, tidak ada versi Excel yang dapat melakukan hal yang sederhana dan perlu dengan cepat seperti membuat ringkasan untuk beberapa rentang data awal yang terletak, misalnya, di lembar yang berbeda atau di tabel yang berbeda:
Sebelum kita mulai, mari kita perjelas beberapa poin. Secara apriori, saya percaya bahwa kondisi berikut terpenuhi dalam data kami:
- Tabel dapat memiliki sejumlah baris dengan data apa pun, tetapi mereka harus memiliki header yang sama.
- Seharusnya tidak ada data tambahan pada lembar dengan tabel sumber. Satu lembar - satu meja. Untuk mengontrol, saya menyarankan Anda untuk menggunakan pintasan keyboard Ctrl+Akhir, yang memindahkan Anda ke sel yang terakhir digunakan di lembar kerja. Idealnya, ini harus menjadi sel terakhir dalam tabel data. Jika ketika Anda mengklik Ctrl+Akhir sel kosong apa pun di sebelah kanan atau di bawah tabel disorot – hapus kolom kosong ini di sebelah kanan atau baris di bawah tabel setelah tabel dan simpan file.
Metode 1: Buat tabel untuk pivot menggunakan Power Query
Mulai dari versi 2010 untuk Excel, ada add-in Power Query gratis yang dapat mengumpulkan dan mengubah data apa pun, lalu memberikannya sebagai sumber untuk membuat tabel pivot. Memecahkan masalah kami dengan bantuan add-in ini sama sekali tidak sulit.
Pertama, mari buat file kosong baru di Excel – perakitan akan dilakukan di dalamnya dan kemudian tabel pivot akan dibuat di dalamnya.
Kemudian pada tab Data (jika Anda memiliki Excel 2016 atau lebih baru) atau pada tab Kueri Daya (jika Anda memiliki Excel 2010-2013) pilih perintah Buat Kueri – Dari File – Excel (Dapatkan Data — Dari file — Excel) dan tentukan file sumber dengan tabel yang akan dikumpulkan:
Di jendela yang muncul, pilih lembar apa saja (tidak masalah yang mana) dan tekan tombol di bawah Perubahan (Ubah):
Jendela Editor Power Query Query akan terbuka di atas Excel. Di sisi kanan jendela pada panel Parameter Permintaan hapus semua langkah yang dibuat secara otomatis kecuali yang pertama – sumber (Sumber):
Sekarang kita melihat daftar umum semua lembar. Jika selain lembar data ada beberapa lembar samping lain dalam file, maka pada langkah ini tugas kita adalah memilih hanya lembar yang informasinya perlu dimuat, tidak termasuk semua yang lain menggunakan filter di header tabel:
Hapus semua kolom kecuali kolom Datadengan mengklik kanan judul kolom dan memilih Hapus kolom lain (Menghapus kolom lainnya):
Anda kemudian dapat memperluas konten tabel yang dikumpulkan dengan mengklik panah ganda di bagian atas kolom (kotak centang Gunakan nama kolom asli sebagai awalan Anda dapat mematikannya):
Jika Anda melakukan semuanya dengan benar, maka pada titik ini Anda akan melihat isi dari semua tabel yang dikumpulkan satu di bawah yang lain:
Tetap menaikkan baris pertama ke header tabel dengan tombol Gunakan baris pertama sebagai header (Gunakan baris pertama sebagai header) tab Beranda (Rumah) dan hapus header tabel duplikat dari data menggunakan filter:
Simpan semua yang dilakukan dengan perintah Tutup dan muat – Tutup dan muat di… (Tutup & Muat — Tutup & Muat ke…) tab Beranda (Rumah), dan di jendela yang terbuka, pilih opsi Hanya koneksi (Koneksi Saja):
Semuanya. Tetap hanya untuk membangun ringkasan. Untuk melakukan ini, buka tab Sisipkan – PivotTable (Sisipkan — Tabel Pivot), pilih opsi Gunakan sumber data eksternal (Gunakan sumber data eksternal)dan kemudian dengan mengklik tombol Pilih sambungan, permintaan kami. Pembuatan dan konfigurasi pivot lebih lanjut terjadi dengan cara yang sepenuhnya standar dengan menyeret bidang yang kita butuhkan ke dalam baris, kolom, dan area nilai:
Jika data sumber berubah di masa mendatang atau beberapa lembar penyimpanan lagi ditambahkan, maka itu akan cukup untuk memperbarui kueri dan ringkasan kami menggunakan perintah Segarkan semua tab Data (Data — Segarkan Semua).
Metode 2. Kami menyatukan tabel dengan perintah SQL UNION dalam makro
Solusi lain untuk masalah kami diwakili oleh makro ini, yang membuat kumpulan data (cache) untuk tabel pivot menggunakan perintah KESATUAN bahasa kueri SQL. Perintah ini menggabungkan tabel dari semua yang ditentukan dalam array Nama Lembar lembar buku menjadi satu tabel data. Artinya, alih-alih menyalin dan menempel secara fisik dari lembar yang berbeda menjadi satu, kami melakukan hal yang sama di RAM komputer. Kemudian makro menambahkan lembar baru dengan nama yang diberikan (variabel NamaLembar Hasil) dan membuat ringkasan lengkap (!) berdasarkan cache yang dikumpulkan.
Untuk menggunakan makro, gunakan tombol Visual Basic pada tab pembangun (Pengembang) atau pintasan keyboard lain+F11. Kemudian kami memasukkan modul kosong baru melalui menu Sisipkan – Modul dan salin kode berikut di sana:
Sub New_Multi_Table_Pivot() Dim i Selama Dim arSQL() Sebagai String Dim objPivotCache Sebagai PivotCache Dim objRS Sebagai Objek Dim ResultSheetName Sebagai String Dim SheetsNames As Variant 'sheet name dimana pivot yang dihasilkan akan ditampilkan ResultSheetName = "Pivot" 'an array of sheet nama dengan tabel sumber SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'kami membentuk cache untuk tabel dari sheet dari SheetsNames Dengan ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Untuk i = LBound (SheetsNames) Ke UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Selanjutnya i Set objRS = CreateObject("ADODB.Recordset") objRS .Buka Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Akhiri Dengan 'buat kembali lembar untuk menampilkan tabel pivot yang dihasilkan Pada Kesalahan Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'tampilkan ringkasan cache yang dihasilkan pada lembar ini Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Tidak Ada Dengan wsPivot objPivotCache.CreatePivotTable TableDestination.=RangesPivot3) objPivotCache = Nothing Range("A3").Pilih End With End Sub Makro yang sudah selesai kemudian dapat dijalankan dengan pintasan keyboard lain+F8 atau tombol Makro pada tab pembangun (Pengembang — Makro).
Kontra dari pendekatan ini:
- Data tidak diperbarui karena cache tidak memiliki koneksi ke tabel sumber. Jika Anda mengubah data sumber, Anda harus menjalankan makro lagi dan membuat ringkasan lagi.
- Saat mengubah jumlah lembar, perlu untuk mengedit kode makro (array Nama Lembar).
Tetapi pada akhirnya kami mendapatkan tabel pivot lengkap yang nyata, dibangun di atas beberapa rentang dari lembar yang berbeda:
Voila!
Catatan teknis: jika Anda mendapatkan kesalahan seperti "Penyedia tidak terdaftar" saat menjalankan makro, kemungkinan besar Anda memiliki versi 64-bit Excel atau versi Office yang tidak lengkap diinstal (tidak ada Access). Untuk memperbaiki situasi, ganti fragmen dalam kode makro:
Penyedia=Microsoft.Jet.OLEDB.4.0;
ke:
Penyedia=Microsoft.ACE.OLEDB.12.0;
Dan unduh dan instal mesin pemrosesan data gratis dari Access dari situs web Microsoft – Microsoft Access Database Engine 2010 Redistributable
Metode 3: Konsolidasi PivotTable Wizard dari Versi Excel Lama
Metode ini agak ketinggalan jaman, tetapi masih layak disebut. Secara formal, di semua versi hingga dan termasuk tahun 2003, ada opsi di PivotTable Wizard untuk "membangun poros untuk beberapa rentang konsolidasi". Namun, laporan yang dibuat dengan cara ini, sayangnya, hanya akan menjadi kemiripan yang menyedihkan dari ringkasan lengkap yang nyata dan tidak mendukung banyak "chip" dari tabel pivot konvensional:
Dalam pivot seperti itu, tidak ada judul kolom dalam daftar bidang, tidak ada pengaturan struktur yang fleksibel, kumpulan fungsi yang digunakan terbatas, dan, secara umum, semua ini tidak terlalu mirip dengan tabel pivot. Mungkin itu sebabnya, mulai tahun 2007, Microsoft menghapus fungsi ini dari dialog standar saat membuat laporan tabel pivot. Sekarang fitur ini hanya tersedia melalui tombol khusus Penyihir PivotTable(Penyihir Tabel Pivot), yang, jika diinginkan, dapat ditambahkan ke Bilah Alat Akses Cepat melalui File – Opsi – Sesuaikan Bilah Alat Akses Cepat – Semua Perintah (File — Opsi — Kustomisasi Bilah Alat Akses Cepat — Semua Perintah):
Setelah mengklik tombol yang ditambahkan, Anda harus memilih opsi yang sesuai pada langkah pertama wizard:
Dan kemudian di jendela berikutnya, pilih setiap rentang secara bergantian dan tambahkan ke daftar umum:
Tapi, sekali lagi, ini bukan ringkasan lengkap, jadi jangan berharap terlalu banyak darinya. Saya dapat merekomendasikan opsi ini hanya dalam kasus yang sangat sederhana.
- Membuat Laporan dengan PivotTable
- Siapkan perhitungan di PivotTable
- Apa itu makro, bagaimana menggunakannya, di mana menyalin kode VBA, dll.
- Pengumpulan data dari beberapa lembar menjadi satu (PLEX add-on)