Månad till datum - Excel-tips

Hur man visar månadsförsäljning i en pivottabell. Detta är ett Duelling Excel-avsnitt.

Kolla på video

Bills metod
Lägg till en hjälpcell med en MTD-formel
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Lägg till det fältet som en skivare där = True
Bonustips: Gruppens dagliga datum upp till år
Lägg till en beräkning utanför pivottabellen medan du undviker GetPivotData
Mike's strategi:
Förvandla data till en tabell med Ctrl + T. Detta gör att mer data kan läggas till i tabellen och uppdatera formlerna.
SUMIFS med DATE, MONTH, DAY-funktioner
Genom att trycka på F4 tre gånger låses en referens till bara kolumnen.
Se upp - om du drar en tabellformel åt sidan ändras kolumnerna. Kopiera och klistra in - inga problem
Med TEXT (datum, format. Trevligt knep med 1 för att infoga siffran 1 i texten

Videoutskrift

Bill Jelen: Hej, välkommen tillbaka. Det är dags för ytterligare en Duelling Excel Podcast. Jag är Bill Jelen från. Jag får sällskap av Mike Girvin från Excel Is Fun.

Detta är vårt avsnitt 181: Månad till datum pivottabell.

Tja, hej, dagens fråga - dagens idé för denna duell skickas in av Mike. Han säger, "Kan du skapa en månad till datum-rapport i en pivottabell?"

Låt oss gå. Så här är vad vi har, vi har två års datum från januari 2016 ända fram till 2017. Nu spelar jag självklart in detta i april, det är den 15 april just nu när jag spelar in min bit av duellen. Och så här har vi en pivottabell som visar dagar på vänster sida, kategori överst och intäkter i hjärtat av pivottabellen.

Nu, för att skapa en månad till datum-rapport, vad jag ska göra är att jag ska säga att jag kommer att lägga till en ny hjälpkolumn här i mina ursprungliga data och det kommer att kontrollera två saker. Och eftersom jag letar efter två saker ska jag använda OCH-funktionen måste båda sakerna vara sanna för att det ska vara månad till datum. Och jag ska använda en funktion här som heter I DAG. I DAG, okej, så jag vill veta om MÅNADEN I DAG ()) är = till MÅNADEN för det datumet där borta i kolumn A. Om det är sant, om det är den aktuella månaden, så med andra ord om det är april, då kolla och se om dagen för det datumet där i A2 är <= DAGEN I DAG. Det vackra är när vi öppnar den här arbetsboken i morgon eller en vecka från och med nu, kommer dagens dag automatiskt att uppdateras och vi dubbelklickar för att kopiera den.

Okej nu, vi måste få in dessa extra data i vår pivottabell så jag kommer hit Pivot Table, Analysera och det är inte så svårt att ändra datakällan, klicka bara på den stora knappen där och säg att vi vill gå över till kolumn D , klicka på OK. Okej, så nu har vi det extra fältet, jag ska infoga en skivare baserat på det månaden till datum-fältet och jag vill bara se hur vår månad till datum är sant. Behöver vi den delen för att vara så stor? Nej, vi kan förmodligen göra det till två kolumner och bara ha det diskret där ute på höger sida. Så nu har vi alla datum 2016 och alla datum 2017; även om det skulle vara riktigt coolt att jämföra dessa sida vid sida. Så jag ska ta det datumfältet och analysera. Jag ska gruppera fältet, jag kommer att gruppera det till bara år. Jag don 'bryr dig inte om de enskilda dagarna. Jag vill bara veta månad till datum. Var är vi nu? Så jag kommer att gruppera det till år och vi kommer att sluta med dessa två år där och jag ska sedan ordna om det här, sätta dessa år att gå igenom, kategorier att gå ner. Och nu ser jag var vi var förra året och var vi var i år. Okej nu, eftersom jag har gjort gruppering får jag inte längre skapa ett beräknat fält inuti pivottabellen. Om jag ville ha ett år-över-år-belopp där borta, skulle jag högerklicka, ta bort totalsumma, okej, och nu är vi, så,% förändring, vi är utanför ett pivottabell som pekar inuti pivottabellen . Vi måste se till att antingen stänga av GetPivotData eller bara bygga en formel så här: = J4 / I4-1 och det skapar en formel som vi kan kopiera ner utan problem alls, så.Okej, Mike, låt oss se vad du har.

