Mesec do datuma - nasveti za Excel

Kako prikazati prodajo od meseca do datuma v vrtilni tabeli. To je epizoda Dueling Excel.

Oglejte si video

Billova metoda
Dodajte pomožno celico s formulo MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
To polje dodajte kot rezalnik, kjer je = True
Bonus nasvet: Skupinski dnevni termini do let
Dodajte izračun zunaj vrtilne tabele, pri čemer se izogibajte GetPivotData
Mikeov pristop:
Podatke spremenite v tabelo s kombinacijo tipk Ctrl + T. To omogoča dodajanje več podatkov v tabelo in posodabljanje formul.
SUMIFS s funkcijami DATE, MONTH, DAY
Trikratno pritiskanje tipke F4 zaklene sklic samo na stolpec.
Pazite - če formulo tabele povlečete vstran, se stolpci spremenijo. Kopiraj in prilepi - brez težav
Uporaba BESEDILA (datum, oblika. Lep trik z 1, da v besedilo vstavite številko 1

Video zapis

Bill Jelen: Hej, dobrodošli nazaj. Čas je za še en dvobojni podcast Excel. Sem Bill Jelen iz. Pridružil se mi bo Mike Girvin iz Excela je zabavno.

To je naša epizoda 181: vrtilna tabela od meseca do datuma.

No, hej, današnje vprašanje - današnjo idejo za ta dvoboj je poslal Mike. Pravi: "Ali lahko v vrtilni tabeli ustvarite poročilo od meseca do datuma?"

V redu, gremo. Torej, evo, kar imamo, imamo dve leti datumov od januarja 2016 pa vse do leta 2017. Zdaj to seveda posnamem v aprilu, zdaj je 15. april, ko snemam svoj del dvoboja. Tako imamo tukaj vrtilno tabelo, ki na levi strani prikazuje dneve, kategorijo na vrhu in prihodke v osrčju vrtilne tabele.

Zdaj, da ustvarim poročilo od meseca do datuma, bom rekel, da bom tu dodal nov stolpec za pomoč svojim prvotnim podatkom in to bo preverilo dve stvari. In ker preverjam dve stvari, ki jih bom uporabil funkcijo AND, obe stvari morata biti resnični, da bo mesec do datuma. In tukaj bom uporabil funkcijo, imenovano DANES. DANES, v redu, zato želim vedeti, ali je MESEC DANES () = = MESEC tistega datuma tam v stolpcu A. Če je to res, če je trenutni mesec, torej z drugimi besedami, če je april, potem preverite in preverite, ali je dan tistega datuma tam v A2 <= DAN DANES. Čudovito je, da ko delovni zvezek odpremo jutri ali čez teden, se bo današnji dan samodejno posodobil in dvakrat kliknemo, da ga kopiramo.

Zdaj moramo te dodatne podatke spraviti v svojo vrtilno tabelo, zato pridem sem, vrtilno tabelo, analiziram in ni težko spremeniti vira podatkov, samo kliknite tam velik gumb in recite, da želimo iti v stolpec D , kliknite V redu. Torej, zdaj imamo to dodatno polje, namesto tega bom vstavil rezalnik, ki temelji na polju Month To Date in samo želim videti, kako resničen je naš mesec do datuma. Zdaj, ali moramo ta Rezina biti tako velika? Ne, verjetno lahko naredimo, da gre za dva stolpca, in to nekako nekako nevsiljivo na desni strani. Torej, zdaj imamo vse datume v letu 2016 in vse datume v letu 2017; čeprav bi bilo res super, če bi jih primerjali drug ob drugem. Torej bom vzel to datumsko polje in analiziral. Grupirala bom polje, razdelila ga bom na samo leta. Jaz nePravzaprav ne skrbi za posamezne dni. Zanima me samo mesec do danes. Kje smo zdaj? Torej bom razvrstil po letih in tam bomo končali ti dve leti, nato pa bom to preuredil, dal ta leta, da bodo šla čez, kategorije pa naj se spustijo. In zdaj vidim, kje smo bili lani in kje letos. Zdaj, ker sem razvrščal skupine, ne smem več ustvarjati izračunanega polja znotraj vrtilne tabele. Če bi hotel imeti medletni znesek tam, bi z desno miškino tipko kliknil Odstrani vsoto, v redu in zdaj smo, torej,% Sprememba, smo zunaj vrtilne tabele, usmerjene v vrtilno tabelo . Poskrbeti moramo, da izklopimo GetPivotData ali pa samo sestavimo formulo, kot je ta: = J4 / I4-1, ki ustvari formulo, ki jo lahko brez kakršnih koli težav, tako na primer, kopiramo.V redu, Mike, poglejmo, kaj imaš.

Mike Girvin: Hvala. Da, vprašanje sem poslal na, ker sem to naredil s formulami in nisem mogel ugotoviti, kako to narediti s standardno vrtilno tabelo, nato pa sem se spomnil, da sem v preteklih letih videl kopico kul videoposnetkov o pomožnih stolpcih in vrtilnih tabelah . To je čudovita formula in čudovita rešitev. Torej, kako to storiti s pivot tabelo, poglejmo, kako to narediti s formulo.

Zdaj to počnem dva dni po tem, ko je to storil. F2 Imam funkcijo DANES, ki bo vedno informacija o datumu za današnji trenutni datum, ki jo bodo uporabile formule tukaj spodaj, ker želimo, da se posodobi. Uporabil sem tudi Excelovo tabelo in se imenuje FSales. Če pritisnem Ctrl + puščica dol, vidim, da je 4/14, vendar želim dodati najnovejše zapise in vključiti posodobitve formul, ko skočimo na naslednji mesec. Ctrl + puščica navzgor. V redu, letna merila imam kot glave stolpcev, kategorijo kot glave vrstic, nato pa bodo iz te celice prišli podatki za mesec in dan. Torej bom preprosto uporabil funkcijo SUMIFS, saj dodajamo z več pogoji, obseg vsote je tu prihodek, uporabili bomo ta odličen trik za Excelovo tabelo.Takoj na vrhu vidimo tisto črno puščico navzdol, BAM! To vpiše pravilno ime tabele in nato v oglatih oklepajih ime polja, vejica. Obseg meril, datum bomo morali uporabiti dvakrat, zato bom začel z datumom. Kliknite, tam je stolpec z datumi, vejica. Zdaj sem v aprilu, zato moram ustvariti pogoj> = do 1. aprila. Torej primerjalni operatorji “> =” v dvojnih narekovajih in pridružil se jim bom. Zdaj moram ustvariti neko datumsko formulo, ki bo vedno videti tukaj in bo ustvarila prvi mesec v tem letu. Torej bom uporabil funkcijo DATE. Leto, no, leto imam pravico kot glavo stolpca in pritisnil bom tipko F4, dvakrat, da zaklenem vrstico, ne pa stolpca, zato se bomo, ko se premakne sem, premaknili na leto 2017, vejica, Mesec - jaz 'm bom uporabil funkcijo MONTH, da dobim mesec od 1 do 12. To je, kateri koli mesec je v tej celici, F4, da ga zaklene v vse smeri, zapre oklepaje in nato vejico, 1 vedno bo 1. od mesec, ne glede na mesec, zaprite oklepaje.

Torej, to so merila. Vedno bo> = prvi v mesecu, vejica, obseg meril dva. Dobil bom stolpec Datum, vejica. Merila dva, no, to bo <= zgornja meja, torej v "<=" in &. Goljufal bom, pazi to. To bom samo skopiral od tu, ker gre za isto stvar, Ctrl-C Ctrl-V, razen dneva, uporabiti moramo funkcijo DAY in vedno dobiti za zgornjo mejo kateri koli dan iz tega meseca . F4, da ga zaklenete v vse smeri, zaprite oklepaje na datum. Torej, to je naše drugo merilo: vejica. Razpon meril 3 je kategorija. Tam je, vejica in tam je naša glava vrstice. Torej, to moramo trikrat F4 dvakrat zakleniti, zakleniti stolpec, ne pa tudi vrstice. Ko bomo kopirali formulo, se bomo premaknili na Gizmo in Widget,tesna oklepaja in to je formula. Povlecite, dvakrat kliknite in ga pošljite navzdol. Vidim, da so težave. Raje pridem do zadnje celice, ki je diagonalno najbolj oddaljena. Pritisnite F2. Zdaj je privzeto vedenje nomenklature tabel formul, ko kopiramo formule na stran, se dejanski stolpci premikajo, kot da bi bili mešani sklici celic. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.Povlecite, dvakrat kliknite in ga pošljite navzdol. Vidim, da so težave. Raje pridem do zadnje celice, ki je diagonalno najbolj oddaljena. Pritisnite F2. Zdaj je privzeto vedenje Nomenklature tabel formul, ko kopiramo formule na stran, se dejanski stolpci premaknejo, kot da bi bili mešani sklici celic. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.Povlecite, dvakrat kliknite in ga pošljite navzdol. Vidim, da so težave. Raje pridem do zadnje celice, ki je diagonalno najbolj oddaljena. Pritisnite F2. Zdaj je privzeto vedenje nomenklature tabel formul, ko kopiramo formule na stran, se dejanski stolpci premikajo, kot da bi bili mešani sklici celic. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.Raje pridem do zadnje celice, ki je diagonalno najbolj oddaljena. Pritisnite F2. Zdaj je privzeto vedenje nomenklature tabel formul, ko kopiramo formule na stran, se dejanski stolpci premikajo, kot da bi bili mešani sklici celic. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.Raje pridem do zadnje celice, ki je diagonalno najbolj oddaljena. Pritisnite F2. Zdaj je privzeto vedenje nomenklature tabel formul, ko kopiramo formule na stran, se dejanski stolpci premikajo, kot da bi bili mešani sklici celic. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.dejanski stolpci se premikajo, kot da bi bili mešani sklici na celice. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.dejanski stolpci se premikajo, kot da bi bili mešani sklici na celice. Zdaj bi jih lahko zaklenili, vendar to tokrat ne bom storil. Zdaj opazite, ko ga kopirate, deluje v redu, ko pa kopirate na stran, ko se dejanski stolpci premaknejo. Torej, glejte to, grem na Ctrl + C in Ctrl + V, nato pa se izognete premikanju stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.m grem na Ctrl + C in Ctrl + V, kar preprečuje premikanje stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.m grem na Ctrl + C in Ctrl + V, kar preprečuje premikanje stolpcev F, ko ga kopirate na stran. Dvokliknite in ga pošljite navzdol. Zdaj naša formula% spremembe = končni znesek / začetni znesek -1, Ctrl + Enter, dvakrat kliknite in ga pošljite navzdol.

Zdaj, preden ga preizkusimo, zdaj dodajte nekaj novih zapisov. Pravzaprav želim ustvariti to oznako tukaj, tako da je dinamična. In način, kako bom to naredil, je, da bom rekel = znak in naredili bomo besedilno formulo, tako da boste kadar koli želeli besedilo in formulo, jo vstavite: "in jaz sem bom vtipkal Prodaja med, presledek & "in zdaj moram tam izvleči en sam datum, prvi v mesecu do konca meseca. Uporabil bom funkcijo TEXT. Funkcija TEXT lahko zajema datume številk ali serijske številke, vejice in uporablja določeno oblikovanje števil po meri v ”. Vedno želim videti tričrkovno okrajšavo za mesec, mmm, vedno jo želim kot prvo. Če sem tukaj postavil 1, presledek yyy, to ne bo delovalo. Hoče videti, da nam to daje vrednost ali ker mu to ni všeč 1. Ampak milahko uporabimo en znak, če uporabimo poševnico naprej, to je v oblikovanju po meri. Formatiranje številke po meri mm in yy bo razumljeno kot mesec in leto, zdaj pa bo format številke po meri razumel, da vstavite številko 1. F2 in zdaj bomo preprosto: & "-" & TEXT te vejice in zdaj bomo Samo uporabil bom oblikovanje naravnost števil: “mmm presledekD, llll”) Ctrl + Enter.

