Comment utiliser la fonction pivotby dans Excel

La fonction PIVOTBY d'Excel vous permet de regrouper vos chiffres sans avoir besoin de recréer vos données dans un tiillonnable. De plus, les résumés de données créés via Pivotby mettent automatiquement à jour pour refléter les modifications de vos données d'origine, et vous pouvez personnaliser ce qu'ils montrent en peaufinant la formule.

Si vous savez déjà comment utiliser la fonction GroupBy, apprendre à utiliser la fonction Pivotby sera simple. La différence est que lorsque GroupBy ne regroupe que les variables en lignes, Pivotby a un argument supplémentaire qui vous permet également de regrouper certaines variables en colonnes.

La syntaxe Pivotby

La fonction Pivotby vous permet de saisir un total de 11 arguments. Bien que cela semble intimidant, l'avantage d'avoir autant d'arguments est que vous pouvez adapter le résultat pour vous aligner sur ce que vous voulez que les données affichent.

Cependant, seuls les quatre premiers champs sont nécessaires.

Champs requis

Les champs Pivotby requis sont l'endroit où vous dites à Excel quelles variables vous souhaitez afficher dans les lignes, quelles variables vous souhaitez afficher dans les colonnes, où trouver les valeurs et comment vous souhaitez agréger vos données:

=PIVOTBY(a,b,c,d)

où

• un Les cellules contenant la catégorie ou les catégories que vous souhaitez apparaître sous forme de têtes de ligne sur le côté gauche de votre résultat,
• b Les cellules contenant la catégorie ou les catégories que vous souhaitez apparaître sous forme de têtes de colonne en haut de votre résultat,
• c sont les valeurs qui apparaîtront au centre de votre résultat, selon les lignes et les colonnes que vous avez sélectionnées pour les arguments un et bet
• d est la fonction Excel (ou la fonction que vous avez créée vous-même via Lambda) qui définit comment les données seront agrégées.

Bien que les arguments un et b sont nécessaires pour que Pivotby produise le type de résultat qu'il a été créé pour produire, vous pouvez les omettre uniquement pour regrouper les données en lignes ou colonnes.

Champs facultatifs

Les champs Pivotby en option vous permettent d'inclure des en-têtes et des totaux, trier vos données et – dans des circonstances très spécifiques – spécifier certains paramètres supplémentaires:

=PIVOTBY(a,b,c,d,(e,f,g,h,i,j,k))

Pivotby: en utilisant uniquement les arguments requis

Tout d'abord, permettez-moi de vous montrer comment utiliser la fonction Pivotby sous sa forme la plus simple avec les quatre champs requis.

Disons que vous avez reçu ce tableau de données nommé Sports_viewers. Il montre les chiffres de visionnage en direct (colonne D) pour six sports (colonne B) dans quatre régions (colonne C) sur quatre ans (colonne A), et on vous a demandé de générer un résumé des téléspectateurs totaux par sport et année.

Toi pourrait Utilisez la fonction GroupBy pour ce faire, bien que vous deviez utiliser des arguments facultatifs pour produire un résultat qui facilite l'analyse. De plus, puisque Groupby ne fait que les données en lignes, vous pourriez 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 vos numéros s'accumulent.

Pour ce faire, dans la cellule F1, type

=PIVOTBY(Sports_Viewers(Sport),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM)

où

• Sports_viewers (Sport) est la colonne nommée du tableau nommé qui représente les six sports différents. Ce seront les en-têtes de rangée sur le côté gauche de votre résultat.
• Sports_viewers (année) représente la colonne de l'année de la table, et ce seront les en-têtes de colonne le long du haut du résultat.
• Sports_viewers (téléspectateurs) La colonne montre le nombre de personnes qui ont regardé ces sports au cours de ces années, donc ces données seront la partie focale du résultat.
• SOMME dit à Excel que vous voulez que les colonnes totales ajoutent les chiffres ensemble. D'un autre côté, si, par exemple, vous utilisez la moyenne, le résultat vous montrera la moyenne de ces chiffres à la place.

Au lieu de taper les noms de table et de colonne dans votre formule (également appelée références structurées) manuellement, si vous sélectionnez les cellules du tableau à l'aide de votre souris, Excel les saisira pour vous.

En raison de la façon dont Pivotby affiche et organise les données, vous pouvez dessiner des informations à partir du résultat tout de suite. Par exemple, vous pouvez voir que les données sont absentes pour certains sports au cours de certaines années. De plus, en l'absence des champs facultatifs, Excel ajoute automatiquement des totaux aux lignes et aux colonnes du résultat Pivotby, ce qui signifie que vous pouvez instantanément voir les chiffres de visualisation totaux par an et par sport.

