Heb je een hekel aan de GETPIVOTDATA-functie van Excel? Waarom verschijnt het? Hoe kun je dit voorkomen? Is er een goed gebruik voor GETPIVOTDATA?
De meeste mensen komen GETPIVOTDATA voor het eerst tegen wanneer ze een formule proberen te bouwen buiten een draaitabel die getallen in de draaitabel gebruikt. Dit variantiepercentage wordt bijvoorbeeld niet naar de andere maanden gekopieerd omdat Excel GETPIVOTDATA-functies invoegt.
Excel voegt GETPIVOTDATA in telkens wanneer u de muis- of pijltoetsen gebruikt om naar een cel in de draaitabel te wijzen terwijl u een formule buiten de draaitabel maakt.
Als u trouwens niet wilt dat de functie GETPIVOTDATA verschijnt, typ dan gewoon een formule zoals = D5 / C5-1 zonder de muis of pijltoetsen te gebruiken om naar cellen te wijzen. Die formule kopieert zonder problemen.
Hier is een dataset die één abonneenummer per maand per winkel bevat. Er zijn ook daadwerkelijke verkopen per maand per winkel voor de maanden die compleet zijn. Uw doel is om een rapport samen te stellen met de werkelijke cijfers voor de voltooide maanden en plannen voor de komende maanden.
Bouw een draaitabel met Store in ROWS. Zet Maand en typ de KOLOMMEN. U krijgt het onderstaande rapport te zien, met Januari Actual, Januari Plan en het volkomen onzinnige Januari Actual + Plan.
Als u een maandcel selecteert en naar Veldinstellingen gaat, kunt u subtotalen wijzigen in Geen.
Dit verwijdert het nutteloze Actual + Plan. Maar je moet nog steeds van de planzuilen af voor januari tot en met april. Er is geen goede manier om dit te doen in de draaitabel.
Uw maandelijkse workflow wordt dus:
- Voeg de werkelijke waarden voor de nieuwe maand toe aan de dataset.
- Bouw een nieuwe draaitabel vanaf het begin.
- Kopieer de draaitabel en plak deze als waarden zodat het geen draaitabel meer is.
- Verwijder de kolommen die u niet nodig heeft.
Er is een betere manier om te gaan. De volgende zeer kleine afbeelding toont een nieuw Excel-werkblad dat aan de werkmap is toegevoegd. Dit is allemaal gewoon Excel, geen draaitabellen. Het enige beetje magie is een ALS-functie in rij 4 die schakelt van Werkelijk naar Plan op basis van de datum in cel P1.
De allereerste cel die moet worden ingevuld, is januari, Actuals voor Baybrook. Klik in die cel en typ een gelijkteken. Navigeer met de muis terug naar de draaitabel. Zoek de cel voor januari Actuals voor Baybrook. Klik op die cel en druk op Enter. Zoals gewoonlijk bouwt Excel een van die vervelende GETPIVOTDATA-functies die niet kunnen worden gekopieerd.
Maar laten we vandaag de syntaxis van GETPIVOTDATA bestuderen.
Het eerste argument hieronder is het numerieke veld "Verkoop". Het tweede argument is de cel waarin de draaitabel zich bevindt. De overige argumentenparen zijn veldnaam en waarde. Zie je wat de automatisch gegenereerde formule deed? Het heeft "Baybrook" hard gecodeerd als de naam van de winkel. Daarom kunt u deze automatisch gegenereerde GETPIVOTDATA-formules niet kopiëren. Ze coderen namen eigenlijk in formules. Ook al kunt u deze formules niet kopiëren, u kunt ze wel bewerken. In dit geval zou het beter zijn als u de formule zo bewerkt dat deze naar cel $ D6 wijst.
Hier is de formule nadat u deze heeft bewerkt. Voorbij zijn "Baybrook", "Jan" en "Actual". In plaats daarvan wijst u naar $ D6, E $ 3, E $ 4.
Kopieer deze formule en kies vervolgens Plakken speciaal, formules in alle andere numerieke cellen.
Dit is nu uw jaarlijkse workflow:
- Bouw een lelijke draaitabel die niemand ooit zal zien.
- Stel het rapportwerkblad in.
Elke maand moet u:
- Plak nieuwe werkelijke waarden onder de gegevens.
- Vernieuw de lelijke draaitabel.
-
Wijzig cel P1 op het rapportblad om de nieuwe maand weer te geven. Alle cijfers worden bijgewerkt.
Verander cel P1
Je moet toegeven dat het gebruik van een eenvoudig rapport dat cijfers uit een draaitabel haalt, je het beste van twee werelden geeft. U bent vrij om het rapport zo op te maken dat u geen draaitabel kunt opmaken. Lege rijen zijn prima. U kunt valutasymbolen op de eerste en laatste rij hebben, maar niet ertussenin. U krijgt ook dubbele onderstrepingen onder de eindtotalen.
Met dank aan @iTrainerMX voor het voorstellen van deze functie.
Bekijk video
- GetPivotData vindt plaats wanneer een formule naar een draaitabel verwijst
- Hoewel de oorspronkelijke formule correct is, kunt u de formule niet kopiëren
- De meeste mensen hebben een hekel aan getpivotdata en willen dit voorkomen
- Methode 1: Bouw een formule zonder de muis of pijltoetsen
- Methode 2: Schakel GetPivotData permanent uit met behulp van de vervolgkeuzelijst naast opties
- Maar er is een gebruik voor GetPivotData
- Je manager wil een rapport met Actuals voor de afgelopen maanden en budget voor de toekomst
- Bij de normale workflow maakt u een draaitabel, converteert u naar waarden en verwijdert u kolommen
- Subtotalen verwijderen om te voorkomen dat januari Actual + Plan wordt gebruikt met behulp van veldinstellingen
- Maak in plaats daarvan een draaitabel met "te veel" gegevens
- Gebruik een mooi opgemaakt rapportwerkblad
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Bouw een formule met de muis vanuit de eerste gegevenscel op het werkblad
- Laat GetPivotData gebeuren
- Bestudeer de syntaxis van GetPivotData (te retourneren veld, draaiplaats, paren)
- Wijzig de hardgecodeerde waarde zodat deze naar een cel wijst
- Als u driemaal op F4 drukt, wordt alleen de kolom vergrendeld
- Als u tweemaal op F4 drukt, wordt alleen de rij vergrendeld
- Plak speciale formules
- Workflow volgende maand: gegevens toevoegen, draaitabel vernieuwen, datum wijzigen
- Wees uiterst voorzichtig om op nieuwe winkels te letten
Transcript van de video
Leer Excel van podcast, aflevering 2013 - GetPivotData is misschien niet helemaal slecht!
Ik ga dit hele boek podcasten, klik op die "i" in de rechterbovenhoek om je te abonneren.
Oké, in aflevering 1998 had ik het kort over dit GetPivotData-probleem. Als we een% variantie berekenen en we zijn buiten de draaitabel die naar binnen wijst, en ik gebruik de muis of de pijltjestoets, dus 2019 / 2018-1. Dit antwoord dat we hier gaan krijgen, is correct voor januari, maar als we dubbelklikken om dat naar beneden te kopiëren, wordt de formule niet gekopieerd, we krijgen het januari-antwoord helemaal naar beneden. En als we ernaar kijken, krijgen we GetPivotData, ik heb GetPivotData niet getypt, ik heb alleen naar die cellen gewezen, en dit begon in Excel 2002 te gebeuren zonder enige waarschuwing. En op dat moment zei ik dat de manier om dit te vermijden is door de formule C5 / B5-1 in te typen, en je krijgt een formule die je kunt kopiëren. Of als je gewoon een hekel hebt aan GetPivotData, als het "volledig slecht" is, ga dan naar het tabblad Analyseren, don 't open trouwens de knop Opties. Ga terug naar de draaitabel, ga naar het tabblad Analyseren, open de vervolgkeuzelijst naast Opties, schakel dit selectievakje uit, het is een algemene instelling. Als je het eenmaal hebt uitgeschakeld, is het voor altijd uitgeschakeld, oké.
Meestal zijn de vragen die ik krijg: 'Hoe schakel ik GetPivotData uit?' maar af en toe krijg ik iemand die van GetPivotData houdt. En ik was aan het lunchen met Rob Collie toen hij nog bij Microsoft werkte, en hij zei: "Nou, onze interne klanten zijn dol op GetPivotData." Wat zei ik? Nee, iedereen heeft een hekel aan GetPivotData! " Rob zegt: "Je hebt gelijk, buiten Microsoft hebben ze absoluut een hekel aan GetPivotData." Ik heb het over de accountants binnen Microsoft, en later ontmoette ik er een die nu voor het Excel-team werkt, Carlos, en Carlos was een van de accountants die deze methode toepasten.
Oké, dus hier is wat we moeten doen. We hebben ons rapport, een dataset hier dat we voor elke maand het plan voor elke winkel hebben, en onderaan verzamelen we werkelijke cijfers. Oké, dus we hebben werkelijke waarden voor januari tot en met december, maar we hebben slechts werkelijke waarden voor een paar maanden, de maanden die voorbij zijn. En wat onze manager wil dat we doen, is een rapport opstellen met winkels aan de linkerkant, alleen de winkels in Texas natuurlijk om het leven moeilijker te maken. En als we er dan overheen gaan, hebben we maanden, en als we een actueel voor die maand hebben, tonen we de feitelijke, dus feitelijke januari, werkelijke februari, werkelijke maart, werkelijke april. Maar voor de maanden waarin we geen werkelijke cijfers hebben, schakelen we over en laten we het budget zien, dus budget tot december, en dan een totaal van alles, oké. Nou, als je deze draaitabel probeert te maken, ja,het werkt niet.
Dus voeg draaitabel, nieuw werkblad in, je plaatst Store aan de linkerkant, de mooie kant, zet maanden bovenaan, zet Type bovenaan, zet Verkoop hier, oké. Dus hier is wat we krijgen waarmee we moeten beginnen te werken, dus we hebben het feitelijke januari-plan voor januari en dan het volkomen nutteloze feitelijke plus-plan voor januari. Niemand zal dit ooit gebruiken, maar ik kan deze grijze kolommen verwijderen, dat is eenvoudig genoeg, sommige hier naar deze cel, ga naar Veldinstellingen en verander de subtotalen in Geen. Maar er is absoluut geen manier voor mij om het plan van januari te verwijderen, dat niet ook het plan van april, mei, juni, juli zal verwijderen, oké, er is geen manier om hier vanaf te komen. Dus op dit punt zit ik elke maand vast aan het selecteren van de hele draaitabel, naar Kopiëren en vervolgens Plakken, Waarden plakken. Het is geen draaitabel meer,en dan begin ik handmatig de kolommen te verwijderen die niet in het rapport voorkomen.
Oké, dat is de normale methode, maar de accountants bij Microsoft hebben in januari een extra stap toegevoegd, het duurt 15 minuten, en met deze stap kan deze draaitabel voor altijd leven, toch? Ik noem dit 's werelds lelijkste draaitabel, en de accountants bij Microsoft accepteren dat dit' s werelds lelijkste draaitabel is, maar niemand zal dit rapport ooit zien behalve zij. Wat ze doen, is dat ze hier naar een nieuw blad komen en het rapport opstellen dat hun manager wil. Oké, dus hier zijn de winkels aan de linkerkant, ik heb het zelfs gegroepeerd in Houston, Dallas en andere, het is een mooi opgemaakt rapport. Ik heb de totalen gemarkeerd, je zult zien dat als we wat getallen binnen krijgen, er valuta op de eerste rij staat, maar niet de volgende rijen, lege rijen. Ooh, lege rijen in de draaitabel.En een klein beetje logica hier, waar ik de doorloopdatum in cel P1 kan zetten, en dan heb ik hier een formule die analyseert dat ALS de maand van de doorloopdatum> deze kolom is, en zet dan het woord Werkelijk, anders zeg maar plan, oké. Dus alles wat ik hoef te doen is dit veranderen tot en met datum, en dan het woord Werkelijk omdraaien om te plannen, Oké.
Nu, hier is wat we doen, we gaan onszelf toestaan om GetPivotData'd te worden, toch? Ik weet niet zeker of dat een werkwoord is, maar we gaan Microsoft toestaan om GetPivotData te gebruiken. Dus ik begin een formule te bouwen met een =, ik pak de muis, en ik ga op zoek naar Baybrook in januari! Dus ik ga terug naar 's werelds lelijkste draaitabel, ik vind Baybrook, ik vind januari, ik vind de werkelijke, en ik klik op Enter, en laat ze het met mij doen, oké, daar gaan we, we hebben nu een GetPivotData-formule. Ik herinner me de dag dat ik dit deed, het was alsof, weet je, nadat Rob me had uitgelegd wat ze aan het doen waren, en ik ging terug en probeerde het. Nu ben ik ineens, heel mijn leven, van GetPivotData af, ik heb GetPivotData nooit echt omarmd. Dus wat het is, is dat het eerste item is wat we zoeken, daar 'een veld genaamd Sales, dit is waar de draaitabel begint, en het kan elke cel in de draaitabel zijn, ze gebruiken de linkerbovenhoek.
Oké, dit is een veldnaam 'Store', en dan hebben ze 'Baybrook' hard gecodeerd, dit is een veldnaam 'Month', ze hebben 'January' hard gecodeerd, dit is een veldnaam 'Type', en ze ' heb hard gecodeerd "Actual". Daarom kun je het niet kopiëren, omdat ze de waarden hard hebben gecodeerd. Maar de accountants bij Microsoft, Carlos en zijn collega's beseffen: “Ho, wacht even, we hebben het woord Baybrook hier, we hebben januari hier, we hebben hier Actual. We hoeven deze formule alleen te wijzigen, zodat deze naar de werkelijke cellen in het rapport verwijst in plaats van dat ze hard gecodeerd zijn. " Oké, dus noemen ze dit parametriseren de GetPivotData.
Verwijder het woord Baybrook, kom hierheen en klik op cel D6. Nu moet ik dit vergrendelen op de kolom, oké, dus ik druk drie keer op de F4-toets, en krijg een enkele $ voor de D, oké. Voor de maand januari verwijder ik de hardgecodeerde januari, ik klik op de cel E3, ik druk twee keer op F4 om deze vast te zetten op de rij, E $ 3. Typ Actual, verwijder het woord Actual, klik op E4, nogmaals twee keer F4, oké, en ik krijg een formule die nu die gegevens terughaalt. Ik ga dat kopiëren, en dan Plakken speciaal, kies Formaten, alt = "" ESF, zie dat de F daar onderstreept is, ESF Enter, en dan heb ik dat gedaan, ik herhaal het gewoon met F4, F4 is een herhaling, en F4. Oké, dus nu hebben we een mooi uitziend rapport, het heeft lege plekken, het heeft opmaak, het heeft de enkele boekhoudkundige onderstreping onder elke sectie,helemaal onderaan heeft het de dubbele boekhoudkundige onderstreping.
Juist, je krijgt dit spul nooit in een draaitabel, dat is onmogelijk, maar dit rapport wordt gestuurd vanuit de draaitabel. Dus wat we doen als we de werkelijke waarden van mei krijgen, kom hier terug, plak ze in, vernieuw 's werelds lelijkste draaitabel en verander dan hier in het rapport de doorloopdatum van 30-4 in 31-5. En wat dat doet, is dat deze formule overschakelt van het woord Plan naar Actual, dat de werkelijke waarden uit het rapport haalt in plaats van het plan, oké. Nu, hier is het ding dat … dit is geweldig, toch? Ik kan zien waar ik dit vaak zou doen als ik nog steeds, weet je, in de boekhouding zou werken.
Waar je echt voorzichtig mee moet zijn, is dat als ze een nieuwe winkel bouwen, je moet weten dat je het handmatig moet toevoegen, juist, de gegevens zullen in de draaitabel verschijnen, maar je zou het handmatig toevoegen. Deze is een subset van alle winkels. Als hij alle winkels zou rapporteren, zou ik hier waarschijnlijk, buiten het afdrukbereik, iets hebben dat het totaal van de draaitabel heeft gehaald. En dan zou ik weten dat als dit totaal niet overeenkomt met het eindtotaal van de draaitabel, dat er iets mis is, en een ALS-functie hier beneden hebben. Zeggen: “Hé, er zijn, weet je, nieuwe gegevens die zijn toegevoegd, wees heel voorzichtig. " Ze hebben een soort mechanisme om te detecteren dat er nieuwe gegevens zijn. Maar ik snap het, het is een cool gebruik. Dus hoewel GetPivotData ons meestal gek maakt, kan er echt een nut voor zijn. Oké,dus dat is tip # 21 van de 40 in het boek, koop het boek nu, bestel online, klik op die "i" in de rechterbovenhoek.
Lange, lange samenvatting vandaag, oké: GetPivotData vindt plaats wanneer een formule binnen een draaitabel wijst, een formule buiten de draaitabel naar binnen wijst. Hoewel de oorspronkelijke formule correct is, wordt deze niet gekopieerd. De meeste mensen haten GetPivotData en willen dit voorkomen. Dus je kunt een formule bouwen zonder de muis of de pijltjestoetsen, typ gewoon de formule of schakel GetPivotData permanent uit, maar er is een nut, oké. We moeten dus een rapport maken met de werkelijke cijfers voor de afgelopen maand, budget voor de toekomst. Normale workflow, maak een draaitabel, converteer naar waarden, verwijder kolommen. Er is een manier om de subtotalen te verwijderen met behulp van Veldinstellingen, waardoor het feitelijke plusplan van januari wordt verwijderd. In plaats daarvan gaan we gewoon 's werelds lelijkste draaitabel maken met te veel gegevens.
Bouw een mooi opgemaakt, gewoon oud rapportwerkblad met misschien een beetje logica om het woord Werkelijk in Plan te veranderen. En dan vanuit de eerste rapportcel, de eerste plaats waar getallen in dat rapport zullen komen, typ an =, ga naar de draaitabel en laat GetPivotData gebeuren. We onderzoeken de syntaxis van GetPivotData, dus het is het veld dat moet worden geretourneerd, Verkoop, waar de draaitabel staat, en vervolgens paren criteria, de veldnaam en de waarde. We gaan de hardgecodeerde waarde verwijderen en naar een cel wijzen, door driemaal op F4 te drukken wordt alleen de kolom vergrendeld, door tweemaal op F4 te drukken wordt alleen de rij vergrendeld, kopiëren we die formule, Speciale formules plakken. Ik gooide daar een extra tip in dat F4 een herhaling is, dus ik hoefde maar één keer naar het dialoogvenster Plakken speciaal te gaan, en dan voor de volgende Speciale formules plakken gebruikte ik zojuist F4. Voeg volgende maand de gegevens toe,vernieuw de draaitabel, verander de tot en met datum. Zorg ervoor dat ze geen nieuwe winkels hebben gebouwd, weet je, een soort mechanisme hebben, ofwel handmatig, of een controleformule, bekijk het eens. Met dank aan iTrainerMX op Twitter, die GetPivotData voorstelde, ook Carlos en Rob van Microsoft, en nu Rob van Power Pivot Pro. Carlos omdat hij dit gebruikte, en Rob omdat hij me vertelde dat Carlos het gebruikte, ik ontmoette Carlos later, en hij bevestigde ja, hij was een van de accountants die dit de hele tijd bij Microsoft gebruikte, oké, alsjeblieft.en Rob omdat hij me vertelde dat Carlos het gebruikte, ik ontmoette Carlos later, en hij bevestigde ja, hij was een van de accountants die dit de hele tijd bij Microsoft gebruikte, oké, alsjeblieft.en Rob omdat hij me vertelde dat Carlos het gebruikte, ik ontmoette Carlos later, en hij bevestigde ja, hij was een van de accountants die dit de hele tijd bij Microsoft gebruikte, oké, alsjeblieft.
Nou hey, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2013.xlsx
