Hónaptól a dátumig - Excel tippek

Hogyan mutathatja meg a hónapról napra történő eladásokat egy forgatótáblában. Ez egy Dueling Excel epizód.

Videót nézni

Bill módszere
Adjon hozzá egy segítő cellát MTD képlettel
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Adja hozzá ezt a mezőt szeletelőként, ahol = Igaz
Bónusztipp: Csoportos napi dátumok akár évekig
Adjon hozzá egy számítást a kimutatási táblán kívül, elkerülve ezzel a GetPivotData-t
Mike megközelítése:
Az adatokat táblázatokká alakíthatja a Ctrl + T billentyűkombinációval. Ez lehetővé teszi további adatok hozzáadását a táblához és a képletek frissítését.
SUMIFS a DATE, MONTH, DAY függvényekkel
Az F4 háromszori megnyomásával a hivatkozás csak az oszlopra záródik.
Vigyázzon - ha a táblázat képletét oldalra húzza, az oszlopok megváltoznak. Másolás és beillesztés - nincs probléma
A TEXT (dátum, formátum. Szép trükk az 1-el az 1-es szám beszúrásához a szövegbe

Videó átirat

Bill Jelen: Hé, üdvözlöm újra. Itt az ideje egy újabb Dueling Excel Podcastnak. Bill Jelen vagyok. Mike Girvin lesz az Excel Is Fun munkatársa.

Ez a 181. rész: Hónaptól a dátumig kimutató táblázat.

Nos, hé, a mai kérdés - a párharc mai ötletét Mike küldte be. Azt mondja: "Hozhat létre hónapról napra jelentést egy forgatótáblában?"

Rendben, menjünk. Tehát itt van, van két év dátumunk 2016 januárjától egészen 2017-ig. Most természetesen ezt áprilisban rögzítem, most április 15-e van, amikor a párbaj darabját rögzítem. Tehát itt van egy pivot táblázat, amelyen a bal oldalon látható a Napok, a tetején a Kategória és a Bevétel a forgóasztal szívében.

Most, hogy létrehozzak egy Month To Date jelentést, azt fogom mondani, hogy új segítő oszlopot adok ide az eredeti adataimhoz, és ez két dolgot fog ellenőrizni. És mivel két dolgot vizsgálok, és az AND függvényt fogom használni, mindkét dolognak igaznak kell lennie ahhoz, hogy hónapról napra legyen. És itt fogok használni egy TODAY nevű funkciót. MA, rendben, ezért szeretném tudni, hogy a MA HÓNAPJA () = az adott dátum HÓNAPJAI-e ott az A oszlopban. Ha ez igaz, ha ez az aktuális hónap, tehát más szóval ha április, akkor ellenőrizze, hogy az adott dátum napja ott A2-ben <= TODAY DAY-e. Gyönyörű dolog, amikor holnap vagy egy hét múlva kinyitjuk ezt a munkafüzetet, a mai nap automatikusan frissül, és duplán kattintva másoljuk le.

Rendben, most be kell vonnunk ezeket az extra adatokat a forgatótáblánkba, így ideérkezem a Pivot Table elemzésére, és nem olyan nehéz megváltoztatni az adatforrást, csak kattints arra a nagy gombra és mondd, hogy át akarunk menni a D oszlopba , kattintson az OK gombra. Rendben, szóval most megvan az a külön mező, be fogok illeszteni egy szeletelőt a hónaptól a dátumig mező alapján, és csak azt szeretném megtudni, hogy a hónapunk dátumunk mennyire igaz. Szükségünk van arra, hogy a Szelet ekkora legyen? Nem, valószínűleg két oszloppá tehetjük, és valahogy nem feltűnő a kint a jobb oldalon. Tehát most az összes dátum 2016-ban és minden dátum 2017-ben van; bár nagyon jó lenne ezeket egymás mellett összehasonlítani. Tehát ezt a Dátum mezőt fogom elemezni. Csoportosítom a mezőt, csak Évekig fogom csoportosítani. Én nemNem érdekelnek az egyes napok. Csak azt szeretném tudni, hogy Hónap-ig. Most hol vagyunk? Szóval csoportosítom évekre, és ezzel a 2 évvel ott leszünk, majd ezt át fogom rendezni, ezeket az éveket át kell tenni, a kategóriákat pedig lefelé. És most látom, hol voltunk tavaly és hol voltunk idén. Rendben, mert a csoportosítást elvégeztem, így már nem hozhatok létre számított mezőt a kimutató táblázatban. Ha azt szeretném, hogy évről évre összege legyen ott, akkor jobb egérgombbal kattintanék, a Teljes összeg eltávolítása, rendben, és most így vagyunk,% Változás, kívül állunk a kimutatótáblán, amely a forgótábla belsejébe mutat . Meg kell győződnünk arról, hogy kikapcsoljuk-e a GetPivotData-t, vagy csak felépítünk egy ilyen képletet: = J4 / I4-1, és ez létrehoz egy képletet, amelyet mindenféle szóváltás nélkül le tudunk másolni.Rendben, Mike, nézzük meg, mi van.

Mike Girvin: Köszönöm. Igen, azért küldtem a kérdést, mert képletekkel csináltam, és nem tudtam rájönni, hogyan kell csinálni egy szokásos pivot táblával, és eszembe jutott, hogy az évek során láttam, csináltam egy csomó jó videót a segítő oszlopokról és a pivot táblákról . Ez egy gyönyörű képlet és egy szép megoldás. Tehát így kell csinálni egy pivot táblával, nézzük meg, hogyan kell csinálni egy képlettel.

Most csinálom ezt két nappal azután, hogy megcsinálta. F2 Megvan a TODAY függvény, amely mindig a mai aktuális dátum dátuminformációja lesz, amelyet az itt található képletek fognak használni, mert frissíteni akarjuk. Használtam egy Excel táblázatot is, amelynek neve FSales. Ha Ctrl + Le nyíl, látom, hogy 4/14-es, de szeretnék hozzáadni a legfrissebb rekordokat, és a képleteinket is frissíteni kell, amikor a következő hónapra ugrunk. Ctrl + fel nyíl. Rendben, az Évfeltételek oszlopfejlécek vannak, a Kategória sorfejlécek, majd a hónap és a nap adatai abból a cellából származnak. Tehát egyszerűen a SUMIFS függvényt fogom használni, mivel több feltételt adunk hozzá, az összegtartomány itt a bevétel, ezt a nagyszerű trükköt fogjuk használni egy Excel-táblázathoz.Rögtön a tetején látjuk azt a fekete lefelé mutató nyíl, BAM! Ez beírja a megfelelő táblanevet, majd szögletes zárójelbe teszi a mező nevét, vesszőt. Kritériumtartomány, kétszer kell használnunk a Dátumot, ezért a Dátummal kezdem. Kattintson, ott a Dátum oszlop, vessző. Most áprilisban vagyok, ezért létre kell hoznom a> = feltételt április 1-ig. Tehát összehasonlító operátorok „> =” kettős idézőjelben, és csatlakozni fogok hozzá. Most létre kell hoznom egy dátumképletet, amely mindig ide néz, és létrehozza az adott év hónapjának első részét. Tehát a DATE függvényt fogom használni. Év, hát nekem az Év van az oszlopfejléc, és kétszer fogom lenyomni az F4 billentyűt, hogy lezárjam a sort, de az oszlopot nem, így amikor ide költözik, akkor 2017-re lépünk, vessző, Hónap - én 'A MONTH függvényt használom az 1-es szám 12-re számításához. Bármelyik hónap is fent van ebben a cellában, F4, hogy minden irányba rögzítse, zárójeleket zárjon be, majd vesszővel, 1 ez mindig az 1. lesz. hónap, függetlenül attól, hogy melyik hónapról van szó, zárójelek.

Rendben, tehát ez a kritérium. Mindig> = a hónap elsője lesz, vessző, második kritériumtartomány. Megkapom a Dátum oszlopomat, vesszőt. Második kritérium, nos, ez <= a felső határ lesz, tehát a „<=” -ban és a & -ban. Meg fogok csalni, nézze meg ezt. Csak átmásolom ezt innen fentről, mivel ugyanaz a dolog, Ctrl-C Ctrl-V, kivéve a Napot, használnunk kell a NAP funkciót, és mindig a felső határként kell megkapnunk, függetlenül attól a naptól, amely ettől a hónaptól függ. . F4, hogy minden irányba rögzítse, zárja be a zárójelet a Date-on. Rendben, szóval ez a kritériumunk: vessző. A 3. kritériumtartomány kategória. Ott van, vessző és ott van a sorfejlécünk. Tehát ezt az F4-et kell kétszer háromszor lezárnunk, az oszlopot lezárni, a sort azonban nem, így amikor a képletet lefelé másoljuk, a Gizmo és a Widget oldalra lépünkszoros zárójel és ez a képlet. Húzza át, kattintson duplán és küldje el. Látom, hogy baj van. Jobb, ha az utolsó cellába átlósan a legtávolabb kerülök. Hit F2. Most a táblázat képlet-nómenklatúra alapértelmezett viselkedése az, amikor a képleteket oldalra másolja, a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.Húzza át, kattintson duplán és küldje el. Látom, hogy baj van. Jobb, ha az utolsó cellába átlósan a legtávolabb kerülök. Hit F2. Most a táblázat képlet-nómenklatúra alapértelmezett viselkedése az, amikor a képleteket oldalra másolja, a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.Húzza át, kattintson duplán és küldje el. Látom, hogy baj van. Jobb, ha átlósan az utolsó cellába jövök. Hit F2. A táblázat képlet-nómenklatúra alapértelmezett viselkedése az, amikor a képleteket oldalra másolja, a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.Jobb, ha átlósan az utolsó cellába jövök. Hit F2. A táblázat képlet-nómenklatúra alapértelmezett viselkedése az, amikor a képleteket oldalra másolja, a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.Jobb, ha az utolsó cellába átlósan a legtávolabb kerülök. Hit F2. Most a táblázat képlet-nómenklatúra alapértelmezett viselkedése az, amikor a képleteket oldalra másolja, a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.a tényleges oszlopok úgy mozognak, mintha vegyes cellahivatkozások lennének. Most bezárhatnánk őket, de ezúttal nem fogom megtenni. Most vegye észre, amikor lemásolja, akkor jól működik, de amikor arra az oldalra másol, amikor a tényleges oszlopok mozognak. Tehát figyelje ezt, a Ctrl + C és a Ctrl + V billentyűkombinációra megyek, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.m a Ctrl + C és a Ctrl + V billentyűkombinációra megy, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.m a Ctrl + C és a Ctrl + V billentyűkombinációra megy, és ez elkerüli az F oszlopok elmozdulását, amikor oldalra másolja. Kattintson duplán, és küldje el. Most a% Change képletünk = a végösszeg / a kezdő összeg -1, Ctrl + Enter, kattintson duplán és küldje el.

Mielőtt tesztelnénk, most adjon hozzá néhány új rekordot. Valójában itt szeretném létrehozni ezt a címkét, így dinamikus. És ahogy ezt fogom tenni, azt mondom, hogy = sign, és meg fogunk csinálni egy Text képletet, így bármikor, amikor szöveget és képletet akarunk, be kell tenned: „és én beírom az Értékesítés között, szóköz ”szót, és most ki kell dolgoznom az egyetlen dátumot, a hónap első napjától a hónap végéig. A SZÖVEG funkciót fogom használni. A SZÖVEG függvény számadatokat vagy sorszámokat, vesszőt vehet fel, és használhat néhány egyedi számformázást a ”szövegben. Mindig szeretném látni a hónap hárombetűs rövidítését, mmm, mindig elsőnek akarom. Most ha ide teszek 1-et, vesszőköz yyy, az nem fog menni. Azt akarja látni, hogy ez értéket adjon nekünk, vagy mert ez nem tetszik nekik.Engedélyezhetünk egyetlen karakter beszúrását, ha előre perjelet használunk, ez az egyéni szám formátumban van. Az mm és az yy értékeket az egyéni szám formázása hónapként és évként fogja megérteni, és most az egyéni szám formátum megérteni fogja az 1-es szám beillesztését. F2, és most egyszerűen a következő vesszőre megyünk: & "-" & TEXT, és most mi Csak egyenes számformázást használ: „mmm szóköz, éééé”) 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.

Rendben, hát hé, szeretnék köszönetet mondani mindenkinek, hogy betértek. Legközelebb találkozunk egy másik Dueling Excel Podcast from and Excel Is Fun programmal.

Fájl letöltése

Töltse le a minta fájlt innen: Duel181.xlsm