Sans les arguments facultatifs, Excel trie également le résultat en un ordre alphabétique ou ascendant par argument unet n'inclut pas les en-têtes de colonne pour l'argument un.

Maintenant, disons que vous souhaitez 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 en argument un:

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM)

Pour regrouper les colonnes non adjacentes dans un argument, utilisez la fonction ChooseCols.

Ce résultat fournit un aperçu plus détaillé de vos données en montrant les chiffres de visualisation totale de chaque sport par région. Cependant, parce que vous avez ajouté une variable supplémentaire à l'argument unPour rendre le résultat plus clair, vous pouvez saisir certains arguments facultatifs.

Pivotby: en utilisant les arguments facultatifs

Chaque fois que vous créez une formule Excel, chaque argument est séparé par une virgule. Cela signifie que si vous voulez sauter l'un des arguments facultatifs, vous devez simplement taper une première virgule pour ouvrir l'argument, suivi d'une deuxième virgule pour le fermer. Le fait qu'il n'y ait rien entre ces deux virgules indique à Excel que vous avez délibérément omis cet argument, vous voulez donc que le programme adopte le paramètre par défaut.

Exemple 1: Grands totaux et sous-totaux

Une façon de rendre le Pivotby résulte de l'exemple précédent serait d'ajouter des lignes subtotales pour chaque sport.

Pour ce faire, vous devez taper:

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM,,2)

Remarquez comment, après l'argument d (SOMME), deux virgules indiquent que vous voulez sauter par-dessus l'argument e (en-têtes), mais inclure l'argument f (dans ce cas, 2 signifie que vous voulez inclure un sous-total au bas de chaque sport, ainsi que de grands totaux au bas du résultat global).

Pour rendre les données encore plus claires, j'ai utilisé la mise en forme conditionnelle pour colorer toutes les lignes dans le résultat où la colonne F n'est pas vide, la colonne G est vide et la colonne F ne contient pas les mots «grand total». L'application de la mise en forme directe aux tableaux renversés n'est pas conseillé, car le formatage est attaché aux cellules plutôt qu'aux données. Cela signifie que si les données changent, le formatage ne changera pas avec.

Exemple 2: tri

Voyons maintenant une autre façon de manipuler votre résultat: tri par l'une des colonnes de sortie.

Dactylographie:

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM,,2,3)

signifie qu'après avoir sauté l'argument eainsi que l'inclusion de grands totaux et de sous-totaux (numéro 2 en plaidoirie f), vous souhaitez également commander vos données par des chiffres de visualisation globale dans l'ordre croissant (numéro 3 en plaidoirie g).

Remarquez comment le tri par des chiffres de vision totale place non seulement les sports dans l'ordre en fonction de leurs totaux globaux, mais aussi des sous-catégories régionales en fonction de leurs sous-totaux.

Exemple 3: pourcentages (pourcentage)

Voici comment afficher votre résultat en utilisant des pourcentages.

Dactylographie:

=PIVOTBY(Sports_Viewers(Sport),Sports_Viewers(Year),Sports_Viewers(Viewers),PERCENTOF,,,2,,,,2)

dit à Excel que vous voulez le sport comme en-têtes de ligne (argument un), les années en tant qu'en-têtes de colonne (argument b), et vous souhaitez agréger vos données (argument c) sous forme de pourcentages (argument d). Le premier 2 (argument g) représente l'ordre, qui, dans ce cas, est par les valeurs. Enfin, le second 2 (argument k) dit à Excel que les pourcentages devraient être relatifs aux données globales. Les virgules non peuplées disent à Excel que vous voulez qu'il adopte la valeur par défaut pour les arguments e, f, h, i, et J.

Maintenant, sélectionnez les cellules contenant les résultats décimalisés, puis cliquez sur l'icône « pourcentage de style » dans le groupe numérique de l'onglet Home sur le ruban. Pendant que vous y êtes, cliquez sur les boutons « augmenter décimal » et « diminuer les décimales » pour définir le nombre de décimales.

Sélectionnez des lignes supplémentaires sous le résultat au cas où plus de données sont ajoutées au tableau d'origine à l'avenir.

Enfin, prenez un moment pour digérer ce que ce résultat vous dit.

Par exemple, le nombre de personnes qui regardent le softball en 2022 représentent 11,5% des chiffres globaux de la visualisation pour tous les sports de toutes les années. De plus, comme les données sont organisées par la colonne totale dans l'ordre croissant, vous pouvez voir que près d'un quart (23,7%) des chiffres globaux de la vision provenaient de personnes qui regardent le basket-ball.

---

En plus d'utiliser la somme, la moyenne et le pourcentage de l'argument de la fonction dans Pivotby, vous pouvez également utiliser d'autres fonctions d'agrégation, comme Count, Min ou Max.