Editions ENI Livres | Vidéos | e-Formations
Les fonctions de calcul comme alternative aux TCD
Introduction
Dans ce chapitre, nous allons explorer deux fonctions récentes d’Excel : GROUPER.PAR et PIVOTER.PAR.
Ces fonctions permettent de simuler les capacités des tableaux croisés dynamiques tout en offrant une alternative basée sur des formules.
Les fonctions GROUPER.PAR et PIVOTER.PAR font partie de la famille de fonctions de type matricielle dynamique (ou Dynamic Arrays en anglais)
Il s’agit d’un type formule qui renvoie non pas une seule valeur, mais un tableau de valeurs. Ce tableau s’adapte automatiquement à la taille des données, ce qui signifie qu’il n’est plus nécessaire de copier-coller des formules ou de redimensionner manuellement vos tableaux.
La fonction GROUPER.PAR
GROUPER.PAR est une fonction qui permet de faire des agrégations de données sans utiliser de tableaux croisés dynamiques. Avec GROUPER.PAR, vous pouvez regrouper des données selon une ou plusieurs colonnes et faire des calculs d’agrégation comme la somme, la moyenne, la médiane, etc.
La syntaxe de la fonction GROUPER.PAR est la suivante :
=GROUPER.PAR (row_fields; values; function; [field_headers]; [total_treatment]; [sort_order]; [filter_array])
-
row_fields : les colonnes à utiliser pour regrouper les lignes.
-
values : les valeurs à agréger.
-
function : la fonction d’agrégation à appliquer (SUM, AVERAGE, COUNT, etc.).
-
field_headers (optionnel) : les en-têtes des champs résultants.
-
total_treatment (optionnel) : la profondeur de regroupement.
-
sort_order (optionnel) : l’ordre de tri des résultats.
-
filter_array (optionnel) : les critères de filtrage des données.
Pour illustrer cette fonction, nous utiliserons le jeu de données suivant :
Vous retrouverez ce tableau dans le fichier GROUPER_PIVOTER.xlsx.
Dans un premier temps, nous souhaitons regrouper les ventes par ville.
Dans la feuille GROUPER.PAR, dans la cellule F4, saisissez =GROUPER.PAR.
Premier argument row_fields : il s’agit des lignes regroupées, sélectionnez la colonne contenant les villes...
La fonction PIVOTER.PAR
Cette fonction est similaire à PIVOTER.PAR mais elle permet de créer des regroupements basés sur des colonnes.
La syntaxe de la fonction PIVOTER.PAR est la suivante :
= PIVOTER.PAR (row_fields; col_fields; values; function; [filed_headers]; [row_total_depth]; [col_total_depth]; [col_sort_order]; [filter_array])
Par rapport à la fonction GROUPER.PAR, la fonction PIVOTER.PAR contient l’argument col_fields, c’est-à-dire la valeur à utiliser pour regrouper les colonnes.
Pour cet exemple nous souhaitons créer un rapport des ventes par ville (en ligne) et par produit (en colonne).
Dans la feuille PIVOTER.PAR, dans la cellule F3, saisissez =PIVOTER.PAR(.
Premier argument row_fields : il s’agit des regroupements en ligne, sélectionnez la colonne des villes soit Ventes[[#Tout];[Ville]] puis saisissez ;.
Deuxième argument col_fields : il s’agit des regroupements en colonne, sélectionnez la colonne des produits soit Ventes[[#Tout];[Produit]] puis saisissez ;.
Troisième argument values : sélectionnez la colonne contenant les montants soit Ventes[[#Tout];[Montant]] puis saisissez ;.
Quatrième argument function : pour cet exemple, ce sera la SOMME.
La formule est :
=PIVOTER.PAR(Ventes[[#Tout];[Ville]];Ventes[[#Tout];[Produit]];Ventes[[#Tout];[Montant]];SOMME)
Le résultat est le suivant :
Ce résultat...
Comparaison entre les fonctions GROUPER.PAR, PIVOTER.PAR et les tableaux croisés dynamiques
GROUPER.PAR et PIVOTER.PAR
-
Approche basée sur les formules : les fonctions GROUPER.PAR et PIVOTER.PAR permettent une flexibilité et un contrôle précis grâce à l’utilisation de formules. Les utilisateurs peuvent personnaliser les agrégations et les analyses directement dans les cellules.
-
Résultats dynamiques : les formules GROUPER.PAR et PIVOTER.PAR se mettent automatiquement à jour lorsque les données sources changent, ce qui permet une analyse en temps réel sans besoin de rafraîchir manuellement les tableaux.
Inconvénients :
-
Manque de fonctionnalités interactives : contrairement aux tableaux croisés dynamiques, les fonctions GROUPER.PAR et PIVOTER.PAR ne disposent pas de fonctionnalités interactives telles que le filtrage, le tri ou encore les segments.
-
Complexité des formules : l’utilisation de GROUPER.PAR et PIVOTER.PAR peut nécessiter une compréhension plus approfondie des fonctions et des formules Excel, ce qui peut être intimidant pour les utilisateurs moins expérimentés.
Tableaux croisés dynamiques
-
Facilité d’utilisation : créer des tableaux croisés dynamiques est souvent plus intuitif pour les utilisateurs, grâce à l’interface...
