Manakah lima bulan terakhir curah hujan? Pelajari cara menyelesaikan masalah ini menggunakan tabel pivot.
Menonton video
- Tabel pivot yang dibuat pada tahun 2013 tidak dapat diperbarui pada tahun 2007
- Anda perlu membuat tabel pivot pada tahun 2007 agar dapat disegarkan
- Tujuannya adalah menemukan lima bulan dengan curah hujan paling sedikit
- Buat tabel pivot besar dengan curah hujan per bulan
- Urutkan berdasarkan curah hujan
- Ubah ke Bentuk Tabular
- Gunakan Value Filters, Top 10, untuk mendapatkan 5 terbawah!
- Hapus baris Grand Total
- Perhatikan bahwa dasi mungkin menyebabkan laporan ini memberi Anda 6 baris atau lebih
- Setelah Anda memiliki tabel pivot pertama, salin ke tempatnya dan buat tabel pivot berikutnya
- Saat Anda mengubah dari satu bidang nilai ke nilai lainnya, Anda harus melakukan ulang pengurutan dan filter
- Saat Anda mengubah dari satu bidang baris ke lainnya, Anda harus melakukan ulang pengurutan dan filter
- Tip bonus: membuat tabel pivot dengan baris dan kolom
Transkrip Video
Pelajari Excel dari Podcast, Episode 2063: Lima Bulan atau Tahun Atas atau Bawah Menggunakan Tabel Pivot.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dikirim oleh Ken. Ken memiliki spreadsheet yang luar biasa di sini dengan tahun curah hujan harian selama bertahun-tahun, kembali ke tahun 1999. Kumpulan data yang sangat mengesankan yang dia miliki, dan Ken memiliki beberapa formula yang luar biasa untuk dicoba dan menemukan bulan dengan curah hujan paling banyak, di curah hujan paling sedikit. Jadi sekarang, Anda tahu, ini akan jauh lebih mudah dengan tabel pivot.
Baiklah sekarang, Ken tidak pernah membuat tabel pivot dan untuk lebih memperumitnya, saya di sini di Excel 2016, Ken menggunakan Excel 2007. Tabel pivot saya yang saya buat pada tahun 2016, dia bisa melihatnya tetapi dia tidak bisa menyegarkannya. Baiklah, jadi video ini adalah Tabel Pivot 101: Cara membuat tabel pivot pertama Anda.
Pertama, Ken memiliki Tanggal ini di Kolom A, tanggal sebenarnya, apakah kita baik-baik saja? Itu luar biasa, bukan? Dan kemudian saya menggunakan- masukkan beberapa rumus tambahan di sini di fungsi = YEAR untuk mendapatkan fungsi tahun, = MONTH untuk mendapatkan fungsi bulan, = DAY. Dan kemudian menggabungkannya kembali, saya benar-benar menggunakan fungsi = TEXT dalam YYYY-MM, dengan cara itu saya memiliki tahun dan bulan ke bawah. Ini adalah data Ken, data hujan disini dan kemudian saya tambahkan beberapa rumus. Ken memiliki kurang dari 0,5 milimeter, tidak dihitung sebagai hari hujan jadi ada rumusnya di sana. Dan kemudian, dari Episode 735, kembali dan lihat itu untuk melihat bagaimana saya menghitung deretan hari dengan hujan dan deretan hari tanpa hujan. Sekarang itu tidak akan digunakan hari ini, itu digunakan untuk hal lain.
Jadi, kami datang ke sini. Dan pertama, kami ingin memilih data untuk tabel pivot kami. Sekarang, dalam banyak kasus, Anda dapat memilih semua data sehingga Anda dapat memilih satu sel saja di sini tetapi dalam kasus ini, ada rentang Nama yang mendefinisikan data hanya melalui, dalam kasus ini, 2016. Kami duduk di sini- Saya ' Saya merekam ini di awal tahun 2017. Data Ken hanya melewati akhir 2016. Jadi, kita akan memilih data itu saja. Dan kemudian pada tab Sisipkan- tab Sisipkan. Excel 2007, ini pertama kalinya tabel pivot pindah dari tab Data kembali ke tab Sisipkan. Jadi kami memilih: Tabel Pivot, dan data yang kami pilih akan menjadi data yang kami buat. Dan, kami tidak ingin pergi ke lembar kerja baru, kami akan pergi ke lembar kerja yang sudah ada dan saya akan meletakkannya di sini di Kolom - mari kita pergi dengan Kolom N.Pada akhirnya, saya ingin data Tahun dengan Curah Hujan Terendah ini muncul di sini, tetapi saya tahu bahwa saat saya membuat tabel pivot ini, akan membutuhkan lebih banyak baris daripada 5 baris tersebut, bukan? Jadi, saya membangunnya ke samping di sini, oke. Dan kami klik OK.
Baiklah, sekarang ini yang Anda dapatkan. Ini adalah tempat laporan akan pergi dan inilah daftar semua bidang yang kami miliki di kumpulan data kecil kami. Dan kemudian kami memiliki, untuk apa yang saya sebut dengan nama mengerikan keluar. Baris adalah item yang Anda inginkan di sisi kiri. Nilai adalah hal yang ingin Anda simpulkan dan kemudian Kolom adalah hal yang Anda inginkan di bagian atas. Kami mungkin menggunakan ini di akhir. Kami tidak akan menggunakan Filter hari ini. Jadi, kami hanya membangun tabel pivot kecil sederhana dengan total curah hujan menurut tahun, jadi saya mengambil bidang Tahun dan menyeretnya ke sini ke sisi kiri. Ada daftar semua tahun kita, oke? Dan kemudian, pikirkanlah. Untuk mendapatkan rumus ini di sini tanpa tabel pivot, Anda akan melakukan apa? SUMIF, oh ya, SUMIF. Anda bahkan dapat menggunakan SUMIF kembali di Excel 2007. Jadi,Saya akan mengambil bidang Rain dan menyeretnya ke sini. Sekarang hati-hati terhadap - Lihat, mereka memilih Hitungan Hujan, itu karena ada beberapa hari dalam data atau Ken memiliki sel kosong, sel kosong, bukan 0. Dan ya, kita harus melalui dan memperbaikinya tapi itu data Ken. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken memiliki alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.Sekarang hati-hati terhadap - Lihat, mereka memilih Hitungan Hujan, itu karena ada beberapa hari dalam data atau Ken memiliki sel kosong, sel kosong, bukan 0. Dan ya, kita harus melalui dan memperbaikinya tapi itu data Ken. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken memiliki alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.Sekarang hati-hati terhadap - Lihat, mereka memilih Hitungan Hujan, itu karena ada beberapa hari dalam data atau Ken memiliki sel kosong, sel kosong, bukan 0. Dan ya, kita harus melalui dan memperbaikinya tapi itu data Ken. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken memiliki alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.Itu karena ada beberapa hari dalam data atau Ken memiliki sel kosong, sel kosong bukannya 0. Dan ya, kita harus melalui dan memperbaikinya tapi itu adalah data Ken. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken memiliki alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.Karena ada beberapa hari dalam data atau Ken memiliki sel kosong, sel kosong, bukan 0. Dan ya, kita harus melalui dan memperbaikinya tetapi itu adalah data Ken. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken punya alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.s data. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken memiliki alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.s data. Ini adalah data selama 20 tahun. Saya tidak akan melanjutkan bahkan menggunakan Temukan & Ganti. Baiklah, saya hanya- Untuk alasan apa pun saya akan menghormati bahwa Ken punya alasan untuk memilikinya, seperti saya akan membiarkannya tetap kosong. Dan di sini, di bawah Hitungan Hujan, saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.Saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.Saya akan memastikan untuk memilih sel di kolom Hitungan Hujan, pergi ke Pengaturan Bidang, dan mengubahnya dari Hitungan ke Jumlah, oke? Jadi ada tahun-tahun kami dan berapa banyak hujan yang kami alami setiap tahun. Dan kami sedang mencari tahun-tahun dengan curah hujan terendah.
Baiklah sekarang, satu hal yang mengganggu saya adalah kata di sini Label Baris. Itu mulai terjadi pada kami di Excel 2007, oke? Dan saya - 10 tahun kemudian saya masih membenci itu. Saya pergi ke Tab Desain, membuka Tata Letak Laporan dan mengatakan Perlihatkan dalam Bentuk Tabular, dan semua yang dilakukannya. Dalam kasus ini adalah mendapatkan judul nyata di sana Tahun, kan? Dan saya lebih suka judul yang sebenarnya. Saat ini, kami hanya ingin melihat bagian atas atau dalam hal ini, Tahun dengan Curah Hujan Terendah. Jadi saya akan mengurutkan data ini naik. Sekarang ada dua cara untuk melakukan ini. Anda dapat membuka tarik-turun ini, pergi ke Opsi Urutan Lainnya, pilih Untuk mengirim berdasarkan jumlah hujan, tetapi juga memungkinkan untuk masuk ke sini ke Data, A hingga Z untuk menyortir hal-hal dari yang terendah ke tertinggi. Tapi saya tidak ingin hanya melihat 5 tahun teratas, jadi Tahun dengan Curah Hujan Terendah,Saya datang ke sini untuk judul Year, buka drop-down kecil ini dan pilih Value Filters. Dan saya sedang mencari 5 Bawah. Tidak ada filter untuk 5 Bawah. Ahh, tapi yang ini untuk sepuluh besar sangat kuat. Baiklah, tidak harus menjadi yang teratas. Bisa atas atau bawah. Tidak harus 10; bisa 5. Jadi, 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 berterima kasih kepada Ken karena telah mengirimkan pertanyaan itu. Saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2063.xlsm
