Měsíc k dnešnímu dni - Excel tipy

Jak zobrazit měsíční tržby v kontingenční tabulce. Toto je epizoda souboje Excel.

Sledovat video

Billova metoda
Přidejte pomocnou buňku pomocí vzorce MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Přidejte toto pole jako průřez, kde = True
Bonusový tip: Seskupujte denní data až na roky
Přidejte výpočet mimo kontingenční tabulku a vyhněte se GetPivotData
Mikeův přístup:
Proměňte data v tabulku pomocí Ctrl + T. To umožňuje přidat do tabulky více dat a aktualizovat vzorce.
SUMIFY s funkcemi DATE, MONTH, DAY
Stisknutím klávesy F4 třikrát uzamknete odkaz pouze na sloupec.
Dávejte pozor - pokud přetáhnete vzorec tabulky do strany, sloupce se změní. Kopírování a vkládání - žádné problémy
Použití TEXTU (datum, formát. Pěkný trik s 1 k vložení čísla 1 do textu

Přepis videa

Bill Jelen: Hej, vítej zpět. Je čas na další podcast Dueling Excel. Jsem Bill Jelen. Přidá se ke mně Mike Girvin z aplikace Excel Is Fun.

Toto je naše kontingenční tabulka epizody 181: Měsíc k dnešnímu dni.

No, hej, dnešní otázka - dnešní myšlenku na tento duel poslal Mike. Říká: „Můžete vytvořit sestavu Měsíc k dnešnímu dni v kontingenční tabulce?“

Dobře, pojďme. Takže tady je to, co máme, máme dva roky rande od ledna 2016 až do roku 2017. Teď to samozřejmě nahrávám v dubnu, teď je 15. dubna, když nahrávám svůj kousek duelu. A tak tady máme kontingenční tabulku zobrazující dny na levé straně, kategorii nahoře a výnosy v srdci kontingenční tabulky.

Nyní, abych vytvořil přehled Měsíc k dnešnímu dni, to, co udělám, je, že řeknu, že sem přidám nový sloupec pomocníka do svých původních dat a to zkontroluje dvě věci. A protože kontroluji dvě věci, které budu používat funkci AND, obě věci musí být pravdivé, aby to byl Měsíc k dnešnímu dni. A já zde použiji funkci nazvanou DNES. DNES, v pořádku, takže chci vědět, jestli MĚSÍC DNES ()) je = k MĚSÍCI daného data tam ve sloupci A. Pokud je to pravda, pokud je to aktuální měsíc, tedy jinými slovy, pokud je duben, pak zkontrolujte a zkontrolujte, zda je tam den daného data v A2 <= DEN DNES. Krásné je, že když tento sešit otevřeme zítra nebo o týden, automaticky se aktualizuje den dneška a dvojitým kliknutím jej zkopírujeme.

Dobře, teď musíme dostat tato další data do naší kontingenční tabulky, takže sem přijdu kontingenční tabulka, analyzuji a není tak těžké změnit zdroj dat, stačí kliknout na to velké tlačítko a říct, že chceme přejít do sloupce D , klikněte na OK. Dobře, takže teď máme toto pole navíc, vložím Slicer na základě tohoto měsíce k datu a chci jen vidět, jak je náš měsíc k dnešnímu dni pravdivý. Potřebujeme, aby ten Plátek byl tak velký? Ne, pravděpodobně to můžeme udělat jako dva sloupce a tak trochu to mít nenápadné tam na pravé straně. Takže teď máme všechna data v roce 2016 a všechna data v roce 2017; i když by bylo opravdu skvělé porovnávat je vedle sebe. Takže vezmu toto pole Datum a analyzuji. Chystám se seskupit pole, seskupím to jen na roky. Já neVe skutečnosti se nestarám o jednotlivé dny. Jen chci vědět Měsíc k dnešnímu dni. Kde jsme teď? Takže to seskupím do Years a my skončíme s těmi 2 roky tam a já to potom přeuspořádám, dát ty Years to go across, Categories to go down. A teď vidím, kde jsme byli minulý rok a kde jsme byli letos. Dobře, teď, protože jsem seskupil, už nemám dovoleno vytvářet vypočítané pole uvnitř kontingenční tabulky. Kdybych tam chtěl mít meziroční částku, kliknul bych pravým tlačítkem, Odebrat celkový součet, v pořádku, a teď jsme, takže,% Change, jsme mimo kontingenční tabulku směřující dovnitř kontingenční tabulky . Musíme se ujistit, že buď vypneme GetPivotData, nebo jen vytvoříme vzorec jako je tento: = J4 / I4-1 a tím se vytvoří vzorec, který si můžeme zkopírovat bez jakýchkoli potíží.Dobře, Mike, podívejme se, co máš.

