Jaké jsou posledních pět měsíců srážek? Naučte se, jak vyřešit tento problém pomocí kontingenční tabulky.
Sledovat video
- Kontingenční tabulky vytvořené v roce 2013 nelze v roce 2007 aktualizovat
- V roce 2007 musíte vytvořit kontingenční tabulku, aby byla obnovitelná
- Cílem je najít pět měsíců s nejméně srážkami
- Vytvořte velkou kontingenční tabulku se srážkami podle měsíce
- Seřadit podle srážek vzestupně
- Změnit na tabulkovou formu
- Pomocí hodnotových filtrů, Top 10, získáte spodních 5!
- Odeberte řádek Celkový součet
- Pamatujte, že remíza může způsobit, že vám tento přehled poskytne 6 nebo více řádků
- Jakmile máte první kontingenční tabulku, zkopírujte ji na místo a vytvořte další kontingenční tabulku
- Když přecházíte z jednoho hodnotového pole do druhého, musíte znovu provést třídění a filtrování
- Když přecházíte z jednoho pole řádku do druhého, musíte znovu provést řazení a filtrování
- Bonusový tip: vytvoření kontingenční tabulky s řádky a sloupci
Přepis videa
Naučte se Excel z Podcastu, epizoda 2063: Horní nebo dolní pět měsíců nebo let pomocí kontingenční tabulky.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka zaslaná Kenem. Ken zde má úžasnou tabulku s roky a roky a roky denních srážek, které sahají až do roku 1999. Opravdu působivá sbírka dat, která má, a Ken měl několik úžasných vzorců, aby se pokusil najít měsíc s nejvíce srážkami, na nejméně srážek. Takže teď, víte, s kontingenční tabulkou to bude mnohem jednodušší.
Dobře, Ken nikdy nevytvořil kontingenční tabulku a abych to ještě více zkomplikoval, jsem tady v Excelu 2016, Ken používá Excel 2007. Moje kontingenční tabulky, které jsem vytvořil v roce 2016, ho mohl vidět, ale nemohl je aktualizovat. Dobře, takže toto video je kontingenční tabulka 101: Jak vytvořit první kontingenční tabulku.
Nejprve, Ken, mít toto datum ve sloupci A, skutečná data, jsme dobří? To je úžasné, že? A pak použiji - vložím pár dalších vzorců sem do funkce = YEAR pro získání roku, = MONTH funkce pro získání měsíce, = DAY funkce. A potom je zřetězit dohromady, vlastně jsem použil funkci = TEXT v RRRR-MM, takže mám rok a měsíc dole. Toto jsou Kenova data, data o dešti zde a pak jsem přidal několik vzorců. Ken má něco méně než 0,5 milimetru, nepočítá se jako deštivý den, takže tam existuje vzorec. A pak se od epizody 735 vraťte a podívejte se na to, abyste zjistili, jak jsem vypočítal pruh dní s deštěm a pruh dní bez deště. To se dnes nebude používat, to se používalo pro něco jiného.
Pojďme sem. A nejprve chceme vybrat data pro naši kontingenční tabulku. Nyní ve většině případů můžete vybrat pouze všechna data, abyste si mohli vybrat pouze jednu buňku, ale v tomto případě existuje rozsah názvů, který definuje data právě v tomto případě, v tomto případě 2016. Sedíme zde - já ' m zaznamenáváme to na začátku roku 2017. Kenova data procházejí teprve koncem roku 2016. Takže vybereme právě ta data. A pak na kartě Vložit - karta Vložit. Excel 2007, je to poprvé, co se kontingenční tabulky přesunou z karty Data zpět na kartu Vložit. Takže jsme si vybrali: Kontingenční tabulka a naše vybraná data budou data, ze kterých budeme stavět. A nechceme přejít na nový list, přejdeme na existující list a já to vložím sem do sloupce - pojďme se sloupcem N.Nakonec chci, aby se tato data Years with the Lowest Rainfall objevila právě tady, ale vím, že při vytváření této kontingenční tabulky bude zapotřebí mnohem více řádků než těch 5, že? Takže to tady buduji na stranu, dobře. A klikneme na OK.
Dobře, tady je to, co dostanete. To je místo, kam se zpráva chystá, a zde je seznam všech polí, která máme v naší malé datové sadě. A pak máme, protože vypadne to, čemu říkám příšerně pojmenované. Řádky jsou položky, které chcete dole vlevo. Hodnoty je věc, kterou chcete shrnout, a sloupce jsou věci, které chcete v horní části. Můžeme to použít na konci. Dnes filtry používat nebudeme. Právě tedy vytváříme jednoduchý malý kontingenční stůl s celkovým úhrnem srážek za rok, takže vezmu pole Rok a přetáhnu jej zde dole na levou stranu. Existuje seznam všech našich let, dobře? A pak o tom přemýšlejte. Chcete-li získat tento vzorec bez kontingenční tabulky, co byste dělali? SUMIF, ach jo, SUMIF. Můžete dokonce použít zpět SUMIF v aplikaci Excel 2007. Takže,Vezmu pole Rain a přetáhnu to sem. Právě teď dávejte pozor na - Podívejte se, vybrali si Počet deště, to proto, že v datech je několik dní nebo Ken má prázdnou buňku, prázdnou buňku namísto 0. A jo, měli bychom to projít a opravit to, ale jsou to Kenova data. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změním to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.Právě teď dávejte pozor na - Podívejte se, vybrali si Počet deště, to proto, že v datech je několik dní nebo Ken má prázdnou buňku, prázdnou buňku místo 0. A ano, měli bychom to projít a opravit jsou to Kenova data. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změní to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.Právě teď dávejte pozor na - Podívejte se, vybrali si Počet deště, to proto, že v datech je několik dní nebo Ken má prázdnou buňku, prázdnou buňku namísto 0. A jo, měli bychom to projít a opravit to, ale jsou to Kenova data. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změní to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.s protože v datech je několik dní nebo má Ken prázdnou buňku, prázdnou buňku místo 0. A ano, měli bychom to projít a opravit, ale jsou to Kenova data. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změní to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.s protože v datech je několik dní nebo má Ken prázdnou buňku, prázdnou buňku místo 0. A ano, měli bychom to projít a opravit, ale jsou to Kenova data. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změní to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.s daty. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změním to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.s daty. Data mají 20 let. Nebudu procházet ani pomocí funkce Najít a nahradit. Dobře, jsem - Z jakéhokoli důvodu budu respektovat, že Ken má důvod, aby je měl, jako kdybych jim dovolil zůstat prázdný. A tady, pod Count of Rain, se ujistím, že vyberete buňku ve sloupci Count of Rain, přejdu na Field Settings a změní to z Count na Sum, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.Ujistím se, že vyberete buňku ve sloupci Počet deště, přejdu do Nastavení pole a změním to z Počítat na Součet, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.Ujistím se, že vyberete buňku ve sloupci Počet deště, přejdu do Nastavení pole a změním to z Počítat na Součet, dobře? Takže tam jsou všechny naše roky a kolik deště jsme každý rok měli. A hledáme roky s nejnižšími srážkami.
Dobře, jedna věc, která mě štve, je toto slovo zde Řádkové štítky. To se nám začalo dít v Excelu 2007, dobře? A já - o 10 let později tím stále pohrdám. Jdu na kartu Návrh, otevřete Rozvržení sestavy a řeknu Zobrazit ve formě tabulky a vše, co dělá. V tomto konkrétním případě je dostat tam skutečný směr roku, že? A já dávám přednost skutečnému směru. Právě teď chceme vidět jen vrchol nebo v tomto případě Roky s nejnižšími srážkami. Takže budu třídit tato data vzestupně. Nyní existují dva způsoby, jak to udělat. Tuto rozevírací nabídku můžete otevřít, přejít na Více možností řazení, zvolit Odeslat na základě součtu deště, ale je také možné přijít sem do Data, A až Z, aby se věci seřadily od nejnižší po nejvyšší. Ale nechci vidět jen top 5 let, takže Roky s nejnižšími srážkami,Přišel jsem sem do záhlaví roku, otevřel tento malý rozevírací seznam a zvolil hodnotové filtry. A hledám Bottom 5. No, není Bottom 5 žádný filtr. Ahh, ale tenhle pro první desítku je neuvěřitelně silný. Dobře, nemusí to být top. Může to být nahoře nebo dole. Nemusí to být 10; může to být 5. Takže požádejte o Top 5 položek na základě součtu dešťů, klikněte na OK. A je tu naše zprá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, chci poděkovat Kenu za zaslání té otázky. Chci ti poděkovat, že jsi se zastavil. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2063.xlsm
