Nuo mėnesio iki datos - „Excel“ patarimai

Kaip parodyti mėnesio dienos pardavimus suvestinėje lentelėje. Tai „Dueling Excel“ epizodas.

Žiūrėti video

Bilo metodas
Pridėkite pagalbinę ląstelę pagal MTD formulę
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Pridėkite tą lauką kaip pjaustyklę, kur = Tiesa
Premijos patarimas: grupės dienos dienos iki metų
Pridėkite skaičiavimą už suvestinės lentelės, vengdami „GetPivotData“
Mike'o požiūris:
Pasukite duomenis į lentelę naudodami Ctrl + T. Tai leidžia pridėti daugiau duomenų prie lentelės ir atnaujinti formules.
SUMIFS su DATE, MONTH, DAY funkcijomis
Tris kartus paspaudus F4, užfiksuojama nuoroda tik į stulpelį.
Saugokitės - jei vilksite lentelės formulę į šoną, stulpeliai pasikeis. Kopijuoti ir įklijuoti - jokių problemų
Naudojant TEXT (data, formatas. Puikus triukas su 1, norint įterpti skaičių 1 į tekstą

Vaizdo įrašo nuorašas

Billas Jelenas: Ei, sveiki sugrįžę. Atėjo laikas kitam „Dueling Excel“ tinklui. Aš esu Billas iš. Prie manęs prisijungs Mike'as Girvinas iš „Excel Is Fun“.

Tai yra mūsų 181 serija: „Nuo mėnesio iki datos“ suvestinė lentelė.

Na, ei, šiandienos klausimas - šiandienos šios dvikovos idėją atsiuntė Mike'as. Jis sako: „Ar galite sukurti ataskaitą„ Nuo mėnesio iki datos “suvestinėje lentelėje?“

Gerai, einam. Taigi, ką mes turime, mes turime dvejų metų datas nuo 2016 m. Sausio iki 2017 m. Dabar, žinoma, aš tai įrašau balandžio mėnesį, dabar yra balandžio 15 d., Kai įrašau savo dvikovos kūrinį. Taigi čia mes turime suvestinę lentelę, kurioje kairėje pusėje rodomos dienos, viršuje - kategorija, o šerdies lentelės centre - pajamos.

Dabar, norėdamas sukurti ataskaitą „Nuo mėnesio iki datos“, pasakysiu, kad pridėsiu naują pagalbinį stulpelį čia prie savo pradinių duomenų ir patikrinsiu du dalykus. Kadangi tikrinu du dalykus, kuriuos naudosiu funkcija AND, abu dalykai turi būti teisingi, kad tai būtų mėnuo iki datos. Ir aš naudosiu funkciją, pavadintą TODAY. ŠIANDIE, gerai, todėl noriu sužinoti, ar ŠIANDIEN ()) mėnuo yra tos dienos MĖNESIS, esantis A stulpelyje. Jei tai tiesa, jei dabartinis mėnuo, kitaip tariant, jei balandžio mėnuo, tada patikrinkite ir pažiūrėkite, ar tos dienos A2 diena yra <= ŠIANDIEN DAY. Gražiausia tai, kad atidarę šią darbaknygę rytoj ar po savaitės, šiandienos diena bus automatiškai atnaujinta ir dukart spustelėdami ją nukopijuosime.

Gerai, dabar turime gauti šiuos papildomus duomenis į savo sukamą lentelę, todėl aš čia ateinu „Pivot Table“, „Analyze“ ir nėra taip sunku pakeisti duomenų šaltinį, tiesiog spustelėkite ten tą didelį mygtuką ir pasakykite, kad norime pereiti į D stulpelį , spustelėkite Gerai. Gerai, taigi dabar turime tą papildomą lauką, aš įvesiu pjaustyklę pagal tą mėnesį iki datos lauką ir noriu tik pamatyti, kaip mūsų mėnuo iki datos yra teisinga. Ar mums reikia, kad tas Riekė būtų toks didelis? Ne, mes tikriausiai galime padaryti, kad tai būtų dvi kolonos ir tiesiog turėtų būti netrukdančios dešinėje pusėje. Taigi dabar turime visas datas 2016 m. Ir visas datas 2017 m. nors, būtų tikrai šaunu lyginti šiuos vienas šalia kito. Taigi aš paimsiu tą lauką Data ir analizuosiu. Aš ketinu sugrupuoti lauką, grupuosiu tik iki metų. Aš nefaktiškai rūpi atskiros dienos. Aš tiesiog noriu sužinoti, kas mėnesį iki datos. Dabar, kur mes esame? Taigi suskirstysiu jį į metus ir baigsime tuos dvejus metus ten, tada aš tai pertvarkysiu, tuos metus dėsiu, kategorijas - žemyn. Ir dabar matau, kur buvome pernai ir kur buvome šiais metais. Gerai dabar, nes aš grupavau, man nebeleidžiama kurti apskaičiuoto lauko suvestinės lentelės viduje. Jei norėčiau, kad ten būtų suma per metus, dešiniuoju pelės mygtuku spustelėkite, Pašalinti bendrą sumą, gerai, ir dabar mes esame, taigi,% Keisti, mes nesame suvestinės lentelės, rodančios suvestinės lentelės viduje . Turime įsitikinti, kad išjungėte „GetPivotData“, arba tiesiog sukurkite tokią formulę: = J4 / I4-1. Tai sukuria formulę, kurią galime nukopijuoti be jokių rūpesčių.Gerai, Mike, pažiūrėkime, ką turi.

