Večina ljudi se prvič sreča z GETPIVOTDATA, ko poskuša zgraditi formulo zunaj vrtilne tabele, ki uporablja številke v vrtilni tabeli. Na primer, ta odstotek variance se ne bo kopiral na druge mesece, ker Excel vstavi funkcije GETPIVOTDATA.
Excel vstavi GETPIVOTDATA vsakič, ko z miško ali puščičnimi tipkami pokažete na celico znotraj vrtilne tabele, medtem ko gradite formulo zunaj vrtilne tabele.
Mimogrede, če ne želite, da se prikaže funkcija GETPIVOTDATA, preprosto vnesite formulo, na primer =D5/C5-1brez uporabe miške ali puščičnih tipk za usmerjanje na celice. Ta formula se brez težav kopira.
Tu je nabor podatkov, ki vsebuje eno številko načrta na mesec na trgovino. Obstajajo tudi dejanske prodaje na mesec na trgovino za mesece, ki so končani. Vaš cilj je sestaviti poročilo, ki prikazuje dejanske podatke za zaključene mesece in načrt za prihodnje mesece.
Zgradite vrtilno tabelo s storitvijo Shrani v vrstice. V stolpce vnesite mesec in tip. Dobite poročilo, prikazano spodaj, z dejanskim januarjem, januarskim načrtom in popolnoma nesmiselnim januarskim dejanskim + načrtom.
Če izberete celico za mesec in odprete Nastavitve polja, lahko medseštevke spremenite v Brez.
S tem odstranite neuporaben načrt Actual +. A vseeno se morate znebiti stolpcev načrta za januar do aprila. V vrtilni tabeli tega ni dobrega načina.
Torej, vaš mesečni potek dela postane:
- V nabor podatkov dodajte dejanske podatke za novi mesec.
- Izdelajte novo vrtilno tabelo iz nič.
- Kopirajte vrtilno tabelo in prilepite kot vrednosti, tako da ta ni več vrtilna tabela.
- Izbrišite stolpce, ki jih ne potrebujete.
Obstaja boljša pot. Naslednja zelo stisnjena slika prikazuje nov Excelov delovni list, dodan v delovni zvezek. Vse to je samo naravnost Excel, brez pivot tabel. Edina magija je funkcija IF v vrstici 4, ki preklopi iz dejanskega v načrt, glede na datum v celici P1.
Prva celica, ki jo je treba izpolniti, je januar Actual za Baybrook. Kliknite to celico in vnesite znak enakosti.
Z miško se pomaknite nazaj do vrtilne tabele. Poiščite celico za januar Actual za Baybrook. Kliknite to celico in pritisnite Enter. Kot običajno Excel ustvari eno izmed tistih nadležnih funkcij GETPIVOTDATA, ki jih ni mogoče kopirati.
Danes pa preučimo sintakso GETPIVOTDATA.
Prvi argument spodaj je številčno polje "Prodaja". Drugi argument je celica, v kateri je vrtilna tabela. Preostali pari argumentov so ime polja in vrednost. Ali vidite, kaj je storila samodejno ustvarjena formula? Kot ime trgovine je bilo težko kodirano "Baybrook". Zato teh samodejno ustvarjenih formul GETPIVOTDATA ne morete kopirati. Imena dejansko trdo kodirajo v formule. Čeprav teh formul ne morete kopirati, jih lahko uredite. V tem primeru bi bilo bolje, če bi formulo uredili tako, da kaže na celico $ D6.
Spodnja slika prikazuje formulo, potem ko jo uredite. Gone so "Baybrook", "Jan" in "Actual". Namesto tega kažete na $ D6, E $ 3 in E $ 4.
Kopirajte to formulo in nato izberite Paste Special, Formulas v vseh drugih numeričnih celicah.
Zdaj je tu vaš mesečni potek dela:
- Zgradite grdo vrtilno tabelo, ki je nihče ne bo videl.
- Nastavite delovni list poročila.
Vsak mesec morate:
- Pod podatke prilepite nove dejanske podatke.
- Osvežite grdo vrtilno tabelo.
-
Spremenite celico P1 na poročilu, da bo odražala nov mesec. Vse številke se posodobijo.
Morate priznati, da vam uporaba poročila, ki potegne številke iz vrtilne tabele, daje najboljše iz obeh svetov. Poročilo lahko formatirate tako, da vrtilne tabele ne morete oblikovati. Prazne vrstice so v redu. V prvi in zadnji vrstici so lahko simboli valut, vmes pa ne. Pod velikimi seštevki dobite tudi dvojna podčrtanja.
Hvala @iTrainerMX, ker je predlagal to funkcijo.
