() translation by (you can also view the original English article)
Ketika kamu ingin membuat PivotTable, apa yang kamu lakukan jika kamu memiliki data dalam worksheet yang berbeda? Jika kamu menggunakan Excel 2013, kamu memiliki metode umum untuk melakukan ini. Ada sebuah teknik yang disebut Data Model, dan itu menggunakan keterkaitan data seperti yang dilakukan database.
Di dalam tutorial ini, saya akan menunjukkan semuanya yang kamu perlukan untuk membuat PivotTable dalam Excel 2013 dari data dalam banyak sheet, dengan menggunakan Data Model.
Screencast
Jika kamu ingin mengikuti bersamaan dengan tutorial ini dengan menggunakan file Excel-mu sendiri, kamu dapat melakukannya. Atau jika kamu lebih suka, download file zip yang dicantumkan untuk tutorial ini, yang berisi workbook contoh bernama Pivot Consolidate.xlsx.
Memeriksa Data
Workbook ini memiliki tiga worksheet: Customer Info, Order Info, dan Payment Info.
Klik sheet Customer Info, dan lihat apakah itu berisi nomor pemesanan dan nama dan negara bagian pelanggan.
Klik sheet Order Info, dan lihat apakah itu juga berisi nomor pemesanan, sebagaimana fields untuk bulan, produk yang dipesan, dan apakah produk tersebut organik atau tidak.
Klik sheet Payment Info, dan lihat apakah itu berisi nomor pemesanan, jumlah dollar tiap penjualan, metode pembayaran, dan apakah pesanan dibuat oleh pelanggan baru atau yang sudah ada.
Dengan menghubungkan semua sheet ini di dalam task pane PivotTable, kita dapat memilih data dari tiap sheet. Karena nomor pesanan ada di dalam ketiga sheet, mereka akan menjadi titik koneksi. Ini adalah apa yang disebut database sebagai primary key. Perhatikan bahwa itu tidak selalu memerlukan primary key, namun itu mengurangi kesempatan error.
Membuat Table Bernama
Sebelum membuat PivotTable, mari kita buat sebuah tabel dari tiap sheet.
Klik kembal pada Customer Table, kemudian klik dimana pun di dalam area data. Pergi ke tab Insert pada bar ribbon, kemudian klik icon Table.
Kotak Create Table mengidentifikasi secara benar area tabel. Kotak centang pada bagian bawah hendaklah juga mengidentifikasi bahwa baris pertama tabel adalah untuk header. (Jika tidak, pilih pilihan itu).
Klik OK, dan sekarang kamu memiliki sebuah tabel dengan shading bergaris dan tombol filter. Kamu dapat mengklik di dalamnya untuk membatalkan pilihan, jika kamu ingin tampilan yang lebih baik pada itu (hanya jangan klik di luar tabel). Bar ribbon juga menampilkan tab Design untuk tabel. Pada sisi kiri ribbon, kotak Table Name menunjukkan nama sementara yaitu Table1. Hapus itu, dan beri nama Customer_Info (gunakan garis bawah alih-alih spasi). Kemudian tekan Enter.
Ulangi proses ini pada sheet Order Info dan Payment Info. Beri nama tabel Order_Info dan Payment_Info, sesuai urutan.
Sekarang kita siap memasukkan PivotTable.
Memasukkan PivotTable
Pada sheet Payment Info, pastikan kursor ada di suatu tempat dalam tabel. Kembali ke tab Insert pada ribbon, dan klik icon PivotTable (itu adalah icon yang paling awal).
Kotak dialog yang muncul hendaklah mengidentifikasi secara benar tabel dan memilih bahwa PivotTable akan berada pada worksheet baru. Pada bagian bawah, klik kotak centang Add this data to the Data Model. Kemudian klik OK.
Kamu sekarang akan memiliki sebuah PivotTable pada worksheet baru, akan ada sebuah task pane pada sisi kanan layar, dan bar ribbon akan menampilkan tab Analyze.
Task pane menunjukkan tabel dan field hanya pada sheet aktif, jadi klik ALL untuk melihat semua tabel yang kamu buat. Namun sebelum kita dapat menggunakannya, kita harus menghubungkan mereka ke masing-masing lainnya, dan itu berarti membuat keterkaitan. Klik tombol Relationships pada bar ribbon.
Pengaturan Keterkaitan Tabel
Mengklik tombol itu menampilkan dialog Manage Relationships. Klik tombol New, dan itu menampilkan dialog Create Relationship. Kita akan membuat dua relationship dengan menggunakan nomor pemesanan sebagai penghubung.
Dari daftar drop-down, pilih Payment_Info untuk tabel, dan di sebelahnya, pilih Order # dari drop-down Column. Pada baris kedua, pilih Customer_Info dari daftar drop-down Related Table, dan di sebelahnya, pilih Order # dari daftar drop-down Related Column.
Ini berarti tabel Payment_Info dan Customer_Info terkait dimana mereka memiliki nomor pesanan yang cocok.
Klik OK, dan kita melihat keterkaitan itu didaftarkan dalam kotak Manage Relationships.
Ulangi proses ini untuk membuat sebuah keterkaitan yang menghubungkan Payment_Info ke Order_Info, juga menggunakan Order #. Kotak Manage Relationships sekarang hendaklah tampak seperti ini:
Perhatikan bahwa tidak perlu membuat keterkaitan antara tabel Order_Info dan Customer_Info, karena mereka digabungkan secara otomatis melalui tabel Payment_Info.
Klik tombol Close pada bagian bawah kotak. Sekarang akhirnya kita dapat menarik field ke dalam PivotTable.
Memasukkan Fields Ke Dalam PivotTable
Dalam SEMUA bagian task pane, klik panah kecil untuk membuka ketiga tabel, sehingga kamu melihat field-nya. Seret field ke dalam area PivotTable sebagai berikut:
- State dan Month ke dalam rows
- Product ke dalam columns
- $ Sale ke dalam values
- Status ke dalam filters
Kamu sekarang dapat menggunakan dan memodifikasi ini seperti PivotTable lainnya.
Kesimpulan
Dengan menggunakan fitur Object Data Model yang baru dalam Excel 2013, kamu dapat memilih kolom dari banyak worksheet untuk membuat PivotTable terpadu. Mohon diingat bahwa baris tiap tabel perlu dikaitkan pada masing-masing lainnya dalam beberapa cara. Kamu akan memiliki kesempatan berhasil terbaik ketika tabel memiliki kolom umum dengan nilai unik.
Jika kamu mencari cara bagus dalam menyajikan data, Envato Market memiliki pilihan bagus template Excel dan PowerPoint, juga script dan aplikasi untuk mengubah data Excel ke dalam format ramah web dan sebaliknya.
