Care sunt ultimele cinci luni de precipitații? Aflați cum să rezolvați această problemă folosind un tabel pivot.
Urmăriți videoclipul
- Tabelele pivot create în 2013 nu pot fi actualizate în 2007
- Trebuie să creați tabelul pivot în 2007 pentru a permite reîmprospătarea acestuia
- Scopul este de a găsi cele cinci luni cu cele mai puține precipitații
- Creați un tabel pivot mare cu precipitații pe lună
- Sortează după precipitații crescătoare
- Treceți la Formular tabular
- Folosiți filtrele de valoare, Top 10, pentru a obține cele 5 de jos!
- Eliminați rândul Grand Total
- Rețineți că o egalitate poate determina acest raport să vă ofere 6 sau mai multe rânduri
- După ce aveți primul tabel pivot, copiați-l în loc și creați următorul tabel pivot
- Când treceți de la un câmp valoric la altul, trebuie să refaceți sortarea și filtrarea
- Când treceți de la un câmp de rând la altul, trebuie să refaceți sortarea și filtrarea
- Sfat bonus: crearea unui tabel pivot cu rânduri și coloane
Transcriere video
Aflați Excel din Podcast, episodul 2063: partea de sus sau de jos cinci luni sau ani folosind un tabel pivot.
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi trimisă de Ken. Ken are aici o foaie de calcul uimitoare, cu ani, ani și ani de date zilnice de precipitații, începând din 1999. O colecție cu adevărat impresionantă de date pe care o are, iar Ken a avut câteva formule uimitoare pentru a încerca să găsească luna cu cele mai multe precipitații mai puține precipitații. Deci, acum, știți, acest lucru va fi mult mai ușor cu un tabel pivot.
Bine acum, Ken nu a creat niciodată un tabel pivot și pentru a complica și mai mult lucrurile, sunt aici în Excel 2016, Ken folosește Excel 2007. Tabelele mele pivot pe care le-am creat în 2016, el l-a putut vedea, dar nu le-a putut reîmprospăta. Bine, deci acest videoclip este Tabelul pivot 101: Cum să creați primul tabel pivot.
În primul rând, Ken are această dată în coloana A, date reale, suntem buni? E minunat, nu? Și apoi folosesc - introduc câteva formule suplimentare aici la funcția = YEAR pentru a obține anul, = funcția LUNĂ pentru a obține luna, = funcția DAY. Și apoi concatenați-le împreună, am folosit de fapt funcția = TEXT în AAAA-MM, așa am anul și luna jos. Acestea sunt datele lui Ken, datele de ploaie aici și apoi am adăugat câteva formule. Ken are ceva mai puțin de .5 milimetri, nu este o zi ploioasă, așa că există o formulă acolo. Și apoi, din episodul 735, întoarce-te și aruncă o privire la asta pentru a vedea cum am calculat șirul de zile cu ploaie și șirul de zile fără ploaie. Acum, asta nu va fi folosit astăzi, ci a fost folosit pentru altceva.
Deci, venim aici. Și mai întâi, dorim să selectăm datele pentru tabelul nostru pivot. Acum, în majoritatea cazurilor, puteți selecta toate datele, astfel încât să puteți alege o singură celulă aici, dar în acest caz, există un interval de nume care definește datele până în 2016, în acest caz. Înregistrez acest lucru la începutul anului 2017. Datele lui Ken trec până la sfârșitul anului 2016. Deci, vom selecta doar acele date. Și apoi pe fila Inserare - fila Inserare. Excel 2007, este prima dată când tabelele pivot se mută din fila Date înapoi în fila Insert. Așadar, alegem: Tabelul pivot, iar datele selectate vor fi datele din care construim. Și nu vrem să mergem la o nouă foaie de lucru, vom merge la o foaie de lucru existentă și o voi pune chiar aici în Coloană - să mergem cu Coloana N.Acum, în cele din urmă, vreau ca aceste date Anii cu cele mai mici precipitații să apară chiar aici, dar știu că, pe măsură ce construiesc acest tabel pivot, va avea nevoie de mult mai multe rânduri decât cele 5, nu? Deci, o construiesc aici, bine. Și facem clic pe OK.
În regulă, iată ce primești. Aici va merge raportul și iată o listă cu toate câmpurile pe care le avem în micul nostru set de date. Și atunci avem, pentru că ceea ce numesc îngrozitor numit scade. Rows este elementele pe care le doriți în partea stângă. Valorile este ceea ce doriți să rezumați și apoi Coloanele sunt lucrurile pe care le doriți în partea de sus. S-ar putea să folosim acest lucru la sfârșit. Nu vom folosi filtrele astăzi. Deci, tocmai construim un mic tabel pivot simplu cu precipitații totale pe an, așa că iau câmpul An și îl trag aici în partea stângă. Există o listă a tuturor anilor noștri, bine? Și apoi, gândește-te la asta. Pentru a obține această formulă aici fără un tabel pivot, ce ați face? SUMIF, oh da, SUMIF. Puteți folosi chiar și SUMIF înapoi în Excel 2007. Deci,O să iau câmpul de ploaie și să-l trag aici. Acum aveți grijă la - Vedeți, au ales Count of Rain, asta pentru că există câteva zile în date sau Ken are o celulă goală, o celulă goală în loc de 0. Și da, ar trebui să trecem și să remediem asta, dar sunt datele lui Ken. Sunt date în valoare de 20 de ani. N-am de gând să trec chiar și folosind Căutare și înlocuire. Bine, sunt doar - Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.Acum aveți grijă la - Vedeți, au ales Count of Rain, asta pentru că există câteva zile în date sau Ken are o celulă goală, o celulă goală în loc de 0. Și da, ar trebui să trecem și să remediem asta, dar sunt datele lui Ken. Sunt date în valoare de 20 de ani. N-am de gând să trec chiar și folosind Căutare și înlocuire. Bine, sunt doar - Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.Acum aveți grijă la - Vedeți, au ales Count of Rain, asta pentru că există câteva zile în date sau Ken are o celulă goală, o celulă goală în loc de 0. Și da, ar trebui să trecem și să remediem asta, dar sunt datele lui Ken. Sunt date în valoare de 20 de ani. N-am de gând să trec chiar și folosind Căutare și înlocuire. Bine, sunt doar - Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la Sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.Pentru că există câteva zile în date sau Ken are o celulă goală, o celulă goală în loc de 0. Și da, ar trebui să trecem și să remediem asta, dar sunt datele lui Ken. Sunt date în valoare de 20 de ani. N-am să trec nici măcar folosind Căutare și înlocuire. Bine, sunt doar - Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.Pentru că există câteva zile în date sau Ken are o celulă goală, o celulă goală în loc de 0. Și da, ar trebui să trecem și să remediem asta, dar sunt datele lui Ken. Sunt date în valoare de 20 de ani. N-am de gând să trec chiar și folosind Căutare și înlocuire. Bine, sunt doar … Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la Sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.datele s. Sunt date în valoare de 20 de ani. N-am să trec nici măcar folosind Căutare și înlocuire. Bine, sunt doar - Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.datele s. Sunt date în valoare de 20 de ani. N-am de gând să trec chiar și folosind Căutare și înlocuire. Bine, sunt doar - Din orice motiv, voi respecta faptul că Ken are un motiv să le aibă, de parcă le voi permite să rămână necompletate. Și aici, sub Numărul ploii, mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setări câmp și o schimb de la Număr la sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.Mă voi asigura că aleg o celulă din coloana Numărul ploii, mergi la Setările câmpului și o schimbăm din Număr în Sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.Mă voi asigura că aleg o celulă din coloana Numărul de ploaie, mergi la Setări câmp și o schimbăm din Număr în Sumă, bine? Deci, sunt toți anii noștri și câtă ploaie am avut în fiecare an. Și căutăm anii cu cele mai mici precipitații.
Bine acum, un lucru care mă bâlbâie este acest cuvânt aici Row Labels. Asta a început să ni se întâmple în Excel 2007, bine? Și eu - 10 ani mai târziu, încă disprețuiesc asta. Mă duc la fila Proiectare, deschid aspectul raportului și spun Afișare în formă tabulară și tot ce face. În acest caz special este să obțineți o rubrică reală acolo a anului, nu? Și prefer titlul real. În acest moment, vrem să vedem doar vârful sau, în acest caz, Anii cu cele mai mici precipitații. Așa că voi ordona aceste date ascendent. Acum există două modalități de a face acest lucru. Puteți deschide această listă derulantă, accesați Mai multe opțiuni de sortare, alegeți Trimiterea pe baza sumei de ploaie, dar este, de asemenea, posibil să intrați aici în Date, de la A la Z pentru a face lucrurile sortate de la cel mai mic la cel mai mare. Dar nu vreau să văd doar primii 5 ani, deci Anii cu cele mai mici precipitații,Vin aici la rubrica An, deschid această mică listă derulantă și aleg Filtre de valoare. Și îl caut pe fundul 5. Ei bine, nu există niciun filtru pentru fundul 5. Ahh, dar acesta pentru top zece este incredibil de puternic. În regulă, nu trebuie să fie de top. Poate fi sus sau jos. Nu trebuie să fie 10; poate fi 5. Deci, cereți primele 5 articole pe baza sumei de ploaie, faceți clic pe OK. Și acolo este raportul nostru.
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.
Ei bine, vreau să-i mulțumesc lui Ken pentru că mi-a trimis întrebarea. Vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2063.xlsm
