Hvilke er de fem nederste månedene med nedbør? Lær hvordan du løser dette problemet ved hjelp av en pivottabell.
Se på video
- Pivottabeller opprettet i 2013 kan ikke oppdateres i 2007
- Du må opprette pivottabellen i 2007 for å la den være forfriskbar
- Målet er å finne de fem månedene med minst nedbør
- Lag et stort pivottabell med nedbør etter måned
- Sorter etter stigende nedbør
- Bytt til tabellform
- Bruk verdifiltrene, topp 10, for å få bunnen 5!
- Fjern raden Grand Total
- Merk at uavgjort kan føre til at denne rapporten gir deg 6 eller flere rader
- Når du har den første pivottabellen, kopier den på plass og opprett neste pivottabell
- Når du bytter fra ett verdifelt til et annet, må du gjøre sorteringen og filtrere på nytt
- Når du bytter fra ett radfelt til et annet, må du gjøre sorteringen og filtrere på nytt
- Bonustips: lage et pivottabell med rader og kolonner
Videoutskrift
Lær Excel fra Podcast, episode 2063: Toppen eller bunnen av fem måneder eller år ved hjelp av et pivottabell.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål sendt inn av Ken. Ken har et fantastisk regneark her med år og år og år med daglige nedbørsdatoer, helt tilbake til 1999. En virkelig imponerende samling av data som han har, og Ken hadde noen fantastiske formler for å prøve å finne den måneden med mest nedbør, kl. minst nedbør. Så nå vet du, dette blir mye lettere med et pivottabell.
Greit nå, Ken har aldri opprettet en pivottabell, og for å komplisere tingene ytterligere, er jeg her i Excel 2016, Ken bruker Excel 2007. Pivottabellene mine som jeg opprettet i 2016, han kunne se ham, men han kunne ikke oppdatere dem. OK, så denne videoen er pivottabell 101: Hvordan lage din første pivottabell.
For det første har Ken denne datoen i kolonne A, ekte datoer, er vi gode? Det er kjempebra, ikke sant? Og så bruker jeg - sett inn et par ekstra formler her på = YEAR-funksjonen for å få året, = MONTH-funksjonen for å få måneden, = DAY-funksjonen. Og sammenkoblet de sammen, jeg brukte faktisk = TEKST-funksjonen i ÅÅÅÅ-MM, på den måten har jeg år og måned nede. Dette er Ken's data, regndataene her og så la jeg til noen formler. Ken's har noe mindre enn 0,5 millimeter, regnes ikke som en regndag, så det er en formel der. Og så, fra Episode 735, gå tilbake og ta en titt på det for å se hvordan jeg beregnet strek av dager med regn og strek av dager uten regn. Nå skal det ikke brukes i dag, det ble brukt til noe annet.
Så, vi kommer hit. Og først vil vi velge data for pivottabellen. Nå, i de fleste tilfeller, kan du bare velge alle data, så du kan bare velge en celle her, men i dette tilfellet er det et navneområde som definerer dataene bare gjennom, i dette tilfellet, 2016. Vi sitter her - jeg ' m registrerer dette i begynnelsen av 2017. Kens data går bare ut 2016, så vi skal velge akkurat de dataene. Og så på Sett inn-fanen - Sett inn-fanen. Excel 2007, det er første gang pivottabellene flytter fra Data-fanen tilbake til Sett inn-fanen. Så vi velger: Pivottabell, og de valgte dataene våre blir dataene vi bygger fra. Og vi vil ikke gå til et nytt regneark, vi skal gå til et eksisterende regneark, og jeg skal legge det rett her i kolonne - la oss gå med kolonne N.Nå til slutt vil jeg at disse dataene År med lavest nedbør skal vises her, men jeg vet at når jeg bygger dette pivottabellen, vil det trenge mye flere rader enn de 5, ikke sant? Så jeg bygger den til siden her, ok. Og vi klikker OK.
OK, nå er det du får. Det er her rapporten skal gå, og her er en liste over alle feltene vi har i vårt lille datasett. Og så har vi det, for det jeg kaller forferdelig kalt faller ut. Rader er elementene du vil ha på venstre side. Verdier er tingen du vil oppsummere, og deretter er kolonner de tingene du vil ha på toppen. Vi kan bruke dette på slutten. Vi skal ikke bruke filtre i dag. Så vi bygger bare et enkelt, lite pivottabell med total nedbør etter år, så jeg tar Year-feltet og drar det ned hit til venstre. Det er en liste over alle årene våre, ok? Og så tenk på det. For å få denne formelen her uten et pivottabell, gjør du hva? SUMIF, oh yeah, SUMIF. Du kan til og med bruke SUMIFs tilbake i Excel 2007. Så,Jeg skal ta Rain-feltet og dra det hit. Akkurat nå se opp for - Se, de valgte Count of Rain, det er fordi det er noen dager i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi burde gå gjennom og fikse det, men det er Kens data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett av hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til Field Settings, og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.Akkurat nå se opp for - Se, de valgte Count of Rain, det er fordi det er noen dager i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi burde gå gjennom og fikse det, men det er Kens data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.Akkurat nå se opp for - Se, de valgte Count of Rain, det er fordi det er noen dager i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi burde gå gjennom og fikse det, men det er Kens data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.s fordi det er noen dager i dataene eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi burde gå igjennom og fikse det, men det er Kens data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.s fordi det er noen dager i dataene eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi burde gå igjennom og fikse det, men det er Kens data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til feltinnstillinger, og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.s data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett av hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.s data. Det er 20 år med data. Jeg kommer ikke til å gå gjennom engang ved å bruke Finn og erstatt. Ok, jeg er bare - Uansett hvilken grunn jeg vil respektere at Ken har en grunn til å ha dem, som om jeg skal la dem være tomme. Og her, under Count of Rain, skal jeg sørge for å velge en celle i Count of Rain-kolonnen, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.Jeg skal sørge for å velge en celle i kolonnen Count of Rain, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.Jeg skal sørge for å velge en celle i kolonnen Count of Rain, gå til feltinnstillinger og endre det fra Count to Sum, ok? Så det er alle årene vi har og hvor mye regn vi hadde hvert år. Og vi ser etter årene med lavest nedbør.
Greit nå, en ting som forstyrrer meg er dette ordet her. Det begynte å skje med oss i Excel 2007, ok? Og jeg - 10 år senere forakter jeg det fortsatt. Jeg går til Design-fanen, åpner rapportoppsett og sier Vis i tabellform, og alt som gjør. I dette spesielle tilfellet er det å få en skikkelig kurs dit av året, ikke sant? Og jeg foretrekker den virkelige overskriften. Akkurat nå vil vi se bare toppen eller i dette tilfellet årene med lavest nedbør. Så jeg skal sortere disse dataene stigende. Nå er det to måter å gjøre dette på. Du kan åpne denne rullegardinmenyen, gå til Flere sorteringsalternativer, velge Å sende basert på summen av regn, men det er også mulig bare å komme hit til Data, A til Z for å få ting sortert fra laveste til høyeste. Men jeg vil ikke se bare de fem beste årene, så årene med lavest nedbør,Jeg kommer hit til overskriften År, åpner denne lille rullegardinmenyen og velger Verdifilter. Og jeg ser etter Bunn 5. Vel, det er ikke noe filter for Bunn 5. Ahh, men denne for topp ti er utrolig kraftig. Ok, det trenger ikke å være topp. Det kan være topp eller bunn. Det trenger ikke være 10; det kan være 5. Så be om de fem beste elementene basert på summen av regn, klikk OK. Og det er vår rapport.
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.
Vel, hei, jeg vil takke Ken for at du sendte det spørsmålet inn. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2063.xlsm
