Kuukausi - Excel-vinkit

Kuinka näyttää kuukausittainen myynti pivot-taulukossa. Tämä on Dueling Excel -jakso.

Katso video

Billin menetelmä
Lisää auttajasolu MTD-kaavalla
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Lisää kenttä Sliceriksi, jossa = True
Bonusvinkki: Ryhmän päivittäiset päivämäärät jopa vuosiin
Lisää laskenta pivot-taulukon ulkopuolelle välttäen GetPivotDataa
Miken lähestymistapa:
Muunna tiedot taulukkoon painamalla Ctrl + T.Se lisää taulukkoon tietoja ja kaavat päivittyvät.
SUMIFS, joissa on DATE, MONTH, DAY
F4-painikkeen painaminen kolme kertaa lukitsee viitteen vain sarakkeeseen.
Varo - jos vedät taulukon kaavaa sivuttain, sarakkeet muuttuvat. Kopioi ja liitä - ei ongelmia
Käyttämällä TEXT (päivämäärä, muoto. Mukava temppu 1: llä lisätä numero 1 tekstiin

Videon transkriptio

Bill Jelen: Hei, tervetuloa takaisin. On aika uudelle Dueling Excel Podcastille. Olen Bill Jelen. Minuun liittyy Mike Girvin Excel Is Fun -sovelluksesta.

Tämä on jakso 181: Kuukausi-päivä-pivot-taulukko.

No, hei, tämän päivän kysymys - tämän ajatuksen tästä kaksintaistelusta lähetti Mike. Hän sanoo: "Voitteko luoda kuukausiraportin pivot-taulukkoon?"

Oikein, mennään. Joten tässä meillä on, meillä on kahden vuoden arvoiset päivämäärät tammikuusta 2016 aina vuoteen 2017 asti. Nyt tietysti nauhoitan tämän huhtikuussa, nyt on 15. huhtikuuta, kun nauhoitan teokseni kaksintaistelusta. Ja niin täällä on pivot-taulukko, joka näyttää Päivät vasemmalla puolella, Luokka ylhäällä ja Tulot pivot-pöydän sydämessä.

Luotakseni Kuukauden päivämäärä -raportin, aion sanoa, että aion lisätä uuden auttajasarakkeen täältä alkuperäisiin tietoihini, ja se tarkistaa kaksi asiaa. Ja koska tarkistan kahta asiaa, aion käyttää JA-toimintoa, molempien asioiden on oltava totta, jotta se olisi kuukausi. Ja aion käyttää täällä toimintoa nimeltä TÄNÄÄN. TÄNÄÄN, okei, joten haluan tietää, onko TÄNEN KUUKAUSI ()) kyseisen päivämäärän KUUKAUDESSA sarakkeessa A. Jos se on totta, jos se on kuluva kuukausi, toisin sanoen jos on huhtikuu, niin tarkista ja katso, onko kyseisen päivämäärän päivä tuolla A2: lla = = PÄIVÄ TÄNÄÄN. Kaunis asia on, kun avamme tämän työkirjan huomenna tai viikon kuluttua, päivän päivä päivittyy automaattisesti ja kopioimme tuplaklikkaamalla sen alas.

Selvä, nyt meidän on saatava nämä ylimääräiset tiedot pivot-taulukkoon, joten tulen tänne pivot-taulukko, analysoi ja tietolähteen vaihtaminen ei ole niin vaikeaa, napsauta vain sitä suurta painiketta siellä ja sano, että haluamme siirtyä sarakkeeseen D , napsauta OK. Selvä, joten nyt meillä on ylimääräinen kenttä, aion lisätä viipalointipalvelun kyseisen Kuukausi-päivämäärä-kentän perusteella ja haluan vain nähdä, kuinka Kuukausi-päivämäärä on totta. Tarvitsemmeko siipin olevan niin suuri? Ei, voimme luultavasti tehdä siitä kaksi saraketta ja juuri sellainen, että se on huomaamaton siellä oikealla puolella. Joten nyt meillä on kaikki päivämäärät vuonna 2016 ja kaikki päivämäärät vuonna 2017; vaikka olisi todella hienoa verrata näitä vierekkäin. Joten aion ottaa kyseisen päivämääräkentän ja analysoida. Aion ryhmitellä kentän, aion ryhmitellä sen vain vuosiin. Minä enEn todellakaan välitä yksittäisistä päivistä. Haluan vain tietää Kuukausi mennessä. Nyt missä olemme? Joten ryhmittelen sen vuosiin ja päädymme näihin kahteen vuoteen siellä, ja aion järjestää tämän uudelleen, laittaa nuo vuodet läpi, luokat laskea. Ja nyt näen missä olimme viime vuonna ja missä olimme tänä vuonna. Selvä nyt, koska olen suorittanut ryhmittelyn, en saa enää luoda laskettua kenttää pivot-taulukon sisällä. Jos haluaisin saada vuoden yli vuoden määrän, napsautan hiiren kakkospainikkeella, Poista loppusumma, okei, ja nyt olemme,% Muutos, olemme pivot-taulukon ulkopuolella, joka osoittaa pivot-taulukon sisällä . Meidän on varmistettava joko sammuttamalla GetPivotData tai rakentamalla vain tällainen kaava: = J4 / I4-1 ja se luo kaavan, jonka voimme kopioida alas ilman mitään vaivaa, tuollaista.Selvä, Mike, katsotaan mitä sinulla on.

