La plupart des gens rencontrent d'abord GETPIVOTDATA lorsqu'ils essaient de créer une formule en dehors d'un tableau croisé dynamique qui utilise des nombres dans le tableau croisé dynamique. Par exemple, ce pourcentage de variance ne sera pas copié vers les autres mois en raison d'Excel insérant les fonctions GETPIVOTDATA.
Excel insère GETPIVOTDATA chaque fois que vous utilisez la souris ou les touches fléchées pour pointer vers une cellule à l'intérieur du tableau croisé dynamique lors de la création d'une formule en dehors du tableau croisé dynamique.
En passant, si vous ne voulez pas que la fonction GETPIVOTDATA apparaisse, tapez simplement une formule telle que =D5/C5-1sans utiliser la souris ou les touches fléchées pour pointer vers les cellules. Cette formule se copie sans aucun problème.
Voici un ensemble de données contenant un numéro de forfait par mois et par magasin. Il y a aussi des ventes réelles par mois et par magasin pour les mois qui sont terminés. Votre objectif est de créer un rapport qui montre les chiffres réels pour les mois terminés et le plan pour les mois à venir.
Créez un tableau croisé dynamique avec Store in Rows. Mettez le mois et tapez dans les colonnes. Vous obtenez le rapport ci-dessous, avec le plan réel de janvier, le plan de janvier et le plan réel + de janvier complètement absurde.
Si vous sélectionnez une cellule de mois et accédez aux paramètres de champ, vous pouvez modifier les sous-totaux sur Aucun.
Cela supprime le plan Réel + inutile. Mais vous devez toujours vous débarrasser des colonnes de plan pour janvier à avril. Il n'y a pas de bon moyen de le faire dans le tableau croisé dynamique.
Ainsi, votre flux de travail mensuel devient:
- Ajoutez les chiffres réels du nouveau mois à l'ensemble de données.
- Créez un nouveau tableau croisé dynamique à partir de zéro.
- Copiez le tableau croisé dynamique et collez-le en tant que valeurs afin qu'il ne soit plus un tableau croisé dynamique.
- Supprimez les colonnes dont vous n'avez pas besoin.
Il y a une meilleure façon de procéder. La figure très compressée suivante montre une nouvelle feuille de calcul Excel ajoutée au classeur. Tout cela est juste Excel, pas de tableaux croisés dynamiques. Le seul peu de magie est une fonction IF de la ligne 4 qui bascule de Réel à Plan, en fonction de la date de la cellule P1.
La toute première cellule à remplir est celle de janvier pour Baybrook. Cliquez dans cette cellule et tapez un signe égal.
À l'aide de la souris, revenez au tableau croisé dynamique. Recherchez la cellule du réel janvier pour Baybrook. Cliquez sur cette cellule et appuyez sur Entrée. Comme d'habitude, Excel crée l'une de ces fonctions GETPIVOTDATA ennuyeuses qui ne peuvent pas être copiées.
Mais aujourd'hui, étudions la syntaxe de GETPIVOTDATA.
Le premier argument ci-dessous est le champ numérique "Ventes". Le deuxième argument est la cellule dans laquelle réside le tableau croisé dynamique. Les paires d'arguments restantes sont le nom et la valeur du champ. Voyez-vous ce que la formule générée automatiquement a fait? Il a codé en dur "Baybrook" comme nom du magasin. C'est pourquoi vous ne pouvez pas copier ces formules GETPIVOTDATA générées automatiquement. Ils codent en fait des noms en dur dans des formules. Même si vous ne pouvez pas copier ces formules, vous pouvez les modifier. Dans ce cas, il serait préférable que vous modifiiez la formule pour qu'elle pointe vers la cellule $ D6.
La figure ci-dessous montre la formule après l'avoir modifiée. Finis "Baybrook", "Jan" et "Actual". Au lieu de cela, vous pointez vers $ D6, E $ 3 et E $ 4.
Copiez cette formule, puis choisissez Collage spécial, formules dans toutes les autres cellules numériques.
Voici maintenant votre flux de travail mensuel:
- Construisez un tableau croisé dynamique que personne ne verra jamais.
- Configurez la feuille de calcul du rapport.
Chaque mois, vous devez:
- Collez les nouveaux chiffres réels sous les données.
- Actualisez le vilain tableau croisé dynamique.
-
Modifiez la cellule P1 de la feuille de rapport pour refléter le nouveau mois. Tous les numéros sont mis à jour.
Vous devez admettre que l'utilisation d'un rapport qui extrait des chiffres d'un tableau croisé dynamique vous donne le meilleur des deux mondes. Vous êtes libre de mettre en forme le rapport de manière à ne pas mettre en forme un tableau croisé dynamique. Les lignes vides sont bien. Vous pouvez avoir des symboles monétaires sur la première et la dernière ligne, mais pas entre les deux. Vous obtenez également des soulignements doubles sous les totaux généraux.
Merci à @iTrainerMX d'avoir suggéré cette fonctionnalité.
