Mesiac k dnešnému dňu - tipy pre Excel

Ako zobraziť mesačné tržby v kontingenčnej tabuľke. Toto je epizóda Dueling Excel.

Pozeraj video

Billova metóda
Pridajte pomocnú bunku so vzorcom MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Pridajte toto pole ako Slicer, kde = True
Bonusový tip: Skupinové denné dáta až do rokov
Pridajte výpočet mimo kontingenčnej tabuľky a vyhnite sa GetPivotData
Mikeov prístup:
Pomocou Ctrl + T urobte z údajov tabuľku. To umožňuje pridať do tabuľky viac údajov a aktualizovať vzorce.
SUMIFS s funkciami DATE, MONTH, DAY
Stlačením klávesu F4 trikrát uzamknete odkaz iba na stĺpec.
Dávajte si pozor - ak potiahnete vzorec tabuľky do strán, stĺpce sa zmenia. Kopírovanie a prilepenie - žiadne problémy
Použitie TEXTU (dátum, formát. Pekný trik s 1 na vloženie čísla 1 do textu

Prepis videa

Bill Jelen: Hej, vitaj späť. Je čas na ďalší podcast Dueling Excel. Som Bill Jelen z. Doplní ma Mike Girvin z programu Excel Is Fun.

Toto je naša kontingenčná tabuľka epizódy 181: Mesiac od dátumu.

Hej, dnešná otázka - dnešný nápad na tento duel posiela Mike. Hovorí: „Môžete vytvoriť prehľad za celý mesiac v kontingenčnej tabuľke?“

V poriadku, poďme. Takže tu je to, čo máme, máme dvojročné dátumy od januára 2016 až do roku 2017. Teraz to samozrejme nahrávam v apríli, teraz je 15. apríla, keď nahrávam svoj kúsok z duelu. Takže tu máme kontingenčnú tabuľku, ktorá zobrazuje dni na ľavej strane, kategóriu v hornej časti a výnosy v strede kontingenčnej tabuľky.

Teraz, aby som vytvoril prehľad za celý mesiac, urobím len to, že poviem, že sem pridám nový stĺpec pomocníka k svojim pôvodným údajom a skontrolujem dve veci. A pretože kontrolujem dve veci, ktoré budem používať funkciu AND, obe veci musia byť pravdivé, aby to bol Month To Date. A tu použijem funkciu s názvom DNES. DNES, v poriadku, takže chcem vedieť, či MESIAC DNES ()) je = k MESIACU daného dátumu tam v stĺpci A. Ak je to pravda, ak je to aktuálny mesiac, teda inými slovami, ak je apríl, potom skontrolujte, či je tam deň daného dňa v A2 <= DEŇ DNES. Krásne je, že keď otvoríme tento zošit zajtra alebo o týždeň, dnešný deň sa automaticky aktualizuje a dvojitým kliknutím ho skopírujeme.

V poriadku, teraz musíme získať tieto ďalšie údaje do našej kontingenčnej tabuľky, takže prídem sem, kontingenčná tabuľka, analyzujem a nie je také ťažké zmeniť zdroj údajov, stačí kliknúť na to veľké tlačidlo a povedať, že chceme prejsť do stĺpca D , kliknite na OK. Dobre, takže teraz máme to extra pole, idem vložiť Slicer na základe tohto poľa Month To Date a chcem iba vidieť, ako je náš Month To Date pravdivý. Potrebujeme, aby ten plátok bol taký veľký? Nie, pravdepodobne to môžeme urobiť tak, že to budú dva stĺpce a bude to tam akosi nenápadné tam na pravej strane. Takže teraz máme všetky dátumy v roku 2016 a všetky dátumy v roku 2017; aj keď, bolo by naozaj super porovnávať ich vedľa seba. Takže vezmem to pole Dátum a analyzujem. Chystám sa zoskupiť pole, zoskupím to iba na roky. Ja nieVlastne sa nestarám o jednotlivé dni. Chcel by som vedieť iba mesiac od dátumu. Takže, kde sme? Takže to zoskupím do rokov a my tam skončíme s týmito 2 rokmi a potom to usporiadam, vložím tie roky, ktoré pôjdem naprieč, kategórie dolu. A teraz vidím, kde sme boli minulý rok a kde sme boli tento rok. Dobre, teraz, pretože som zoskupil, už nemôžem vytvárať vypočítané pole v kontingenčnej tabuľke. Ak by som tam chcel mať medziročnú sumu, klikol by som pravým tlačidlom myši, Odstrániť celkový súčet, v poriadku, a teraz sme, takže,% Change, sme mimo kontingenčnej tabuľky smerujúcej do kontingenčnej tabuľky . Musíme sa uistiť, či buď vypneme GetPivotData, alebo len vytvoríme vzorec ako je tento: = J4 / I4-1, ktorý vytvorí vzorec, ktorý môžeme bez problémov kopírovať dole.Dobre, Mike, pozrime sa, čo máš.

