Maand tot nu toe - Excel-tips

Hoe u maand-tot-datum-verkopen in een draaitabel kunt weergeven. Dit is een Dueling Excel-aflevering.

Bekijk video

Bill's methode
Voeg een hulpcel toe met een MTD-formule
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Voeg dat veld toe als een slicer waarbij = True
Bonustip: Groepeer dagelijkse datums tot jaren
Voeg een berekening toe buiten de draaitabel en vermijd GetPivotData
Mike's aanpak:
Verander de gegevens in een tabel met Ctrl + T. Hierdoor kunnen meer gegevens aan de tabel worden toegevoegd en kunnen de formules worden bijgewerkt.
SUMIFS met functies DATUM, MAAND, DAG
Als u driemaal op F4 drukt, wordt een verwijzing naar alleen de kolom vergrendeld.
Pas op: als u een tabelformule zijwaarts sleept, veranderen de kolommen. Kopiëren en plakken - geen problemen
TEXT gebruiken (datum, formaat. Leuke truc met 1 om het nummer 1 in de tekst in te voegen

Videotranscriptie

Bill Jelen: Hey, welkom terug. Het is tijd voor nog een Dueling Excel Podcast. Ik ben Bill Jelen van. Ik krijg gezelschap van Mike Girvin van Excel Is Fun.

Dit is onze Aflevering 181: Draaitabel van maand tot nu.

Nou, de vraag van vandaag - het idee van vandaag voor dit duel is ingezonden door Mike. Hij zegt: "Kun je een maand-tot-datum-rapport maken in een draaitabel?"

Goed, laten we gaan. Dus hier is wat we hebben, we hebben data voor twee jaar vanaf januari 2016 tot en met 2017. Nu neem ik dit natuurlijk op in april, het is nu 15 april als ik mijn stuk van het duel opneem. En dus hebben we hier een draaitabel met Dagen aan de linkerkant, Categorie bovenaan en Omzet in het hart van de draaitabel.

Om nu een maand tot nu toe rapport te maken, ga ik zeggen dat ik hier een nieuwe helperkolom ga toevoegen aan mijn oorspronkelijke gegevens en dat gaat op twee dingen controleren. En omdat ik op twee dingen controleer, ga ik de EN-functie gebruiken, beide dingen moeten Waar zijn om de maand tot nu toe te zijn. En ik ga hier een functie gebruiken genaamd TODAY. VANDAAG, oké, dus ik wil weten of de MAAND van TODAY ()) = is tot de MAAND van die datum daar in kolom A.Als dat waar is, als het de huidige maand is, dus met andere woorden als het april is, dan controleer en kijk of de dag van die datum daar in A2 <= DAG van VANDAAG is. Het mooie is dat wanneer we dit werkboek morgen of over een week openen, de dag van vandaag automatisch wordt bijgewerkt en we dubbelklikken om dat te kopiëren.

Oké, we moeten deze extra gegevens in onze draaitabel krijgen, dus ik kom hier Draaitabel, analyseer en het is niet zo moeilijk om de gegevensbron te wijzigen, klik gewoon op die grote knop daar en zeg dat we naar kolom D willen gaan , Klik OK. Oké, dus nu hebben we dat extra veld, ik ga een slicer invoegen op basis van dat veld Month To Date en ik wil alleen zien hoe onze Month To Date waar is. Hebben we die plak nu nodig om zo groot te zijn? Nee, we kunnen er waarschijnlijk van maken dat het uit twee kolommen bestaat en dat het daar aan de rechterkant een beetje onopvallend is. Dus wat we nu hebben zijn alle datums in 2016 en alle datums in 2017; hoewel het heel gaaf zou zijn om deze naast elkaar te vergelijken. Dus ik ga dat datumveld nemen en analyseren. Ik ga het veld groeperen, ik ga het groeperen tot slechts jaren. Ik don 'Het geeft eigenlijk niet om de individuele dagen. Ik wil gewoon de maand tot nu toe weten. Nu, waar zijn we? Dus ik zal het groeperen in jaren en we zullen eindigen met deze 2 jaar daar en ik ga dit herschikken, die jaren overzetten, categorieën die naar beneden gaan. En nu zie ik waar we vorig jaar waren en waar we dit jaar waren. Oké, omdat ik klaar ben met groeperen, mag ik niet langer een berekend veld in de draaitabel maken. Als ik daar een jaar-op-jaarbedrag wilde hebben, zou ik met de rechtermuisknop klikken, Eindtotaal verwijderen, oké, en nu zijn we, dus,% verandering, we staan buiten een draaitabel die naar de binnenkant van de draaitabel wijst . We moeten ervoor zorgen dat we GetPivotData uitschakelen of gewoon een formule als deze bouwen: = J4 / I4-1 en dat creëert een formule die we zonder gedoe kunnen kopiëren, zoals dat.Oké, Mike, laten we eens kijken wat je hebt.

