Jak wyświetlić sprzedaż od początku miesiąca w tabeli przestawnej. To jest odcinek Dueling Excel.
Obejrzyj wideo
- Metoda Billa
- Dodaj komórkę pomocniczą z formułą MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Dodaj to pole jako fragmentator, gdzie = True
- Wskazówka bonusowa: Codzienne daty grupowe do lat
- Dodaj obliczenia poza tabelą przestawną, unikając GetPivotData
- Podejście Mike'a:
- Zamień dane w tabelę za pomocą Ctrl + T. Umożliwia to dodanie większej ilości danych do tabeli i zaktualizowanie formuł.
- SUMA.WARUNKÓW z funkcjami DATA, MIESIĄC, DZIEŃ
- Trzykrotne naciśnięcie klawisza F4 blokuje odniesienie tylko do kolumny.
- Uważaj - jeśli przeciągniesz formułę tabeli w bok, kolumny zmienią się. Kopiuj i wklej - bez problemów
- Użycie TEKSTU (data, format. Niezła sztuczka z 1 do wstawienia cyfry 1 w tekście
Transkrypcja wideo
Bill Jelen: Hej, witaj z powrotem. Czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z. Dołączy do mnie Mike Girvin z Excel Is Fun.
To jest nasz odcinek 181: Tabela przestawna od początku miesiąca.
No cóż, dzisiejsze pytanie - dzisiejszy pomysł na ten pojedynek przysłał Mike. Mówi: „Czy możesz utworzyć raport od początku miesiąca w tabeli przestawnej?”
Dobra, chodźmy. Więc oto co mamy, mamy daty z dwóch lat od stycznia 2016 aż do 2017. Teraz oczywiście nagrywam to w kwietniu, jest 15 kwietnia, kiedy nagrywam swój pojedynek. Mamy tutaj tabelę przestawną pokazującą Dni po lewej stronie, kategorię u góry i Przychody w sercu tabeli przestawnej.
Teraz, aby utworzyć raport od początku miesiąca, zamierzam powiedzieć, że dodam tutaj nową kolumnę pomocnika do moich oryginalnych danych i sprawdzę dwie rzeczy. A ponieważ sprawdzam dwie rzeczy, zamierzam użyć funkcji AND, obie muszą być prawdziwe, aby było to od początku miesiąca. I użyję tutaj funkcji o nazwie DZIŚ. DZISIAJ, w porządku, więc chcę wiedzieć, czy MIESIĄC DZISIAJ ()) to = MIESIĄC tej daty w kolumnie A. Jeśli to prawda, jeśli jest to bieżący miesiąc, czyli innymi słowy, jeśli jest to kwiecień, to sprawdź i zobacz, czy ten dzień w A2 to <= DZIEŃ DZISIAJ. Piękną rzeczą jest to, że kiedy jutro lub za tydzień otworzymy ten skoroszyt, dzisiejszy dzień zostanie automatycznie zaktualizowany i klikniemy dwukrotnie, aby go skopiować.
W porządku, musimy pobrać te dodatkowe dane do naszej tabeli przestawnej, więc przychodzę tutaj Tabela przestawna, analizuj i nie jest trudno zmienić źródło danych, po prostu kliknij ten duży przycisk i powiedz, że chcemy przejść do kolumny D , Kliknij OK. W porządku, więc teraz mamy to dodatkowe pole, zamierzam wstawić fragmentator na podstawie tego pola od początku miesiąca i chcę tylko zobaczyć, jak nasza data od początku miesiąca jest prawdziwa. Czy potrzebujemy, żeby ten kawałek był tak duży? Nie, prawdopodobnie możemy sprawić, że będą to dwie kolumny i po prostu dyskretnie po prawej stronie. Więc teraz mamy wszystkie daty z 2016 roku i wszystkie z 2017 roku; chociaż byłoby naprawdę fajnie porównać je obok siebie. Więc wezmę to pole Data i przeanalizuję. Mam zamiar zgrupować pole, zgrupuję to tylko do lat. Ja nienie obchodzą mnie poszczególne dni. Chcę tylko wiedzieć od początku miesiąca. Gdzie teraz jesteśmy? Więc pogrupuję to w lata i skończę z tymi 2 latami, a potem zmienię to, wstawię te lata do przejścia, kategorie do zejścia. A teraz widzę, gdzie byliśmy w zeszłym roku, a gdzie w tym roku. W porządku, ponieważ skończyłem grupowanie, nie mogę już tworzyć pola obliczeniowego w tabeli przestawnej. Gdybym chciał mieć tam kwotę rok do roku, kliknąłbym prawym przyciskiem myszy, Usuń sumę końcową, w porządku, a teraz jesteśmy, więc zmiana procentowa, jesteśmy poza tabelą przestawną wskazującą wewnątrz tabeli przestawnej . Musimy upewnić się, że albo wyłączyliśmy GetPivotData, albo po prostu zbudujemy taką formułę: = J4 / I4-1, która tworzy formułę, którą możemy skopiować bez żadnych kłopotów.W porządku, Mike, zobaczmy, co masz.
Mike Girvin: Dzięki. Tak, wysłałem pytanie do, ponieważ zrobiłem to z formułami i nie mogłem wymyślić, jak to zrobić ze standardową tabelą przestawną, a potem przypomniałem sobie, że przez lata robiłem kilka fajnych filmów o kolumnach pomocniczych i tabelach przestawnych . To piękna formuła i piękne rozwiązanie. Więc tak to zrobić z tabelą przestawną, zobaczmy, jak to zrobić za pomocą formuły.
Teraz robię to dwa dni po tym, jak to zrobił. F2 Mam funkcję DZIŚ, która zawsze będzie informacją o dacie dla dzisiejszej bieżącej daty, która będzie używana przez formuły tutaj, ponieważ chcemy, aby została zaktualizowana. Użyłem również tabeli Excela i nazywa się FSales. Jeśli wykonam Ctrl + strzałka w dół, widzę, że jest 14 kwietnia, ale chcę mieć możliwość dodania najnowszych rekordów i uwzględnienia aktualizacji naszych formuł, gdy przejdziemy do następnego miesiąca. Ctrl + strzałka w górę. W porządku, mam kryteria roku jako nagłówki kolumn, kategorię jako nagłówki wierszy, a następnie dane dotyczące miesiąca i dnia będą pochodzić z tej komórki. Więc po prostu użyję funkcji SUMA.WARUNKÓW, ponieważ dodajemy wiele warunków, zakres sumy to przychód, użyjemy tej świetnej sztuczki dla tabeli Excela.Na samej górze widzimy tę czarną strzałkę skierowaną w dół, BAM! To umieszcza właściwą nazwę tabeli, a następnie w nawiasach kwadratowych nazwę pola, przecinek. Zakres kryteriów, będziemy musieli dwukrotnie użyć daty, więc zacznę od daty. Kliknij, jest kolumna z datami, przecinek. Teraz jestem w kwietniu, więc muszę utworzyć warunek> = do 1 kwietnia. Więc operatory porównawcze „> =” w podwójnych cudzysłowach i zamierzam do nich dołączyć. Teraz muszę utworzyć formułę daty, która zawsze wygląda tutaj i tworzy pierwszy dzień miesiąca dla tego konkretnego roku. Więc użyję funkcji DATA. Rok, cóż, mam rok dokładnie jako nagłówek kolumny i zamierzam nacisnąć klawisz F4 jeden, dwa razy, aby zablokować wiersz, ale nie kolumnę, więc kiedy się tu przesunie, przejdziemy do 2017, przecinek Miesiąc - jazamierzam użyć funkcji MIESIĄC, aby uzyskać numer miesiąca od 1 do 12. To znaczy, którykolwiek miesiąc jest w tej komórce, F4, aby zablokować go we wszystkich kierunkach, zamknij nawiasy, a następnie przecinek, 1 to zawsze będzie pierwszy miesiąc bez względu na to, który to miesiąc, zamknij nawiasy.
W porządku, więc takie są kryteria. Zawsze będzie> = pierwszy dzień miesiąca, przecinek, zakres kryteriów dwa Otrzymam kolumnę Data, przecinek. Kryterium drugie, cóż, to będzie <= górna granica, więc w „<=” i &. Mam zamiar oszukiwać, patrz na to. Po prostu skopiuję to stąd, ponieważ to to samo, Ctrl-C Ctrl-V z wyjątkiem dnia, musimy użyć funkcji DZIEŃ i zawsze otrzymujemy jako nasz górny limit, niezależnie od dnia z tego konkretnego miesiąca . F4, aby zablokować go we wszystkich kierunkach, zamknij nawiasy na Data. W porządku, więc to jest nasze kryterium dwa: przecinek. Zakres kryteriów 3, to jest Kategoria. Tam jest, przecinek i jest nasz nagłówek wiersza. Więc ten musimy F4 jeden dwa trzy razy, zablokować kolumnę, ale nie wiersz, więc kiedy skopiujemy formułę w dół, przejdziemy do Gizmo i Widget,zamknięty nawias i to jest wzór. Przeciągnij, kliknij dwukrotnie i wyślij w dół. Widzę, że są kłopoty. Lepiej pójdę do ostatniej celi położonej po przekątnej. Hit F2. Teraz domyślnym zachowaniem dla nomenklatury formuł tabel jest kopiowanie formuł na bok, a rzeczywiste kolumny przesuwają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.Przeciągnij, kliknij dwukrotnie i wyślij w dół. Widzę, że są kłopoty. Lepiej pójdę do ostatniej celi położonej po przekątnej. Hit F2. Teraz domyślne zachowanie w przypadku nomenklatury formuł tabel polega na skopiowaniu formuł na bok, a rzeczywiste kolumny przesuwają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.Przeciągnij, kliknij dwukrotnie i wyślij w dół. Widzę, że są kłopoty. Lepiej pójdę do ostatniej celi położonej po przekątnej. Hit F2. Teraz domyślnym zachowaniem w przypadku nomenklatury formuł tabel jest kopiowanie formuł na bok, a rzeczywiste kolumny przesuwają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.Lepiej pójdę do ostatniej celi położonej po przekątnej. Hit F2. Teraz domyślne zachowanie w przypadku nomenklatury formuł tabel polega na skopiowaniu formuł na bok, a rzeczywiste kolumny przesuwają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.Lepiej pójdę do ostatniej celi położonej po przekątnej. Hit F2. Teraz domyślne zachowanie w przypadku nomenklatury formuł tabel polega na skopiowaniu formuł na bok, a rzeczywiste kolumny przesuwają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.rzeczywiste kolumny poruszają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.rzeczywiste kolumny poruszają się tak, jakby były mieszanymi odwołaniami do komórek. Moglibyśmy je teraz zablokować, ale tym razem tego nie zrobię. Teraz zauważ, że kiedy kopiujesz go w dół, działa dobrze, ale kiedy kopiujesz na bok, wtedy przesuwają się rzeczywiste kolumny. Więc patrz na to, zamierzam użyć Ctrl + C i Ctrl + V, a to pozwoli uniknąć przesunięcia F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.m przechodząc do Ctrl + C i Ctrl + V, a następnie zapobiega przesuwaniu się F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.m przechodząc do Ctrl + C i Ctrl + V, a następnie zapobiega przesuwaniu się F do kolumn po skopiowaniu go na bok. Kliknij dwukrotnie i wyślij. Teraz nasza formuła% zmiany = kwota końcowa / kwota początkowa -1, Ctrl + Enter, kliknij dwukrotnie i wyślij.
Teraz, zanim przejdziemy do testowania, dodaj nowe rekordy. Właściwie chcę stworzyć tę etykietę tutaj, aby była dynamiczna. Sposób, w jaki to zrobię, to powiedzieć = znak i zrobimy formułę tekstową, więc za każdym razem, gdy chcemy tekst i formułę, musisz to wpisać: „i jestem przechodzę do typu Sprzedaż między, spacja ”i teraz muszę wyodrębnić z tej pojedynczej daty tam, od pierwszego do końca miesiąca. Zamierzam użyć funkcji TEKST. Funkcja TEKST może przyjmować daty liczbowe lub numery seryjne, przecinek i używać niestandardowego formatowania liczb w ”. Zawsze chcę widzieć trzyliterowy skrót miesiąca, mmm, zawsze chcę go jako pierwszy. Jeśli wstawię tutaj 1, przecinek yyy, to nie zadziała. Chce zobaczyć, że to daje nam wartość lub dlatego, że tego nie lubi 1. Ale my ”ponownie wolno wstawiać pojedynczy znak, jeśli używamy ukośnika, to jest w niestandardowym formatowaniu liczb. Mm i rr będą rozumiane przez formatowanie liczb niestandardowych jako miesiąc i rok, a teraz format niestandardowej liczby będzie rozumiał wstawianie liczby 1. F2 i teraz po prostu: & „-” i TEKST tego przecinka, a teraz Użyję tylko prostego formatowania liczb: „mmm spacjaD, 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.
W porządku, cóż, hej, chcę podziękować wszystkim za zatrzymanie się. Do zobaczenia następnym razem w kolejnym podcastie Dueling Excel od i Excel Is Fun.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel181.xlsm
