Ultimo mese - Suggerimenti per Excel

Come mostrare le vendite da inizio mese a oggi in una tabella pivot. Questo è un episodio di Dueling Excel.

Guarda un video

Il metodo di Bill
Aggiungi una cella helper con una formula MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Aggiungi quel campo come Slicer dove = True
Suggerimento bonus: date giornaliere di gruppo fino a anni
Aggiungi un calcolo all'esterno della tabella pivot evitando GetPivotData
L'approccio di Mike:
Trasforma i dati in una tabella usando Ctrl + T. Ciò consente di aggiungere più dati alla tabella e l'aggiornamento delle formule.
SUMIFS con funzioni DATA, MESE, GIORNO
Premendo F4 tre volte si blocca un riferimento alla sola colonna.
Attenzione: se trascini lateralmente una formula Tabella, le colonne cambiano. Copia e incolla: nessun problema
Utilizzo di TEXT (data, formato. Bel trucco con 1 per inserire il numero 1 nel testo

Trascrizione del video

Bill Jelen: Ehi, bentornato. È il momento per un altro Podcast Dueling Excel. Sono Bill Jelen di. Sarò raggiunto da Mike Girvin di Excel Is Fun.

Questo è il nostro episodio 181: Tabella pivot del mese in corso.

Ehi, la domanda di oggi: l'idea di oggi per questo duello è stata inviata da Mike. Dice: "Puoi creare un rapporto Mese fino ad oggi in una tabella pivot?"

Bene, andiamo. Quindi ecco cosa abbiamo, abbiamo due anni di date da gennaio 2016 fino al 2017. Ora ovviamente sto registrando questo ad aprile, è il 15 aprile in questo momento quando sto registrando il mio pezzo del duello. E così qui abbiamo una tabella pivot che mostra i giorni sul lato sinistro, la categoria in alto e le entrate nel cuore della tabella pivot.

Ora, per creare un rapporto Mese ad oggi, quello che farò è dire che aggiungerò una nuova colonna di aiuto qui ai miei dati originali e questo verificherà due cose. E poiché sto verificando due cose che userò la funzione AND, entrambe le cose devono essere Vere affinché sia Mese in corso. E userò una funzione qui chiamata TODAY. OGGI, va bene, quindi voglio sapere se il MESE di OGGI ()) è = al MESE di quella data laggiù nella colonna A. Se è vero, se è il mese corrente, quindi in altre parole se è aprile, allora controlla e vedi se il giorno di quella data laggiù in A2 è <= GIORNO di OGGI. La cosa bella è che quando apriamo questa cartella di lavoro domani o tra una settimana, il giorno di oggi si aggiornerà automaticamente e faremo doppio clic per copiarlo.

Va bene ora, dobbiamo inserire questi dati extra nella nostra tabella pivot, quindi vengo qui Tabella pivot, Analizza e non è così difficile cambiare l'origine dati, basta fare clic su quel grande pulsante lì e dire che vogliamo andare alla Colonna D , fare clic su OK. Va bene, quindi ora abbiamo quel campo extra, inserirò un filtro dei dati basato su quel campo Mese ad oggi e voglio solo vedere come il nostro mese attuale è vero. Ora, abbiamo bisogno che Slice sia così grande? No, probabilmente possiamo fare in modo che siano due colonne e che sia discreto là fuori sul lato destro. Quindi ora quello che abbiamo sono tutte le date nel 2016 e tutte le date nel 2017; tuttavia, sarebbe davvero interessante confrontarli fianco a fianco. Quindi prenderò quel campo Data e analizzerò. Vado a Raggruppare il campo, lo raggrupperò solo in Anni. Io nonIn realtà mi interessa i singoli giorni. Voglio solo sapere il mese di oggi. Adesso, dove siamo? Quindi lo raggrupperò in Anni e finiremo con questi 2 anni lì e poi lo riorganizzerò, metterò quegli Anni da attraversare, Categorie per andare giù. E ora vedo dove eravamo l'anno scorso e dove eravamo quest'anno. Va bene ora, poiché ho finito di raggruppare non mi è più permesso creare un campo calcolato all'interno della tabella pivot. Se volessi avere un importo anno su anno laggiù, farei clic con il pulsante destro del mouse su Rimuovi totale generale, va bene, e ora siamo, quindi,% di variazione, siamo fuori da una tabella pivot che punta all'interno della tabella pivot . Dobbiamo assicurarci di disattivare GetPivotData o semplicemente creare una formula come questa: = J4 / I4-1 e questo crea una formula che possiamo copiare senza problemi, come quella.Va bene, Mike, vediamo cosa hai.

