Większość ludzi po raz pierwszy napotyka GETPIVOTDATA, gdy próbują zbudować formułę poza tabelą przestawną, która używa liczb w tabeli przestawnej. Na przykład ten procent wariancji nie zostanie skopiowany do innych miesięcy z powodu wstawienia funkcji GETPIVOTDATA przez program Excel.
Program Excel wstawia GETPIVOTDATA za każdym razem, gdy używasz myszy lub klawiszy strzałek, aby wskazać komórkę wewnątrz tabeli przestawnej podczas tworzenia formuły poza tabelą przestawną.
Nawiasem mówiąc, jeśli nie chcesz, aby funkcja GETPIVOTDATA była wyświetlana, po prostu wpisz formułę, na przykład =D5/C5-1bez używania myszy lub klawiszy strzałek do wskazywania komórek. Ta formuła kopiuje się bez żadnych problemów.
Oto zestaw danych zawierający jeden numer planu miesięcznie na sklep. Istnieją również rzeczywiste miesięczne obroty na sklep w zakończonych miesiącach. Twoim celem jest zbudowanie raportu zawierającego dane rzeczywiste za zakończone miesiące i plan na przyszłe miesiące.
Utwórz tabelę przestawną za pomocą opcji Store in Rows. Wpisz miesiąc i wpisz kolumny. Otrzymasz raport pokazany poniżej, z Planem Rzeczywistym na styczeń, Planem styczniowym i kompletnie bezsensownym Planem Rzeczywistym + na styczeń.
Jeśli wybierzesz komórkę miesiąca i przejdziesz do Ustawień pola, możesz zmienić Sumy częściowe na Brak.
Spowoduje to usunięcie bezużytecznego planu Actual +. Ale nadal musisz pozbyć się kolumn planu na okres od stycznia do kwietnia. W tabeli przestawnej nie ma dobrego sposobu na zrobienie tego.
Tak więc Twój miesięczny przepływ pracy staje się:
- Dodaj wartości rzeczywiste dla nowego miesiąca do zestawu danych.
- Utwórz nową tabelę przestawną od podstaw.
- Skopiuj tabelę przestawną i wklej jako wartości, aby nie była już tabelą przestawną.
- Usuń kolumny, których nie potrzebujesz.
Jest lepszy sposób. Poniższy bardzo skompresowany rysunek przedstawia nowy arkusz programu Excel dodany do skoroszytu. To tylko zwykły Excel, bez tabel przestawnych. Jedynym odrobiną magii jest funkcja JEŻELI w wierszu 4, która przełącza się z rzeczywistych na planowane na podstawie daty w komórce P1.
Pierwsza komórka, którą należy wypełnić, to dane ze stycznia dla Baybrook. Kliknij tę komórkę i wpisz znak równości.
Za pomocą myszy przejdź z powrotem do tabeli przestawnej. Znajdź komórkę „Rzeczywiste styczeń” dla Baybrook. Kliknij tę komórkę i naciśnij Enter. Jak zwykle Excel tworzy jedną z tych irytujących funkcji GETPIVOTDATA, których nie można skopiować.
Ale dzisiaj przyjrzyjmy się składni GETPIVOTDATA.
Pierwszy argument poniżej to pole liczbowe „Sprzedaż”. Drugi argument to komórka, w której znajduje się tabela przestawna. Pozostałe pary argumentów to nazwa pola i wartość. Czy widzisz, co zrobiła automatycznie wygenerowana formuła? Na stałe zakodował „Baybrook” jako nazwę sklepu. Dlatego nie można skopiować tych automatycznie wygenerowanych formuł GETPIVOTDATA. W rzeczywistości zapisują nazwy w formułach. Nawet jeśli nie możesz skopiować tych formuł, możesz je edytować. W takim przypadku byłoby lepiej, gdybyś zmodyfikował formułę tak, aby wskazywała komórkę $ D6.
Poniższy rysunek przedstawia formułę po jej edycji. Nie ma już „Baybrook”, „Jan” i „Actual”. Zamiast tego wskazujesz na D6 $, 3 $ i 4 $.
Skopiuj tę formułę, a następnie wybierz opcję Wklej specjalnie, formuły we wszystkich pozostałych komórkach liczbowych.
Oto Twój miesięczny przepływ pracy:
- Zbuduj brzydką tabelę przestawną, której nikt nigdy nie zobaczy.
- Skonfiguruj arkusz raportu.
Każdego miesiąca musisz:
- Wklej nowe wartości rzeczywiste poniżej danych.
- Odśwież brzydką tabelę przestawną.
-
Zmień komórkę P1 w arkuszu raportu, aby odzwierciedlała nowy miesiąc. Aktualizacja wszystkich numerów.
Musisz przyznać, że korzystanie z raportu, który pobiera liczby z tabeli przestawnej, daje ci to, co najlepsze z obu światów. Możesz sformatować raport w sposób, w jaki nie możesz sformatować tabeli przestawnej. Puste rzędy są w porządku. Możesz mieć symbole walut w pierwszym i ostatnim wierszu, ale nie pomiędzy nimi. Pod sumami końcowymi również pojawiają się podwójne podkreślenia.
Dzięki @iTrainerMX za zasugerowanie tej funkcji.
