Millised on viimase viie kuu vihmasadu? Siit saate teada, kuidas seda probleemi lahendada pöördtabeli abil.
Vaata videot
- 2013. aastal loodud pöördtabeleid ei saa 2007. aastal värskendada
- Peate tabeli värskendamiseks looma pöördtabeli 2007. aastal
- Eesmärk on leida viis kuud, kus on kõige vähem sademeid
- Looge kuude kaupa suur pöördelaud koos sademetega
- Sorteeri sademete järgi kasvavalt
- Valige tabelivorm
- Viimasele kohale jõudmiseks kasutage väärtusfiltrid, top 10!
- Eemaldage rida Kokku
- Pange tähele, et viigiseisu tõttu võib see aruanne anda teile 6 või enam rida
- Kui teil on esimene pöördetabel, kopeerige see oma kohale ja looge järgmine pöördtabel
- Kui vahetate ühelt väärtusväljalt teisele, peate uuesti sortima ja filtreerima
- Kui vahetate ühelt reaväljalt teisele, peate uuesti sortima ja filtreerima
- Boonusenipp: ridade ja veergudega pöördtabeli loomine
Video ärakiri
Õppige Exceli rakendusest Podcast, osa 2063: Viis kuud või aastat ülemisel või alumisel poolel, kasutades liigendtabelit.
Tere, tere tulemast tagasi netisaate juurde, ma olen Bill Jelen. Tänane küsimus, mille saatis Ken. Kenil on siin hämmastav arvutustabel, mis sisaldab aastaid ja aastaid ning iga-aastaseid sademete kuupäevi, mis ulatuvad aastasse 1999. Tõeliselt muljetavaldav andmete kogu, mis tal on, ja Kenil oli hämmastavaid valemeid, et proovida leida kõige rohkem vihmasadu, vähemalt sademeid. Nii et nüüd on see pöördlaua abil palju lihtsam.
Hea küll, Ken pole kunagi loonud pöördtabelit ja asjade keerukamaks muutmiseks olen siin Excel 2016-s, Ken kasutab Exceli 2007. Minu pöördtabelid, mille lõin 2016. aastal, nägi ta teda, kuid ei suutnud neid värskendada. Olgu, nii et see video on Pivot Table 101: Kuidas luua oma esimest Pivot Table.
Esiteks on Kenil veerus A see kuupäev, tõelised kuupäevad, kas me oleme tublid? See on vinge, eks? Ja siis ma kasutan- sisestage siia funktsiooni = AASTA juurde paar lisavalemit, et saada aasta, = KUU, et saada kuu, = PÄEV. Ja siis ühendage need uuesti kokku, kasutasin tegelikult funktsiooni = TEXT AAAA-KK, nii on mul aasta ja kuu maas. Need on Keni andmed, vihmaandmed siin ja siis lisasin mõned valemid. Kenil on midagi vähem kui .5 millimeetrit, seda ei loeta vihmapäevaks, nii et seal on valem. Ja siis minge episoodist 735 tagasi ja vaadake seda, et näha, kuidas ma arvutasin vihmapäevade ja vihmadeta päevade jada. Nüüd ei kasutata seda täna, seda kasutati millegi muu jaoks.
Nii et me tuleme siia. Esiteks tahame valida oma pöördtabeli andmed. Nüüd saate enamikul juhtudel lihtsalt valida kõik andmed, et saaksite siin valida lihtsalt ühe lahtri, kuid sel juhul on olemas nimevahemik, mis määratleb andmed lihtsalt läbi, antud juhul 2016. aastal. Me istume siin - ma Ma salvestasin selle 2017. aasta alguses. Keni andmed läbivad alles 2016. aasta lõpu. Nii et valime just need andmed. Ja siis vahekaardil Lisa- vahekaart Lisa. Excel 2007 on see esimene kord, kui liigendtabelid liiguvad vahekaardilt Andmed tagasi vahekaardile Lisa. Seega valime: Pivot Table ja meie valitud andmed on need, millest me koostame. Ja me ei taha minna uuele töölehele, me läheme olemasolevale töölehele ja ma panen selle siia veergu - läheme veerg N-ga.Nüüd tahaksin lõpuks, et need andmed ilmneksid kõige madalama sademete aastaga just siin, kuid ma tean, et seda pöördtabelit ehitades läheb vaja palju rohkem ridu kui neid 5, eks? Ehitan selle siin küljele, olgu. Ja klõpsame nuppu OK.
Hästi, nüüd saate siit teada. Siia läheb aruanne ja siin on loetelu kõigist väljadest, mis meil on väikeses andmekogumis. Ja siis oleme, sest see, mida ma nimetan kohutavalt nimeks, langeb välja. Ridad on üksused, mida soovite vasakus servas allapoole panna. Väärtused on see, mida soovite kokku võtta, ja siis on veerud need asjad, mida soovite ülalt. Me võime seda lõpus kasutada. Me ei hakka täna filtreid kasutama. Niisiis, me ehitame lihtsalt lihtsat väikest pöördlauda, mille sademete hulk on aasta lõikes, nii et võtan välja Aasta ja lohistan selle siia vasakule. Seal on nimekiri kõigist meie aastatest, eks? Ja siis mõtle sellele. Selle valemi saamiseks ilma pöördtabelita siin teeksite mida? SUMIF, oh jah, SUMIF. Võite isegi kasutada rakenduses Excel 2007 SUMIFi tagakülge.Võtan Vihmavälja ja lohistan selle siia. Praegu olge ettevaatlik - vaadake, nad valisid vihma krahvi, selle põhjuseks on see, et andmetes on mõni päev või on Kenil tühi lahter, tühi lahter 0 asemel. Ja jah, me peaksime selle parandama, kuid need on Keni andmed. Andmete väärtus on 20 aastat. Ma ei kavatse isegi läbi otsida ja asendada. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.Praegu olge ettevaatlik - vaadake, nad valisid vihma krahvi, seda seetõttu, et andmetes on mõni päev või on Kenil tühi lahter, tühi lahter 0 asemel. Ja jah, me peaksime selle läbi parandama, kuid need on Keni andmed. Andmete väärtus on 20 aastat. Ma ei hakka läbi vaatama isegi funktsiooni Leia ja asenda abil. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.Praegu olge ettevaatlik - vaadake, nad valisid vihma krahvi, seda seetõttu, et andmetes on mõni päev või on Kenil tühi lahter, tühi lahter 0 asemel. Ja jah, me peaksime selle läbi parandama, kuid need on Keni andmed. Andmete väärtus on 20 aastat. Ma ei hakka läbi vaatama isegi funktsiooni Leia ja asenda abil. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.s kuna andmetes on mõni päev või on Kenil tühi lahter, tühi lahter 0 asemel. Ja jah, peaksime selle läbi vaatama, kuid see on Keni andmed. Andmete väärtus on 20 aastat. Ma ei kavatse isegi läbi otsida ja asendada. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.s kuna andmetes on mõni päev või on Kenil tühi lahter, tühi lahter 0 asemel. Ja jah, peaksime selle läbi vaatama, kuid see on Keni andmed. Andmete väärtus on 20 aastat. Ma ei hakka läbi vaatama isegi funktsiooni Leia ja asenda abil. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.s andmed. Andmete väärtus on 20 aastat. Ma ei hakka läbi vaatama isegi funktsiooni Leia ja asenda abil. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.s andmed. Andmete väärtus on 20 aastat. Ma ei kavatse isegi läbi otsida ja asendada. Hästi, ma olen lihtsalt - mingil põhjusel austan seda, et Kenil on põhjust neid omada, nagu luban neil jääda tühjaks. Ja siin, vihma loenduse all, valin kindlasti veerus Vihma arv loendi, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.Ma valin veerus Vihma arv kindlasti lahtri, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.Ma valin veerus Vihma arv kindlasti lahtri, minge välja sättedesse ja muudan selle loendist Summa, olgu? Nii et siin on kõik meie aastad ja see, kui palju vihma meil igal aastal oli. Ja otsime kõige madalamate sademetega aastaid.
Olgu, üks asi, mis mind vigastab, on see sõna siin Rid Labels. See hakkas meil Excel 2007-s juhtuma, eks? Ja mina - 10 aastat hiljem põlgan seda siiani. Ma lähen vahekaardile Kujundus, avan aruande paigutuse ja ütlen, et Kuva tabelivormis, ja kõik see, mida tehakse. Sel konkreetsel juhul on saada tõeline rubriik sinna Aasta, eks? Ja ma eelistan tõelist suunda. Praegu tahame näha ainult kõige madalamat vihma või siis antud juhul kõige väiksemate sademetega aastaid. Nii et ma hakkan neid andmeid kasvavas järjekorras sorteerima. Nüüd on selleks kaks võimalust. Saate avada selle rippmenüü, minna jaotisse Rohkem sortimisvalikuid, valida Saate saamiseks vihmasumma alusel, kuid siia on võimalik tulla ka andmete, A kuni Z vahel, et asjad saaksid järjestatud kõige madalamast kõrgeimani. Kuid ma ei taha näha ainult 5 parimat aastat, nii et madalaima vihmasajuga aastad,Tulen siia rubriiki Aasta, avan selle väikese rippmenüü ja valin Väärtusfiltrid. Ja ma otsin Bottom 5. Noh, Bottom 5 jaoks pole filtrit. Ahh, kuid see esikümne jaoks on uskumatult võimas. Hästi, see ei pea olema tipp. See võib olla ülemine või alumine. See ei pea olema 10; see võib olla 5. Niisiis, küsige vihma summa põhjal 5 kõige paremat toodet, klõpsake nuppu OK. Ja seal on meie aruanne.
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.
Noh, hei, ma tahan tänada Keni selle küsimuse saatmise eest. Ma tahan tänada teid peatumast. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2063.xlsm
