Bekerja dengan Jadual Pivot dalam Microsoft Excel

PivotTables adalah salah satu ciri yang paling berkuasa Microsoft Excel. Mereka membenarkan sejumlah besar data untuk dianalisis dan diringkaskan dalam hanya beberapa klik tetikus. Dalam artikel ini, kami meneroka PivotTables, memahami apa yang mereka ada, dan belajar bagaimana membuat dan menyesuaikannya.

Nota: Artikel ini ditulis menggunakan Excel 2010 (Beta). Konsep PivotTable telah berubah sedikit sejak bertahun-tahun, tetapi kaedah mencipta telah berubah dalam hampir setiap lelaran Excel. Jika anda menggunakan versi Excel yang bukan 2010, mengharapkan skrin yang berbeza daripada yang anda lihat dalam artikel ini.

Sejarah Sedikit

Pada masa awal program spreadsheet, Lotus 1-2-3 memerintah roost. Penguasaannya begitu lengkap sehingga orang menganggap ia adalah satu pembaziran masa bagi Microsoft untuk mengganggu membangunkan perisian spreadsheet mereka sendiri (Excel) untuk bersaing dengan Lotus. Flash ke depan untuk tahun 2010, dan dominasi pasaran spreadsheet Excel lebih besar dari Lotus yang pernah ada, sementara bilangan pengguna masih berjalan Lotus 1-2-3 sedang mendekati sifar. Bagaimana ini berlaku? Apa yang menyebabkan begitu banyak pembalikan dramatik?

Penganalisis industri meletakkannya ke dua faktor: Pertama, Lotus memutuskan bahawa platform GUI baru yang bernama "Windows" ini merupakan satu lintasan yang tidak akan lepas. Mereka menolak untuk membuat versi Windows Lotus 1-2-3 (selama beberapa tahun, bagaimanapun), meramalkan bahawa versi DOS mereka adalah semua orang yang memerlukannya. Microsoft, secara semulajadi, membangun Excel secara eksklusif untuk Windows. Kedua, Microsoft mengembangkan ciri untuk Excel yang Lotus tidak menyediakan dalam 1-2-3, iaitu PivotTables.  Ciri-ciri PivotTables, eksklusif untuk Excel, dianggap sangat mengagumkan berguna bahawa orang bersedia mempelajari keseluruhan pakej perisian (Excel) daripada melekat dengan program (1-2-3) yang tidak memilikinya. Ciri-ciri ini, bersama-sama dengan kejahilan kejayaan Windows, adalah kejatuhan kematian untuk Lotus 1-2-3, dan permulaan kejayaan Microsoft Excel.

Memahami PivotTables

Jadi apa yang PivotTable, betul-betul?

Secara ringkasnya, PivotTable merupakan ringkasan dari beberapa data, yang dibuat untuk membolehkan analisis mudah data tersebut. Tetapi tidak seperti ringkasan dibuat secara manual, Excel PivotTables adalah interaktif. Sebaik sahaja anda telah membuat satu, anda boleh dengan mudah mengubahnya jika ia tidak menawarkan pandangan yang tepat ke dalam data anda yang anda harapkan. Dalam beberapa klik ringkasan boleh "diputar" - diputar sedemikian rupa sehingga tajuk lajur menjadi tajuk baris, dan sebaliknya. Ada banyak lagi yang boleh dilakukan juga. Daripada cuba untuk menggambarkan semua ciri-ciri PivotTables, kami hanya akan menunjukkan mereka ...

Data yang anda analisa menggunakan PivotTable tidak boleh menjadi adil mana-mana data - ia sepatutnya mentah data, sebelum ini tidak diproses (tidak disahkulasikan) - biasanya senarai semacam. Satu contoh ini mungkin merupakan senarai urus niaga jualan dalam syarikat selama enam bulan yang lalu.

Periksa data yang ditunjukkan di bawah:

Perhatikan bahawa ini tidak data mentah. Malah, ia sudah menjadi ringkasan semacamnya. Di sel B3 kita dapat melihat $ 30,000, yang nampaknya jumlah penjualan James Cook untuk bulan Januari. Jadi di mana data mentah? Bagaimanakah kita sampai ke angka $ 30,000? Di manakah senarai jualan urus niaga asli yang dihasilkan oleh angka ini? Sudah jelas bahawa di suatu tempat, seseorang mesti pergi ke masalah menyusun semua urus niaga jualan selama enam bulan yang lalu ke dalam ringkasan yang kita lihat di atas. Berapa lamakah anda mengambil ini? Sejam? Sepuluh?

