Måned til dato - Excel-tip

Sådan vises måned-til-dato salg i en pivottabel. Dette er en Dueling Excel-episode.

Se video

  • Bills metode
  • Tilføj en hjælpercelle med en MTD-formel
  • =AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
  • Tilføj dette felt som en Slicer hvor = True
  • Bonustip: Gruppedaglige datoer op til år
  • Tilføj en beregning uden for pivottabellen, mens du undgår GetPivotData
  • Mike's tilgang:
  • Gør dataene til en tabel ved hjælp af Ctrl + T. Dette gør det muligt at tilføje flere data til tabellen og opdatere formlerne.
  • SUMIFS med DATE, MONTH, DAY-funktioner
  • Ved at trykke F4 tre gange låses en henvisning til kun kolonnen.
  • Pas på - hvis du trækker en tabelformel sidelæns, ændres kolonnerne. Copy & Paste - ingen problemer
  • Brug af TEKST (dato, format. Dejligt trick med 1 for at indsætte nummer 1 i teksten

Videoudskrift

Bill Jelen: Hej, velkommen tilbage. Det er tid til endnu en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får følgeskab af Mike Girvin fra Excel Is Fun.

Dette er vores afsnit 181: Måned til dato pivottabel.

Nå, hej, dagens spørgsmål - dagens idé til denne duel sendes af Mike. Han siger, "Kan du oprette en måned til dato-rapport i en pivottabel?"

Lad os gå. Så her er hvad vi har, vi har to års værdier fra januar 2016 helt op til 2017. Nu optager jeg selvfølgelig dette i april, det er den 15. april lige nu, når jeg optager mit stykke af duellen. Og så herovre har vi en pivottabel, der viser dage på venstre side, kategori over toppen og indtægter i hjertet af pivottabellen.

For at oprette en måned til dato-rapport skal jeg sige, at jeg vil tilføje en ny hjælpekolonne her til mine originale data, og det vil kontrollere, om der er to ting. Og fordi jeg tjekker for to ting, skal jeg bruge AND-funktionen, skal begge ting være sandt, for at det skal være måned til dato. Og jeg vil bruge en funktion her kaldet I DAG. I DAG, okay, så jeg vil vide, om MÅNEDEN I DAG ()) er = til MÅNEDEN på den dato derovre i kolonne A. Hvis det er sandt, hvis det er den aktuelle måned, så med andre ord, hvis det er april, så Kontroller og se om dagen for den dato derovre i A2 er <= DAG i DAG. Smuk ting er, når vi åbner denne projektmappe i morgen eller en uge fra nu, opdateres dagen i dag automatisk, og vi dobbeltklikker for at kopiere den ned.

Okay nu, vi er nødt til at få disse ekstra data ind i vores pivottabel, så jeg kommer her Pivottabel, analyser, og det er ikke så svært at ændre datakilden, bare klik på den store knap der og sig, at vi vil gå over til kolonne D , klik på OK. Okay, så nu har vi det ekstra felt, jeg vil indsætte en skiver baseret på det måned til dato-felt, og jeg vil kun se, hvordan vores måned til dato er sand. Har vi brug for det stykke for at være så stort? Nej, vi kan sandsynligvis gøre det til to kolonner og bare have det diskret derude på højre side. Så hvad vi nu har er alle datoer i 2016 og alle datoer i 2017; skønt det ville være rigtig sejt at sammenligne disse side om side ved side. Så jeg vil tage dette datofelt og analysere. Jeg skal gruppere feltet, jeg grupperer det op til bare år. Jeg don 'faktisk ikke bekymrer sig om de enkelte dage. Jeg vil bare vide Måned til dato. Hvor er vi nu? Så jeg grupperer det op til år, og vi ender med disse 2 år der, og jeg vil derefter omarrangere dette, sætte disse år til at gå på tværs, kategorier til at gå ned. Og nu ser jeg, hvor vi var sidste år, og hvor vi var i år. Okay nu, fordi jeg har lavet gruppering, har jeg ikke længere lov til at oprette et beregnet felt inde i pivottabellen. Hvis jeg ville have et år-over-år beløb derovre, ville jeg højreklikke på, Fjern Grand Total, okay, og nu er vi, så,% Change, vi er uden for en pivottabel, der peger inde i pivottabellen . Vi skal sørge for enten at slukke GetPivotData eller bare oprette en formel som denne: = J4 / I4-1, og der oprettes en formel, som vi overhovedet kan kopiere uden besvær, sådan.Okay, Mike, lad os se, hvad du har.

