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.
Trouwens, als u niet wilt dat de GETPIVOTDATA-functie verschijnt, typ dan gewoon een formule, bijvoorbeeld =D5/C5-1zonder 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 in 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 gecomprimeerde 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 wisselt van Werkelijk naar Plan, gebaseerd op de datum in cel P1.
De allereerste cel die ingevuld moet worden, is Januari Actueel voor Baybrook. Klik in die cel en typ een gelijkteken.
Navigeer met de muis terug naar de draaitabel. Zoek de cel voor januari Actual 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.
De onderstaande afbeelding toont de formule nadat u deze hebt bewerkt. Voorbij zijn "Baybrook", "Jan" en "Actual". In plaats daarvan wijst u naar $ D6, E $ 3 en E $ 4.
Kopieer deze formule en kies vervolgens Plakken speciaal, formules in alle andere numerieke cellen.
Dit is nu uw maandelijkse 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.
Je moet toegeven dat het gebruik van een rapport dat getallen 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.
