Comment formater un tableau renversé dans Excel

Sommaire

Toggle

Résumé

• L'application de la mise en forme directe aux tableaux renversés dans Excel pourrait entraîner des problèmes si les données modifient la forme ou la taille.

• Les règles de formatage conditionnel basées sur la formule permettent des ajustements de formatage automatique lorsque les paramètres de données changent.

• Placer un symbole d'un dollar avant la référence de la colonne vous permet d'appliquer les règles à toutes les lignes des données.

Dans Excel, vous pouvez appliquer un formatage direct aux valeurs ou aux arrière-plans des cellules pour faciliter la lecture de la feuille de calcul. Cependant, lorsqu'une formule Excel renvoie un ensemble de valeurs – connues en tant que tableau renversé – l'application de la mise en forme directe entraînera des problèmes si la taille ou la forme des données change.

Disons que vous avez cette feuille de calcul contenant le résultat renversé d'une formule Pivotby, qui montre le nombre de téléspectateurs par sport dans diverses régions sur quatre ans.

Pour suivre comme vous le lisez, téléchargez une copie de ce classeur Excel. Après avoir cliqué sur le lien, vous trouverez le bouton de téléchargement dans le coin supérieur droit de votre écran.

Parce que la fonction Pivotby n'inclut pas un argument qui vous permet de formater le résultat, la distinction entre les lignes d'en-tête, les lignes de données, les lignes sous-totales et la grande ligne totale est difficile.

À ce stade, vous pourriez être tenté d'appliquer une mise en forme directe – via le groupe de polices de l'onglet Home sur le ruban – pour séparer visuellement les différents types de lignes dans les données.

Cependant, si vous modifiez plus tard les paramètres dans la formule Pivotby, ou si le résultat se développe ou rétrécit en raison des modifications des données d'origine, le formatage direct que vous avez appliqué ne s'ajustera pas en conséquence. En effet, le formatage direct dans Excel est lié aux cellules, plutôt que les données qu'ils contiennent. Voir la confusion que cela pourrait provoquer dans la capture d'écran ci-dessous, où les données se sont rétrécies, mais la mise en forme directe est toujours appliquée aux mêmes lignes.

Au lieu de cela, vous devez utiliser la mise en forme conditionnelle, ce qui vous permet de formater les cellules et les lignes en fonction de leurs valeurs.

Sélectionnez toutes les données – plus quelques lignes supplémentaires en bas pour permettre une croissance verticale – et dans l'onglet Home sur le ruban, cliquez sur Formatage conditionnel> Gérer les règles.

Ensuite, dans la boîte de dialogue de gestion des règles de mise en forme conditionnelle, cliquez sur « Nouvelle règle ».

Pour chacune des règles que vous allez créer pour formater votre tableau renversé, vous devrez utiliser une formule. Ainsi, dans la zone de type de règle Sélectionnez une nouvelle boîte de dialogue de règles de formatage, sélectionnez l'option finale, « Utilisez une formule pour déterminer les cellules à formater. »

La première règle que vous souhaitez créer concerne les lignes d'en-tête. Plus précisément, vous voulez que ces cellules adoptent un fond gris.

Pour y parvenir, prenez un moment pour identifier ce qui rend les lignes d'en-tête uniques des autres lignes de votre table. Dans ce cas, les lignes d'en-tête sont les seules lignes qui ne contiennent pas de nombres dans la colonne G. Ainsi, dans le champ de formule, type:

=ISTEXT($G1)

Étant donné que la fonction ISText traite les cellules vierges et les cellules contenant du texte comme valeur de texte, la règle de formatage conditionnelle considérera les cellules G1 en G3 comme contenant du texte, avec les cellules restantes de la colonne G contenant des valeurs de nombre.

Surtout, l'ajout d'un signe ($) du dollar avant la référence de la colonne – également connue sous le nom de référence mixte – fixe la mise en forme conditionnelle à cette colonne, tout en permettant à Excel d'appliquer la règle aux lignes restantes.

Après avoir tapé une référence cellulaire, plutôt que de taper le signe du dollar manuellement, appuyez sur F4 pour basculer entre les références absolues (par exemple, $ g 1), les références mixtes (par exemple, $ G1 ou G 1 $) et les références relatives (par exemple, G1).

Enfin, comme vous avez initialement sélectionné des données dans les colonnes A à G, la mise en forme conditionnelle s'appliquera à toute la ligne où la condition est remplie.

