Contents [show]
Jadual pangsi ialah salah satu alat yang paling berkuasa dalam Excel. Ia membolehkan anda menganalisis dan meringkaskan pelbagai ringkasan jumlah data yang besar dengan hanya beberapa klik tetikus. Dalam artikel ini, kita akan berkenalan dengan jadual pangsi, memahami apa itu, belajar cara mencipta dan menyesuaikannya.
Artikel ini ditulis menggunakan Excel 2010. Konsep Jadual Pangsi tidak banyak berubah selama ini, tetapi cara anda menciptanya sedikit berbeza dalam setiap versi baharu Excel. Jika anda mempunyai versi Excel bukan 2010, maka bersedialah bahawa tangkapan skrin dalam artikel ini akan berbeza daripada apa yang anda lihat pada skrin anda.
Sedikit sejarah
Pada hari-hari awal perisian hamparan, bola peraturan Lotus 1-2-3. Penguasaannya begitu lengkap sehinggakan usaha Microsoft untuk membangunkan perisiannya sendiri (Excel) sebagai alternatif kepada Lotus kelihatan seperti membuang masa. Sekarang cepat ke 2010! Excel mendominasi hamparan lebih daripada kod Lotus yang pernah dilakukan dalam sejarahnya, dan bilangan orang yang masih menggunakan Lotus adalah hampir sifar. Bagaimana ini boleh berlaku? Apakah sebab kejadian yang begitu dramatik?
Penganalisis mengenal pasti dua faktor utama:
- Mula-mula, Lotus memutuskan bahawa platform GUI bermodel baharu yang dipanggil Windows ini hanyalah satu trend yang tidak akan bertahan lama. Mereka enggan membina versi Windows Lotus 1-2-3 (tetapi hanya untuk beberapa tahun), meramalkan bahawa versi DOS perisian mereka akan menjadi keperluan semua pengguna. Microsoft secara semula jadi membangunkan Excel khusus untuk Windows.
- Kedua, Microsoft memperkenalkan alat dalam Excel yang dipanggil PivotTables yang tidak tersedia dalam Lotus 1-2-3. Jadual Pangsi, eksklusif untuk Excel, terbukti sangat berguna sehingga orang ramai cenderung menggunakan suite perisian Excel baharu daripada meneruskan dengan Lotus 1-2-3, yang tidak mempunyainya.
Jadual Pangsi, bersama-sama dengan meremehkan kejayaan Windows secara umum, memainkan perarakan maut untuk Lotus 1-2-3 dan membawa kejayaan Microsoft Excel.
Apakah jadual pangsi?
Jadi, apakah cara terbaik untuk mencirikan apa itu Jadual Pangsi?
Secara ringkasnya, jadual pangsi ialah ringkasan beberapa data, dicipta untuk memudahkan analisis data ini. Tidak seperti jumlah yang dibuat secara manual, Jadual Pangsi Excel adalah interaktif. Setelah dibuat, anda boleh mengubah suainya dengan mudah jika mereka tidak memberikan gambar yang anda harapkan. Dengan hanya beberapa klik tetikus, jumlah boleh dibalikkan supaya tajuk lajur menjadi tajuk baris dan sebaliknya. Anda boleh melakukan banyak perkara dengan jadual pangsi. Daripada cuba menerangkan dalam perkataan semua ciri jadual pangsi, lebih mudah untuk menunjukkannya dalam amalan …
Data yang anda analisis dengan Jadual Pangsi tidak boleh rawak. Ia mestilah data mentah mentah, seperti senarai sejenis. Sebagai contoh, ia boleh menjadi senarai jualan yang dibuat oleh syarikat dalam tempoh enam bulan yang lalu.
Lihat data yang ditunjukkan dalam rajah di bawah:
Ambil perhatian bahawa ini bukan data mentah mentah, kerana ia telah diringkaskan. Dalam sel B3 kita melihat $30000, yang mungkin merupakan jumlah hasil yang James Cook lakukan pada bulan Januari. Di manakah data asal itu? Dari mana datangnya angka $30000? Di manakah senarai asal jualan yang mana jumlah bulanan ini diperoleh? Jelas sekali bahawa seseorang telah melakukan kerja yang baik dalam mengatur dan mengisih semua data jualan selama enam bulan lalu dan mengubahnya menjadi jadual jumlah yang kita lihat. Berapa lama anda fikir ia mengambil masa? Jam? Pukul sepuluh?
Hakikatnya jadual di atas bukanlah jadual pangsi. Ia dibuat tangan daripada data mentah yang disimpan di tempat lain dan mengambil masa sekurang-kurangnya beberapa jam untuk diproses. Hanya jadual ringkasan sedemikian boleh dibuat menggunakan jadual pangsi dalam beberapa saat sahaja. Mari kita fikirkan bagaimana…
Jika kita kembali ke senarai jualan asal, ia akan kelihatan seperti ini:
Anda mungkin terkejut bahawa daripada senarai dagangan ini dengan bantuan jadual pangsi dan hanya dalam beberapa saat, kami boleh membuat laporan jualan bulanan dalam Excel, yang kami analisis di atas. Ya, kita boleh melakukannya dan banyak lagi!
Bagaimana untuk membuat jadual pangsi?
Mula-mula, pastikan anda mempunyai beberapa data sumber dalam helaian Excel. Senarai urus niaga kewangan adalah yang paling tipikal yang berlaku. Malah, ia boleh menjadi senarai apa sahaja: butiran hubungan pekerja, koleksi CD atau data penggunaan bahan api syarikat anda.
Jadi, kita mulakan Excel … dan memuatkan senarai sedemikian …
Selepas kami membuka senarai ini dalam Excel, kami boleh mula membuat jadual pangsi.
Pilih mana-mana sel daripada senarai ini:
Kemudian pada tab Memasukkan (Sisipkan) arahan pilih Jadual Pangsi (Jadual pangsi):
Kotak dialog akan muncul Cipta Jadual Pangsi (Membuat jadual pangsi) dengan dua soalan untuk anda:
- Apakah data yang hendak digunakan untuk mencipta jadual pangsi baharu?
- Di mana untuk meletakkan jadual pangsi?
Oleh kerana dalam langkah sebelumnya kami telah memilih salah satu daripada sel senarai, keseluruhan senarai akan dipilih secara automatik untuk membuat jadual pangsi. Ambil perhatian bahawa kita boleh memilih julat yang berbeza, jadual yang berbeza dan juga beberapa sumber data luaran seperti jadual pangkalan data Access atau MS-SQL. Di samping itu, kita perlu memilih tempat untuk meletakkan jadual pangsi baharu: pada helaian baharu atau pada salah satu daripada yang sedia ada. Dalam contoh ini, kami akan memilih pilihan - Lembaran Kerja Baharu (ke helaian baharu):
Excel akan mencipta helaian baharu dan meletakkan jadual pangsi kosong di atasnya:
Sebaik sahaja kami mengklik pada mana-mana sel dalam jadual pangsi, kotak dialog lain akan muncul: Senarai Medan Jadual Pangsi (Medan jadual pangsi).
Senarai medan di bahagian atas kotak dialog ialah senarai semua tajuk daripada senarai asal. Empat kawasan kosong di bahagian bawah skrin membolehkan anda memberitahu Jadual Pangsi cara anda ingin meringkaskan data. Selagi kawasan-kawasan ini kosong, tiada apa-apa di dalam meja sama ada. Apa yang perlu kita lakukan ialah seret tajuk dari kawasan atas ke kawasan kosong di bawah. Pada masa yang sama, jadual pangsi dijana secara automatik, mengikut arahan kami. Jika kita membuat kesilapan, kita boleh mengalih keluar tajuk dari kawasan bawah atau menyeret orang lain untuk menggantikannya.
Kawasan Nilai-nilai (Makna) mungkin yang paling penting daripada empat. Tajuk yang diletakkan di kawasan ini menentukan data yang akan diringkaskan (jumlah, purata, maksimum, minimum, dll.) Ini hampir selalu merupakan nilai berangka. Calon yang sangat baik untuk tempat di kawasan ini ialah data di bawah tajuk jumlah (Kos) jadual asal kami. Seret tajuk ini ke kawasan itu Nilai-nilai (Nilai):
Sila ambil perhatian bahawa tajuk jumlah kini ditandakan dengan tanda semak, dan di kawasan itu Nilai-nilai (Nilai) entri telah muncul Jumlah Amaun (Jumlah medan Amaun), menunjukkan bahawa lajur jumlah disimpulkan.
Jika kita melihat jadual pangsi itu sendiri, kita akan melihat jumlah semua nilai dari lajur jumlah meja asal.
Jadi, jadual pangsi pertama kami dicipta! Mudah, tetapi tidak begitu mengagumkan. Kami mungkin ingin mendapatkan lebih banyak maklumat tentang data kami daripada yang kami miliki sekarang.
Mari beralih kepada data asal dan cuba kenal pasti satu atau lebih lajur yang boleh digunakan untuk membahagikan jumlah ini. Sebagai contoh, kami boleh membentuk jadual pangsi kami sedemikian rupa sehingga jumlah jualan dikira untuk setiap penjual secara individu. Itu. baris akan ditambahkan pada jadual pangsi kami dengan nama setiap jurujual dalam syarikat dan jumlah jualan mereka. Untuk mencapai keputusan ini, hanya seret tajuk orang jualan (wakil jualan) ke rantau ini Label Baris (String):
Ia menjadi lebih menarik! Jadual Pangsi kami mula terbentuk…
Nampak faedahnya? Dalam beberapa klik, kami mencipta jadual yang akan mengambil masa yang lama untuk dibuat secara manual.
Apa lagi yang boleh kita lakukan? Nah, dari satu segi, jadual pangsi kami sudah sedia. Kami telah mencipta ringkasan berguna bagi data asal. Maklumat penting sudah diterima! Dalam artikel yang lain ini, kita akan melihat beberapa cara untuk mencipta Jadual Pangsi yang lebih kompleks, serta mempelajari cara untuk menyesuaikannya.
Persediaan Jadual Pangsi
Pertama, kita boleh mencipta jadual pangsi dua dimensi. Mari lakukan ini menggunakan tajuk lajur Cara pembayaran (Kaedah pembayaran). Seret sahaja tajuk Cara pembayaran ke kawasan tersebut Label Lajur (Lajur):
Kami mendapat hasilnya:
Nampak sangat cool!
Sekarang mari kita buat jadual tiga dimensi. Apakah rupa meja sedemikian? Jom tengok…
Seret Pengepala Pakej (Kompleks) ke kawasan itu penapis laporan (Penapis):
Perhatikan di mana dia berada…
Ini memberi kami peluang untuk menapis laporan berdasarkan "Kompleks percutian manakah yang telah dibayar." Sebagai contoh, kita boleh melihat pecahan mengikut penjual dan kaedah pembayaran untuk semua kompleks, atau dalam beberapa klik tetikus, tukar paparan jadual pangsi dan tunjukkan pecahan yang sama hanya untuk mereka yang memesan kompleks Pencari Matahari.
Jadi, jika anda memahami perkara ini dengan betul, maka jadual pangsi kami boleh dipanggil tiga dimensi. Mari teruskan persediaan…
Jika tiba-tiba ternyata hanya pembayaran melalui cek dan kad kredit (iaitu, pembayaran tanpa tunai) harus dipaparkan dalam jadual pangsi, maka kita boleh mematikan paparan tajuk Tunai (Wang tunai). Untuk ini, di sebelah Label Lajur klik anak panah ke bawah dan nyahtanda kotak dalam menu lungsur Tunai:
Mari lihat rupa jadual pangsi kita sekarang. Seperti yang anda lihat, lajur Tunai hilang dari dia.
Memformat Jadual Pangsi dalam Excel
Jadual Pangsi jelas merupakan alat yang sangat berkuasa, tetapi setakat ini hasilnya kelihatan jelas dan membosankan. Sebagai contoh, nombor yang kami tambahkan tidak kelihatan seperti jumlah dolar – ia hanyalah nombor. Mari kita betulkan ini.
Sangat menarik untuk melakukan perkara yang biasa anda lakukan dalam situasi sedemikian dan hanya pilih keseluruhan jadual (atau keseluruhan helaian) dan gunakan butang pemformatan nombor standard pada bar alat untuk menetapkan format yang diingini. Masalah dengan pendekatan ini ialah jika anda menukar struktur jadual pangsi pada masa hadapan (yang berlaku dengan peluang 99%), pemformatan akan hilang. Apa yang kita perlukan ialah cara untuk menjadikannya (hampir) kekal.
Mula-mula, jom cari entry Jumlah Amaun in Nilai-nilai (Nilai) dan klik padanya. Dalam menu yang muncul, pilih item Tetapan Medan Nilai (Pilihan medan nilai):
Kotak dialog akan muncul Tetapan Medan Nilai (Pilihan medan nilai).
akhbar Format Nombor (Format Nombor), kotak dialog akan dibuka. Format Sel (format sel):
Dari senarai kategori (Format nombor) pilih perakaunan (Kewangan) dan tetapkan bilangan tempat perpuluhan kepada sifar. Sekarang tekan beberapa kali OKuntuk kembali ke jadual pangsi kami.
Seperti yang anda lihat, nombor diformatkan sebagai jumlah dolar.
Sementara kita sibuk dengan pemformatan, mari kita sediakan format untuk keseluruhan Jadual Pangsi. Terdapat beberapa cara untuk melakukan ini. Kami menggunakan yang lebih mudah…
Klik Alat Jadual Pangsi: Reka Bentuk (Bekerja dengan PivotTables: Constructor):
Seterusnya, kembangkan menu dengan mengklik pada anak panah di sudut kanan bawah bahagian Gaya Jadual Pangsi (Gaya Jadual Pangsi) untuk melihat koleksi gaya sebaris yang meluas:
Pilih mana-mana gaya yang sesuai dan lihat hasilnya dalam jadual pangsi anda:
Tetapan Jadual Pangsi lain dalam Excel
Kadangkala anda perlu menapis data mengikut tarikh. Sebagai contoh, dalam senarai dagangan kami terdapat banyak, banyak tarikh. Excel menyediakan alat untuk mengumpulkan data mengikut hari, bulan, tahun dan sebagainya. Mari lihat bagaimana ia dilakukan.
Mula-mula keluarkan entri itu. Cara pembayaran dari rantau ini Label Lajur (Lajur). Untuk melakukan ini, seretnya kembali ke senarai tajuk, dan alihkannya, alihkan tajuk Tarikh Ditempah (tarikh tempahan):
Seperti yang anda lihat, ini menyebabkan jadual pangsi kami tidak berguna buat sementara waktu. Excel mencipta lajur berasingan untuk setiap tarikh perdagangan dibuat. Hasilnya, kami mendapat meja yang sangat luas!
Untuk membetulkannya, klik kanan pada mana-mana tarikh dan pilih daripada menu konteks KUMPULAN (Kumpulan):
Kotak dialog kumpulan akan muncul. Kami pilih Bulan (Bulan) dan klik OK:
Voila! Jadual ini lebih berguna:
Ngomong-ngomong, jadual ini hampir sama dengan yang ditunjukkan pada permulaan artikel, di mana jumlah jualan disusun secara manual.
Terdapat satu lagi perkara yang sangat penting yang perlu anda ketahui! Anda boleh membuat bukan satu, tetapi beberapa peringkat tajuk baris (atau lajur):
… dan ia akan kelihatan seperti ini …
Perkara yang sama boleh dilakukan dengan tajuk lajur (atau pun penapis).
Mari kembali kepada bentuk asal jadual dan lihat cara memaparkan purata dan bukannya jumlah.
Untuk bermula, klik pada Jumlah Amaun dan daripada menu yang muncul pilih Tetapan Medan Nilai (Pilihan medan nilai):
Senarai Ringkaskan medan nilai dengan (Operasi) dalam kotak dialog Tetapan Medan Nilai (Pilihan medan nilai) pilih sederhana (Purata):
Pada masa yang sama, sementara kita di sini, mari berubah Nama Tersuai (Nama tersuai) dengan Purata Jumlah (Amount field Amount) kepada sesuatu yang lebih pendek. Masukkan dalam medan ini sesuatu seperti Purata:
Keratan Akhbar OK dan lihat apa yang berlaku. Ambil perhatian bahawa semua nilai telah berubah daripada jumlah kepada purata, dan pengepala jadual (dalam sel kiri atas) telah berubah kepada Purata:
Jika anda mahu, anda boleh segera mendapatkan jumlah, purata dan nombor (jualan) yang diletakkan dalam satu jadual pangsi.
Berikut ialah panduan langkah demi langkah tentang cara melakukan ini, bermula dengan jadual pangsi kosong:
- Seret Pengepala orang jualan (wakil jualan) ke rantau ini Label Lajur (Lajur).
- Seret tajuk tiga kali jumlah (Kos) ke kawasan Nilai-nilai (Nilai).
- Untuk bidang pertama jumlah tukar tajuk ke Jumlah (Jumlah), dan format nombor dalam medan ini ialah perakaunan (Kewangan). Bilangan tempat perpuluhan ialah sifar.
- Medan kedua jumlah nama Puratae, tetapkan operasi untuknya sederhana (Purata) dan format nombor dalam medan ini juga berubah kepada perakaunan (Kewangan) dengan sifar tempat perpuluhan.
- Untuk bidang ketiga jumlah tetapkan tajuk Pengiraan dan pembedahan untuknya - Pengiraan (Kuantiti)
- Dalam Label Lajur Medan (Lajur) dibuat secara automatik Σ Nilai (Nilai Σ) – seret ke kawasan Label Baris (Barisan)
Inilah yang kita akan berakhir dengan:
Jumlah amaun, nilai purata dan bilangan jualan – semuanya dalam satu jadual pangsi!
Kesimpulan
Jadual pangsi dalam Microsoft Excel mengandungi banyak ciri dan tetapan. Dalam artikel yang begitu kecil, mereka tidak hampir untuk menutup mereka semua. Ia memerlukan buku kecil atau tapak web yang besar untuk menerangkan sepenuhnya semua kemungkinan jadual pangsi. Pembaca yang berani dan ingin tahu boleh meneruskan penerokaan jadual pangsi mereka. Untuk melakukan ini, hanya klik kanan pada hampir mana-mana elemen jadual pangsi dan lihat fungsi dan tetapan yang dibuka. Pada Reben anda akan menemui dua tab: Alat Jadual Pangsi: Pilihan (analisis) dan reka bentuk (Pembina). Jangan takut untuk membuat kesilapan, anda sentiasa boleh memadamkan Jadual Pangsi dan mulakan semula. Anda mempunyai peluang yang tidak pernah dimiliki oleh pengguna lama DOS dan Lotus 1-2-3.