GetPivotData w programie Excel - wskazówki dotyczące programu Excel

Właśnie utworzyłeś tabelę przestawną w programie Excel. Klikasz poza tabelą przestawną. Budujesz formułę programu Excel. Kopiujesz tę formułę do wszystkich wierszy w tabeli przestawnej. Obliczenie zgłasza złą odpowiedź dla wszystkich oprócz pierwszego wiersza tabeli przestawnej. Właśnie zostałeś ukąszony przez funkcję "Generuj" GetPivotData. Przyjrzyjmy się, co to jest i jak temu zapobiec.

Po prostu zapobiegnij problemowi GetPivotData - szybka metoda

Najszybszym sposobem zapobiegania generowaniu przez program Excel GetPivotData jest wpisanie formuły bez użycia myszy lub klawiszy strzałek. Jeśli po prostu wpiszesz =E5/D5, program Excel utworzy „normalną” formułę względną, którą można skopiować do wszystkich wierszy sąsiadujących z tabelą przestawną.

Zapobiegaj problemowi GetPivotData - metoda permanentna

Istnieje ukryte ustawienie, które powstrzymuje program Excel przed generowaniem GetPivotData. Jest bardziej ukryty w programie Excel 2003 niż w programie Excel 2007.

W programie Excel 2007 wykonaj następujące kroki:

• Utwórz tabelę przestawną w programie Excel 2007
• Upewnij się, że aktywna komórka znajduje się wewnątrz tabeli przestawnej
• Spójrz na lewą stronę karty Opcje narzędzi tabeli przestawnej na wstążce. Jest przycisk Opcje. Nie klikaj przycisku Opcje! Po prawej stronie przycisku Opcje znajduje się strzałka rozwijana. Kliknij strzałkę w dół. Odznacz Generuj GetPivotData.
• Możesz teraz wprowadzać formuły za pomocą myszy, klawiszy strzałek lub pisania.

W programie Excel 2003 i starszych wykonaj następujące kroki:

• Wybierz Narzędzia, Dostosuj
• W oknie dialogowym Dostosuj znajdują się trzy zakładki. Wybierz kartę Polecenia pośrodku.
• W lewym polu listy wybierz siódmy element, Dane
• W prawym polu listy przewiń prawie do samego dołu. Ósma ikona od końca listy to Generuj GetPivotData. Przeciągnij tę ikonę z pola listy i upuść ją na środku dowolnego istniejącego paska narzędzi.
• Kliknij przycisk Zamknij, aby zamknąć okno dialogowe.
• Kliknij ikonę, którą właśnie dodałeś do paska narzędzi programu Excel. Spowoduje to wyłączenie tej funkcji. Możesz teraz wprowadzać formuły za pomocą myszy bez generowania funkcji GetPivotData.

Oto dodatkowa wskazówka: niegdyś gardziłem tą funkcją i po prostu chciałem ją wyłączać przez cały czas. Dodałem ikonę do mojego paska narzędzi, wyłączyłem ikonę, a następnie usunąłem ją z paska narzędzi. Teraz… trochę się uspokoiłem. Widzę, że ikona czasami ma dobre zastosowania. Zbudowałem więc tabelę przestawną w programie Excel 2003 i upewniłem się, że wskaźnik komórki znajduje się w tabeli przestawnej. Następnie użyłem okna dialogowego dostosowywania, aby przeciągnąć ikonę Generuj GetPivotData na pasek narzędzi tabeli przestawnej. Teraz - kiedy jestem w tabeli przestawnej, mogę włączyć lub wyłączyć tę funkcję.

Dlaczego firma Microsoft to zrobiła? Czy jest jakieś dobre zastosowanie dla GetPivotData?

Co ma zrobić GetPivotData? Najwyraźniej Microsoft lubi tę funkcję, ponieważ wymusili ją na milionach niczego niepodejrzewających ludzi, którzy używają tabel przestawnych.

GetPivotData zwróci wszystkie widoczne komórki z tabeli przestawnej. Jednak zamiast wskazywać adres komórki, możesz opisać wartość jako część wspólną różnych wartości pól.

W miarę zmian w tabeli przestawnej GetPivotData będzie nadal zwracać te same dane logiczne z tabeli przestawnej, pod warunkiem, że dane są nadal widoczne w tabeli przestawnej. Może to być ważne, jeśli zmieniasz się z miesiąca na miesiąc w polu strony tabeli przestawnej i chcesz zawsze zwracać sprzedaż dla określonego klienta. Ponieważ lista klientów zmienia się co miesiąc, pozycja klienta będzie się zmieniać. Używając =GETPIVOTDATA, możesz upewnić się, że zwracasz właściwą wartość z tabeli przestawnej.

=GETPIVOTDATAzawsze zaczyna się od dwóch konkretnych argumentów. Następnie opcjonalnie ma dodatkowe pary argumentów.

Oto dwa wymagane argumenty:

1. Nazwa pola danych. Może to być „Suma przychodów” lub po prostu „Przychody”.
2. Dowolna komórka znajdująca się „wewnątrz” tabeli przestawnej. Czy wiesz, że Twoja tabela przestawna ma nazwę? Prawdopodobnie o tym nie wiedziałeś, ponieważ nie możesz znaleźć nazwy w interfejsie programu Excel. Musiałbyś przejść do VBA, aby poznać nazwę tabeli przestawnej. Tak więc - firmie Microsoft łatwiej było umożliwić identyfikację tabeli przestawnej przez wskazanie dowolnej komórki w tabeli przestawnej. Chociaż możesz wybrać dowolną komórkę, najbezpieczniej jest wybrać komórkę w lewym górnym rogu. Istnieje prawdopodobieństwo, że tabela przestawna może się zmniejszyć dla określonej kombinacji pól strony. W takim przypadku użycie komórki w lewym górnym rogu zapewni, że będziesz wskazywać wewnątrz tabeli przestawnej.

Następnie kontynuujesz funkcję z dodatkowymi parami argumentów. Każda para zawiera nazwę pola i wartość.

GetPivotData może zwracać tylko wartości widoczne w tabeli przestawnej

Gdy zobaczysz, że działa kilka funkcji GetPivotData, możesz pomyśleć, że są one potężniejsze niż w rzeczywistości. W rzeczywistości funkcja będzie działać tylko wtedy, gdy określona wartość jest widoczna w tabeli przestawnej. Spójrz na poniższy obrazek.

• W komórce A2 GETPIVOTDATA zwraca sprzedaż ABC w regionie centralnym w styczniu 2004 roku. To jest pobieranie 80003 z komórki G19.
• Jednak formuła w A6 zawodzi. Prosi o sprzedaż ABC we wszystkich regionach. Tabela przestawna pokazuje całkowite ABC dla regionu Środkowego, całkowite ABC dla Wschodu, całkowite ABC dla Zachodu, ale nigdy nie pokazuje całkowitej liczby ABC dla wszystkich regionów, więc wynikiem jest #REF! błąd.
• Jeśli obrócisz pole regionu w górę do obszaru strony, formuła w A6 zacznie działać, ale formuła w A2 i A4 zacznie zwracać #REF !. Jeśli wybierzesz opcję Centralny z listy rozwijanej Region, wszystkie cztery formuły będą działać.
• Wyłączenie sum całkowitych w oknie dialogowym Opcje tabeli przestawnej spowoduje, że wiele funkcji GetPivotData zacznie zwracać #REF! błędy.