Mike Girvin: Grazie ,. Sì, ho inviato la domanda a perché l'ho fatto con le formule e non riuscivo a capire come farlo con una tabella pivot standard e poi mi sono ricordato di aver visto negli anni, fare una serie di fantastici video su colonne helper e tabelle pivot . Questa è una bella formula e una bella soluzione. Ecco come farlo con una tabella pivot, andiamo a vedere come farlo con una formula.

Ora, lo sto facendo due giorni dopo che l'ha fatto. F2 Ho la funzione TODAY che sarà sempre le informazioni sulla data per la data corrente di oggi che verrà utilizzata dalle formule qui sotto perché vogliamo che si aggiorni. Ho anche usato una tabella Excel e si chiama FSales. Se premo Ctrl + Freccia giù, vedo che è 4/14 ma voglio essere in grado di aggiungere gli ultimi record e includere l'aggiornamento delle nostre formule quando passiamo al mese successivo. Ctrl + Freccia su. Va bene, ho Criteri anno come intestazioni di colonna, Categoria come intestazioni di riga e quindi i dettagli per mese e giorno verranno da quella cella. Quindi userò semplicemente la funzione SUMIFS poiché stiamo aggiungendo con più condizioni, l'intervallo di somma qui è le entrate, useremo quel fantastico trucco per una tabella Excel.Proprio in alto vediamo quella freccia nera rivolta verso il basso, BAM! Ciò inserisce il nome della tabella corretto e quindi tra parentesi quadre il nome del campo, virgola. Intervallo di criteri, dovremo usare Date due volte, quindi inizierò con Date. Fare clic, c'è la colonna della data, virgola. Ora sono in aprile, quindi devo creare la condizione> = al 1 ° aprile. Quindi operatori comparativi "> =" tra virgolette doppie e mi unisco. Ora devo creare una formula di data che guardi sempre qui e crei il primo del mese per questo particolare anno. Quindi userò la funzione DATA. Anno, beh, ho l'anno giusto come intestazione della colonna e premerò il tasto F4 una, due volte per bloccare la riga ma non la colonna, quindi quando si sposta qui ci sposteremo al 2017, virgola, il Mese - I 'userò la funzione MESE per ottenere il numero del mese da 1 a 12. Ovunque sia il mese in quella cella, F4 per bloccarlo in tutte le direzioni, chiudere le parentesi e poi la virgola, 1 sarà sempre il primo mese indipendentemente dal mese, chiudere le parentesi.