Mike Girvin: Vďaka ,. Áno, otázku som poslal, pretože som to urobil pomocou vzorcov a nemohol som prísť na to, ako to urobiť so štandardnou kontingenčnou tabuľkou, a potom som si spomenul, že som v priebehu rokov videl veľa skvelých videí o pomocných stĺpcoch a kontingenčných tabuľkách . To je nádherný vzorec a nádherné riešenie. Takže takto to urobíme s kontingenčnou tabuľkou, poďme sa pozrieť, ako to urobiť pomocou vzorca.

Teraz to robím dva dni potom, čo to urobil. F2 Mám funkciu DNES, ktorá bude vždy informáciou o dátume pre dnešný aktuálny dátum, ktorá sa použije vo vzorcoch dole, pretože ju chceme aktualizovať. Tiež som použil tabuľku programu Excel a volá sa FSales. Ak stlačím Ctrl + šípka dole, vidím, že je to 4/14, ale chcem, aby som mohol pridať najnovšie záznamy a mať aktualizáciu vzorcov zahrnutú, keď prejdeme na nasledujúci mesiac. Ctrl + šípka hore. Dobre, ako hlavičky stĺpcov mám Rokové kritériá, ako hlavičky riadkov kategóriu a z tejto bunky potom budú pochádzať údaje za mesiac a deň. Takže jednoducho použijem funkciu SUMIFS, pretože pridávame s viacerými podmienkami, rozsah súčtu je výnos, použijeme tento skvelý trik pre tabuľku Excel.Hneď na vrchu vidíme tú čiernu šípku smerujúcu dole, BAM! To vloží správny názov tabuľky a potom do hranatých zátvoriek názov poľa, čiarka. Rozsah kritérií, budeme musieť dvakrát použiť Dátum, takže začnem Dátumom. Kliknite, je tu stĺpec dátumu, čiarka. Teraz som v apríli, takže potrebujem vytvoriť podmienku> = do 1. apríla. Takže porovnávacie operátory „> =“ v úvodzovkách a pripojím sa k tomu. Teraz musím vytvoriť nejaký vzorec dátumu, ktorý tu bude vždy vyzerať a vytvorí prvý v mesiaci pre tento konkrétny rok. Budem teda používať funkciu DATE. Rok, dobre, mám rok ako hlavičku stĺpca a narazím na kláves F4, dvakrát, aby som uzamkol riadok, ale nie stĺpec, takže keď sa presunie sem, presunieme sa na rok 2017, čiarka, Mesiac - japoužijem funkciu MESIAC na získanie čísla mesiaca 1 až 12. To je akýkoľvek mesiac, ktorý je v tejto bunke, F4 na uzamknutie vo všetkých smeroch, zatvorte zátvorky a potom čiarku, 1 vždy to bude 1. z mesiac bez ohľadu na to, aký je tento mesiac, zatvorte zátvorky.