Mike Girvin: Bedankt ,. Ja, ik heb de vraag gestuurd omdat ik het deed met formules en ik kon er niet achter komen hoe ik het moest doen met een standaard draaitabel en toen herinnerde ik me dat ik door de jaren heen een aantal coole video's had gezien over hulpkolommen en draaitabellen . Dat is een mooie formule en een mooie oplossing. Dus dat is hoe het te doen met een draaitabel, laten we eens kijken hoe het met een formule moet.

Nu doe ik dit twee dagen nadat hij het deed. F2 Ik heb de TODAY-functie die altijd de datuminformatie zal zijn voor de huidige datum van vandaag die zal worden gebruikt door de formules hier, omdat we willen dat deze wordt bijgewerkt. Ik heb ook een Excel-tabel gebruikt en deze heet FSales. Als ik Ctrl + Pijl-omlaag, zie ik dat het 4/14 is, maar ik wil in staat zijn om de nieuwste records toe te voegen en de update van onze formules te laten opnemen wanneer we naar de volgende maand gaan. Ctrl + pijl-omhoog. Oké, ik heb Jaarcriteria als kolomkoppen, de Categorie als rijkoppen, en dan komen de gegevens voor maand en dag uit die cel. Dus ik ga gewoon de SUMIFS-functie gebruiken, omdat we toevoegen met meerdere voorwaarden, het sombereik hier is de opbrengst, we gaan die geweldige truc gebruiken voor een Excel-tabel.Helemaal bovenaan zien we die zwarte naar beneden wijzende pijl, BAM! Dat plaatst de juiste tabelnaam en vervolgens tussen vierkante haken de veldnaam, komma. Criterium bereik, we zullen Date twee keer moeten gebruiken, dus ik ga beginnen met Date. Klik, daar is de datumkolom, komma. Nu ben ik in april, dus ik moet de voorwaarde> = tot 1 april maken. Dus vergelijkende operatoren "> =" tussen dubbele aanhalingstekens en ik ga erbij. Nu moet ik een datumformule maken die er altijd uitziet en de eerste van de maand voor dit specifieke jaar maakt. Dus ik ga de DATUM-functie gebruiken. Jaar, nou ik heb het jaar rechts als de kolomkop en ik ga één keer op de F4-toets drukken, twee keer om de rij te vergrendelen, maar niet de kolom, dus als het hierheen gaat, gaan we naar 2017, komma, de Maand - ik 'm ga de MAAND-functie gebruiken om het maandnummer 1 tot 12 te krijgen. Dat is de maand die zich in die cel bevindt, F4 om het in alle richtingen te vergrendelen, haakjes sluiten en dan komma, 1 het wordt altijd de eerste van de maand, ongeacht welke maand dit is, sluit haakjes.

