Mitkä ovat viimeiset viisi sademäärää? Opi ratkaisemaan tämä ongelma pivot-taulukon avulla.
Katso video
- Vuonna 2013 luotuja pivot-taulukoita ei voi päivittää vuonna 2007
- Sinun on luotava pivot-taulukko vuonna 2007, jotta se voidaan päivittää
- Tavoitteena on löytää viisi kuukautta, joissa on vähiten sateita
- Luo suuri pivot-taulukko, jossa on sateita kuukausittain
- Lajittele sateiden mukaan nousevasti
- Vaihda taulukkomuotoon
- Käytä arvosuodattimia, Top 10, saadaksesi viimeisen 5!
- Poista loppusumma
- Huomaa, että tasapeli voi aiheuttaa tämän raportin antavan sinulle 6 tai enemmän rivejä
- Kun sinulla on ensimmäinen kääntötaulukko, kopioi se paikalleen ja luo seuraava kääntötaulukko
- Kun vaihdat arvokentästä toiseen, sinun on tehtävä lajittelu ja suodatus uudelleen
- Kun vaihdat rivikentästä toiseen, sinun on tehtävä lajittelu ja suodatus uudelleen
- Bonusvinkki: pivot-taulukon luominen riveillä ja sarakkeilla
Videon transkriptio
Opi Excel Podcastista, jaksosta 2063: Viisi kuukautta tai vuotta alkuun tai alareunaan pivot-taulukon avulla.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Kenin tämän päivän kysymys. Kenillä on täällä hämmästyttävä laskentataulukko, jossa on vuosia ja vuosia päivittäisiä sademääriä, palaten vuoteen 1999. Todella vaikuttava kokoelma tietoja, joita hänellä on, ja Kenillä oli upeita kaavoja yrittää löytää kuukausi, jossa satoi eniten vähiten sateita. Joten nyt, tiedät, tämä on paljon helpompaa kääntöpöydän kanssa.
Selvä, Ken ei ole koskaan luonut pivot-taulukkoa ja monimutkaistaa asioita entisestään. Olen täällä Excel 2016: ssa, Ken käyttää Excel 2007: tä. Luomani pivot-taulukot, jotka luin vuonna 2016, hän näki hänet, mutta ei voinut päivittää niitä. Selvä, joten tämä video on pivot-taulukko 101: Kuinka luoda ensimmäinen pivot-taulukko.
Ensinnäkin Kenillä on tämä päivämäärä sarakkeessa A, todelliset päivämäärät, olemmeko hyviä? Se on mahtavaa, eikö? Ja sitten käytän- lisää muutama ylimääräinen kaava tähän = VUOSI-funktioon saadaksesi vuosi, = KUUKAUS-funktio saadaksesi kuukauden, = PÄIVÄ-funktio. Ja sitten liittää ne takaisin yhteen, käytin itse asiassa = TEXT-funktiota VVVV-KK, jolloin minulla on vuosi ja kuukausi alas. Nämä ovat Kenin tiedot, sadetiedot täällä ja sitten lisäsin joitain kaavoja. Kenillä on alle 0,5 millimetriä, sitä ei lasketa sadepäiväksi, joten siellä on kaava. Ja palaa sitten jaksosta 735 takaisin ja katsokaa sitä nähdäksesi, kuinka laskin päivän sateen ja sateettoman päivän. Nyt sitä ei käytetä tänään, sitä käytettiin johonkin muuhun.
Joten tulemme tänne. Ensin haluamme valita pivot-taulukon tiedot. Nyt useimmissa tapauksissa voit vain valita kaikki tiedot, jotta voit valita vain yhden solun, mutta tässä tapauksessa on olemassa nimialue, joka määrittelee tiedot vain läpi, tässä tapauksessa vuonna 2016. Istumme täällä - minä Tallennan tämän vuoden 2017 alussa. Kenin tiedot menevät vasta vuoden 2016 loppuun. Valitsemme juuri nämä tiedot. Ja sitten Lisää-välilehti- Lisää-välilehti. Excel 2007, se on ensimmäinen kerta, kun pivot-taulukot siirtyvät Tiedot-välilehdeltä takaisin Lisää-välilehteen. Joten valitsemme: Pivot-taulukon, ja valitut tietomme ovat tietoja, joista rakennamme. Ja emme halua siirtyä uudelle laskentataulukolle, menemme olemassa olevaan laskentataulukkoon ja aion laittaa sen suoraan tähän sarakkeeseen - mennään Sarake N: n kanssa.Nyt haluan lopulta, että nämä vuodet, joissa sateet ovat alhaisimmat, näkyvät täällä, mutta tiedän, että kun rakennan tätä kääntötaulukkoa, se tarvitsee paljon enemmän rivejä kuin nämä 5, eikö? Joten rakennan sen sivulle, okei. Ja napsautamme OK.
Selvä, nyt saat mitä saat. Täällä raportti menee, ja tässä on luettelo kaikista kentistä, jotka meillä on pienessä tietojoukossa. Ja sitten meillä on, koska mitä kutsun kauhistuttavaksi nimeksi, pudottaa. Rivit on haluamasi kohteet vasemmalla puolella. Arvot on asia, jonka haluat tiivistää, ja sitten sarakkeet ovat asioita, joita haluat ylhäältä. Voisimme käyttää tätä lopussa. Emme aio käyttää suodattimia tänään. Joten rakennamme vain yksinkertaisen pienen kääntöpöydän, jossa sademäärä on vuodessa, joten otan Vuosi-kentän ja vedän sen alas vasemmalle puolelle. Siellä on luettelo kaikista vuosistamme, okei? Ja sitten, ajattele sitä. Jos haluat saada tämän kaavan täältä ilman kääntötaulukkoa, tekisit mitä? SUMIF, oi joo, SUMIF. Voit jopa käyttää SUMIFin takaosaa Excel 2007: ssä.Otan Sadekentän ja vedän sen tänne. Ole varovainen - Katso, he valitsivat sademäärän, koska tietoissa on muutama päivä tai Kenillä on tyhjä solu, tyhjä solu nollan sijasta. Ja joo, meidän pitäisi käydä läpi ja korjata se, mutta ne ovat Kenin tietoja. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.Ole varovainen - Katso, he valitsivat sademäärän, johtuu siitä, että tiedoissa on muutama päivä tai Kenillä on tyhjä solu, tyhjä solu nollan sijasta. Ja joo, meidän pitäisi käydä läpi ja korjata se, mutta ne ovat Kenin tietoja. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.Ole varovainen - Katso, he valitsivat sademäärän, johtuu siitä, että tiedoissa on muutama päivä tai Kenillä on tyhjä solu, tyhjä solu nollan sijasta. Ja joo, meidän pitäisi käydä läpi ja korjata se, mutta ne ovat Kenin tietoja. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Etsimme vuosia, joissa sateet ovat pienimmät.s koska tiedoissa on muutama päivä tai Kenillä on tyhjä solu, tyhjä solu nollan sijasta. Ja joo, meidän pitäisi käydä läpi ja korjata se, mutta Kenin tiedot. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.s koska tiedoissa on muutama päivä tai Kenillä on tyhjä solu, tyhjä solu nollan sijasta. Ja joo, meidän pitäisi käydä läpi ja korjata se, mutta Kenin tiedot. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.s tiedot. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Etsimme vuosia, joissa sateet ovat pienimmät.s tiedot. Se on 20 vuoden arvoinen data. En aio käydä läpi edes Etsi ja korvaa -toimintoa. Selvä, olen vain - jostain syystä aion kunnioittaa sitä, että Kenillä on syytä olla sellaisia, kuten aion antaa heidän pysyä tyhjinä. Ja täällä, sateenlaskennan alla, aion varmasti valita solun Sateen määrä -sarakkeesta, mennä kenttäasetuksiin ja vaihtaa sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.Aion varmistaa, että valitsen solun Sateen määrä -sarakkeesta, siirry kenttäasetuksiin ja vaihdan sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.Aion varmistaa, että valitsen solun Sateen määrä -sarakkeesta, siirry kenttäasetuksiin ja vaihdan sen lukumäärästä summaan, okei? Joten on kaikki vuodemme ja kuinka paljon sadetta meillä oli joka vuosi. Ja etsimme vuosia, joissa sateet ovat pienimmät.
Hyvä on, yksi asia, joka vikaa minua, on tämä sana täällä Rivitarrat. Se alkoi tapahtua meille Excel 2007: ssä, okei? Ja minä - 10 vuotta myöhemmin halveksin sitä edelleen. Menen Suunnittelu-välilehteen, avaan Raportin asettelu ja sanon Näytä taulukkomuodossa, ja kaikki tämä tapahtuu. Tässä tapauksessa saat todellisen otsikon sinne vuoden, eikö? Ja pidän paremmasta todellisesta otsikosta. Tällä hetkellä haluamme nähdä vain huipun tai tässä tapauksessa vuodet, joissa sateet ovat pienimmät. Joten aion lajitella nämä tiedot nousevasti. Nyt on kaksi tapaa tehdä tämä. Voit avata tämän avattavan valikon, siirtyä Lisää lajitteluvaihtoehtoja -kohtaan, valita Lähettäminen sademäärän perusteella, mutta on myös mahdollista tulla tänne Data-, A – Z-tiloihin saadaksesi asiat järjestykseen alimmasta korkeimpaan. Mutta en halua nähdä vain viittä parasta vuotta, joten vähiten sateiset vuodet,Tulen tänne Vuosi-otsikkoon, avaan tämän pienen avattavan valikon ja valitsen Arvosuodattimet. Ja etsin Bottom 5. No, Bottom 5: lle ei ole suodatinta. Ahh, mutta tämä kymmenen parhaan joukossa on uskomattoman voimakas. Selvä, sen ei tarvitse olla huippua. Se voi olla ylä- tai alaosa. Sen ei tarvitse olla 10; se voi olla 5. Joten kysy 5 parasta kohdetta sademäärän perusteella, napsauta OK. Ja siellä on raporttimme.
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.
Hei, haluan kiittää Keniä tämän kysymyksen lähettämisestä. Haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2063.xlsm
