Application de la mise en forme conditionnelle à un tableau croisé dynamique dans Excel

L'application d'une mise en forme conditionnelle dans un tableau croisé dynamique peut être un peu délicate.

Étant donné que les tableaux croisés dynamiques sont si dynamiques et que les données du backend peuvent changer souvent, vous devez connaître la bonne façon d'utiliser la mise en forme conditionnelle dans un tableau croisé dynamique dans Excel.

La mauvaise façon d'appliquer une mise en forme conditionnelle à un tableau croisé dynamique

Examinons d'abord la manière habituelle d'appliquer la mise en forme conditionnelle dans un tableau croisé dynamique.

Supposons que vous ayez un tableau croisé dynamique comme indiqué ci-dessous :

Dans l'ensemble de données ci-dessus, la date est dans les lignes et nous avons stocké les données de vente dans les colonnes.

Voici la manière habituelle d'appliquer une mise en forme conditionnelle à n'importe quel ensemble de données :

• Sélectionnez les données (dans ce cas, nous appliquons la mise en forme conditionnelle à B5:D14).
• Allez dans Accueil -> Mise en forme conditionnelle -> Règles supérieures/inférieures -> Au-dessus de la moyenne.
• Spécifiez le format (j'utilise « Remplissage vert avec du texte vert foncé »).
• Cliquez sur OK.

Cela appliquera la mise en forme conditionnelle comme indiqué ci-dessous :

Tous les points de données qui sont au-dessus de la moyenne de l'ensemble de données entier ont été mis en évidence.

Le problème avec cette méthode est qu'elle a appliqué le format conditionnel à une plage fixe de cellules (B5:D14). Si vous ajoutez des données dans le backend et actualisez ce tableau croisé dynamique, la mise en forme conditionnelle ne lui sera pas appliquée.

Par exemple, je retourne à l'ensemble de données et ajoute des données pour une date supplémentaire (11 janvier 2015). C'est ce que j'obtiens lorsque j'actualise le tableau croisé dynamique.

Comme vous pouvez le voir sur l'image ci-dessus, les données du 11 janvier 2015 ne sont pas mises en évidence (alors qu'elles le devraient, car les valeurs pour Store 1 et Store 3 sont supérieures à la moyenne).

La raison, comme je l'ai mentionné ci-dessus, est que la mise en forme conditionnelle a été appliquée sur une plage fixe (B5:D14) et qu'elle ne s'étend pas aux nouvelles données du tableau croisé dynamique.

La bonne façon d'appliquer une mise en forme conditionnelle à un tableau croisé dynamique

Voici deux méthodes pour vous assurer que la mise en forme conditionnelle fonctionne même lorsqu'il y a de nouvelles données dans le backend.

Méthode 1 - Utilisation de l'icône de formatage du tableau croisé dynamique

Cette méthode utilise l'icône Options de mise en forme du tableau croisé dynamique qui apparaît dès que vous appliquez une mise en forme conditionnelle dans un tableau croisé dynamique.

Voici les étapes à suivre :

• Sélectionnez les données sur lesquelles vous souhaitez appliquer une mise en forme conditionnelle.
• Allez dans Accueil -> Mise en forme conditionnelle -> Règles supérieures/inférieures -> Au-dessus de la moyenne.
• Spécifiez le format (j'utilise « Remplissage vert avec du texte vert foncé »).
• Cliquez sur OK.
  • Lorsque vous suivez les étapes ci-dessus, il applique la mise en forme conditionnelle sur l'ensemble de données. En bas à droite de l'ensemble de données, vous verrez l'icône Options de formatage :

• Cliquez sur l'icône. Il affichera trois options dans une liste déroulante :
  • Cellules sélectionnées (qui seraient sélectionnées par défaut).
  • Toutes les cellules affichant les valeurs « Somme des revenus ».
  • Toutes les cellules affichant les valeurs « Somme des revenus » pour « Date » et « Magasin ».
• Sélectionnez la troisième option - Toutes les cellules affichant les valeurs « Somme des revenus » pour « Date » et « Magasin ».

Désormais, lorsque vous ajoutez des données dans le back-end et actualisez le tableau croisé dynamique, les données supplémentaires sont automatiquement couvertes par la mise en forme conditionnelle.

Comprendre les trois options :

• Cellules sélectionnées : Il s'agit de l'option par défaut où la mise en forme conditionnelle est appliquée uniquement sur les cellules sélectionnées.
• Toutes les cellules affichant les valeurs « Somme des revenus » : Dans cette option, il considère toutes les cellules qui affichent les valeurs de la somme des revenus (ou toutes les données que vous avez dans la section des valeurs du tableau croisé dynamique).
  • Le problème avec cette option est qu'elle couvrira également les valeurs du grand total et lui appliquera une mise en forme conditionnelle.
• Toutes les cellules affichant les valeurs « Somme des revenus » pour « Date » et « Magasin »: C'est la meilleure option dans ce cas. Il applique le formatage conditionnel à toutes les valeurs (à l'exclusion des totaux généraux) pour la combinaison de Date et Store. Même si vous ajoutez plus de données dans le back-end, cette option s'en chargera.

Noter:

• L'icône Options de mise en forme est visible juste après avoir appliqué une mise en forme conditionnelle à l'ensemble de données. Si disparaît si vous faites autre chose (modifier une cellule ou changer la police/l'alignement, etc.).
• La mise en forme conditionnelle disparaît si vous modifiez les champs de ligne/colonne. Par exemple, si vous supprimez le champ Date et l'appliquez à nouveau, la mise en forme conditionnelle sera perdue.

Méthode 2 - Utilisation du gestionnaire de règles de mise en forme conditionnelle

Outre l'utilisation de l'icône Options de mise en forme, vous pouvez également utiliser la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle pour appliquer une mise en forme conditionnelle dans un tableau croisé dynamique.

Cette méthode est utile lorsque vous avez déjà appliqué la mise en forme conditionnelle et que vous souhaitez modifier les règles.

Voici comment faire:

• Sélectionnez les données sur lesquelles vous souhaitez appliquer une mise en forme conditionnelle.
• Allez dans Accueil -> Mise en forme conditionnelle -> Règles supérieures/inférieures -> Au-dessus de la moyenne.
• Spécifiez le format (j'utilise « Remplissage vert avec du texte vert foncé »).
• Cliquez sur OK. Cela appliquera la mise en forme conditionnelle aux cellules sélectionnées.
• Allez dans Accueil -> Mise en forme conditionnelle -> Gérer les règles.
• Dans le gestionnaire de règles de mise en forme conditionnelle, sélectionnez la règle que vous souhaitez modifier et cliquez sur le bouton Modifier la règle.
• Dans la boîte de dialogue Modifier la règle, vous verrez les trois mêmes options :
  • Cellules sélectionnées.
  • Toutes les cellules affichant les valeurs « Somme des revenus ».
  • Toutes les cellules affichant les valeurs « Somme des revenus » pour « Date » et « Magasin ».
• Sélectionnez la troisième option et cliquez sur OK.

Cela appliquera la mise en forme conditionnelle à toutes les cellules des champs « Date » et « Magasin ». Même si vous modifiez les données principales (ajoutez plus de données de magasin ou de données de date), la mise en forme conditionnelle serait fonctionnelle.

Noter: La mise en forme conditionnelle disparaît si vous modifiez les champs de ligne/colonne. Par exemple, si vous supprimez le champ Date et l'appliquez à nouveau, la mise en forme conditionnelle sera perdue.