Comprendre la MFC

L'élément central de la formule utilisée dans cet exemple est la fonction :

NB.SI ( plage; param )

qui calcule le nombre d’occurrences de "param" dans la "plage".

Par exemple, la formule :

NB.SI ( plage, param ) > 1

rendra VRAI s'il y a au moins 2 occurrences de "param" dans la "plage" et rendra FAUX sinon.

Dans l'exemple présenté, la règle, affichée dans l'image ci-dessous, a été créée avec MS Excel. Elle permet la mise en forme par un fond Jaune ( colonne Format ), des cellules de la plage $A$1:$C$3 ( colonne S'applique à ) pour lesquelles l'évaluation de la formule ( colonne Règle ) rend VRAI.

Comme le montre l'image ci-dessous, elle met en évidence par un fond jaune, pour les données de l'image, les colonnes ( ici la colonne "C" ) dont la 1ère cellule contient une donnée figurant également dans d' autres cellules de la plage (ici la plage "A1:C3").

Le tableur évalue la formule, successivement, pour chaque cellule de la plage sélectionnée et détermine ainsi si elle répond à la règle. Lorsque le résultat du calcul est "VRAI", le formatage spécifié est appliqué à la cellule
La Mise en Forme Conditionnelle étudiée applique donc cette formule successivement à chaque cellule de la plage $A$1:$C$3 donc à 9 cellules ! (3 x 3).

Développer le pas à pas de l'analyse :

La 1^ere cellule évaluée est A1, la formule à évaluer est NB.SI($A$1:$C$3;A$1) > 1. Elle rend FAUX car NB.SI($A$1:$C$3;A$1) = 1. En effet. il y a 1 occurrence de 15 dans la plage ;
La 2^eme cellule évaluée est A2 ( on descend d'une ligne ) mais la formule à évaluer reste NB.SI($A$1:$C$3;A$1) >1 car la syntaxe $1 fige la ligne 1. L'évaluation rend donc FAUX ;
La 3^eme cellule évaluée est A3, pour la même raison, la formule à évaluer reste NB.SI($A$1:$C$3;A$1) > 1. L'évaluation rend donc FAUX ;
La 4^eme cellule évaluée est B1, la formule à évaluer devient NB.SI($A$1:$C$3;B$1) > 1. Elle rend FAUX car NB.SI($A$1:$C$3;B$1) = 1. En effet, il y a 1 occurrence de 75 dans la plage ;
La 5^eme cellule évaluée est B2 la formule à évaluer reste NB.SI($A$1:$C$3;B$1) > 1 car la syntaxe $1 fige la ligne. L' évaluation rend donc FAUX ;
La 6^eme cellule évaluée est B3 la formule à évaluer reste NB.SI($A$1:$C$3;B$1) > 1. L'évaluation rend donc FAUX ;
La 7^eme cellule évaluée est C1 la formule à évaluer devient NB.SI($A$1:$C$3;C$1)> 1. Elle rend VRAI car NB.SI($A$1:$C$3;C$1) = 2. En effet, il y a 2 occurrences de 50 dans la plage ;
La 8^eme cellule évaluée est C2 la formule à évaluer reste NB.SI($A$1:$C$3;C$1)> 1 car la syntaxe $1 fige la ligne. L'évaluation rend donc VRAI ;
La 9^eme cellule évaluée est C3 la formule à évaluer reste NB.SI($A$1:$C$3;C$1) > 1. L'évaluation rend donc VRAI ;

Dans la partie gauche de l'image ci-dessous, on retrouve la plage à analyser. Dans la cellule A6, a été tapée la 1ere partie de la règle de la MFC (cf cadre 1). Cette cellule a été ensuite copiée dans les cellules adjacentes. L affichage confirme l'analyse du paragraphe précédent.

La partie droite de l' image ci-dessus montre une méthode efficace pour visualiser l'effet de la règle utilisée. Elle utilise une nouvelle feuille de calcul dans laquelle la cellule ayant pour coordonnées celle la 1^ère cellule de la plage ciblée (A1 dans l'exemple) reçoit pour valeur une formule très proche de la règle de la MFC à étudier : on ajoute simplement le nom de la feuille où doit s'appliquer la MFC devant chaque référence.

Dans l'exemple, on place donc "RelAbs" (le nom de la feuille) devant la référence de la plage ( pour former RelAbs!$A$1:$C$3 ) et devant la référence de la cellule ( pour former RelAbs!A$1 ).

On recopie ensuite cette formule en tirant sur l'ancre pour former une plage de même dimension que la plage ciblée par la MFC.

4.1 Mettre en évidence les valeurs répétées :

Une modification mineure de la règle de l'exemple étudié a des conséquences importantes. Ici, comme le montre l'image ci-dessous, le $ a été supprimé dans la référence du paramètre de la formule; La règle devient "NB.SI($A$1:$C$3;A1) > 1" en remplacement de "NB.SI($A$1:$C$3;A$1) > 1".

Comme le montre l'image ci-dessous, cette nouvelle règle met en évidence par un fond jaune, pour les données de l'image, toutes les cellules contenant une donnée figurant également dans d'autres cellules de la plage (ici la plage "A1:C3").

Voir l'analyse pas à pas

La 1ere cellule évaluée est A1 la formule à évaluer est NB.SI($A$1:$C$3;A1) >1 qui rend Faux car NB.SI($A$1:$C$3;A1) = 1 (il y a 1 occurrence de 15 dans la plage) ;
La 2eme cellule évaluée est A2 la formule à évaluer devient NB.SI($A$1:$C$3;A2) >1 qui rend Faux car NB.SI($A$1:$C$3;A2) = 1 (il y a 1 occurrence de 75 dans la plage) et 1 > 1 est Faux ;
La 3eme cellule évaluée est A3 la formule à évaluer devient NB.SI($A$1:$C$3;A3) >1 qui rend Vrai car NB.SI($A$1:$C$3;A3) = 2 (il y a 2 occurrences de 50 dans la plage) et 2 > 1 est Vrai ;
La 4eme cellule évaluée est B1 la formule à évaluer devient NB.SI($A$1:$C$3;B1) >1 qui rend Faux car NB.SI($A$1:$C$3;B1) = 1 (il y a 1 occurrence de 10 dans la plage) ;
La 5eme cellule évaluée est B2 la formule à évaluer devient NB.SI($A$1:$C$3;B2) >1 qui rend Vrai car NB.SI($A$1:$C$3;B2) = 2 (il y a 2 occurrences de 100 dans la plage) ;
La 6eme cellule évaluée est B3 la formule à évaluer devient NB.SI($A$1:$C$3;B3) >1 qui rend Vrai car NB.SI($A$1:$C$3;B3) = 2 (il y a 2 occurrences de 100 dans la plage) ;
La 7eme cellule évaluée est C1 la formule à évaluer devient NB.SI($A$1:$C$3;C1)>1 qui rend Vrai car NB.SI($A$1:$C$3;C1) = 3 (il y a 2 occurrences de 25 dans la plage) ;
La 8eme cellule évaluée est C2 la formule à évaluer devient NB.SI($A$1:$C$3;C2)>1 qui rend Vrai car NB.SI($A$1:$C$3;C2) = 2 (il y a 2 occurrences de 50 dans la plage) ;
La 9eme cellule évaluée est C3 la formule à évaluer devient NB.SI($A$1:$C$3;C3) >1 qui rend Vrai car NB.SI($A$1:$C$3;C3) = 3 (il y a 2 occurrences de 25 dans la plage).

4.2 Mettre en évidence les entêtes de ligne répétées :

Voici une autre modification mineure de la règle de l'exemple étudié. Comme le montre l'image ci-dessous, le $ a été déplacé dans la référence du paramètre de la formule ; La règle devient "NB.SI($A$1:$C$3;$A1) > 1" en remplacement de "NB.SI($A$1:$C$3;A$1) > 1".

Comme le montre l'image ci-dessous, cette nouvelle règle met en évidence par un fond jaune, pour les données de l'image, toutes les lignes dont la 1ère cellule contient une donnée figurant également dans d'autres cellules de la plage (ici la plage "A1:C3").

Voir l'analyse pas à pas

La 1ere cellule évaluée est A1, la formule à évaluer est NB.SI($A$1:$C$3;$A1) >1 qui rend FAUX car NB.SI($A$1:$C$3;$A1) =1 (il y a 1 occurrence de 15 dans la plage) ;
La 2eme cellule évaluée est A2, la formule à évaluer devient NB.SI($A$1:$C$3;$A2) >1 qui rend FAUX car NB.SI($A$1:$C$3;$A2) = 1 (il y a 1 occurrence de 10 dans la plage) ;
La 3eme cellule évaluée est A3 la formule à évaluer devient NB.SI($A$1:$C$3;$A3) >1 qui rend VRAI car NB.SI($A$1:$C$3;C3) = 2 (il y a 2 occurrences de 25 dans la plage) ;
La 4eme cellule évaluée est B1 la formule à évaluer redevient NB.SI($A$1:$C$3;$A1) >1 ; le $A fige la colonne. L'évaluation rend FAUX car NB.SI($A$1:$C$3;$A1) =1 (il y a 1 occurrence de 15 dans la plage) ;
La 5eme cellule évaluée est B2 la formule à évaluer devient NB.SI($A$1:$C$3;$A2) >1 ; le $A fige la colonne. L'évaluation rend FAUX car NB.SI($A$1:$C$3;$A2) = 1 (il y a 1 occurrence de 10 dans la plage) ;
La 6eme cellule évaluée est B3 la formule à évaluer devient NB.SI($A$1:$C$3;$A3) >1 ; le $A fige la colonne. L'évaluation rend VRAI car NB.SI($A$1:$C$3;C3) = 2 (il y a 2 occurrences de 25 dans la plage) ;
La 7eme cellule évaluée est C1 la formule à évaluer devient NB.SI($A$1:$C$3;$A1)>1 ; le $A fige la colonne. L'évaluation rend FAUX ;
La 8eme cellule évaluée est C2 la formule à évaluer devient NB.SI($A$1:$C$3;$A2)>1  ; le $A fige la colonne. L'évaluation rend FAUX ;
La 9eme cellule évaluée est C3 la formule à évaluer devient NB.SI($A$1:$C$3;$A3) >1 ; le $A fige la colonne. L'évaluation rend VRAI.