Bekerja dengan Pivot Tables di Microsoft Excel

PivotTables adalah salah satu fitur paling kuat dari Microsoft Excel. Mereka memungkinkan sejumlah besar data untuk dianalisis dan diringkas hanya dalam beberapa klik mouse. Di artikel ini, kami menjelajahi PivotTables, memahami apa itu, dan mempelajari cara membuat dan menyesuaikannya.

Catatan: Artikel ini ditulis menggunakan Excel 2010 (Beta). Konsep PivotTable telah berubah sedikit selama bertahun-tahun, tetapi metode pembuatannya telah berubah di hampir setiap iterasi Excel. Jika Anda menggunakan versi Excel yang bukan 2010, harapkan layar berbeda dari yang Anda lihat di artikel ini.

A Little History

Pada hari-hari awal program spreadsheet, Lotus 1-2-3 memerintah. Dominasi ini begitu lengkap sehingga orang berpikir bahwa buang-buang waktu bagi Microsoft untuk repot mengembangkan perangkat lunak lembar bentang mereka sendiri (Excel) untuk bersaing dengan Lotus. Flash-forward ke 2010, dan dominasi Excel terhadap pasar spreadsheet lebih besar daripada Lotus sebelumnya, sementara jumlah pengguna yang masih menjalankan Lotus 1-2-3 mendekati nol. Bagaimana ini bisa terjadi? Apa yang menyebabkan pembalikan nasib yang dramatis?

Analis industri meletakkannya ke dua faktor: Pertama, Lotus memutuskan bahwa platform GUI baru mewah yang disebut "Windows" ini adalah mode yang tidak akan pernah lepas landas. Mereka menolak untuk membuat versi Windows Lotus 1-2-3 (untuk beberapa tahun, toh), memprediksi bahwa versi DOS dari perangkat lunak itu adalah semua yang dibutuhkan oleh siapa pun. Microsoft, secara alami, mengembangkan Excel secara eksklusif untuk Windows. Kedua, Microsoft mengembangkan fitur untuk Excel yang tidak disediakan Lotus di 1-2-3, yaitu Tabel pivot. Fitur PivotTables, eksklusif untuk Excel, dianggap sangat berguna sehingga orang-orang mau mempelajari seluruh paket perangkat lunak baru (Excel) daripada bertahan dengan program (1-2-3) yang tidak memilikinya. Fitur yang satu ini, bersama dengan salah penilaian keberhasilan Windows, adalah lonceng kematian bagi Lotus 1-2-3, dan awal dari kesuksesan Microsoft Excel.

Memahami PivotTables

Jadi apa itu PivotTable, tepatnya?

Sederhananya, PivotTable adalah ringkasan dari beberapa data, dibuat untuk memungkinkan analisis data yang mudah. Tetapi tidak seperti ringkasan yang dibuat secara manual, Excel PivotTables bersifat interaktif. Setelah Anda membuat satu, Anda dapat dengan mudah mengubahnya jika tidak menawarkan wawasan yang tepat ke dalam data yang Anda harapkan. Dalam beberapa klik ringkasan dapat "diputar" - diputar sedemikian rupa sehingga judul kolom menjadi judul baris, dan sebaliknya. Ada banyak lagi yang bisa dilakukan. Daripada mencoba menggambarkan semua fitur PivotTables, kami hanya akan menunjukkannya ...

Data yang Anda analisis menggunakan PivotTable tidak bisa adil apa saja data - itu harus mentah data, yang sebelumnya tidak diproses (tidak disatukan) - biasanya berupa daftar. Contohnya mungkin daftar transaksi penjualan dalam suatu perusahaan selama enam bulan terakhir.

Periksa data yang ditunjukkan di bawah ini:

Perhatikan ini tidak data mentah. Bahkan, ini sudah merupakan semacam ringkasan. Di sel B3 kita bisa melihat $ 30.000, yang tampaknya adalah total penjualan James Cook untuk bulan Januari. Jadi di mana data mentahnya? Bagaimana kita sampai pada angka $ 30.000? Di mana daftar asli transaksi penjualan dari mana angka ini dihasilkan? Sudah jelas bahwa di suatu tempat, seseorang pasti kesulitan mengumpulkan semua transaksi penjualan selama enam bulan terakhir ke dalam ringkasan yang kita lihat di atas. Menurut Anda berapa lama ini? Satu jam? Sepuluh?

