Pobierz przykład danych tabeli przestawnej programu Excel

zostaw komentarz 6,950

Wcześniej mówiłem już o tym, jak odnosić się do komórki Stół obrotowy zamiast zwykłego łącza zwracana jest funkcja GET.PIVORTABLE.DATA (patrz). Jeśli jesteś zainteresowany Jak Aby przezwyciężyć tę niedogodność, polecam zapoznać się ze wspomnianą notatką. Jeśli jesteś zainteresowany, Dlaczego to się dzieje, a także co pozytywne aspekty funkcja ma GET.DATA.PIVOT.TABLE, to proponuję fragment książki Jelena, Alexandra. (Rozdział 15). Rozważana technika pozwoli nam uporać się z wieloma problemami, które powodują bóle głowy użytkowników tabel przestawnych, w szczególności:

Po odświeżeniu tabeli przestawnej poprzednio zastosowane formatowanie znika. Formaty liczb zostały utracone. Wyniki dostosowywania szerokości kolumn znikają.
Nie istnieje prosta droga tworzenie asymetrycznej tabeli przestawnej. Jedyną opcją jest użycie nazwanych zestawów, ale ta metoda jest dostępna tylko dla tych, którzy korzystają z tabel przestawnych modelu danych, a nie ze zwykłych tabel przestawnych.
Excel nie zapamiętuje szablonów. Jeśli konieczne jest wielokrotne tworzenie tabel przestawnych, konieczne będzie ponowne przeprowadzenie grupowania, zastosowanie pól obliczeniowych i elementów oraz wykonanie wielu innych podobnych zadań.

Tak naprawdę wszystko, co tu opisano, nie jest nowością. Co więcej, podobne techniki stosowane są od Excela 2002. Jednak z mojej komunikacji z użytkownikami wynika, że zna je mniej niż 1% użytkowników. Jedyne pytanie, jakie mają użytkownicy, to jak wyłączyć dziwną funkcję GET.PIRTTABLE.DATA. Szkoda…

Pobierz notatkę w formacie lub, przykłady w formacie

Cóż, zacznijmy po kolei.

Jak porzucić problematyczną funkcję GET.PIVOT.TABLE.DATA

Funkcja GET.PIVO.TABLE.DATA od dawna sprawiała wielu użytkownikom ból głowy. Całkiem nagle, bez żadnego ostrzeżenia, zachowanie tabel przestawnych zmieniło się w programie Excel 2002. Gdy zaczniesz tworzyć formuły poza tabelą przestawną, które odwołują się do jej danych, ta funkcja pojawia się znikąd.

Załóżmy, że w tabeli przestawnej pokazanej na ryc. 1, należy porównać dane za lata 2015 i 2014.

Ryż. 1. Oryginalna tabela przestawna

Dodaj nagłówek „% wzrostu” do komórki D3.
Skopiuj format z komórki C3 do komórki D3.
W komórce D4 wpisz znak równości.
Kliknij komórkę C4.
Wpisz / (ukośnik), aby przedstawić operację dzielenia.
Kliknij komórkę B4.
Wpisz -1 i naciśnij kombinację klawiszy pozostać w tej samej celi. Sformatuj wynik jako procent. Jak widać, w regionie zachodnim przychody spadły o 43,8% (rysunek 2). Niezbyt dobre wyniki.
Po zakończeniu wprowadzania pierwszej formuły wybierz komórkę D4.
Kliknij dwukrotnie mały kwadrat znajdujący się w prawym dolnym rogu komórki. To pole reprezentuje uchwyt wypełniania, który umożliwia skopiowanie formuły w celu wypełnienia całej kolumny raportu.

Po zakończeniu kopiowania formuły, patrząc na ekran, zorientujesz się, że coś jest nie tak – każdy region wykazał spadek o 43,8% w ciągu roku (ryc. 3).

Ryż. 3. Po zakończeniu kopiowania formuły do wszystkich komórek w kolumnie zobaczysz, że każdy region wykazał spadek o 43,8%

Jest mało prawdopodobne, aby coś takiego miało miejsce w prawdziwe życie. Każdy użytkownik powie Ci, że po wykonaniu powyższych kroków Excel utworzy formułę =C4/B4–1. Wróć do komórki D4 i zwróć uwagę na pasek formuły (rysunek 4). Po prostu jakiś rodzaj diabelstwa! Prosta formuła =C4/B4–1 już nie istnieje. Zamiast tego program zastępuje złożoną konstrukcję funkcją GET.PIVORTABLE.DATA. Dlaczego ta formuła daje poprawne wyniki w komórce D4, ale po skopiowaniu do komórek poniżej nie działa?

Pierwszą reakcją każdego użytkownika na to, co się stało, będzie następująca: „Co to za dziwna konstrukcja GET.PIVOT.TABLE.DATA, która zrujnowała mój raport?” Większość użytkowników będzie chciała od razu pozbyć się tej funkcji. Niektórzy zadają pytanie: „Dlaczego Microsoft udostępnił nam tę funkcję?”

Nic takiego nie było w Czasy Excela 2000. Kiedy zacząłem regularnie spotykać się z funkcją GET.PIVOT.TABLE.DATA, po prostu ją znienawidziłem. Kiedy na jednym z seminariów ktoś zapytał mnie, jak można to wykorzystać dla dobra sprawy, byłem oszołomiony. Nigdy nie zadawałam sobie takiego pytania! Moim zdaniem, jak i większości użytkowników Excela, funkcja GET.DATA.PICTTABLE była wytworem zła i nie miała nic wspólnego z siłami dobra. Na szczęście istnieją dwa sposoby wyłączenia tej funkcji.

Zablokowanie funkcji GET.PIVOT.TABLE.DATA poprzez wprowadzenie formuły. Istnieje prosty sposób zapobiegania pojawianiu się funkcji GET.PIVOTABLE.DATA. Aby to zrobić, musisz utworzyć formułę bez użycia myszy i klawiszy kursora. Po prostu wykonaj następujące kroki.

Przejdź do komórki D4 i wprowadź = (znak równości).
Wpisz C4.
Wprowadź znak / (ukośnik wskazujący operację dzielenia).
Wpisz B4.
Wpisz -1.
Kliknij Wchodzić.