Oké, dus dat zijn de criteria. Het zal altijd> = de eerste van de maand, komma, criteriabereik twee zijn. Ik krijg mijn kolom Datum, komma. Criterium twee: dit wordt <= de bovengrens, dus in "<=" en de &. Ik ga vals spelen, kijk hiernaar. Ik ga dit gewoon vanaf hier kopiëren omdat het hetzelfde is, Ctrl-C Ctrl-V behalve de dag, we moeten de functie DAG gebruiken en altijd als onze bovengrens krijgen, ongeacht de dag van deze specifieke maand . F4 om het in alle richtingen te vergrendelen, sluit haakjes op Datum. Oké, dus dat is ons criterium twee: komma. Criteriabereik 3, het is Categorie. Daar is het, komma en daar is onze rijkop. Dus deze moeten we F4 een twee drie keer, vergrendel de kolom maar niet de rij, dus als we de formule naar beneden kopiëren, gaan we naar Gizmo en Widget,haakje sluiten en dat is de formule. Sleep over, dubbelklik en verzend het naar beneden. Ik kan zien dat er problemen zijn. Ik kan maar beter naar de laatste cel komen die het verst weg is. Raak F2. Nu is het standaardgedrag voor de nomenclatuur van tabelformules dat wanneer u de formules opzij kopieert, de werkelijke kolommen worden verplaatst alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.Sleep over, dubbelklik en verzend het naar beneden. Ik kan zien dat er problemen zijn. Ik kan maar beter naar de laatste cel komen die het verst weg is. Raak F2. Nu is het standaardgedrag voor de nomenclatuur van tabelformules dat wanneer u de formules opzij kopieert, de werkelijke kolommen worden verplaatst alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.Sleep over, dubbelklik en verzend het naar beneden. Ik kan zien dat er problemen zijn. Ik kan maar beter naar de laatste cel komen die het verst weg is. Raak F2. Nu is het standaardgedrag voor de nomenclatuur van tabelformules dat wanneer u de formules opzij kopieert, de werkelijke kolommen worden verplaatst alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.Ik kan maar beter naar de laatste cel komen die het verst weg is. Raak F2. Nu is het standaardgedrag voor de nomenclatuur van tabelformules dat wanneer u de formules opzij kopieert, de werkelijke kolommen worden verplaatst alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.Ik kan maar beter naar de laatste cel komen die het verst weg is. Raak F2. Nu is het standaardgedrag voor de nomenclatuur van tabelformules dat wanneer u de formules opzij kopieert, de werkelijke kolommen worden verplaatst alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.de werkelijke kolommen bewegen alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.de werkelijke kolommen bewegen alsof het gemengde celverwijzingen zijn. Nu kunnen we ze op slot doen, maar dat ga ik deze keer niet doen. Merk nu op dat wanneer u het naar beneden kopieert, het goed werkt, maar wanneer u naar de zijkant kopieert, worden de werkelijke kolommen verplaatst. Dus let op, ik ga naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer je het naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.m gaat naar Ctrl + C en Ctrl + V en dat voorkomt dat F naar de kolommen verschuift wanneer u deze naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.m gaat naar Ctrl + C en Ctrl + V en dan wordt voorkomen dat F naar de kolommen beweegt wanneer u deze naar de zijkant kopieert. Dubbelklik en verstuur het. Nu onze% Change-formule = het eindbedrag / het beginbedrag -1, Ctrl + Enter, dubbelklik en verzend het naar beneden.

Nu, voordat we het gaan testen, voegt u nu enkele nieuwe records toe. Ik wil dit label hier eigenlijk maken, zodat het dynamisch is. En de manier waarop ik dat ga doen is: ik ga zeggen = teken en we gaan een tekstformule doen, dus elke keer dat we tekst en een formule willen, moet je het invoeren: "en ik ben ik ga Verkoop tussen, spatie ”& typen en nu moet ik uit die ene datum daar halen, de eerste van de maand tot het einde van de maand. Ik ga de TEXT-functie gebruiken. De TEXT-functie kan een getal, datums of serienummers, komma's en een aantal aangepaste getalnotaties gebruiken in ”. Ik wil altijd een drieletterige afkorting voor de maand zien, mmm, ik wil het altijd als eerste. Als ik hier een 1 plaats, komma jjj, dan zal dat niet werken. Wil zien dat dat ons een waarde geeft of omdat het dat niet leuk vindt 1. Maar we 'opnieuw toegestaan om een enkel teken in te voegen als we een schuine streep gebruiken, dat is in aangepaste nummeropmaak. De mm en de jj zullen worden begrepen door de aangepaste getalnotatie als maand en jaar en nu zal de aangepaste getalnotatie begrijpen hoe het nummer 1 moet worden ingevoegd. F2 en nu gaan we gewoon naar: & "-" & TEKST van die komma en nu gebruik gewoon gewone getalnotatie: “mmm spatieD, jjj”) 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.

Oké, nou, ik wil iedereen bedanken voor het langskomen. We zien je de volgende keer voor nog een Dueling Excel Podcast van en Excel Is Fun.

Download bestand

Download het voorbeeldbestand hier: Duel181.xlsm