Mike Girvin: Tack. Ja, jag skickade frågan till för att jag gjorde det med formler och jag inte kunde ta reda på hur jag skulle göra det med ett standardtabell och sedan kom jag ihåg att jag såg genom åren, göra en massa häftiga videor om hjälpkolumner och pivottabeller . Det är en vacker formel och en vacker lösning. Så det är hur man gör det med en pivottabell, låt oss se hur man gör det med en formel.

Nu gör jag det här två dagar efter att han gjorde det. F2 Jag har TODAY-funktionen som alltid kommer att vara datuminformationen för dagens aktuella datum som kommer att användas av formlerna här nere eftersom vi vill att den ska uppdateras. Jag har också använt en Excel-tabell och den heter FSales. Om jag Ctrl + Pil ner ser jag att det är 4/14 men jag vill kunna lägga till de senaste posterna och ha vår formeluppdatering inkluderad när vi hoppar till nästa månad. Ctrl + uppåtpil. Okej, jag har årskriterier som kolumnrubriker, kategorin som radrubriker och sedan kommer uppgifterna för månad och dag från den cellen. Så jag ska helt enkelt använda SUMIFS-funktionen eftersom vi lägger till med flera villkor, summan är här intäkterna, vi kommer att använda det fantastiska tricket för en Excel-tabell.Högst upp ser vi den svarta nedåtpekande pilen, BAM! Det sätter in rätt tabellnamn och sedan i hakparenteser fältnamnet, komma. Kriterier, vi måste använda Date två gånger, så jag börjar med Date. Klicka, det finns datumkolumnen, komma. Nu är jag i april, så jag måste skapa villkor> = till 1 april. Så jämförande operatörer "> =" i dubbla citat och jag ska gå med i det. Nu måste jag skapa en datumformel som alltid ser här och skapar den första i månaden för just detta år. Så jag ska använda DATE-funktionen. År, ja, jag har året rätt som kolumnrubrik och jag kommer att trycka på F4-tangenten en, två gånger för att låsa raden men inte kolumnen, så när den flyttar hit flyttar vi till 2017, komma, Månad - jag 'm kommer att använda månadsfunktionen för att få månad nummer 1 till 12. Det är vilken månad som helst i den cellen, F4 för att låsa den i alla riktningar, stänga parenteser och sedan komma, 1 det kommer alltid att vara den första av månad oavsett vilken månad det här är, nära parenteser.