Teraz utworzyłeś plik regularny Formuła Excela, które można skopiować do komórek pod kolumną i dzięki którym można uzyskać prawidłowe wyniki (rys. 5). Jak widać, w obszarach poza tabelą przestawną można tworzyć formuły, które odwołują się do danych znajdujących się w tabeli przestawnej. A ci, którzy nie wierzą, że jest to możliwe, niech sami wykonają opisane kroki.

Ryż. 5. Wystarczy wpisać z klawiatury =С4/В4–1, a formuła będzie działać tak, jak powinna

Część użytkowników odczuje dyskomfort w związku z zakłóceniem zwykłej kolejności wprowadzania formuł. Ponadto proponowana opcja jest bardziej pracochłonna. Jeśli jesteś jednym z tych użytkowników, druga metoda jest dla Ciebie...

Wyłączenie funkcji GET.PIVOT.TABLE.DATA. Możesz trwale wyłączyć funkcję GET.PIVOTABLE.DATA. Kliknij wstążkę menu Plik → Opcje. W oknie, które się otworzy OpcjePrzewyższać przejdź do depozytu Formuły i odznacz pole obok opcji Użyj funkcjiGetPivotData dla łączy w tabeli przestawnej. Kliknij OK.

Alternatywna opcja. Kliknij tabelę przestawną i wyświetloną kartę kontekstową Analiza kliknij listę rozwijaną obok przycisku Opcje. Odznacz pole obok elementu Utwórz GetPivotData(ryc. 7). Domyślnie pole wyboru jest włączone.

Dlaczego Microsoft zaoferował nam funkcję GET.PICTTABLE.DATA. Jeśli ta funkcja jest tak okropna, dlaczego Microsoft domyślnie ją włączył? Dlaczego zależy im na utrzymaniu obsługi tej funkcji w nowych wersjach Excela? Czy są świadomi nastrojów użytkowników? I przechodzimy do najciekawszego...

Korzystanie z funkcji GET.PIVORTABLE.DATA w celu ulepszenia tabel przestawnych

Tabele przestawne to wspaniały wynalazek ludzkości. Tabelę przestawną można utworzyć kilkoma kliknięciami, eliminując potrzebę stosowania zaawansowanych filtrów, BDSUMM i tabel danych. Tabele przestawne umożliwiają tworzenie jednostronicowych raportów na podstawie ogromnych ilości danych. Zalety te przyćmiewają niektóre wady tabel przestawnych, takie jak słabe formatowanie i konieczność konwertowania tabel przestawnych na wartości w celu dodatkowego dostosowania. Na ryc. Rysunek 8 przedstawia typowy proces tworzenia tabeli przestawnej. W tym przypadku wszystko zaczyna się od danych początkowych. Tworzymy tabelę przestawną i wykorzystujemy wszystkie możliwe techniki, aby ją dostosować i ulepszyć. Czasem konwertujemy tabelę przestawną na wartości i dokonujemy ostatecznego formatowania.

Nowa technika tworzenia tabel przestawnych, zaproponowana przez Roba Colleya (programistę Microsoft) i omówiona poniżej, stanowi ulepszenie procesu opisanego powyżej. W tym przypadku najpierw tworzona jest prymitywna tabela przestawna. Ta tabela nie wymaga formatowania. Następnie przechodzi jednoetapowy, stosunkowo pracochłonny proces mający na celu utworzenie ładnie sformatowanej powłoki, w której będzie mieścił się raport końcowy. Następnie funkcja GET.PIVOT.TABLE.DATA służy do szybkiego zapełnienia danymi raportu znajdującego się w powłoce. Po otrzymaniu nowych danych możesz umieścić je na arkuszu, zaktualizować pierwotną tabelę przestawną i wydrukować raport znajdujący się w powłoce (rys. 9). Technika ta ma wiele niezaprzeczalnych zalet. Na przykład nie musisz się martwić o formatowanie raportu natychmiast po jego utworzeniu. Proces tworzenia tabel przestawnych staje się niemal całkowicie zautomatyzowany.

W poniższych sekcjach omówiono sposób tworzenia raportu dynamicznego wyświetlającego wartości rzeczywiste z poprzednich miesięcy i cele na przyszłe miesiące.

Tworzenie prymitywnej tabeli przestawnej. Dane źródłowe (rys. 10) prezentowane są w formie transakcji zawierających informacje o planowanych i rzeczywistych wskaźnikach dla każdego regionu, w którym spółka posiada oddziały. Planowane wskaźniki uszczegóławiane są na poziomie miesiąca, a rzeczywiste – na poziomie poszczególnych dni. Tworzone są wskaźniki planowe na rok następny, natomiast wskaźniki rzeczywiste – na miesiące ubiegłe. Ponieważ raport będzie aktualizowany co miesiąc, proces ten jest znacznie uproszczony, jeśli źródło danych tabeli przestawnej powiększa się w miarę dodawania nowych danych na dole. W starszych wersjach Excela tworzenie takiego źródła danych odbywało się z wykorzystaniem nazwanego zakresu dynamicznego z wykorzystaniem funkcji PRZESUNIĘCIE (więcej informacji znajdziesz w artykule). Podczas pracy w programie Excel 2013 wystarczy wybrać jedną z komórek danych i nacisnąć kombinację klawiszy Ctrl+T (utwórz tabelę). Spowoduje to utworzenie nazwanego zestawu danych, który będzie automatycznie rozszerzany w miarę dodawania nowych wierszy i kolumn.

Stwórzmy teraz tabelę przestawną. Funkcja GET.PIVORTABLE.DATA jest dość potężna, ale może zwrócić tylko wartości, które pojawiają się w rzeczywistej tabeli przestawnej. Ta funkcja nie może przeglądać pamięci podręcznej w celu obliczenia elementów, których nie ma w tabeli przestawnej.

Utwórz tabelę przestawną:

