Kuidas kuvada kuu ajakohaseid müüke pöördtabelis. See on Dueling Exceli episood.
Vaata videot
- Billi meetod
- Lisage MTD valemiga abistajarakk
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Lisage see väli viilutajana, kus = True
- Boonusenipp: grupi päevased kuupäevad kuni aastad
- Lisage arvutus väljaspool pöördtabelit, vältides GetPivotData
- Mike'i lähenemine:
- Muutke andmed tabeliks, kasutades klahve Ctrl + T. See võimaldab tabelisse lisada rohkem andmeid ja valemeid värskendada.
- SUMIFS funktsioonidega DATE, MONTH, DAY
- Kolm korda klahvi F4 vajutades lukustatakse viide ainult veerule.
- Ettevaatust - kui lohistate tabeli valemit külili, muutuvad veerud. Kopeerimine ja kleepimine - pole probleeme
- Kasutades teksti TEXT (kuupäev, vorming. Kena nipp klahviga 1 numbri 1 sisestamiseks teksti
Video ärakiri
Bill Jelen: Hei, tere tulemast tagasi. Aeg on veel üks Dueling Exceli podcast. Olen Bill Jelen pärit. Minuga liitub Mike Girvin Excel Is Funist.
See on meie osa 181: Pivot-tabel kuust kuupäevani.
Noh, hei, tänane küsimus - tänase duelli idee saadab Mike. Ta ütleb: "Kas saate pöördetabelisse luua kuu kuni kuupäeva aruande?"
Otse, lähme. Nii et meil on siin, meil on kaks aastat väärt kuupäevi alates jaanuarist 2016 kuni 2017. aastani. Nüüd muidugi salvestan selle aprillis, praegu on 15. aprill, kui ma oma duellitükki lindistan. Nii et siin on meil pöördtabel, kus vasakul pool on päevad, ülaservas kategooria ja pöördlaua südames tulud.
Nüüd, et luua kuu kuni kuupäeva aruanne, kavatsen öelda, et lisan siia oma algandmetele uue abistaja veeru ja see kontrollib kahte asja. Ja kuna ma kontrollin kahte asja, mida kasutan funktsiooni AND, peavad mõlemad asjad olema tõesed, et see oleks kuu kuni kuupäev. Ja ma hakkan siin kasutama funktsiooni nimega TÄNA. TÄNA, olgu, nii et ma tahan teada, kas TÄNA KUU () on veerus A selle kuupäeva KUU. Kui see on tõsi, kui see on praegune kuu, siis teisisõnu, kui see on aprill, siis kontrollige ja vaadake, kas selle kuupäeva päev A2-s on <= TÄNA PÄEV. Ilus on see, et kui selle töövihiku homme või nädala pärast avame, värskendatakse tänast päeva automaatselt ja selle kopeerimiseks topeltklõpsake.
Olgu, nüüd peame need lisad andmed oma pöördtabelisse saama, nii et ma tulen siia Pivoti tabel, analüüsige ja andmeallikat pole nii raske muuta, klõpsake lihtsalt sellel suurel nupul ja öelge, et tahame minna veergu D , klõpsake nuppu OK. Hästi, nii et nüüd on meil see lisaväli, lisan selle kuu kuni kuupäeva välja põhjal viilutaja ja tahan ainult näha, kuidas meie kuu kuni kuupäev on tõene. Kas me vajame, et see Viil oleks nii suur? Ei, me võime ilmselt muuta selle kaheks veeruks ja lihtsalt parempoolsel küljel pealetükkimatuks. Nüüd on meil kõik kuupäevad 2016. aastal ja kõik 2017. aasta kuupäevad; kuigi oleks lahe neid kõrvuti võrrelda. Nii et ma võtan selle kuupäevavälja ja analüüsin. Ma hakkan rühmitama põldu, rühmitan selle lihtsalt aastateks. Ma ei 'tegelikult ei hooli üksikutest päevadest. Ma tahan lihtsalt teada Kuust kuupäevani. Kus me oleme? Nii et ma rühmitan selle aastateks ja lõpetame nende 2 aastat seal ja ma kavatsen selle siis ümber korraldada, panna need aastad üle minna, kategooriad alla minna. Ja nüüd näen, kus me eelmisel aastal ja kus sel aastal olime. Hea küll, kuna olen grupeerimise lõpetanud, pole mul enam lubatud pöördtabelisse arvutatud välja luua. Kui ma tahaksin, et seal oleks aasta-aastalt suurem summa, klõpsaksin hiire parema nupuga, Eemalda kokkuvõte, olgu, ja nüüd oleme,% Muuda, oleme väljaspool pöördtabelit, mis osutab pöördtabeli sees . Peame kindlasti kas GetPivotData välja lülitama või lihtsalt ehitama sellise valemi: = J4 / I4-1 ja see loob valemi, mille saame kopeerida ilma igasuguste probleemideta.Hästi, Mike, vaatame, mis sul on.
Mike Girvin: aitäh ,. Jah, ma saatsin selle küsimuse, kuna tegin seda valemitega ja ma ei suutnud aru saada, kuidas seda tavalise pöördtabeli abil teha, ja siis meenus, et olin aastate jooksul näinud, teinud hulga lahedaid videoid abiveergude ja pöördtabelite kohta . See on ilus valem ja ilus lahendus. Nii et seda saab teha pöördtabeli abil, vaatame, kuidas seda valemiga teha.
Nüüd teen seda kaks päeva pärast seda, kui ta seda tegi. F2 Mul on funktsioon TÄNA, mis on alati tänase praeguse kuupäeva kuupäeva teave, mida siin valemid kasutavad, sest me tahame seda värskendada. Olen kasutanud ka Exceli tabelit ja selle nimi on FSales. Kui ma vajutan klahve Ctrl + allanool, näen, et see on 4/14, kuid ma tahan, et saaksin lisada uusimad kirjed ja lasta järgmisel valikul hüpates lisada meie valemid. Ctrl + nool üles. Hea küll, mul on veerupäistadeks aasta kriteeriumid, rea päisteks kategooria ja siis tulevad selle lahtri andmed kuu ja päeva kohta. Nii et ma lihtsalt kasutan funktsiooni SUMIFS, kuna lisame mitme tingimusega, siin on summa vahemik tulu, me kasutame seda suurepärast trikki Exceli tabeli jaoks.Otse ülaosas näeme seda musta allapoole suunatud noolt, BAM! See paneb tabeli õige nime ja seejärel nurksulgudesse välja nime koma. Kriteeriumivahemik, peame kuupäeva kasutama kaks korda, nii et alustan kuupäevast. Klõpsake, seal on kuupäevaveerg, koma. Nüüd olen aprillis, seega pean looma tingimuse> = 1. aprillini. Nii et võrdlusoperaatorid “> =” topelt jutumärkides ja ma liitun sellega. Nüüd pean looma mõne kuupäevavalemi, mis alati vaatab siia ja loob selle aasta kuu esimese. Nii et kasutan funktsiooni KUUPÄEV. Aasta, noh, mul on veeru päiseks aasta Aasta ja ma vajutan kaks korda klahvi F4, et lukustada rida, kuid mitte veerg, nii et kui see siin üle liigub, liigume aastasse 2017, koma, Kuu - minaKasutan funktsiooni KUU, et saada kuu number 1 kuni 12. See on ükskõik milline kuu on selles lahtris üleval, F4, et lukustada see igas suunas, sulgeda sulgud ja seejärel koma, 1 on see alati 1. väärtus kuu olenemata sellest, mis kuu see on, sulgud sulguvad.
Hästi, nii et see on kriteerium. Alati on> = kuu esimene, koma, kriteeriumivahemik kaks, ma saan veeru Kuupäev, koma. Kriteeriumid 2, noh, see saab olema <= ülemine piir, nii et “<=” ja &. Lähen petma, vaata seda. Kopeerin selle lihtsalt siit üles, kuna see on sama asi, Ctrl-C Ctrl-V, välja arvatud päev, peame kasutama funktsiooni PÄEV ja alati olema ülemine piir, olenemata sellest kuust, millal see päev on . F4 selle lukustamiseks igas suunas, sulgege sulgudes kuupäev. Hästi, nii et see on meie kriteerium kaks: koma. Kriteeriumivahemik 3, see on kategooria. Seal see on, koma ja seal on meie rea päis. Nii et see on meil F4 üks kaks kolm korda, lukustage veerg, kuid mitte rida, nii et kui valemi alla kopeerime, liigume Gizmo ja Widget juurde,lähedane sulg ja see on valem. Lohistage kohale, topeltklõpsake ja saatke see alla. Ma näen, et on probleeme. Parem tulen viimasesse lahtrisse diagonaalselt kõige kaugemal. Tulemus F2. Nüüd on tabeli valemite nomenklatuuri vaikekäitumine see, kui kopeerite valemid küljele, tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle allapoole, see töötab hästi, kuid kui kopeerite küljele, kus tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.Lohistage kohale, topeltklõpsake ja saatke see alla. Ma näen, et on probleeme. Parem tulen viimasesse lahtrisse diagonaalselt kõige kaugemal. Tulemus F2. Nüüd on tabeli valemite nomenklatuuri vaikekäitumine see, kui kopeerite valemid küljele, tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle allapoole, see töötab hästi, kuid kui kopeerite küljele, kus tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.Lohistage kohale, topeltklõpsake ja saatke see alla. Ma näen, et on probleeme. Parem tulen viimasesse lahtrisse diagonaalselt kõige kaugemal. Tulemus F2. Nüüd on tabeli valemite nomenklatuuri vaikekäitumine see, kui kopeerite valemid küljele, tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle allapoole, see töötab hästi, kuid kui kopeerite küljele, kus tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.Parem tulen viimasesse lahtrisse diagonaalselt kõige kaugemal. Tulemus F2. Nüüd on tabeli valemite nomenklatuuri vaikekäitumine see, kui kopeerite valemid küljele, tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle allapoole, see töötab hästi, kuid kui kopeerite küljele, kus tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.Parem tulen viimasesse lahtrisse diagonaalselt kõige kaugemal. Tulemus F2. Nüüd on tabeli valemite nomenklatuuri vaikekäitumine see, kui kopeerite valemid küljele, tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle alla, see töötab hästi, kuid kui kopeerite küljele, siis kui tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle allapoole, see töötab hästi, kuid kui kopeerite küljele, kus tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.tegelikud veerud liiguvad nii, nagu oleksid need lahtriviited. Nüüd saaksime nad lukustada, kuid ma ei kavatse seda seekord teha. Pange nüüd tähele, kui kopeerite selle alla, see töötab hästi, kuid kui kopeerite küljele, siis kui tegelikud veerud liiguvad. Nii et vaadake seda. Ma lähen klahvidele Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.Ma liigun klahvikombinatsioonile Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.Ma liigun klahvikombinatsioonile Ctrl + C ja Ctrl + V ja see väldib veergude F liikumist, kui kopeerite selle küljele. Topeltklõpsake ja saatke see alla. Nüüd meie% Change valem = lõppsumma / algsumma -1, Ctrl + Enter, topeltklõpsake ja saatke see alla.
Enne kui proovime seda proovida, lisage nüüd mõned uued kirjed. Ma tahan selle sildi tegelikult siia üles luua, et see oleks dünaamiline. Ja kuidas ma seda teen, ütlen = märk ja teeme teksti valemi, nii et kui soovite teksti ja valemit, peate selle alati sisestama: "Ja ma olen kirjutan müügi vahel, tühik ”ja nüüd pean välja võtma selle ühe kuupäeva sealsest, kuu esimesest kuni kuu lõpuni. Ma hakkan kasutama funktsiooni TEXT. Funktsioon TEXT võib võtta numbri kuupäevad või seerianumbrid, komad ja kasutada mõningaid kohandatud numbrivorminguid tekstis ”. Ma tahan alati näha kuu kolmetähelist lühendit, mmm, ma tahan seda alati esimesena. Kui panen siia 1, siis koma tühik yyy, see ei toimi. Tahab näha, et see annab meile väärtuse või kuna see ei meeldi sellele 1. Aga meKui kasutame kaldkriipsu, on lubatud sisestada üks märk, see on kohandatud numbri vormingus. Mm ja yy saavad kohandatud numbri vormindamisel aru kui kuu ja aasta ning nüüd saab kohandatud numbri vorming sisestada numbri 1. F2 ja nüüd läheme lihtsalt selle komaga: & "-" & TEXT ja nüüd me Kasutan lihtsalt sirget numbrivormingut: "mmm spaceD, yyy") Ctrl + Enter.
Muutkem nüüd, enne kui lisame mõned andmed, muudame seda. Teeseldes, et täna oli näha: 15.03.2017 just nii värskendatakse kõiki valemeid ja ka meie tekstivalem on Ctrl + Z. Läheme nüüd andmekogumi põhja, Ctrl + nool alla, ma tahan lisada ühe uue kirje. Olen andmekogumi viimases lahtris ja vajutasin tabeldusklahvi, et lisada meie andmekogumisse uus kirje. Kopeerin selle plaadi lihtsalt siia, Ctrl + Nool üles ja seal näeme erinevust. Kui tahtsime neid valemiväärtusi võrrelda nendega, mis tegid: = suhteline lahtriviide = klõpsake lehel, klõpsame paremal I4-s. Nägime oma valemit seal üleval, Ctrl + Enter. Tegelikult ma lohistan selle alla. Ctrl + Enter asustas lihtsalt kõik, mille olin esile tõstnud. Ja muidugi VALE VÄÄR.No arvake ära? = see summa seal -, klõpsake nuppu Ctrl + nool alla, Ctrl + tagasilükkeklahv, nii et ma lahutan selle lihtsalt kontrollimiseks ja piisavalt, et see oleks täpne summa, mida saaksime sinna tagasi vaadata.
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.
Hästi, noh hei, ma tahan tänada kõiki peatumiste eest. Järgmisel korral näeme teist Dueling Exceli Podcastit ja Excel on lõbus.
Laadige fail alla
Laadige näidisfail alla siit: Duel181.xlsm