Kemungkinan besar, ya. Anda lihat, spreadsheet di atas sebenarnya tidak PivotTable. Ia dicipta secara manual dari data mentah yang disimpan di tempat lain, dan ia memang mengambil masa beberapa jam untuk dikompilasi. Walau bagaimanapun, ia betul-betul jenis ringkasan itu boleh dicipta menggunakan PivotTables, dalam hal ini ia akan mengambil hanya beberapa saat. Mari cari tahu bagaimana ...

Jika kita mengesan senarai jualan urus niaga asal, mungkin kelihatan seperti ini:

Anda mungkin terkejut mengetahui bahawa, menggunakan ciri PivotTable Excel, kami boleh membuat ringkasan jualan bulanan sama seperti yang di atas dalam beberapa saat, dengan hanya beberapa klik tetikus. Kita boleh melakukan ini - dan lebih banyak juga!

Bagaimana Membuat PivotTable

Pertama, pastikan anda mempunyai beberapa data mentah dalam lembaran kerja dalam Excel. Senarai transaksi kewangan adalah tipikal, tetapi ia boleh menjadi senarai hanya kira-kira apa-apa: Butiran perhubungan pekerja, koleksi CD anda, atau angka penggunaan bahan bakar untuk armada kereta syarikat anda.

Jadi kita mulakan Excel ... dan kita memuatkan senarai sedemikian ...

Sebaik sahaja kami mendaftarkan senarai dalam Excel, kami bersedia untuk memulakan PivotTable.

Klik pada mana-mana satu sel tunggal dalam senarai:

Kemudian, dari Masukkan tab, klik PivotTable ikon:

The Buat PivotTable kotak muncul, meminta anda dua soalan: Data apa yang harus berdasarkan PivotTable baharu anda, dan di manakah ia harus dibuat? Kerana kita sudah mengklik pada sel dalam senarai (dalam langkah di atas), seluruh senarai sekitar sel itu telah dipilih untuk kami ($ A $ 1: $ G $ 88 pada Pembayaran lembaran, dalam contoh ini). Perhatikan bahawa kami boleh memilih senarai di mana-mana rantau lain mana-mana lembaran kerja lain, atau bahkan beberapa sumber data luaran, seperti jadual pangkalan Akses, atau jadual pangkalan data MS-SQL Server. Kami juga perlu memilih sama ada kami mahukan PivotTable baru kami dibuat pada baru lembaran kerja, atau pada sedia ada satu. Dalam contoh ini kita akan memilih a baru satu:

Lembaran kerja baru dicipta untuk kami, dan PivotTable kosong dicipta pada lembaran kerja:

Kotak lain juga muncul: The Senarai Medan PivotTable.  Senarai medan ini akan ditunjukkan apabila kita mengklik mana-mana sel dalam PivotTable (di atas):

Senarai medan di bahagian atas kotak sebenarnya adalah koleksi tajuk lajur dari lembaran kerja data mentah asli. Empat kotak kosong di bahagian bawah skrin membolehkan kami memilih cara kami ingin PivotTable kami untuk meringkaskan data mentah. Setakat ini, tiada apa-apa di dalam kotak itu, jadi PivotTable kosong. Apa yang perlu kita lakukan ialah menyeret medan turun dari senarai di atas dan lepaskan mereka di kotak yang lebih rendah. A PivotTable kemudian dicipta secara automatik untuk memadankan arahan kami. Sekiranya kita salah, kita hanya perlu menyeret bidang ke tempat asalnya dan / atau seret baru bidang untuk menggantikannya.

The Nilai kotak boleh dikatakan yang paling penting daripada empat. Bidang yang diseret ke dalam kotak ini mewakili data yang perlu diringkaskan dalam beberapa cara (dengan menjumlahkan, membuat purata, mencari maksimum, minimum, dan sebagainya). Ia hampir selalu berangka data. Calon yang sempurna untuk kotak ini dalam data sampel kami ialah bidang / lajur "Amaun". Mari seret medan itu ke dalam Nilai kotak:

Perhatikan bahawa (a) medan "Amaun" dalam senarai medan sekarang ditandakan, dan "Amaun Jumlah" telah ditambah kepada Nilai kotak, menunjukkan bahawa jumlah ruang telah dijumlahkan.

Sekiranya kita memeriksa PivotTable itu sendiri, kita dapati jumlah semua nilai "Amaun" dari lembaran kerja data mentah:

Kami telah mencipta PivotTable pertama kami! Berguna, tetapi tidak begitu mengagumkan. Kemungkinan besar kita memerlukan lebih banyak wawasan tentang data kita daripada itu.