Mike Girvin: Tak. Ja, jeg sendte spørgsmålet til, fordi jeg gjorde det med formler, og jeg ikke kunne finde ud af, hvordan jeg gjorde det med et standard drejetabel, og så huskede jeg at have set gennem årene, lav en masse seje videoer om hjælpekolonner og drejetabeller . Det er en smuk formel og en smuk løsning. Så det er sådan, man gør det med en pivottabel, lad os se, hvordan man gør det med en formel.

Nu gør jeg det to dage efter, at han gjorde det. F2 Jeg har TODAY-funktionen, som altid vil være datainformationen for dagens aktuelle dato, der vil blive brugt af formlerne her nede, fordi vi ønsker, at den opdateres. Jeg har også brugt en Excel-tabel, og den hedder FSales. Hvis jeg Ctrl + Pil ned, ser jeg, at det er 4/14, men jeg vil være i stand til at tilføje de nyeste poster og have vores formelopdatering inkluderet, når vi hopper til næste måned. Ctrl + Pil op. Okay, jeg har årskriterier som kolonneoverskrifter, kategorien som rækkeoverskrifter, og derefter kommer oplysningerne for måned og dag fra den celle. Så jeg vil simpelthen bruge SUMIFS-funktionen, da vi tilføjer med flere betingelser, sumområdet her er indtægterne, vi skal bruge det store trick til en Excel-tabel.Lige øverst ser vi den sorte pil nedad, BAM! Det sætter det korrekte tabelnavn og derefter i firkantede parenteser feltnavnet, komma. Kriterier, vi bliver nødt til at bruge Date to gange, så jeg begynder med Date. Klik, der er datakolonnen, komma. Nu er jeg i april, så jeg skal oprette betingelsen> = til 1. april. Så komparative operatører “> =” i dobbelt anførselstegn, og jeg vil deltage i det. Nu skal jeg oprette en datoformel, der altid ser her ud og opretter den første i måneden for dette særlige år. Så jeg skal bruge DATE-funktionen. År, ja, jeg har året rigtigt som kolonneoverskrift, og jeg skal trykke på F4-tasten en to gange for at låse rækken, men ikke kolonnen, så når den bevæger sig herover, flytter vi til 2017, komma, Måned - jeg 'm skal bruge MÅNED-funktionen til at få måned nummer 1 til 12. Det er uanset hvilken måned der er ope i den celle, F4 for at låse den i alle retninger, lukke parenteser og derefter komma, 1 det bliver altid den første af måned uanset hvilken måned dette er, tæt parentes.

Okay, så det er kriterierne. Det vil altid være> = den første i måneden, komma, kriterierne to, jeg vil hente min dato-kolonne, komma. Kriterier to, ja, dette vil være <= den øvre grænse, så i “<=” og &. Jeg skal snyde, se dette. Jeg skal bare kopiere dette herfra, da det er det samme, Ctrl-C Ctrl-V bortset fra dagen, vi er nødt til at bruge DAY-funktionen og altid få som vores øvre grænse uanset dagen fra denne særlige måned er . F4 for at låse den i alle retninger, luk parenteser på dato. Okay, så det er vores kriterium to: komma. Kriterieområde 3, det er kategori. Der er det, komma, og der er vores rækkeoverskrift. Så denne skal vi F4 en to tre gange, låse søjlen, men ikke rækken, så når vi kopierer formlen ned, flytter vi til Gizmo og Widget,tæt parentes, og det er formlen. Træk over, dobbeltklik og send den ned. Jeg kan se, at der er problemer. Jeg må hellere komme til den sidste celle diagonalt længst væk. Hit F2. Nu er standardopførelsen for tabelformelnomenklatur, når du kopierer formlerne til siden, de aktuelle kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.Træk over, dobbeltklik og send den ned. Jeg kan se, at der er problemer. Jeg må hellere komme til den sidste celle diagonalt længst væk. Hit F2. Nu er standardopførelsen for tabelformelnomenklatur, når du kopierer formlerne til siden, de aktuelle kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.Træk over, dobbeltklik og send den ned. Jeg kan se, at der er problemer. Jeg må hellere komme til den sidste celle diagonalt længst væk. Hit F2. Nu er standardopførelsen for tabelformelnomenklatur, når du kopierer formlerne til siden, de aktuelle kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.Jeg må hellere komme til den sidste celle diagonalt længst væk. Hit F2. Nu er standardopførelsen for tabelformelnomenklatur, når du kopierer formlerne til siden, de aktuelle kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.Jeg må hellere komme til den sidste celle diagonalt længst væk. Hit F2. Nu er standardopførelsen for tabelformelnomenklatur, når du kopierer formlerne til siden, de aktuelle kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.de faktiske kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.de faktiske kolonner bevæger sig som om de var blandede cellereferencer. Nu kunne vi låse dem, men jeg vil ikke gøre det denne gang. Bemærk nu, når du kopierer det ned, det fungerer fint, men når du kopierer til den side, det er når de faktiske kolonner flytter. Så se dette, jeg går til Ctrl + C og Ctrl + V, og så undgår F til kolonnerne fra at bevæge sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.m går til Ctrl + C og Ctrl + V og derefter undgår det, at F til kolonnerne bevæger sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.m går til Ctrl + C og Ctrl + V og derefter undgår det, at F til kolonnerne bevæger sig, når du kopierer det til siden. Dobbeltklik og send det ned. Nu er vores% Change formel = slutbeløbet / startbeløbet -1, Ctrl + Enter, dobbeltklik og send det ned.

