Excel est un outil puissant pour la gestion et l’analyse de données. Parmi ses nombreuses fonctionnalités, les fonctions GROUPER.PAR et PIVOTER.PAR offrent des moyens avancés pour synthétiser et organiser les informations. Cet article explore en profondeur ces deux fonctions, leur syntaxe, leurs avantages, et propose des cas pratiques pour optimiser votre utilisation d’Excel.
Introduction aux fonctions GROUPER.PAR et PIVOTER.PAR
Les fonctions GROUPER.PAR et PIVOTER.PAR sont des ajouts récents à Excel, conçus pour faciliter le regroupement et l’agrégation de données directement via des formules, sans recourir aux tableaux croisés dynamiques traditionnels.
- GROUPER.PAR : Permet de regrouper des données selon un ou plusieurs critères et d’appliquer des fonctions d’agrégation telles que SOMME, MOYENNE, NB, etc.
- PIVOTER.PAR : Va plus loin en permettant de créer des résumés de données en utilisant deux axes (lignes et colonnes), similaire à un tableau croisé dynamique, mais avec la flexibilité des formules.
Avantages de l’utilisation de ces fonctions
L’adoption des fonctions GROUPER.PAR et PIVOTER.PAR présente plusieurs bénéfices pour l’analyse de données dans Excel :
- Intégration fluide : Ces fonctions s’intègrent directement dans les formules Excel, permettant des analyses complexes sans passer par l’interface des tableaux croisés dynamiques.
- Mises à jour automatiques : Les résultats se mettent à jour automatiquement lorsque les données sources changent, éliminant le besoin d’actualiser manuellement les tableaux.
- Simplicité et flexibilité : Elles offrent une approche plus simple et flexible pour regrouper et analyser les données, notamment pour les utilisateurs familiarisés avec les formules Excel.
Syntaxe et utilisation de la fonction GROUPER.PAR
La fonction GROUPER.PAR permet de créer un résumé de vos données via une formule. Elle permet de regrouper les données le long d’un axe et d’agréger les valeurs associées.
Syntaxe :
=GROUPER.PAR(row_fields; values; function; [field_headers]; [total_depth]; [sort_order]; [filter_array]; [field_relationship])
Arguments :
- row_fields (obligatoire) : Plage utilisée pour regrouper les données.
- values (obligatoire) : Données numériques à analyser.
- function (obligatoire) : Opération d’agrégation à appliquer, telle que « SOMME », « MOYENNE », « NB », etc.
- field_headers (optionnel) : Indique si les données contiennent des en-têtes.
- total_depth (optionnel) : Spécifie si une ligne de total est souhaitée.
- sort_order (optionnel) : Colonne sur laquelle appliquer le tri.
- filter_array (optionnel) : Permet d’appliquer un filtre sur les données.
- field_relationship (optionnel) : Établit une relation entre plusieurs colonnes lors du tri.
Cas pratiques d’utilisation de GROUPER.PAR
- Calcul du total des ventes par catégorie
Objectif : Déterminer le nombre total d’unités vendues pour chaque catégorie de produits.
Étapes :
- Assurez-vous que vos données incluent une colonne pour les catégories (par exemple, B9:B23) et une colonne pour les unités vendues (F9:F23).
- Utilisez la formule suivante : =GROUPER.PAR(B9:B23; F9:F23; SOMME)
Résultat : Un tableau affichant chaque catégorie avec le total des unités vendues correspondantes.
- Analyse du chiffre d’affaires par région
Objectif : Calculer le chiffre d’affaires total pour chaque région.
Étapes :
- Vos données doivent inclure une colonne pour les régions (H9:H23), une pour le prix unitaire (E9:E23) et une pour les unités vendues (F9:F23).
- Appliquez la formule suivante : =GROUPER.PAR(H9:H23; E9:E23 * F9:F23; SOMME)
Résultat : Un tableau indiquant le chiffre d’affaires total pour chaque région.
- Nombre de ventes par auteur avec filtre
Objectif : Compter le nombre de ventes réalisées par chaque auteur, en filtrant uniquement les ventes de la catégorie « BD ».
Étapes :
- Vos données doivent comporter une colonne pour les auteurs (D9:D23), une pour les catégories (B9:B23) et une pour les unités vendues (F9:F23).
- Utilisez la formule suivante avec un filtre : =GROUPER.PAR(D9:D23; F9:F23; SOMME;;;; B9:B23= »BD »)
Résultat : Un tableau affichant le nombre total d’unités vendues par chaque auteur pour la catégorie « BD ».
Qu’est-ce que la fonction PIVOTER.PAR et pourquoi l’utiliser ?
La fonction PIVOTER.PAR est une nouveauté dans Excel qui permet de créer des tableaux de synthèse directement via une formule. Elle offre une alternative dynamique aux tableaux croisés dynamiques traditionnels en permettant de regrouper, d’agréger, de trier et de filtrer des données selon des champs de ligne et de colonne spécifiés. Contrairement aux tableaux croisés dynamiques, les résultats générés par PIVOTER.PAR se mettent à jour automatiquement lorsque les données sources changent, éliminant ainsi le besoin d’actualisation manuelle. De plus, cette fonction s’intègre aisément avec d’autres formules Excel, offrant ainsi une flexibilité accrue dans l’analyse des données.
Syntaxe de la fonction PIVOTER.PAR
Syntaxe :
=PIVOTER.PAR(row_fields; col_fields; values; function; [field_headers]; [row_total_depth]; [row_sort_order]; [col_total_depth]; [col_sort_order]; [filter_array]; [relative_to])
Arguments :
- row_fields (obligatoire) : Plage ou tableau contenant les valeurs utilisées pour regrouper les lignes et générer les en-têtes de ligne. Cette plage peut contenir plusieurs colonnes pour créer des niveaux de regroupement supplémentaires.
- col_fields (obligatoire) : Plage ou tableau contenant les valeurs utilisées pour regrouper les colonnes et générer les en-têtes de colonne. Comme pour row_fields, plusieurs colonnes peuvent être incluses.
- values (obligatoire) : Plage ou tableau de données numériques à agréger.
- function (obligatoire) : Opération d’agrégation à appliquer, telle que « SOMME », « MOYENNE », « NB », « MIN », « MAX », etc.
- field_headers (optionnel) : Indique si les données contiennent des en-têtes. Par défaut, la fonction détecte automatiquement la présence ou l’absence d’en-têtes.
- row_total_depth (optionnel) : Spécifie le niveau de totalisation pour les lignes.
- row_sort_order (optionnel) : Définit l’ordre de tri des lignes.
- col_total_depth (optionnel) : Spécifie le niveau de totalisation pour les colonnes.
- col_sort_order (optionnel) : Définit l’ordre de tri des colonnes.
- filter_array (optionnel) : Permet d’appliquer un filtre sur les données.
- relative_to (optionnel) : Définit la relation entre plusieurs colonnes lors du tri.
Cas pratiques d’utilisation de la fonction PIVOTER.PAR
- Création d’un tableau de synthèse des ventes par région et par produit
Imaginons que nous disposions d’une base de données contenant des informations sur les ventes, avec les colonnes suivantes : Région, Produit et Ventes. Nous souhaitons obtenir un tableau résumant le total des ventes par région et par produit.
Étapes :
- Sélection des champs en ligne (row_fields) : Il s’agit des regroupements en ligne, ici la colonne des régions.
- Sélection des champs en colonne (col_fields) : Il s’agit des regroupements en colonne, ici la colonne des produits.
- Sélection des valeurs à agréger (values) : La colonne contenant les montants des ventes.
- Choix de la fonction d’agrégation (function) : Ici, nous utilisons « SOMME » pour obtenir le total des ventes.
Formule : =PIVOTER.PAR(A2:A100; B2:B100; C2:C100; SOMME)
Où :
- A2:A100 représente la plage des régions.
- B2:B100 représente la plage des produits.
- C2:C100 représente la plage des montants des ventes.
Cette formule génère un tableau affichant les régions en lignes, les produits en colonnes, et les totaux des ventes correspondants aux intersections.
- Comptage du nombre de ventes par représentant et par trimestre
Supposons que notre base de données inclut également les colonnes Représentant et Date de vente. Nous souhaitons connaître le nombre de ventes réalisées par chaque représentant pour chaque trimestre.
Étapes :
- Extraction du trimestre à partir de la date de vente : Ajouter une colonne calculée pour déterminer le trimestre de chaque date de vente.
- Sélection des champs en ligne (row_fields) : La colonne des représentants.
- Sélection des champs en colonne (col_fields) : La colonne des trimestres.
- Sélection des valeurs à agréger (values) : Une colonne contenant des valeurs constantes (par exemple, une colonne remplie de 1) pour permettre le comptage.
- Choix de la fonction d’agrégation (function) : « NB » pour compter le nombre d’occurrences.
Formule : =PIVOTER.PAR(D2:D100; E2:E100; F2:F100; « NB »)
Où :
- D2:D100 représente la plage des représentants.
- E2:E100 représente la plage des trimestres.
- F2:F100 est une colonne contenant des 1 pour permettre le comptage.
Cette formule produit un tableau indiquant le nombre de ventes effectuées par chaque représentant pour chaque trimestre.
- Calcul de la moyenne des ventes par catégorie de produit et par mois
Si notre base de données comprend les colonnes Catégorie de produit, Date de vente et Montant de la vente, nous pouvons calculer la moyenne des ventes pour chaque catégorie de produit par mois.
Étapes :
- Extraction du mois à partir de la date de vente : Ajouter une colonne calculée pour déterminer le mois de chaque date de vente.
- Sélection des champs en ligne (row_fields) : La colonne des catégories de produits.
- Sélection des champs en colonne (col_fields) : La colonne des mois.
- Sélection des valeurs à agréger (values) : La colonne des montants des ventes.
- Choix de la fonction d’agrégation (function) : « MOYENNE » pour obtenir la moyenne des ventes.
Formule : =PIVOTER.PAR(G2:G100; H2:H100; I2:I100; « MOYENNE »)
Où :
- G2:G100 représente la plage des catégories de produits.
- H2:H100 représente la plage des mois.
- I2:I100 représente la plage des montants des ventes.
Cette formule génère un tableau affichant la moyenne des ventes.
Les fonctions GROUPER.PAR et PIVOTER.PAR d’Excel offrent des solutions puissantes et flexibles pour l’analyse de données sans avoir à recourir aux tableaux croisés dynamiques classiques. Grâce à leur intégration dans les formules, elles permettent de regrouper, agréger et synthétiser des données de manière dynamique et automatique. Ces fonctions sont particulièrement utiles pour les utilisateurs recherchant une approche plus manuelle et personnalisée de l’analyse des données tout en restant dans l’écosystème Excel.
En maîtrisant GROUPER.PAR et PIVOTER.PAR, vous pourrez optimiser vos analyses, réduire la complexité des tâches répétitives et créer des rapports détaillés directement dans vos feuilles de calcul.
Leur flexibilité permet d’adapter les analyses aux besoins spécifiques de votre entreprise, tout en garantissant une mise à jour automatique des résultats en fonction des données sources. Adopter ces outils, c’est gagner en efficacité, précision et réactivité dans la gestion de vos données Excel.
