Kā pagrieziena tabulā parādīt mēneša datumu pārdošanu. Šī ir Dueling Excel epizode.
Skatīties video
- Bila metode
- Pievienojiet palīga šūnu ar MTD formulu
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Pievienojiet šo lauku kā sagriezēju, kur = True
- Bonusa padoms: Grupas ikdienas datumi līdz gadiem
- Pievienojiet aprēķinu ārpus rakurstabulas, izvairoties no GetPivotData
- Maika pieeja:
- Pārvērtiet datus tabulā, izmantojot Ctrl + T. Tas ļauj tabulai pievienot vairāk datu un atjaunināt formulas.
- SUMIFS ar funkcijām DATE, MONTH, DAY
- Trīs reizes nospiežot F4, tiek bloķēta atsauce tikai uz kolonnu.
- Uzmanies - ja jūs velkat tabulas formulu uz sāniem, kolonnas mainās. Kopēt un ielīmēt - nav problēmu
- Izmantojot tekstu TEXT (datums, formāts. Jauks triks ar 1, lai tekstā ievietotu skaitli 1
Video atšifrējums
Bils Jelens: Hei, laipni gaidīti. Ir pienācis laiks kārtējam Dueling Excel Podcast. Es esmu Bils Jelen no. Man pievienosies Maiks Girvins no Excel Is Fun.
Šī ir mūsu 181. sērija: rakurss no mēneša līdz datumam.
Nu, hei, šodienas jautājums - šodienas ideju par šo dueli iesūta Maiks. Viņš saka: "Vai rakurstabulā varat izveidot pārskatu No mēneša līdz datumam?"
Labi, ejam. Tātad, lūk, kas mums ir, mums ir divu gadu datumi, sākot no 2016. gada janvāra līdz pat 2017. gadam. Tagad, protams, es to ierakstīju aprīlī, šobrīd ir 15. aprīlis, kad es ierakstīju savu divkaujas gabalu. Un šeit mums ir pagrieziena tabula, kurā kreisajā pusē ir redzamas dienas, augšpusē - kategorija un griešanās tabulas centrā - ieņēmumi.
Tagad, lai izveidotu pārskatu no mēneša līdz datumam, es darīšu to, ka es saku, ka es šeit pievienošu jaunu palīgu kolonnu saviem sākotnējiem datiem, un tas pārbaudīs divas lietas. Tā kā es pārbaudu divas lietas, kuras izmantošu funkciju AND, abām lietām jābūt patiesām, lai tā būtu mēneša līdz datumam. Un es šeit izmantošu funkciju, ko šodien sauc. ŠODIEN, labi, tāpēc es vēlos uzzināt, vai šodienas MĒNESIS ()) ir līdz šī datuma MĒNEŠAM, kas atrodas A slejā. Ja tā ir taisnība, ja tas ir pašreizējais mēnesis, tātad, citiem vārdiem sakot, ja tas ir aprīlis, tad pārbaudiet un pārliecinieties, vai šī datuma diena A2 vietā ir <= DAY ŠODIEN. Skaisti ir tas, ka, atverot šo darbgrāmatu rīt vai pēc nedēļas, šodienas diena tiks automātiski atjaunināta, un mēs veicam dubultklikšķi, lai to nokopētu.
Labi, mums ir jāiegūst šie papildu dati mūsu rakurstabulā, tāpēc es šeit ierados rakurstabula, analizēju un mainīt datu avotu nav tik grūti, vienkārši noklikšķiniet tur uz šīs lielās pogas un sakiet, ka mēs vēlamies pāriet uz D kolonnu , noklikšķiniet uz Labi. Labi, tāpēc tagad mums ir šis papildu lauks, es ievietošu griezēju, pamatojoties uz šo lauku no mēneša līdz datumam, un es tikai vēlos redzēt, kā mūsu mēnesis līdz datumam ir patiess. Vai mums vajag, lai šī Šķēle būtu tik liela? Nē, mēs, iespējams, varam padarīt to par divām kolonnām un tikai tā, lai tas būtu neuzkrītošs tur labajā pusē. Tātad tagad mums ir visi datumi 2016. gadā un visi 2017. gada datumi; lai gan, būtu tiešām forši salīdzināt šos blakus. Tāpēc es ņemšu šo lauku Datums un analizēšu. Es gatavojos grupēt lauku, grupēšu to tikai līdz gadiem. Es nedomājuMan patiesībā rūp atsevišķas dienas. Es tikai vēlos uzzināt mēnesi līdz datumam. Tagad, kur mēs atrodamies? Tāpēc es to sagrupēšu līdz gadiem, un mēs ar šiem 2 gadiem tur nonāksim, un es pēc tam to pārkārtošu, ielikšu šos gadus pāri, kategorijas samazināsies. Un tagad es redzu, kur mēs bijām pagājušajā gadā un kur mēs bijām šogad. Labi tagad, jo esmu grupējis, man vairs nav atļauts izveidot aprēķinātu lauku rakurstabulas iekšpusē. Ja es gribētu, lai tur būtu summa gadu iepriekš, es ar peles labo pogu noklikšķiniet uz Noņemt kopsummu, labi, un tagad mēs esam,% Izmaiņas, mēs esam ārpus rakurstabulas, kas norāda rakurstabulas iekšpusi . Mums jāpārliecinās vai nu izslēgt GetPivotData, vai arī vienkārši izveidot šādu formulu: = J4 / I4-1, un tas izveido formulu, kuru mēs varam nokopēt bez jebkādām grūtībām, piemēram, tā.Labi, Maik, paskatīsimies, kas tev ir.
Maiks Girvins: Paldies ,. Jā, es nosūtīju jautājumu, jo es to izdarīju ar formulām un es nevarēju saprast, kā to izdarīt ar standarta pagrieziena tabulu, un pēc tam es atcerējos, ka gadu gaitā redzēju, veicu virkni foršu videoklipu par palīgu kolonnām un pagrieziena tabulām . Tā ir skaista formula un skaists risinājums. Tātad, kā to izdarīt ar pagrieziena tabulu, ejam redzēt, kā to izdarīt ar formulu.
Tagad es to daru divas dienas pēc tam, kad viņš to izdarīja. F2 Man ir funkcija ŠODIEN, kas vienmēr būs informācija par šodienas pašreizējo datumu, kuru šeit izmantotās formulas izmantos, jo mēs vēlamies, lai tā tiktu atjaunināta. Esmu izmantojis arī Excel tabulu, un tās nosaukums ir FSales. Ja es nospiedu Ctrl + lejupvērstā bultiņa, es redzu, ka ir 4/14, bet es vēlos, lai varētu pievienot jaunākos ierakstus un lai mūsu formulas tiktu atjauninātas, pārejot uz nākamo mēnesi. Ctrl + bultiņa uz augšu. Labi, kolonnu galvenēs man ir Gada kritēriji, rindu galvenēs - Kategorija, un tad dati no mēneša un dienas nāks no šīs šūnas. Tāpēc es vienkārši izmantoju funkciju SUMIFS, jo mēs pievienojam vairākus nosacījumus, šeit ir ieņēmumu summas diapazons, mēs izmantosim šo lielisko triku Excel tabulai.Tieši augšpusē mēs redzam melno lejup vērsto bultiņu BAM! Tas ievieto pareizo tabulas nosaukumu un pēc tam kvadrātiekavās lauka nosaukumu, komatu. Kritēriju diapazons: mums datums būs jāizmanto divreiz, tāpēc es sākšu ar datumu. Noklikšķiniet, tur ir datuma kolonna, komats. Tagad esmu aprīlī, tāpēc man ir jāizveido nosacījums> = līdz 1. aprīlim. Tātad salīdzinošie operatori “> =” divkāršās pēdiņās, un es pievienojos tam. Tagad man ir jāizveido kāda datuma formula, kas vienmēr izskatās šeit un izveido šī mēneša pirmo mēnesi. Tāpēc es izmantošu funkciju DATE. Gads, labi, kolonnas galvenē ir tieši gads, un es vienu reizi nospiedīšu F4 taustiņu, lai bloķētu rindu, bet ne kolonnu, tāpēc, kad tā pāriet šeit, mēs pāriet uz 2017. gadu, komats, Mēnesis - esEs izmantoju funkciju MONTH, lai iegūtu mēneša skaitli no 1 līdz 12. Tas ir neatkarīgi no tā, kāds mēnesis ir šajā šūnā, F4, lai to bloķētu visos virzienos, aizvērtu iekavas un pēc tam komatu, 1 tas vienmēr būs 1. no mēnesis neatkarīgi no tā, kāds mēnesis tas ir, aizveriet iekavas.
Labi, tāpēc tie ir kritēriji. Tas vienmēr būs> = mēneša pirmais, komats, otro kritēriju diapazons. Es saņemšu kolonnu Datums, komats. Otrais kritērijs, labi, tas būs <= augšējā robeža, tāpēc “<=” un &. Es krāpšos, skaties šo. Es tikai to nokopēšu no šejienes, jo tas ir viens un tas pats, Ctrl-C Ctrl-V, izņemot dienu, mums ir jāizmanto funkcija DAY un vienmēr jāsaņem kā augšējā robeža neatkarīgi no tā, kāda diena ir no šī mēneša . F4, lai to bloķētu visos virzienos, aizveriet iekavas datumā. Labi, tāpēc tie ir divi mūsu kritēriji: komats. Kritēriju diapazons 3, tā ir kategorija. Šeit tas ir, komats un tur ir mūsu rindas galvene. Tātad šis mums ir F4 viens divas trīs reizes, bloķējiet kolonnu, bet ne rindu, tāpēc, kad mēs kopējam formulu uz leju, mēs pāriet uz Gizmo un Widget,cieša iekava, un tā ir formula. Pārvelciet, veiciet dubultklikšķi un nosūtiet to uz leju. Es redzu, ka ir nepatikšanas. Es labāk nāku uz pēdējo šūnu pa diagonāli vistālāk. Rezultāts F2. Tagad tabulas formulu nomenklatūras noklusējuma darbība ir tad, kad kopējat formulas malā, faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad pamaniet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.Pārvelciet, veiciet dubultklikšķi un nosūtiet to uz leju. Es redzu, ka ir nepatikšanas. Es labāk nāku uz pēdējo šūnu pa diagonāli vistālāk. Rezultāts F2. Tagad tabulas formulu nomenklatūras noklusējuma darbība ir tad, kad kopējat formulas malā, faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad pamaniet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.Pārvelciet, veiciet dubultklikšķi un nosūtiet to uz leju. Es redzu, ka ir nepatikšanas. Es labāk nāku uz pēdējo šūnu pa diagonāli vistālāk. Rezultāts F2. Tagad tabulas formulu nomenklatūras noklusējuma darbība ir tad, kad kopējat formulas malā, faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad ievērojiet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tad tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.Es labāk nāku uz pēdējo šūnu pa diagonāli vistālāk. Rezultāts F2. Tagad tabulas formulu nomenklatūras noklusējuma darbība ir tad, kad kopējat formulas malā, faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad ievērojiet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tad tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.Es labāk nāku uz pēdējo šūnu pa diagonāli vistālāk. Rezultāts F2. Tagad tabulas formulu nomenklatūras noklusējuma darbība ir tad, kad kopējat formulas malā, faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad pamaniet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tad tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad pamaniet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tad tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.faktiskās kolonnas pārvietojas tā, it kā tās būtu jauktas šūnu atsauces. Tagad mēs tos varētu bloķēt, bet es to nedarīšu šoreiz. Tagad pamaniet, kad to nokopējat, tas darbojas labi, bet, kad kopējat uz sāniem, kad pārvietojas faktiskās kolonnas. Tāpēc skatieties to, es eju uz Ctrl + C un Ctrl + V, un tad tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.Dodos uz Ctrl + C un Ctrl + V, un tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.Dodos uz Ctrl + C un Ctrl + V, un tas novērš F kolonnās pārvietošanos, kad kopējat to uz sāniem. Veiciet dubultklikšķi un nosūtiet to uz leju. Tagad mūsu% Change formula = beigu summa / sākuma summa -1, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju.
Tagad, pirms mēs to pārbaudām, pievienojiet dažus jaunus ierakstus. Es patiešām vēlos izveidot šo etiķeti šeit, tāpēc tā ir dinamiska. To, kā es to darīšu, es teikšu = sign, un mēs darīsim Teksta formulu, tāpēc jebkurā brīdī, kad mēs vēlamies tekstu un formulu, jums tas ir jāievieto: “un es esmu gatavojas ierakstīt Pārdošana starp, atstarpe ”un tagad man jāizņem no šī vienīgā datuma - mēneša pirmā līdz mēneša beigām. Es izmantošu funkciju TEXT. Funkcija TEKSTS var ņemt skaitļu datumus vai sērijas numurus, komatus un izmantot dažus pielāgotus ciparu formatējumus. Es vienmēr gribu redzēt mēneša trīs burtu saīsinājumu, mmm, es vienmēr gribu, lai tas būtu pirmais. Ja es šeit ievietoju 1, komats atstarpe yyy, tas nedarbosies. Vēlas redzēt, ka tas mums piešķir vērtību vai tāpēc, ka tas nepatīk 1. Bet mēsAtļauts ievietot vienu rakstzīmi, ja mēs izmantojam slīpsvītru uz priekšu, tas ir pielāgotā numura formatējumā. Mm un yy tiks saprasts pēc pielāgotā numura formatēšanas kā mēnesis un gads, un tagad pielāgotā numura formāts sapratīs ievietot skaitli 1. F2, un tagad mēs vienkārši ejam: & “-” & TEKSTS šim komatam, un tagad mēs Izmantosiet tikai taisnu ciparu formatējumu: “mmm atstarpeD, gggg”) 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.
Labi, labi, hei, es gribu pateikties visiem par apstāšanos. Tiksimies nākamreiz, lai skatītu citu Dueling Excel Podcast no un Excel ir jautri.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Duel181.xlsm
