Mise en forme conditionnel selon plusieurs critères

[Gonteur]

Bonjour,
Questions pour une mise en forme conditionnelle selon plusieurs critères

Fichier exemple joint

Les lignes entières (plage choisie) seront colorées via une règle qui applique une mise en forme selon une formule;
Lorsque la valeur de la colonne A est égal ($A$2:$A$7=$A2) et (*) la valeur de la colonne B ou (+) la valeur de la colonne C ou(+) la valeur de la colonne D ne sont pas égal ($B$2:$B$7<>$B2)

Ce qui donne comme formule;

=SOMMEPROD(((((($A$2:$A$7=$A2)*($B$2:$B$7<>$B2))+( ($A$2:$A$7=$A2))*(($C$2:$C$7<>$C2))+(($A$2:$A$7=$A 2))*(($D$2:$D$7<>$D2))))))

Cela fonctionne, les lignes sont colorés lorsque A a la même valeur et que les valeurs en B ou C ou D diffèrent dans la même colonne. Si vous modifier les donnée sur le chiffrier exemple joint vous verrez comment la mise en forme change.

Là où j’ai un problème, c’est que je veux une validation différente pour les colonnes E ou F, plûtôt que pas égal (<>) comme aux colonnes B, C et D, je veux que la formule valide si la même valeur des colonnes E ou F est plus grand que 1 (>1)

Donc comment intégrer la validation suivante à la formule plus haute ;

(($A$2:$A$7=$A2)*($E$2:$E$7="x"))>1 et (($A$2:$A$7=$A2)*($F$2:$F$7="x"))>1

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

Bonjour Gonteur,

Avant d'aller plus loin, il est clair que votre formule initiale se simplifie par mise en facteur :

=SOMMEPROD(($A$2:$A$7=$A2)*(($B$2:$B$7<>$B2)+($C$2:$C$7<>$C2)+($D$2:$D$7<>$D2)))

Pour la suite je ne comprends pas l'imbrication du "x" et du 1, exprimez ça clairement en français.

Et quel opérateur faut-il utiliser: et (*), ou (+) ?

A+

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Merci job75,

Pour les colonnes B, C et D, ce que je veux valider est que lorsque l'expression des lignes en A est identique alors l'expression des lignes en B ou C ou D doit être identiques pour toutes les lignes identiques en A.

Cela fonctionne et merci d'avoir abrègé la formule que j'avais.

