Nienawidzisz funkcji GETPIVOTDATA programu Excel? Dlaczego się pojawia? Jak możesz temu zapobiec? Czy warto skorzystać z GETPIVOTDATA?
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łę, taką jak = D5 / C5-1, bez 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 mały rysunek przedstawia nowy arkusz programu Excel dodany do skoroszytu. To tylko zwykły Excel, bez tabel przestawnych. Jedyną magią 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 styczeń, dane rzeczywiste dla Baybrook. Kliknij tę komórkę i wpisz znak równości. Za pomocą myszy przejdź z powrotem do tabeli przestawnej. Znajdź komórkę danych rzeczywistych ze stycznia 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.
Oto formuła po edycji. Zniknęły „Baybrook”, „Jan” i „Actual”. Zamiast tego wskazujesz na D6 $, 3 $ E, 4 $ E.
Skopiuj tę formułę, a następnie wybierz opcję Wklej specjalnie, formuły we wszystkich pozostałych komórkach liczbowych.
Oto Twój roczny 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.
Zmień komórkę P1
Musisz przyznać, że używanie zwykłego 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.
Obejrzyj wideo
- GetPivotData ma miejsce, gdy formuła wskazuje wnętrze tabeli przestawnej
- Chociaż formuła początkowa jest poprawna, nie można jej skopiować
- Większość ludzi nienawidzi getpivotdata i chce temu zapobiec
- Metoda 1: Zbuduj formułę bez myszy lub klawiszy strzałek
- Metoda 2: Wyłącz GetPivotData na stałe za pomocą menu rozwijanego obok opcji
- Ale jest zastosowanie dla GetPivotData
- Twój menedżer potrzebuje raportu zawierającego dane rzeczywiste za ostatnie miesiące i budżet na przyszłość
- Normalny przepływ pracy wymagałby utworzenia tabeli przestawnej, konwersji na wartości, usunięcia kolumn
- Usunięcie sum pośrednich, aby uniemożliwić styczniowy plan Rzeczywisty + za pomocą Ustawień pola
- Zamiast tego utwórz tabelę przestawną zawierającą „za dużo” danych
- Użyj ładnie sformatowanego arkusza raportu
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Z pierwszej komórki danych w arkuszu utwórz formułę za pomocą myszy
- Zezwól na działanie GetPivotData
- Zbadaj składnię GetPivotData (pole do zwrócenia, lokalizacja przestawna, pary)
- Zmień zakodowaną wartość, aby wskazywała komórkę
- Trzykrotne naciśnięcie klawisza F4 blokuje tylko kolumnę
- Dwukrotne naciśnięcie klawisza F4 blokuje tylko wiersz
- Wklej specjalne formuły
- Kolejny miesiąc: dodaj dane, odśwież tabelę przestawną, zmień datę
- Bardzo uważaj, aby uważać na nowe sklepy
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2013 - GetPivotData może nie być całkowicie zła!
Będę podcastował całą książkę, kliknij „i” w prawym górnym rogu, aby zasubskrybować.
W porządku, w odcinku 1998 rozmawiałem krótko o tym problemie GetPivotData. Jeśli obliczymy wariancję% i znajdujemy się poza tabelą przestawną wskazującą do środka, a ja używam myszy lub klawisza strzałki, więc 2019 / 2018-1. Ta odpowiedź, którą tu uzyskamy, jest poprawna dla stycznia, ale kiedy klikniemy dwukrotnie, aby ją skopiować, formuła się nie kopiuje, otrzymujemy odpowiedź ze stycznia aż do końca. A kiedy na to spojrzymy, otrzymujemy GetPivotData, nie wpisałem GetPivotData, po prostu wskazałem na te komórki i zaczęło się to dziać w programie Excel 2002 bez żadnego ostrzeżenia. W tym momencie powiedziałem, że sposobem na uniknięcie tego jest wpisanie wzoru C5 / B5-1, a otrzymasz formułę, którą możesz skopiować. Lub jeśli po prostu nienawidzisz GetPivotData, jeśli jest to „całkowicie złe”, przejdź do zakładki Analiza, nieOtwórz przy okazji przycisk Opcje. Wróć do tabeli przestawnej, przejdź do karty Analiza, otwórz menu rozwijane obok opcji, odznacz to pole, jest to ustawienie globalne. Po wyłączeniu będzie wyłączone na zawsze, w porządku.
Najczęściej otrzymuję pytania „Jak wyłączyć GetPivotData?” ale od czasu do czasu znajdę kogoś, kto kocha GetPivotData. Jadłem lunch z Robem Collie, gdy był jeszcze w firmie Microsoft, i powiedział: „Cóż, nasi wewnętrzni klienci uwielbiają GetPivotData”. Ja powiedziałem co? Nie, wszyscy nienawidzą GetPivotData! ” Rob mówi: „Masz rację, poza firmą Microsoft absolutnie nienawidzą GetPivotData”. Mówię o księgowych w firmie Microsoft, a później spotkałem Carlosa, który teraz pracuje dla zespołu Excela, a Carlos był jednym z księgowych, którzy używają tej metody.
W porządku, więc oto co musimy zrobić. Mamy tu nasz raport, zestaw danych, że na każdy miesiąc mamy plan dla każdego sklepu, a potem na dole gromadzimy dane rzeczywiste. W porządku, więc mamy dane rzeczywiste od stycznia do grudnia, ale tylko dla kilku miesięcy, miesięcy, które już minęły. Nasz menedżer chce, abyśmy stworzyli raport ze sklepami po lewej stronie, oczywiście tylko w Teksasie, aby utrudnić życie. A potem mamy miesiące, a jeśli mamy rzeczywisty dla tego miesiąca, pokazujemy rzeczywisty, czyli rzeczywisty styczeń, faktyczny luty, marzec, kwiecień. Ale potem dla miesięcy, w których nie mamy danych rzeczywistych, przełączamy się i pokazujemy budżet, więc budżet do grudnia, a potem w sumie wszystko, w porządku. Cóż, jeśli spróbujesz utworzyć tę tabelę przestawną, tak,to nie działa.
Więc wstaw tabelę przestawną, nowy arkusz roboczy, umieść Sklep po lewej stronie, z piękną stroną, umieść Miesiące u góry, wpisz Typ u góry, wstaw Sprzedaż tutaj, w porządku. Oto, co otrzymujemy, z czym musimy zacząć pracować, więc mamy rzeczywisty plan na styczeń, plan na styczeń, a następnie całkowicie bezużyteczny plan na styczeń z plusem. Nikt nigdy tego nie użyje, ale mogę pozbyć się tych szarych kolumn, to dość łatwe, niektórzy tutaj do tej komórki, przejdź do Ustawień pola i zmień Sumy częściowe na Brak. Ale nie ma absolutnie żadnego sposobu, abym usunął plan styczniowy, który nie usunie również planu z kwietnia, maja, czerwca i lipca, w porządku, nie ma sposobu, aby się tego pozbyć. Tak więc w tym momencie każdego miesiąca utknęłam, wybierając całą tabelę przestawną, przechodząc do Kopiuj, a następnie Wklej, Wklej wartości. To już nie jest tabela przestawna,a potem zaczynam ręcznie usuwać kolumny, które nie pojawiają się w raporcie.
W porządku, to normalna metoda, ale księgowi Microsoft dodali dodatkowy krok w styczniu, zajmuje to 15 minut, a ten krok pozwala tej tabeli przestawnej żyć wiecznie, prawda? Nazywam to najbrzydszą tabelą przestawną na świecie, a księgowi firmy Microsoft zgadzają się, że jest to najbrzydsza tabela przestawna na świecie, ale poza nimi nikt nigdy nie zobaczy tego raportu. To co robią, to przychodzą tutaj do nowego arkusza i budują raport, którego chce ich menedżer. W porządku, więc oto sklepy po lewej stronie, pogrupowałem je nawet w Houston, Dallas i inne, to ładnie sformatowany raport. Podkreśliłem sumy, zobaczysz, że kiedy otrzymamy kilka liczb, w pierwszym wierszu jest waluta, ale nie w kolejnych wierszach, pustych wierszach. Ooh, puste wiersze w tabeli przestawnej.I trochę logiki tutaj, gdzie mogę umieścić datę końcową w komórce P1, a następnie mam tutaj formułę, która analizuje, że JEŻELI miesiąc daty końcowej jest> ta kolumna, a następnie wstawię słowo Rzeczywiste, w przeciwnym razie wstaw słowo Plan, w porządku. Więc wszystko, co muszę zrobić, to zmienić to do daty, a następnie słowo Rzeczywisty przewróć do planu, w porządku.
Teraz, oto co robimy, pozwolimy sobie być GetPivotData'd, prawda? Nie jestem pewien, czy to czasownik, ale zamierzamy zezwolić firmie Microsoft na GetPivotData. Więc zaczynam budować formułę za pomocą =, chwytam mysz i zamierzam poszukać styczniowego prawdziwego Baybrook! Więc wracam do najbrzydszej tabeli przestawnej na świecie, znajduję Baybrook, znajduję styczeń, znajduję rzeczywisty, i klikam Enter i pozwalam im to zrobić, dobrze, zaczynamy, mamy teraz formułę GetPivotData. Pamiętam dzień, w którym to zrobiłem, było to tak, jakby Rob wyjaśnił mi, co robią, a ja wróciłem i spróbowałem. Teraz nagle przez całe życie pozbywałem się GetPivotData, tak naprawdę nigdy nie korzystałem z GetPivotData. Więc to jest pierwszy przedmiot, którego szukamy,Pole o nazwie Sprzedaż, tutaj zaczyna się tabela przestawna i może to być dowolna komórka w tabeli przestawnej, używają lewej górnej ręki.
W porządku, to jest nazwa pola „Sklep”, a następnie zakodowali na stałe „Baybrook”, to jest nazwa pola „Miesiąc”, wpisali na stałe „Styczeń”, to jest nazwa pola „Typ”, a oni ' ve zakodowane „Rzeczywiste”. Dlatego nie możesz tego skopiować, ponieważ zakodowali wartości na sztywno. Ale księgowi w firmie Microsoft, Carlos i jego współpracownicy zdają sobie sprawę: „Zaraz, chwileczkę, mamy tutaj słowo Baybrook, mamy styczeń, mamy Actual. Musimy tylko zmienić tę formułę, aby wskazywała rzeczywiste komórki w raporcie, zamiast być zapisywana na stałe ”. W porządku, więc wywołują to parametryzację GetPivotData.
Usuń słowo Baybrook, podejdź tutaj i kliknij komórkę D6. Teraz muszę zablokować to w kolumnie, w porządku, więc naciskam klawisz F4 3 razy, dostaję pojedynczy $ przed D, w porządku. W styczniu usuwam zakodowany na stałe styczeń, klikam komórkę E3, dwukrotnie wciskam F4, aby zablokować ją w wierszu E $ 3. Wpisz Actual, usuń słowo Actual, kliknij E4, ponownie dwukrotnie F4, dobrze, i otrzymuję formułę, która teraz pobiera te dane z powrotem. Skopiuję to, a następnie Wklej specjalnie, wybierz Formaty, alt = "" ESF, zobacz, że F jest tam podkreślone, ESF Enter, a teraz to zrobiłem. Powtórzę tylko z F4, F4 to ponawianie, a F4. W porządku, więc teraz mamy ładnie wyglądający raport, ma spacje, ma formatowanie, ma pojedyncze podkreślenie księgowe pod każdą sekcją,na samym dole ma podwójne podkreślenie księgowe.
Racja, nigdy nie dostaniesz tych rzeczy w tabeli przestawnej, to niemożliwe, ale ten raport jest oparty na tabeli przestawnej. Więc co robimy, gdy otrzymamy dane rzeczywiste z maja, wróć tutaj, wklej je, odśwież najbrzydszą na świecie tabelę przestawną, a następnie tutaj w raporcie po prostu zmień datę końcową z 30.04 na 31.05. A to powoduje, że ta formuła zmienia się ze słowa Plan na Rzeczywiste, co powoduje, że dane rzeczywiste z raportu zamiast planu są w porządku. Otóż, chodzi o to, że - to jest świetne, prawda? Widzę, gdzie bym to często robił, gdybym nadal pracował w księgowości.
Musisz być naprawdę ostrożny, jeśli budują nowy sklep, musisz wiedzieć, aby dodać go ręcznie, prawda, dane pojawią się w tabeli przestawnej, ale dodasz je ręcznie. Teraz ten jest podzbiorem wszystkich sklepów, gdyby raportował wszystkie sklepy, prawdopodobnie miałbym tutaj, poza zakresem drukowania, coś, co wyciągnęło sumę całkowitą z tabeli przestawnej. A potem wiedziałbym, jeśli ta suma nie pasuje do sumy całkowitej z tabeli przestawnej, że coś jest nie tak i mam tutaj funkcję JEŻELI Mówiąc „Hej, wiesz, nowe dane, które zostały dodane, bądź bardzo ostrożny. ” Mają jakiś mechanizm wykrywania, że są tam nowe dane. Ale rozumiem, to fajne zastosowanie. Tak więc, podczas gdy przez większość czasu GetPivotData po prostu doprowadza nas do szaleństwa, może być z tego pożytek. W porządku,to wskazówka nr 21 z 40 w książce. Kup książkę już teraz, zamów online, kliknij „i” w prawym górnym rogu.
Długie, długie podsumowanie dzisiaj, w porządku: GetPivotData ma miejsce, gdy formuła wskazuje wewnątrz tabeli przestawnej, a formuła poza tabelą przestawną wskazuje wewnątrz. Chociaż początkowa formuła jest poprawna, nie zostanie skopiowana. Większość ludzi nienawidzi GetPivotData i chce temu zapobiec. Możesz więc zbudować formułę bez myszy lub klawiszy strzałek, po prostu wpisz formułę lub wyłącz GetPivotData na stałe, ah, ale jest zastosowanie, w porządku. Musimy więc zbudować raport z danymi rzeczywistymi z poprzedniego miesiąca i budżetem na przyszłość. Normalny przepływ pracy, utwórz tabelę przestawną, przekonwertuj na wartości, Usuń kolumny. Istnieje sposób na usunięcie sum pośrednich za pomocą Ustawień pola, pozbywając się tego rzeczywistego planu Plus na styczeń. Zamiast tego po prostu utworzymy najbrzydszą na świecie tabelę przestawną ze zbyt dużą ilością danych.
Zbuduj ładnie sformatowany, po prostu stary arkusz raportu z odrobiną logiki, aby zmienić słowo Rzeczywiste na Plan. A następnie z pierwszej komórki raportu, pierwszego miejsca, w którym mają znajdować się liczby w tym raporcie, wpisz =, przejdź do tabeli przestawnej i pozwól, aby GetPivotData się stało. Badamy składnię GetPivotData, więc jest to pole do zwrócenia Sales, w którym znajduje się tabela przestawna, a następnie pary kryteriów, nazwa pola i wartość. Zamierzamy usunąć zakodowaną wartość i wskazać komórkę, naciśnięcie F4 3 razy blokuje tylko kolumnę, naciśnięcie F4 2 razy blokuje tylko wiersz, skopiuj tę formułę, Wklej specjalne formuły. Dodałem tam dodatkową wskazówkę, że F4 to przeróbka, więc musiałem tylko raz przejść do okna dialogowego Wklej specjalnie, a następnie dla następnego Wklej specjalne formuły właśnie użyłem F4. W przyszłym miesiącu dodaj dane,Odśwież tabelę przestawną, zmień datę końcową. Upewnij się, że nie zbudowali żadnych nowych sklepów, wiesz, mają jakiś mechanizm, albo ręczny, albo formułę czekową, sprawdź to. Podziękowania dla iTrainerMX na Twitterze, który zasugerował GetPivotData, a także Carlos i Rob z Microsoft, Rob teraz z Power Pivot Pro. Carlosowi za użycie tego i Robowi za poinformowanie mnie, że Carlos go używa. Spotkałem Carlosa później, a on potwierdził, że tak, był jednym z księgowych, którzy używali tego przez cały czas w firmie Microsoft. Dobra, proszę bardzo.i Rob za to, że powiedział mi, że Carlos go używa, spotkałem Carlosa później, a on potwierdził, że tak, był jednym z księgowych, którzy używali tego przez cały czas w firmie Microsoft. Dobra, proszę bardzo.i Rob za to, że powiedział mi, że Carlos go używa, spotkałem Carlosa później, a on potwierdził, że tak, był jednym z księgowych, którzy używali tego przez cały czas w firmie Microsoft. Dobra, proszę bardzo.
Hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2013.xlsx