Mike Girvin: Kiitos. Kyllä, lähetin kysymyksen osoitteeseen, koska tein sen kaavoilla, enkä kyennyt selvittämään, miten se tehdään tavallisella kääntötaulukolla, ja sitten muistan nähneeni vuosien varrella, tekemällä joukon hienoja videoita auttajasarakkeista ja kääntötaulukoista . Se on kaunis kaava ja kaunis ratkaisu. Joten miten se tehdään pivot-taulukolla, katsotaanpa, miten se tehdään kaavan avulla.

Nyt teen tämän kaksi päivää sen jälkeen, kun hän teki sen. F2 Minulla on TODAY-toiminto, joka tulee olemaan aina tämän päivän nykyisen päivämäärän päivämäärätietoja, joita kaavat käyttävät täällä, koska haluamme sen päivittyvän. Olen myös käyttänyt Excel-taulukkoa ja sen nimi on FSales. Jos painan Ctrl + alanuoli, näen sen olevan 4/14, mutta haluan pystyä lisäämään uusimmat tietueet ja sisällyttämään kaavamme päivitykseen, kun siirrymme seuraavaan kuukauteen. Ctrl + Ylänuoli. Selvä, minulla on Year Criteria sarakeotsikoina, Category rivin otsikoina ja sitten kuukauden ja päivän tiedot tulevat kyseisestä solusta. Joten aion yksinkertaisesti käyttää SUMIFS-funktiota, koska lisäämme useita ehtoja, summa-alue on tässä tulo, aiomme käyttää sitä hienoa temppua Excel-taulukkoon.Aivan yläosassa näemme tuon mustan alaspäin osoittavan nuolen, BAM! Se lisää oikean taulukon nimen ja sitten hakasulkeissa kentän nimen, pilkun. Kriteerialue, meidän on käytettävä päivämäärää kahdesti, joten aloitan päivämäärällä. Napsauta, siellä on päivämäärä sarake, pilkku. Nyt olen huhtikuussa, joten minun on luotava ehto> = 1. huhtikuuta. Joten vertailevat operaattorit “> =” lainausmerkeissä ja aion liittyä siihen. Nyt minun on luotava päivämääräkaava, joka näyttää aina täältä ja luo kuukauden ensimmäisen kuukauden tälle vuodelle. Joten aion käyttää PÄIVÄYS-toimintoa. Vuosi, minulla on vuosi oikea sarakkeen otsikkona ja aion lyödä F4-näppäintä kaksi kertaa lukitaksesi rivin, mutta ei saraketta, joten kun se siirtyy tänne, siirrymme vuoteen 2017, pilkku, Kuukausi - minäKäytän MONTH-funktiota saadaksesi kuukauden numerot 1-12. Se on mikä tahansa kuukausi solussa, F4 lukitsemaan sen kaikkiin suuntiin, sulkemaan sulkeet ja sitten pilkun, 1 se tulee aina olemaan 1. kuukausi riippumatta siitä, mikä kuukausi tämä on, sulje sulkeet.

