Vilka är de nedersta nederbördens fem månader? Lär dig hur du löser detta problem med en pivottabell.
Kolla på video
- Pivottabeller som skapades 2013 kan inte uppdateras 2007
- Du måste skapa pivottabellen 2007 så att den kan uppdateras
- Målet är att hitta de fem månaderna med minst nederbörd
- Skapa ett stort pivottabell med nederbörd per månad
- Sortera efter stigande regn
- Byt till tabellform
- Använd värdefiltret, topp 10, för att få botten 5!
- Ta bort raden Total
- Observera att oavgjort kan leda till att denna rapport ger dig 6 eller fler rader
- När du har den första pivottabellen, kopiera den på plats och skapa nästa pivottabell
- När du byter från ett värdefält till ett annat måste du göra om sorteringen och filtrera
- När du byter från ett radfält till ett annat måste du göra om sorteringen och filtrera
- Bonustips: skapa en pivottabell med rader och kolumner
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2063: Topp eller botten fem månader eller år med hjälp av en pivottabell.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga skickad in av Ken. Ken har ett fantastiskt kalkylblad här med år och år och år med dagliga nederbördsdatum, som går tillbaka till 1999. En riktigt imponerande samling av data som han har, och Ken hade några fantastiska formler för att försöka hitta den månad med mest nederbörd, kl. minst nederbörd. Så nu vet du att det här blir mycket lättare med ett vridbord.
Okej nu, Ken har aldrig skapat en pivottabell och för att ytterligare komplicera saker, jag är här i Excel 2016, Ken använder Excel 2007. Mina pivottabeller som jag skapade 2016, han kunde se honom men han kunde inte uppdatera dem. Okej, så den här videon är pivottabell 101: Hur man skapar din första pivottabell.
Först har Ken detta datum i kolumn A, riktiga datum, är vi bra? Det är fantastiskt, eller hur? Och sedan använder jag - sätt in ett par extra formler här vid = YEAR-funktionen för att få året, = MONTH-funktionen för att få månaden, = DAY-funktionen. Och sedan sammanfoga dem igen, jag använde faktiskt = TEXT-funktionen i ÅÅÅÅ-MM, på det sättet har jag år och månad nere. Det här är Kens data, regndata här och sedan lade jag till några formler. Ken's har något mindre än 0,5 millimeter, räknas inte som en regndag så det finns en formel där. Och sedan från avsnitt 735, gå tillbaka och ta en titt på det för att se hur jag beräknade strimman av dagar med regn och strimmor av dagar utan regn. Nu kommer det inte att användas idag, det användes för något annat.
Så, vi kommer hit. Och först vill vi välja data för vår pivottabell. Nu, i de flesta fall, kan du bara välja all data så att du bara kan välja en cell här men i det här fallet finns det ett namnintervall som definierar data bara genom, i det här fallet, 2016. Vi sitter här- jag ' Jag spelar in detta i början av 2017. Kens data går bara till slutet av 2016. Så vi kommer att välja just den informationen. Och sedan på fliken Infoga - Infoga fliken. Excel 2007 är det första gången pivottabellerna flyttar från fliken Data tillbaka till fliken Infoga. Så vi väljer: Pivottabell, och våra valda data kommer att vara de data som vi bygger från. Och vi vill inte gå till ett nytt kalkylblad, vi ska gå till ett befintligt kalkylblad och jag ska lägga det här i kolumnen - låt oss gå med kolumn N.Nu i slutändan vill jag att de här uppgifterna År med det lägsta nederbörden ska visas här, men jag vet att när jag bygger det här pivottabellen kommer det att behöva mycket fler rader än de 5, eller hur? Så jag bygger det åt sidan här, okej. Och vi klickar på OK.
Okej, nu är det här du får. Det är här rapporten kommer att gå och här är en lista över alla fält vi har i vår lilla datamängd. Och sedan har vi, för det jag kallar hemskt namnet faller ut. Rader är de objekt du vill ha längst ner till vänster. Värden är det du vill sammanfatta och sedan är Kolumner de saker du vill ha överst. Vi kanske använder detta i slutet. Vi ska inte använda filter idag. Så vi bygger bara en enkel liten pivottabell med total nederbörd per år, så jag tar fältet År och drar ner det här till vänster. Det finns en lista över alla våra år, okej? Och tänk sedan på det. För att få denna formel här utan en pivottabell, skulle du göra vad? SUMIF, åh ja, SUMIF. Du kan till och med använda SUMIFs tillbaka i Excel 2007. Så,Jag ska ta regnfältet och dra det hit. Just nu se upp för - Se, de valde Count of Rain, det beror på att det finns några dagar i data eller att Ken har en tom cell, en tom cell istället för en 0. Och ja, vi borde gå igenom och fixa det men det är Kens data. Det är 20 års värde av data. Jag kommer inte att gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha sådana, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till Field Settings och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.Just nu se upp för - Se, de valde Count of Rain, det beror på att det finns några dagar i data eller att Ken har en tom cell, en tom cell istället för en 0. Och ja, vi borde gå igenom och fixa det men det är Kens data. Det är 20 års värde av data. Jag ska inte gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha sådana, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till fältinställningar och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.Just nu se upp för - Se, de valde Count of Rain, det beror på att det finns några dagar i data eller att Ken har en tom cell, en tom cell istället för en 0. Och ja, vi borde gå igenom och fixa det men det är Kens data. Det är 20 års värde av data. Jag ska inte gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha sådana, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till Field Settings och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.s eftersom det finns några dagar i data eller att Ken har en tom cell, en tom cell istället för en 0. Och ja, vi borde gå igenom och fixa det men det är Kens data. Det är 20 års värde av data. Jag ska inte gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha sådana, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till fältinställningar och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.s eftersom det finns några dagar i data eller att Ken har en tom cell, en tom cell istället för en 0. Och ja, vi borde gå igenom och fixa det men det är Kens data. Det är 20 års värde av data. Jag ska inte gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha sådana, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till Field Settings och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.s data. Det är 20 års värde av data. Jag ska inte gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha sådana, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till fältinställningar och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.s data. Det är 20 års värde av data. Jag ska inte gå igenom ens med Find & Replace. Okej, jag är bara - Av vilken anledning som helst kommer jag att respektera att Ken har en anledning att ha dem, som om jag ska tillåta dem att vara tomma. Och här, under Count of Rain, ska jag se till att välja en cell i kolumnen Count of Rain, gå till Field Settings och ändra det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.Jag ska se till att jag väljer en cell i kolumnen Count of Rain, går till fältinställningar och ändrar det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.Jag ska se till att jag väljer en cell i kolumnen Count of Rain, går till fältinställningar och ändrar det från Count to Sum, okej? Så det finns alla våra år och hur mycket regn vi hade varje år. Och vi letar efter åren med lägst nederbörd.
Okej nu, en sak som stör mig är detta ord här Radetiketter. Det började hända oss i Excel 2007, okej? Och jag - 10 år senare föraktar jag det fortfarande. Jag går till fliken Design, öppnar rapportlayout och säger Visa i tabellform och allt som gör. I det här specifika fallet är att få en riktig kurs dit av året, eller hur? Och jag föredrar den riktiga rubriken. Just nu vill vi bara se toppen eller i det här fallet de år som har lägst nederbörd. Så jag ska sortera dessa data stigande. Nu finns det två sätt att göra detta. Du kan öppna den här rullgardinsmenyn, gå till Fler sorteringsalternativ, välj Att skicka baserat på summan av regn, men det är också möjligt att bara komma hit till Data, A till Z för att få saker sorterade från lägsta till högsta. Men jag vill inte bara se de fem bästa åren, så åren med lägst nederbörd,Jag kommer hit till årets rubrik, öppnar den här lilla rullgardinsmenyn och väljer Value Filter. Och jag letar efter botten 5. Det finns inget filter för botten 5. Ahh, men den här för topp tio är otroligt kraftfull. Okej, det behöver inte vara topp. Det kan vara topp eller botten. Det behöver inte vara tio; det kan vara 5. Fråga efter de fem bästa artiklarna baserat på summan av regn, klicka på OK. Och det finns 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.
Tja, hej, jag vill tacka Ken för att ha skickat den frågan. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2063.xlsm
