Welches sind die unteren fünf Monate des Niederschlags? Erfahren Sie, wie Sie dieses Problem mithilfe einer Pivot-Tabelle lösen.
Schau Video
- 2013 erstellte Pivot-Tabellen können 2007 nicht aktualisiert werden
- Sie müssen die Pivot-Tabelle 2007 erstellen, damit sie aktualisiert werden kann
- Ziel ist es, die fünf Monate mit dem geringsten Niederschlag zu finden
- Erstellen Sie eine große Pivot-Tabelle mit monatlichen Niederschlägen
- Nach aufsteigendem Niederschlag sortieren
- Wechseln Sie zu Tabellenform
- Verwenden Sie die Wertfilter, Top 10, um die unteren 5 zu erhalten!
- Entfernen Sie die Gesamtsummenreihe
- Beachten Sie, dass ein Gleichstand dazu führen kann, dass dieser Bericht 6 oder mehr Zeilen enthält
- Wenn Sie die erste Pivot-Tabelle haben, kopieren Sie sie an ihren Platz und erstellen Sie die nächste Pivot-Tabelle
- Wenn Sie von einem Wertefeld in ein anderes wechseln, müssen Sie die Sortierung und den Filter erneut durchführen
- Wenn Sie von einem Zeilenfeld in ein anderes wechseln, müssen Sie die Sortierung und den Filter erneut durchführen
- Bonus-Tipp: Erstellen einer Pivot-Tabelle mit Zeilen und Spalten
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2063: Die oberen oder unteren fünf Monate oder Jahre mithilfe einer Pivot-Tabelle.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage von Ken. Ken hat hier eine erstaunliche Tabelle mit jahrelangen täglichen Niederschlagsdaten, die bis ins Jahr 1999 zurückreichen. Eine wirklich beeindruckende Sammlung von Daten, die er hat, und Ken hatte einige erstaunliche Formeln, um zu versuchen, den Monat mit den meisten Niederschlägen zu finden am wenigsten Niederschlag. Jetzt, wissen Sie, wird dies mit einem Pivot-Tisch viel einfacher.
Okay, Ken hat noch nie eine Pivot-Tabelle erstellt. Um die Sache noch komplizierter zu machen, bin ich hier in Excel 2016, Ken verwendet Excel 2007. Bei meinen Pivot-Tabellen, die ich 2016 erstellt habe, konnte er ihn sehen, aber er konnte sie nicht aktualisieren. Okay, dieses Video ist also Pivot-Tabelle 101: So erstellen Sie Ihre erste Pivot-Tabelle.
Erstens hat Ken dieses Datum in Spalte A, echte Daten, sind wir gut? Das ist großartig, oder? Und dann benutze ich - füge hier an der Funktion = YEAR ein paar zusätzliche Formeln ein, um das Jahr zu erhalten, = MONTH, um den Monat zu erhalten, = DAY. Und dann verketten Sie diese wieder zusammen. Ich habe tatsächlich die Funktion = TEXT in JJJJ-MM verwendet, so dass ich Jahr und Monat weniger habe. Dies sind Kens Daten, die Regendaten hier und dann habe ich einige Formeln hinzugefügt. Ken's hat weniger als 0,5 Millimeter, zählt nicht als Regentag, also gibt es dort eine Formel. Und dann, aus Episode 735, gehen Sie zurück und sehen Sie sich das an, um zu sehen, wie ich den Streifen von Tagen mit Regen und den Streifen von Tagen ohne Regen berechnet habe. Nun, das wird heute nicht verwendet, das wurde für etwas anderes verwendet.
Also kommen wir hierher. Und zuerst möchten wir die Daten für unsere Pivot-Tabelle auswählen. In den meisten Fällen können Sie jetzt einfach alle Daten auswählen, sodass Sie hier nur eine Zelle auswählen können. In diesem Fall gibt es jedoch einen Namensbereich, der die Daten bis 2016 definiert. Wir sitzen hier - ich ' Ich nehme dies Anfang 2017 auf. Kens Daten reichen nur bis Ende 2016. Wir werden also nur diese Daten auswählen. Und dann auf der Registerkarte Einfügen - Registerkarte Einfügen. In Excel 2007 werden die Pivot-Tabellen zum ersten Mal von der Registerkarte Daten zurück zur Registerkarte Einfügen verschoben. Also wählen wir: Pivot-Tabelle, und unsere ausgewählten Daten werden die Daten sein, aus denen wir erstellen. Und wir wollen nicht zu einem neuen Arbeitsblatt gehen, wir werden zu einem vorhandenen Arbeitsblatt gehen und ich werde das hier in Spalte gleich setzen - lass uns mit der Spalte N gehen.Letztendlich möchte ich, dass diese Datenjahre mit dem geringsten Niederschlag genau hier angezeigt werden, aber ich weiß, dass beim Erstellen dieser Pivot-Tabelle viel mehr Zeilen als diese 5 benötigt werden, oder? Also baue ich es hier zur Seite, okay. Und wir klicken auf OK.
Okay, jetzt bekommen Sie Folgendes. Hier wird der Bericht abgelegt. Hier finden Sie eine Liste aller Felder, die wir in unserem kleinen Datensatz haben. Und dann haben wir, denn was ich schrecklich benenne, fällt aus. Zeilen sind die Elemente, die Sie auf der linken Seite haben möchten. Werte sind das, was Sie zusammenfassen möchten, und dann sind Spalten die Dinge, die Sie oben haben möchten. Wir könnten dies am Ende verwenden. Wir werden heute keine Filter verwenden. Wir bauen also nur eine einfache kleine Pivot-Tabelle mit dem Gesamtniederschlag pro Jahr, also nehme ich das Feld Jahr und ziehe es hier nach unten auf die linke Seite. Es gibt eine Liste aller unserer Jahre, okay? Und dann denke darüber nach. Um diese Formel hier ohne Pivot-Tabelle zu erhalten, würden Sie was tun? SUMIF, oh ja, SUMIF. Sie können sogar SUMIFs in Excel 2007 verwenden.Ich werde das Regenfeld nehmen und das hierher ziehen. Achten Sie jetzt auf die - Sehen Sie, sie haben Count of Rain gewählt, weil die Daten ein paar Tage enthalten oder Ken eine leere Zelle hat, eine leere Zelle anstelle einer 0. Und ja, wir sollten das durchgehen und beheben, aber Es sind Kens Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.Achten Sie jetzt auf die - Sehen Sie, sie haben Count of Rain gewählt, weil die Daten ein paar Tage enthalten oder Ken eine leere Zelle hat, eine leere Zelle anstelle einer 0. Und ja, wir sollten das durchgehen und beheben, aber Es sind Kens Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.Achten Sie jetzt auf die - Sehen Sie, sie haben Count of Rain gewählt, weil die Daten ein paar Tage enthalten oder Ken eine leere Zelle hat, eine leere Zelle anstelle einer 0. Und ja, wir sollten das durchgehen und beheben, aber Es sind Kens Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.s weil die Daten ein paar Tage enthalten oder Ken eine leere Zelle hat, eine leere Zelle anstelle einer 0. Und ja, wir sollten das durchgehen und beheben, aber es sind Kens Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.s weil die Daten ein paar Tage enthalten oder Ken eine leere Zelle hat, eine leere Zelle anstelle einer 0. Und ja, wir sollten das durchgehen und beheben, aber es sind Kens Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.s Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.s Daten. Es sind Daten im Wert von 20 Jahren. Ich werde nicht einmal Find & Replace verwenden. Okay, ich bin nur … Aus irgendeinem Grund werde ich respektieren, dass Ken einen Grund hat, diese zu haben, so wie ich ihnen erlauben werde, leer zu bleiben. Und hier, unter Anzahl der Regenfälle, werde ich sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, in Ordnung? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.Ich werde sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, okay? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.Ich werde sicherstellen, dass Sie eine Zelle in der Spalte Anzahl der Regenfälle auswählen, zu Feldeinstellungen gehen und diese von Anzahl zu Summe ändern, okay? Es gibt also all unsere Jahre und wie viel Regen wir in jedem Jahr hatten. Und wir suchen die Jahre mit dem niedrigsten Niederschlag.
Okay, eine Sache, die mich nervt, ist dieses Wort hier Row Labels. Das ist uns in Excel 2007 passiert, okay? Und ich - 10 Jahre später verachte ich das immer noch. Ich gehe zur Registerkarte "Design", öffne das Berichtslayout und sage "In tabellarischer Form anzeigen". In diesem speziellen Fall ist es eine echte Überschrift des Jahres, oder? Und ich bevorzuge die echte Überschrift. Im Moment wollen wir nur die Spitze oder in diesem Fall die Jahre mit dem geringsten Niederschlag sehen. Also werde ich diese Daten aufsteigend sortieren. Jetzt gibt es zwei Möglichkeiten, dies zu tun. Sie können diese Dropdown-Liste öffnen, zu Weitere Sortieroptionen gehen und "Senden basierend auf der Regensumme" auswählen. Es ist jedoch auch möglich, hier nur in "Daten" von A bis Z zu wechseln, um die Sortierung vom niedrigsten zum höchsten Wert durchzuführen. Aber ich möchte nicht nur die Top 5 Jahre sehen, also die Jahre mit dem geringsten Niederschlag.Ich komme hierher zur Überschrift Jahr, öffne dieses kleine Dropdown-Menü und wähle Wertfilter. Und ich suche nach Bottom 5. Nun, es gibt keinen Filter für Bottom 5. Ahh, aber dieser für die Top Ten ist unglaublich leistungsfähig. Okay, es muss nicht top sein. Es kann oben oder unten sein. Es muss nicht 10 sein; es kann 5 sein. Fragen Sie also nach den Top 5 Artikeln basierend auf der Regensumme und klicken Sie auf OK. Und da ist unser Bericht.
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.
Nun, hey, ich möchte Ken dafür danken, dass er diese Frage eingeschickt hat. Ich möchte Ihnen dafür danken, dass Sie vorbeischauen. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2063.xlsm