Kemungkinan besar, ya. Soalnya, spreadsheet di atas sebenarnya tidak PivotTable. Itu dibuat secara manual dari data mentah yang disimpan di tempat lain, dan memang butuh beberapa jam untuk dikompilasi. Namun, itu semacam ringkasan itu bisa dibuat menggunakan PivotTables, dalam hal ini hanya perlu beberapa detik. Mari cari tahu caranya ...

Jika kami melacak daftar asli transaksi penjualan, mungkin akan terlihat seperti ini:

Anda mungkin terkejut mengetahui bahwa, dengan menggunakan fitur PivotTable dari Excel, kami dapat membuat ringkasan penjualan bulanan yang mirip dengan yang di atas dalam beberapa detik, dengan hanya beberapa klik mouse. Kita bisa melakukan ini - dan banyak lagi juga!

Cara Membuat PivotTable

Pertama, pastikan Anda memiliki beberapa data mentah di lembar kerja di Excel. Daftar transaksi finansial adalah tipikal, tetapi bisa berupa daftar apa saja: Rincian kontak karyawan, koleksi CD Anda, atau angka konsumsi bahan bakar untuk armada mobil perusahaan Anda.

Jadi kami memulai Excel ... dan kami memuat daftar seperti itu ...

Setelah daftar terbuka di Excel, kami siap untuk mulai membuat PivotTable.

Klik salah satu sel tunggal dalam daftar:

Lalu, dari Memasukkan tab, klik Tabel pivot ikon:

Itu Buat PivotTable kotak muncul, menanyakan dua pertanyaan: Data apa yang harus menjadi dasar PivotTable baru Anda, dan di mana seharusnya itu dibuat? Karena kami sudah mengklik sel di dalam daftar (pada langkah di atas), seluruh daftar di sekitar sel itu sudah dipilih untuk kami ($ A $ 1: $ G $ 88 di Pembayaran sheet, dalam contoh ini). Perhatikan bahwa kita bisa memilih daftar di wilayah lain dari lembar kerja lain, atau bahkan beberapa sumber data eksternal, seperti tabel database Access, atau bahkan tabel database MS-SQL Server. Kita juga perlu memilih apakah kita ingin PivotTable baru dibuat di a baru lembar kerja, atau pada ada satu. Dalam contoh ini kita akan memilih a baru satu:

Lembar kerja baru dibuat untuk kami, dan PivotTable kosong dibuat di lembar kerja itu:

Kotak lain juga muncul: The Daftar Bidang PivotTable. Daftar bidang ini akan ditampilkan setiap kali kami mengklik sel mana pun di dalam PivotTable (di atas):

Daftar bidang di bagian atas kotak sebenarnya adalah kumpulan judul kolom dari lembar kerja data mentah asli. Keempat kotak kosong di bagian bawah layar memungkinkan kita untuk memilih cara yang kita inginkan PivotTable kita untuk merangkum data mentah. Sejauh ini, tidak ada apa pun di kotak itu, jadi PivotTable kosong. Yang perlu kita lakukan adalah menyeret bidang ke bawah dari daftar di atas dan meletakkannya di kotak bawah. PivotTable kemudian secara otomatis dibuat agar sesuai dengan instruksi kami. Jika kita salah, kita hanya perlu menyeret bidang kembali ke tempat asalnya dan / atau seret baru bidang ke bawah untuk menggantinya.

Itu Nilai-nilai kotak bisa dibilang yang paling penting dari empat. Bidang yang diseret ke dalam kotak ini mewakili data yang perlu diringkas dalam beberapa cara (dengan menjumlahkan, rata-rata, menemukan maksimum, minimum, dll). Hampir selalu numerik data. Kandidat yang sempurna untuk kotak ini dalam data sampel kami adalah bidang / kolom “Jumlah”. Mari seret bidang itu ke dalam Nilai-nilai kotak:

Perhatikan bahwa (a) bidang "Jumlah" dalam daftar bidang sekarang dicentang, dan "Jumlah Jumlah" telah ditambahkan ke Nilai-nilai kotak, menunjukkan bahwa kolom jumlah telah dijumlahkan.

Jika kami memeriksa PivotTable sendiri, kami memang menemukan jumlah semua nilai "Jumlah" dari lembar kerja data mentah:

Kami telah membuat PivotTable pertama kami! Berguna, tetapi tidak terlalu mengesankan. Kemungkinan kita membutuhkan sedikit lebih banyak wawasan tentang data kita dari itu.