Zdaj pa samo, preden dodamo nekaj podatkov, samo spremenimo to. Pretvarjanje, da se kaže danes: 15.3.2017 prav tako se posodabljajo vse formule in tudi naša besedilna formula je Ctrl + Z. Zdaj se spustimo na dno nabora podatkov, Ctrl + puščica dol. Želim dodati en nov zapis. Sem v zadnji celici nabora podatkov, pritisnil sem Tab in dodal nov zapis v naš nabor podatkov. Enostavno bom skopiral ta zapis sem, Ctrl + puščica gor, in tam lahko vidimo razliko. Če bi želeli te vrednosti formul primerjati s tistimi, ki so:: = relativna referenca celice = klik na list, bomo kliknili desno v I4. Videli smo našo formulo zgoraj, Ctrl + Enter. Pravzaprav ga bom povlekel dol. Ctrl + Enter je pravkar naselil vse, kar sem poudaril. In seveda, FALSE FALSE.Pa ugani kaj? = ta znesek tam -, kliknite Ctrl + puščica navzdol, Ctrl + Backspace, tako da ga bom odvzel samo zato, da preverim in prepričan, da je bil točen znesek, ki bi ga lahko pogledali nazaj.

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.

V redu, no hej, rad bi se zahvalil vsem, da ste se ustavili. Naslednjič se vidimo za še en dvoboj Excel Podcast od in Excel Is Fun.

Prenesite datoteko

Prenesite vzorčno datoteko tukaj: Duel181.xlsm