Ποιοι είναι οι τελευταίοι πέντε μήνες βροχόπτωσης; Μάθετε πώς μπορείτε να επιλύσετε αυτό το πρόβλημα χρησιμοποιώντας έναν συγκεντρωτικό πίνακα.
Δες το βίντεο
- Οι συγκεντρωτικοί πίνακες που δημιουργήθηκαν το 2013 δεν μπορούν να ανανεωθούν το 2007
- Πρέπει να δημιουργήσετε τον συγκεντρωτικό πίνακα το 2007 για να είναι ανανεώσιμος
- Στόχος είναι να βρείτε τους πέντε μήνες με τις λιγότερες βροχοπτώσεις
- Δημιουργήστε έναν μεγάλο περιστρεφόμενο πίνακα με βροχόπτωση ανά μήνα
- Ταξινόμηση κατά αύξουσα βροχόπτωση
- Αλλαγή σε μορφή πίνακα
- Χρησιμοποιήστε τα φίλτρα τιμής, τα κορυφαία 10, για να πάρετε το 5!
- Καταργήστε τη σειρά Grand Total
- Λάβετε υπόψη ότι μια ισοπαλία μπορεί να προκαλέσει αυτήν την αναφορά να σας δώσει 6 ή περισσότερες σειρές
- Μόλις έχετε τον πρώτο συγκεντρωτικό πίνακα, αντιγράψτε τον στη θέση του και δημιουργήστε τον επόμενο συγκεντρωτικό πίνακα
- Όταν αλλάζετε από ένα πεδίο τιμών σε άλλο, πρέπει να κάνετε ξανά την ταξινόμηση και το φίλτρο
- Όταν αλλάζετε από ένα πεδίο σε άλλη, πρέπει να κάνετε ξανά την ταξινόμηση και να φιλτράρετε
- Συμβουλή μπόνους: δημιουργία περιστρεφόμενου πίνακα με σειρές και στήλες
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2063: The Top or Bottom Five Months or Years Using A Pivot Table.
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση που έστειλε ο Ken. Ο Ken έχει ένα εκπληκτικό υπολογιστικό φύλλο εδώ με χρόνια και χρόνια και χρόνια ημερήσιων ημερομηνιών βροχόπτωσης, που χρονολογούνται από το 1999. Μια πραγματικά εντυπωσιακή συλλογή δεδομένων που έχει, και ο Ken είχε μερικές εκπληκτικές φόρμουλες για να δοκιμάσει και να βρει τον μήνα με τις περισσότερες βροχοπτώσεις, στο λιγότερη βροχόπτωση. Τώρα, ξέρετε, αυτό θα είναι πολύ πιο εύκολο με έναν περιστρεφόμενο πίνακα.
Εντάξει τώρα, ο Ken δεν δημιούργησε ποτέ έναν περιστρεφόμενο πίνακα και για να περιπλέξει περαιτέρω τα πράγματα, είμαι εδώ στο Excel 2016, ο Ken χρησιμοποιεί το Excel 2007. Οι συγκεντρωτικοί πίνακες μου που δημιούργησα το 2016, μπορούσε να τον δει αλλά δεν μπορούσε να τους ανανεώσει. Εντάξει, έτσι αυτό το βίντεο είναι Pivot Table 101: Πώς να δημιουργήσετε τον πρώτο σας συγκεντρωτικό πίνακα.
Πρώτον, ο Ken έχει αυτήν την ημερομηνία στη στήλη Α, πραγματικές ημερομηνίες, είμαστε καλοί; Είναι φοβερό, σωστά; Και μετά χρησιμοποιώ - εισάγετε μερικούς επιπλέον τύπους εδώ στη συνάρτηση = YEAR για να πάρετε το έτος, = συνάρτηση MONTH για να πάρετε το μήνα, = συνάρτηση DAY. Και έπειτα συνενώσαμε αυτά τα μέρη μαζί, χρησιμοποίησα πραγματικά τη συνάρτηση = TEXT σε YYYY-MM, με αυτόν τον τρόπο έχω χρόνο και μήνα κάτω. Αυτά είναι τα δεδομένα του Ken, τα δεδομένα της βροχής εδώ και μετά πρόσθεσα κάποιους τύπους. Ο Ken έχει κάτι λιγότερο από 0,5 χιλιοστά, δεν μετράει ως ημέρα βροχής, οπότε υπάρχει ένας τύπος εκεί. Και μετά, από το επεισόδιο 735, επιστρέψτε και ρίξτε μια ματιά σε αυτό για να δείτε πώς υπολόγισα τη σειρά ημερών με τη βροχή και τη σειρά ημερών χωρίς βροχή. Τώρα αυτό δεν πρόκειται να χρησιμοποιηθεί σήμερα, που χρησιμοποιήθηκε για κάτι άλλο.
Ερχόμαστε λοιπόν εδώ. Και πρώτα, θέλουμε να επιλέξουμε τα δεδομένα για τον συγκεντρωτικό μας πίνακα. Τώρα, στις περισσότερες περιπτώσεις, θα μπορούσατε απλώς να επιλέξετε όλα τα δεδομένα, ώστε να μπορείτε να επιλέξετε μόνο ένα κελί εδώ, αλλά σε αυτήν την περίπτωση, υπάρχει μια περιοχή ονομάτων που ορίζει τα δεδομένα μόλις, σε αυτήν την περίπτωση, το 2016. Καθίζουμε εδώ- Καταγράφω αυτό στις αρχές του 2017. Τα δεδομένα του Ken περνούν μόνο στο τέλος του 2016. Έτσι, θα επιλέξουμε μόνο αυτά τα δεδομένα. Και μετά στην καρτέλα Εισαγωγή - καρτέλα Εισαγωγή. Excel 2007, είναι η πρώτη φορά που οι συγκεντρωτικοί πίνακες μετακινούνται από την καρτέλα Δεδομένα πίσω στην καρτέλα Εισαγωγή. Έτσι επιλέγουμε: Συγκεντρωτικός πίνακας και τα επιλεγμένα δεδομένα μας θα είναι τα δεδομένα από τα οποία δημιουργούμε. Και, δεν θέλουμε να πάμε σε νέο φύλλο εργασίας, θα πάμε σε ένα υπάρχον φύλλο εργασίας και θα το βάλω ακριβώς εδώ στη στήλη - ας πάμε με τη στήλη Ν.Τελικά, θέλω αυτά τα δεδομένα χρόνια με τη χαμηλότερη βροχόπτωση να εμφανίζονται εδώ, αλλά ξέρω ότι καθώς δημιουργώ αυτόν τον περιστρεφόμενο πίνακα, θα χρειαστούν πολύ περισσότερες σειρές από αυτές τις 5, σωστά; Λοιπόν, το χτίζω στο πλάι εδώ, εντάξει. Και κάνουμε κλικ στο ΟΚ.
Εντάξει, τώρα είναι αυτό που παίρνεις. Εδώ θα πάει η αναφορά και εδώ είναι μια λίστα με όλα τα πεδία που έχουμε στο μικρό μας σύνολο δεδομένων. Και μετά έχουμε, για αυτό που αποκαλώ φρικτά ονομάζεται σταγόνες. Οι σειρές είναι τα αντικείμενα που θέλετε στην αριστερή πλευρά. Οι τιμές είναι το πράγμα που θέλετε να συνοψίσετε και στη συνέχεια οι στήλες είναι τα πράγματα που θέλετε στην κορυφή. Μπορεί να το χρησιμοποιήσουμε στο τέλος. Δεν πρόκειται να χρησιμοποιήσουμε φίλτρα σήμερα. Κατασκευάζουμε λοιπόν έναν απλό μικρό περιστρεφόμενο πίνακα με συνολικές βροχοπτώσεις ανά έτος, οπότε παίρνω το πεδίο Έτος και το μεταφέρω κάτω στην αριστερή πλευρά. Υπάρχει μια λίστα όλων των ετών μας, εντάξει; Και μετά, σκεφτείτε το. Για να λάβετε αυτόν τον τύπο εδώ χωρίς έναν συγκεντρωτικό πίνακα, θα κάνατε αυτό; SUMIF, ω ναι, SUMIF. Θα μπορούσατε ακόμη και να χρησιμοποιήσετε το SUMIF's back στο Excel 2007. Έτσι,Θα πάρω το πεδίο της βροχής και θα το μεταφέρω εδώ. Αυτήν τη στιγμή προσέξτε για το - Δείτε, επέλεξαν το Count of Rain, αυτό συμβαίνει επειδή υπάρχουν λίγες μέρες στα δεδομένα ή ο Ken έχει ένα κενό κελί, ένα κενό κελί αντί για το 0. Και ναι, πρέπει να περάσουμε και να το διορθώσουμε, αλλά είναι τα δεδομένα του Ken. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στο Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.Αυτήν τη στιγμή προσέξτε για το - Δείτε, επέλεξαν το Count of Rain, αυτό συμβαίνει επειδή υπάρχουν λίγες μέρες στα δεδομένα ή ο Ken έχει ένα κενό κελί, ένα κενό κελί αντί για το 0. Και ναι, πρέπει να περάσουμε και να το διορθώσουμε, αλλά είναι τα δεδομένα του Ken. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στο Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.Αυτήν τη στιγμή προσέξτε για το - Δείτε, επέλεξαν το Count of Rain, αυτό συμβαίνει επειδή υπάρχουν λίγες μέρες στα δεδομένα ή ο Ken έχει ένα κενό κελί, ένα κενό κελί αντί για το 0. Και ναι, πρέπει να περάσουμε και να το διορθώσουμε, αλλά είναι τα δεδομένα του Ken. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στην περιοχή Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.επειδή υπάρχουν λίγες μέρες στα δεδομένα ή ο Ken έχει ένα κενό κελί, ένα κενό κελί αντί για ένα 0. Και ναι, πρέπει να το εξετάσουμε και να το διορθώσουμε, αλλά είναι τα δεδομένα του Ken. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στο Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.επειδή υπάρχουν λίγες μέρες στα δεδομένα ή ο Ken έχει ένα κενό κελί, ένα κενό κελί αντί για ένα 0. Και ναι, πρέπει να το εξετάσουμε και να το διορθώσουμε, αλλά είναι τα δεδομένα του Ken. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στο Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.δεδομένα s. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στο Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.δεδομένα s. Τα δεδομένα αξίας 20 ετών. Δεν πρόκειται να περάσω ακόμη και χρησιμοποιώντας το Find & Replace. Εντάξει, είμαι απλά - Για οποιονδήποτε λόγο θα σεβαστώ ότι ο Ken έχει έναν λόγο να τα έχει, όπως θα τους επιτρέψω να παραμείνουν κενές. Και εδώ, στην περιοχή Count of Rain, θα φροντίσω να επιλέξω ένα κελί στη στήλη Count of Rain, να μεταβω στις Field Field και να το αλλάξω από Count σε Sum, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.Θα σιγουρευτώ ότι θα επιλέξω ένα κελί στη στήλη Αρίθμηση βροχής, θα πάω στο Ρυθμίσεις πεδίου και θα το αλλάξω από το πλήθος στο άθροισμα, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.Θα σιγουρευτώ ότι θα επιλέξω ένα κελί στη στήλη "Καταμέτρηση βροχής", θα μεταβω στις Ρυθμίσεις πεδίου και θα το αλλάξω από το πλήθος σε άθροισμα, εντάξει; Υπάρχουν λοιπόν όλα τα χρόνια μας και πόση βροχή είχαμε κάθε χρόνο. Και αναζητούμε τα χρόνια με τη χαμηλότερη βροχόπτωση.
Alright now, one thing that bugs me is this word here Row Labels. That started happening to us in Excel 2007, alright? And I - 10 years later I still despise that. I go to the Design Tab, open Report Layout and say Show in Tabular Form, and all that does. In this particular case is get a real heading there of Year, right? And I prefer the real heading. Right now, we want to see just the top or in this case, the Years with the Lowest Rainfall. So I’m going to sort this data ascending. Now there's two ways to do this. You could open this drop-down, go to More Sort Options, choose To sending based on sum of rain, but it's also possible just to come here into Data, A to Z to get things sorted from lowest to highest. But I don't want to see just the top 5 years, so the Years with the Lowest Rainfall, I come here to the Year heading, open this little drop-down and choose Value Filters. And I’m looking for Bottom 5. Well, there is no filter for Bottom 5. Ahh, but this one for top ten is incredibly powerful. Alright, it doesn't have to be top. It can be top or bottom. It doesn't have to be 10; it can be 5. So, ask for the Top 5 Items based on sum of rain, click OK. And there is our report.
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.
Λοιπόν, θέλω να ευχαριστήσω τον Ken για την αποστολή του. Θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2063.xlsm