Maintenant, cliquez sur « Format » pour sélectionner le formatage pour les lignes d'en-tête. Dans ce cas, vous voulez qu'ils soient colorés gris. Ensuite, cliquez sur « OK » dans les cellules du format et modifiez les boîtes de dialogue des règles de mise en forme.

Lorsque vous cliquez sur « Appliquer » dans la boîte de dialogue de gestion des règles de mise en forme conditionnelle, vous verrez que seules les lignes dans lesquelles la colonne G contient des cellules vierges ou du texte – en d'autres termes, les lignes d'en-tête – sont remplies de gris.

Ensuite, vous souhaitez formater les lignes sous-totales afin qu'ils adoptent un remplissage vert clair.

Encore une fois, passez soigneusement les données pour voir quelles conditions vous pouvez utiliser pour appliquer le formatage à ces lignes uniquement. Dans ce cas, les lignes subtotales contiennent du texte dans la colonne A mais rien dans la colonne B. De plus, puisque la grande ligne totale répond également à ces critères, vous devez exclure les cellules de la colonne A contenant les mots «grand total».

Avec la boîte de dialogue de règles de formatage conditionnel, il est toujours ouvert, cliquez sur « nouvelle règle » et sélectionnez l'option qui vous permet d'utiliser une formule pour formater les cellules. Cette fois, dans le champ de formule, type:

=AND($A1<>"",$B1="",$A1<>"Grand Total")

où

• La fonction et vous permet de spécifier plus d'une condition dans les parenthèses,

• A1 $<> «  » dit à Excel de rechercher des cellules de la colonne A qui ne contiennent pas (<>) cellules vierges («  »),
• $ B1 = «  » dit à Excel de rechercher des cellules de la colonne B qui contiennent (=) des cellules vierges («  »), et
• A1 $<> « Grand total » dit à Excel d'exclure (<>) toutes les cellules de la colonne A qui contiennent le texte «grand total».

Comme pour la règle précédente, n'oubliez pas d'insérer le signe $ avant les références de colonne pour permettre à Excel d'appliquer la même règle dans toutes les lignes sélectionnées.

Maintenant, cliquez sur « Format » pour choisir la couleur de remplissage vert clair, et après avoir fermé les cellules du format et modifier la boîte de dialogue des règles de mise en forme, cliquez sur « Appliquer » pour voir les rangées sous-totales remplies en vert clair.

Enfin, vous voulez que les cellules de la grande rangée totale soient remplies d'un vert plus audacieux.

Étant donné que la grande ligne totale est la seule ligne qui contient les mots « grand total » dans la colonne A, c'est le critère que vous pouvez utiliser pour la mise en forme conditionnelle. Dans la boîte de dialogue Dialogue Règles de formatage conditionnel, cliquez sur « nouvelle règle » et sélectionnez l'option finale dans la liste de type de règles Sélectionnez une règle. Maintenant, dans le champ de formule, type:

=$A1="Grand Total"

Ensuite, cliquez sur « Format » et sélectionnez une couleur de remplissage vert audacieux pour appliquer aux cellules qui correspondent à ce critère. Maintenant, lorsque vous fermez les cellules du format et modifiez les boîtes de dialogue des règles de mise en forme, et cliquez sur « Appliquer » dans la boîte de dialogue de gestionnaire de règles de mise en forme conditionnelle, vous verrez que votre grande ligne totale a adopté ce formatage.

Maintenant que vous avez appliqué toutes vos règles, cliquez sur « Closez » dans la boîte de dialogue Dialogue de règles de mise en forme conditionnelle. Ensuite, ajustez certaines de vos données dans la table d'origine et voyez le résultat renversé et sa mise à jour de formatage en conséquence.

Dans cet exemple, même si j'ai retiré 12 lignes de la table de données d'origine, le résultat de pivotby renversé est toujours correctement formaté, avec les lignes d'en-tête gris coloré, les lignes subtotales colorées vert clair et le grand vert audacieux au gras.

---

Si vous devez apporter des modifications aux règles que vous avez créées et appliquées, sélectionnez simplement n'importe quelle cellule dans les données et cliquez sur Formatage conditionnel> Gérer les règles pour relancer le gestionnaire de règles de mise en forme conditionnel. Ensuite, double-cliquez sur une règle pour modifier ses conditions.