If-Koubou

Bekerja dengan Pivot Tables di Microsoft Excel

Bekerja dengan Pivot Tables di Microsoft Excel (Bagaimana caranya)

PivotTable adalah salah satu fitur paling kuat dari Microsoft Excel. Mereka memungkinkan sejumlah besar data untuk dianalisis dan diringkas hanya dalam beberapa klik mouse. Dalam artikel ini, kami menjelajahi PivotTable, memahami apa itu, dan belajar 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, mengharapkan layar yang berbeda dari yang Anda lihat di artikel ini.

A Little History

Pada hari-hari awal program spreadsheet, Lotus 1-2-3 memutuskan bertengger. Dominasi ini begitu lengkap sehingga orang berpikir itu adalah buang-buang waktu bagi Microsoft untuk repot-repot mengembangkan perangkat lunak spreadsheet mereka sendiri (Excel) untuk bersaing dengan Lotus. Flash-maju ke 2010, dan dominasi Excel dari pasar spreadsheet lebih besar dari Lotus yang pernah ada, sementara jumlah pengguna yang masih menjalankan Lotus 1-2-3 mendekati nol. Bagaimana ini bisa terjadi? Apa yang menyebabkan pembalikan kekayaan secara dramatis?

Para analis industri meletakkannya pada dua faktor: Pertama, Lotus memutuskan bahwa platform GUI baru yang mewah ini disebut "Windows" adalah mode yang lewat yang tidak akan pernah lepas landas. Mereka menolak untuk membuat versi Windows Lotus 1-2-3 (untuk beberapa tahun, bagaimanapun juga), memprediksi bahwa versi DOS mereka dari perangkat lunak adalah semua yang dibutuhkan. Microsoft, secara alami, mengembangkan Excel secara eksklusif untuk Windows. Kedua, Microsoft mengembangkan fitur untuk Excel yang tidak disediakan oleh Lotus dalam 1-2-3, yaitu Tabel pivot. Fitur PivotTable, eksklusif untuk Excel, dianggap sangat bermanfaat sehingga orang ingin mempelajari seluruh paket perangkat lunak baru (Excel) daripada tetap menggunakan program (1-2-3) yang tidak memilikinya. Fitur yang satu ini, bersama dengan kesalahan penilaian kesuksesan Windows, adalah lonceng kematian untuk Lotus 1-2-3, dan awal dari kesuksesan Microsoft Excel.

Memahami PivotTable

Jadi apa itu PivotTable, tepatnya?

Sederhananya, PivotTable adalah ringkasan dari beberapa data, dibuat untuk memungkinkan analisis yang mudah dari data tersebut. Namun tidak seperti ringkasan yang dibuat secara manual, Excel PivotTable bersifat interaktif. Setelah Anda membuatnya, Anda dapat dengan mudah mengubahnya jika tidak menawarkan wawasan yang tepat ke data yang Anda harapkan. Dalam beberapa klik, ringkasan dapat "diputar" - dirotasi sedemikian rupa sehingga judul kolom menjadi judul baris, dan sebaliknya. Ada banyak lagi yang bisa dilakukan juga. Daripada mencoba mendeskripsikan semua fitur PivotTable, kami akan mendemonstrasikannya…

Data yang Anda analisis menggunakan PivotTable tidak bisa begitu saja apa saja data - itu harus mentah data, yang sebelumnya tidak diproses (tidak dipetakan) - biasanya berupa daftar. Contoh dari ini mungkin adalah daftar transaksi penjualan di sebuah perusahaan selama enam bulan terakhir.

Periksa data yang ditunjukkan di bawah ini:

Perhatikan bahwa ini tidak data mentah. Sebenarnya, ini sudah merupakan ringkasan dari beberapa macam. Di sel B3 kita dapat melihat $ 30.000, yang tampaknya adalah total penjualan James Cook untuk bulan Januari. Jadi dimana data mentahnya? Bagaimana kita sampai pada angka $ 30.000? Di mana daftar asli dari transaksi penjualan yang dihasilkan oleh angka ini? Sudah jelas bahwa di suatu tempat, seseorang pasti mengalami kesulitan dalam menyusun semua transaksi penjualan selama enam bulan terakhir ke dalam ringkasan yang kita lihat di atas. Berapa lama menurut Anda ini? Satu jam? Sepuluh?

Kemungkinan besar, ya. Anda lihat, spreadsheet di atas sebenarnya tidak PivotTable. Itu dibuat secara manual dari data mentah yang disimpan di tempat lain, dan itu memang membutuhkan waktu beberapa jam untuk dikompilasi. Namun, ini persis seperti ringkasan itu bisa dibuat menggunakan PivotTable, dalam hal ini hanya butuh beberapa detik. Mari kita cari tahu caranya ...

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

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

Cara Membuat PivotTable

Pertama, pastikan Anda memiliki beberapa data mentah dalam lembar kerja di Excel. Daftar transaksi keuangan adalah tipikal, tetapi dapat berupa daftar apa saja: Detail 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 kami membuka daftar di Excel, kami siap untuk mulai membuat PivotTable.

Klik pada satu sel tunggal dalam daftar:

