Måned til dato - Excel-tips

Hvordan vise måned-til-dato salg i en pivottabell. Dette er en Dueling Excel-episode.

Se på video

Bills metode
Legg til en hjelpercelle med en MTD-formel
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Legg til det feltet som en Slicer hvor = True
Bonustips: Gruppedaglige datoer opp til år
Legg til en beregning utenfor pivottabellen mens du unngår GetPivotData
Mike's tilnærming:
Gjør dataene til en tabell ved hjelp av Ctrl + T. Dette gjør at flere data kan legges til tabellen og oppdatering av formlene.
SUMIFS med DATE, MONTH, DAY funksjoner
Ved å trykke F4 tre ganger låses en referanse til bare kolonnen.
Se opp - hvis du drar en tabellformel sidelengs, endres kolonnene. Kopier og lim inn - ingen problemer
Bruker TEKST (dato, format. Fint triks med 1 for å sette inn nummer 1 i teksten

Videoutskrift

Bill Jelen: Hei, velkommen tilbake. Det er på tide med nok en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får selskap av Mike Girvin fra Excel Is Fun.

Dette er vår episode 181: Måned til dato pivottabell.

Vel, hei, dagens spørsmål - dagens idé for denne duellen sendes inn av Mike. Han sier: "Kan du lage en måned til dato-rapport i en pivottabell?"

Greit, la oss gå. Så her er hva vi har, vi har to års verdier fra januar 2016 helt opp i 2017. Nå spiller jeg selvfølgelig opp dette i april, det er 15. april akkurat nå når jeg spiller inn stykket mitt i duellen. Og så her borte har vi et pivottabell som viser dager på venstre side, kategori over toppen og inntekter i hjertet av pivottabellen.

Nå, for å lage en måned til dato-rapport, skal jeg si at jeg skal legge til en ny hjelpekolonne her til de opprinnelige dataene mine, og det vil sjekke om to ting. Og fordi jeg ser etter to ting, skal jeg bruke AND-funksjonen, begge ting må være sant for at det skal være måned til dato. Og jeg skal bruke en funksjon her som heter I DAG. I DAG, ok, så jeg vil vite om MÅNEDEN I DAG ()) er = til MÅNEDEN på den datoen der borte i Kolonne A. Hvis det er sant, hvis det er den nåværende måneden, så med andre ord hvis det er april, så sjekk og se om dagen for den datoen der borte i A2 er <= DAG i DAG. Vakker ting er når vi åpner denne arbeidsboken i morgen eller en uke fra nå, vil dagen i dag automatisk oppdateres, og vi dobbeltklikker for å kopiere den ned.

Greit nå, vi må få disse ekstra dataene inn i pivottabellen vår, så jeg kommer hit Pivottabell, analyser, og det er ikke så vanskelig å endre datakilden, bare klikk den store knappen der og si at vi vil gå over til kolonne D , klikk OK. Ok, så nå har vi det ekstra feltet, jeg skal sette inn en skiver basert på det måned til dato-feltet, og jeg vil bare se hvordan vår måned til dato er sant. Trenger vi den delen å være så stor? Nei, vi kan sannsynligvis få det til å være to kolonner og bare ha det lite påtrengende der ute på høyre side. Så det vi har er alle datoene i 2016 og alle datoene i 2017; selv om det ville være veldig kult å sammenligne disse side om side ved side. Så jeg skal ta datofeltet og analysere. Jeg skal gruppere feltet, jeg skal gruppere det til bare år. Jeg gjør n'faktisk ikke bryr seg om de enkelte dagene. Jeg vil bare vite Måned til dato. Hvor er vi nå? Så jeg grupperer det til år, og vi vil ende opp med disse to årene der, og jeg skal deretter omorganisere dette, sette de årene som skal gå over, kategorier å gå ned. Og nå ser jeg hvor vi var i fjor og hvor vi var i år. Greit nå, fordi jeg har gjort gruppering, har jeg ikke lenger lov til å lage et beregnet felt inne i pivottabellen. Hvis jeg ønsket å ha et år-over-år beløp der borte, høyreklikker jeg, Fjern Grand Total, ok, og nå er vi, så,% Endre, vi er utenfor et pivottabell som peker inne i pivottabellen . Vi må sørge for å enten slå av GetPivotData eller bare bygge en formel som denne: = J4 / I4-1 og det skaper en formel som vi kan kopiere ned uten problemer i det hele tatt, slik.OK, Mike, la oss se hva du har.

Mike Girvin: Takk. Ja, jeg sendte spørsmålet til fordi jeg gjorde det med formler, og jeg kunne ikke finne ut hvordan jeg skulle gjøre det med et standard pivottabell, og så husket jeg å se gjennom årene, lage en haug med kule videoer om hjelpekolonner og pivottabeller . Det er en vakker formel og en vakker løsning. Så det er hvordan du gjør det med et pivottabell, la oss se hvordan du gjør det med en formel.

