Yang merupakan lima bulan hujan paling bawah? Ketahui cara menyelesaikan masalah ini menggunakan jadual pangsi.
Tonton Video
- Jadual pangsi yang dibuat pada tahun 2013 tidak dapat dimuat semula pada tahun 2007
- Anda perlu membuat jadual pangsi pada tahun 2007 untuk membolehkannya disegarkan semula
- Tujuannya adalah untuk mencari lima bulan dengan jumlah hujan paling sedikit
- Buat meja pangsi besar dengan curah hujan setiap bulan
- Isih mengikut kenaikan hujan
- Tukar kepada Borang Jadual
- Gunakan Penapis Nilai, 10 Teratas, untuk mendapatkan 5 teratas!
- Keluarkan baris Grand Total
- Perhatikan bahawa keputusan mungkin menyebabkan laporan ini memberi anda 6 baris atau lebih
- Setelah anda mempunyai jadual pangsi pertama, salin ke tempatnya dan buat jadual pangsi seterusnya
- Apabila anda menukar dari satu bidang nilai ke medan nilai yang lain, anda harus melakukan penyortiran semula dan menapis
- Apabila anda menukar dari satu baris baris ke bidang yang lain, anda harus melakukan penyortiran semula dan menapis
- Petua bonus: membuat jadual pangsi dengan baris dan lajur
Transkrip Video
Belajar Excel dari Podcast, Episode 2063: Lima Bulan atau Tahun Paling Atas atau Bawah Menggunakan Jadual Pangsi.
Hei, selamat datang kembali ke siaran net, saya Bill Jelen. Soalan hari ini dihantar oleh Ken. Ken mempunyai spreadsheet yang luar biasa di sini dengan bertahun-tahun dan bertahun-tahun tarikh hujan harian, bermula pada tahun 1999. Koleksi data yang sangat mengagumkan yang dia ada, dan Ken mempunyai beberapa formula yang luar biasa untuk dicuba dan mencari bulan dengan curah hujan paling banyak, paling sedikit hujan. Jadi sekarang, anda tahu, ini akan menjadi lebih mudah dengan jadual pangsi.
Baiklah sekarang, Ken tidak pernah membuat jadual pangsi dan untuk merumitkan lagi keadaan, saya berada di Excel 2016, Ken menggunakan Excel 2007. Jadual pangsi saya yang saya buat pada tahun 2016, dia dapat melihatnya tetapi dia tidak dapat menyegarkannya. Baiklah, jadi video ini adalah Jadual Pangsi 101: Cara membuat jadual pangsi pertama anda.
Pertama, Ken mempunyai Tarikh ini di Lajur A, tarikh sebenar, adakah kita baik? Itu hebat, bukan? Dan kemudian saya gunakan- masukkan beberapa formula tambahan di sini pada fungsi = TAHUN untuk mendapatkan tahun, = fungsi BULAN untuk mendapatkan bulan, = fungsi HARI. Dan kemudian menggabungkannya kembali, saya benar-benar menggunakan fungsi = TEXT dalam YYYY-MM, dengan cara itu saya mempunyai tahun dan bulan ke bawah. Ini adalah data Ken, data hujan di sini dan kemudian saya menambahkan beberapa formula. Ken mempunyai apa-apa kurang dari 0,5 milimeter, tidak dikira sebagai hari hujan jadi ada formula di sana. Kemudian, dari Episod 735, kembali dan lihatlah untuk melihat bagaimana saya mengira rentetan hari dengan hujan dan deretan hari tanpa hujan. Sekarang itu tidak akan digunakan hari ini, yang digunakan untuk yang lain.
Jadi, kami datang ke sini. Dan pertama, kami ingin memilih data untuk jadual pangsi kami. Sekarang, dalam kebanyakan kes, anda boleh memilih semua data sehingga anda boleh memilih satu sel di sini tetapi dalam kes ini, terdapat julat Nama yang menentukan data hanya, dalam kes ini, 2016. Kami duduk di sini- Saya merakamnya pada awal tahun 2017. Data Ken hanya akan berakhir pada tahun 2016. Oleh itu, kita akan memilih hanya data tersebut. Dan kemudian pada tab Insert- tab Insert. Excel 2007, ini adalah kali pertama jadual pangsi berpindah dari tab Data kembali ke tab Sisip. Oleh itu, kami memilih: Pivot Table, dan data yang kami pilih akan menjadi data yang kami bina. Dan, kami tidak mahu pergi ke lembaran kerja baru, kami akan pergi ke lembaran kerja yang ada dan saya akan meletakkannya di sini di Lajur - mari pergi bersama Lajur N.Sekarang akhirnya, saya mahu data Tahun ini dengan Hujan Terendah muncul di sini tetapi saya tahu bahawa semasa saya membina jadual pangsi ini, ia memerlukan lebih banyak baris daripada yang 5, bukan? Jadi, saya teruskan ke sini, baiklah. Dan kami klik OK.
Baiklah, sekarang inilah yang anda dapat. Di sinilah laporan akan dilampirkan dan inilah senarai semua bidang yang terdapat dalam set data kecil kami. Dan kemudian kita ada, untuk apa yang saya sebut dengan kejam bernama drop out. Baris adalah item yang anda mahukan di sebelah kiri. Nilai adalah perkara yang anda ingin rumuskan dan kemudian Lajur adalah perkara yang anda mahukan di bahagian atas. Kami mungkin menggunakan ini pada akhirnya. Kami tidak akan menggunakan Penapis hari ini. Oleh itu, kami hanya membina meja pangsi kecil sederhana dengan jumlah curah hujan setiap tahun, jadi saya mengambil medan Tahun dan menyeretnya ke sini ke kiri. Ada senarai sepanjang tahun kita, okey? Dan kemudian, fikirkanlah. Untuk mendapatkan formula ini di sini tanpa jadual pangsi, anda akan melakukan apa? SUMIF, oh ya, SUMIF. Anda juga boleh menggunakan belakang SUMIF di Excel 2007. Jadi,Saya akan mengambil medan Hujan dan menyeretnya ke sini. Sekarang perhatikan - Lihat, mereka memilih Count of Rain, itu kerana ada beberapa hari dalam data atau Ken mempunyai sel kosong, sel kosong dan bukannya 0. Dan ya, kita harus melalui dan memperbaikinya tetapi itu data Ken. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya- Untuk apa jua alasan saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.Sekarang perhatikan - Lihat, mereka memilih Count of Rain, itu kerana ada beberapa hari dalam data atau Ken mempunyai sel kosong, sel kosong dan bukannya 0. Dan ya, kita harus melalui dan memperbaikinya tetapi itu data Ken. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya- Untuk apa jua alasan saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.Sekarang perhatikan - Lihat, mereka memilih Count of Rain, itu kerana ada beberapa hari dalam data atau Ken mempunyai sel kosong, sel kosong dan bukannya 0. Dan ya, kita harus melalui dan memperbaikinya tetapi itu data Ken. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya - Atas sebab apa pun saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.kerana ada beberapa hari dalam data atau Ken mempunyai sel kosong, sel kosong dan bukannya 0. Dan ya, kita harus meneliti dan memperbaikinya tetapi itu adalah data Ken. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya- Untuk apa jua alasan saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.kerana ada beberapa hari dalam data atau Ken mempunyai sel kosong, sel kosong dan bukannya 0. Dan ya, kita harus meneliti dan memperbaikinya tetapi itu adalah data Ken. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya- Untuk apa jua alasan saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.data s. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya- Untuk apa jua alasan saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.data s. Data bernilai 20 tahun. Saya tidak akan melalui walaupun Cari & Ganti. Baiklah, saya hanya - Atas sebab apa pun saya akan menghormati bahawa Ken mempunyai alasan untuk memilikinya, seperti saya akan membiarkan mereka tetap kosong. Dan di sini, di bawah Count of Rain, saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.Saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.Saya akan memastikan memilih sel di lajur Count of Rain, pergi ke Field Settings, dan menukarnya dari Count ke Sum, baiklah? Jadi ada sepanjang tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami mencari selama bertahun-tahun dengan curah hujan terendah.
Baiklah sekarang, satu perkara yang mengganggu saya adalah perkataan ini di sini Row Labels. Itu mula berlaku kepada kita di Excel 2007, ok? Dan saya - 10 tahun kemudian saya masih memandang rendah perkara itu. Saya pergi ke Tab Reka Bentuk, buka Tata Letak Laporan dan katakan Tunjukkan dalam Borang Tabular, dan semua yang berlaku. Dalam kes ini, dapatkan tajuk yang benar di sana pada tahun ini, bukan? Dan saya lebih suka tajuk yang sebenarnya. Buat masa ini, kami ingin melihat bahagian atas atau dalam kes ini, Tahun dengan Hujan Terendah. Oleh itu, saya akan menyusun data ini secara menaik. Sekarang ada dua cara untuk melakukan ini. Anda boleh membuka menu lungsur ini, pergi ke Lebih banyak Pilihan Susun, pilih Untuk mengirim berdasarkan jumlah hujan, tetapi juga mungkin hanya datang ke sini ke Data, A hingga Z untuk mendapatkan sesuatu yang disusun dari yang paling rendah ke tertinggi. Tetapi saya tidak mahu melihat hanya 5 tahun teratas, jadi Tahun dengan Hujan Terendah,Saya datang ke sini menuju tajuk Tahun, buka drop-down kecil ini dan pilih Penapis Nilai. Dan saya mencari Bawah 5. Baiklah, tidak ada penapis untuk Bawah 5. Ahh, tetapi yang ini untuk sepuluh teratas sangat hebat. Baiklah, ia tidak perlu menjadi yang terbaik. Ia boleh menjadi bahagian atas atau bawah. Tidak semestinya 10; ia dapat 5. Oleh itu, minta 5 Item Teratas berdasarkan jumlah hujan, klik OK. Dan ada laporan kami.
Now in this case, it would be really highly unusual if we had exactly 2 years with 767.7 inches or millimeters of rain exactly, right? Just not going to happen. But you have to be cognizant of the fact that when you asked for the Top 5, if there is a tie for that position, you might get a sixth row. If there's a 3-way tie, you might get a seventh row. Alright, so just be prepared for that. Grand total here really makes no sense since we're showing just the Top 5, and they're not even 5 consecutive years. So I’m going to right-click on the word Grand Total and say Remove Grand Total. Remove Grand Total. I’m wondering if that was there in Excel 2007. If it's not there in Excel 2007, go to the Design tab, Grand Totals, Off for Rows and Columns. We’ll do the same thing, alright. So now that we have this first pivot table and it's sized correctly, I’m going to copy that pivot table, Ctrl+C, make sure to choose the entire pivot table and go there - Years with Lowest Rainfall.
Now another thing that Ken wants is the years with highest, highest rainfall. Alright, so in this case, we're going to Sort the data, Z-A descending. And then here, come back into the Value Filters, go back into Top 10 and simply change it from Bottom to Top, click OK. Alright, so once you get the first pivot table built, pivot tables are so flexible. It's incredible how easy it is to just keep changing the pivot table. Alright now, here's the- here's the gotcha, right. The thing that makes us a little bit difficult. Now we want to look at the years with the least number of rain days, alright. How many days do we have- the fewest number of days with rain? Alright, so now this is going to change the pivot table a little because I want to take the Sum of Rain out, and I’m going to replace it with Rain Day. Alright, and see that one automatically came in as sum because my formula here is always returning a numeric values. We didn't have to change it to Account. And we're looking for the years with the least rain day so we're going to sort this ascending, alright, so that gets our sort back in but we've lost the Value Filter, the Top 5. Because we took the field that it was using, Rain out. So we have to reapply that: Value Filters and say Top - Actually we want the bottom, with the Bottom 5, like that.
Alright, so every time that I take a field in or out over here in the Values area, you have to be prepared to redo the Sort and redo the Filter. So Ctrl+C to copy that and paste right here, Years with the Least Rain Days.
Now, Ken has a lot of other statistics. I'll leave this up to Ken how to do this, but see here when we change the months, months with the highest rainfall. Alright, so now, I’m going to be changing a field in the row area. So we take Year out, put the Month field in like that and then this goes back to Rain, instead of Rain Days we put Rain in. Again, they forgotten that we want a sum so you have to go back in. In 2007, it’s called the Analyze tab, it's the first pivot table tools tab. Go to Field Settings, choose something like that - beautiful. And what are we looking for? We're looking for the months with the highest rainfall. So we're going to Sort Descending: Data, Z to A, and then again here, go back in to the Value Filters, Top 10 and we will ask for the Top 5 like that, alright? So very, very flexible. You can figure out the months with the least rain days, the most rain days and so on. So copy here, Ctrl+C and come and paste.
Now, one of the thing that Ken was building, he’s building a beautiful master table. It took this daily data and summarized it by year and month. So, let's just do that. Let's put Years down the left-hand side like this. Year's down the left-hand side, I got lazy there. I tried to check Market, it went to the wrong spot. We have some rainfall but we want to see months going across. And I know I have some other data out here to the right so I’m just going to insert a whole bunch of extra columns. Insert columns that way, I know that my pivot table won't crash into that. Beautiful thing here that Month field, the 1 through 12, I take that, drag it to the columns. And I now have a report showing years down the side, months going across the top and the summary of how much rain we had in each one. Those pivot tables are just an amazing, amazing feature.
Alright, if you're new to pivot tables, my new book, Power Excel with. This book hit the bookstores January 1st , what about - 36 days ago. But the new e-book versions for the Kindle, for your iPad, and we are PDF. Those are all now available at. If you buy the book from me, if you buy the print book for me, you get all 3 of those eBook formats for free. Minor have no DRM, no hassles. We believe in no hassles. You buy the book, you get all the formats. And what’s - Click the link down there in the YouTube description to get to my page where you can buy that book.
So wrap- up: Pivot tables created in Excel 2013 or 2016, can’t be refreshed in 2007. You have to create the pivot table in 2007 to allow it to be refreshable. So our goal is to find the five months with the least rainfall. Created a large pivot table with a rainfall by month, I knew that was going to fit in more than five rows. I built it off to the side. Sort by rainfall, actually, ascending is what we did here. Change the tabular form and then open that drop-down in the first column using the Value Filters, Top 5. It’s weird, you asked for the Top 10 and you get the bottom 5. Took the Grand Total row out. Even though we're asking for 5, you might get 6 if there's ever a tie - 6 or 7. And then, once we have the first pivot table, copied it into place and then created the next pivot table and just kept doing that. Although couple of gotchas when you change from one value field to another, you have to redo the Sort & Filter. You might have to redo the Count to Sum. When you change from one row field to another you have to redo the Sort & Filter. And then, right there at the end, showed you how to create a pivot table with rows and column, alright.
Baiklah, saya ingin mengucapkan terima kasih kepada Ken kerana telah menghantar soalan itu. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail sampel di sini: Podcast2063.xlsm