Mike'as Girvinas: Ačiū. Taip, aš nusiunčiau klausimą, nes tai padariau naudodamas formules ir negalėjau suprasti, kaip tai padaryti naudojant standartinę „pivot“ lentelę, ir po to prisiminiau, kad per daugelį metų mačiau daugybę šaunių vaizdo įrašų apie pagalbines stulpelius ir pasisukimo lenteles. . Tai yra graži formulė ir gražus sprendimas. Taigi, kaip tai padaryti su sukamąja lentele, pažiūrėkime, kaip tai padaryti pagal formulę.

Aš tai darau dvi dienas po to, kai jis tai padarė. F2 Aš turiu TODAY funkciją, kuri visada bus šios dienos datos informacija, kurią naudos čia pateiktos formulės, nes mes norime, kad ji būtų atnaujinta. Taip pat naudojau „Excel“ lentelę, kurios pavadinimas FSales. Jei aš „Ctrl“ + rodyklė žemyn, matau, kad tai 4/14, bet aš noriu, kad galėčiau pridėti naujausius įrašus ir kad mūsų formulės būtų atnaujintos, kai pereisime prie kito mėnesio. „Ctrl“ + rodyklė aukštyn. Gerai, aš turiu Metų kriterijus kaip stulpelių antraštes, Kategoriją kaip eilutės antraštes, o tada mėnesio ir dienos duomenys bus iš tos langelio. Taigi aš paprasčiausiai naudosiuosi funkcija SUMIFS, nes pridedame kelias sąlygas, sumų diapazonas yra pajamos, mes naudosime tą puikų triuką „Excel“ lentelėje.Tiesiai viršuje matome tą juodą žemyn nukreiptą rodyklę BAM! Taip įrašomas tinkamas lentelės pavadinimas, o tada laužtiniuose skliaustuose - lauko pavadinimas, kablelis. Kriterijų diapazonas, datą turėsime naudoti du kartus, todėl pradėsiu nuo datos. Spustelėkite, yra datos stulpelis, kablelis. Dabar esu balandžio mėn., Todėl turiu sukurti sąlygą> = iki balandžio 1 d. Taigi lyginamieji operatoriai „> =“ dvigubose kabutėse ir aš prie jo prisijungsiu. Dabar turiu sukurti tam tikrą datos formulę, kuri visada atrodo čia ir sukuria mėnesio pirmąją šiems metams. Taigi naudosiuosi funkcija DATA. Metai, gerai, aš turiu metus kaip stulpelio antraštę ir du kartus paspausiu F4 klavišą, kad užfiksuočiau eilutę, bet ne stulpelį, taigi, kai jis persikels čia, mes pereisime prie 2017 m., Kablelis, Mėnuo - ašAš naudosiu funkciją MĖNESIS, kad gautumėte mėnesio skaičių nuo 1 iki 12. Tai yra bet koks mėnuo toje langelyje, F4, kad užrakintų jį į visas puses, uždarytų skliaustus ir kablelius, 1 tai visada bus 1 iš mėnuo, nesvarbu, koks šis mėnuo yra, uždaryti skliausteliuose.

