Katerih je pet mesecev padavin? Naučite se rešiti to težavo z vrtilno tabelo.
Oglejte si video
- Vrtilnih tabel, ustvarjenih leta 2013, v letu 2007 ni mogoče osvežiti
- V letu 2007 morate ustvariti vrtilno tabelo, da jo je mogoče osvežiti
- Cilj je najti pet mesecev z najmanj padavinami
- Ustvarite veliko vrtilno tabelo s padavinami po mesecih
- Razvrsti po padavinah naraščajoče
- Spremeni se v tabelarni obrazec
- Uporabite filtre vrednosti, top 10, da dobite spodnjih 5!
- Odstranite vrstico Vsota
- Upoštevajte, da lahko zaradi izenačevanja v tem poročilu dobite 6 ali več vrstic
- Ko imate prvo vrtilno tabelo, jo kopirajte na svoje mesto in ustvarite naslednjo vrtilno tabelo
- Ko preklopite iz enega polja vrednosti v drugo, morate znova opraviti razvrščanje in filtriranje
- Ko preklopite iz enega polja v drugo, morate znova opraviti razvrščanje in filtriranje
- Bonus nasvet: ustvarjanje vrtilne tabele z vrsticami in stolpci
Video zapis
Naučite se Excel iz podcasta, epizoda 2063: Vrhunskih ali spodnjih pet mesecev ali let z uporabo vrtilne tabele.
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje je poslal Ken. Ken ima tukaj neverjetno preglednico z leti in leti in leti dnevnih datumov padavin, ki segajo v leto 1999. Resnično impresivna zbirka podatkov, ki jih ima, in Ken je imel nekaj neverjetnih formul, s katerimi je poskusil najti mesec z največ padavinami, ob najmanj padavin. Torej, veste, s pivot tabelo bo to veliko lažje.
Zdaj, Ken nikoli ni ustvaril vrtilne tabele in za nadaljnje zapletanje sem tukaj v Excelu 2016, Ken uporablja Excel 2007. Moje vrtilne tabele, ki sem jih ustvaril leta 2016, ga je lahko videl, vendar jih ni mogel osvežiti. Torej, ta video je vrtilna tabela 101: Kako ustvariti prvo vrtilno tabelo.
Prvič, Ken ima ta datum v stolpcu A, prave datume, ali smo dobri? To je super, kajne? In potem uporabim - tukaj v funkcijo = YEAR vstavim nekaj dodatnih formul, da dobim leto, = MONTH funkcijo, da dobim mesec, = DAY funkcijo. In nato jih združim nazaj, dejansko sem uporabil funkcijo = TEXT v LLLL-MM, tako da imam leto in mesec manj. To so Kenovi podatki, tukaj so podatki o dežju in nato sem dodal nekaj formul. Ken's ima kaj manj kot .5 milimetrov, ne šteje kot deževen dan, zato obstaja formula. Potem pa se od epizode 735 vrnite in si oglejte to, da vidite, kako sem izračunal niz dni z dežjem in niz dni brez dežja. Zdaj tega danes ne bomo uporabljali, to je bilo uporabljeno za nekaj drugega.
Torej, prišli smo sem. Najprej želimo izbrati podatke za svojo vrtilno tabelo. Zdaj lahko v večini primerov samo izberete vse podatke, tako da lahko tukaj izberete samo eno celico, vendar v tem primeru obstaja obseg imen, ki definira podatke, ravno v tem primeru, v tem letu 2016. Sedimo tukaj - I ' m zabeležil to v začetku leta 2017. Kenovi podatki segajo šele konec leta 2016. Torej, izbrali bomo samo te podatke. In nato na zavihku Vstavi - zavihek Vstavi. V programu Excel 2007 se vrtilne tabele prvič premaknejo z zavihka Podatki nazaj na zavihek Vstavljanje. Izbrali smo torej: vrtilno tabelo in izbrani podatki bodo podatki, iz katerih gradimo. In ne želimo iti na nov delovni list, gremo na obstoječi delovni list in to bom postavil tukaj v stolpec - gremo s stolpcem N.Zdaj želim, da se ti podatki leta z najnižjimi padavinami prikažejo ravno tukaj, vendar vem, da bo pri izdelavi te vrtilne tabele treba veliko več vrstic kot tistih 5, kajne? Torej, tu gradim stran, v redu. In kliknemo V redu.
V redu, zdaj dobiš to. Tu gre poročilo in tukaj je seznam vseh polj, ki jih imamo v našem majhnem naboru podatkov. In potem smo, kajti temu, čemur pravim grozljivo ime, odpade. Vrstice so elementi, ki jih želite po levi strani. Vrednote je tisto, kar želite povzeti, potem pa so stolpci stvari, ki jih želite na vrhu. To bomo morda uporabili na koncu. Danes ne bomo uporabljali filtrov. Torej, samo ustvarjamo preprosto majhno vrtilno tabelo s skupnimi padavinami po letih, zato vzamem polje Let in ga povlečem sem na levo stran. Obstaja seznam vseh naših let, v redu? In potem razmislite. Kaj bi naredili, če bi dobili to formulo tukaj brez vrtilne tabele? SUMIF, oh ja, SUMIF. Lahko bi celo uporabili nazaj SUMIF v Excelu 2007. Torej,Vzel bom polje Dež in ga povlekel sem. Zdaj bodite pozorni na - Glejte, izbrali so Count of Rain, to je zato, ker je v podatkih nekaj dni ali ima Ken prazno celico, prazno celico namesto 0. In ja, morali bi iti skozi in popraviti to, to so Kenovi podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tukaj bom v okviru Count of Rain izbral celico v stolpcu Count of Rain, odšel v Field Settings in to spremenil iz Count v Sum, v redu? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.Zdaj bodite pozorni na - Glejte, izbrali so Count of Rain, to je zato, ker je v podatkih nekaj dni ali ima Ken prazno celico, prazno celico namesto 0. In ja, morali bi iti skozi in popraviti to, to so Kenovi podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tukaj bom v okviru Count of Rain izbral celico v stolpcu Count of Rain, odšel v Field Settings in to spremenil iz Count v Sum, v redu? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.Zdaj bodite pozorni na - Glejte, izbrali so Count of Rain, ker je v podatkih nekaj dni ali ima Ken prazno celico, prazno celico namesto 0. In ja, morali bi iti skozi in popraviti to, to so Kenovi podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tu, v okviru Count of Rain bom zagotovo izbral celico v stolpcu Count of Rain, pojdem na Field Settings in to spremenim iz Count v Sum, kajne? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.s, ker je v podatkih nekaj dni ali pa ima Ken prazno celico, prazno celico namesto 0. In ja, morali bi iti skozi in popraviti to, ampak to so Kenovi podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tukaj bom v okviru Count of Rain izbral celico v stolpcu Count of Rain, odšel v Field Settings in to spremenil iz Count v Sum, v redu? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.s, ker je v podatkih nekaj dni ali pa ima Ken prazno celico, prazno celico namesto 0. In ja, morali bi iti skozi in popraviti to, ampak to so Kenovi podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tukaj bom v okviru Count of Rain izbral celico v stolpcu Count of Rain, odšel v Field Settings in to spremenil iz Count v Sum, v redu? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.s podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tukaj bom v okviru Count of Rain izbral celico v stolpcu Count of Rain, odšel v Field Settings in to spremenil iz Count v Sum, v redu? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.s podatki. Podatki so vredni 20 let. Ne bom šel skozi niti iskanja in zamenjave. V redu, samo … Iz kakršnega koli razloga bom spoštoval, da ima Ken razlog, da jih ima, kot da jim bom dovolil, da ostanejo prazni. In tukaj bom v okviru Count of Rain izbral celico v stolpcu Count of Rain, odšel v Field Settings in to spremenil iz Count v Sum, v redu? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.V stolpcu Štetje dežja bom izbral celico, pojdem na Nastavitve polja in to spremenim iz Števec v Vsoto, prav? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.V stolpcu Štetje dežja bom izbral celico, pojdem na Nastavitve polja in to spremenim iz Števec v Vsoto, prav? Torej obstajajo vsa naša leta in koliko dežja smo imeli vsako leto. In iščemo leta z najmanj padavinami.
V redu, ena stvar, ki me moti, je ta beseda tukaj Row Labels. To se nam je začelo dogajati v Excelu 2007, kajne? In jaz - 10 let kasneje to še vedno preziram. Pojdem na zavihek Oblikovanje, odprem postavitev poročila in izgovorim Pokaži v tabelarni obliki in vse to. V tem konkretnem primeru se resnično odpravite v leto, kajne? In raje imam pravi naslov. Trenutno želimo videti le vrh ali v tem primeru leta z najnižjimi padavinami. Torej bom te podatke razvrstil naraščajoče. Zdaj obstajata dva načina za to. Lahko odprete ta spustni meni, pojdite na Več možnosti razvrščanja, izberite Pošiljanje glede na vsoto dežja, vendar je možno tudi samo priti sem v Podatke, od A do Ž, da stvari razvrstite od najnižjega do najvišjega. Ampak ne želim videti le prvih 5 let, zato so leta z najnižjimi padavinami,Prišel sem k naslovu Leto, odprl ta spustni meni in izbral Filtri vrednosti. In iščem Bottom 5. No, za Bottom 5. ni filtra. Ahh, toda ta za deseterico je neverjetno zmogljiv. V redu, ni nujno, da je na vrhu. Lahko je zgoraj ali spodaj. Ni nujno, da je 10; lahko je 5. Torej, vprašajte za 5 najboljših elementov glede na vsoto dežja, kliknite V redu. In tu je naše poročilo.
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, rad bi se zahvalil Kenu, da je poslal to vprašanje. Zahvaljujem se vam, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2063.xlsm