Nu, før vi tester det, skal du nu tilføje nogle nye poster. Jeg vil faktisk oprette denne etiket heroppe, så den er dynamisk. Og den måde, jeg vil gøre det på, vil jeg sige = underskrive, og vi skal lave en tekstformel, så når som helst vi vil have tekst og en formel, skal du sætte den i: "og jeg er går til at skrive Salg mellem, mellemrum ”& og nu skal jeg udtrække fra den eneste dato der, den første i måneden til slutningen af ​​måneden. Jeg vil bruge TEKST-funktionen. TEKST-funktionen kan tage et antal datoer eller serienumre, komma og bruge noget brugerdefineret nummerformatering i ”. Jeg vil altid se en forkortelse på tre bogstaver for måneden, mmm, jeg vil altid have den som den første. Hvis jeg nu sætter en 1 her, komma mellemrum yyy, fungerer det ikke. Ønsker at se, at det giver os en værdi, eller fordi det ikke kan lide det 1. Men vi 'får lov til at indsætte et enkelt tegn, hvis vi bruger skråstreg, det er i formatering af brugerdefineret nummer. Mm og yy forstås ved formatering af brugerdefineret nummer som måned og år, og nu vil brugerdefineret nummerformat forstå at indsætte nummeret 1. F2 og nu skal vi simpelthen: & “-” & TEKST for det komma, og nu skal vi Brug bare lige talformatering: “mmm spaceD, yyy”) Ctrl + Enter.

Lad os bare, før vi tilføjer nogle data, lad os bare ændre dette. At foregive, at i dag viste: 15-03-2017 bare sådan opdateres alle formler, og vores tekstformel er også Ctrl + Z. Lad os nu gå ned til bunden af ​​datasættet, Ctrl + Pil ned Jeg vil tilføje en ny post. Jeg er i den sidste celle i datasættet, jeg ramte Tab for at tilføje en ny post til vores datasæt. Jeg skal simpelthen kopiere denne post herover, Ctrl + Pil op, og der kan vi se forskellen. Hvis vi ønskede at kontrollere disse formelværdier mod dem, der gjorde: = relativ cellereference = klik på arket, skal vi klikke højre i I4. Vi kunne se vores formel deroppe, Ctrl + Enter. Faktisk vil jeg trække det ned. Ctrl + Enter befolket lige alt, hvad jeg havde fremhævet. Og selvfølgelig FALSE FALSE.Gæt hvad? = det beløb lige der -, klik på Ctrl + Pil ned, Ctrl + Backspace, så jeg vil trække det bare for at kontrollere og sikre nok, at det var det nøjagtige beløb, vi kunne se tilbage der.

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.

Okay hej, jeg vil gerne takke alle for at komme forbi. Vi ses næste gang til en anden Dueling Excel Podcast fra og Excel er sjovt.

Download fil

Download eksempelfilen her: Duel181.xlsm

Interessante artikler...