Dobre, takže to sú kritériá. Vždy to bude> = prvý v mesiaci, čiarka, rozsah kritérií dva Dostanem stĺpec Dátum, čiarka. Kritérium dva, bude to <= horná hranica, takže v „<=“ a &. Idem podvádzať, sledujte toto. Len to skopírujem odtiaľto hore, pretože je to to isté, Ctrl-C Ctrl-V okrem Day, musíme použiť funkciu DAY a vždy dostať ako svoju hornú hranicu akýkoľvek deň z tohto konkrétneho mesiaca . F4 na uzamknutie vo všetkých smeroch, zatvorte zátvorky v Date. Dobre, takže to sú naše dve kritériá: čiarka. Rozsah kritérií 3, je to kategória. Tam to, čiarka, a tam je naša hlavička riadku. Takže tento musíme F4 jeden dva trikrát, uzamknúť stĺpec, ale nie riadok, takže keď skopírujeme vzorec dole, presunieme sa na Gizmo a Widget,úzka zátvorka a to je vzorec. Potiahnite, dvakrát kliknite a odošlite to dole. Vidím, že sú problémy. Radšej prídem do poslednej cely diagonálne najďalej. Stlačte F2. Teraz je predvolené správanie pre názvoslovie vzorcov tabuľky také, že keď kopírujete vzorce na stranu, skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.Potiahnite, dvakrát kliknite a odošlite to dole. Vidím, že sú problémy. Radšej prídem do poslednej cely diagonálne najďalej. Stlačte F2. Teraz je predvolené správanie pre názvoslovie vzorcov tabuľky také, že keď kopírujete vzorce na stranu, skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.Potiahnite, dvakrát kliknite a odošlite to dole. Vidím, že sú problémy. Radšej prídem do poslednej cely diagonálne najďalej. Stlačte F2. Teraz je predvolené správanie pre názvoslovie vzorcov tabuľky také, že keď kopírujete vzorce na stranu, skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.Radšej prídem do poslednej cely diagonálne najďalej. Stlačte F2. Teraz je predvolené správanie pre názvoslovie vzorcov tabuľky také, že keď kopírujete vzorce na stranu, skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.Radšej prídem do poslednej cely diagonálne najďalej. Stlačte F2. Teraz je predvolené správanie pre názvoslovie vzorcov tabuľky také, že keď kopírujete vzorce na stranu, skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.skutočné stĺpce sa pohybujú, akoby išlo o zmiešané odkazy na bunky. Teraz by sme ich mohli zamknúť, ale tentoraz to neurobím. Teraz si všimnite, že keď to kopírujete, funguje to dobre, ale keď kopírujete na stranu, keď sa pohybujú skutočné stĺpce. Takže toto sledujte, idem na Ctrl + C a Ctrl + V a potom sa vyhnete tomu, aby sa F pri kopírovaní do strany pohybovalo stĺpcom. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.idem na Ctrl + C a Ctrl + V a potom to zabráni tomu, aby sa F pri kopírovaní na stranu pohlo do stĺpcov. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.idem na Ctrl + C a Ctrl + V a potom to zabráni tomu, aby sa F pri kopírovaní na stranu pohlo do stĺpcov. Dvakrát kliknite a odošlite ho dole. Teraz náš vzorec% Change = konečná suma / počiatočná suma -1, Ctrl + Enter, dvakrát kliknite a odošlite ju dole.

Teraz, predtým ako to otestujeme, teraz pridajte nejaké nové záznamy. Vlastne tu chcem vytvoriť toto označenie, aby bolo dynamické. A spôsob, ako to urobím, je, že poviem = podpísať a urobíme textový vzorec, takže kedykoľvek budeme chcieť text a vzorec, musíte to vložiť: „a ja napíšem Predaj medzi, medzera ”& a teraz tam musím extrahovať z tohto jediného dátumu, prvého mesiaca do konca mesiaca. Budem používať funkciu TEXT. Funkcia TEXT môže obsahovať číselné dáta alebo sériové čísla, čiarky a použiť niektoré vlastné formátovanie čísel v ”. Vždy chcem vidieť trojpísmenovú skratku pre mesiac, mmm, vždy ju chcem ako prvú. Teraz, keď sem vložím 1, čiarka, rrr, to nebude fungovať. Chce vidieť, že to pre nás znamená hodnotu, alebo preto, že sa jej to nepáči. 1. Ale myje povolené vložiť jeden znak, ak použijeme lomku, ktorá je vo formáte vlastného čísla. Mm a yy bude pochopené formátovaním vlastného čísla ako mesiac a rok a teraz formát vlastného čísla pochopí vloženie čísla 1. F2 a teraz jednoducho ideme na: & „-“ & TEXT tejto čiarky a teraz my Použijem iba formátovanie priameho čísla: „mmm medzerníkD, rrr“) 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.

Dobre, dobre, chcem sa poďakovať všetkým, že sa zastavili. Uvidíme sa nabudúce pri ďalšom Dueling Excel Podcastu z a Excelu je zábava.

Stiahnuť súbor

Stiahnutie vzorového súboru nájdete tu: Duel181.xlsm