Kurie yra apatiniai penki lietaus mėnesiai? Sužinokite, kaip išspręsti šią problemą naudojant „pivot“ lentelę.
Žiūrėti video
- 2013 m. Sukurtų „Pivot“ lentelių negalima atnaujinti 2007 m
- Norėdami sukurti atsinaujinančią lentelę, turite sukurti sukamą lentelę 2007 m
- Tikslas - surasti penkis mėnesius, kuriuose būtų mažiausiai kritulių
- Sukurkite didelę sukamą lentelę su krituliais pagal mėnesį
- Rūšiuoti pagal kritulių kiekį didėjant
- Pakeiskite į lentelės formą
- Norėdami gauti 5 žemiausius, naudokite „Vertės filtrus“, 10 geriausių!
- Pašalinkite „Total Total“ eilutę
- Atminkite, kad dėl lygių rezultatų ši ataskaita gali suteikti 6 ar daugiau eilučių
- Kai turėsite pirmąją suvestinę lentelę, nukopijuokite ją į vietą ir sukurkite kitą suvestinę lentelę
- Kai keičiate vieną vertės lauką į kitą, turite iš naujo atlikti rūšiavimą ir filtravimą
- Kai keičiate eilutės lauką į kitą, turite iš naujo atlikti rūšiavimą ir filtravimą
- Premijos patarimas: sukimo lentelės su eilutėmis ir stulpeliais sukūrimas
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2063 epizodas: „Viršutiniai ar apatiniai penki mėnesiai ar metai“, naudojant „Pivot“ lentelę.
Ei, sveiki sugrįžę į internetą, aš Billas Jelenas. Šiandienos klausimą atsiuntė Kenas. Kenas čia turi nuostabią skaičiuoklę su metų ir metų kritulių datomis, pradedant 1999 m.. Tikrai įspūdingas duomenų rinkinys, kurį jis turi, ir Kenas turėjo keletą nuostabių formulių, kad galėtų išbandyti mėnesį, kuriame iškrito daugiausia kritulių. mažiausiai kritulių. Taigi dabar, žinote, tai bus daug lengviau su sukamuoju stalu.
Gerai, Kenas niekada nesukūrė pasukamosios lentelės ir norėdamas dar labiau apsunkinti reikalus, aš esu čia „Excel 2016“, Kenas naudoja „Excel 2007.“. Mano sukamos lentelės, kurias sukūriau 2016 m., Jis matė jį, bet negalėjo jų atnaujinti. Gerai, todėl šis vaizdo įrašas yra „Pivot“ lentelė 101: Kaip sukurti pirmąją sukimosi lentelę.
Pirma, Kenas turi šią datą A stulpelyje, tikros datos, ar mes geri? Tai nuostabu, tiesa? Tada aš naudoju - į = YEAR funkciją įterpkite porą papildomų formulių, kad gautumėte metus, = MONTH funkciją, kad gautumėte mėnesį, = DAY funkciją. Tada susieju tuos atgal, aš iš tikrųjų naudojau = TEXT funkciją YYYY-MM, tokiu būdu turiu metus ir mėnesius. Tai yra Keno duomenys, lietaus duomenys čia ir tada aš pridėjau keletą formulių. Kenas turi mažiau nei .5 milimetrų, nelaikomas lietaus diena, todėl yra formulė. Tada grįžkite iš 735 serijos ir pažiūrėkite, kaip aš apskaičiavau dienų su lietumi ir dienų be lietaus ruožą. Dabar tai nebus naudojama šiandien, ji buvo naudojama kažkam kitam.
Taigi, mes ateiname čia. Pirmiausia norime pasirinkti duomenis suvestinei lentelei. Dabar daugeliu atvejų galite tiesiog pasirinkti visus duomenis, kad galėtumėte tiesiog pasirinkti vieną langelį, bet šiuo atveju yra pavadinimų diapazonas, kuris apibrėžia duomenis tik per, šiuo atveju, 2016 m. Mes sėdime čia - aš m įrašau tai 2017 m. pradžioje. Keno duomenys eina tik iki 2016 m. pabaigos. Taigi, mes pasirinksime tik tuos duomenis. Tada skirtuke Įterpti - Įterpti skirtuką. „Excel 2007“ - tai pirmas kartas, kai suvestinės lentelės iš skirtuko Duomenys grįžta į skirtuką Įterpti. Taigi mes pasirenkame: „Pivot Table“, o mūsų pasirinkti duomenys bus tie, iš kurių mes sukursime. Ir mes nenorime eiti į naują darbalapį, mes eisime prie esamo darbalapio, kurį aš įdėsiu čia, stulpelyje - eikime su N stulpeliu.Dabar galų gale noriu, kad šie duomenys „Metai su mažiausiu kritulių kiekiu“ būtų rodomi čia, bet žinau, kad kuriant šią sukamą lentelę reikės daug daugiau eilučių nei toms 5, tiesa? Taigi, aš čia statau jį į šoną. Ir mes spustelėkite Gerai.
Gerai, dabar štai ką gauni. Čia bus parengta ataskaita ir pateikiamas visų laukų, kuriuos turime mažame duomenų rinkinyje, sąrašas. Ir tada mes turime, nes tai, ką aš vadinu siaubingai pavadintu, iškrenta. Eilutės yra elementai, kuriuos norite kairėje pusėje. Vertybės yra dalykas, kurį norite apibendrinti, o stulpeliai yra tai, ko norite viršuje. Galime tai panaudoti pabaigoje. Šiandien nenaudosime filtrų. Taigi, mes tiesiog statome paprastą mažą sukamą lentelę su bendru kritulių kiekiu kiekvienais metais, todėl aš paimsiu Metų lauką ir vilksiu jį žemyn į kairę pusę. Yra visų mūsų metų sąrašas, gerai? Ir tada pagalvok. Norėdami gauti šią formulę čia be sukamosios lentelės, ką darytumėte? SUMIF, o taip, SUMIF. Galite netgi naudoti „SUMIF“ nugarą programoje „Excel 2007“. Taigi,Imsiuosi lietaus lauko ir nutempsiu čia. Šiuo metu saugokitės - žiūrėkite, jie pasirinko lietaus grafą, nes todėl, kad duomenyse yra kelios dienos, arba Ken'as turi tuščią langelį, tuščią langelį, o ne 0. Ir taip, mes turėtume tai ištaisyti, bet tai Keno duomenys. Tai 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tuščias. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.Šiuo metu saugokitės - žiūrėkite, jie pasirinko lietaus grafą, nes todėl, kad duomenyse yra kelios dienos, arba Kenas turi tuščią langelį, tuščią langelį, o ne 0. Ir taip, mes turėtume tai ištaisyti, bet tai Keno duomenys. Tai 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tuščias. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.Šiuo metu saugokitės - žiūrėkite, jie pasirinko lietaus grafą, nes todėl, kad duomenyse yra kelios dienos, arba Ken'as turi tuščią langelį, tuščią langelį, o ne 0. Ir taip, mes turėtume tai ištaisyti, bet tai Keno duomenys. Tai 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tuščias. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.s, nes duomenyse yra kelios dienos arba Kenas turi tuščią langelį, tuščią langelį, o ne 0. Ir taip, mes turėtume tai ištaisyti, bet tai Keno duomenys. Tai 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tušti. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.s, nes duomenyse yra kelios dienos arba Kenas turi tuščią langelį, tuščią langelį, o ne 0. Ir taip, mes turėtume tai ištaisyti, bet tai Keno duomenys. Tai 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tuščias. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.s duomenys. Tai 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tuščias. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.s duomenys. Tai yra 20 metų vertės duomenys. Aš neketinu išgyventi net naudodamas „Rasti ir pakeisti“. Gerai, aš tiesiog. Dėl kokios nors priežasties aš gerbsiu, kad Kenas turi pagrindą tokių turėti, pavyzdžiui, aš leisiu jiems likti tuščias. Ir čia, po lietaus grafu, aš būtinai pasirodysiu langelį „Lietaus skaičius“ stulpelyje, eikite į „Lauko nustatymai“ ir pakeiskite tai iš „Count to Sum“, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.Aš būtinai pasirinksiu langelį „Count of Rain“ stulpelyje, eikite į „Field Settings“ ir pakeiskite tai iš Count į Sum, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.Aš būtinai pasirinksiu langelį „Count of Rain“ stulpelyje, eikite į „Field Settings“ ir pakeiskite tai iš Count į Sum, gerai? Taigi yra visi mūsų metai ir kiek lietaus mes patyrėme kiekvienais metais. Mes ieškome tų metų, kuriuose būtų mažiausias kritulių kiekis.
Gerai, vienas dalykas, kuris mane klaidina, yra šis žodis čia Eilių etiketės. Tai mums nutiko „Excel 2007“, gerai? Aš - po 10 metų vis dar niekinu. Einu į skirtuką Dizainas, atidarau ataskaitos maketą ir sakau „Rodyti lentelės forma“ ir visa tai. Šiuo konkrečiu atveju yra tikroji metų antraštė, tiesa? Man labiau patinka tikroji antraštė. Šiuo metu mes norime pamatyti tik viršūnę arba šiuo atveju metus su mažiausiais krituliais. Taigi aš ketinu rūšiuoti šiuos duomenis didėjimo tvarka. Dabar yra du būdai tai padaryti. Galite atidaryti šį išskleidžiamąjį meniu, eiti į „Daugiau rūšiavimo parinkčių“, pasirinkti „Siuntimas“ pagal lietaus sumą, tačiau taip pat galima tiesiog patekti čia į „Data“ nuo A iki Z, kad viskas būtų surūšiuota nuo žemiausios iki didžiausios. Bet aš nenoriu pamatyti tik geriausių 5 metų, todėl metai su mažiausiais krituliais,Čia einu į Metų antraštę, atidarau šį mažą išskleidžiamąjį meniu ir pasirenku „Vertės filtrai“. Aš ieškau „Bottom 5“. Na, „Bottom 5“ filtro nėra. Ahh, bet šis, skirtas dešimčiai geriausių, yra nepaprastai galingas. Gerai, tai nebūtinai turi būti viršuje. Tai gali būti viršuje arba apačioje. Tai neturi būti 10; gali būti 5. Taigi, paprašykite 5 geriausių elementų pagal lietaus sumą, spustelėkite Gerai. Ir yra mūsų ataskaita.
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.
Na, aš, noriu padėkoti Kenui, kad jis išsiuntė šį klausimą. Noriu padėkoti, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2063.xlsm