Gerai, todėl tai yra kriterijai. Tai visada bus> = mėnesio pirmoji, kablelis, kriterijų antrasis diapazonas. Aš gausiu stulpelį Data, kablelis. Antras kriterijus, na, tai bus <= viršutinė riba, taigi „<=“ ir &. Aš apgausiu, žiūrėk šitą. Aš tiesiog nukopijuosiu tai iš čia, nes tai yra tas pats dalykas, Ctrl-C Ctrl-V, išskyrus dieną, mes turime naudoti DAY funkciją ir visada gauti viršutinę ribą, kad ir kokia būtų šio mėnesio diena. . F4, kad jį užrakintumėte visomis kryptimis, uždarykite skliaustus „Date“. Gerai, taigi tai yra du mūsų kriterijai: kablelis. 3 kriterijų diapazonas, tai kategorija. Štai, kablelis ir mūsų eilutės antraštė. Taigi mes turime F4 vieną du tris kartus, užfiksuoti stulpelį, bet ne eilutę, taigi, kai nukopijuosime formulę žemyn, mes pereisime prie „Gizmo“ ir „Widget“,glaudus skliaustas ir tai yra formulė. Vilkite, dukart spustelėkite ir nusiųskite žemyn. Matau, kad yra problemų. Geriau ateisiu į paskutinę langelį įstrižai toliausiai. Paspauskite F2. Dabar numatytoji lentelės formulių nomenklatūros elgsena yra tada, kai nukopijuojate formules į šoną, tikrieji stulpeliai juda taip, lyg jie būtų mišrių langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate jį žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V, tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.Vilkite, dukart spustelėkite ir nusiųskite žemyn. Matau, kad yra problemų. Geriau ateisiu į paskutinę langelį įstrižai toliausiai. Paspauskite F2. Dabar numatytoji lentelės formulių nomenklatūros elgsena yra tada, kai nukopijuojate formules į šoną, tikrieji stulpeliai juda taip, lyg jie būtų mišrių langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate jį žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V, tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.Vilkite, dukart spustelėkite ir nusiųskite žemyn. Matau, kad yra problemų. Geriau ateisiu į paskutinę langelį įstrižai toliausiai. Paspauskite F2. Dabar numatytoji lentelės formulių nomenklatūros elgsena yra tada, kai nukopijuojate formules į šoną, tikrieji stulpeliai juda taip, lyg jie būtų mišrių langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V ir tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.Geriau ateisiu į paskutinę langelį įstrižai toliausiai. Paspauskite F2. Dabar numatytoji lentelės formulių nomenklatūros elgsena yra tada, kai nukopijuojate formules į šoną, tikrieji stulpeliai juda taip, lyg jie būtų mišrių langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate jį žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V, tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.Geriau ateisiu į paskutinę langelį įstrižai toliausiai. Paspauskite F2. Dabar numatytoji lentelės formulių nomenklatūros elgsena yra tada, kai nukopijuojate formules į šoną, tikrieji stulpeliai juda taip, lyg jie būtų mišrių langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate jį žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V, tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.faktiniai stulpeliai juda taip, lyg jie būtų mišrios langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V, tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.faktiniai stulpeliai juda taip, lyg jie būtų mišrios langelių nuorodos. Dabar galėtume juos užrakinti, bet šį kartą to nedarysiu. Dabar atkreipkite dėmesį, kai nukopijuojate jį žemyn, jis veikia gerai, bet kai nukopijuojate į tą pusę, kai juda faktiniai stulpeliai. Taigi žiūrėkite tai, eisiu į „Ctrl“ + C ir „Ctrl“ + V, tada išvengsite, kad F stulpeliuose nejudėtų, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.m eisiu į „Ctrl“ + C ir „Ctrl“ + V ir tada išvengsite F stulpeliuose judėjimo, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.m eisiu į „Ctrl“ + C ir „Ctrl“ + V ir tada išvengsite „F“ į stulpelius judėjimo, kai nukopijuosite jį į šoną. Dukart spustelėkite ir nusiųskite žemyn. Dabar mūsų% pakeitimo formulė = pabaigos suma / pradinė suma -1, „Ctrl“ + „Enter“, dukart spustelėkite ir nusiųskite ją žemyn.

Dabar, prieš pradėdami jį išbandyti, pridėkite keletą naujų įrašų. Aš iš tikrųjų noriu sukurti šią etiketę, kad ji būtų dinamiška. Aš tai pasakysiu = ženklas ir atliksime teksto formulę, todėl bet kada, kai norime teksto ir formulės, turite ją įdėti: „Ir aš rašysiu „Pardavimas tarp, tarpas“ ir dabar turiu išgauti iš tos vienintelės datos - mėnesio pirmąją iki mėnesio pabaigos. Aš naudosiu TEXT funkciją. Funkcija TEKSTAS gali užimti skaičių datas arba serijos numerius, kablelius ir naudoti tam tikrą pasirinktinį skaičių formatavimą “. Aš visada noriu pamatyti trijų raidžių mėnesio santrumpą, mmm, aš visada noriu, kad ji būtų pirmoji. Jei čia įdėsiu 1, kablelio tarpas yyy, tai neveiks. Nori pamatyti, kad tai suteikia mums vertę, arba todėl, kad tai nepatinka 1. Bet mesleidžiama įterpti vieną simbolį, jei naudojame pasvirąjį brūkšnį, tai yra pasirinktinio numerio formatavimas. Mm ir yy bus suprantami pagal pasirinkto skaičiaus formatą kaip mėnuo ir metai, o dabar pasirinktinio numerio formatas supras įterpti skaičių 1. F2 ir dabar mes tiesiog eisime: & “-“ & TEKSTAS to kablelio ir dabar mes Tiesiog naudosite tiesų skaičių formatavimą: „mmm tarpasD, 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.

Gerai, gerai ei, noriu padėkoti visiems, kad užsukote. Kitą kartą pamatysime kitą „Dueling Excel Podcast“ iš „Excel Is Fun“.

Atsisiųsti failą

Failo pavyzdį atsisiųskite čia: „Duel181.xlsm“