Zacznij od danych z wielu lat.
Powiedzmy, że masz szczegółowe zapisy z dwóch lat. Każdy rekord ma dzienną datę. Podczas tworzenia tabeli przestawnej na podstawie tego raportu będziesz mieć setki wierszy z datami dziennymi w tabeli przestawnej. To nie jest duże podsumowanie.
Wybierz jedną z tych komórek dat w tabeli przestawnej. Na karcie Analiza na wstążce wybierz opcję Pole grupy.
Ponieważ znajdujesz się w polu daty, otrzymasz tę wersję okna dialogowego Grupowanie. W nim odznacz Miesiące i wybierz Lata.
Daty dzienne są rolowane do lat. Przenieś pole Data z ROWS do COLUMNS.
Wynik jest prawie doskonały. Ale zamiast sumy całkowitej w kolumnie D, prawdopodobnie potrzebujesz wariancji procentowej.
Aby pozbyć się kolumny Suma całkowita, na karcie Projekt wybierz opcję Sumy całkowite, Włącz tylko dla kolumn. (Zgadzam się, jest to jedno z bardziej niezręcznych sformułowań w Excelu).
Aby zbudować kolumnę wariancji, musisz napisać formułę poza tabelą przestawną, która będzie wskazywała wewnątrz tabeli przestawnej. Nie dotykaj myszy ani klawiszy strzałek podczas tworzenia formuły, ponieważ pojawi się nieprzyjemna funkcja GETPIVOTDATA. Zamiast tego po prostu wpisz =C5/B5-1i naciśnij Enter.
Podziękowania dla Александр Воробьев za zasugerowanie tej wskazówki.
Obejrzyj wideo
- Zacznij od danych z wielu lat
- Wstaw, tabela przestawna
- Przeciągnij pole daty do obszaru wiersza
- Excel 2016: naciśnij Ctrl + Z, aby rozgrupować daty
- Przeciągnij przychody do obszaru wartości
- Wybierz dowolną datę w obszarze wiersza
- Użyj pola grupy
- Wybierz miesiące i lata
- Jak dodać sumy częściowe do pola lat po zgrupowaniu tabeli przestawnej
- Użyj formularza tabelarycznego w tabeli przestawnej, aby nadać każdemu polu wiersza własną kolumnę
- Powtórz wszystkie etykiety wierszy, aby wypełnić puste miejsca w tabeli przestawnej
- Przeciągnij lata do obszaru kolumny
- Kliknij prawym przyciskiem myszy nagłówek kolumny Suma całkowita i usuń
- Jak uniknąć funkcji GetPivotData, gdy formuła wskazuje na tabelę przestawną
- Aby zbudować wariancję, wpisz formułę bez myszy i strzałek
- Podziękowania dla Александр Воробьев za zasugerowanie tej wskazówki
Transkrypcja wideo
Naucz się programu Excel z podcastu MrExcel, odcinek 1998. Tabela przestawna rok po roku.
Będę podcastował całą książkę, idź, kliknij „i” w prawym górnym rogu, aby wyświetlić listę odtwarzania MrExcel Excel.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dziś pierwszy temat w książce o tabelach przestawnych i sposobie tworzenia raportu rocznego. Zaczynamy więc od zbioru danych, który obejmuje dwa lata, widzisz, przechodzimy od 2018 do 2019. Mam zamiar utworzyć tabelę przestawną, wstawianie, tabelę przestawną, OK, w porządku, przejdźmy do nowy arkusz roboczy.
W nowym arkuszu bierzemy pole Data i przeciągamy je do obszaru Wiersze. A potem, widzisz, w programie Excel 2016 automatycznie grupuje elementy. Teraz wiem, że większość ludzi nadal korzysta z programu Excel 2013, może Excel 2010, więc zamierzam nacisnąć Ctrl + Z, aby cofnąć. To doświadczenie, które otrzymasz w 2010 i 2013 r., Weźmiemy pole Przychody i przeciągniemy je do obszaru Wartości. Więc zaczynamy od brzydkiego raportu, będą setki wierszy, ponieważ każdego dnia mamy sprzedaż. Ale mam zamiar przejść do pierwszego pola daty, pierwszego pola daty, przejść do narzędzi tabeli przestawnej, Analiza, w 2013 lub 2016 i 2010 roku nazywało się ono Opcje i wybierz Pole grupy. Upewnij się, że wybrałeś datę przed wybraniem pola grupy i rzuć wszystko do miesięcy i lat. Lub kwartały i lata,lub Miesiące i kwartały i lata, kliknij OK. Otrzymujemy nowe wirtualne pole o nazwie Years w obszarze Rows.
Teraz trochę irytacji. To denerwujące, że nie podają nam sum za rok, łatwo to naprawić, chociaż w moim przypadku nie muszę tego naprawiać. Klikamy Ustawienia pola i Automatyczne, ponieważ nie planuję zostawiać tutaj Years. Oczywiście za każdym razem, gdy tworzę tabelę przestawną, przechodzę do projektu i mówię Pokaż w formie tabelarycznej, aby umieścić wszystko we własnej kolumnie. A następnie powtórz wszystkie etykiety elementów, aby wypełnić puste miejsca po lewej stronie. Znowu nie byłoby to konieczne, ponieważ nie planuję zostawiać Lat tam, gdzie jest, zamierzam wziąć lata i przeciągnąć je na Kolumny, i to jest raport, do którego dążyłem.
W porządku, mamy Miesiące od 2018 do 2019 r. W kolumnie D, czego naprawdę chcę, nie chcę sumy całkowitej, kliknę prawym przyciskiem myszy i powiem Usuń sumę Razem i teraz jesteśmy poza tabelą przestawną. Tak więc zmiana%, bardzo kuszące, aby zbudować tę formułę za pomocą myszy lub klawiszy strzałek, ale nie możesz, ponieważ otrzymasz funkcję… get Pivot Data, której nie da się łatwo skopiować. Więc = C5 / B5-1, sformatujemy to jako wartość procentową i kliknij dwukrotnie, aby skopiować, w prawo. Piękny raport pokazujący, gdzie byliśmy w zeszłym roku, gdzie byliśmy w tym roku, a potem procentową zmianę z roku na rok.
Cóż, ta wskazówka i 40 innych jest w tej książce. Śmiało, kliknij „i” w prawym górnym rogu obok książki. W porządku, więc zaczynasz od zestawu danych z wieloma latami, wykonaj Wstaw, tabelę przestawną, przeciągnij pole daty do obszaru wiersza. W programie Excel 2016 naciśnij Ctrl + Z, aby cofnąć automatyczne aktualizacje grupowania. Dlaczego nie zostawimy tego tutaj? Właściwie to zależy. Jeśli masz dane od, powiedzmy, 2 stycznia do 30 grudnia, nie będą one poprawnie grupować tych danych. Musisz spędzić cały rok, aby zdobyć pole roczne. Może ci się uda, jestem trochę maniakiem kontroli, wolę po prostu mieć kontrolę, dlatego cofam. Jest: przeciągnij przychód do obszaru wartości, wybierz dowolną komórkę, dowolną datę w obszarze wiersza, a następnie pole grupy, wybierz Miesiące i lata,przeciągnij lata po obszarze kolumny, kliknij prawym przyciskiem myszy sumę końcową, aby usunąć, a następnie utwórz wariancję, wpisz formułę, nie używaj myszy ani klawiszy strzałek.
Nie mam pojęcia, jak to wymówić, ale dzięki temu czytelnikowi (Александр Воробьев) za zasugerowanie tej wskazówki i dziękuję za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast1998.xlsx
