Formatez automatiquement les données Excel avec la nouvelle fonction PIVOTBY
Lorsque vous travaillez avec des feuilles de calcul complexes, il devient difficile de suivre et d’analyser manuellement toutes les valeurs. Au lieu de créer un tableau croisé dynamique traditionnel et de suivre plusieurs étapes, la nouvelle fonction PIVOTBY d'Excel fournit un moyen simple de réorganiser et de regrouper automatiquement les données en fonction des colonnes que vous spécifiez.
Une fois la formule tapée, le tableau se réorganise pour afficher des valeurs agrégées ou statistiques par catégorie, date ou autre critère, sans aucune autre intervention. Dans ce guide, nous démontrons comment utiliser la fonction avec des exemples pratiques et la comparons aux méthodes de compilation traditionnelles pour illustrer la différence significative en termes de performances et de facilité.
La fonction PIVOTBY dans Excel vous permet d'agréger des nombres sans avoir à recréer vos données dans un tableau croisé dynamique. De plus, les résumés de données générés via PIVOTBY sont automatiquement mis à jour pour refléter les modifications apportées à vos données d'origine, et vous pouvez personnaliser ce qu'ils affichent en modifiant la formule.
ملاحظة
Si vous savez déjà comment utiliser la fonction GROUPBY, apprendre à l'utiliser sera facile. La différence est que GROUPBY regroupe uniquement les variables en lignes, tandis que PIVOTBY dispose d'un argument supplémentaire qui vous permet également de regrouper des variables spécifiques en colonnes.
Structure PIVOTBY
La fonction PIVOTBY vous permet de saisir un total de 11 arguments. Même si cela peut sembler écrasant, l’avantage d’avoir autant de médias est que vous pouvez personnaliser le résultat pour qu’il corresponde à ce que vous souhaitez que les données montrent.
Cependant, seuls les quatre premiers champs sont obligatoires.
Champs obligatoires
Les champs PIVOTBY sont utilisés pour spécifier les variables que vous souhaitez afficher dans les lignes et les colonnes, où trouver les valeurs et comment regrouper vos données.
=PIVOTBY(a,b,c,d)
Où
- a sont les cellules qui contiennent la ou les catégories que vous souhaitez voir apparaître comme en-têtes de ligne en bas à gauche de votre résultat,
- b sont les cellules qui contiennent la ou les catégories que vous souhaitez voir apparaître comme en-têtes de colonne en haut de votre résultat,
- c sont les valeurs qui apparaîtront au milieu de votre résultat, selon les lignes et les colonnes que vous avez spécifiées pour les arguments a et b, et d est la fonction Excel (ou celle que vous créez vous-même à l'aide de LAMBDA) qui détermine comment regrouper les données.
ملاحظة
Bien que les arguments a et b soient nécessaires pour que PIVOTBY produise le type de résultat pour lequel il a été créé, vous pouvez les omettre pour regrouper les données en lignes ou en colonnes uniquement.
Champs facultatifs
Les champs PIVOTBY facultatifs vous permettent d'inclure des en-têtes et des totaux, de trier vos données et de spécifier certains paramètres supplémentaires, dans des circonstances très spécifiques :
=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])
| Le coefficient | الوصف | ملاحظات |
|---|---|---|
| e | Un nombre qui spécifie si les opérandes a, b et c ont des en-têtes de colonne et si vous souhaitez qu'ils soient affichés dans le résultat. | 0 = Aucun en-tête de colonne (par défaut) 1 = Contient des en-têtes, mais n'est pas affiché 2 = Pas d'en-têtes, mais ils sont générés automatiquement 3 = Contient des en-têtes et est affiché |
| f | Un nombre qui spécifie si l'opérande a contient des sommes et des sous-totaux. | 0 = Aucun total 1 = Groupes principaux ci-dessous (par défaut) 2 = Totaux principaux et sous-totaux ci-dessous -1 = Groupes majeurs ci-dessus -2 = Totaux majeurs et sous-totaux ci-dessus |
| g | Un nombre qui spécifie comment les colonnes sont disposées. | Le numéro fait référence aux colonnes du paramètre a, suivies de la colonne du paramètre c. Par exemple, si vous avez deux colonnes dans le paramètre a, le nombre « 2 » signifie le tri par la deuxième colonne et « 3 » signifie le tri par le paramètre c. Un nombre positif signifie un ordre croissant ou alphabétique, et un nombre négatif signifie le contraire. |
| h | Un nombre qui spécifie si l'opérande b contient des sommes et des sous-totaux. | 0 = Aucun total 1 = Grosses sommes à droite (par défaut) 2 = Totaux principaux et sous-totaux à droite -1 = groupes majeurs à gauche -2 = totaux majeurs et sous-totaux à gauche |
| i | Un nombre qui spécifie comment les lignes sont disposées. | Le numéro fait référence aux colonnes du paramètre b, suivies de la colonne du paramètre c. Cela fonctionne de la même manière que le facteur g. |
| j | Une expression logique ou une formule utilisée pour filtrer les lignes contenant des valeurs ou du texte spécifiques. | غير متوفر |
| k | Un nombre qui vous donne un contrôle supplémentaire sur la manière dont les calculs sont effectués lors de l'utilisation de la fonction PERCENTOF dans le paramètre d. | 0 = Sommes des colonnes (par défaut) 1 = Sommes des lignes 2 = Total 3 = Somme de la colonne parent 4 = Total de la classe du père |
PIVOTBY : utilisez uniquement les supports requis
Tout d’abord, laissez-moi vous montrer comment utiliser la fonction PIVOTBY dans sa forme la plus simple, avec seulement les quatre champs obligatoires.
Disons que cette feuille de calcul vous est donnée et nommée Sports_Viewers. Le tableau présente les chiffres d'audience en direct (colonne D) pour six sports (colonne B) dans quatre régions (colonne C) sur quatre ans (colonne A), et il vous est demandé de créer un résumé du nombre total de téléspectateurs par sport et par année.
Vous pouvez utiliser la fonction GROUPBY pour ce faire, bien que vous deviez utiliser des arguments facultatifs pour produire un résultat plus facile à analyser. De plus, comme GROUPBY trie uniquement les données en lignes, vous risquez de vous retrouver avec une longue liste d'informations difficiles à interpréter.
Au lieu de cela, l'utilisation de la fonction PIVOTBY vous permet de prendre une ou plusieurs variables et de les placer dans des colonnes, vous donnant une image plus claire de la façon dont les nombres s'empilent.
ملاحظة
Étant donné que la fonction PIVOTBY produit un tableau déroulant dynamique, vous devez écrire sa formule dans une zone de la feuille de calcul qui n'est pas formatée comme un tableau Excel.
Pour ce faire, dans la cellule F1, tapez
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Année],Sports_Viewers[Téléspectateurs],SOMME)
Où
- Sports_Viewers[Sport] est la colonne nommée dans la table nommée, qui représente les six sports différents. Ce seront les en-têtes de ligne en bas à gauche du résultat.
- Sports_Viewers[Year] est la colonne de l'année dans le tableau, et ce seront les en-têtes de colonne au-dessus du résultat.
- Sports_Viewers[Téléspectateurs] est la colonne qui montre combien de personnes ont regardé ces sports au cours de ces années, donc ces données constitueront la partie principale du résultat.
SUM indique à Excel que vous souhaitez que les colonnes totales additionnent les nombres. En revanche, si vous utilisez par exemple la moyenne, le résultat vous montrera la moyenne de ces nombres.
conseil
Au lieu de saisir manuellement les noms des tables et des colonnes dans la formule (également appelées références structurées), si vous sélectionnez des cellules de table avec la souris, Excel les saisira pour vous.
Grâce à la façon dont PIVOTBY visualise et organise les données, vous pouvez extraire des informations directement à partir du résultat. Par exemple, vous remarquerez peut-être qu’il n’existe aucune donnée pour certains sports au cours de certaines années. De plus, si les champs facultatifs ne sont pas présents, Excel ajoute automatiquement les totaux aux lignes et colonnes de résultats PIVOTBY, ce qui signifie que vous pouvez voir instantanément le nombre total de vues pour chaque année et chaque sport.
Sans utiliser d'arguments facultatifs, Excel trie également le résultat par ordre alphabétique ou croissant par l'argument « A » et n'inclut pas les en-têtes de colonne pour l'argument « A ».
Supposons maintenant que vous souhaitiez inclure une colonne qui divise chaque sport en quatre régions différentes. Pour ce faire, vous devrez inclure à la fois le sport et la région dans l’argument « A » :
=PIVOTBY(Sports_Téléspectateurs[[Sport]:[Région]],Sports_Viewers[Année],Sports_Viewers[Téléspectateurs],SOMME)
conseil
Pour regrouper des colonnes non adjacentes dans un argument, utilisez la fonction CHOOSECOLS.
Ce résultat fournit une analyse plus détaillée de vos données en affichant le nombre total de vues de chaque sport par région. Cependant, comme une variable supplémentaire est ajoutée à l'argument a, pour rendre le résultat plus clair, vous pouvez entrer des arguments facultatifs.
PIVOTBY : utiliser un support optionnel
Lorsque vous créez une formule dans Excel, chaque argument est séparé par une virgule. Cela signifie que si vous souhaitez ignorer l'un des arguments facultatifs, tapez simplement une première virgule pour ouvrir l'argument, suivie d'une deuxième virgule pour le fermer. L'absence de quoi que ce soit entre ces virgules indique que vous avez intentionnellement omis cet argument, le programme souhaite donc utiliser le paramètre par défaut.
Exemple 1 : Totaux et sous-totaux
Une façon d’illustrer le résultat PIVOTBY dans l’exemple précédent est d’ajouter les lignes de sous-total pour chaque sport.
Pour ce faire, tapez :
=PIVOTBY(Sports_Viewers[[Sport]:[Région]],Sports_Viewers[Année],Sports_Viewers[Téléspectateurs],SOMME, 2)
Remarquez comment les deux virgules après l'argument d (SOMME) indiquent que vous souhaitez ignorer l'argument e (faces), mais inclure l'argument f (dans ce cas, le 2 signifie que vous souhaitez inclure un sous-total au bas de chaque sport, ainsi que les totaux généraux au bas du score global).
ملاحظة
Pour clarifier davantage les données, j’ai utilisé une mise en forme conditionnelle pour coder en couleur toutes les lignes du résultat où la colonne F n’est pas vide, la colonne G est vide et la colonne F ne contient pas l’expression « Total général ». L'application d'une mise en forme directe aux tableaux clairsemés n'est pas recommandée, car la mise en forme est liée aux cellules et non aux données. Cela signifie que si les données changent, le format ne changera pas avec elles.
Exemple 2 : Ordre de tri
Voyons maintenant une autre façon de contrôler votre résultat : trier par l’une des colonnes de sortie.
L'écriture:
=PIVOTBY(Sports_Viewers[[Sport]:[Région]],Sports_Viewers[Année],Sports_Viewers[Téléspectateurs],SOMME, 2,3)
Cela signifie qu'après avoir ignoré l'argument e, en plus d'inclure les totaux généraux et les sous-totaux (#2 dans l'argument f), vous souhaitez également trier vos données par numéros de vue totaux par ordre croissant (#3 dans l'argument g).
Notez comment le tri par nombre total d'audience trie non seulement les sports en fonction de leur audience totale, mais trie également les sous-catégories régionales en fonction de leur sous-total.
Exemple 3 : Pourcentages
Voici comment afficher votre résultat à l’aide de pourcentages.
L'écriture:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Année],Sports_Viewers[Téléspectateurs],POURCENTAGEDE,,,2,,,,2)
Indiquez à Excel que vous souhaitez que les sports soient des en-têtes de ligne (argument A), que les années soient des en-têtes de colonne (argument B) et que vous souhaitiez que vos données soient regroupées (argument C) sous forme de pourcentages (argument D). Les 2 premiers (l'argument z) représentent l'ordre, qui dans ce cas est par valeurs. Enfin, le deuxième 2 (l'argument k) indique à Excel que les pourcentages doivent être relatifs aux données totales. Les virgules vides indiquent à Excel que vous souhaitez qu'il utilise le paramètre par défaut pour les arguments e, f, h, i et y.
Sélectionnez maintenant les cellules qui contiennent les résultats décimaux et cliquez sur l’icône Style de pourcentage dans le groupe Nombre de l’onglet Accueil du ruban. Pendant ce temps, cliquez sur les boutons « Augmenter la décimale » et « Diminuer la décimale » pour spécifier le nombre de décimales.
conseil
Spécifiez des lignes supplémentaires sous le résultat au cas où vous ajouteriez plus de données à la table d'origine à l'avenir.
Enfin, prenez un moment pour assimiler ce que ce résultat vous dit.
Par exemple, l’audience du baseball en 2022 représente 11,5 % du total des audiences de tous les sports au fil des ans. Étant donné que les données sont triées par ordre croissant dans la colonne Total, vous pouvez constater que près d'un quart (23,7 %) du nombre total de téléspectateurs provient des téléspectateurs de basket-ball.
En plus d'utiliser SOMME, MOYENNE et POURCENTAGE DE pour la fonction PIVOTBY, vous pouvez également utiliser d'autres fonctions d'agrégation, telles que COUNT, MIN et MAX.
La fonction PIVOTBY offre une solution intelligente pour tous ceux qui organisent leurs données manuellement ou utilisent des tableaux croisés dynamiques traditionnels.
Grâce à cette fonction, vous pouvez simplifier vos analyses et accélérer considérablement la préparation de vos rapports quotidiens ou mensuels.
Il vous suffit de créer la formule une fois et les données seront automatiquement restructurées en fonction des catégories que vous spécifiez.
Essayez-le sur votre prochain fichier et constatez une différence immédiate dans la façon dont vous visualisez et analysez les données.