Quali sono gli ultimi cinque mesi di pioggia? Scopri come risolvere questo problema utilizzando una tabella pivot.
Guarda un video
- Le tabelle pivot create nel 2013 non possono essere aggiornate nel 2007
- È necessario creare la tabella pivot nel 2007 per consentirne l'aggiornamento
- L'obiettivo è trovare i cinque mesi con meno precipitazioni
- Crea una grande tabella pivot con le precipitazioni mensili
- Ordina per precipitazione crescente
- Cambia in formato tabulare
- Usa i filtri valore, primi 10, per ottenere i primi 5!
- Rimuovere la riga del totale complessivo
- Tieni presente che un pareggio potrebbe far sì che questo rapporto ti dia 6 o più righe
- Una volta che hai la prima tabella pivot, copiala in posizione e crea la successiva tabella pivot
- Quando si passa da un campo valore a un altro, è necessario ripetere l'ordinamento e il filtro
- Quando si passa da un campo riga a un altro, è necessario ripetere l'ordinamento e il filtro
- Suggerimento bonus: creazione di una tabella pivot con righe e colonne
Trascrizione del video
Impara Excel da Podcast, Episodio 2063: I cinque mesi o gli anni superiori o inferiori utilizzando una tabella pivot.
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi inviata da Ken. Ken ha un foglio di calcolo straordinario qui con anni e anni e anni di date di pioggia giornaliera, che risalgono al 1999. Una raccolta di dati davvero impressionante che ha, e Ken aveva alcune formule sorprendenti per cercare di trovare il mese con la maggior parte delle precipitazioni, a meno precipitazioni. Quindi ora, sai, sarà molto più semplice con una tabella pivot.
Va bene ora, Ken non ha mai creato una tabella pivot e per complicare ulteriormente le cose, sono qui in Excel 2016, Ken usa Excel 2007. Le mie tabelle pivot che ho creato nel 2016, poteva vederlo ma non poteva aggiornarle. Bene, quindi questo video è Tabella pivot 101: come creare la tua prima tabella pivot.
Primo, Ken ha questa data nella colonna A, date reali, siamo a posto? È fantastico, vero? E poi uso: inserisco un paio di formule extra qui nella funzione = ANNO per ottenere l'anno, la funzione = MESE per ottenere il mese, la funzione = GIORNO. E poi concatenarli di nuovo insieme, ho effettivamente usato la funzione = TEXT in AAAA-MM, in questo modo ho anno e mese in calo. Questi sono i dati di Ken, i dati della pioggia qui e poi ho aggiunto alcune formule. Ken ha qualcosa di meno di 0,5 millimetri, non conta come un giorno di pioggia quindi c'è una formula lì. E poi, dall'episodio 735, torna indietro e dai un'occhiata a quello per vedere come ho calcolato la serie di giorni con pioggia e la serie di giorni senza pioggia. Ora non verrà usato oggi, è stato usato per qualcos'altro.
Quindi, veniamo qui. E per prima cosa, vogliamo selezionare i dati per la nostra tabella pivot. Ora, nella maggior parte dei casi, potresti semplicemente selezionare tutti i dati in modo da poter scegliere solo una cella qui, ma in questo caso, c'è un intervallo di nomi che definisce i dati solo attraverso, in questo caso, 2016. Siamo seduti qui- Io " Sto registrando questo all'inizio del 2017. I dati di Ken sono validi solo fino alla fine del 2016. Quindi selezioneremo solo quei dati. E poi nella scheda Inserisci - scheda Inserisci. Excel 2007, è la prima volta che le tabelle pivot si spostano dalla scheda Dati alla scheda Inserisci. Quindi scegliamo: Tabella pivot ei nostri dati selezionati saranno i dati da cui costruiremo. E non vogliamo andare a un nuovo foglio di lavoro, andremo a un foglio di lavoro esistente e lo metterò proprio qui in Colonna - andiamo con la Colonna N.Ora, alla fine, voglio che questi dati Anni con precipitazioni più basse appaiano proprio qui, ma so che mentre sto costruendo questa tabella pivot, avrà bisogno di molte più righe di quelle 5, giusto? Quindi, lo sto costruendo di lato qui, va bene. E facciamo clic su OK.
Bene, ora ecco cosa ottieni. Qui è dove andrà il rapporto ed ecco un elenco di tutti i campi che abbiamo nel nostro piccolo set di dati. E poi abbiamo, per quello che chiamo orribilmente chiamato abbandona. Le righe sono gli elementi che desideri sul lato sinistro. I valori sono ciò che vuoi riassumere e quindi le colonne sono le cose che vuoi in alto. Potremmo usarlo alla fine. Oggi non useremo i filtri. Quindi, stiamo solo costruendo una semplice piccola tabella pivot con le precipitazioni totali per anno, quindi prendo il campo Anno e lo trascino qui sul lato sinistro. C'è un elenco di tutti i nostri anni, va bene? E poi, pensaci. Per ottenere questa formula qui senza una tabella pivot, cosa faresti? SUMIF, oh sì, SUMIF. Potresti persino utilizzare SUMIF in Excel 2007. Quindi,Prenderò il campo Rain e lo trascinerò qui. In questo momento fai attenzione al - Vedi, hanno scelto Count of Rain, è perché ci sono alcuni giorni nei dati o Ken ha una cella vuota, una cella vuota invece di 0. E sì, dovremmo passare attraverso e aggiustarlo ma sono i dati di Ken. Valgono 20 anni di dati. Non ho intenzione di andare avanti nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò il fatto che Ken abbia un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.In questo momento fai attenzione al - Vedi, hanno scelto Count of Rain, è perché ci sono alcuni giorni nei dati o Ken ha una cella vuota, una cella vuota invece di 0. E sì, dovremmo passare attraverso e aggiustarlo ma sono i dati di Ken. Valgono 20 anni di dati. Non ho intenzione di andare avanti nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò che Ken ha un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.In questo momento fai attenzione al - Vedi, hanno scelto Count of Rain, perché ci sono alcuni giorni nei dati o Ken ha una cella vuota, una cella vuota invece di uno 0. E sì, dovremmo passare attraverso e aggiustarlo ma sono i dati di Ken. Valgono 20 anni di dati. Non ho intenzione di andare avanti nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò il fatto che Ken abbia un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.s perché ci sono pochi giorni nei dati o Ken ha una cella vuota, una cella vuota invece di uno 0. E sì, dovremmo esaminarlo e aggiustarlo, ma sono i dati di Ken. Valgono 20 anni di dati. Non ho intenzione di continuare nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò che Ken ha un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.s perché ci sono alcuni giorni nei dati o Ken ha una cella vuota, una cella vuota invece di uno 0. E sì, dovremmo esaminarlo e aggiustarlo, ma sono i dati di Ken. Valgono 20 anni di dati. Non ho intenzione di continuare nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò che Ken ha un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.dati di s. Valgono 20 anni di dati. Non ho intenzione di continuare nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò che Ken ha un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.dati di s. Valgono 20 anni di dati. Non ho intenzione di continuare nemmeno usando Trova e sostituisci. Va bene, sono solo … Per qualsiasi motivo rispetterò il fatto che Ken abbia un motivo per averli, come se permettessi loro di rimanere in bianco. E qui, sotto Count of Rain, mi assicurerò di scegliere una cella nella colonna Count of Rain, andare su Impostazioni sul campo e cambiarlo da Count a Sum, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.Mi assicurerò di scegliere una cella nella colonna Conteggio delle piogge, andare su Impostazioni campo e cambiarla da Conteggio a Somma, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.Mi assicurerò di scegliere una cella nella colonna Conteggio delle piogge, andare su Impostazioni campo e cambiarla da Conteggio a Somma, va bene? Quindi ci sono tutti i nostri anni e quanta pioggia abbiamo avuto in ogni anno. E stiamo cercando gli anni con le precipitazioni più basse.
Va bene ora, una cosa che mi infastidisce è questa parola qui Row Labels. È iniziato a succedere a noi in Excel 2007, va bene? E io - 10 anni dopo lo disprezzo ancora. Vado alla scheda Progettazione, apro Layout report e dico Mostra in formato tabulare e tutto ciò che fa. In questo caso particolare c'è un vero titolo dell'anno, giusto? E preferisco la vera intestazione. In questo momento, vogliamo vedere solo il massimo o, in questo caso, gli anni con le precipitazioni più basse. Quindi ordinerò questi dati in modo crescente. Ora ci sono due modi per farlo. È possibile aprire questo menu a discesa, andare su Altre opzioni di ordinamento, scegliere Per inviare in base alla somma della pioggia, ma è anche possibile entrare qui in Dati, dalla A alla Z per ordinare le cose dal più basso al più alto. Ma non voglio vedere solo i primi 5 anni, quindi gli anni con le precipitazioni più basse,Vengo qui all'intestazione Anno, apro questo piccolo menu a discesa e scegli Value Filters. E sto cercando il Bottom 5. Beh, non ci sono filtri per il Bottom 5. Ahh, ma questo per i primi dieci è incredibilmente potente. Va bene, non deve essere il massimo. Può essere in alto o in basso. Non deve essere 10; può essere 5. Quindi, chiedi i primi 5 articoli in base alla somma della pioggia, fai clic su OK. E c'è il nostro rapporto.
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.
Beh, ehi, voglio ringraziare Ken per avermi inviato questa domanda. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2063.xlsm
