Utiliser des tableaux croisés dynamiques dans des scripts Office

Article
05/03/2024

Les tableaux croisés dynamiques vous permettent d’analyser rapidement de grandes collections de données. Avec leur puissance vient la complexité. Les API Scripts Office vous permettent de personnaliser un tableau croisé dynamique en fonction de vos besoins, mais l’étendue de l’ensemble d’API complique la prise en main. Cet article explique comment effectuer des tâches courantes de tableau croisé dynamique et explique les classes et méthodes importantes.

Remarque

Pour mieux comprendre le contexte des termes utilisés par les API, lisez d’abord la documentation de tableau croisé dynamique d’Excel. Commencez par Create un tableau croisé dynamique pour analyser les données de feuille de calcul.

Modèle d’objet

Image simplifiée des classes, méthodes et propriétés utilisées lors de l’utilisation de tableaux croisés dynamiques.

Le tableau croisé dynamique est l’objet central des tableaux croisés dynamiques dans l’API Scripts Office.

L’objet Workbook possède une collection de tous les tableaux croisés dynamiques. Chaque feuille de calcul contient également une collection de tableaux croisés dynamiques qui est locale à cette feuille.
Un tableau croisé dynamique contient des hiérarchies de tableau croisé dynamique. Une hiérarchie peut être considérée comme une colonne dans une table.
PivotHierarchies peut être ajouté en tant que lignes ou colonnes (RowColumnPivotHierarchy), données (DataPivotHierarchy) ou filtres (FilterPivotHierarchy).
Chaque PivotHierarchy contient exactement un champ de tableau croisé dynamique. Les structures de tableau croisé dynamique en dehors d’Excel peuvent contenir plusieurs champs par hiérarchie. Cette conception existe donc pour prendre en charge les options futures. Pour les scripts Office, les champs et les hiérarchies sont mappés aux mêmes informations.
Un champ de tableau croisé dynamique contient plusieurs pivotitems. Chaque élément pivot est une valeur unique dans le champ. Considérez chaque élément comme une valeur dans la colonne de table. Les éléments peuvent également être des valeurs agrégées, telles que des sommes, si le champ est utilisé pour les données.
PivotLayout définit le mode d’affichage des champs croisés dynamiques et des éléments croisés dynamiques.
Les filtres croisés dynamiques filtrent les données du tableau croisé dynamique à l’aide de différents critères.

Pour voir comment ces relations fonctionnent dans la pratique, commencez par télécharger l’exemple de classeur. Ces données décrivent les ventes de fruits de diverses exploitations agricoles. Il s’agit de la base de tous les exemples de cet article. Exécutez les exemples de scripts tout au long de l’article pour créer et explorer des tableaux croisés dynamiques.

Télécharger l’exemple de classeur

Une collection de ventes de fruits de différents types provenant de différentes fermes.

Create un tableau croisé dynamique avec des champs

Les tableaux croisés dynamiques sont créés avec des références à des données existantes. Les plages et les tables peuvent être la source d’un tableau croisé dynamique. Ils ont également besoin d’un emplacement pour exister dans le classeur. Étant donné que la taille d’un tableau croisé dynamique est dynamique, seul le coin supérieur gauche de la plage de destination est spécifié.

L’extrait de code suivant crée un tableau croisé dynamique basé sur une plage de données. Le tableau croisé dynamique n’ayant aucune hiérarchie, les données ne sont pas encore regroupées.

  const dataSheet = workbook.getWorksheet("Data");
  const pivotSheet = workbook.getWorksheet("Pivot");

  const farmPivot = pivotSheet.addPivotTable(
    "Farm Pivot", /* The name of the PivotTable. */
    dataSheet.getUsedRange(), /* The source data range. */
    pivotSheet.getRange("A1") /* The location to put the new PivotTable. */);

Un tableau croisé dynamique nommé « Farm Pivot » sans hiérarchie.

Hiérarchies et champs

Les tableaux croisés dynamiques sont organisés via des hiérarchies. Ces hiérarchies sont utilisées pour faire pivoter les données lorsqu’elles sont ajoutées en tant que type spécifique de hiérarchie. Il existe quatre types de hiérarchies.

Ligne : affiche les éléments dans des lignes horizontales.
Colonne : affiche les éléments dans des colonnes verticales.
Données : affiche des agrégats de valeurs en fonction des lignes et des colonnes.
Filtre : ajoute ou supprime des éléments du tableau croisé dynamique.

