Excel: tabella Pivot ordinata per giorno della settimana e mese
Le tabelle pivot di Excel offrono la possibilità di effettuare un report giornaliero datato per mese, trimestre o anno.
Ma non vi era, fino a poco tempo fa, un metodo integrato utile ad effettuare il report per un determinato giorno della settimana.
Con la funzionalità Get & Transform introdotta in Excel 2016, è finalmente possibile produrre un report di questo tipo.
In precedenza, avreste dovuto utilizzare formule quali =TEXT(A2,“DDDD”) per aggiungere una nuova colonna alla vostra sorgente dati ed ottenere il giorno della settimana.
La nuova funzionalità Column From Examples all’interno delle Power Query vi fornisce una funzionalità nuova e più efficace.
Purtroppo, la feature si limita alle versioni Windows di Excel 2016 e successive.
Per testarla, iniziate con un data set che includa una colonna Data.
Recatevi nella tab Home, Formatta come tabella per confermare i dati come una tabella.
Poi, selezionando una cella con la data scelta, recatevi nella tab Dati
Nella sezione Recupera e Trasforma Dati, cliccate sulla voce Da tabella/intervallo.
In questo modo, si aprirà l’Editor Power Query.
Cliccate sulla heading della colonna Data. Nella tab Aggiungi colonna scegliete la voce Colonna da Esempi.
La data presente nella prima riga è 01/01/2018, era un lunedì.
Colonna da Esempi crea a questo punto una nuova Column1 posizionata alla parte destra dello schermo.
Scrivete “Monday” nella casella e Power Query suggerirà “Day of Week from Date.” (Giorno della settimana dalla Data).
Cliccate Invio ed una nuova colonna verrà aggiunta alla finestra di preview dati mostrandovi il corrispondente giorno della settimana per ogni data presente nella tabella.
La barra formula, mostrerà la funzione Power Query utilizzata per creare la colonna: Date.DayofWeekName([Date]). Questa formula illustra quello che per i veterani di Excel può essere frustrante: le formule in Power Query sono sensibili a maiuscole-minuscole.
In excel, una formula puà essere scritta così =text(A2,“DDDD”) o così =TEXT(A2,“dddd”) oppure ancora così =TeXt(a2,“ddDd”) e restituire i risultati. In Power Query, invece, dovete utilizzare maiuscole e minuscole in maniera appropriata.
Fortunatamente, la funzione Colonna da Esempi genera automaticamente la formula: non dovete preoccuparvi di ricordare la giusta forma.
Ripetete la tecnica Colonna da Esempi per creare una colonna che contenga il nome del mese.
La barra della formula rivelerà una formula di Date.MonthName([Date]).
Se non vedete la funzionalità Colonna da Esempi
La funzionalità Colonna da Esempi è stata aggiunta lo scorso anno per i clienti Office 365.
Se non vedete l’icona corrispondente all’interno di Power Query, potete usare l’icona Custom Column e costruire una formula.
Scrivete =Date.DayofWeekName(. Poi trovate il campo data nella lista dei campi disponibili. Fate doppio-click per inserire il campo nella formula. Inserite una parentesi di chiusura e cliccate OK.
Dopo aver inserito i nuovi campi Giorno della settimana e Mese in Power Query, potete tornare ai dati su Excel.
Invece di utilizzare il pulsante Close and Load nella tab Home, aprite il menù a tendina Close and Load e scegliete Close and Load to…, che aprirà la finestra di dialogo Import Data.
Cliccate su Only Create Connection, poi Add This Data To The Data Model e poi OK.
A questo punto, sarete riportati su Excel. Vi apparirà un nuovo pannello Queries and Connections sull’estremità destra di Excel.
Recatevi sull’area del foglio di lavoro in cui volete appaia la tabella pivot.
Scegliete Inserisci – Tabella Pivot. La casella di dialogo PivotTable verrà avrà spuntato di default la voce Use This Workbook’s Data Model.
Cliccate OK.
Il pannello PivotTable Fields vi presenterà due oggetti chiamati Table1.
Cercate quello rappresentato dall’icona arancione cilindrica. Questa è l’icona che indica la versione della tabella che contiene i campi extra.
Costruite la tabella pivot con i campi Weekday, Month e Sales.
Ordinamento Tabella Pivot
C’è ancora una seccatura riguardo le tabelle pivot in relazione al Data Model.
La tabella pivot non ordinerà i nomi dei giorni della settimana o dei mesi in ordine progressivo.
Quando vedete, ad esempio, dei mesi messi nella seguente sequenza: April, August, December, February significa che sono ordinati alfabeticamente.
Cliccate sulla freccetta a cascata all’interno delle celle B1 e A2.
In entrambi i casi, scegliete l’opzione More Sort. Nella finestra di ordinamento, scegliete Ascending by Weekday e cliccate poi sul pulsante More Options nella angolo in basso a sinistra della finestra. Nella sezione che si aprirà More Sort, deselezionate la voce “Sort automatically every time the report is updated“. A questo punto, scegliete la lista desiderata dal menù a tendina First Key Sort Order drop-down.
Dopo aver ordinato sia il campo Mese che Giorno della settimana, avrete una tabella pivot che vi mostra le vendite ordinate per mese e giorno della settimana.
A seconda del tipo di analisi che dovete fare, potreste utilizzare il Field Settings per modificare l’operazione da Somma a Media.
Applicate una scala di colori e vedrete il miglior giorno della settimana per le vendite.
I vantaggi delle Power Query
La prima volta in cui dovrete costruire una tabella pivot utilizzando Power Query sarà la più dura.
Costruirla con un metodo diverso dalle Power Query sarebbe più veloce. Se impiegate un maggior impegno in termini di tempo per definire i vari passaggi in Power Query però, ne beneficerete in semplicità e velocità nell’aggiornamento della tabella pivot.
Per aggiornare i dati, incollate una nuova data alla fine di quelle precedentemente inserite.
Nel pannello Queries & Connections, vi apparirà un’icona di Refresh. Cliccatevi sopra e tutti i passaggi inseriti precedentemente per aggiungere nuove colonne, avverranno in automatico. Aggiornate la tabella pivot e il report sarà aggiornato.
Le informazioni presenti in questo post sono prese dall’articolo: EXCEL: PIVOT TABLE BY WEEKDAY AND MONTH.
