Месец до данас - Екцел савети

Како приказати продају од месеца до данас у пивот табели. Ово је епизода Дуелинг Екцел-а.

Погледајте видео

Биллова метода
Додајте помоћну ћелију са МТД формулом
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Додајте то поље као Резач где је = Тачно
Бонус савет: Групни дневни термини до година
Додајте прорачун изван пивот табеле, избегавајући ГетПивотДата
Мајков приступ:
Претворите податке у табелу помоћу тастера Цтрл + Т. То омогућава додавање више података у табелу и ажурирање формула.
СУМИФИРА са функцијама ДАТУМ, МЕСЕЦ, ДАН
Притиском на Ф4 три пута закључава се референца само на колону.
Пазите - ако формулу табеле повучете бочно, колоне се мењају. Копирај и налепи - без проблема
Коришћење ТЕКСТА (датум, формат. Леп трик са 1 за уметање броја 1 у текст

Видео транскрипт

Билл Јелен: Хеј, добродошао назад. Време је за још један Дуел Екцел подцаст. Ја сам Билл Јелен из. Придружиће ми се Мике Гирвин из Екцел-а је забавно.

Ово је наша епизода 181: Изведена табела за месец до данас.

Па, хеј, данашње питање - данашњу идеју за овај дуел шаље Мике. Каже, „Можете ли да направите извештај од месеца до датума у пивот табели?“

Добро, идемо. Дакле, ево шта имамо, имамо две године вредне датуме од јануара 2016. па све до 2017. Сада, наравно, ово снимам у априлу, управо је 15. април кад снимам свој део дуела. Тако овде имамо пивот табелу која приказује дане са леве стране, категорију преко врха и приход у срцу пивот табеле.

Сада, да бих креирао извештај од месеца до данас, оно што ћу урадити је да кажем да ћу овде додати нови помоћни ступац својим оригиналним подацима и то ће проверити две ствари. И зато што проверавам две ствари које ћу користити функцију АНД, обе ствари морају бити тачне да би могло бити месец до данас. А ја ћу овде користити функцију која се зове ДАНАС. ДАНАС, у реду, па желим да знам да ли је МЕСЕЦ ДАНАС () = МЕСЕЦ тог датума тамо у колони А. Ако је то тачно, ако је то тренутни месец, дакле другим речима ако је април, онда проверите и видите да ли је дан тог датума тамо у А2 <= ДАН ДАНАС. Лепа ствар је када отворимо ову радну свеску сутра или за недељу дана, данашњи дан ће се аутоматски ажурирати и двоструким кликом ћемо то копирати.

У реду сада, морамо да унесемо ове додатне податке у нашу пивот табелу, па долазим овде, Пивот Табле, анализирам и није тако тешко променити извор података, само кликните то велико дугме тамо и реците да желимо да пређемо на колону Д , притисните ОК. У реду, сада имамо то додатно поље, убацићу резач на основу тог поља Месец до датума и желим само да видим како је наш Месец до датума тачан. Сад, да ли нам треба тај Слице да буде толико велик? Не, вероватно можемо да направимо то у две колоне и да то некако буде неупадљиво са десне стране. Дакле, сада имамо све датуме у 2016. години и све датуме у 2017. години; мада, било би заиста супер упоређивати их упоредо. Па ћу узети то поље за датум и анализирати. Групираћу поље, груписаћу га до само Године. Ја неЗаправо их није брига за поједине дане. Само желим да знам месец до данас. Сад, где смо Дакле, груписаћу га по годинама и тамо ћемо завршити ове две године, а ја ћу то преуредити, ставити те године да иду преко, категорије да опадају. И сад видим где смо били прошле године, а где ове године. У реду сада, јер сам извршио груписање, више ми није дозвољено да креирам израчунато поље унутар пивот табеле. Ако бих желео да тамо имам годишњи износ, кликнуо бих десним тастером миша, Уклони свеукупно, у реду, и сада смо, дакле,% Промена, налазимо се изван пивот табеле која показује унутар пивот табеле . Морамо бити сигурни да смо или искључили ГетПивотДата или само изградили формулу попут ове: = Ј4 / И4-1 и то ствара формулу коју можемо копирати без икаквих гњаважа, попут оне.У реду, Мике, да видимо шта имаш.

Мике Гирвин: Хвала ,. Да, послао сам питање јер сам то урадио са формулама и нисам могао да схватим како то да урадим са стандардном пивот табелом, а онда сам се сетио да сам током година видео гомилу супер видео снимака о помоћним колонама и пивот табелама . То је лепа формула и лепо решење. Дакле, то је како то учинити са пивот табелом, идемо да видимо како се то ради помоћу формуле.

Сада то радим два дана након што је он то учинио. Ф2 Имам функцију ДАНАС која ће увек бити информација о датуму за данашњи тренутни датум која ће се користити овде наведеним формулама јер желимо да се ажурира. Такође сам користио Екцел табелу и она се зове ФСалес. Ако притиснем Цтрл + стрелица надоле, видим да је 4/14, али желим да могу да додам најновије записе и укључим ажурирање формула када пређемо на следећи месец. Цтрл + стрелица нагоре. У реду, имам заглавља година као заглавља колона, категорију као заглавља редова, а затим ће подаци из месеца и дана доћи из те ћелије. Дакле, једноставно ћу користити функцију СУМИФС, јер додајемо са више услова, опсег збира је приход, користићемо тај сјајни трик за Екцел табелу.Тачно на врху видимо ону црну стрелицу усмјерену надоле, БАМ! То ставља правилно име табеле, а затим у угластим заградама име поља, зарез. Опсег критеријума, мораћемо да користимо датум два пута, па ћу почети са датумом. Кликните, ту је ступац са датумом, зарез. Сада сам у априлу, па морам створити услов> = до 1. априла. Дакле, упоредни оператори “> =” у двоструким наводницима и придружићу му се. Сада морам да креирам неку датумску формулу која увек изгледа овде и креира први у месецу за ову одређену годину. Па ћу користити функцију ДАТЕ. Година, па имам годину као заглавље колоне и притиснућу тастер Ф4, два пута да закључам ред, али не и колону, па кад се пресели овде, прећи ћемо на 2017., зарез, Месец - ја 'м ћу користити функцију МОНТХ да добијем месец од 1 до 12. То је који год месец био у тој ћелији, Ф4 да га закључам у свим правцима, затворим заграде, а затим зарез, 1 увек ће бити 1. од месец без обзира који је месец, затворите заграде.

У реду, то су критеријуми. Увек ће бити> = првог у месецу, зарез, распон критеријума два. Добићу своју колону Датум, зарез. Критеријуми два, па, ово ће бити <= горња граница, тако да у „<=“ и &. Идем да варам, пази ово. Само ћу копирати ово одавде, јер је то иста ствар, Цтрл-Ц Цтрл-В, осим Дана, морамо да користимо функцију ДАИ и увек као горњу границу добијемо било који дан из овог месеца . Ф4 да бисте га закључали у свим правцима, затворите заграде на Датум. У реду, то су наша два критеријума: зарез. Распон критеријума 3, то је категорија. Ено га, зарез и ту је наше заглавље реда. Дакле, овај морамо Ф4 један два пута три пута, закључати колону, али не и ред, па кад копирамо формулу, прећи ћемо на Гизмо и Видгет,блиска заграда и то је формула. Превуците, двапут кликните и пошаљите доле. Видим да има проблема. Боље да дођем до последње ћелије најудаљеније. Притисните Ф2. Сада је подразумевано понашање Номенклатуре формула табеле када копирате формуле са стране, стварни ступци се померају као да су мешане референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сада приметите када га копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то спречава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада наша% Цханге формула = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.Превуците, двапут кликните и спустите. Видим да има проблема. Боље да дођем до последње ћелије најудаљеније. Притисните Ф2. Сада је подразумевано понашање Номенклатуре формула табеле када копирате формуле са стране, стварни ступци се померају као да су мешане референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сад приметите да када копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то спречава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.Превуците, двапут кликните и пошаљите доле. Видим да има проблема. Боље да дођем до последње ћелије најудаљеније. Притисните Ф2. Сада је подразумевано понашање Номенклатуре формула табеле када копирате формуле са стране, стварни ступци се померају као да су мешане референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сада приметите када га копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то спречава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.Боље да дођем до последње ћелије најудаљеније. Притисните Ф2. Сада је подразумевано понашање Номенклатуре формула табеле када копирате формуле са стране, стварни ступци се померају као да су мешане референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сада приметите када га копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то спречава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.Боље да дођем до последње ћелије најудаљеније. Притисните Ф2. Сада је подразумевано понашање Номенклатуре формула табеле када копирате формуле са стране, стварни ступци се померају као да су мешане референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сада приметите када га копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то спречава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.стварни ступци се крећу као да су мешовите референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сада приметите када га копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то спречава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.стварни ступци се крећу као да су мешовите референце ћелија. Сад бисмо могли да их закључамо, али овога пута то нећу учинити. Сада приметите када га копирате, то добро функционише, али када копирате на страну, тада се крећу стварни ступци. Па пазите ово, идем на Цтрл + Ц и Цтрл + В, а то избегава померање Ф у колоне када га копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.м иде на Цтрл + Ц и Цтрл + В, а то избегава померање колона Ф када је копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.м иде на Цтрл + Ц и Цтрл + В, а то избегава померање колона Ф када је копирате у страну. Двапут кликните и пошаљите га доле. Сада је наша формула% промене = крајњи износ / почетни износ -1, Цтрл + Ентер, двапут кликните и пошаљите га доле.

Сада, пре него што кренемо да га тестирамо, сада додајте неке нове записе. Заправо желим да створим ову етикету овде, тако да је динамична. А начин на који ћу то урадити је да кажем = знак и урадићемо текстуалну формулу, па кад год пожелимо текст и формулу, морате је ставити: „и ја сам идем да откуцам Продаја Између, размак “& и сада тамо морам да издвојим од тог једног датума, првог у месецу до краја месеца. Користићу функцију ТЕКСТ. Функција ТЕКСТ може узимати датуме бројева или серијске бројеве, зарезе и користити неко прилагођено форматирање бројева у ”. Увек желим да видим скраћеницу од три слова за месец, ммм, увек је желим као прву. Сад ако овде ставим 1, размак зарез иии, то неће успети. Жели да види да ли нам то даје вредност или зато што му се то не свиђа 1. Али ми 'дозвољено је уметање једног знака ако користимо косу црту, то је у обликовању прилагођеног броја. Обликовање прилагођених бројева мм и ии ће се разумети као месец и година, а сада ће формат Цустом Нумбер разумети уметање броја 1. Ф2 и сада ћемо једноставно: & “-” & ТЕКСТ те зарезе и сада ћемо Само ћу користити право обликовање бројева: „ммм размак Д, ггг“) Цтрл + Ентер.

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.

У реду, па, хеј, желим да се захвалим свима што су навратили. Видећемо се следећи пут за још један двобојни Екцел подцаст од и Екцел је забаван.

Скини докуменат

Преузмите датотеку узорка овде: Дуел181.клсм