Okej, så det är kriterierna. Det kommer alltid att vara> = den första i månaden, komma, kriterierna två kommer jag att få min Datum-kolumn, komma. Kriterier två, ja, detta kommer att vara <= den övre gränsen, så i "<=" och &. Jag ska fuska, titta på det här. Jag ska bara kopiera det här uppifrån eftersom det är samma sak, Ctrl-C Ctrl-V förutom dagen, vi måste använda DAY-funktionen och alltid få som vår övre gräns oavsett dagen från denna månad . F4 för att låsa den i alla riktningar, stäng parenteser på Datum. Okej, så det är våra kriterier två: komma. Kriterieområde 3, det är kategori. Där är det, komma och det finns vårt radhuvud. Så den här måste vi F4 en två tre gånger, låsa kolumnen men inte raden så när vi kopierar formeln, flyttar vi till Gizmo och Widget,nära parentes och det är formeln. Dra över, dubbelklicka och skicka ner den. Jag kan se att det finns problem. Jag borde komma till den sista cellen längst bort. Hit F2. Nu är standardbeteendet för tabellformelnomenklatur när du kopierar formlerna till sidan, de faktiska kolumnerna rör sig som om de vore blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det då de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och då undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.Dra över, dubbelklicka och skicka ner den. Jag kan se att det finns problem. Jag borde komma till den sista cellen längst bort. Hit F2. Nu är standardbeteendet för tabellformelnomenklatur när du kopierar formlerna till sidan, de faktiska kolumnerna rör sig som om de vore blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det då de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och sedan undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.Dra över, dubbelklicka och skicka ner den. Jag kan se att det finns problem. Jag borde komma till den sista cellen längst bort. Hit F2. Nu är standardbeteendet för tabellformelnomenklatur när du kopierar formlerna till sidan, de faktiska kolumnerna rör sig som om de vore blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det då de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och sedan undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.Jag borde komma till den sista cellen längst bort. Hit F2. Nu är standardbeteendet för tabellformelnomenklatur när du kopierar formlerna till sidan, de faktiska kolumnerna rör sig som om de vore blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det då de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och sedan undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.Jag borde komma till den sista cellen längst bort. Hit F2. Nu är standardbeteendet för tabellformelnomenklatur när du kopierar formlerna till sidan, de faktiska kolumnerna rör sig som om de vore blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det när de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och då undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.de faktiska kolumnerna rör sig som om de var blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det då de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och då undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.de faktiska kolumnerna rör sig som om de var blandade cellreferenser. Nu kan vi låsa dem men jag kommer inte att göra det den här gången. Lägg märke till när du kopierar ner det fungerar bra, men när du kopierar till sidan är det när de faktiska kolumnerna rör sig. Så titta på det här, jag går till Ctrl + C och Ctrl + V och då undviks F till kolumnerna från att röra sig när du kopierar den till sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.m går till Ctrl + C och Ctrl + V och sedan undviks F till kolumnerna från att röra sig när du kopierar den åt sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.m går till Ctrl + C och Ctrl + V och sedan undviks F till kolumnerna från att röra sig när du kopierar den åt sidan. Dubbelklicka och skicka ner den. Nu är vår% Change formel = slutbeloppet / startbeloppet -1, Ctrl + Enter, dubbelklicka och skicka ner det.

Nu, innan vi testar det, lägg nu till några nya poster. Jag vill faktiskt skapa den här etiketten här uppe så att den är dynamisk. Och hur jag ska göra det är att jag kommer att säga = underteckna och vi ska göra en textformel så när som helst vi vill ha text och en formel måste du lägga in den: "och jag kommer att skriva Sales Between, space ”& och nu måste jag extrahera från det enda datumet där, den första i månaden till slutet av månaden. Jag ska använda TEXT-funktionen. TEXT-funktionen kan ta ett sifferdatum eller serienummer, komma och använda anpassad nummerformatering i ”. Jag vill alltid se en förkortning på tre bokstäver för månaden, mmm, jag vill alltid ha den som den första. Nu om jag lägger en 1 här, kommatecken yyy, det fungerar inte. Vill se att det ger oss ett värde eller för att det inte gillar det 1. Men vi 'får infoga ett enda tecken om vi använder snedstreck framåt, det är i formatering av anpassat nummer. Mm och yy kommer att förstås av formatering av anpassat nummer som månad och år och nu kommer anpassat nummerformat att förstå att infoga siffran 1. F2 och nu ska vi helt enkelt: & "-" & TEXT för det kommatecknet och nu kommer vi Använd bara rakt sifferformatering: “mmm spaceD, yyy”) Ctrl + Enter.

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.

Okej, hej, jag vill tacka alla för att de kom in. Vi ses nästa gång för en annan Duelling Excel Podcast från och Excel är kul.

Nedladdning fil

Ladda ner exempelfilen här: Duel181.xlsm