Un tableau croisé dynamique peut avoir autant ou peu de champs affectés à ces hiérarchies spécifiques. Un tableau croisé dynamique a besoin d’au moins une hiérarchie de données pour afficher les données numériques résumées et d’au moins une ligne ou colonne sur laquelle effectuer un tableau croisé dynamique. L’extrait de code suivant ajoute deux hiérarchies de lignes et deux hiérarchies de données.

  farmPivot.addRowHierarchy(farmPivot.getHierarchy("Farm"));
  farmPivot.addRowHierarchy(farmPivot.getHierarchy("Type"));
  farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold at Farm"));
  farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold Wholesale"));

Tableau croisé dynamique montrant le total des ventes de différents fruits en fonction de la ferme d’où ils proviennent.

Plages de disposition

Chaque partie du tableau croisé dynamique est mappée à une plage. Cela permet à votre script d’obtenir des données à partir du tableau croisé dynamique pour une utilisation ultérieure dans le script ou à retourner dans un flux Power Automate. Ces plages sont accessibles via l’objet PivotLayout acquis à partir de PivotTable.getLayout(). Le diagramme suivant montre les plages retournées par les méthodes dans PivotLayout.

Diagramme montrant quelles sections d’un tableau croisé dynamique sont retournées par les fonctions get range de la disposition.

Sortie totale du tableau croisé dynamique

L’emplacement de la ligne totale est basé sur la disposition. Utilisez PivotLayout.getBodyAndTotalRange et obtenez la dernière ligne de la colonne pour utiliser les données du tableau croisé dynamique dans votre script.

L’exemple suivant recherche le premier tableau croisé dynamique dans le classeur et journalise les valeurs dans les cellules « Total général » (comme mis en évidence en vert dans l’image ci-dessous).

Tableau croisé dynamique montrant les ventes de fruits avec la ligne Total général mise en évidence en vert.

function main(workbook: ExcelScript.Workbook) {
  // Get the first PivotTable in the workbook.
  const pivotTable = workbook.getPivotTables()[0];

  // Get the names of each data column in the PivotTable.
  const pivotColumnLabelRange = pivotTable.getLayout().getColumnLabelRange();

  // Get the range displaying the pivoted data.
  const pivotDataRange = pivotTable.getLayout().getBodyAndTotalRange();

  // Get the range with the "grand totals" for the PivotTable columns.
  const grandTotalRange = pivotDataRange.getLastRow();

  // Print each of the "Grand Totals" to the console.
  grandTotalRange.getValues()[0].forEach((column, columnIndex) => {
    console.log(`Grand total of ${pivotColumnLabelRange.getValues()[0][columnIndex]}: ${grandTotalRange.getValues()[0][columnIndex]}`);
    // Example log: "Grand total of Sum of Crates Sold Wholesale: 11000"
  });
}

Filtres et segments

Il existe trois façons de filtrer un tableau croisé dynamique.

FilterPivotHierarchies

FilterPivotHierarchies ajouter une hiérarchie supplémentaire pour filtrer chaque ligne de données. Toute ligne avec un élément filtré est exclue du tableau croisé dynamique et de ses résumés. Étant donné que ces filtres sont basés sur des éléments, ils fonctionnent uniquement sur des valeurs discrètes. Si « Classification » est une hiérarchie de filtre dans l’exemple, les utilisateurs peuvent sélectionner les valeurs « Organique » et « Conventionnel » pour le filtre. De même, si « Crates Sold Wholesale » est sélectionné, les options de filtre sont les nombres individuels, tels que 120 et 150, au lieu des plages numériques.

FilterPivotHierarchies sont créés avec toutes les valeurs sélectionnées. Cela signifie que rien n’est filtré tant que l’utilisateur n’interagit pas manuellement avec le contrôle de filtre ou qu’un PivotManualFilter n’est pas défini sur le champ appartenant au FilterPivotHierarchy.

L’extrait de code suivant ajoute « Classification » en tant que hiérarchie de filtre.

  farmPivot.addFilterHierarchy(farmPivot.getHierarchy("Classification"));

Contrôle de filtre qui utilise « Classification » pour un tableau croisé dynamique.

PivotFilters

L’objet PivotFilters est une collection de filtres appliqués à un seul champ. Étant donné que chaque hiérarchie a exactement un champ, vous devez toujours utiliser le premier champ dans PivotHierarchy.getFields() lors de l’application de filtres. Il existe quatre types de filtres.

Filtre de date : filtrage basé sur la date du calendrier.
Filtre d’étiquette : filtrage de comparaison de texte.
Filtre manuel : filtrage d’entrée personnalisé.
Filtre de valeur : filtrage de comparaison de nombres. Cela compare les éléments de la hiérarchie associée aux valeurs d’une hiérarchie de données spécifiée.

