Bagaimana cara membuat tabel pivot dengan banyak lembar?

Tabel pivot adalah salah satu cara terbaik untuk meringkas data.

Anda dapat membuat laporan ringkasan dalam hitungan detik dari ribuan baris data. Namun saat Anda bekerja dengan data di dunia nyata, tidak semuanya sempurna.

Dan hari ini saya ingin berbagi dengan Anda masalah umum yang kita semua hadapi.

Sebelum kita mulai, saya ingin mengucapkan terima kasih kepada Raman Girdhar atas pertanyaannya. Dia sudah mengikutiku cukup lama.

Terkadang kami menerima atau menangkap data kami di buku kerja yang berbeda dan dalam hal ini, membuat tabel pivot memerlukan upaya ekstra untuk menggabungkan beberapa buku kerja ini menjadi satu.

Baru setelah itu kita bisa membuat tabel pivot.

Jadi, dalam artikel ini, saya ingin menunjukkan kepada Anda proses tiga langkah untuk membuat tabel pivot menggunakan data dari beberapa buku kerja.

Langkah-langkah Membuat PivotTable Menggunakan Data dari Beberapa Buku Kerja

Penting: Untuk ini kita perlu menggunakan power query, jadi pastikan Anda memiliki power query di versi Excel Anda. Untuk Excel 2016, terletak di tab Data, dan untuk versi lain (2010 dan 2013), Anda perlu menginstal add-on.

Di sini saya memiliki empat buku kerja berbeda dengan data penjualan untuk area berbeda. Anda dapat mengunduh file-file ini dari sini untuk mengikuti .

Dan pastikan Anda memiliki semua file ini dalam satu folder.

Langkah 1 – Gabungkan File Menggunakan Power Query

Pertama, kita perlu menggabungkan semua file menjadi satu file dengan PowerQuery.

Buka tab Data -> Dapatkan Transformasi -> Kueri Baru -> Dari File -> Dari Folder.
Sekarang, dari jendela pemilihan folder, klik “Jelajahi” dan pilih folder tempat Anda menyimpan semua file.
Klik Oke.
Dari sana Anda akan mendapatkan jendela “Gabungkan File”.
Dan dari jendela ini, pilih lembar kerja dengan data Anda dari semua buku kerja. (Tips: Pastikan Anda memiliki nama lembar kerja yang sama di semua buku kerja).
Setelah Anda mengklik OK, semua data di buku kerja akan diedit di Editor Power Query.

Langkah 2 – Siapkan data untuk tabel pivot

Sekarang kita perlu membuat perubahan kecil pada data kita untuk mempersiapkannya untuk tabel pivot.

Jika dilihat dari datanya, disini kita memiliki kolom tambahan dengan nama file sumber.
Klik kanan pada kolom ini dan pilih Pisahkan kolom -> Berdasarkan pembatas.
Di jendela pembatas, pilih kustom, tambahkan “–” sebagai pembatas dan pilih “Pembatas paling kiri”.
Klik Oke.
Setelah itu, hapus kolom kedua.
Sekarang ganti nama kolom pertama.
Satu hal lagi, klik kanan lagi pada kolom pertama dan pergi ke Transform -> Capitalize every word.
Dan sekarang data Anda sudah siap. Jadi, klik tutup dan muat.

Langkah 3 – Masukkan PivotTable

Pada titik ini, kami memiliki lembar kerja baru di buku kerja kami dengan data gabungan dari keempat file.

Sekarang saatnya membuat tabel pivot darinya.

Pilih tabel dan buka Tab Sisipkan dan klik tombol PivotTable.
Di sini Anda akan mendapatkan jendela penyisipan tabel pivot.
Klik OK di jendela Sisipkan PivotTable dan Anda akan mendapatkan tabel pivot baru di buku kerja Anda.

Selamat! Anda telah berhasil membuat tabel pivot baru dari file yang berbeda.

Segarkan PivotTable

Saya yakin Anda sedang memikirkan cara menyegarkan tabel pivot Anda setelah memperbarui file sumber.

Itu mudah. Untuk menyegarkan tabel pivot, Anda harus memperbarui kueri sehingga data dalam file sumber bisa diperbarui di tabel sumber yang Anda buat dengan PowerQuery. Dan setelah itu Anda dapat memperbarui tabel pivot Anda.

Namun cara termudah dan terbaik adalah dengan menggunakan tombol refresh pada tab data. Ini akan merujuk ke keduanya (tabel pivot + kueri).

Poin penting

Di sini saya telah mencantumkan beberapa poin yang perlu Anda ingat saat menggunakan power query untuk tabel pivot.

File harus berada dalam satu folder.
Data harus dalam format yang sama di semua file. Jika tidak, Anda harus membuatnya dapat digunakan setelah menggabungkannya.
Nama lembar kerja harus sama di semua file.
Pastikan tidak ada kesalahan pada suatu file. Atau Anda dapat menggunakan opsi “Abaikan file kesalahan”.
Jangan mengganti nama buku kerja apa pun.