5 votes

Référence à plusieurs champs et éléments à partir d'une seule cellule - GETPIVOTDATA

J'ai déjà posé cette question sur StackOverflow, mais je me rends compte qu'elle est peut-être plus appropriée ici.

Est-il possible de référencer plusieurs champs et éléments d'une même cellule dans une formule GETPIVOTDATA ?

J'ai un tableau de bord qui affiche les données d'un tableau croisé dynamique en fonction d'un certain nombre de contrôles utilisateur. En fonction de ce qui a été sélectionné, je peux avoir besoin d'utiliser un seul champ et élément ou plusieurs pour référencer les bonnes données. Je suis capable de créer une chaîne de champs et d'éléments requis dans une cellule, mais je ne parviens pas à l'insérer dans une formule GETPIVOTDATA. Celle-ci semble traiter la cellule comme un élément unique entre guillemets. Existe-t-il un moyen de contourner ce problème ?

Example code:
=GetPivotData("Time", PTSchedule, $A$1)
Cell $A$1 contains "ID", $A$5, "Team", "Team 1"

L'idéal serait d'évaluer

=GetPivotData("Time", PTSchedule, "ID", $A$5, "Team", "Team 1")

Cependant, il semble évaluer à

=GetPivotData("Time", PTSchedule, ""ID", $A$5, "Team", "Team 1"")

Des idées ?

Les données que je traite comportent plusieurs niveaux de hiérarchie : Manager > Equipe > Agent En fonction des choix de l'utilisateur, je veux récupérer les données à l'un ou l'autre de ces niveaux. Cela signifie que l'instruction GETPIVOTDATA peut avoir besoin de 1 à 3 paires champ/élément pour identifier les bonnes informations.

Je suppose que je pourrais utiliser trois GETPIVOTDATA et les entourer d'une fonction CHOOSE :

=CHOOSE(Selection, GETPIVOTDATA("Sales", PTSALES, "Manager", Manager),
                   GETPIVOTDATA("Sales", PTSALES, "Manager", Manager, "Team", Team),
                   GETPIVOTDATA("Sales", PTSALES, "Manager", Manager, "Team", Team, "Agent", Agent))

Cependant, j'espérais une solution plus soignée qui permettrait de déplacer toutes les valeurs " variables " en un seul endroit où elles seraient plus faciles à gérer, plutôt que de les répartir dans chaque cellule où je tire des données du tableau croisé dynamique.

4voto

Ruut Points 421

La documentation officielle d'Excel indique la syntaxe de GETPIVOTDATA est :

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

Toutefois, il existe une autre façon, plus souple, d'utiliser l'outil d'évaluation de la qualité de l'eau. GETPIVOTDATA qui n'est pas documenté :

GETPIVOTDATA(pivot_table,"'Sum of " & data_field & "' '" & item1 & 
     "' '" & item2 & "' '" & ... & "'")

Sum of pourrait être remplacé par d'autres types d'agrégations.

La solution à votre problème est donc :

GETPIVOTDATA(
  PTSALES,
  "'Sum of Sales' " &
  "'" & Manager & "' " &
  if(len(Team>0),"'" & Team & "' ","") &
  if(len(Agent>0),"'" & Agent & '","")
)

Et assurez-vous que la gamme nommée Team y Agent sont vides au cas où vous voudriez avoir à les agréger. Pour plus de clarté, vous pouvez déplacer les instructions if dans une cellule séparée.

1voto

AdamV Points 5934

Non.

Mais puisque vous avez "un certain nombre de contrôles utilisateur", pourquoi ne pas simplement construire votre GETPIVOTDATA directement à partir de leur sortie, par exemple :

=GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7)

Ainsi, pour chaque contrôle, placez la sortie dans sa propre cellule (en vérifiant bien sûr les erreurs en cours de route) et utilisez-les dans votre formule.

(A propos : pour des raisons de robustesse, j'utiliserais toujours une cellule pour obtenir l'étiquette du champ plutôt que de la coder en dur, par exemple en faisant référence à l'en-tête de la colonne d'origine. De cette façon, si quelqu'un renomme "Team" en "Group" ou "ID" en "SSN" dans la table source originale et donc dans le PT, votre formule ne sera pas cassée).

Malheureusement, il n'existe pas de moyen simple d'ignorer un élément vide, par exemple si l'ID n'est pas spécifié, mais vous pouvez bien sûr intégrer le tout dans une instruction IF pour vérifier ce point :

=IF($A$5="",GETPIVOTDATA("Time", PTSchedule, "Team", $B$7),GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7))

Cela ne fonctionnera que si la hiérarchie de votre PT est Team > Id, et que Team a des sous-totaux visibles (GetPivotData ne renverra que les valeurs déjà calculées et affichées dans le PT).

Ou simplement dire à l'utilisateur qu'il y a un problème s'il ne remplit pas tout :

=IFERROR(GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7),"Please choose all parameters")

SistemesEz.com

SystemesEZ est une communauté de sysadmins où vous pouvez résoudre vos problèmes et vos doutes. Vous pouvez consulter les questions des autres sysadmins, poser vos propres questions ou résoudre celles des autres.

Powered by:

X