Mengacu pada data sampel kami, kami perlu mengidentifikasi satu atau lebih judul kolom yang dapat kami gunakan untuk membagi total ini. Misalnya, kami dapat memutuskan bahwa kami ingin melihat ringkasan data kami di tempat kami memiliki judul baris untuk masing-masing tenaga penjualan yang berbeda di perusahaan kami, dan total untuk masing-masing. Untuk mencapai ini, yang perlu kita lakukan adalah menyeret bidang "Tenaga penjual" ke Label Baris kotak:

Sekarang, akhirnya, banyak hal mulai menarik! PivotTable kami mulai terbentuk ... .

Dengan beberapa klik, kami telah membuat tabel yang membutuhkan waktu lama untuk melakukannya secara manual.

Jadi apa lagi yang bisa kita lakukan? Nah, di satu sisi PivotTable kami sudah lengkap. Kami telah membuat ringkasan yang berguna dari data sumber kami. Hal-hal penting sudah dipelajari! Untuk sisa artikel ini, kami akan memeriksa beberapa cara agar PivotTable yang lebih kompleks dapat dibuat, dan cara-cara agar PivotTable tersebut dapat disesuaikan.

Pertama, kita bisa membuat dua-tabel dimensi. Mari kita lakukan itu dengan menggunakan "Metode Pembayaran" sebagai judul kolom. Cukup seret "Metode Pembayaran" ke Label Kolom kotak:

Yang terlihat seperti ini:

Mulai mendapatkan sangat keren!

Mari kita membuatnya tiga-tabel dimensi. Seperti apa meja itu? Baiklah, mari kita lihat ...

Seret kolom / judul "Paket" ke Filter Laporan kotak:

Perhatikan di mana itu berakhir ... .

Ini memungkinkan kami untuk memfilter laporan kami berdasarkan “paket liburan” mana yang dibeli. Sebagai contoh, kita dapat melihat rincian metode tenaga penjual vs pembayaran untuk semua paket, atau, dengan beberapa klik, ubah untuk menampilkan rincian yang sama untuk paket “Sunseekers”:

Jadi, jika Anda memikirkannya dengan benar, PivotTable kami sekarang tiga dimensi. Mari terus menyesuaikan ...

Jika ternyata, katakanlah, bahwa kita hanya ingin melihat cek dan kartu kredit transaksi (mis. tidak ada transaksi tunai), maka kita dapat membatalkan pilihan item "Tunai" dari judul kolom. Klik panah drop-down di sebelah Label Kolom, dan untick "Cash":

Mari kita lihat seperti apa itu… Seperti yang Anda lihat, “Uang Tunai” hilang.

Memformat

Ini jelas merupakan sistem yang sangat kuat, tetapi sejauh ini hasilnya terlihat sangat sederhana dan membosankan. Sebagai permulaan, angka yang kami simpulkan tidak terlihat seperti jumlah dolar - hanya angka lama. Mari kita perbaiki itu.

Godaan mungkin untuk melakukan apa yang biasa kita lakukan dalam keadaan seperti itu dan cukup pilih seluruh tabel (atau seluruh lembar kerja) dan gunakan tombol pemformatan angka standar pada bilah alat untuk menyelesaikan pemformatan. Masalah dengan pendekatan itu adalah bahwa jika Anda pernah mengubah struktur PivotTable di masa mendatang (yang kemungkinan 99%), maka format angka tersebut akan hilang. Kita membutuhkan cara yang akan membuat mereka (semi-) permanen.

Pertama, kami menemukan entri "Jumlah Jumlah" di Nilai-nilai kotak, dan klik di atasnya. Menu muncul. Kami memilih Pengaturan Nilai Bidang ... dari menu:

Itu Pengaturan Nilai Bidang kotak muncul.

Klik tautan Format angka tombol, dan standar Format kotak Sel muncul:

Dari Kategori daftar, pilih (katakan) Akuntansi, dan jatuhkan jumlah tempat desimal ke 0. Klik baik beberapa kali untuk kembali ke PivotTable ...

Seperti yang Anda lihat, angka-angka telah diformat dengan benar sebagai jumlah dolar.

Sementara kita berada di subjek pemformatan, mari memformat seluruh PivotTable. Ada beberapa cara untuk melakukan ini. Mari kita gunakan yang sederhana ...

Klik tautan Alat / Desain PivotTable tab:

Kemudian jatuhkan panah di kanan bawah menu Gaya PivotTable daftar untuk melihat koleksi gaya bawaan:

Pilih salah satu yang menarik, dan lihat hasilnya di PivotTable Anda:

Pilihan lain

