Kuri ir pēdējie pieci nokrišņu mēneši? Uzziniet, kā atrisināt šo problēmu, izmantojot rakurstabulu.
Skatīties video
- 2013. gadā izveidotās rakurstabulas nevar atsvaidzināt 2007. gadā
- Jums jāveido rakurstabula 2007. gadā, lai to varētu atsvaidzināt
- Mērķis ir atrast piecus mēnešus ar vismazāko nokrišņu daudzumu
- Izveidojiet lielu pagrieziena galdu ar nokrišņiem mēnesī
- Kārtot pēc nokrišņu daudzuma
- Mainiet uz tabulas formu
- Izmantojiet Value Filters, Top 10, lai iegūtu 5.
- Noņemiet rindu Kopsumma
- Ņemiet vērā, ka neizšķirta dēļ šajā pārskatā var tikt rādītas 6 vai vairāk rindas
- Kad esat ieguvis pirmo rakurstabulu, kopējiet to vietā un izveidojiet nākamo rakurstabulu
- Mainot vienu vērtību lauku uz citu, jums ir atkārtoti jāveic kārtošana un filtrēšana
- Pārejot no vienas rindas lauka uz citu, jums ir atkārtoti jāveic kārtošana un filtrēšana
- Bonusa padoms: izveidojiet pagrieziena tabulu ar rindām un kolonnām
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2063. sērija: Piecu mēnešu vai gadu augšējā vai apakšējā daļa, izmantojot rakurstabulu.
Sveiki, laipni aicināti atgriezties netcast ēterā, es esmu Bils Jelens. Šodienas jautājumu iesūtīja Kens. Kenam ir pārsteidzoša izklājlapa ar gadiem un gadiem un ikdienas lietusgāžu datumiem, sākot ar 1999. gadu. Patiesi iespaidīga datu kolekcija, kas viņam ir, un Kenam bija dažas pārsteidzošas formulas, lai mēģinātu atrast mēnesi, kurā ir visvairāk nokrišņu vismazāk nokrišņu. Tātad tagad, jūs zināt, tas būs daudz vieglāk ar pagrieziena galdu.
Labi, Kens nekad nav izveidojis rakurstabulu un, lai vēl vairāk sarežģītu lietas, esmu šeit Excel 2016, Kens izmanto Excel 2007. Manas rakurstabulas, kuras izveidoju 2016. gadā, viņš varēja viņu redzēt, bet nevarēja tās atsvaidzināt. Labi, tāpēc šis videoklips ir 101. rakurstabula: kā izveidot pirmo rakurstabulu.
Pirmkārt, Kenam A slejā ir šis datums, reālie datumi, vai mēs esam labi? Tas ir lieliski, vai ne? Pēc tam es izmantoju - ievietojiet pāris papildu formulas šeit = YEAR funkcijā, lai iegūtu gadu, = MONTH funkciju, lai iegūtu mēnesi, = DAY funkciju. Pēc tam savienojiet tos kopā, es faktiski izmantoju funkciju = TEKSTS GGGG-MM, tādā veidā man ir palicis gads un mēnesis. Tie ir Kena dati, lietus dati šeit, un tad es pievienoju dažas formulas. Kenam ir kaut kas mazāks par .5 milimetriem, tas netiek uzskatīts par lietus dienu, tāpēc tur ir formula. Un tad, no 735. sērijas, atgriezieties un apskatiet to, lai redzētu, kā es aprēķināju dienu svītru ar lietu un dienu joslu bez lietus. Tagad to šodien neizmantos, ko izmantoja kaut kam citam.
Tātad, mēs nākam šeit. Vispirms mēs vēlamies atlasīt datus mūsu rakurstabulai. Tagad lielākajā daļā gadījumu jūs vienkārši varat atlasīt visus datus, lai šeit varētu izvēlēties tikai vienu šūnu, taču šajā gadījumā ir nosaukuma diapazons, kas definē datus tikai caur šo, šajā gadījumā 2016. gadu. Mēs sēžam šeit - es Es to reģistrēju 2017. gada sākumā. Kena dati ir tikai 2016. gada beigās. Tātad, mēs atlasīsim tieši šos datus. Un pēc tam cilnē Ievietot - cilni Ievietot. Excel 2007 ir pirmā reize, kad rakurstabulas no cilnes Dati pāriet atpakaļ uz cilni Ievietot. Tāpēc mēs izvēlamies: rakurstabulu, un mūsu atlasītie dati būs tie dati, no kuriem mēs veidosimies. Un mēs nevēlamies pāriet uz jaunu darblapu, mēs ejam uz esošu darblapu, un es to ievietošu šeit, slejā - ejam ar N kolonnu.Tagad es gribu, lai šie dati Gadi ar viszemāko nokrišņu daudzumu parādās tieši šeit, bet es zinu, ka, veidojot šo rakurstabulu, tam būs vajadzīgas daudz vairāk rindu nekā šīm 5, vai ne? Tātad, es to šeit būvēju uz sāniem, labi. Un mēs noklikšķinām uz Labi.
Labi, tagad šeit ir tas, ko jūs saņemat. Tieši šeit tiks sagatavots pārskats, un šeit ir saraksts ar visiem laukiem, kas mums ir mūsu mazajā datu kopā. Un tad mums ir, jo tas, ko es saucu par briesmīgi nosauktu, izstājas. Rindas ir vienumi, kurus vēlaties redzēt kreisajā pusē. Vērtības ir tā, ko vēlaties apkopot, un pēc tam slejas ir tās lietas, kuras vēlaties augšpusē. Mēs to varētu izmantot beigās. Mēs šodien neizmantosim filtrus. Tātad, mēs tikai veidojam vienkāršu mazu grozāmo galdu ar kopējo nokrišņu daudzumu gadā, tāpēc es ņemu lauku Gads un velku to šeit uz kreiso pusi. Ir saraksts ar visiem mūsu gadiem, labi? Un tad padomājiet par to. Lai iegūtu šo formulu šeit bez rakurstabulas, jūs darāt ko? SUMIF, ak jā, SUMIF. Jūs pat varētu izmantot SUMIF aizmuguri programmā Excel 2007. Tātad,Es iešu laukā Lietus un vilkšu to šeit. Šobrīd uzmanieties - Skatiet, viņi izvēlējās Lietus grāfu, tas ir tāpēc, ka datos ir dažas dienas vai arī Kenam ir tukša šūna, tukša šūna, nevis 0. Un jā, mums vajadzētu to izlabot, tie ir Kena dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es esmu vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir pamats tos piemīt, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no skaita uz summu, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.Šobrīd uzmanieties - Skatiet, viņi izvēlējās Lietus grāfu, tas ir tāpēc, ka datos ir dažas dienas vai Kenam ir tukša šūna, tukša šūna, nevis 0. Un jā, mums vajadzētu to izlabot, bet tie ir Kena dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir iemesls, lai tos piemeklētu, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no skaita uz summu, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.Šobrīd uzmanieties - Skatiet, viņi izvēlējās Lietus grāfu, tas ir tāpēc, ka datos ir dažas dienas vai Kenam ir tukša šūna, tukša šūna, nevis 0. Un jā, mums vajadzētu to izlabot, bet tie ir Kena dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir iemesls, lai tos piemeklētu, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no skaita uz summu, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.s tāpēc, ka datos ir dažas dienas, vai Kenam ir tukša šūna, tukša šūna, nevis 0. Un jā, mums vajadzētu to izlabot, bet tas ir Kena dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir iemesls, lai tos piemeklētu, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no Count uz Sum, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.s tāpēc, ka datos ir dažas dienas, vai Kenam ir tukša šūna, tukša šūna, nevis 0. Un jā, mums vajadzētu to izlabot, bet tas ir Kena dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir iemesls, lai tos piemeklētu, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no skaita uz summu, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.s dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es esmu vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir pamats tos piemīt, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no skaita uz summu, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.s dati. Datu vērtība ir 20 gadu. Es nepārdzīvošu pat izmantojot Find & Replace. Labi, es vienkārši - kāda iemesla dēļ es cienīšu, ka Kenam ir iemesls, lai tos piemeklētu, piemēram, es atļaušos viņiem palikt tukšs. Un šeit zem lietus skaita es noteikti izvēlēšos šūnu kolonnas Lietus skaits, dodieties uz lauka iestatījumiem un mainiet to no skaita uz summu, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.Es pārliecināšos, ka kolonnā Count of Rain izvēlos šūnu, dodieties uz Field Settings un mainiet to no Count uz Sum, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.Es pārliecināšos, ka kolonnā Count of Rain izvēlos šūnu, dodieties uz Field Settings un mainiet to no Count uz Sum, labi? Tātad ir visi mūsu gadi un cik daudz lietus mums bija katru gadu. Un mēs meklējam gadus ar vismazāko nokrišņu daudzumu.
Labi, viena lieta, kas mani kļūdas, ir šis vārds šeit Row Labels. Tas sākās ar mums programmā Excel 2007, labi? Un es - pēc 10 gadiem es to joprojām nicinu. Es dodos uz cilni Noformējums, atveru Atskaites izkārtojumu un saku Rādīt tabulas veidā, un viss, kas tiek darīts. Šajā konkrētajā gadījumā ir reāls virziens uz gadu, vai ne? Es dodu priekšroku reālajam virzienam. Šobrīd mēs vēlamies redzēt tikai virsotni vai šajā gadījumā gadus ar vismazāko nokrišņu daudzumu. Tāpēc es kārtošu šos datus augošā secībā. Tagad ir divi veidi, kā to izdarīt. Jūs varētu atvērt šo nolaižamo izvēlni, pāriet uz sadaļu Citas kārtošanas opcijas, izvēlēties Sūtīt, pamatojoties uz lietus daudzumu, taču ir iespējams vienkārši ienākt šeit Datos no A līdz Z, lai kārtotu lietas no zemākās līdz augstākajām. Bet es negribu redzēt tikai top 5 gadus, tāpēc gadi ar vismazāko nokrišņu daudzumu,Es eju šeit uz sadaļu Gads, atveru šo mazo nolaižamo izvēlni un izvēlos Vērtību filtri. Un es meklēju 5. apakšdaļu. Nu, 5. apakšā nav filtra. Ahh, bet šis desmitniekam ir neticami spēcīgs. Labi, tam nav jābūt topam. Tas var būt augšējais vai apakšējais. Tam nav jābūt 10; tas var būt 5. Tātad, pieprasiet Top 5 preces, pamatojoties uz lietus summu, noklikšķiniet uz Labi. Un tur ir mūsu ziņojums.
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.
Nu, hei, es gribu pateikties Kenam par šī jautājuma iesūtīšanu. Es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2063.xlsm
