Ktoré sú posledných päť mesiacov zrážok? Naučte sa, ako vyriešiť tento problém pomocou kontingenčnej tabuľky.
Pozeraj video
- Kontingenčné tabuľky vytvorené v roku 2013 nie je možné obnoviť v roku 2007
- V roku 2007 musíte vytvoriť kontingenčnú tabuľku, aby bola obnoviteľná
- Cieľom je nájsť päť mesiacov s najmenšími zrážkami
- Vytvorte veľkú kontingenčnú tabuľku so zrážkami podľa mesiaca
- Zoradiť podľa zrážok vzostupne
- Zmeňte na tabuľkovú formu
- Použite hodnotové filtre, top 10, na získanie spodných 5!
- Odstráňte riadok Celkový súčet
- Upozorňujeme, že remíza môže spôsobiť, že tento prehľad bude mať 6 alebo viac riadkov
- Keď máte prvú kontingenčnú tabuľku, skopírujte ju na miesto a vytvorte ďalšiu kontingenčnú tabuľku
- Keď prechádzate z jedného hodnotového poľa do druhého, musíte znova vykonať triedenie a filtrovanie
- Keď prechádzate z jedného poľa riadku do druhého, musíte znova vykonať triedenie a filtrovanie
- Bonusový tip: vytvorenie kontingenčnej tabuľky s riadkami a stĺpcami
Prepis videa
Naučte sa program Excel z Podcastu, epizóda 2063: Horných alebo dolných päť mesiacov alebo rokov pomocou kontingenčnej tabuľky.
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnešnú otázku poslal Ken. Ken tu má úžasnú tabuľku s rokmi a rokmi a rokmi denných zrážok, ktoré siahajú až do roku 1999. Skutočne pôsobivá zbierka údajov, ktoré má, a Ken mal niekoľko úžasných vzorcov, pomocou ktorých sa pokúsil nájsť mesiac s najväčšími zrážkami, na najmenej zrážok. Takže teraz, viete, to bude s kontingenčnou tabuľkou oveľa jednoduchšie.
V poriadku, Ken nikdy nevytvoril kontingenčnú tabuľku. Aby som to ešte viac skomplikoval, som tu v programe Excel 2016, Ken používa program Excel 2007. Moje kontingenčné tabuľky, ktoré som vytvoril v roku 2016, ho mohol vidieť, ale nemohol ich aktualizovať. Dobre, takže toto video je kontingenčná tabuľka 101: Ako vytvoriť prvú kontingenčnú tabuľku.
Najprv má Ken tento dátum v stĺpci A, skutočné dátumy, sme dobrí? To je úžasné, že? A potom použijem - vložím sem niekoľko ďalších vzorcov do funkcie = YEAR, aby som získal rok, = MONTH funkcia na získanie mesiaca, = DAY funkcia. A potom ich spojím dokopy, vlastne som použil funkciu = TEXT v RRRR-MM, takže mám rok a mesiac dole. Toto sú Kenove údaje, tu údaje o daždi a potom som pridal niekoľko vzorcov. Ken's má niečo menej ako 0,5 milimetra, nepočíta sa ako daždivý deň, takže je tu vzorec. A potom, od epizódy 735, vráťte sa späť a pozrite sa na to, aby ste videli, ako som vypočítal sériu dní s dažďom a sériu dní bez dažďa. To sa dnes nebude používať, to sa používalo na niečo iné.
Takže poďme sem. Najskôr chceme vybrať údaje pre našu kontingenčnú tabuľku. Teraz vo väčšine prípadov môžete vybrať iba všetky údaje, aby ste si tu mohli vybrať iba jednu bunku, ale v tomto prípade existuje rozsah názvov, ktorý definuje údaje, ktoré prechádzajú, v tomto prípade rok 2016. Sedíme tu - m to zaznamenávame začiatkom roka 2017. Údaje Kena prechádzajú až do konca roka 2016. Vyberieme teda iba tieto údaje. A potom na karte Vložiť - karta Vložiť. Excel 2007, je to prvýkrát, čo sa kontingenčné tabuľky presunú z karty Údaje späť na kartu Vložiť. Takže vyberieme: Kontingenčná tabuľka a nami vybrané údaje budú dáta, z ktorých vychádzame. A nechceme ísť na nový pracovný hárok, pôjdeme do existujúceho pracovného hárka a ja to vložím priamo sem do stĺpca - poďme so stĺpcom N.Teraz nakoniec chcem, aby sa tieto údaje Roky s najnižšími zrážkami zobrazili práve tu, ale viem, že keď zostavujem túto kontingenčnú tabuľku, bude potrebných oveľa viac riadkov ako tých 5, však? Takže, budujem to tu bokom, dobre. A klikneme na OK.
Dobre, tu je to, čo dostanete. To je miesto, kam sa bude chystať prehľad. Tu je zoznam všetkých polí, ktoré máme v našom malom súbore údajov. A potom máme, pretože vypadne to, čo ja nazývam príšerne pomenované. Riadky sú položky, ktoré chcete mať na ľavej strane. Hodnoty sú veci, ktoré chcete zhrnúť, a potom stĺpce sú veci, ktoré chcete vidieť zhora. Možno by sme to použili na konci. Filtre dnes nebudeme používať. Takže práve budujeme jednoduchý malý kontingenčný stôl s celkovým počtom zrážok za rok, takže vezmem pole Rok a presuniem ho sem na ľavú stranu. Existuje zoznam všetkých našich rokov, dobre? A potom sa zamyslite. Čo by ste urobili, keby ste sem dostali tento vzorec bez kontingenčnej tabuľky? SUMIF, ach jo, SUMIF. Môžete dokonca použiť späť SUMIF v programe Excel 2007. Takže,Zoberiem dažďové pole a pretiahnem to sem. Momentálne dávajte pozor na - Vidíte, vybrali si Count of Rain, to je preto, že v dátach je niekoľko dní alebo Ken má prázdnu bunku, prázdnu bunku namiesto 0. A áno, mali by sme to prejsť a opraviť to, ale sú to údaje Kena. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, som - Z akéhokoľvek dôvodu budem rešpektovať, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiete bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníte to z Počítania na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.Momentálne si dajte pozor na - Vidíte, vybrali si Count of Rain, to je preto, že v dátach je pár dní alebo Ken má prázdnu bunku, prázdnu bunku namiesto 0. A áno, mali by sme to prejsť a opraviť to, ale sú to údaje Kena. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, som - Z akéhokoľvek dôvodu budem rešpektovať, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiete bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníte to z Počítania na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.Momentálne si dajte pozor na - Vidíte, vybrali si Count of Rain, to je preto, že v dátach je pár dní alebo Ken má prázdnu bunku, prázdnu bunku namiesto 0. A áno, mali by sme to prejsť a opraviť to, ale sú to údaje Kena. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, len - z akéhokoľvek dôvodu budem rešpektovať to, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiem bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníme to z Počítania na Sum, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.s pretože v dátach je niekoľko dní alebo Ken má prázdnu bunku, prázdnu bunku namiesto 0. A áno, mali by sme to prejsť a opraviť to, ale sú to Kenove dáta. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, len - z akéhokoľvek dôvodu budem rešpektovať to, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiete bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníte to z Počítania na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.s pretože v dátach je niekoľko dní alebo Ken má prázdnu bunku, prázdnu bunku namiesto 0. A áno, mali by sme to prejsť a opraviť to, ale sú to Kenove dáta. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, len - z akéhokoľvek dôvodu budem rešpektovať to, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiete bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníte to z Počítania na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.s údajmi. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, som - Z akéhokoľvek dôvodu budem rešpektovať, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiete bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníte to z Počítania na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.s údajmi. Sú to údaje za 20 rokov. Nejdem to absolvovať ani pomocou funkcie Nájsť a nahradiť. Dobre, len - z akéhokoľvek dôvodu budem rešpektovať to, že Ken má dôvod ich mať, akoby som im dovolil zostať prázdnymi. A tu pod položkou Počet dažďov sa uistím, že vyberiem bunku v stĺpci Počet dažďov, prejdem na Nastavenia poľa a zmeníme to z Počítania na Sum, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.Chystám sa vybrať bunku v stĺpci Počet dažďov, prejsť na Nastavenia poľa a zmeniť to z Počítať na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.Chystám sa vybrať bunku v stĺpci Počet dažďov, prejsť na Nastavenia poľa a zmeniť to z Počítať na Súčet, dobre? Takže sú tu všetky naše roky a koľko dažďov sme každý rok mali. A hľadáme roky s najnižšími zrážkami.
V poriadku, jedna vec, ktorá ma štve, je toto slovo tu Row Row Labels. To sa nám začalo diať v programe Excel 2007, dobre? A ja - o 10 rokov neskôr tým stále pohŕdam. Prejdem na kartu Návrh, otvorím rozloženie prehľadu, poviem Zobraziť v tabuľkovej podobe a všetko ostatné. V tomto konkrétnom prípade je to skutočný smer roka, že? A mám radšej skutočné smerovanie. Práve teraz chceme vidieť iba vrchol alebo v tomto prípade Roky s najnižšími zrážkami. Takže tieto dáta zoradím vzostupne. Teraz existujú dva spôsoby, ako to urobiť. Túto rozbaľovaciu ponuku môžete otvoriť, prejsť do časti Viac možností zoradenia a zvoliť možnosť Odosielanie na základe množstva dažďa. Je však tiež možné prejsť sem do sekcie Údaje od A do Z a zoradiť veci od najnižšej po najvyššiu. Ale nechcem vidieť iba najlepších 5 rokov, takže Roky s najnižšími zrážkami,Prichádzam sem k nadpisu Rok, otvorím túto malú rozbaľovaciu ponuku a vyberiem Hodnotové filtre. A hľadám Bottom 5. Pre Bottom 5 neexistuje žiadny filter. Ahh, ale tento pre prvú desiatku je neuveriteľne silný. Dobre, nemusí to byť top. Môže to byť zhora alebo zdola. Nemusí to byť 10; môže to byť 5. Takže, opýtajte sa na 5 najlepších položiek podľa dažďového množstva, kliknite na OK. A je tu aj naša správa.
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.
No, hej, chcem sa poďakovať Kenovi za odoslanie tejto otázky. Chcem ti poďakovať, že si sa zastavil. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2063.xlsm
