Wat zijn de onderste vijf maanden regen? Leer hoe u dit probleem kunt oplossen met behulp van een draaitabel.
Bekijk video
- Draaitabellen die in 2013 zijn gemaakt, kunnen in 2007 niet worden vernieuwd
- U moet de draaitabel in 2007 maken om deze te vernieuwen
- Het doel is om de vijf maanden met de minste regenval te vinden
- Maak een grote draaitabel met neerslag per maand
- Sorteer op stijgende regenval
- Verander naar tabelvorm
- Gebruik de waardefilters, Top 10, om de onderste 5 te krijgen!
- Verwijder de rij met het eindtotaal
- Houd er rekening mee dat een gelijkspel ertoe kan leiden dat dit rapport u 6 of meer rijen geeft
- Zodra u de eerste draaitabel heeft, kopieert u deze op zijn plaats en maakt u de volgende draaitabel
- Wanneer u van het ene waardeveld naar het andere verandert, moet u het sorteren en filteren opnieuw uitvoeren
- Wanneer u van het ene rijveld naar het andere wisselt, moet u het sorteren en filteren opnieuw uitvoeren
- Bonustip: maak een draaitabel met rijen en kolommen
Videotranscriptie
Leer Excel van Podcast, aflevering 2063: de bovenste of onderste vijf maanden of jaren met behulp van een draaitabel.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag is ingediend door Ken. Ken heeft hier een geweldige spreadsheet met jaren en jaren en jaren van dagelijkse regenvaldata, die teruggaan tot 1999. Een werkelijk indrukwekkende verzameling gegevens die hij heeft, en Ken had een aantal fantastische formules om te proberen de maand met de meeste regenval te vinden, op minste regenval. Dus nu, weet je, dit wordt een stuk eenvoudiger met een draaitabel.
Oké, Ken heeft nooit een draaitabel gemaakt en om de zaken nog ingewikkelder te maken, ben ik hier in Excel 2016, Ken gebruikt Excel 2007. Mijn draaitabellen die ik in 2016 heb gemaakt, hij kon hem zien, maar hij kon ze niet vernieuwen. Oké, dus deze video is Draaitabel 101: hoe u uw eerste draaitabel maakt.
Ten eerste, Ken heeft deze datum in kolom A, echte datums, gaat het goed? Dat is geweldig, toch? En dan gebruik ik - voeg hier een paar extra formules in bij de = YEAR-functie om het jaar te krijgen, = MAAND-functie om de maand te krijgen, = DAY-functie. En voeg die vervolgens weer samen, ik heb eigenlijk de functie = TEXT in JJJJ-MM gebruikt, op die manier heb ik jaar en maand naar beneden. Dit zijn de gegevens van Ken, de regengegevens hier en toen heb ik wat formules toegevoegd. Ken's heeft iets minder dan 0,5 millimeter, telt niet als een regendag, dus er is een formule. En ga dan, vanaf aflevering 735, terug en kijk daar eens naar om te zien hoe ik de reeks dagen met regen en de reeks dagen zonder regen heb berekend. Nu dat zal vandaag niet worden gebruikt, dat werd voor iets anders gebruikt.
Dus we komen hier. En eerst willen we de gegevens voor onze draaitabel selecteren. Nu zou je in de meeste gevallen gewoon alle gegevens kunnen selecteren, zodat je hier gewoon één cel kunt kiezen, maar in dit geval is er een naambereik dat de gegevens definieert tot, in dit geval, 2016. We zitten hier- ik ' Ik leg dit begin 2017 vast. De gegevens van Ken gaan pas tot eind 2016. Dus we gaan alleen die gegevens selecteren. En dan op het tabblad Invoegen - tabblad Invoegen. Excel 2007, het is de eerste keer dat de draaitabellen van het tabblad Gegevens terug naar het tabblad Invoegen gaan. Dus kiezen we: Draaitabel, en onze geselecteerde gegevens worden de gegevens waaruit we bouwen. En we willen niet naar een nieuw werkblad gaan, we gaan naar een bestaand werkblad en ik ga dat hier in de kolom plaatsen - laten we gaan met de kolom N.Nu wil ik uiteindelijk dat deze gegevens Jaren met de laagste regenval hier verschijnen, maar ik weet dat terwijl ik deze draaitabel aan het bouwen ben, er veel meer rijen nodig zijn dan die 5, toch? Dus ik bouw het hier opzij, oké. En we klikken op OK.
Oké, dit is wat je krijgt. Dit is waar het rapport naartoe gaat en hier is een lijst met alle velden die we in onze kleine dataset hebben. En dan hebben we, want wat ik vreselijk benoemde afvallers. Rijen zijn de items die u aan de linkerkant wilt hebben. Waarden is het ding dat u wilt samenvatten en vervolgens zijn kolommen de dingen die u bovenaan wilt. We kunnen dit op het einde gebruiken. We gaan vandaag geen filters gebruiken. We bouwen dus gewoon een eenvoudige kleine draaitabel met de totale regenval per jaar, dus ik neem het veld Jaar en sleep het hier naar de linkerkant. Er is een lijst van al onze jaren, oké? En denk er dan over na. Wat zou u doen om deze formule hier te krijgen zonder een draaitabel? SUMIF, oh ja, SUMIF. Je zou zelfs SUMIF's terug kunnen gebruiken in Excel 2007. Dus,Ik ga het regenveld nemen en dat hierheen slepen. Kijk nu uit voor de - Kijk, ze kozen voor Count of Rain, dat komt omdat er een paar dagen in de gegevens zitten of Ken heeft een lege cel, een lege cel in plaats van een 0. En ja, we zouden dat moeten oplossen, maar het zijn de gegevens van Ken. Het is 20 jaar aan gegevens. Ik ga niet door, zelfs niet met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.Kijk nu uit voor de - Kijk, ze kozen voor Count of Rain, dat komt omdat er een paar dagen in de gegevens zitten of Ken heeft een lege cel, een lege cel in plaats van een 0. En ja, we zouden dat moeten oplossen, maar het zijn de gegevens van Ken. Het is 20 jaar aan gegevens. Ik ga het zelfs niet doornemen met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we zoeken naar de jaren met de minste regenval.Kijk nu uit voor de - Kijk, ze kozen voor Count of Rain, dat komt omdat er een paar dagen in de gegevens zitten of Ken heeft een lege cel, een lege cel in plaats van een 0. En ja, we zouden dat moeten oplossen, maar het zijn de gegevens van Ken. Het is 20 jaar aan gegevens. Ik ga niet door, zelfs niet met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we zoeken naar de jaren met de minste regenval.s omdat er een paar dagen in de gegevens zitten of Ken een lege cel heeft, een lege cel in plaats van een 0. En ja, we zouden dat moeten doornemen en repareren, maar het zijn de gegevens van Ken. Het is 20 jaar aan gegevens. Ik ga niet door, zelfs niet met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.s omdat er een paar dagen in de gegevens zitten of Ken een lege cel heeft, een lege cel in plaats van een 0. En ja, we moeten dat doornemen en repareren, maar het zijn de gegevens van Ken. Het is 20 jaar aan gegevens. Ik ga niet door, zelfs niet met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.s gegevens. Het is 20 jaar aan gegevens. Ik ga het zelfs niet doornemen met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.s gegevens. Het is 20 jaar aan gegevens. Ik ga het zelfs niet doornemen met Zoeken en vervangen. Oké, ik ben gewoon … Om welke reden dan ook respecteer ik dat Ken een reden heeft om die te hebben, alsof ik ze blanco laat blijven. En hier, onder Count of Rain, ga ik ervoor zorgen dat ik een cel kies in de Count of Rain-kolom, ga naar Field Settings en verander dat van Count in Sum, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.Ik ga ervoor zorgen dat ik een cel kies in de kolom Telling van regen, ga naar Veldinstellingen en verander dat van Tellen in Som, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.Ik ga ervoor zorgen dat ik een cel kies in de kolom Telling van regen, ga naar Veldinstellingen en verander dat van Tellen in Som, oké? Dus er zijn al onze jaren en hoeveel regen we elk jaar hebben gehad. En we kijken naar de jaren met de minste regenval.
Oké, een ding dat me irriteert, is dit woord hier Rijlabels. Dat overkwam ons in Excel 2007, oké? En ik … 10 jaar later veracht ik dat nog steeds. Ik ga naar het Ontwerptabblad, open Rapportlay-out en zeg Weergeven in tabelvorm, en dat alles. In dit specifieke geval is het een echte titel van het jaar, toch? En ik geef de voorkeur aan de echte koers. Op dit moment willen we alleen de top zien, of in dit geval de jaren met de laagste regenval. Dus ik ga deze gegevens oplopend sorteren. Nu zijn er twee manieren om dit te doen. Je zou deze vervolgkeuzelijst kunnen openen, naar Meer sorteeropties gaan, Verzenden kiezen op basis van de som van de regen, maar het is ook mogelijk om gewoon hier in Gegevens, A tot Z te komen om dingen te sorteren van laag naar hoog. Maar ik wil niet alleen de top 5 jaar zien, dus de jaren met de laagste regenval,Ik kom hier naar de kop Jaar, open deze kleine vervolgkeuzelijst en kies Waardefilters. En ik ben op zoek naar Bottom 5. Nou, er is geen filter voor Bottom 5. Ahh, maar deze voor de top tien is ongelooflijk krachtig. Oké, het hoeft niet top te zijn. Het kan boven of onder zijn. Het hoeft geen 10 te zijn; het kan 5 zijn. Vraag dus naar de Top 5-items op basis van de som van regen, klik op OK. En daar is ons 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.
Nou, hé, ik wil Ken bedanken voor het insturen van die vraag. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2063.xlsm