Nå gjør jeg dette to dager etter at han gjorde det. F2 Jeg har TODAY-funksjonen som alltid vil være datainformasjonen for dagens gjeldende dato som vil bli brukt av formlene her nede fordi vi vil at den skal oppdateres. Jeg har også brukt en Excel-tabell, og den heter FSales. Hvis jeg Ctrl + Pil ned, ser jeg at den er 4/14, men jeg vil være i stand til å legge til de nyeste postene og ha formeloppdateringen inkludert når vi hopper til neste måned. Ctrl + pil opp. OK, jeg har årskriterier som kolonneoverskrifter, kategorien som radoverskrifter, og deretter vil opplysningene for måned og dag komme fra den cellen. Så jeg skal rett og slett bruke SUMIFS-funksjonen siden vi legger til med flere forhold, sumområdet her er inntektene, vi skal bruke det flotte trikset for en Excel-tabell.Rett øverst ser vi den svarte pilen nedover, BAM! Det setter i det riktige tabellnavnet og deretter i firkantede parenteser feltnavnet, komma. Kriterier, vi må bruke Date to ganger, så jeg begynner med Date. Klikk, det er datakolonnen, komma. Nå er jeg i april, så jeg må opprette tilstanden> = til 1. april. Så komparative operatører “> =” i doble anførselstegn, og jeg kommer til å bli med på det. Nå må jeg lage en datoformel som alltid ser ut her og skaper den første i måneden for dette året. Så jeg skal bruke DATE-funksjonen. År, vel, jeg har året riktig som kolonneoverskrift, og jeg skal trykke på F4-tasten en, to ganger for å låse raden, men ikke kolonnen, så når den beveger seg herover flytter vi til 2017, komma, Måned - jeg 'm kommer til å bruke MÅNED-funksjonen for å få måned nummer 1 til 12. Det er uansett hvilken måned som er oppe i den cellen, F4 for å låse den i alle retninger, lukke parenteser og deretter komma, 1 det blir alltid den første av måned uansett hvilken måned dette er, nære parenteser.

Greit, så det er kriteriene. Det vil alltid være> = den første i måneden, komma, kriterieområdet to. Jeg skal få dato-kolonnen min, komma. Kriterier to, vel, dette kommer til å være <= den øvre grensen, så i "<=" og &. Jeg skal jukse, se på dette. Jeg skal bare kopiere dette herfra siden det er det samme, Ctrl-C Ctrl-V bortsett fra dagen, vi må bruke DAY-funksjonen og alltid få som vår øvre grense uansett dagen fra denne spesielle måneden er . F4 for å låse den i alle retninger, lukk parenteser på Date. Ok, så det er kriteriene to: komma. Kriterieområde 3, det er Kategori. Der er det, komma og det er radteksten vår. Så denne må vi F4 en to tre ganger, låse kolonnen, men ikke raden, så når vi kopierer formelen, flytter vi til Gizmo og Widget,nær parentes og det er formelen. Dra over, dobbeltklikk og send den ned. Jeg kan se at det er problemer. Jeg må helst komme til den siste cellen diagonalt lengst unna. Hit F2. Nå er standardoppførselen for Tabellformel Nomenklatur når du kopierer formlene til siden, de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden, er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.Dra over, dobbeltklikk og send den ned. Jeg kan se at det er problemer. Jeg må helst komme til den siste cellen diagonalt lengst unna. Hit F2. Nå er standardoppførselen for Tabellformel Nomenklatur når du kopierer formlene til siden, de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden, er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.Dra over, dobbeltklikk og send den ned. Jeg kan se at det er problemer. Jeg må helst komme til den siste cellen diagonalt lengst unna. Hit F2. Nå er standardoppførselen for Tabellformel Nomenklatur når du kopierer formlene til siden, de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden, er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.Jeg må helst komme til den siste cellen diagonalt lengst unna. Hit F2. Nå er standardoppførselen for tabellformelnomenklatur når du kopierer formlene til siden, de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden, er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.Jeg må helst komme til den siste cellen diagonalt lengst unna. Hit F2. Nå er standardoppførselen for Tabellformel Nomenklatur når du kopierer formlene til siden, de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden, er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden, er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.de faktiske kolonnene beveger seg som om de var blandede cellereferanser. Nå kan vi låse dem, men jeg kommer ikke til å gjøre det denne gangen. Legg merke til når du kopierer det ned, det fungerer bra, men når du kopierer til siden er det da de faktiske kolonnene beveger seg. Så se dette, jeg skal til Ctrl + C og Ctrl + V, og deretter unngår F til kolonnene fra å bevege seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.m går til Ctrl + C og Ctrl + V og deretter unngår du at F til kolonnene beveger seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.m går til Ctrl + C og Ctrl + V og deretter unngår du at F til kolonnene beveger seg når du kopierer den til siden. Dobbeltklikk og send den ned. Nå er vår% Endre formel = sluttbeløpet / startbeløpet -1, Ctrl + Enter, dobbeltklikk og send det ned.

Nå, før vi tester det, legg nå til noen nye poster. Jeg vil faktisk lage denne etiketten her oppe så den er dynamisk. Og slik jeg skal gjøre det skal jeg si = signere og vi skal gjøre en tekstformel, så når som helst vi vil ha tekst og en formel, må du legge den inn: "og jeg kommer til å skrive Sales Between, space ”& og nå må jeg trekke ut fra den eneste datoen der, den første i måneden til slutten av måneden. Jeg skal bruke TEKST-funksjonen. TEKST-funksjonen kan ta et antall datoer eller serienumre, komma og bruke noen tilpassede nummerformatering i ”. Jeg vil alltid se forkortelse på tre bokstaver for måneden, mmm, jeg vil alltid ha den som den første. Nå hvis jeg setter en 1 her, komma mellomrom, det fungerer ikke. Ønsker å se at det gir oss en verdi eller fordi det ikke liker det 1. Men vi 'får lov til å sette inn et enkelt tegn hvis vi bruker skråstrek, det er i formatering av tilpasset nummer. Mm og yy vil bli forstått av formatering av tilpasset nummer som måned og år, og nå vil tilpasset nummerformat forstå å sette inn nummeret 1. F2, og nå skal vi ganske enkelt: & "-" & TEKST for det kommaet og nå skal vi Bare bruk rett tallformatering: “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.

Greit, vel hei, jeg vil takke alle for at de var innom. Vi sees neste gang for en annen Dueling Excel Podcast fra og Excel er gøy.

Last ned fil

Last ned eksempelfilen her: Duel181.xlsm