Wybierz zespół Wstawić → Stół obrotowy a następnie w oknie dialogowym Tworzenie tabeli przestawnej Kliknij OK.
Na liście pól tabeli przestawnej wybierz odpowiednie pole data. Po lewej stronie tabeli przestawnej pojawi się lista dat (rys. 11).
Wybierz dowolną komórkę daty, na przykład A4. Na karcie kontekstowej Analiza znajduje się w zestawie zakładek kontekstowych Praca z tabelami przestawnymi, kliknij przycisk Grupuj według pól(patrz szczegóły). W oknie dialogowym Grupowanie Wybierz opcję Miesiące(ryc. 12). Kliknij OK. Nazwy miesięcy pojawią się po lewej stronie tabeli przestawnej (rys. 13).
Przeciągnij pole data do obszaru kolumn tabeli przestawnej.
Przeciągnij pole Indeks do obszaru kolumn listy pól tabeli przestawnej.
Wybierz pole Region, który pojawi się w lewej kolumnie tabeli przestawnej.
Wybierz pole Dochód który pojawia się w obszarze wartości tabeli przestawnej.

Ryż. 11. Zacznij od grupowania według pól data

Na tym etapie nasza tabela przestawna wygląda dość prymitywnie (rys. 14). Bardzo nie podobają mi się napisy Nazwy linii I Nazwy kolumn. Wyświetlanie sum dla jest niepraktyczne Plan Jana I Fakt Jana w kolumnie D itp. Ale nie martw się wygląd tę tabelę podsumowującą, ponieważ nikt inny oprócz Ciebie nie będzie jej widział. Od tego momentu będziemy tworzyć powłokę raportu, dla której źródłem danych będzie nowo utworzona tabela przestawna.

Tworzenie powłoki raportu. Włóż pusty arkusz do skoroszytu. Odłóżmy na chwilę narzędzia do pracy z tabelami przestawnymi i przejdźmy do zwykłych narzędzi Excela. Naszym zadaniem jest użycie formuł i formatowania, aby stworzyć piękny raport, którego pokazanie menadżerowi nie będzie krępujące.

Wykonaj poniższe kroki (ryc. 15).