Kemudian, 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 (dalam langkah di atas), seluruh daftar yang mengelilingi sel tersebut telah dipilih untuk kami ($ A $ 1: $ G $ 88 pada Pembayaran lembar, dalam contoh ini). Perhatikan bahwa kita bisa memilih daftar di wilayah lain mana pun 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 kami dibuat pada 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 mengeklik sel apa 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 kita ingin PivotTable kita meringkas data mentah. Sejauh ini, tidak ada apa pun di kotak itu, sehingga PivotTable kosong. Yang perlu kita lakukan adalah menyeret bidang ke bawah dari daftar di atas dan menjatuhkannya di kotak bawah. PivotTable kemudian secara otomatis dibuat untuk mencocokkan instruksi kami. Jika kita salah, kita hanya perlu menyeret kembali bidang ke tempat mereka berasal dan / atau seret baru sawah untuk menggantikannya.

Itu Nilai-nilai kotak ini bisa dibilang yang paling penting dari empat. Bidang yang diseret ke dalam kotak ini mewakili data yang perlu dirangkum 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 kolom / 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 jumlah kolom telah dijumlahkan.

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

Kami telah membuat PivotTable pertama kami! Berguna, tetapi tidak terlalu mengesankan. Sepertinya kita memerlukan sedikit lebih banyak wawasan ke dalam data kita daripada itu.

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

Sekarang, akhirnya, hal-hal mulai menjadi menarik! PivotTable kami mulai terbentuk….

Dengan beberapa klik kami telah membuat tabel yang akan memakan waktu lama untuk dilakukan secara manual.

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

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

Yang terlihat seperti ini:

Mulai mendapatkan sangat keren!

Mari kita buat a tiga-dimensi dimensi. Seperti apa meja itu? Baiklah, mari kita lihat ...

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

Perhatikan di mana itu berakhir….

Ini memungkinkan kami untuk memfilter laporan kami berdasarkan "paket liburan" yang dibeli. Misalnya, kita dapat melihat rincian metode penjual vs pembayaran untuk semua paket, atau, dengan beberapa klik, ubah untuk menunjukkan rincian yang sama untuk paket "Sunseekers":

Jadi, jika Anda memikirkannya dengan cara yang benar, PivotTable kami sekarang tiga dimensi. Mari tetap menyesuaikan ...

Jika ternyata, katakanlah, bahwa kita hanya ingin melihat cek dan kartu kredit transaksi (yaitu tidak ada transaksi tunai), maka kita dapat membatalkan pilihan item "Uang" dari judul kolom. Klik panah tarik-turun di samping Label Kolom, dan untick “Uang Tunai”:

Mari kita lihat apa yang terlihat seperti ... Seperti yang Anda lihat, "Uang Tunai" hilang.

Format

Ini jelas sistem yang sangat kuat, tetapi sejauh ini hasilnya terlihat sangat polos dan membosankan. Sebagai permulaan, angka-angka yang kita tambahkan tidak tampak 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 depan (yang kemungkinannya 99%), maka format angka tersebut akan hilang. Kami membutuhkan cara yang akan membuat mereka (semi) permanen.

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

Itu Pengaturan Nilai Bidang kotak muncul.

Klik Format Angka tombol, dan standar Format kotak Sel muncul:

Dari Kategori daftar, pilih (katakanlah) 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.

Saat kami sedang memformat, mari kita format seluruh PivotTable. Ada beberapa cara untuk melakukan ini. Mari gunakan yang sederhana ...

Klik Alat / Desain PivotTable tab:

Lalu, turunkan panah di kanan bawah Gaya PivotTable daftar untuk melihat koleksi besar gaya bawaan:

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

Pilihan lain

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

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

Seperti yang Anda lihat, ini membuat PivotTable kami langsung tidak berguna, memberi kami satu kolom untuk setiap tanggal saat transaksi terjadi - tabel yang sangat lebar!

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

Kotak pengelompokan muncul. Kami pilih Bulan dan klik OK:

Voila! SEBUAH banyak tabel lebih berguna:

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

Hal keren lain yang harus diperhatikan adalah Anda dapat memiliki lebih dari satu kumpulan judul baris (atau judul kolom):

... yang terlihat seperti ini ....

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

Menjaga semua hal tetap sederhana lagi, mari kita lihat bagaimana caranya merencanakannya dirata-ratakan nilai, bukan nilai yang dijumlahkan.

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

Dalam Rangkumlah bidang nilai dengan daftar di Pengaturan Nilai Bidang kotak, pilih Rata-rata:

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

Klik baik, dan lihat seperti apa bentuknya. Perhatikan bahwa semua nilai berubah dari penjumlahan total menjadi rata-rata, dan judul tabel (sel kiri atas) telah berubah menjadi "Rta":

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 tempat (dimulai dari PivotTable kosong):

  1. Seret "Tenaga Penjual" ke dalam Label Kolom
  2. Seret bidang "Jumlah" ke dalam Nilai-nilai kotak tiga kali
  3. Untuk bidang "Jumlah" pertama, ubah nama khususnya menjadi "Total" dan format angka itu menjadi Akuntansi (0 tempat desimal)
  4. Untuk kolom "Jumlah" kedua, ubah nama khususnya menjadi "Rata-rata", fungsinya menjadi Rata-rata dan itu format angka Akuntansi (0 tempat desimal)
  5. Untuk kolom "Jumlah" ketiga, ubah namanya menjadi "Hitung" dan fungsinya menjadi Menghitung
  6. Seret secara otomatis dibuat bidang dari Label Kolom untuk Label Baris

Inilah yang kita berakhir dengan:

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

Kesimpulan

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

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

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

Unduh Buku Kerja Praktik Excel Kami