Kami juga bisa bekerja dengan teman kencan. Sekarang biasanya, ada banyak, banyak tanggal dalam daftar transaksi seperti yang kita mulai. Tetapi Excel menyediakan opsi untuk mengelompokkan item data bersama-sama berdasarkan hari, minggu, bulan, tahun, dll. Mari kita lihat bagaimana hal ini dilakukan.

Pertama, mari kita hapus kolom "Metode Pembayaran" dari Label Kolom kotak (cukup seret kembali ke daftar bidang), dan ganti dengan kolom "Date Booked":

Seperti yang Anda lihat, ini membuat PivotTable kami menjadi tidak berguna secara instan, memberi kami satu kolom untuk setiap tanggal terjadinya transaksi - tabel yang sangat luas!

Untuk memperbaikinya, klik kanan pada tanggal apa saja dan pilih Kelompok… dari menu konteks:

Kotak pengelompokan muncul. Kami memilih Bulan dan klik OK:

Voila! SEBUAH banyak tabel yang lebih bermanfaat:

(Kebetulan, tabel ini hampir identik dengan yang ditunjukkan di awal artikel ini - ringkasan penjualan asli yang dibuat secara manual.)

Hal keren lainnya yang perlu diperhatikan adalah bahwa Anda dapat memiliki lebih dari satu set judul baris (atau judul kolom):

... yang terlihat seperti ini ... .

Anda dapat melakukan hal serupa dengan judul kolom (atau bahkan filter laporan).

Menyederhanakan segala sesuatunya lagi, mari kita lihat cara merencanakannya rata-rata nilai, bukan nilai yang dijumlahkan.

Pertama, klik "Jumlah Jumlah", dan pilih Pengaturan Nilai Bidang ... dari menu konteks yang muncul:

Dalam Ringkas bidang nilai menurut daftar di Pengaturan Nilai Bidang kotak, pilih Rata-rata:

Sementara kita di sini, mari kita ubah Nama Khusus, dari "Average of Amount" ke sesuatu yang sedikit lebih ringkas. Ketik sesuatu seperti "Rata-rata":

Klik baik, dan lihat seperti apa rupanya. Perhatikan bahwa semua nilai berubah dari total yang dijumlahkan ke rata-rata, dan judul tabel (sel kiri atas) telah berubah menjadi "Rata-rata":

Jika kita suka, kita bahkan dapat memiliki jumlah, rata-rata dan jumlah (jumlah = berapa banyak penjualan yang ada) semua pada PivotTable yang sama!

Berikut adalah langkah-langkah untuk mendapatkan sesuatu seperti itu di tempatnya (mulai dari PivotTable kosong):

Seret "Tenaga Penjual" ke dalam Label Kolom
Seret bidang "Jumlah" ke bawah Nilai-nilai kotak tiga kali
Untuk bidang "Jumlah" pertama, ubah nama khusus menjadi "Total" dan format nomornya menjadi Akuntansi (0 tempat desimal)
Untuk bidang "Jumlah" kedua, ubah nama khusus menjadi "Rata-rata", fungsinya menjadi Rata-rata dan itu format angka untuk Akuntansi (0 tempat desimal)
Untuk bidang "Jumlah" ketiga, ubah namanya menjadi "Jumlah" dan fungsinya menjadi Menghitung
Seret yang dibuat secara otomatis bidang dari Label Kolom untuk Label Baris

Inilah yang kami akhirnya:

Total, rata-rata, dan hitung pada PivotTable yang sama!

Kesimpulan

Ada banyak, lebih banyak fitur dan opsi untuk PivotTables yang dibuat oleh Microsoft Excel - terlalu banyak untuk dicantumkan dalam artikel seperti ini. Untuk sepenuhnya menutupi potensi PivotTables, diperlukan sebuah buku kecil (atau situs web besar). Pembaca yang berani dan / atau culun dapat menjelajahi PivotTables lebih jauh dengan mudah: Cukup klik kanan pada segala hal, dan lihat opsi apa yang tersedia untuk Anda. Ada juga dua tab pita: Alat / Opsi PivotTable dan Desain. Tidak masalah jika Anda membuat kesalahan - mudah untuk menghapus PivotTable dan mulai lagi - kemungkinan pengguna DOS lama Lotus 1-2-3 tidak pernah memiliki.

Jika Anda bekerja di Office 2007, Anda mungkin ingin memeriksa artikel kami tentang cara membuat PivotTable di Excel 2007.

Kami telah menyertakan buku kerja Excel yang dapat Anda unduh untuk melatih keterampilan PivotTable Anda. Ini harus bekerja dengan semua versi Excel mulai dari 97 dan seterusnya.

Unduh Buku Latihan Excel Kami