Merujuk kepada data sampel kami, kami perlu mengenal pasti satu atau lebih tajuk lajur yang dapat kita gunakan untuk memecahkan jumlah ini. Sebagai contoh, kami mungkin memutuskan bahawa kami ingin melihat ringkasan data kami di mana kami mempunyai tajuk baris untuk setiap penjual yang berbeza di syarikat kami, dan jumlah keseluruhan bagi setiap. Untuk mencapai matlamat ini, semua yang perlu kita lakukan ialah menyeret bidang "Jurujual" ke dalam Label Baris kotak:

Sekarang, akhirnya, perkara mula menarik! PivotTable kami mula mengambil bentuk ... .

Dengan beberapa klik kami telah membuat jadual yang akan mengambil masa yang lama untuk dilakukan secara manual.

Jadi apa lagi yang boleh kita lakukan? Nah, dalam satu keadaan, PivotTable kami selesai. Kami telah mencipta ringkasan berguna data sumber kami. Perkara penting sudah dipelajari! Untuk bahagian artikel yang lain, kami akan mengkaji beberapa cara yang lebih kompleks PivotTables boleh diwujudkan, dan cara yang PivotTables itu boleh disesuaikan.

Pertama, kita boleh mencipta dua-jadual dimensi. Mari kita lakukannya dengan menggunakan "Kaedah Pembayaran" sebagai tajuk lajur. Hanya seret "Kaedah Pembayaran" menuju ke Label Lajur kotak:

Yang kelihatan seperti ini:

Bermula untuk mendapatkan sangat sejuk!

Mari kita buat tiga-jadual dimensi. Apa yang boleh kelihatan seperti jadual? Nah, mari lihat ...

Seret lajur "Pakej" / menuju ke Laporkan Penapis kotak:

Notis di mana ia berakhir ... .

Ini membolehkan kami menapis laporan kami berdasarkan "pakej percutian" yang dibeli. Sebagai contoh, kita dapat melihat pecahan kaedah jualan vs jurujual untuk semua pakej, atau, dengan beberapa klik, ubahnya untuk menunjukkan pecahan yang sama untuk pakej "Sunseekers":

Dan jika anda berfikir tentang cara yang betul, PivotTable kami sekarang tiga dimensi. Mari teruskan menyesuaikan ...

Jika ternyata, katakan, kita hanya mahu melihat cek dan kad kredit urus niaga (iaitu tiada urus niaga tunai), maka kami boleh menyahpilih item "Tunai" dari tajuk lajur. Klik anak panah lungsur di sebelah Label Lajur, dan mengutip "Tunai":

Mari lihat apa yang kelihatan seperti ... Seperti yang anda lihat, "Tunai" hilang.

Pemformatan

Ini jelas merupakan sistem yang sangat kuat, tetapi setakat ini hasilnya kelihatan sangat jelas dan membosankan. Sebagai permulaan, nombor yang kita nyatakan tidak kelihatan seperti jumlah dolar - hanya nombor lama biasa. Baiklah.

Cobaan mungkin melakukan apa yang biasa kita lakukan dalam keadaan sedemikian dan hanya pilih seluruh jadual (atau keseluruhan lembaran kerja) dan gunakan butang pemformatan nombor standard pada bar alat untuk menyelesaikan pemformatan. Masalahnya dengan pendekatan itu ialah jika anda pernah mengubah struktur PivotTable pada masa akan datang (yang mungkin 99%), maka format nombor tersebut akan hilang. Kita memerlukan satu cara yang akan menjadikan mereka (semi-) kekal.

Mula-mula, kita dapati kemasukan "Amaun Jumlah" di Nilai kotak, dan klik padanya. Menu muncul. Kami pilih Tetapan Field Value ... dari menu:

The Tetapan Medan Nilai kotak muncul.

Klik Format Nombor butang, dan piawai Kotak sel format muncul:

Daripada Kategori senarai, pilih (katakan) Perakaunan, dan jatuhkan bilangan tempat perpuluhan kepada 0. Klik okey beberapa kali untuk kembali ke PivotTable ...

Seperti yang dapat anda lihat, nombor telah diformat dengan betul sebagai jumlah dolar.

Walaupun kita dalam subjek pemformatan, mari format semua PivotTable. Terdapat beberapa cara untuk melakukan ini. Mari kita gunakan yang mudah ...

Klik Alat / Reka Bentuk PivotTable tab:

Kemudian lepaskan anak panah di sebelah kanan bawah Gaya PivotTable senarai untuk melihat koleksi gaya terbina dalam yang luas:

Pilih mana-mana rayuan, dan lihat hasil dalam PivotTable anda:

Pilihan lain

