Kako prikazati prodaju od mjeseca do datuma u zaokretnoj tablici. Ovo je epizoda Dueling Excela.
Gledaj video
- Billova metoda
- Dodajte pomoćnu ćeliju s MTD formulom
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Dodajte to polje kao Rezač gdje je = True
- Bonus savjet: Grupirajte dnevne datume do godina
- Dodajte izračun izvan zaokretne tablice uz izbjegavanje GetPivotData
- Mikeov pristup:
- Pretvorite podatke u tablicu pomoću Ctrl + T. To omogućuje dodavanje više podataka u tablicu i ažuriranje formula.
- SUMIFIRA s funkcijama DATUM, MJESEC, DAN
- Pritiskom na F4 tri puta zaključava se referenca samo na stupac.
- Pazite - ako bočno povučete formulu tablice, stupci će se promijeniti. Kopiraj i zalijepi - bez problema
- Korištenje TEKSTA (datum, format. Lijep trik s 1 za umetanje broja 1 u tekst
Prijepis videozapisa
Bill Jelen: Hej, dobrodošao natrag. Vrijeme je za još jedan dvobojni Excel Podcast. Ja sam Bill Jelen iz. Pridružit će mi se Mike Girvin iz Excela je zabavno.
Ovo je naša epizoda 181: Zaokretna tablica od mjeseca do danas.
Pa, hej, današnje pitanje - današnju ideju za ovaj dvoboj šalje Mike. Kaže, "Možete li stvoriti izvješće Mjesec do datuma u zaokretnoj tablici?"
Dobro, idemo. Dakle, evo što imamo, imamo dvije godine vrijedne datume od siječnja 2016. pa sve do 2017. Sada, naravno, ovo snimam u travnju, upravo je 15. travnja kad snimam svoj dio dvoboja. Tako ovdje imamo pivot tablicu koja prikazuje Dane s lijeve strane, Kategorija preko vrha i Prihod u srcu pivot tablice.
Sada, da bih stvorio izvješće Mjesec do danas, ono što ću učiniti je da kažem da ću ovdje dodati novi pomoćni stupac svojim izvornim podacima i to će provjeriti dvije stvari. I budući da provjeravam dvije stvari koje ću koristiti funkciju AND, obje stvari moraju biti Istinite da bi bilo Mjesec do danas. I ovdje ću upotrijebiti funkciju koja se zove DANAS. DANAS, u redu, pa želim znati je li MJESEC DANAS () = MJESEC tog datuma tamo u stupcu A. Ako je to istina, ako je to tekući mjesec, dakle drugim riječima ako je travanj, onda provjerite je li dan tog datuma tamo u A2 <= DAN DANAS. Lijepo je kad ovu radnu knjigu otvorimo sutra ili za tjedan dana, današnji dan će se automatski ažurirati i dvaput kliknemo da to kopiramo.
U redu, moramo dodati ove dodatne podatke u našu pivot tablicu, pa dolazim ovdje, Pivot Table, analiziram i nije tako teško promijeniti izvor podataka, samo kliknite taj veliki gumb tamo i recite da želimo prijeći na stupac D , kliknite U redu. U redu, sada, dakle, imamo to dodatno polje, umetnut ću rezač na temelju tog polja Mjesec do datuma i želim samo vidjeti kako je naš Mjesec do danas točan. Sad, trebamo li taj Krišak biti toliko velik? Ne, vjerojatno možemo učiniti da to budu dva stupca i da to nekako bude neupadljivo s desne strane. Dakle, sada imamo sve datume u 2016. i sve datume u 2017. godini; premda bi bilo stvarno super usporediti ih uporedo. Pa ću uzeti to polje Datum i Analizirati. Grupirat ću polje, grupirat ću ga na samo Godine. Ja neZapravo me nije briga za pojedine dane. Samo želim znati mjesec do danas. Sad, gdje smo? Tako da ću to grupirati po godinama i završit ćemo s ove dvije godine tamo, a ja ću to preurediti, staviti te godine da idu preko, kategorije da padnu. I sad vidim gdje smo bili prošle godine, a gdje ove godine. U redu sada, jer sam izvršio grupiranje, više ne smijem stvarati izračunato polje unutar zaokretne tablice. Da želim tamo imati godišnji iznos, kliknuo bih desnom tipkom, Ukloni ukupni zbroj, u redu, i sad smo, dakle,% Promjena, nalazimo se izvan zaokretne tablice usmjerene unutar zaokretne tablice . Moramo biti sigurni da smo isključili GetPivotData ili samo izgradili formulu poput ove: = J4 / I4-1 i to stvara formulu koju možemo kopirati bez ikakvih gnjavaža, poput one.Dobro, Mike, da vidimo što imaš.
Mike Girvin: Hvala ,. Da, poslao sam pitanje jer sam to učinio s formulama i nisam mogao shvatiti kako to učiniti sa standardnom zaokretnom tablicom, a onda sam se sjetio kako sam tijekom godina vidio hrpu super videa o pomoćnim stupcima i zaokretnim tablicama . To je lijepa formula i lijepo rješenje. Dakle, to je kako to učiniti s pivot tablicom, idemo vidjeti kako to učiniti s formulom.
Sad to radim dva dana nakon što je on to učinio. F2 Imam funkciju DANAS koja će uvijek biti informacija o datumu za današnji trenutni datum koja će se koristiti ovdje dolje navedenim formulama jer želimo da se ažurira. Također sam koristio Excel tablicu i zove se FSales. Ako Ctrl + strelica prema dolje, vidim da je 4/14, ali želim biti u mogućnosti dodati najnovije zapise i uključiti ažuriranje naših formula kada prijeđemo na sljedeći mjesec. Ctrl + strelica prema gore. Dobro, imam zaglavlja godina kao zaglavlja stupaca, kategoriju kao zaglavlja redaka, a zatim će podaci iz mjeseca i dana doći iz te ćelije. Dakle, jednostavno ću koristiti funkciju SUMIFS, jer zbrajamo s više uvjeta, raspon zbroja ovdje je prihod, koristit ćemo taj sjajni trik za Excel tablicu.Odmah na vrhu vidimo onu crnu strelicu usmjerenu prema dolje, BAM! To stavlja pravilno ime tablice, a zatim u uglastim zagradama naziv polja, zarez. Raspon kriterija, morat ćemo dva puta upotrijebiti datum, pa ću započeti s datumom. Kliknite, tu je stupac s datumom, zarez. Sada sam u travnju, pa moram stvoriti uvjet> = do 1. travnja. Dakle, usporedni operatori “> =” u dvostrukim navodnicima i pridružit ću mu se. Sada moram stvoriti neku datumsku formulu koja uvijek izgleda ovdje i kreira prvi u mjesecu za ovu određenu godinu. Pa ću koristiti funkciju DATE. Godina, dobro, imam godinu kao zaglavlje stupca i pritisnut ću tipku F4, dva puta da zaključam redak, ali ne i stupac, pa kad se pomakne ovdje, premjestit ćemo se na 2017., zarez, Mjesec - ja 'koristit ću funkciju MONTH da dobijem mjesec broj 1 do 12. To je koji god mjesec bio u toj ćeliji, F4 da ga zaključam u svim smjerovima, zatvorim zagrade, a zatim zarez, 1 to će uvijek biti 1. od mjesec bez obzira koji je ovo mjesec, zatvorite zagrade.
U redu, to su kriteriji. Uvijek će biti> = prvog u mjesecu, zarez, raspon kriterija dva. Dobit ću svoj stupac Datum, zarez. Kriteriji dva, pa, ovo će biti <= gornja granica, tako da u "<=" i &. Idem varati, pazi ovo. Samo ću ovo kopirati odavde, jer je to ista stvar, Ctrl-C Ctrl-V, osim Dana, moramo koristiti funkciju DAY i uvijek ćemo kao gornju granicu dobiti bilo koji dan u ovom mjesecu . F4 da biste ga zaključali u svim smjerovima, zatvorite zagrade na Datum. U redu, to su naša dva kriterija: zarez. Raspon kriterija 3, to je kategorija. Tu je, zarez i tu je naše zaglavlje retka. Dakle, ovaj moramo F4 jedan dva puta tri puta, zaključati stupac, ali ne i redak, pa kad kopiramo formulu, preći ćemo na Gizmo i Widget,bliska zagrada i to je formula. Povucite, dvaput kliknite i spustite. Vidim da ima problema. Bolje da dođem do zadnje stanice dijagonalno najudaljenije. Pritisnite F2. Sada je zadano ponašanje Nomenklature formula tablice kada kopirate formule sa strane, stvarni se stupci pomiču kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.Povucite, dvaput kliknite i spustite. Vidim da ima problema. Bolje da dođem do zadnje stanice dijagonalno najudaljenije. Pritisnite F2. Sada je zadano ponašanje Nomenklature formula tablice kada kopirate formule sa strane, stvarni se stupci pomiču kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.Povucite, dvaput kliknite i spustite. Vidim da ima problema. Bolje da dođem do zadnje stanice dijagonalno najudaljenije. Pritisnite F2. Sada je zadano ponašanje Nomenklature formula tablice kada kopirate formule sa strane, stvarni se stupci pomiču kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.Bolje da dođem do zadnje stanice dijagonalno najudaljenije. Pritisnite F2. Sada je zadano ponašanje Nomenklature formula tablice kada kopirate formule sa strane, stvarni se stupci pomiču kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.Bolje da dođem do zadnje stanice dijagonalno najudaljenije. Pritisnite F2. Sada je zadano ponašanje za Nomenklaturu formula tablice kada kopirate formule sa strane, stvarni se stupci pomiču kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.stvarni se stupci kreću kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.stvarni se stupci kreću kao da su miješane reference ćelija. Sad bismo ih mogli zaključati, ali ovaj put to neću učiniti. Sad primijetite kad ga kopirate, to dobro funkcionira, ali kad kopirate na stranu, tada se stvarni stupci pomiču. Dakle, pazite ovo, idem na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.m ide na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.m ide na Ctrl + C i Ctrl + V, a to izbjegava pomicanje F prema stupcima kada ga kopirate na stranu. Dvaput kliknite i pošaljite ga dolje. Sada je naša formula% Promjena = konačni iznos / početni iznos -1, Ctrl + Enter, dvaput kliknite i pošaljite ga dolje.
Prije nego što krenemo testirati, dodajte nove zapise. Zapravo želim stvoriti ovu oznaku ovdje gore, tako da je dinamična. A način na koji ću to učiniti je da kažem = znak i napravit ćemo tekstualnu formulu, tako da kad god poželimo tekst i formulu, morate je staviti: „i ja sam idem upisati Prodaja između, razmak ”& i sada tamo moram izdvojiti taj jedini datum, prvi u mjesecu do kraja mjeseca. Koristit ću funkciju TEXT. Funkcija TEXT može uzimati datume brojeva ili serijske brojeve, zareze i koristiti neko oblikovanje prilagođenih brojeva u ”. Uvijek želim vidjeti kraticu od tri slova za mjesec, mmm, uvijek je želim kao prvu. Ako ovdje stavim 1, razmak zarez yyy, to neće uspjeti. Želi vidjeti da li nam to daje vrijednost ili zato što mu se to ne sviđa 1. Ali mi 'dopušteno je umetanje jednog znaka ako koristimo kosu crtu prema naprijed, to je u oblikovanju prilagođenog broja. Oblikovanje prilagođenih brojeva mm i yy razumjet će se kao mjesec i godina, a sada će format prilagođenog broja razumjeti umetanje broja 1. F2, a sada ćemo jednostavno: & "-" & TEXT te zarezke i sada ćemo Samo ću upotrijebiti ravno oblikovanje brojeva: “mmm razmakD, yyy”) Ctrl + Enter.
Sada samo, prije nego što dodamo neke podatke, samo promijenimo ovo. Pretvarajući se da je današnji dan pokazivao: 15.3.2017. Upravo tako, sve se formule ažuriraju, a naša tekstualna formula je također, Ctrl + Z. Sada se spustimo na dno skupa podataka, Ctrl + strelica prema dolje. Želim dodati jedan novi zapis. Nalazim se u posljednjoj ćeliji skupa podataka, pritisnuo sam Tab da bih dodao novi zapis u naš skup podataka. Jednostavno ću kopirati ovaj zapis ovdje, Ctrl + strelica prema gore, i tamo možemo vidjeti razliku. Ako smo željeli provjeriti ove vrijednosti formula u odnosu na one jesu: = relativna referenca na ćeliju = klik na list, kliknut ćemo desno u I4. Gore smo mogli vidjeti našu formulu, Ctrl + Enter. Zapravo ću ga povući prema dolje. Ctrl + Enter je upravo popunio sve što sam istaknuo. I naravno, FALSE FALSE.Pa pogodite što? = taj iznos upravo tamo -, pritisnite Ctrl + strelica prema dolje, Ctrl + Backspace, pa ću to oduzeti samo da provjerim i budem siguran da je točan iznos koji bismo tamo mogli potražiti.
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.
Dobro, hej, želim zahvaliti svima što su navratili. Vidimo se sljedeći put za još jedan dvobojni Excel podcast od i Excel je zabavan.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Duel181.xlsm