W komórce A1 wpisz nazwę raportu - Planowane i rzeczywiste wskaźniki według regionów.
Przejdź do zakładki dom, kliknij przycisk Style komórek wybierz format Nagłówek 1.
W komórce A2 wprowadź formułę =MIESIĄC(DZISIAJ();0). Ta funkcja zwraca ostatni dzień bieżącego miesiąca. Na przykład, jeśli czytasz to 14 sierpnia 2014 r., w komórce A2 zostanie wyświetlona data 31 sierpnia 2014 r.
Wybierz komórkę A2. Naciśnij kombinację klawiszy Ctrl+1, aby wyświetlić okno dialogowe Format komórki. Na karcie Numer kliknij element Wszystkie formaty. Wprowadź niestandardowy format liczb jako „Od miesiąca „MMMM” planowane wskaźniki”(ryc. 16). W rezultacie obliczona data pojawi się jako tekst.
W komórce A5 wprowadź tytuł Region.
Wprowadź tytuły regionów w pozostałych komórkach kolumny A. Tytuły regionów muszą odpowiadać nazwom regionów wymienionym w tabeli przestawnej.
Jeśli to konieczne, dodaj etykiety do kolumny zawierającej sumy działów.
Na dole raportu dodaj wiersz Razem dla firmy.
W komórce B4 wprowadź formułę =DATA(ROK(2$A$),KOLUMNA(A1),1). Ta formuła zwraca daty 01.01.2014, 01.02.2104 itd., czyli pierwsze dni wszystkich 12 miesięcy bieżącego roku.
Wybierz komórkę B4. Naciśnij kombinację klawiszy Ctrl+1, aby otworzyć okno Format komórki. Na karcie Numer W rozdziale Wszystkie formaty wprowadź niestandardowy format liczb MMM. W tym formacie wyświetlana jest trzyliterowa nazwa miesiąca. Wyrównaj tekst do prawej krawędzi komórki.
Skopiuj zawartość komórki B4 do zakresu C4:M4. Na górze tabeli przestawnej pojawi się wiersz z nazwami miesięcy.
W komórce B5 wpisz formułę =JEŻELI(MIESIĄC(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt oraz na teraźniejszość i przyszłość - Plan.
Dodaj nagłówek do komórki N5 Konkluzja. Do komórki O4 - Konkluzja, O5 - Plan, P5 - % odchylenia.
Wprowadź zwykłe formuły programu Excel używane do obliczania sum działowych, wierszy sumy firmy, kolumn sumy całkowitej i kolumn wariancji %:
1. w komórce B8 wpisz formułę =SUMA(B6:B7) i skopiuj ją do innych komórek wiersza;
2. w komórce N6 wpisz formułę =SUMA(B6:M6) i skopiuj ją do pozostałych komórek w kolumnie;
3. w komórce P6 wprowadź formułę =JEŻELI((N6/O10)-1;0) i skopiuj ją do innych komórek w kolumnie;
4. w komórce B13 wprowadź formułę =SUMA(B10:B12) i skopiuj ją do innych komórek wiersza;
5. w komórce B17 wprowadź formułę =SUMA(B15:B16) i skopiuj ją do innych komórek wiersza;
6. W komórce B19 wprowadź formułę =SUMA(B6:B18)/2 i skopiuj ją do innych komórek w wierszu.
Zastosuj styl Nagłówek 4 do podpisów w kolumnie A oraz nagłówków w wierszach 4 i 5.
Dla zakresu komórek B6:O19 wybierz format liczb # ##0.
W przypadku komórek kolumny P wybierz format liczbowy 0,0%.

Zakończyliśmy więc tworzenie powłoki raportu pokazanej na ryc. 15. Niniejszy raport zawiera całe wymagane formatowanie. W następnej sekcji pokazano, jak używać funkcji GET.PIVORTABLE.DATA do ukończenia raportu.

Ryż. 15. Zgłoś powłokę przed dodaniem formuł GET.PIVOT.TABLE.DATA

Korzystanie z funkcji GET.PIVORTABLE.DATA w celu wypełnienia powłoki raportu danymi. Od tej chwili będziesz mógł doświadczyć wszystkich korzyści płynących ze stosowania funkcji GET.PIVOT.TABLE.DATA. Jeśli odznaczyłeś pole wyboru włączające tę funkcję, wróć do odpowiedniego ustawienia i przywróć zaznaczenie pola wyboru (patrz opis na rysunku 6 lub 7).

Wybierz komórkę B6 powłoki raportu. Komórka ta odpowiada regionowi północno-wschodniemu i rzeczywistym danym za styczeń.

Wprowadź = (znak równości), aby rozpocząć wprowadzanie formuły.
Przejdź do arkusza z tabeli przestawnej i kliknij komórkę odpowiadającą regionowi północno-wschodniemu oraz rzeczywiste wskaźniki za styczeń - C12 (ryc. 17).
Naciśnij klawisz Wchodzić aby zakończyć wprowadzanie formuły i powrócić do powłoki raportu. W rezultacie Excel doda funkcję GET.PIOTTABLE.DATA do komórki B6. Komórka wyświetli wartość 277 435 USD.

Zapamiętaj tę liczbę, ponieważ będzie Ci ona potrzebna przy porównywaniu jej z wynikami uruchomienia formuły, którą będziesz później edytować. Formuła wygenerowana przez program wygląda następująco: =GET.DATA.PIVOTABLE(" Dochód " ;'Rys. 11-14′!$A$3; " Region " ; " Północny wschód " ; " Data " ;1; "Wskaźnik" ; „Fakt”). Jeśli do tej pory ignorowałeś funkcję GET.PIRTTABLE.DATA, czas przyjrzeć się jej bliżej. Na ryc. 18 formuła ta jest pokazywana w trybie edycji wraz z podpowiedzią.

Argumenty funkcji:

Pole_danych. Pole z obszaru wartości tabeli przestawnej. Uwaga: w tym przypadku pole jest używane Dochód, ale nie Kwota według pola Dochód.
Stół obrotowy. W przypadku tej opcji Microsoft pyta: „Której tabeli przestawnej chcesz użyć?” Wystarczy podać jedną z komórek tabeli przestawnej. Wpis „Rys. 11-14’!$A$3 odnosi się do pierwszej komórki tabeli przestawnej, do której wprowadzane są dane. Ponieważ w naszym przypadku możemy podać dowolną komórkę związaną z tabelą przestawną, argument pozostawiamy bez zmian. Adres komórki $A$3 jest odpowiedni pod każdym względem.
Pole 1; element 1. W formule generowanej automatycznie jako nazwa pola wybierana jest nazwa Region, a jako wartość pola - Północny wschód. Tutaj właśnie pojawiają się problemy, które pojawiają się podczas pracy z funkcją GET.PIVOT.TABLE.DATA. Nie można skopiować automatycznie wybranych wartości, ponieważ są one zakodowane na stałe. Dlatego też, jeśli formuły zostaną skopiowane na cały obszar raportu, konieczne będzie ich ręczne zmodyfikowanie. Zamiast wartości Northeast zastąp odwołanie do komórki w postaci $A6. Określając znak dolara przed nazwą kolumny A, określasz, że część wiersza odwołania może zostać zmieniona po skopiowaniu formuły do komórek kolumny.
Pole 2; element 2. Ta para argumentów określa pole data o wartości 1. Jeśli pierwotna tabela przestawna była pogrupowana według miesięcy, pole miesiąca zachowuje pierwotną nazwę pola data. Wartość liczbowa miesiąca to 1, co odpowiada styczniu. Niewskazane jest używanie takiej wartości przy tworzeniu ogromnych formuł określonych w dziesiątkach, a nawet setkach komórek raportu. Lepiej jest użyć wzoru, który oblicza wartości pól data, podobnie jak formuła w komórce B4. Zamiast 1 w tym przypadku możesz użyć formuły MIESIĄC(B$4). Znak dolara przed cyfrą 4 wskazuje, że formuła może przypisywać wartości do pola data na podstawie innych miesięcy podczas kopiowania formuły do komórek wiersza.
Pole 3; element 3. W tym przypadku nazwa pola jest nadawana automatycznie Indeks i wartość pola Fakt. Wartości te są prawidłowe dla stycznia, natomiast dla kolejnych miesięcy wartość pola będzie musiała zostać zmieniona na Plan. Zmień zakodowaną na stałe wartość pola Fakt za link 5 B$.
Pole 4; element 4. Argumenty te nie są używane, ponieważ pola się skończyły.

Nowa formuła pokazana jest na rys. 19. W ciągu minuty zamiast zakodowanej na stałe formuły przeznaczonej do pracy z pojedynczą wartością stworzono elastyczną formułę, którą można skopiować do wszystkich komórek zbioru danych. Naciśnij klawisz Wchodzić, a otrzymasz taki sam wynik jak przed edycją formuły. Edytowana formuła przyjmuje następującą postać: =GET.DATA.PIVORTABLE("Przychody" ;'Rys. 11-14′!$A$3; "Region" ;$A6; "Data" ;MIESIĄC(B$4); "Wskaźnik " ;B$5)

Ryż. 19. Po zakończeniu edycji formuła GET.PIOTTABLE.DATA nadaje się do skopiowania do wszystkich komórek zakresu

Skopiuj formułę do wszystkich pustych komórek w kolumnach B:M, w których obliczane są wyniki. Teraz, gdy raport zawiera rzeczywiste wartości liczbowe, możesz dokonać ostatecznej korekty szerokości kolumn.

W kolejnym kroku skonfigurujemy formułę GET.DATA.PICTTABLE w celu obliczenia ostatecznych planowanych wskaźników. Jeśli po prostu skopiujesz formułę do komórki O6, pojawi się komunikat o błędzie #LINK! Powodem pojawienia się tego błędu jest słowo Konkluzja w komórce O4 nie ma nazwy miesiąca. Aby funkcja GET.PIVORTABLE.DATA działała poprawnie, wymagana wartość musi znajdować się w tabeli przestawnej. Ale ponieważ w oryginalnej tabeli przestawnej pole Indeks to drugie pole w obszarze kolumn, kolumna danych Wynik planu faktycznie nieobecny. Przesuń pole Indeks tak, aby stał się pierwszym w obszarze kolumny (ryc. 20).

Ryż. 20. Dostosuj układ pól w obszarze kolumn tak, aby pojawiła się kolumna Zaplanuj wynik

Porównaj z rys. 14. Tam, w obszarze KOLUMNY, znajdowało się pierwsze pole data, co spowodowało, że początkowo kolumny pogrupowano według daty, a w obrębie każdego miesiąca według planu/rzeczywistości. Teraz pole Wskaźnik znajduje się na pierwszym miejscu, a w podsumowaniu na pierwszym miejscu znajdują się kolumny Plan, wewnątrz posortowane według miesięcy, a następnie wszystkie kolumny idą Fakt.

Wracając do arkusza powłoki raportu, przejdź do komórki O6, wpisz = (znak równości) i odwołaj się do komórki N12 w arkuszu tabeli przestawnej odpowiadającej planowanym wynikom dla regionu północno-wschodniego. Kliknij Wchodzić. Otrzymana formuła =GET.DATA.PIVORTABLE("Dochód";'Rys. 11-14′!$A$3;"Region";"Północny wschód";"Wskaźnik";"Plan"). Edytuj to: =GET. PODSUMOWANIE.TABELA DANE(„Przychody”;’Rys. 11-14′!$A$3;”Region”;$A6;”Wskaźnik”;O$5). Skopiuj tę formułę do innych komórek w kolumnie O (ryc. 21). Należy pamiętać, że nawet podczas przenoszenia różnych obszarów raportu w formie tabeli przestawnej powłoka działa poprawnie. Oczywiście, jeśli dezaktywujesz niektóre pola podsumowań, powłoka nie będzie w stanie tego obsłużyć...

Ryż. 21. Raport końcowy, który można przedstawić menadżerowi

Masz teraz dobrze sformatowane opakowanie raportu, które wykorzystuje wartości z dynamicznej tabeli przestawnej. Choć początkowe utworzenie raportu zajęło sporo czasu, jego aktualizacja to kwestia zaledwie kilku minut.

Zaktualizuj raport. Aby zaktualizować raport o dane za kolejne miesiące należy wykonać poniższe kroki:

Wstaw rzeczywiste dane poniżej oryginalnego zbioru danych. Ponieważ dane źródłowe mają format tabeli, formatowanie tabeli jest automatycznie propagowane do nowych wierszy danych. Rozszerzona została także definicja oryginalnej tabeli przestawnej (w pliku Excel dodałem już rzeczywiste wskaźniki za cały rok).
Przejdź do tabeli przestawnej. Kliknij prawym przyciskiem myszy i wybierz Aktualizacja. Wygląd tabeli przestawnej ulegnie zmianie, ale nie ma w tym nic złego.
Przejdź do powłoki raportów. W zasadzie wszystko zostało już zrobione, aby zaktualizować raport, ale nie zaszkodzi przetestować wyniki. Zmień formułę w komórce A2 na przykład na następującą: =MIESIĄC(DZISIAJ() +31 ;0) i zobacz co się stanie.

Dodając co miesiąc nowe rzeczywiste dane sprzedażowe, nie musisz się martwić o ponowne tworzenie formatów, formuł itp. Opisany proces aktualizacji raportów jest tak prosty, że na zawsze zapomnisz o problemach, które pojawiły się podczas przygotowywania raportów miesięcznych. Jedyny problem może pojawić się w przypadku reorganizacji firmy, w wyniku której w tabeli przestawnej mogą pojawić się nowe regiony. Aby mieć pewność, że formuły działają poprawnie, upewnij się, że sumy w raporcie są zgodne z sumami w tabeli przestawnej. Gdy pojawi się nowy region, po prostu dodaj go do arkusza z powłoką i „przeciągnij” odpowiednie formuły.

Nie sądziłem, że kiedykolwiek to powiem: „Funkcja GET.DATA.PICTTABLE to największe błogosławieństwo. Jak istnieliśmy wcześniej bez niej?

W oryginale Jelena dane źródłowe zostały tak ułożone, że dalsze formuły działały poprawnie dopiero w lipcu 2015 r. W załączonym do tej notatki pliku Excel zmodyfikowałem dane źródłowe, a także niektóre formuły tak, aby wszystko działało niezależnie od datę, kiedy będziesz eksperymentować z załączonym plikiem Excel. Niestety formuły musiały być skomplikowane.

W przypadku tabel przestawnych dostępna jest funkcja GET.PIVORTABLE.DATA, która zwraca dane przechowywane w raporcie tabeli przestawnej.

Aby szybko uzyskać dostęp do funkcji, należy wprowadzić znak równości w komórce (=) i zaznaczyć żądaną komórkę w tabeli przestawnej. Excel automatycznie wygeneruje funkcję GET.PIRTTABLE.DATA.

Wyłączanie tworzenia GetPivotData

Aby wyłączyć automatyczne generowanie funkcji GET.PIOTTABLE.DATA należy zaznaczyć dowolną komórkę w tabeli przestawnej, przejść do zakładki Praca z tabelami przestawnymi -> Opcje do grupy Stół obrotowy. Kliknij strzałkę w dół obok karty Opcje. W menu rozwijanym usuń zaznaczenie elementu TworzyćPobierzPivotData.

Używanie odwołań do komórek w funkcji GET.PIVOTABLE.DATA

Zamiast podawać nazwy elementów lub pól w funkcji GET.PIOTTABLE.DATA, można odwołać się do komórek znajdujących się na arkuszu. W poniższym przykładzie komórka E3 zawiera nazwę produktu, a formuła w komórce E4 do niego się odwołuje. W rezultacie zostanie zwrócona całkowita ilość ciast.

Używanie odniesień do pola tabeli przestawnej

Nie ma żadnych pytań dotyczących działania łączy do elementów tabeli przestawnej; problemy pojawiają się, gdy chcemy odwołać się do pola danych.

W przykładzie komórka E3 zawiera nazwę pola danych „Ilość” i dobrze byłoby odwołać się do tej komórki w funkcji, zamiast podawać nazwę pola w formule GET.PIVOT.TABLE.DATA.

Jeśli jednak zmienimy pierwszy argument pole_danych do odwołania do komórki E3, Excel zwróci błąd #REF!

POBIERZ.DANE OBROTOWE(E3,$A$3)

Po prostu dodanie pustego ciągu („”) na początku lub na końcu odwołania do komórki rozwiąże problem.

POBIERZ.DANE OBROTOWE(E3&"";$A$3)

Samo dostosowanie formuły zwróci prawidłową wartość.

Używanie dat w funkcji GET.PIVOTABLE.DATA

Jeśli używasz dat w funkcji GET.PIVORTABLE.DATA, mogą wystąpić problemy, nawet jeśli data pojawia się w tabeli przestawnej. Przykładowo argumentem poniższej formuły jest data „21.04.2013”, a tabela przestawna zawiera pole z datami sprzedaży. Jednak formuła w komórce E4 zwraca błąd.

GET.TABELA OBROTOWA.DATA("Wolumin";3$A$;"Data";"21.04.2013″)

Aby zapobiec błędom w datach, możesz skorzystać z jednej z następujących metod:

Porównaj formaty dat w formule i tabeli przestawnej
Użyj funkcji DATAWARTOŚĆ
Użyj funkcji DATA
Odwołaj się do komórki z poprawną datą

Porównaj formaty dat w formule i tabeli przestawnej.

Aby uzyskać poprawny wynik, korzystając z funkcji GET.PIVORTABLE.DATA, należy upewnić się, że formaty daty w argumencie formuły i tabeli przestawnej są takie same.

W komórce E4 formuła użyła formatu daty „DD.MM.RRRR” i zwróciła prawidłowe informacje.

Korzystanie z funkcji DATAWARTOŚĆ

Zamiast ręcznie wpisywać datę do formuły, możesz dodać funkcję DATEVALUE, aby zwrócić datę.

W komórce E4 datę wprowadza się za pomocą funkcji DATAWARTOŚĆ, a Excel zwraca wymagane informacje.

GET.TABELA PRZESTAWNA.DATA("Wolumin";$A$3;"Data";DATAWARTOŚĆ("21.04.2013"))

Korzystanie z funkcji DATA

Zamiast ręcznie wpisywać datę w formule, można skorzystać z funkcji DATA, która umożliwi prawidłowe zwrócenie niezbędnych informacji.

GET.TABELA OBROTOWA.DATA("Wolumin";$A$3;"Data";DATA(2013,4,21))

Odniesienie do komórki daty

Zamiast ręcznie wprowadzać datę do formuły, możesz odwołać się do komórki zawierającej datę (w dowolnym formacie, w którym Excel akceptuje dane jako daty). W przykładzie w komórce E4 formuła odwołuje się do komórki E3, a Excel zwraca prawidłowe dane.

GET.TABELA OBROTOWA.DANE("Wolumin";$A$3;"Data";E3)

Wcześniej mówiłem już o tym, jak podczas odwoływania się do komórki tabeli przestawnej zamiast zwykłego łącza zwracana jest funkcja GET.PIVORTABLE.DATA (patrz). Jeśli jesteś zainteresowany Jak Aby przezwyciężyć tę niedogodność, polecam zapoznać się ze wspomnianą notatką. Jeśli jesteś zainteresowany, Dlaczego tak się dzieje, a także jakie pozytywne strony ma funkcja GET.DATA.PICTTABLE, to proponuję fragment książki Jelena, Alexandra. (Rozdział 15). Rozważana technika pozwoli nam uporać się z wieloma problemami, które powodują bóle głowy użytkowników tabel przestawnych, w szczególności:

Po odświeżeniu tabeli przestawnej poprzednio zastosowane formatowanie znika. Formaty liczb zostały utracone. Wyniki dostosowywania szerokości kolumn znikają.
Nie ma łatwego sposobu na utworzenie asymetrycznej tabeli przestawnej. Jedyną opcją jest użycie nazwanych zestawów, ale ta metoda jest dostępna tylko dla tych, którzy korzystają z tabel przestawnych modelu danych, a nie ze zwykłych tabel przestawnych.
Excel nie zapamiętuje szablonów. Jeśli konieczne jest wielokrotne tworzenie tabel przestawnych, konieczne będzie ponowne przeprowadzenie grupowania, zastosowanie pól obliczeniowych i elementów oraz wykonanie wielu innych podobnych zadań.

Pobierz notatkę w formacie lub, przykłady w formacie

Cóż, zacznijmy po kolei.

Jak porzucić problematyczną funkcję GET.PIVOT.TABLE.DATA

Załóżmy, że w tabeli przestawnej pokazanej na ryc. 1, należy porównać dane za lata 2015 i 2014.

Ryż. 1. Oryginalna tabela przestawna

Dodaj nagłówek „% wzrostu” do komórki D3.
Skopiuj format z komórki C3 do komórki D3.
W komórce D4 wpisz znak równości.
Kliknij komórkę C4.
Wpisz / (ukośnik), aby przedstawić operację dzielenia.
Kliknij komórkę B4.
Wpisz -1 i naciśnij kombinację klawiszy pozostać w tej samej celi. Sformatuj wynik jako procent. Jak widać, w regionie zachodnim przychody spadły o 43,8% (rysunek 2). Niezbyt dobre wyniki.
Po zakończeniu wprowadzania pierwszej formuły wybierz komórkę D4.
Kliknij dwukrotnie mały kwadrat znajdujący się w prawym dolnym rogu komórki. To pole reprezentuje uchwyt wypełniania, który umożliwia skopiowanie formuły w celu wypełnienia całej kolumny raportu.

Po zakończeniu kopiowania formuły, patrząc na ekran, zorientujesz się, że coś jest nie tak – każdy region wykazał spadek o 43,8% w ciągu roku (ryc. 3).

Ryż. 3. Po zakończeniu kopiowania formuły do wszystkich komórek w kolumnie zobaczysz, że każdy region wykazał spadek o 43,8%

Jest to mało prawdopodobne w prawdziwym życiu. Każdy użytkownik powie Ci, że po wykonaniu powyższych kroków Excel utworzy formułę =C4/B4–1. Wróć do komórki D4 i zwróć uwagę na pasek formuły (rysunek 4). Po prostu jakiś rodzaj diabelstwa! Prosta formuła =C4/B4–1 już nie istnieje. Zamiast tego program zastępuje złożoną konstrukcję funkcją GET.PIVORTABLE.DATA. Dlaczego ta formuła daje poprawne wyniki w komórce D4, ale po skopiowaniu do komórek poniżej nie działa?

W czasach Excela 2000 nie było czegoś takiego. Kiedy zacząłem regularnie spotykać się z funkcją GET.PIVOTABLE.DATA, po prostu jej nienawidziłem. Kiedy na jednym z seminariów ktoś zapytał mnie, jak można to wykorzystać dla dobra sprawy, byłem oszołomiony. Nigdy nie zadawałam sobie takiego pytania! Moim zdaniem, jak i większości użytkowników Excela, funkcja GET.DATA.PICTTABLE była wytworem zła i nie miała nic wspólnego z siłami dobra. Na szczęście istnieją dwa sposoby wyłączenia tej funkcji.

Przejdź do komórki D4 i wprowadź = (znak równości).
Wpisz C4.
Wprowadź znak / (ukośnik wskazujący operację dzielenia).
Wpisz B4.
Wpisz -1.
Kliknij Wchodzić.

Utworzyłeś teraz zwykłą formułę Excela, którą możesz skopiować do komórek pod kolumną i uzyskać prawidłowe wyniki (rysunek 5). Jak widać, w obszarach poza tabelą przestawną można tworzyć formuły, które odwołują się do danych znajdujących się w tabeli przestawnej. A ci, którzy nie wierzą, że jest to możliwe, niech sami wykonają opisane kroki.

Ryż. 5. Wystarczy wpisać z klawiatury =С4/В4–1, a formuła będzie działać tak, jak powinna

Korzystanie z funkcji GET.PIVORTABLE.DATA w celu ulepszenia tabel przestawnych

W poniższych sekcjach omówiono sposób tworzenia raportu dynamicznego wyświetlającego wartości rzeczywiste z poprzednich miesięcy i cele na przyszłe miesiące.

Utwórz tabelę przestawną:

Wybierz zespół Wstawić → Stół obrotowy a następnie w oknie dialogowym Tworzenie tabeli przestawnej Kliknij OK.
Na liście pól tabeli przestawnej wybierz odpowiednie pole data. Po lewej stronie tabeli przestawnej pojawi się lista dat (rys. 11).
Wybierz dowolną komórkę daty, na przykład A4. Na karcie kontekstowej Analiza znajduje się w zestawie zakładek kontekstowych Praca z tabelami przestawnymi, kliknij przycisk Grupuj według pól(patrz szczegóły). W oknie dialogowym Grupowanie Wybierz opcję Miesiące(ryc. 12). Kliknij OK. Nazwy miesięcy pojawią się po lewej stronie tabeli przestawnej (rys. 13).
Przeciągnij pole data do obszaru kolumn tabeli przestawnej.
Przeciągnij pole Indeks do obszaru kolumn listy pól tabeli przestawnej.
Wybierz pole Region, który pojawi się w lewej kolumnie tabeli przestawnej.
Wybierz pole Dochód który pojawia się w obszarze wartości tabeli przestawnej.

Ryż. 11. Zacznij od grupowania według pól data

Na tym etapie nasza tabela przestawna wygląda dość prymitywnie (rys. 14). Bardzo nie podobają mi się napisy Nazwy linii I Nazwy kolumn. Wyświetlanie sum dla jest niepraktyczne Plan Jana I Fakt Jana w kolumnie D itp. Ale nie martw się o wygląd tej tabeli przestawnej, ponieważ nikt inny oprócz Ciebie nie będzie jej widział. Od tego momentu będziemy tworzyć powłokę raportu, dla której źródłem danych będzie nowo utworzona tabela przestawna.

Wykonaj poniższe kroki (ryc. 15).

W komórce A1 wpisz nazwę raportu - Planowane i rzeczywiste wskaźniki według regionów.
Przejdź do zakładki dom, kliknij przycisk Style komórek wybierz format Nagłówek 1.
W komórce A2 wprowadź formułę =MIESIĄC(DZISIAJ();0). Ta funkcja zwraca ostatni dzień bieżącego miesiąca. Na przykład, jeśli czytasz to 14 sierpnia 2014 r., w komórce A2 zostanie wyświetlona data 31 sierpnia 2014 r.
Wybierz komórkę A2. Naciśnij kombinację klawiszy Ctrl+1, aby wyświetlić okno dialogowe Format komórki. Na karcie Numer kliknij element Wszystkie formaty. Wprowadź niestandardowy format liczb jako „Od miesiąca „MMMM” planowane wskaźniki”(ryc. 16). W rezultacie obliczona data pojawi się jako tekst.
W komórce A5 wprowadź tytuł Region.
Wprowadź tytuły regionów w pozostałych komórkach kolumny A. Tytuły regionów muszą odpowiadać nazwom regionów wymienionym w tabeli przestawnej.
Jeśli to konieczne, dodaj etykiety do kolumny zawierającej sumy działów.
Na dole raportu dodaj wiersz Razem dla firmy.
W komórce B4 wprowadź formułę =DATA(ROK(2$A$),KOLUMNA(A1),1). Ta formuła zwraca daty 01.01.2014, 01.02.2104 itd., czyli pierwsze dni wszystkich 12 miesięcy bieżącego roku.
Wybierz komórkę B4. Naciśnij kombinację klawiszy Ctrl+1, aby otworzyć okno Format komórki. Na karcie Numer W rozdziale Wszystkie formaty wprowadź niestandardowy format liczb MMM. W tym formacie wyświetlana jest trzyliterowa nazwa miesiąca. Wyrównaj tekst do prawej krawędzi komórki.
Skopiuj zawartość komórki B4 do zakresu C4:M4. Na górze tabeli przestawnej pojawi się wiersz z nazwami miesięcy.
W komórce B5 wpisz formułę =JEŻELI(MIESIĄC(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt oraz na teraźniejszość i przyszłość - Plan.
Dodaj nagłówek do komórki N5 Konkluzja. Do komórki O4 - Konkluzja, O5 - Plan, P5 - % odchylenia.
Wprowadź zwykłe formuły programu Excel używane do obliczania sum działowych, wierszy sumy firmy, kolumn sumy całkowitej i kolumn wariancji %:
1. w komórce B8 wpisz formułę =SUMA(B6:B7) i skopiuj ją do innych komórek wiersza;
2. w komórce N6 wpisz formułę =SUMA(B6:M6) i skopiuj ją do pozostałych komórek w kolumnie;
3. w komórce P6 wprowadź formułę =JEŻELI((N6/O10)-1;0) i skopiuj ją do innych komórek w kolumnie;
4. w komórce B13 wprowadź formułę =SUMA(B10:B12) i skopiuj ją do innych komórek wiersza;
5. w komórce B17 wprowadź formułę =SUMA(B15:B16) i skopiuj ją do innych komórek wiersza;
6. W komórce B19 wprowadź formułę =SUMA(B6:B18)/2 i skopiuj ją do innych komórek w wierszu.
Zastosuj styl Nagłówek 4 do podpisów w kolumnie A oraz nagłówków w wierszach 4 i 5.
Dla zakresu komórek B6:O19 wybierz format liczb # ##0.
W przypadku komórek kolumny P wybierz format liczbowy 0,0%.

Ryż. 15. Zgłoś powłokę przed dodaniem formuł GET.PIVOT.TABLE.DATA

Wybierz komórkę B6 powłoki raportu. Komórka ta odpowiada regionowi północno-wschodniemu i rzeczywistym danym za styczeń.

Wprowadź = (znak równości), aby rozpocząć wprowadzanie formuły.
Przejdź do arkusza z tabeli przestawnej i kliknij komórkę odpowiadającą regionowi północno-wschodniemu oraz rzeczywiste wskaźniki za styczeń - C12 (ryc. 17).
Naciśnij klawisz Wchodzić aby zakończyć wprowadzanie formuły i powrócić do powłoki raportu. W rezultacie Excel doda funkcję GET.PIOTTABLE.DATA do komórki B6. Komórka wyświetli wartość 277 435 USD.

Argumenty funkcji:

Pole_danych. Pole z obszaru wartości tabeli przestawnej. Uwaga: w tym przypadku pole jest używane Dochód, ale nie Kwota według pola Dochód.
Stół obrotowy. W przypadku tej opcji Microsoft pyta: „Której tabeli przestawnej chcesz użyć?” Wystarczy podać jedną z komórek tabeli przestawnej. Wpis „Rys. 11-14’!$A$3 odnosi się do pierwszej komórki tabeli przestawnej, do której wprowadzane są dane. Ponieważ w naszym przypadku możemy podać dowolną komórkę związaną z tabelą przestawną, argument pozostawiamy bez zmian. Adres komórki $A$3 jest odpowiedni pod każdym względem.
Pole 1; element 1. W formule generowanej automatycznie jako nazwa pola wybierana jest nazwa Region, a jako wartość pola - Północny wschód. Tutaj właśnie pojawiają się problemy, które pojawiają się podczas pracy z funkcją GET.PIVOT.TABLE.DATA. Nie można skopiować automatycznie wybranych wartości, ponieważ są one zakodowane na stałe. Dlatego też, jeśli formuły zostaną skopiowane na cały obszar raportu, konieczne będzie ich ręczne zmodyfikowanie. Zamiast wartości Northeast zastąp odwołanie do komórki w postaci $A6. Określając znak dolara przed nazwą kolumny A, określasz, że część wiersza odwołania może zostać zmieniona po skopiowaniu formuły do komórek kolumny.
Pole 2; element 2. Ta para argumentów określa pole data o wartości 1. Jeśli pierwotna tabela przestawna była pogrupowana według miesięcy, pole miesiąca zachowuje pierwotną nazwę pola data. Wartość liczbowa miesiąca to 1, co odpowiada styczniu. Niewskazane jest używanie takiej wartości przy tworzeniu ogromnych formuł określonych w dziesiątkach, a nawet setkach komórek raportu. Lepiej jest użyć wzoru, który oblicza wartości pól data, podobnie jak formuła w komórce B4. Zamiast 1 w tym przypadku możesz użyć formuły MIESIĄC(B$4). Znak dolara przed cyfrą 4 wskazuje, że formuła może przypisywać wartości do pola data na podstawie innych miesięcy podczas kopiowania formuły do komórek wiersza.
Pole 3; element 3. W tym przypadku nazwa pola jest nadawana automatycznie Indeks i wartość pola Fakt. Wartości te są prawidłowe dla stycznia, natomiast dla kolejnych miesięcy wartość pola będzie musiała zostać zmieniona na Plan. Zmień zakodowaną na stałe wartość pola Fakt za link 5 B$.
Pole 4; element 4. Argumenty te nie są używane, ponieważ pola się skończyły.

Ryż. 19. Po zakończeniu edycji formuła GET.PIOTTABLE.DATA nadaje się do skopiowania do wszystkich komórek zakresu

Ryż. 20. Dostosuj układ pól w obszarze kolumn tak, aby pojawiła się kolumna Zaplanuj wynik

Ryż. 21. Raport końcowy, który można przedstawić menadżerowi

Zaktualizuj raport. Aby zaktualizować raport o dane za kolejne miesiące należy wykonać poniższe kroki:

Wstaw rzeczywiste dane poniżej oryginalnego zbioru danych. Ponieważ dane źródłowe mają format tabeli, formatowanie tabeli jest automatycznie propagowane do nowych wierszy danych. Rozszerzona została także definicja oryginalnej tabeli przestawnej (w pliku Excel dodałem już rzeczywiste wskaźniki za cały rok).
Przejdź do tabeli przestawnej. Kliknij prawym przyciskiem myszy i wybierz Aktualizacja. Wygląd tabeli przestawnej ulegnie zmianie, ale nie ma w tym nic złego.
Przejdź do powłoki raportów. W zasadzie wszystko zostało już zrobione, aby zaktualizować raport, ale nie zaszkodzi przetestować wyniki. Zmień formułę w komórce A2 na przykład na następującą: =MIESIĄC(DZISIAJ() +31 ;0) i zobacz co się stanie.

Nie sądziłem, że kiedykolwiek to powiem: „Funkcja GET.DATA.PICTTABLE to największe błogosławieństwo. Jak istnieliśmy wcześniej bez niej?