Va bene, quindi questo è il criterio. Sarà sempre> = il primo del mese, virgola, intervallo di criteri due Vado a ottenere la mia colonna Data, virgola. Secondo il criterio, beh, questo sarà <= il limite superiore, quindi in "<=" e &. Sto per imbrogliare, guarda questo. Lo copierò da quassù poiché è la stessa cosa, Ctrl-C Ctrl-V tranne per il giorno, dobbiamo usare la funzione GIORNO e ottenere sempre come limite superiore qualunque sia il giorno di questo particolare mese . F4 per bloccarlo in tutte le direzioni, chiudere le parentesi in Data. Va bene, quindi questo è il nostro secondo criterio: virgola. Intervallo di criteri 3, è Categoria. Eccola, virgola e c'è la nostra intestazione di riga. Quindi questo dobbiamo F4 uno due tre volte, bloccare la colonna ma non la riga, quindi quando copiamo la formula verso il basso, ci spostiamo su Gizmo e Widget,chiudere la parentesi e questa è la formula. Trascina, fai doppio clic e invialo. Vedo che ci sono problemi. È meglio che arrivi all'ultima cella diagonalmente più lontana. Premi F2. Ora il comportamento predefinito per Nomenclatura formula tabella è quando si copiano le formule a lato, le colonne effettive si spostano come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e quindi evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.Trascina, fai doppio clic e invialo. Vedo che ci sono problemi. È meglio che arrivi all'ultima cella diagonalmente più lontana. Premi F2. Ora il comportamento predefinito per Nomenclatura formula tabella è quando si copiano le formule a lato, le colonne effettive si spostano come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e poi questo evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.Trascina, fai doppio clic e invialo. Vedo che ci sono problemi. È meglio che arrivi all'ultima cella diagonalmente più lontana. Premi F2. Ora il comportamento predefinito per Nomenclatura formula tabella è quando si copiano le formule a lato, le colonne effettive si spostano come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e quindi evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.È meglio che arrivi all'ultima cella diagonalmente più lontana. Premi F2. Ora il comportamento predefinito per Nomenclatura formula tabella è quando si copiano le formule a lato, le colonne effettive si spostano come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e quindi evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.È meglio che arrivi all'ultima cella diagonalmente più lontana. Premi F2. Ora il comportamento predefinito per Nomenclatura formula tabella è quando si copiano le formule a lato, le colonne effettive si spostano come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e quindi evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.le colonne effettive si muovono come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e quindi evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.le colonne effettive si muovono come se fossero riferimenti di cella misti. Ora potremmo bloccarli ma questa volta non lo farò. Ora nota quando lo copi funziona bene, ma quando copi sul lato è allora che le colonne effettive si spostano. Quindi guarda questo, vado su Ctrl + C e Ctrl + V e quindi evita che F sulle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.m andando su Ctrl + C e Ctrl + V e poi questo evita che F alle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.m andando su Ctrl + C e Ctrl + V e poi questo evita che F alle colonne si sposti quando lo copi di lato. Fare doppio clic e inviarlo. Ora la nostra formula di cambio% = l'importo finale / l'importo iniziale -1, Ctrl + Invio, fare doppio clic e inviarlo.

Ora, prima di testarlo, ora aggiungi alcuni nuovi record. In realtà voglio creare questa etichetta qui, quindi è dinamica. E il modo in cui lo farò è che dico = segno e creeremo una formula di testo, quindi ogni volta che vogliamo testo e una formula, devi inserirli: "e io andando a digitare Vendite tra, spazio "& e ora ho bisogno di estrarre da quella singola data lì, dal primo del mese alla fine del mese. Userò la funzione TEXT. La funzione TESTO può assumere un numero di date o numeri di serie, virgola e utilizzare una formattazione numerica personalizzata in ”. Voglio sempre vedere l'abbreviazione di tre lettere per il mese, mmm, lo voglio sempre come primo. Ora, se metto un 1 qui, virgola yyy, non funzionerà. Vuole vedere che questo ci dà un valore o perché non gli piace 1. Ma noi 'è consentito inserire un singolo carattere se usiamo la barra, che è nella formattazione del numero personalizzato. Il mm e il aa saranno compresi dalla formattazione del numero personalizzato come mese e anno e ora il formato del numero personalizzato capirà di inserire il numero 1. F2 e ora andremo semplicemente a: & "-" & TESTO di quella virgola e ora noi userò solo la formattazione del numero semplice: "mmm spaceD, yyy") Ctrl + Invio.

Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.

That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.

Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.

And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.

Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.

Va bene, beh, ehi, voglio ringraziare tutti per essere passati. Ci vediamo la prossima volta per un altro Podcast di Dueling Excel da ed Excel Is Fun.

Download file

Scarica il file di esempio qui: Duel181.xlsm