Pour les colonnes E et F, ce que je veux valider est que lorsque l'expression des lignes en A est identique alors l'expression des lignes en E ou F ne doivent pas être identique (inscrit plus qu'une fois) pour toutes les lignes identiques en A et la formule ne doit pas considérer les cellules vides.

La formule pour les colonnes B, C ou D valide les cellules vides, ce que je ne veux pas pour les colonnes E et F

J'espère que c'est plus clair

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

Re,

Pour les colonnes B C D il suffit de lire la formule.

Mais pour E et F vos formules n'ont pas de sens, c'est là le problème.

Pouvez-vous reprendre vos explications sans le mot "valide" qui n'a pas de sens non plus.

Dans le SOMMEPROD il n'y a que des matrices qui renvoient VRAI ou FAUX (ou des nombres).

A+

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Job75
Désolé des termes utilisés, je ne suis vraiment pas un expert, c’est souvent par des essais-erreur via des recherches sur le net que je finis par obtenir les résultats voulues.

Je croyais qu’une mise en forme s’appliquait lorsque la valeur retournée était vrai tel qu’inscrit dans la fenêtre modal d’une modification d’une règle de mise en forme;

« Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vrai »

=SOMMEPROD(($A$2:$A$7=$A2)*(($B$2:$B$7<>$B2)+($C$2:$C$7<>$C2)+($D$2:$D$7<>$D2)))

Je croyais qu’une validation se faisait à savoir que si ($A$2:$A$7=$A2) est vrai et que une ou l’autre des 3 autres formules suivantes est vrai alors la mise en forme est appliqué.

Cette mise en forme conditionnelle si la valeur de la formule suivante est vrai;
=SOMMEPROD(($A$2:$A$7=$A2)*($E$2:$E$7="x"))>1, fonctionne lorsque appliqué sur la colonne E seulement (voir le fichier exemple)

Je souhaite l’intégrer à la mise en forme via formule des colonnes B C D;

Si on regarde une autre avenue, si j’ajouterais à la première formule +($E$2:$E$7=$E2), comment faire pour que celle-ci ignore les cellulles vides.

Ton aide est vraiment apprécié, merci d’être patient

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

Re,

Bon j'ai supposé qu'une ligne doit se colorer seulement s'il y a un "x" en colonne E ou F.

La formule de la MFC pour la ligne 2 est donc :

=SOMMEPROD(($A$2:$A$7=$A2)*(($B$2:$B$7<>$B2)+($C$2:$C$7<>$C2)+($D$2:$D$7<>$D2))*($E2:$F2="x"))

Fichier joint.

A+

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Re,


"Bon j'ai supposé qu'une ligne doit se colorer seulement s'il y a un "x" en colonne E ou F."

toutes les lignes doivent se colorer s'il y a plus d'un "x" en colonne E ou F


Toutes les lignes ayant la même valeur en A doivent être colorées si toutes les cellules en B ou en C ou en D ne sont pas identiques OU que si toutes les cellules en E ou F contiennent plus que 1 "x". de là le plus grand que 1

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

Re,

A priori il faut 2 SOMMEPROD :

=SOMMEPROD(($A$2:$A$7=$A2)*(($B$2:$B$7<>$B2)+($C$2:$C$7<>$C2)+($D$2:$D$7<>$D2)))+(SOMMEPROD(($A$2:$A$7=$A2)*($E$2:$F$7="x"))>1)

Fichier (2).

A+

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Merci job75,

Ça fonctionne

J'ai modifier la formule pour que la colonne E et F soient indépendantes

=SOMMEPROD(($A$2:$A$7=$A2)*(($B$2:$B$7<>$B2)+($C$2:$C$7<>$C2)+($D$2:$D$7<>$D2)))+ (SOMMEPROD(($A$2:$A$7=$A2)*($E$2:$E$7="x"))>1) +(SOMMEPROD(($A$2:$A$7=$A2)*($F$2:$F$7="x"))>1)

Tout fonctionne comme je le voulais, fichier joint

Merci beaucoup! C'est très apprécié...

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

RE,

J'ai modifier la formule pour que la colonne E et F soient indépendantes

Ce serait utile si les colonnes étaient disjointes ou s'il y avait un * (ET) entre le 2ème et le 3ème SOMMEPROD.

Avec le signe + (OU) ma formule du post #8 suffit.

A+

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

Re,

J'avais annulé le post, mais c'était bon.

Comme pour E et F, il est mieux de joindre les colonnes B C D, la formule est plus simple :

=SOMMEPROD(($A$2:$A$7=$A2)*($B$2:$D$7<>$B2:$D2))+(SOMMEPROD(($A$2:$A$7=$A2)*($E$2:$F$7="x"))>1)

Fichier (3).

Bonne nuit et A+

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Ce serait utile si les colonnes étaient disjointes

Les colonnes E et F sont disjointes contrairement à B C et D

J'en suis rendu à

 =SOMMEPROD(($A$2:$A$7=$A2)*($B$2:$D$2<>$B2:$D2)) + (SOMMEPROD(($A$2:$A$7=$A2)*($E$2:$E$7="x"))>1) + (SOMMEPROD(($A$2:$A$7=$A2)*($F$3:$F$7="x"))>1)

Tout fonctionne bien

Encore merci

 

[job75]

Re : Mise en forme conditionnel selon plusieurs critères

Bonjour Gonteur, le forum,

Dans votre dernière formule je suppose que c'est $F$2 et pas $F$3 ??

Notez que même avec 2 colonnes disjointes 2 SOMMEPROD suffisent :

=SOMMEPROD(($A$2:$A$7=$A2)*($B$2:$D$7<>$B2:$D2))+(SOMMEPROD(($A$2:$A$7=$A2)*(($E$2:$E$7="x")+($G$2:$G$7="x")))>1)

Fichier (4)

A+

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Sujet résolu,

Un gros merci!

 

[Gonteur]

Re : Mise en forme conditionnel selon plusieurs critères

Bonjour,

Je relance une question en rapport à ce thread;

Voici une formule pour une mise en forme conditionnel;

=SOMMEPROD(($A$3:$A$1000=$A3)*($H$3:$H$1000="√"))>0*(SOMMEPROD(($A$3:$A$1000=$A3)*($J$3:$J$1000="√"))>0)

Dans mon chiffrier, j'ai une valeur vrai pour le premier SOMMEPROD et une valeur faux pour le deuxième, alors pourquoi la mise en forme s'applique? J'imagine qu'il y a une erreur dans la formule?

Merci de votre aide