Mike Girvin: Díky ,. Ano, poslal jsem otázku, protože jsem to udělal pomocí vzorců a nemohl jsem přijít na to, jak to udělat se standardní kontingenční tabulkou, a pak jsem si vzpomněl, že jsem v průběhu let viděl spoustu skvělých videí o pomocných sloupcích a kontingenčních tabulkách . To je krásný vzorec a krásné řešení. Takže to je způsob, jak to udělat s kontingenční tabulkou, pojďme se podívat, jak to udělat pomocí vzorce.

Dělám to dva dny poté, co to udělal. F2 Mám funkci DNES, která bude vždy informací o datu pro dnešní aktuální datum, které budou použity ve vzorcích níže, protože ji chceme aktualizovat. Také jsem použil tabulku Excel a má název FSales. Pokud jsem Ctrl + šipka dolů, vidím, že je to 4/14, ale chci mít možnost přidat nejnovější záznamy a zahrnout aktualizaci našich vzorců, když skočíme na další měsíc. Ctrl + šipka nahoru. Dobře, jako záhlaví sloupců mám Kritéria roku, kategorii jako záhlaví řádků a poté z této buňky budou pocházet údaje za měsíc a den. Jednoduše tedy použiji funkci SUMIFS, protože přidáváme s více podmínkami, rozsah součtu zde je výnos, použijeme ten skvělý trik pro tabulku Excel.Vpravo nahoře vidíme tu černou šipku směřující dolů, BAM! Tím se vloží správný název tabulky a poté do hranatých závorek název pole, čárka. Rozsah kritérií, budeme muset použít Date dvakrát, takže začnu s Date. Klikněte, je tu sloupec data, čárka. Teď jsem v dubnu, takže musím vytvořit podmínku> = do 1. dubna. Takže srovnávací operátory „> =“ v uvozovkách a připojím se k tomu. Teď musím vytvořit nějaký vzorec data, který vždy vypadá tady a vytvoří první v měsíci pro tento konkrétní rok. Budu tedy používat funkci DATE. Rok, dobře, mám rok jako záhlaví sloupce a stisknu klávesu F4, dvakrát, abych uzamkl řádek, ale ne sloupec, takže když se přesune sem, přesuneme se na rok 2017, čárka, Měsíc - jám použiji funkci MĚSÍC k získání čísla měsíce 1 až 12. To je jakýkoli měsíc v té buňce, F4 to uzamkne ve všech směrech, zavře závorky a pak čárku, 1 vždy to bude 1. z měsíc bez ohledu na to, o jaký měsíc se jedná, zavřete závorky.

