Hvilke er de nederste fem måneders nedbør? Lær hvordan du løser dette problem ved hjælp af en pivottabel.
Se video
- Pivottabeller oprettet i 2013 kan ikke opdateres i 2007
- Du er nødt til at oprette pivottabellen i 2007, så den kan opdateres
- Målet er at finde de fem måneder med mindst mulig nedbør
- Opret et stort pivottabel med nedbør pr. Måned
- Sorter efter stigende nedbør
- Skift til tabelform
- Brug Value Filters, Top 10, for at få bunden 5!
- Fjern rækken Grand Total
- Bemærk, at uafgjort kan få denne rapport til at give dig 6 eller flere rækker
- Når du har den første pivottabel, skal du kopiere den på plads og oprette den næste pivottabel
- Når du skifter fra et værdifelt til et andet, skal du genudføre sorteringen og filtrere
- Når du skifter fra et rækkefelt til et andet, skal du genudføre sorteringen og filtrere
- Bonustip: Oprettelse af en pivottabel med rækker og kolonner
Videoudskrift
Lær Excel fra Podcast, afsnit 2063: Top eller bund fem måneder eller år ved hjælp af en pivottabel.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål sendt af Ken. Ken har et fantastisk regneark her med år og år og år med daglige nedbørsdatoer, der går tilbage til 1999. En virkelig imponerende samling af data, som han har, og Ken havde nogle fantastiske formler til at prøve at finde den måned med mest nedbør, kl. mindst nedbør. Så nu ved du, dette bliver meget lettere med et drejetabell.
Okay, Ken oprettede aldrig en pivottabel, og for yderligere at komplicere tingene er jeg her i Excel 2016, Ken bruger Excel 2007. Mine pivottabeller, som jeg oprettede i 2016, kunne se ham, men han kunne ikke opdatere dem. Okay, så denne video er pivottabel 101: Sådan oprettes din første pivottabel.
For det første har Ken denne dato i kolonne A, rigtige datoer, er vi gode? Det er fantastisk, ikke? Og så bruger jeg - indsæt et par ekstra formler her ved = YEAR-funktionen for at få året, = MÅNED-funktionen for at få måneden, = DAY-funktionen. Og sammenkæd derefter dem sammen igen, jeg brugte faktisk funktionen = TEKST i ÅÅÅÅ-MM, på den måde har jeg år og måned nede. Dette er Kens data, regndataene her og så tilføjede jeg nogle formler. Ken's har noget mindre end 0,5 millimeter, tæller ikke med som en regndag, så der er en formel der. Og så, fra Episode 735, gå tilbage og kig på det for at se, hvordan jeg beregnede streg af dage med regn og streg af dage uden regn. Nu skal det ikke bruges i dag, det blev brugt til noget andet.
Så vi kommer her. Og først vil vi vælge dataene til vores pivottabel. Nu kan du i de fleste tilfælde bare vælge alle data, så du kan bare vælge en celle her, men i dette tilfælde er der et navneområde, der definerer dataene lige igennem, i dette tilfælde 2016. Vi sidder her - jeg ' m optager dette i begyndelsen af 2017. Kens data går først gennem udgangen af 2016. Så vi vælger netop disse data. Og derefter på fanen Indsæt - Indsæt fanen. Excel 2007, det er første gang, at pivottabellerne flytter fra fanen Data tilbage til fanen Indsæt. Så vi vælger: Pivottabel, og vores valgte data bliver de data, som vi bygger ud fra. Og vi vil ikke gå til nyt regneark, vi går til et eksisterende regneark, og jeg vil lægge det lige her i kolonne - lad os gå med kolonne N.Nu i sidste ende ønsker jeg, at disse data År med lavest nedbør skal vises lige her, men jeg ved, at når jeg bygger dette drejebord, vil det have brug for meget flere rækker end de 5, ikke? Så jeg bygger det op til siden her, okay. Og vi klikker OK.
Okay, her er hvad du får. Det er her rapporten kommer hen, og her er en liste over alle de felter, vi har i vores lille datasæt. Og så er vi, for hvad jeg kalder forfærdeligt navngivet, falder ud. Rækker er de emner, du vil have på venstre side. Værdier er den ting, du vil opsummere, og derefter er kolonner de ting, du vil have øverst. Vi bruger muligvis dette i slutningen. Vi bruger ikke filtre i dag. Så vi bygger bare et simpelt lille drejetabel med samlet nedbør pr. År, så jeg tager feltet År og trækker det ned her til venstre side. Der er en liste over alle vores år, okay? Og så tænk over det. For at få denne formel her uden en pivottabel, gør du hvad? SUMIF, åh ja, SUMIF. Du kan endda bruge SUMIFs tilbage i Excel 2007. SåJeg tager feltet Rain og trækker det her. Lige nu pas på - Se, de valgte Count of Rain, det er fordi der er et par dage i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi skal gå igennem og rette det, men det er Kens data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain vil jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter de år med lavest nedbør.Lige nu pas på - Se, de valgte Count of Rain, det er fordi der er et par dage i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi skal gå igennem og rette det, men det er Kens data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain skal jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter de år med lavest nedbør.Lige nu pas på - Se, de valgte Count of Rain, det er fordi der er et par dage i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi skal gå igennem og rette det, men det er Kens data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain skal jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter årene med den laveste nedbør.s fordi der er et par dage i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi skulle gå igennem og rette det, men det er Ken's data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain skal jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter årene med den laveste nedbør.s fordi der er et par dage i dataene, eller Ken har en tom celle, en tom celle i stedet for en 0. Og ja, vi skulle gå igennem og rette det, men det er Ken's data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain skal jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter årene med den laveste nedbør.s data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain skal jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter årene med den laveste nedbør.s data. Det er 20 års data værd. Jeg vil ikke gå igennem, selv ved hjælp af Find & Replace. Okay, jeg er bare - Af en eller anden grund vil jeg respektere, at Ken har en grund til at have dem, som om jeg vil lade dem forblive tomme. Og her under Count of Rain skal jeg sørge for at vælge en celle i Count of Rain-kolonnen, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter årene med den laveste nedbør.Jeg skal sørge for at vælge en celle i kolonnen Count of Rain, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter de år med lavest nedbør.Jeg vil sørge for at vælge en celle i kolonnen Count of Rain, gå til feltindstillinger og ændre det fra Count til Sum, okay? Så der er alle vores år, og hvor meget regn vi havde hvert år. Og vi leder efter årene med den laveste nedbør.
Okay, en ting, der forstyrrer mig, er dette ord her. Det begyndte at ske for os i Excel 2007, okay? Og jeg - 10 år senere forakter jeg det stadig. Jeg går til fanen Design, åbner rapportlayout og siger Vis i tabelform, og alt det der gør. I dette særlige tilfælde er få en rigtig retning der af året, ikke? Og jeg foretrækker den rigtige overskrift. Lige nu vil vi bare se toppen eller i dette tilfælde de år med den laveste nedbør. Så jeg vil sortere disse data stigende. Nu er der to måder at gøre dette på. Du kan åbne denne rullemenu, gå til Flere sorteringsindstillinger, vælge Til afsendelse baseret på summen af regn, men det er også muligt bare at komme her ind i Data, A til Z for at få tingene sorteret fra laveste til højeste. Men jeg vil ikke se bare de fem bedste år, så årene med den laveste nedbør,Jeg kommer her til overskriften År, åbner denne lille rullemenu og vælger Værdifiltre. Og jeg leder efter bund 5. Nå, der er ikke noget filter til bund 5. Ahh, men denne til top ti er utrolig kraftig. Okay, det behøver ikke at være top. Det kan være top eller bund. Det behøver ikke at være 10; det kan være 5. Så bed om de 5 bedste varer baseret på summen af regn, klik på OK. Og der er vores 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.
Nå, hej, jeg vil takke Ken for at sende det spørgsmål. Jeg vil takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2063.xlsm
