Vihaatko Excelin GETPIVOTDATA-toimintoa? Miksi se näkyy? Kuinka voit estää sen? Onko GETPIVOTDATAlle hyvää käyttöä?
Useimmat ihmiset kohtaavat ensin GETPIVOTDATAn, kun he yrittävät rakentaa kaavan pivot-taulukon ulkopuolelle, joka käyttää pivot-taulukon numeroita. Esimerkiksi tämä varianssiprosentti ei kopioidu alas muihin kuukausiin, koska Excel lisää GETPIVOTDATA-funktiot.
Excel lisää GETPIVOTDATA-tiedot aina, kun osoitat hiirtä tai nuolinäppäimiä pivot-taulukon sisäiseen soluun rakentaessasi kaavaa pivot-taulukon ulkopuolelle.
Muuten, jos et halua GETPIVOTDATA-toiminnon näkyvän, kirjoita yksinkertaisesti kaava, kuten = D5 / C5-1, osoittamatta soluihin hiirtä tai nuolinäppäimiä. Tämä kaava kopioitu ilman ongelmia.
Tässä on tietojoukko, joka sisältää yhden suunnitelman numeron kuukaudessa kauppaa kohden. Myös toteutuneiden kuukausien todellinen myynti kuukaudessa myymälää kohti. Tavoitteenasi on rakentaa raportti, joka näyttää toteutuneiden kuukausien toteutumat ja suunnitella tulevia kuukausia.
Rakenna pivot-taulukko Store in ROWS -palvelun avulla. Laita kuukausi ja kirjoita sarakkeisiin. Saat alla olevan raportin, jossa on tammikuun todellinen, tammikuun suunnitelma ja täysin järjetön tammikuun todellinen + suunnitelma.
Jos valitset kuukauden solun ja siirryt kenttäasetuksiin, voit muuttaa välisumman arvoksi Ei mitään.
Tämä poistaa hyödytön Todellinen + Suunnitelma. Mutta sinun on silti päästävä eroon suunnitelman sarakkeista tammikuusta huhtikuuhun. Ei ole mitään hyvää tapaa tehdä tämä pivot-pöydän sisällä.
Joten kuukausittainen työnkulku muuttuu:
- Lisää uuden kuukauden tosiasiat tietojoukkoon.
- Rakenna uusi kääntöpöytä alusta alkaen.
- Kopioi pivot-taulukko ja liitä arvoina, jotta se ei ole enää pivot-taulukko.
- Poista sarakkeet, joita et tarvitse.
On parempi tapa edetä. Seuraava hyvin pieni kuva näyttää uuden Excel-laskentataulukon, joka on lisätty työkirjaan. Tämä kaikki on vain suora Excel, ei pivot-taulukoita. Ainoa taikuutta on IF-funktio rivillä 4, joka vaihtaa todellisesta suunnitelmaan solun P1 päivämäärän perusteella.
Ensimmäinen solu, joka on täytettävä, on tammikuu, Actuals for Baybrook. Napsauta kyseistä solua ja kirjoita yhtäläisyysmerkki. Siirry hiirellä takaisin pivot-taulukkoon. Etsi Baybrookin tammikuun todellisten solu. Napsauta kyseistä solua ja paina Enter. Kuten tavallista, Excel rakentaa yhden niistä ärsyttävistä GETPIVOTDATA-toiminnoista, joita ei voida kopioida.
Mutta tänään tutkitaan GETPIVOTDATA-syntaksia.
Ensimmäinen alla oleva argumentti on numeerinen kenttä "Myynti". Toinen argumentti on solu, jossa pivot-taulukko sijaitsee. Loput argumenttiparit ovat kentän nimi ja arvo. Näetkö mitä automaattisesti luotu kaava teki? Se kovakoodattu "Baybrook" kaupan nimellä. Siksi et voi kopioida näitä automaattisesti luotuja GETPIVOTDATA-kaavoja. He todella koodaavat nimiä kaavoiksi. Vaikka et voi kopioida näitä kaavoja, voit muokata niitä. Tässä tapauksessa olisi parempi, jos muokkaat kaavaa osoittamaan soluun $ D6.
Tässä on kaava muokkaamisen jälkeen. Poissa ovat "Baybrook", "Jan" ja "Actual". Sen sijaan osoitat $ D6, E $ 3, E $ 4.
Kopioi tämä kaava ja valitse sitten Liitä erityinen, kaavat kaikkiin muihin numeerisiin soluihin.
Nyt tässä on vuotuinen työnkulku:
- Rakenna ruma kääntöpöytä, jota kukaan ei koskaan näe.
- Määritä raportin laskentataulukko.
Joka kuukausi sinun on:
- Liitä uudet tiedot ajan alle.
- Päivitä ruma kääntötaulukko.
-
Muuta raporttisivun solu P1 vastaamaan uutta kuukautta. Kaikki numerot päivittyvät.
Vaihda solu P1
Sinun on myönnettävä, että käyttämällä tavallista raporttia, joka vetää numerot pivot-taulukosta, saat molempien maailmojen parhaat puolet. Voit vapaasti muotoilla raportin tavalla, jota et voi muotoilla pivot-taulukkoa. Tyhjät rivit ovat kunnossa. Ensimmäisessä ja viimeisessä rivissä voi olla valuuttamerkkejä, mutta ei niiden välissä. Saat kaksoisviivaa myös loppusumman alle.
Kiitos @iTrainerMX: lle tämän ominaisuuden ehdottamisesta.
Katso video
- GetPivotData tapahtuu, kun kaava osoittaa pivot-taulukon sisälle
- Vaikka alkuperäinen kaava on oikea, et voi kopioida kaavaa
- Suurin osa ihmisistä vihaa getpivotdataa ja haluaa estää sen
- Tapa 1: Rakenna kaava ilman hiirtä tai nuolinäppäimiä
- Tapa 2: Poista GetPivotData käytöstä pysyvästi käyttämällä vaihtoehtojen vieressä olevaa avattavaa valikkoa
- Mutta GetPivotDatalle on käyttöä
- Ylläpitäjäsi haluaa raportin Todelliset tiedot viime kuukausilta ja tulevan budjetin
- Normaalissa työnkulussa sinun on luotava pivot-taulukko, muunnettava arvoiksi ja poistettava sarakkeet
- Välisummien poistaminen estää tammikuun todellisen + suunnitelman käyttämällä kenttäasetuksia
- Luo sen sijaan pivot-taulukko, jossa on "liikaa" tietoa
- Käytä hienosti muotoiltua raporttitaulukkoa
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Luo laskentataulukon ensimmäisestä datasolusta kaava hiirellä
- Anna GetPivotDatan tapahtua
- Tutki GetPivotDatan syntaksia (palattava kenttä, kääntöpaikka, parit)
- Muuta kovakoodattu arvo osoittamaan soluun
- F4-painikkeen painaminen kolme kertaa lukitsee vain sarakkeen
- F4-painikkeen painaminen kahdesti lukitsee vain rivin
- Liitä erikoiskaavat
- Työnkulku ensi kuussa: Lisää tietoja, päivitä pivot-taulukko, muuta päivämäärää
- Ole erityisen varovainen varmistaaksesi uusia kauppoja
Videon transkriptio
Opi Excel podcastista, jakso 2013 - GetPivotData ei välttämättä ole täysin paha!
Lähetän podcasting tämän koko kirjan, klikkaa "i" oikeassa yläkulmassa tilaa.
Selvä, takaisin jaksossa 1998 puhuin lyhyesti tästä GetPivotData-ongelmasta. Jos laskemme varianssin% ja olemme sisällä olevan Pivot-taulukon ulkopuolella, käytän hiirtä tai nuolinäppäintä, joten 2019 / 2018-1. Tämä vastaus, jonka saamme tänne, on oikea tammikuussa, mutta kun kaksoisnapsautamme kopioidaksemme sen, kaava ei kopioi, saamme tammikuun vastauksen kokonaan alas. Ja kun katsomme sitä, saamme GetPivotDataa, en kirjoittanut GetPivotDataa, osoitin vain näihin soluihin, ja tämä alkoi tapahtua Excel 2002: ssa ilman minkäänlaista varoitusta. Ja siinä vaiheessa sanoin, että tapa välttää tämä on kirjoittaa kaava C5 / B5-1, ja saat kaavan, jonka voit kopioida. Tai jos vain vihaat GetPivotDataa, jos se on "täysin paha", siirry Analysoi-välilehteen, älät avaa muuten Asetukset-painike. Palaa Pivot-taulukkoon, siirry Analysoi-välilehteen, avaa Asetukset-kohdan vieressä oleva avattava valikko, poista valinta tästä valintaruudusta, se on globaali asetus. Kun sammutat sen, se on pois päältä ikuisesti.
Useimmiten saamani kysymykset ovat "Kuinka GetPivotData poistetaan käytöstä?" mutta silloin tällöin saan jonkun, joka rakastaa GetPivotDataa. Ja söin lounaan Rob Collien kanssa, kun hän oli vielä Microsoftissa, ja hän sanoi: "No, sisäiset asiakkaamme rakastavat GetPivotDataa." Sanoin ”Mitä? Ei, kaikki vihaavat GetPivotDataa! " Rob sanoo: "Olet oikeassa, Microsoftin ulkopuolella, he vihaavat GetPivotDataa." Puhun kirjanpitäjistä Microsoftin sisällä, ja myöhemmin tapasin yhden, joka nyt työskentelee Excel-tiimissä, Carlos, ja Carlos oli yksi kirjanpitäjistä, jotka käyttävät tätä menetelmää.
Selvä, joten tässä meidän on tehtävä. Meillä on raporttimme, tietojoukko, jossa meillä on jokaisen kuukauden suunnitelma jokaiselle myymälälle, ja sitten alareunassa keräämme todellisuutta. Selvä, joten meillä on tosiasiat tammikuusta joulukuuhun, mutta tosiasiat ovat vain muutaman kuukauden, menneiden kuukausien ajan. Ja mitä johtajamme haluaa meidän tekevän, on rakentaa raportti, jossa myymälät ovat vasemmalla puolella, vain Texas-myymälät tietysti vaikeuttavat elämää. Ja sitten käy läpi kuukausia, ja jos meillä on todellinen kyseiselle kuukaudelle, näytämme todellisen, siis tammikuun todellisen, helmikuun todellisen, maaliskuun todellisen, huhtikuun todellisen. Mutta sitten kuukausina, jolloin meillä ei ole todellisuutta, vaihdamme ja näytämme budjetin, joten budjetti loppuu joulukuuhun ja sitten kaikki yhteensä, okei. No, kun yrität luoda tämän pivot-taulukon, joo,se ei toimi.
Joten aseta PivotTable, Uusi laskentataulukko, laitat Store alas vasemmalle puolelle, joka on kaunis, laita Kuukaudet ylhäältä, laita Type ylhäältä, laita Myynti tänne. Joten tässä saamme, että meidän on aloitettava työskentely, joten meillä on tammikuun varsinainen, tammikuun suunnitelma ja sitten täysin hyödytön tammikuun todellinen plus-suunnitelma. Kukaan ei koskaan käytä tätä, mutta voin päästä eroon näistä harmaista sarakkeista, mikä on tarpeeksi helppoa, jotkut täällä tähän soluun, siirry kenttäasetuksiin ja vaihda välisumman arvoksi Ei mitään. Mutta minulla ei ole mitään keinoa poistaa tammikuun suunnitelmaa, joka ei myöskään poista huhtikuun toukokuun kesäkuun heinäkuun suunnitelmaa, okei, ei ole mitään keinoa päästä eroon tästä. Joten tässä vaiheessa joka kuukausi, olen juuttunut valitsemaan koko pivot-taulukon, siirtymällä Kopioi ja sitten Liitä, liitä arvot. Se ei ole enää Pivot-taulukko,ja sitten aloitan sarakkeiden manuaalisen poistamisen, jotka eivät näy raportissa.
Selvä, se on normaali menetelmä, mutta Microsoftin kirjanpitäjät ovat lisänneet ylimääräisen askeleen tammikuussa, se vie 15 minuuttia, ja tämä vaihe antaa tämän Pivot-pöydän elää ikuisesti, eikö? Kutsun tätä maailman rumin Pivot-taulukoksi, ja Microsoftin kirjanpitäjät hyväksyvät, että tämä on maailman rumin Pivot-taulukko, mutta kukaan ei koskaan näe tätä raporttia paitsi heidät. Mitä he tekevät, ovatko he tulleet tänne uudelle arkille ja rakentavat raportin, jonka johtaja haluaa. Selvä, joten tässä ovat vasemmalla puolella olevat kaupat, ryhmitin sen jopa Houstoon, Dallasiin ja muihin, se on hienosti muotoiltu raportti. Olen korostanut summat, huomaat, että kun saamme joitain numeroita, ensimmäisellä rivillä on valuutta, mutta ei näitä seuraavia, tyhjiä rivejä. Oi, tyhjät rivit Pivot-taulukossa.Ja yksi pieni logiikka täällä, johon voin laittaa läpimenoajan soluun P1, ja sitten minulla on täällä kaava, joka analysoi, että JOS läpimenopäivän kuukausi on> tämä sarake, ja laita sitten sana Todellinen Laita sana Suunnitelma. Joten minun tarvitsee vain muuttaa tämä päivämäärä ja sitten sana Todellinen kääntää suunnitellaksesi, Alright.
Nyt, mitä teemme, annamme itsemme olla GetPivotData'd, eikö? En ole varma, että se on verbi, mutta annamme Microsoftin saada GetPivotData. Joten aloitan kaavan rakentamisen =: lla, tartun hiiren ja aion etsiä tammikuun todellista Baybrookia! Joten palaan takaisin maailman rummimpaan Pivot-taulukkoon, löydän Baybrookin, tammikuun, todellisen ja napsautan Enter-näppäintä ja annan heidän tehdä sen minulle. Muistan päivän, jolloin tein tämän, se oli kuin tiedät, kun Rob selitti minulle, mitä he tekivät, ja minä palasin takaisin ja kokeilin sitä. Nyt yhtäkkiä, koko elämäni, olen päässyt eroon GetPivotDatasta, en ole koskaan oikeastaan omaksunut GetPivotDataa. Joten mitä se on, on ensimmäinen tuote, jota etsimme, siellä ''Sa-kenttä nimeltä Myynti, pivot-taulukko alkaa tästä, ja se voi olla mikä tahansa pivot-taulukon solu, he käyttävät vasenta yläkulmaa.
Selvä, tämä on kentän nimi "Store", ja sitten he ovat kovakoodanneet "Baybrook", tämä on kentän nimi "Month", he ovat koodanneet "tammikuu", tämä on kentän nimi "Type" ja he " olet kovakoodattu ”Todellinen”. Siksi et voi kopioida sitä, koska he ovat koodanneet arvot. Mutta Microsoftin kirjanpitäjät, Carlos ja hänen työtoverinsa ymmärtävät: "Vau, odota hetki, meillä on täällä sana Baybrook, meillä on tammikuu täällä, meillä on Actual täällä. Meidän on vain muutettava tämä kaava osoittamaan raportin todelliset solut kovakoodaamisen sijaan. " Selvä, joten he kutsuvat tätä parametrisoimaan GetPivotDataa.
Poista sana Baybrook, tule tänne ja napsauta solua D6. Minun on nyt lukittava tämä sarakkeeseen, okei, joten painan F4-näppäintä 3 kertaa, saan yhden dollarin ennen D: tä, ok. Tammikuussa poistan kovakoodatun tammikuun, napsautan solua E3, painan F4 kahdesti lukitaksesi sen riville, E $ 3. Kirjoita Todellinen, poista sana Todellinen, napsauta E4, uudelleen F4 kahdesti, ok, ja saan kaavan, joka nyt vetää nämä tiedot takaisin. Kopioin sen ja sitten Liitä erityiseksi, valitse Muotoilut, alt = "" ESF, katso, että F on alleviivattu, ESF Enter, ja nyt kun olen tehnyt niin, toistan vain F4: llä, F4 on uudelleen ja F4. Selvä, joten nyt meillä on hyvännäköinen raportti, se on tyhjiä, siinä on muotoiluja, siinä on yksi kirjanpitoviiva jokaisessa osiossa,aivan alareunassa on kaksinkertainen kirjanpitoviiva.
Aivan, et koskaan saa näitä juttuja Pivot-taulukkoon, se on mahdotonta, mutta tämä raportti ohjataan Pivot-taulukosta. Joten mitä teemme, kun saamme toukokuun todelliset tiedot, palaa tänne, liitä ne sisään, mene päivittämään maailman rumin Pivot-taulukko ja vaihda sitten tässä raportissa vain läpäisypäivä 4/30: sta 5/31: een. Ja mitä tämä tekee, tämä saa tämän kaavan siirtymään sanasta Suunnitelma Todelliseksi, joka menee ja vetää todelliset tiedot raportista suunnitelman sijaan. Nyt tässä on se asia - tämä on hieno, eikö? Näen, missä tekisin tämän paljon, jos työskentelisin edelleen kirjanpidossa.
Asia, johon sinun on oltava todella varovainen, on, että jos he rakentavat uuden myymälän, sinun on tiedettävä, kuinka lisätä se manuaalisesti, oikea, tiedot näkyvät pivot-taulukossa, mutta lisäät ne manuaalisesti. Nyt tämä on kaikkien kauppojen osajoukko, jos se raportoi kaikista kaupoista, haluaisin todennäköisesti täällä, tulostusalueen ulkopuolella, olla jotain, joka veti loppusumman Pivot-taulukosta. Ja sitten tiedän, jos tämä summa ei vastaa Pivot-taulukon loppusummaa, että jokin on vialla, ja jos täällä on JOS-funktio. ” Heillä on jonkinlainen mekanismi havaitsemaan, että uusia tietoja on olemassa. Mutta saan sen, se on viileä käyttö. Joten, vaikka suurimman osan ajasta GetPivotData vain ajaa meidät hulluksi, sille voi tosiasiallisesti olla hyötyä. Hyvä on,Joten se on vihje # 21/40 kirjassa, osta kirja juuri nyt, tilaa verkossa, napsauta oikeaa yläkulmaa olevaa “i” -merkkiä.
Pitkä, pitkä yhteenveto tänään, okei: GetPivotData tapahtuu, kun kaava osoittaa pivot-taulukon sisälle, kaava pivot-taulukon ulkopuolelle osoittaa. Vaikka alkuperäinen kaava on oikea, sitä ei kopioida. Suurin osa ihmisistä vihaa GetPivotDataa ja haluaa estää sen. Joten voit rakentaa kaavan ilman hiirtä tai nuolinäppäimiä, kirjoita kaava tai sammuta GetPivotData pysyvästi, ah, mutta siellä on käyttöä, okei. Joten meidän on rakennettava raportti, joka sisältää viimeisen kuukauden todelliset tiedot, tulevaisuuden budjetin. Normaali työnkulku, luo pivot-taulukko, muunna arvoiksi, poista sarakkeet. Välisummat voidaan poistaa käyttämällä kenttäasetuksia, jolloin pääset eroon tammikuun todellisesta plus-suunnitelmasta. Sen sijaan aiomme vain luoda maailman rumin Pivot-taulukon, jossa on liikaa tietoja.
Rakenna hienosti muotoiltu, yksinkertainen vanha raporttitaulukko, jossa on ehkä vähän logiikkaa, jotta sana Todellinen muutettaisiin Suunnitelmaksi. Ja sitten ensimmäisestä raporttisolusta, ensimmäinen paikka, jossa numerot tulevat olemaan kyseisessä raportissa, kirjoita =, siirry pivot-taulukkoon ja anna GetPivotData tapahtua. Tutkimme GetPivotDatan syntaksia, joten se on palautettava kenttä, Myynti, jossa pivot-taulukko asuu, ja sitten ehtoparit, kentän nimi ja arvo. Poistamme kovakoodatun arvon ja osoitamme soluun. F4-painikkeen painaminen 3 kertaa lukitsee vain sarakkeen, F4-painikkeen painaminen 2 kertaa vain rivin lukitsemisen, kopioi kaava, Liitä erikoiskaavat. Heitin siellä ylimääräisen vihjeen, että F4 on uusinta, joten minun piti mennä vain Liitä erityinen -valintaikkunaan ja sitten seuraavissa Liitä erikoiskaavoissa juuri käytetty F4. Lisää seuraava kuukausi tiedot,päivitä pivot-taulukko, muuta päivämäärä. Varmista, että he eivät rakentaneet uusia kauppoja, tiedä, että heillä on jonkinlainen mekanismi, joko manuaalinen tai tarkistuskaava, tarkista se. Kiitos iTrainerMX Twitterissä, joka ehdotti GetPivotDataa, myös Carlos ja Rob Microsoftista, Rob nyt Power Pivot Prosta. Carlos tämän käyttämisestä, ja Rob siitä, että hän kertoi minulle, että Carlos käytti sitä, tapasin Carlosin myöhemmin, ja hän vahvisti kyllä, hän oli yksi kirjanpitäjistä, joka käytti tätä koko ajan Microsoftissa.ja Rob kertoivat minulle, että Carlos käytti sitä, tapasin Carlosin myöhemmin, ja hän vahvisti kyllä, hän oli yksi kirjanpitäjistä, joka käytti tätä koko ajan Microsoftissa.ja Rob kertoivat minulle, että Carlos käytti sitä, tapasin Carlosin myöhemmin, ja hän vahvisti kyllä, hän oli yksi kirjanpitäjistä, joka käytti tätä koko ajan Microsoftissa.
Hei, haluan kiittää sinua vierailustasi, nähdään ensi kerralla uudesta netcastista!
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2013.xlsx