Dobře, tak to jsou kritéria. Vždy to bude> = první v měsíci, čárka, rozsah kritérií dva Dostanu sloupec Datum, čárka. Kritérium dva, toto bude <= horní hranice, takže v „<=“ a &. Budu podvádět, sleduj to. Prostě to zkopíruji odsud, protože je to to samé, Ctrl-C Ctrl-V kromě Den, musíme použít funkci DEN a vždy dostat jako naši horní hranici bez ohledu na den z tohoto konkrétního měsíce . Klávesou F4 ji uzamknete ve všech směrech, uzavřete v závorkách datum. Dobře, takže to jsou naše dvě kritéria: čárka. Kritéria jsou v rozsahu 3, jedná se o kategorii. Tady je, čárka a je tu naše hlavička řádku. Takže tento musíme F4 jeden dvakrát třikrát, uzamknout sloupec, ale ne řádek, takže když zkopírujeme vzorec dolů, přesuneme se na Gizmo a Widget,uzavřená závorka a to je vzorec. Přetáhněte, poklepejte a odešlete. Vidím, že jsou potíže. Raději přijdu do poslední cely diagonálně nejdále. Stiskněte F2. Výchozím chováním pro nomenklaturu vzorců tabulky je nyní, když kopírujete vzorce na stranu, skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.Přetáhněte, poklepejte a odešlete. Vidím, že jsou potíže. Raději přijdu do poslední cely diagonálně nejdále. Stiskněte F2. Výchozím chováním pro nomenklaturu vzorců tabulky je nyní, když kopírujete vzorce na stranu, skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.Přetáhněte, poklepejte a odešlete. Vidím, že jsou potíže. Raději přijdu do poslední cely diagonálně nejdále. Stiskněte F2. Výchozím chováním pro nomenklaturu vzorců tabulky je nyní, když kopírujete vzorce na stranu, skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.Raději přijdu do poslední cely diagonálně nejdále. Stiskněte F2. Výchozím chováním pro nomenklaturu vzorců tabulky je nyní, když kopírujete vzorce na stranu, skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.Raději přijdu do poslední cely diagonálně nejdále. Stiskněte F2. Výchozím chováním pro nomenklaturu vzorců tabulky je nyní, když kopírujete vzorce na stranu, skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.skutečné sloupce se pohybují, jako by to byly smíšené odkazy na buňky. Nyní bychom je mohli zamknout, ale tentokrát to neudělám. Nyní si všimněte, že když to zkopírujete, funguje to dobře, ale když zkopírujete na stranu, kdy se přesunou skutečné sloupce. Takže sledujte to, jdu na Ctrl + C a Ctrl + V a pak se vyhnete tomu, aby se F při přesunutí do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.jdu na Ctrl + C a Ctrl + V a pak to zabrání tomu, aby se F při kopírování do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.jdu na Ctrl + C a Ctrl + V a pak to zabrání tomu, aby se F při kopírování do strany pohybovalo sloupcům. Poklepejte a odešlete jej. Nyní náš vzorec% Change = koncová částka / počáteční částka -1, Ctrl + Enter, poklepejte a odešlete ji.

Než to otestujeme, přidejte nyní nové záznamy. Vlastně chci vytvořit tento štítek tady, aby byl dynamický. A způsob, jakým to udělám, je, že řeknu = podepsat a uděláme textový vzorec, takže kdykoli budeme chtít text a vzorec, musíte to zadat: „a já přejít na typ Prodej mezi, mezera ”& a teď tam musím extrahovat z toho jediného data, prvního měsíce do konce měsíce. Budu používat funkci TEXT. Funkce TEXT může mít číslo data nebo sériová čísla, čárku a použít některé vlastní formátování čísel v ”. Vždy chci vidět třípísmennou zkratku pro měsíc, mmm, vždy ji chci jako první. Teď, když sem vložím 1, čárka, yyy, to nebude fungovat. Chce vidět, že nám to dává hodnotu nebo proto, že se to nelíbí 1. Ale my 'je povoleno vložit jeden znak, pokud použijeme lomítko, které je ve formátu vlastního čísla. Formát mm a yy bude chápán formátováním vlastního čísla jako měsíc a rok a nyní formát vlastního čísla pochopí vložení čísla 1. F2 a nyní jednoduše přejdeme na: & „-“ & TEXT této čárky a nyní jsme Použiji pouze formátování přímých čísel: „mmm spaceD, yyy“) Ctrl + Enter.

Teď pojďme, než přidáme nějaká data, jednoduše to změňme. Předstíráme, že se dnes zobrazoval: 15. 3. 2017 se všechny vzorce aktualizují a náš textový vzorec je také Ctrl + Z. Nyní pojďme dolů do dolní části datové sady, Ctrl + šipka dolů Chci přidat jeden nový záznam. Jsem v poslední buňce datové sady, stiskem Tab přidám nový záznam do naší datové sady. Jednoduše zkopíruji tento záznam sem, Ctrl + šipka nahoru, a tam vidíme rozdíl. Pokud bychom chtěli porovnat tyto hodnoty vzorců s těmi, které byly provedeny: = relativní odkaz na buňku = kliknout na list, klikneme vpravo v I4. Nahoře jsme viděli náš vzorec, Ctrl + Enter. Vlastně to stáhnu dolů. Ctrl + Enter právě naplnilo vše, co jsem zvýraznil. A samozřejmě FALSE FALSE.No hádejte co? = ta částka právě tam -, klikněte na Ctrl + Šipka dolů, Ctrl + Backspace, takže to odečtu, abych to zkontroloval a dostatečně jistě, že to byla přesná částka, kterou bychom se tam mohli podívat.

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.

Dobře, dobře, chci poděkovat všem, že se zastavili. Uvidíme se příště na další podcast Dueling Excel od a Excel Is Fun.

Stáhnout soubor

Stáhněte si ukázkový soubor zde: Duel181.xlsm