Kita boleh bekerja dengan tarikh juga. Kini biasanya, terdapat banyak, tarikh dalam senarai urusniaga seperti yang kita mulakan. Tetapi Excel memberikan pilihan untuk mengumpulkan data item bersama-sama dengan hari, minggu, bulan, tahun, dan lain-lain. Mari lihat bagaimana ini dilakukan.

Pertama, mari keluarkan lajur "Kaedah Pembayaran" daripada Label Lajur kotak (hanya seret semula ke senarai medan), dan gantikannya dengan lajur "Tarikh Dipetik":

Seperti yang anda dapat lihat, ini menjadikan PivotTable kami dengan serta-merta tidak berguna, memberikan kita satu lajur untuk setiap tarikh transaksi berlaku - meja yang sangat luas!

Untuk membetulkannya, klik kanan pada mana-mana tarikh dan pilih Kumpulan… dari menu konteks:

Kotak pengelompokan muncul. Kami pilih Bulan dan klik OK:

Voila! A banyak jadual yang lebih berguna:

(Secara kebetulan, jadual ini hampir sama dengan yang ditunjukkan pada permulaan artikel ini - ringkasan jualan asal yang dicipta secara manual.)

Satu lagi perkara yang menarik untuk diperhatikan ialah anda boleh mempunyai lebih daripada satu set tajuk baris (atau tajuk lajur):

... yang kelihatan seperti ini ... .

Anda boleh melakukan perkara yang sama dengan tajuk lajur (atau bahkan melaporkan penapis).

Menjaga perkara mudah sekali lagi, mari kita lihat bagaimana plot purata nilai-nilai, dan bukan nilai-nilai yang dijumlahkan.

Pertama, klik pada "Jumlah Jumlah", dan pilih Tetapan Field Value ... dari menu konteks-muncul:

Di dalam Nyatakan nilai medan dengan senarai dalam Tetapan Medan Nilai kotak, pilih Purata:

Walaupun kita berada di sini, mari kita tukar Nama Custom, dari "Purata Jumlah" kepada sesuatu yang lebih ringkas. Taipkan sesuatu seperti "Purata":

Klik okey, dan lihat apa yang kelihatannya. Perhatikan bahawa semua nilai berubah dari jumlah keseluruhan kepada purata, dan tajuk jadual (sel kiri atas) telah berubah menjadi "Purata":

Jika kita suka, kita juga boleh mempunyai jumlah, purata dan tuduhan (bilangan = berapa jualan yang ada) semua pada Pangsi PivotTable yang sama!

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

1. Seret "Jurujual" ke dalam Label Lajur
2. Seret medan "Amaun" ke dalam Nilai kotak tiga kali
3. Untuk medan "Amaun" pertama, tukar nama tersuai kepada "Jumlah" dan format nombor kepada Perakaunan (0 tempat perpuluhan)
4. Untuk bidang "Jumlah" kedua, tukar nama tersuainya kepada "Purata", fungsinya kepada Purata dan format nombor itu kepada Perakaunan (0 tempat perpuluhan)
5. Untuk medan "Jumlah" ketiga, tukar namanya kepada "Count" dan fungsinya kepada Kira
6. Seret secara automatik dibuat bidang dari Label Lajur kepada Label Baris

Berikut adalah apa yang kita berakhir dengan:

Jumlah, purata dan bergantung pada PivotTable yang sama!

Kesimpulannya

Terdapat banyak lagi ciri dan pilihan untuk PivotTables yang dicipta oleh Microsoft Excel - terlalu banyak untuk disenaraikan dalam artikel seperti ini. Untuk menampung sepenuhnya potensi PivotTables, sebuah buku kecil (atau laman web besar) akan diperlukan. Pembaca berani dan / atau pembaca boleh meneroka PivotTables dengan lebih mudah: Cukup klik pada hampir segala-galanya, dan lihat pilihan yang tersedia untuk anda. Terdapat juga dua tab reben: Alat / Pilihan PivotTable dan Reka bentuk.  Tidak kira jika anda membuat kesilapan - mudah untuk memadam PivotTable dan mula lagi - kemungkinan pengguna DOS lama Lotus 1-2-3 tidak pernah.

Jika anda bekerja di Office 2007, anda mungkin ingin menyemak artikel kami mengenai cara membuat PivotTable dalam Excel 2007.

Kami telah memasukkan buku kerja Excel yang boleh anda muat turun untuk mengamalkan kemahiran PivotTable anda. Ia harus berfungsi dengan semua versi Excel dari 97 dan seterusnya.

Muat Turun Amalan Buku Kerja Excel kami