Quels sont les cinq derniers mois de précipitations? Apprenez à résoudre ce problème à l'aide d'un tableau croisé dynamique.
Regarder la vidéo
- Les tableaux croisés dynamiques créés en 2013 ne peuvent pas être actualisés en 2007
- Vous devez créer le tableau croisé dynamique en 2007 pour lui permettre d'être actualisé
- L'objectif est de trouver les cinq mois les moins pluvieux
- Créez un grand tableau croisé dynamique avec les précipitations par mois
- Trier par précipitations ascendantes
- Changer en forme tabulaire
- Utilisez les filtres de valeur, Top 10, pour obtenir les 5 derniers!
- Supprimer la ligne Grand Total
- Notez qu'une égalité peut amener ce rapport à vous donner 6 lignes ou plus
- Une fois que vous avez le premier tableau croisé dynamique, copiez-le en place et créez le prochain tableau croisé dynamique
- Lorsque vous passez d'un champ de valeur à un autre, vous devez refaire le tri et le filtrage
- Lorsque vous passez d'un champ de ligne à un autre, vous devez refaire le tri et le filtrage
- Astuce bonus: créer un tableau croisé dynamique avec des lignes et des colonnes
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2063: Les cinq premiers mois ou années en utilisant un tableau croisé dynamique.
Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui envoyée par Ken. Ken a une feuille de calcul incroyable ici avec des années et des années et des années de dates de précipitations quotidiennes, remontant à 1999. Une collection vraiment impressionnante de données dont il dispose, et Ken avait des formules étonnantes pour essayer de trouver le mois avec le plus de précipitations, à moindre pluie. Alors maintenant, vous savez, ce sera beaucoup plus facile avec un tableau croisé dynamique.
Très bien maintenant, Ken n'a jamais créé de tableau croisé dynamique et pour compliquer davantage les choses, je suis ici dans Excel 2016, Ken utilise Excel 2007. Mes tableaux croisés dynamiques que j'ai créés en 2016, il pouvait le voir mais il ne pouvait pas les rafraîchir. Très bien, cette vidéo s'intitule Tableau croisé dynamique 101: Comment créer votre premier tableau croisé dynamique.
Premièrement, Ken a cette date dans la colonne A, les vraies dates, ça va? C'est génial, non? Et puis j'utilise - insérez quelques formules supplémentaires ici à la fonction = YEAR pour obtenir l'année, la fonction = MONTH pour obtenir le mois, la fonction = DAY. Et puis les concaténer ensemble, j'ai en fait utilisé la fonction = TEXT dans AAAA-MM, de cette façon j'ai une année et un mois vers le bas. Ce sont les données de Ken, les données de pluie ici et puis j'ai ajouté quelques formules. Ken a rien de moins que 0,5 millimètre, ne compte pas comme un jour de pluie donc il y a une formule là-bas. Et puis, à partir de l'épisode 735, revenez en arrière et jetez un œil à cela pour voir comment j'ai calculé la série de jours avec pluie et la série de jours sans pluie. Maintenant, cela ne va pas être utilisé aujourd'hui, cela a été utilisé pour autre chose.
Alors, nous venons ici. Et d'abord, nous voulons sélectionner les données de notre tableau croisé dynamique. Maintenant, dans la plupart des cas, vous pouvez simplement sélectionner toutes les données pour ne choisir qu'une cellule ici, mais dans ce cas, il y a une plage de noms qui définit les données juste à travers, dans ce cas, 2016. Nous sommes assis ici - Je ' Je l'ai enregistré au début de 2017. Les données de Ken ne remontent qu'à la fin de 2016. Nous allons donc sélectionner uniquement ces données. Et puis sur l'onglet Insertion - onglet Insertion. Excel 2007, c'est la première fois que les tableaux croisés dynamiques passent de l'onglet Données à l'onglet Insertion. Nous choisissons donc: Tableau croisé dynamique, et nos données sélectionnées seront les données à partir desquelles nous construisons. Et, nous ne voulons pas aller à une nouvelle feuille de calcul, nous allons aller à une feuille de calcul existante et je vais la mettre juste ici dans la colonne - allons-y avec la colonne N.Maintenant, en fin de compte, je veux que ces données Années avec les précipitations les plus faibles apparaissent ici, mais je sais que lorsque je construis ce tableau croisé dynamique, il va falloir beaucoup plus de lignes que ces 5, non? Donc, je construis ça sur le côté ici, d'accord. Et nous cliquons sur OK.
Très bien, voici ce que vous obtenez. C'est là que le rapport va aller et voici une liste de tous les champs que nous avons dans notre petit ensemble de données. Et puis nous avons, pour ce que j'appelle horriblement nommé abandonne. Les lignes sont les éléments que vous voulez sur le côté gauche. Les valeurs sont ce que vous voulez résumer, puis les colonnes sont les choses que vous voulez en haut. Nous pourrions l'utiliser à la fin. Nous n'allons pas utiliser les filtres aujourd'hui. Donc, nous construisons simplement un petit tableau croisé dynamique avec les précipitations totales par année, donc je prends le champ Année et le fais glisser ici vers le côté gauche. Il y a une liste de toutes nos années, d'accord? Et puis, pensez-y. Pour obtenir cette formule ici sans tableau croisé dynamique, vous feriez quoi? SUMIF, oh ouais, SUMIF. Vous pouvez même utiliser le retour de SUMIF dans Excel 2007. Donc,Je vais prendre le champ Rain et le faire glisser ici. En ce moment, faites attention aux - Vous voyez, ils ont choisi Count of Rain, c'est parce qu'il y a quelques jours dans les données ou que Ken a une cellule vide, une cellule vide au lieu d'un 0. Et oui, nous devrions passer en revue et corriger cela mais ce sont les données de Ken. Cela représente 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.En ce moment, faites attention aux - Vous voyez, ils ont choisi Count of Rain, c'est parce qu'il y a quelques jours dans les données ou que Ken a une cellule vide, une cellule vide au lieu d'un 0. Et oui, nous devrions passer en revue et corriger cela mais ce sont les données de Ken. Cela représente 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.En ce moment, faites attention aux - Vous voyez, ils ont choisi Count of Rain, c'est parce qu'il y a quelques jours dans les données ou que Ken a une cellule vide, une cellule vide au lieu d'un 0. Et oui, nous devrions passer en revue et corriger cela mais ce sont les données de Ken. Cela représente 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.s parce qu'il y a quelques jours dans les données ou que Ken a une cellule vide, une cellule vide au lieu d'un 0. Et oui, nous devrions passer en revue et corriger cela, mais ce sont les données de Ken. Cela représente 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.s parce qu'il y a quelques jours dans les données ou que Ken a une cellule vide, une cellule vide au lieu d'un 0. Et oui, nous devrions passer en revue et corriger cela, mais ce sont les données de Ken. Cela représente 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.s données. C'est 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.s données. Cela représente 20 ans de données. Je ne vais même pas utiliser Find & Replace. D'accord, je suis juste- Pour une raison quelconque, je vais respecter le fait que Ken a une raison de les avoir, comme je vais leur permettre de rester vierges. Et ici, sous Count of Rain, je vais m'assurer de choisir une cellule dans la colonne Count of Rain, aller dans les paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.Je vais m'assurer de choisir une cellule dans la colonne Count of Rain, accéder aux paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.Je vais m'assurer de choisir une cellule dans la colonne Count of Rain, accéder aux paramètres du champ et changer cela de Count en Sum, d'accord? Il y a donc toutes nos années et la quantité de pluie que nous avons eue chaque année. Et nous recherchons les années avec les plus faibles précipitations.
Très bien maintenant, une chose qui me dérange est ce mot ici Row Labels. Cela a commencé à nous arriver dans Excel 2007, d'accord? Et je - 10 ans plus tard, je méprise toujours cela. Je vais dans l'onglet Conception, j'ouvre la mise en page du rapport et je dis Afficher sous forme tabulaire, et tout cela. Dans ce cas particulier, il y a un vrai cap d'Année, non? Et je préfère la vraie rubrique. À l'heure actuelle, nous voulons voir juste le haut ou dans ce cas, les années avec les plus faibles précipitations. Je vais donc trier ces données par ordre croissant. Maintenant, il y a deux façons de faire cela. Vous pouvez ouvrir cette liste déroulante, aller à Plus d'options de tri, choisir À envoyer en fonction de la somme de la pluie, mais il est également possible d'entrer simplement ici dans Données, de A à Z pour que les choses soient triées du plus bas au plus élevé. Mais je ne veux pas voir uniquement les 5 meilleures années, donc les années avec les plus faibles précipitations,J'arrive ici à la rubrique Année, ouvrez ce petit menu déroulant et choisissez Filtres de valeur. Et je cherche Bottom 5. Eh bien, il n'y a pas de filtre pour Bottom 5. Ahh, mais celui-ci pour le top dix est incroyablement puissant. D'accord, ça n'a pas à être top. Cela peut être en haut ou en bas. Il n'est pas nécessaire que ce soit 10; cela peut être 5. Alors, demandez les 5 meilleurs éléments en fonction de la somme des précipitations, cliquez sur OK. Et il y a notre rapport.
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.
Eh bien, je tiens à remercier Ken d'avoir envoyé cette question. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2063.xlsm
