Koji su donjih pet mjeseci kiše? Saznajte kako riješiti ovaj problem pomoću zaokretne tablice.
Gledaj video
- Izvedene tablice stvorene 2013. godine ne mogu se osvježiti u 2007. godini
- Morate stvoriti zaokretnu tablicu u 2007. kako biste je mogli osvježiti
- Cilj je pronaći pet mjeseci s najmanje kiše
- Stvorite veliku stožernu tablicu s padalinama po mjesecima
- Poredaj po padalinama uzlazno
- Promijenite u tablični obrazac
- Upotrijebite filtre vrijednosti, prvih 10, da biste dobili donjih 5!
- Uklonite redak ukupnog broja
- Imajte na umu da kravata može uzrokovati da ovo izvješće dobije 6 ili više redaka
- Nakon što stvorite prvu zaokretnu tablicu, kopirajte je na svoje mjesto i stvorite sljedeću zaokretnu tablicu
- Kada promijenite iz jednog polja vrijednosti u drugo, morate ponovno izvršiti sortiranje i filtriranje
- Kada promijenite polje iz jednog retka u drugo, morate ponovno izvršiti sortiranje i filtriranje
- Bonus savjet: stvaranje pivot tablice s redovima i stupcima
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2063: Vrh ili dno pet mjeseci ili godina pomoću zaokretne tablice.
Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. Današnje pitanje poslao je Ken. Ken ovdje ima nevjerojatnu proračunsku tablicu s godinama i godinama i godinama dnevnih kišnih datuma, koji sežu u 1999. Zaista impresivna zbirka podataka koje ima, a Ken je imao nekoliko nevjerojatnih formula kako bi pokušao pronaći mjesec s najviše kiše, na najmanje kiše. Dakle, znate, ovo će biti puno lakše sa zaokretnom tablicom.
U redu, Ken nikada nije stvorio stožernu tablicu i da dodatno zakompliciram stvari, ja sam ovdje u Excelu 2016, Ken koristi Excel 2007. Moje stožerne tablice koje sam kreirao 2016. mogao ga je vidjeti, ali ih nije mogao osvježiti. U redu, ovaj videozapis je pivot tablica 101: Kako stvoriti svoju prvu pivot tablicu.
Prvo, Ken ima ovaj datum u koloni A, stvarne datume, jesmo li dobri? To je super, zar ne? A onda koristim - ovdje umetnem nekoliko dodatnih formula u funkciji = YEAR da dobijem godinu, = MONTH funkciju da dobijem mjesec, = DAY funkciju. A onda ih opet spojim, zapravo sam koristio funkciju = TEXT u GGGG-MM, na taj način imam godinu i mjesec manje. Ovo su Kenovi podaci, ovdje su podaci o kiši i tada sam dodao neke formule. Ken's ima nešto manje od .5 milimetara, ne računa se kao kišni dan pa tamo postoji formula. A onda, iz Epizode 735, vratite se i pogledajte to kako biste vidjeli kako sam izračunao niz dana s kišom i niz dana bez kiše. To se danas neće koristiti, nego za nešto drugo.
Dakle, dolazimo ovdje. I prvo, želimo odabrati podatke za našu zaokretnu tablicu. Sada, u većini slučajeva, možete samo odabrati sve podatke kako biste ovdje mogli odabrati samo jednu ćeliju, ali u ovom slučaju postoji raspon imena koji definira podatke upravo kroz, u ovom slučaju, 2016. Sjedimo ovdje - ja ' m bilježim ovo početkom 2017. Kenovi podaci prolaze tek do kraja 2016. Dakle, odabrat ćemo upravo te podatke. A zatim na kartici Umetanje - kartica Umetanje. Excel 2007, ovo je prvi put da se pivot tablice premještaju s kartice Podaci natrag na karticu Umetanje. Stoga odabiremo: Zaokretnu tablicu, a odabrani podaci bit će podaci iz kojih gradimo. I, ne želimo ići na novi radni list, idemo na postojeći radni list i stavit ću to točno ovdje u Stupac - idemo sa Stupcem N.U konačnici, želim da se ovi podaci Godine s najnižom kišom pojave upravo ovdje, ali znam da će, dok gradim ovu stožernu tablicu, trebati puno više redaka od onih 5, zar ne? Dakle, ovdje ga gradim sa strane, u redu. I kliknemo U redu.
U redu, evo što ćete dobiti. Ovo je mjesto gdje će izvještaj ići, a ovdje je popis svih polja koja imamo u našem malom skupu podataka. A onda smo, jer ono što ja nazivam užasno nazvanim ispada. Redovi su stavke koje želite dolje s lijeve strane. Vrijednosti je stvar koju želite sažeti, a zatim su stupci stvari koje želite na vrhu. Ovo bismo mogli upotrijebiti na kraju. Danas nećemo koristiti Filtere. Dakle, upravo gradimo jednostavnu malu stožernu tablicu s ukupnom količinom kiše po godini, pa uzmem polje Godina i povučem ga ovdje dolje s lijeve strane. Postoji popis svih naših godina, u redu? A onda, razmislite o tome. Da biste ovu formulu dobili bez zaokretne tablice, što biste radili? SUMIF, oh da, SUMIF. Možete čak koristiti i SUMIF-ova leđa u programu Excel 2007. Dakle,Idem Poljem kiše i odvući to ovamo. Trenutno pripazite na - Vidite, izabrali su Count of Rain, jer zato ima nekoliko dana podataka ili Ken ima praznu ćeliju, praznu ćeliju umjesto 0. I da, trebali bismo to proći i popraviti, ali to su Kenovi podaci. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga zbog kojeg ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, obavezno odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.Trenutno pripazite na - Vidite, izabrali su Count of Rain, jer zato ima nekoliko dana podataka ili Ken ima praznu ćeliju, praznu ćeliju umjesto 0. I da, trebali bismo to proći i popraviti, ali to su Kenovi podaci. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga koji ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, obavezno odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.Trenutno pripazite na - Vidite, izabrali su Count of Rain, jer zato ima nekoliko dana podataka ili Ken ima praznu ćeliju, praznu ćeliju umjesto 0. I da, trebali bismo to proći i popraviti, ali to su Kenovi podaci. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga zbog kojeg ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, obavezno odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.s jer ima nekoliko dana podataka ili Ken ima praznu ćeliju, praznu ćeliju umjesto 0. I da, trebali bismo to proći i popraviti, ali to su Kenovi podaci. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga koji ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, pobrinut ću se da odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Broja u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.s jer ima nekoliko dana podataka ili Ken ima praznu ćeliju, praznu ćeliju umjesto 0. I da, trebali bismo to proći i popraviti, ali to su Kenovi podaci. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga zbog kojeg ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, pobrinut ću se da odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Broja u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.s podacima. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga koji ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, obavezno odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.s podacima. Podaci su vrijedni 20 godina. Neću proći ni kroz upotrebu Pronađi i zamijeni. U redu, samo sam … Iz bilo kojeg razloga zbog kojeg ću poštivati da Ken ima razloga da ih ima, kao da ću im dopustiti da ostanu prazni. I ovdje, pod Brojem kiše, obavezno odaberem ćeliju u stupcu Brojanje kiše, idem u Postavke polja i promijenim to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.Svakako ću odabrati ćeliju u stupcu Brojanje kiše, otići na Postavke polja i promijeniti to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.Svakako ću odabrati ćeliju u stupcu Brojanje kiše, otići na Postavke polja i promijeniti to iz Brojanje u Zbroj, u redu? Dakle, postoje sve naše godine i koliko smo kiše imali svake godine. I tražimo godine s najmanje kiše.
U redu, jedna stvar koja me muči je ova riječ ovdje Row Labels. To nam se počelo događati u programu Excel 2007, u redu? I ja - 10 godina kasnije to još uvijek prezirem. Idem na karticu Dizajn, otvorim Izgled izvješća i kažem Prikaži u tabličnom obliku i sve to. U ovom je konkretnom slučaju stvarno krenuti tamo godine, zar ne? I više volim pravi smjer. Trenutno želimo vidjeti samo vrh ili, u ovom slučaju, Godine s najnižom kišom. Pa ću sortirati ove podatke prema porastu. Sada postoje dva načina za to. Možete otvoriti ovaj padajući izbornik, otići na Više opcija sortiranja, odabrati Na slanje na temelju zbroja kiše, ali moguće je i samo doći ovdje u Podaci, A do Ž da biste sortirali stvari od najnižeg do najvišeg. Ali ne želim vidjeti samo prvih 5 godina, tako da su godine s najnižom kišom,Dolazim ovdje prema naslovu Godina, otvorim ovaj mali padajući meni i odaberem Filteri vrijednosti. I ja sam u potrazi za Bottom 5. Pa, ne postoji filter za Bottom 5. Ahh, ali ovaj za prvih deset nevjerojatno je moćan. U redu, ne mora biti vrh. Može biti gornja ili donja. Ne mora biti 10; može biti 5. Dakle, zatražite Top 5 stavki na temelju zbroja kiše, kliknite U redu. I tu je naše izvješće.
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.
Pa, hej, želim zahvaliti Kenu što je poslao to pitanje. Želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2063.xlsm