En règle générale, un seul des quatre types de filtres est créé et appliqué au champ. Si le script tente d’utiliser des filtres incompatibles, une erreur est générée avec le texte « L’argument n’est pas valide ou manquant ou a un format incorrect ».

L’extrait de code suivant ajoute deux filtres. Le premier est un filtre manuel qui sélectionne les éléments d’une hiérarchie de filtre « Classification » existante. Le deuxième filtre supprime toutes les batteries de serveurs qui ont moins de 300 « Caisses vendues en gros ». Notez que cela filtre la « Somme » de ces batteries de serveurs, et non les lignes individuelles des données d’origine.

  const classificationField = farmPivot.getFilterHierarchy("Classification").getFields()[0];
  classificationField.applyFilter({
    manualFilter: { 
      selectedItems: ["Organic"] /* The included items. */
    }
  });

  const farmField = farmPivot.getHierarchy("Farm").getFields()[0];
  farmField.applyFilter({
    valueFilter: {
      condition: ExcelScript.ValueFilterCondition.greaterThan, /* The relationship of the value to the comparator. */
      comparator: 300, /* The value to which items are compared. */
      value: "Sum of Crates Sold Wholesale" /* The name of the data hierarchy. Note the "Sum of" prefix. */
      }
  });

Tableau croisé dynamique après l’application du filtre de valeurs et du filtre manuel.

Slicers

Les segments filtrent les données d’un tableau croisé dynamique (ou d’un tableau standard). Il s’agit d’objets déplaçables dans la feuille de calcul qui permettent de filtrer rapidement les sélections. Un segment fonctionne de la même façon que le filtre manuel et PivotFilterHierarchy. Les éléments du PivotField sont basculés pour les inclure ou les exclure du tableau croisé dynamique.

L’extrait de code suivant ajoute un segment pour le champ « Type ». Il définit les éléments sélectionnés sur « Citron » et « Lime », puis déplace le segment de 400 pixels vers la gauche.

  const fruitSlicer = pivotSheet.addSlicer(
    farmPivot, /* The table or PivotTale to be sliced. */
    farmPivot.getHierarchy("Type").getFields()[0] /* What source to use as the slicer options. */
  );
  fruitSlicer.selectItems(["Lemon", "Lime"]);
  fruitSlicer.setLeft(400);

Segment filtrant des données sur un tableau croisé dynamique.

Paramètres des champs de valeur pour les résumés

Modifiez la façon dont le tableau croisé dynamique résume et affiche les données avec ces paramètres. Le champ de chaque hiérarchie de données peut afficher les données de différentes manières, telles que des pourcentages, des écarts types et des comparaisons relatives.

Résumer par

Le résumé par défaut d’un champ de hiérarchie de données est une somme. DataPivotHierarchy.setSummarizeBy vous permet de combiner les données de chaque ligne ou colonne d’une manière différente. AggregationFunction répertorie toutes les options disponibles.

L’extrait de code suivant modifie « Crates Sold Wholesale » pour afficher l’écart type de chaque élément, au lieu de la somme.

  const wholesaleSales = farmPivot.getDataHierarchy("Sum of Crates Sold Wholesale");
  wholesaleSales.setSummarizeBy(ExcelScript.AggregationFunction.standardDeviation);

Afficher les valeurs en tant que

DataPivotHierarchy.setShowAs applique un calcul aux valeurs d’une hiérarchie de données. Au lieu de la somme par défaut, vous pouvez afficher des valeurs ou des pourcentages par rapport à d’autres parties du tableau croisé dynamique. Utilisez un ShowAsRule pour définir la façon dont les valeurs de la hiérarchie de données sont affichées.

L’extrait de code suivant modifie l’affichage de « Crates Sold at Farm ». Les valeurs sont affichées sous la forme d’un pourcentage du total général pour le champ.

  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  const rule : ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.percentOfGrandTotal
  };
  farmSales.setShowAs(rule);

Certains ShowAsRuleont besoin d’un autre champ ou élément dans ce champ comme comparaison. L’extrait de code suivant modifie à nouveau l’affichage de « Crates Sold at Farm ». Cette fois, le champ affiche la différence de chaque valeur par rapport à la valeur des « Citrons » dans cette ligne de batterie de serveurs. Si une ferme n’a vendu aucun citron, le champ affiche « #N/A ».

  const typeField = farmPivot.getRowHierarchy("Type").getFields()[0];
  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  const rule: ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.differenceFrom,
    baseField: typeField, /* The field to use for the difference. */
    baseItem: typeField.getPivotItem("Lemon") /* The item within that field that is the basis of comparison for the difference. */
  };
  farmSales.setShowAs(rule);
  farmSales.setName("Difference from Lemons of Crates Sold at Farm");

Partager via