Konten
Tabel pivot adalah salah satu alat yang paling kuat di Excel. Mereka memungkinkan Anda untuk menganalisis dan meringkas berbagai ringkasan data dalam jumlah besar hanya dengan beberapa klik mouse. Pada artikel ini, kita akan berkenalan dengan tabel pivot, memahami apa itu tabel pivot, mempelajari cara membuat dan menyesuaikannya.
Artikel ini ditulis menggunakan Excel 2010. Konsep PivotTable tidak banyak berubah selama bertahun-tahun, tetapi cara Anda membuatnya sedikit berbeda di setiap versi Excel yang baru. Jika Anda memiliki versi Excel bukan 2010, maka bersiaplah bahwa tangkapan layar di artikel ini akan berbeda dari apa yang Anda lihat di layar Anda.
Sedikit sejarah
Pada hari-hari awal perangkat lunak spreadsheet, bola aturan Lotus 1-2-3. Dominasinya begitu lengkap sehingga upaya Microsoft untuk mengembangkan perangkat lunaknya sendiri (Excel) sebagai alternatif Lotus tampak seperti buang-buang waktu. Sekarang maju cepat ke 2010! Excel mendominasi spreadsheet lebih dari yang pernah dilakukan kode Lotus dalam sejarahnya, dan jumlah orang yang masih menggunakan Lotus mendekati nol. Bagaimana ini bisa terjadi? Apa alasan pergantian peristiwa yang begitu dramatis?
Analis mengidentifikasi dua faktor utama:
- Pertama, Lotus memutuskan bahwa platform GUI bermodel baru yang disebut Windows ini hanya sekedar iseng-iseng yang tidak akan bertahan lama. Mereka menolak untuk membangun versi Windows dari Lotus 1-2-3 (tetapi hanya untuk beberapa tahun), memprediksi bahwa versi DOS dari perangkat lunak mereka akan menjadi semua yang dibutuhkan konsumen. Microsoft secara alami mengembangkan Excel khusus untuk Windows.
- Kedua, Microsoft memperkenalkan alat di Excel yang disebut PivotTable yang tidak tersedia di Lotus 1-2-3. PivotTable, eksklusif untuk Excel, terbukti sangat berguna sehingga orang cenderung tetap menggunakan rangkaian perangkat lunak Excel yang baru daripada melanjutkan dengan Lotus 1-2-3, yang tidak memilikinya.
PivotTable, bersama dengan meremehkan keberhasilan Windows secara umum, memainkan pawai kematian untuk Lotus 1-2-3 dan mengantarkan keberhasilan Microsoft Excel.
Apa itu tabel pivot?
Jadi, apa cara terbaik untuk mengkarakterisasi apa itu PivotTable?
Secara sederhana, tabel pivot adalah ringkasan dari beberapa data, dibuat untuk memfasilitasi analisis data ini. Tidak seperti total yang dibuat secara manual, PivotTable Excel bersifat interaktif. Setelah dibuat, Anda dapat dengan mudah memodifikasinya jika tidak memberikan gambar yang Anda harapkan. Hanya dengan beberapa klik mouse, total dapat dibalik sehingga judul kolom menjadi judul baris dan sebaliknya. Anda dapat melakukan banyak hal dengan tabel pivot. Alih-alih mencoba menggambarkan dengan kata-kata semua fitur tabel pivot, lebih mudah untuk mendemonstrasikannya dalam praktik ...
Data yang Anda analisis dengan PivotTable tidak boleh acak. Itu harus berupa data mentah mentah, seperti semacam daftar. Misalnya, bisa berupa daftar penjualan yang dilakukan perusahaan dalam enam bulan terakhir.
Perhatikan data pada gambar di bawah ini:
Perhatikan bahwa ini bukan data mentah mentah, karena telah diringkas. Di sel B3 kita melihat $30000, yang mungkin merupakan hasil total yang James Cook lakukan pada bulan Januari. Lalu dimana data aslinya? Dari mana angka $30000 itu? Dari mana daftar penjualan asli dari mana total bulanan ini diperoleh? Jelas bahwa seseorang telah melakukan pekerjaan yang baik dalam mengatur dan menyortir semua data penjualan selama enam bulan terakhir dan mengubahnya menjadi tabel total yang kita lihat. Berapa lama menurut Anda? Jam? Jam sepuluh?
Faktanya tabel di atas bukanlah tabel pivot. Itu dibuat dengan tangan dari data mentah yang disimpan di tempat lain dan membutuhkan setidaknya beberapa jam untuk diproses. Tabel ringkasan seperti itu dapat dibuat menggunakan tabel pivot hanya dalam beberapa detik. Mari kita cari tahu bagaimana…
Jika kita kembali ke daftar penjualan asli, itu akan terlihat seperti ini:
Anda mungkin terkejut bahwa dari daftar perdagangan ini dengan bantuan tabel pivot dan hanya dalam beberapa detik, kami dapat membuat laporan penjualan bulanan di Excel, yang kami analisis di atas. Ya, kita bisa melakukan itu dan banyak lagi!
Bagaimana cara membuat tabel pivot?
Pertama, pastikan Anda memiliki beberapa data sumber dalam lembar Excel. Daftar transaksi keuangan adalah yang paling khas yang terjadi. Bahkan, bisa berupa daftar apa saja: detail kontak karyawan, koleksi CD, atau data konsumsi bahan bakar perusahaan Anda.
Jadi, kami memulai Excel ... dan memuat daftar seperti itu ...
Setelah kita membuka daftar ini di Excel, kita bisa mulai membuat tabel pivot.
Pilih sel mana saja dari daftar ini:
Kemudian pada tab Insersi (Sisipkan) pilih perintah Tabel pivot (Tabel pivot):
Sebuah kotak dialog akan muncul Buat PivotTable (Membuat tabel pivot) dengan dua pertanyaan untuk Anda:
- Data apa yang digunakan untuk membuat tabel pivot baru?
- Di mana meletakkan tabel pivot?
Karena pada langkah sebelumnya kita telah memilih salah satu sel daftar, seluruh daftar akan dipilih secara otomatis untuk membuat tabel pivot. Perhatikan bahwa kita dapat memilih rentang yang berbeda, tabel yang berbeda, dan bahkan beberapa sumber data eksternal seperti tabel database Access atau MS-SQL. Selain itu, kita perlu memilih tempat untuk menempatkan tabel pivot baru: di lembar baru atau di salah satu lembar yang sudah ada. Dalam contoh ini, kita akan memilih opsi – Lembar Kerja Baru (ke lembar baru):
Excel akan membuat lembar baru dan menempatkan tabel pivot kosong di atasnya:
Segera setelah kita mengklik sel mana pun di tabel pivot, kotak dialog lain akan muncul: Daftar Bidang PivotTable (Bidang tabel pivot).
Daftar bidang di bagian atas kotak dialog adalah daftar semua judul dari daftar asli. Empat area kosong di bagian bawah layar memungkinkan Anda memberi tahu PivotTable bagaimana Anda ingin meringkas data. Selama area ini kosong, tidak ada apa-apa di tabel juga. Yang harus kita lakukan adalah menyeret judul dari area atas ke area kosong di bawah. Pada saat yang sama, tabel pivot dibuat secara otomatis, sesuai dengan instruksi kami. Jika kami membuat kesalahan, kami dapat menghapus judul dari area bawah atau menyeret yang lain untuk menggantinya.
Daerah Nilai - Nilai (Arti) mungkin yang paling penting dari keempatnya. Judul mana yang ditempatkan di area ini menentukan data apa yang akan diringkas (jumlah, rata-rata, maksimum, minimum, dll.) Ini hampir selalu merupakan nilai numerik. Kandidat yang sangat baik untuk tempat di area ini adalah data di bawah judul Jumlah (Biaya) dari tabel asli kami. Seret judul ini ke area Nilai - Nilai (Nilai):
Harap dicatat bahwa judul Jumlah sekarang ditandai dengan tanda centang, dan di area Nilai - Nilai (Nilai) sebuah entri telah muncul Jumlah Jumlah (Jumlah bidang Jumlah), menunjukkan bahwa kolom Jumlah jumlahkan.
Jika kita melihat tabel pivot itu sendiri, kita akan melihat jumlah semua nilai dari kolom Jumlah meja asli.
Jadi, tabel pivot pertama kami dibuat! Nyaman, tetapi tidak terlalu mengesankan. Kami mungkin ingin mendapatkan lebih banyak informasi tentang data kami daripada yang kami miliki saat ini.
Mari kita beralih ke data asli dan mencoba mengidentifikasi satu atau lebih kolom yang dapat digunakan untuk membagi jumlah ini. Misalnya, kita dapat membentuk tabel pivot kita sedemikian rupa sehingga jumlah total penjualan dihitung untuk setiap penjual secara individual. Itu. baris akan ditambahkan ke tabel pivot kami dengan nama setiap tenaga penjualan di perusahaan dan jumlah total penjualan mereka. Untuk mencapai hasil ini, cukup seret judulnya pramuniaga (perwakilan penjualan) ke wilayah Label Baris (String):
Ini menjadi lebih menarik! PivotTable kami mulai terbentuk…
Lihat manfaatnya? Dalam beberapa klik, kami membuat tabel yang membutuhkan waktu sangat lama untuk dibuat secara manual.
Mau bagaimana lagi? Nah, dalam arti tertentu, tabel pivot kami sudah siap. Kami telah membuat ringkasan yang berguna dari data asli. Informasi penting sudah diterima! Di sisa artikel ini, kita akan melihat beberapa cara untuk membuat PivotTable yang lebih kompleks, serta mempelajari cara menyesuaikannya.
Pengaturan PivotTable
Pertama, kita bisa membuat tabel pivot dua dimensi. Mari kita lakukan ini menggunakan judul kolom Metode Pembayaran (Cara Pembayaran). Tarik saja judulnya Metode Pembayaran ke daerah Label Kolom (Kolom):
Kami mendapatkan hasilnya:
Terlihat sangat keren!
Sekarang mari kita buat tabel tiga dimensi. Seperti apa meja seperti itu? Ayo lihat…
Tarik Tajuk Paket (Kompleks) ke daerah filter laporan (Filter):
Perhatikan di mana dia…
Ini memberi kami kesempatan untuk memfilter laporan berdasarkan "Kompleks liburan mana yang dibayar". Misalnya, kita dapat melihat perincian berdasarkan penjual dan metode pembayaran untuk semua kompleks, atau dalam beberapa klik mouse, mengubah tampilan tabel pivot dan menampilkan perincian yang sama hanya untuk mereka yang memesan kompleks pencari matahari.
Jadi, jika Anda memahami ini dengan benar, maka tabel pivot kami dapat disebut tiga dimensi. Mari lanjutkan penyiapan…
Jika tiba-tiba ternyata hanya pembayaran dengan cek dan kartu kredit (yaitu pembayaran tanpa uang tunai) yang ditampilkan di tabel pivot, maka kita dapat mematikan tampilan judul Uang tunai (Uang tunai). Untuk ini, di sebelah Label Kolom klik panah bawah dan hapus centang pada kotak di menu tarik-turun Uang tunai:
Mari kita lihat seperti apa tabel pivot kita sekarang. Seperti yang Anda lihat, kolom Uang tunai menghilang darinya.
Memformat PivotTable di Excel
PivotTable jelas merupakan alat yang sangat kuat, tetapi sejauh ini hasilnya terlihat sedikit biasa dan membosankan. Misalnya, angka yang kita jumlahkan tidak terlihat seperti jumlah dolar – itu hanya angka. Mari kita perbaiki ini.
Sangat menggoda untuk melakukan apa yang biasa Anda lakukan dalam situasi seperti itu dan cukup pilih seluruh tabel (atau seluruh lembar) dan gunakan tombol pemformatan angka standar pada bilah alat untuk mengatur format yang diinginkan. Masalah dengan pendekatan ini adalah jika Anda pernah mengubah struktur tabel pivot di masa mendatang (yang terjadi dengan kemungkinan 99%), pemformatan akan hilang. Yang kita butuhkan adalah cara untuk membuatnya (hampir) permanen.
Pertama, mari kita temukan entri Jumlah Jumlah in Nilai - Nilai (Nilai) dan klik di atasnya. Di menu yang muncul, pilih item Pengaturan Bidang Nilai (Opsi bidang nilai):
Sebuah kotak dialog akan muncul Pengaturan Bidang Nilai (Pilihan bidang nilai).
Klik tombol jumlah Format (Format Angka), sebuah kotak dialog akan terbuka. Format Cells (format sel):
Dari daftar Kategori (Format angka) pilih akuntansi (Keuangan) dan atur jumlah tempat desimal menjadi nol. Sekarang tekan beberapa kali OKuntuk kembali ke tabel pivot kami.
Seperti yang Anda lihat, angka-angka diformat sebagai jumlah dolar.
Sementara kita melakukannya dengan pemformatan, mari kita siapkan format untuk seluruh PivotTable. Ada beberapa cara untuk melakukan ini. Kami menggunakan yang lebih sederhana…
klik Alat PivotTable: Desain (Bekerja dengan PivotTables: Konstruktor):
Selanjutnya, perluas menu dengan mengklik panah di sudut kanan bawah bagian Gaya PivotTable (Gaya PivotTable) untuk melihat koleksi ekstensif gaya sebaris:
Pilih gaya yang sesuai dan lihat hasilnya di tabel pivot Anda:
Pengaturan PivotTable lainnya di Excel
Terkadang Anda perlu memfilter data berdasarkan tanggal. Misalnya, dalam daftar perdagangan kami ada banyak, banyak tanggal. Excel menyediakan alat untuk mengelompokkan data berdasarkan hari, bulan, tahun, dan sebagainya. Mari kita lihat bagaimana hal itu dilakukan.
Hapus entri terlebih dahulu. Metode Pembayaran dari daerah Label Kolom (Kolom). Untuk melakukan ini, seret kembali ke daftar judul, dan sebagai gantinya, pindahkan judul Tanggal Dipesan (tanggal pemesanan):
Seperti yang Anda lihat, untuk sementara ini membuat tabel pivot kami tidak berguna. Excel membuat kolom terpisah untuk setiap tanggal perdagangan dilakukan. Hasilnya, kami mendapat meja yang sangat lebar!
Untuk memperbaikinya, klik kanan pada tanggal apa pun dan pilih dari menu konteks GROUP (Kelompok):
Kotak dialog pengelompokan akan muncul. Kami memilih Bulan (Bulan) dan klik OK:
Voila! Tabel ini jauh lebih berguna:
Omong-omong, tabel ini hampir identik dengan yang ditampilkan di awal artikel, di mana total penjualan dikompilasi secara manual.
Ada hal lain yang sangat penting yang perlu Anda ketahui! Anda dapat membuat bukan hanya satu, tetapi beberapa tingkat judul baris (atau kolom):
… dan akan terlihat seperti ini …
Hal yang sama dapat dilakukan dengan judul kolom (atau bahkan filter).
Mari kembali ke bentuk asli tabel dan lihat bagaimana menampilkan rata-rata, bukan jumlah.
Untuk memulai, klik Jumlah Jumlah dan dari menu yang muncul pilih Pengaturan Bidang Nilai (Opsi bidang nilai):
Daftar Meringkas bidang nilai dengan (Operasi) di kotak dialog Pengaturan Bidang Nilai (Opsi bidang nilai) pilih Biasa saja (Rata-rata):
Pada saat yang sama, saat kita di sini, mari kita berubah Nama Kustom (Nama khusus) dengan Rata-rata Jumlah (Jumlah bidang Jumlah) menjadi sesuatu yang lebih pendek. Masukkan di bidang ini sesuatu seperti Avg:
Tekan OK dan lihat apa yang terjadi. Perhatikan bahwa semua nilai telah berubah dari total menjadi rata-rata, dan header tabel (di sel kiri atas) telah berubah menjadi Avg:
Jika mau, Anda bisa langsung mendapatkan jumlah, rata-rata dan angka (penjualan) yang ditempatkan dalam satu tabel pivot.
Berikut adalah panduan langkah demi langkah tentang cara melakukan ini, dimulai dengan tabel pivot kosong:
- Tarik Tajuk pramuniaga (perwakilan penjualan) ke wilayah Label Kolom (Kolom).
- Tarik judul tiga kali Jumlah (Biaya) ke area Nilai - Nilai (Nilai).
- Untuk bidang pertama Jumlah ubah judulnya menjadi Total (Jumlah), dan format angka di bidang ini adalah akuntansi (Keuangan). Jumlah tempat desimal adalah nol.
- Bidang kedua Jumlah nama Rata-ratae, atur operasinya Biasa saja (Rata-rata) dan format angka di kolom ini juga berubah menjadi akuntansi (Keuangan) dengan nol tempat desimal.
- Untuk bidang ketiga Jumlah tentukan judul Menghitung dan operasi untuknya - Menghitung (Kuantitas)
- Dalam majalah Label Kolom Bidang (Kolom) dibuat secara otomatis Nilai (Σ Nilai) – seret ke area Label Baris (Garis)
Inilah yang akan kita akhiri:
Jumlah total, nilai rata-rata, dan jumlah penjualan – semuanya dalam satu tabel pivot!
Kesimpulan
Tabel pivot di Microsoft Excel berisi banyak fitur dan pengaturan. Dalam artikel sekecil itu, mereka bahkan tidak dekat untuk menutupi semuanya. Dibutuhkan sebuah buku kecil atau situs web besar untuk sepenuhnya menggambarkan semua kemungkinan tabel pivot. Pembaca yang berani dan ingin tahu dapat melanjutkan eksplorasi tabel pivot mereka. Untuk melakukan ini, cukup klik kanan pada hampir semua elemen tabel pivot dan lihat fungsi dan pengaturan apa yang terbuka. Pada Ribbon Anda akan menemukan dua tab: Alat PivotTable: Opsi (analisis) dan Mendesain (Konstruktor). Jangan takut untuk membuat kesalahan, Anda selalu dapat menghapus PivotTable dan memulai dari awal. Anda memiliki kesempatan yang lama tidak pernah dimiliki oleh pengguna DOS dan Lotus 1-2-3.