Selvä, joten se on kriteeri. Se on aina> = kuukauden ensimmäinen, pilkku, kriteerialue kaksi, aion saada Päivämäärä -sarakkeen, pilkku. Kriteerit kaksi, no, tämä tulee olemaan <= yläraja, joten kohdissa “<=” ja &. Aion huijata, katso tätä. Kopioin tämän vain täältä, koska se on sama asia, Ctrl-C Ctrl-V paitsi päivä, meidän on käytettävä PÄIVÄ-toimintoa ja saatava aina ylärajaksi riippumatta siitä päivästä, joka on kyseisestä kuukaudesta . F4 lukitaksesi sen kaikkiin suuntiin, sulje sulkeet päivämäärällä. Selvä, joten se on kaksi kriteeriämme: pilkku. Kriteerialue 3, se on luokka. Siinä se on, pilkku ja rivin otsikko. Joten tämä meidän on F4 yksi kaksi kolme kertaa, lukittava sarake, mutta ei rivi, joten kun kopioimme kaavan alas, siirrymme Gizmo ja Widget,sulkeissa ja se on kaava. Vedä, kaksoisnapsauta ja lähetä se. Näen, että on ongelmia. Tulen paremmin viimeiseen soluun vinosti kauimpana. Napsauta F2. Nyt taulukon kaavan nimikkeistön oletuskäyttäytyminen on, kun kopioit kaavat sivulle, varsinaiset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten katsele tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.Vedä, kaksoisnapsauta ja lähetä se. Näen, että on ongelmia. Tulen paremmin viimeiseen soluun vinosti kauimpana. Napsauta F2. Nyt taulukon kaavan nimikkeistön oletuskäyttäytyminen on, kun kopioit kaavat sivulle, varsinaiset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten varo tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.Vedä, kaksoisnapsauta ja lähetä se. Näen, että on ongelmia. Tulen paremmin viimeiseen soluun vinosti kauimpana. Napsauta F2. Nyt taulukon kaavan nimikkeistön oletuskäyttäytyminen on, kun kopioit kaavat sivulle, varsinaiset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten katsele tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.Tulen paremmin viimeiseen soluun vinosti kauimpana. Napsauta F2. Nyt taulukon kaavan nimikkeistön oletuskäyttäytyminen on, kun kopioit kaavat sivulle, varsinaiset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten katsele tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.Tulen paremmin viimeiseen soluun vinosti kauimpana. Napsauta F2. Nyt taulukon kaavan nimikkeistön oletuskäyttäytyminen on, kun kopioit kaavat sivulle, varsinaiset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten varo tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.todelliset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten varo tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.todelliset sarakkeet liikkuvat ikään kuin ne olisivat sekoitettuja soluviittauksia. Nyt voimme lukita heidät, mutta en aio tehdä sitä tällä kertaa. Huomaa nyt, kun kopioit sen alas, se toimii hyvin, mutta kun kopioit sivulle, jossa todelliset sarakkeet liikkuvat. Joten varo tätä, aion käyttää Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.m menee Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin siirtymästä kopioimalla sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.m menee Ctrl + C ja Ctrl + V ja sitten se estää F: n sarakkeisiin liikkumasta, kun kopioit sen sivulle. Kaksoisnapsauta ja lähetä se alas. Nyt% Change -kaava = loppumäärä / alkusumma -1, Ctrl + Enter, kaksoisnapsauta ja lähetä se.

Nyt, ennen kuin testaamme sitä, lisää nyt uusia tietueita. Haluan itse asiassa luoda tämän tarran täältä, joten se on dynaaminen. Ja tapa, jolla teen sen, sanon = merkki ja teemme Teksti-kaavan, joten milloin tahansa haluamme tekstiä ja kaavaa, sinun on laitettava se: "ja minä olen kirjoitan Myynnin väliin, välilyönnin & & ja nyt minun täytyy poimia tuosta ainoasta päivämäärästä, kuukauden ensimmäisestä kuukauden loppuun. Aion käyttää TEXT-toimintoa. TEXT-toiminto voi viedä numeroiden päivämäärät tai sarjanumerot, pilkut ja käyttää jotakin mukautettua numeromuodostusta ”. Haluan aina nähdä kolmen kirjaimen lyhenteen kuukaudelle, mmm, haluan aina sen ensimmäisenä. Nyt jos laitan 1, pilkku väli yyy, se ei toimi. Haluaa nähdä, että se antaa meille arvon tai koska se ei pidä siitä 1. Mutta mesallitaan lisätä yksi merkki, jos käytämme eteenpäin kauttaviivaa, joka on mukautetun numeron muotoilussa. Mukautetun luvun muotoilu ymmärtää mm: n ja yy: n kuukaudeksi ja vuodeksi, ja nyt mukautetun luvun muoto ymmärtää lisäämään luvun 1. F2 ja nyt yksinkertaisesti aiotaan: & "-" & TEKSTI kyseiselle pilkulle ja nyt me Käytä vain suoraa numeromuotoa: "mmm väliD, vvv") 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.

Selvä, no hei, haluan kiittää kaikkia pysähtymisestä. Nähdään ensi kerralla uudelle Dueling Excel Podcastille ja Excel on hauskaa.

Lataa tiedosto

Lataa esimerkkitiedosto täältä: Duel181.xlsm