Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2016 chercher, trouver et surligner des cellules en fonction d'un critère d'une autre cellule

Cyrille72

XLDnaute Nouveau
bonjour à tous, j'ai quelques notion en excel mais pas en termes de fonctions Excel !;-(

J'ai cherché dans le forum, une discussion qui aurait pu m'aider mais je n'ai pas trouvé.... ou alors j'ai mal cherché ! désolé

Dans mon tableau, je cherche à surligner les cellules qui correspondent à une demande d'une autre cellule.
j'ai trouvé une formule sur le net que correspond, mais seulement pas complétement ! A savoir qu’elle trouve bien un numéro 3, par exemple, mais aussi tous les autres qui contiennent un 3.
dans mon tableau vous verrez que j'ai une formule qui compte et qui est parfaite, mais là je veux surligner en MFC.
Éventuellement, est il possible d'intégrer dans la MFC la formule qui compte...
j’espère m’être bien fait comprendre...

Merci pour votre aide
 

Pièces jointes

  • trouver et surligner.xlsx
    14.8 KB · Affichages: 6
Solution
Re,

Votre formule de MFC est incorrecte. Votre formule :
VB:
=SI(ESTNUM(EQUIV($M$3;$D11:$H500;0)+EQUIV($N$3;$D11:$H500;0)+EQUIV($O$3;$D11:$H500;0));NB.SI($M$3:$O$3;D11)>0;FAUX)

Pour une cellule donnée (ex: D11), on cherche si dans sa ligne on trouve les valeurs en ligne 11.
La ligne de la cellule est $D11:$H11 et non $D11:$H500 qui est le tableau dans son entier.

La formule correcte est donc :
VB:
=SI(ESTNUM(EQUIV($M$3;$D11:$H11;0)+EQUIV($N$3;$D11:$H11;0)+EQUIV($O$3;$D11:$H11;0));NB.SI($M$3:$O$3;D11)>0;FAUX)

Voir fichier joint.

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @Cyrille72,

Pas certain d'avoir bien compris.

Pour surligner les nombres contenant 3 ou 4 ou 7, on utilise sur la plage =$D$2:$H$6, la formule:
VB:
=NB.SI($J$9:$L$9;D2)>0

Pour surligner les nombres contenant 3 ou 4 ou 7, on utilise sur la plage =$D$2:$H$6, la formule:
Code:
=NB.SI($J$9;D2)>0
 

Cyrille72

XLDnaute Nouveau


Merci beaucoup ! C'est exactement ce que je cherchais
J'ai pas bien compris comment ça fonctionne, mais comme les fonctions sont parfois abstraites...
Encore merci
 

Cyrille72

XLDnaute Nouveau
Je viens de me rendre compte que le surlignage fonctionne même quand il n'y a que 2 critères... Or j'aurai voulu que le surlignage n'intervienne que si les 3 numéros sont trouvés...
Est ce possible par ligne par exemple ?
Merci.
Cyrille
 
Dernière édition:

Cyrille72

XLDnaute Nouveau
Merci beaucoup, vous avez bien saisi ma demande.
Par contre pourquoi ne fonctionne t elle pas quand je la copie dans mon tableau originel, avec $D$11:$H500 pour la plage et $M$3 , $N$3 et $O$3 pour les cellules avec valeur de recherche...
j'ai juste modifié les numéro et ça ne fonctionne plus... c'est bizarre.
Pointilleux ce Excel, je ne trouve pas la subtilité...
En tout cas merci pour ton efficacité ! bravo !
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

Votre formule de MFC est incorrecte. Votre formule :
VB:
=SI(ESTNUM(EQUIV($M$3;$D11:$H500;0)+EQUIV($N$3;$D11:$H500;0)+EQUIV($O$3;$D11:$H500;0));NB.SI($M$3:$O$3;D11)>0;FAUX)

Pour une cellule donnée (ex: D11), on cherche si dans sa ligne on trouve les valeurs en ligne 11.
La ligne de la cellule est $D11:$H11 et non $D11:$H500 qui est le tableau dans son entier.

La formule correcte est donc :
VB:
=SI(ESTNUM(EQUIV($M$3;$D11:$H11;0)+EQUIV($N$3;$D11:$H11;0)+EQUIV($O$3;$D11:$H11;0));NB.SI($M$3:$O$3;D11)>0;FAUX)

Voir fichier joint.
 

Pièces jointes

  • Cyrille72- trouver et surligner- v3.xlsx
    46.2 KB · Affichages: 6
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Décidément j'ai du mal à comprendre certaines fonctions....
En fait il faut sélectionner la 1ere ligne .
Il n'est pas toujours évident de faire les formules pour les MFC.

Quand vous sélectionnez une zone pour la MFC, il faut avoir à l'esprit que vous construisez la formule pour la première cellule de la zone (ici D11).
Et ensuite regarder ce qui se passe dans la formule quand on se déplace soit à droite soit vers le bas (principalement pour bien placer les références absolues ou relatives).

Rappel : on construit la formule de la première cellule de la plage soit D11.
Ici, on veut savoir si la valeur M3 est dans la ligne D11à H11.
On utilise la fonction EQUIV(valeur cherchée ; dans plage ; 0) qui renvoie le rang de la valeur dans plage ou bien renvoie l'erreur #N/A si valeur est absente de la plage. Donc en résumé si valeur est dans plage EQUIV renvoie un nombre et si valeur n'est pas dans plage alors EQUIV renvoie une erreur.

On recherche M3 dans la ligne de D11. La ligne de D11 est D11:H11. La fonction EQUIV s'écrit donc :
EQUIV(M3;D11:H11;0)
Quelque soit la cellule de la plage MFC, c'est toujours la cellule M3 qu'on veut tester. On passe donc M3 en référence absolue $M$3 et la formule devient EQUIV($M$3 ; D11:H11 ; 0)
Maintenant on imagine la formule pour la cellule à droite de D11. EQUIV devient EQUIV($M$3 ; E11:I11 ; 0)
(en fait on imagine qu'on tire la formule en D11 en E11)
Et on s'aperçoit qu'on ne cherche plus $M$3 dans la ligne D11:H11 mais dans E11:I11.
Il faut donc dans la formule de départ fixer les colonnes D à H et pour cela on écrit : $D11:$H11.
La formule EQUIV devient : EQUIV($M$3;$D11:$H11;0)

On va faire la même chose pour rechercher les deux autres valeurs: EQUIV($N$3;$D11:$H11;0) et EQUIV($O$3;$D11:$H11)

L'astuce consiste à dire que si les trois valeurs se trouvent dans la ligne, alors chacune des trois recherches EQUIV retourne un nombre. Et dans ce cas,la somme des trois recherches EQUIV est aussi un nombre.
Si une des trois valeurs n'est pas présente dans la ligne alors sa recherche EQUIV retourne une erreur (#N/A) et par conséquent la somme des trois EQUIV sera forcément aussi une erreur.

Donc si Somme des EQUIV est numérique => les trois valeurs sont dans la ligne
Et si Somme des EQUIV est une erreur => au moins une des trois valeurs n'est pas dans la ligne.

On a donc deux cas pour la cellule D11, SI (somme des EQUIV est numérique, il faut colorer la cellule D11 si elle contient un des trois nombres recherchés sinon somme des EQUIV est une erreur et on ne colore pas la cellule D11)

Savoir si D11 est une des trois valeurs recherchées est simple : On compte le nombre de fois où D11 est dans la plage soit NB.SI($M$3:$O$3;D11). Si D11 est une des trois valeurs alors NB.SI($M$3:$O$3;D11) est supérieur à zéro.
La plage des trois valeurs est en référence absolue puisque cette plage est fixe quelque soit la cellule de la MFC.

On colore la cellule quand la formule de MFC retourne VRAI et on ne colore pas la cellule quand la formule de la MFC retourne FAUX.

On aboutit à la formule :
SI (somme des EQUIV est numérique, il faut colorer la cellule D11 si elle contient un des trois nombres recherchés sinon somme des EQUIV est une erreur et on ne colore pas la cellule D11) qui s'écrit:

SI (somme des EQUIV est numérique, il faut colorer la cellule D11 si NB.SI($M$3:$O$3;D11) >0 sinon somme des EQUIV est une erreur et on ne colore pas la cellule D11) qui s'écrit:

SI (somme des EQUIV est numérique ; NB.SI($M$3:$O$3;D11) >0 ; FAUX) qui s'écrit:

SI ( ESTNUM( EQUIV($M$3;$D11:$H11;0)+EQUIV($N$3;$D11:$H11;0)+EQUIV($O$3;$D11:$H11;0) ) ; NB.SI($M$3:$O$3;D11) >0 ; FAUX)

nota : on peut raccourcir la condition concernant la somme des trois EQUIV ce qui donne :
=SI(ESTNUM(SOMMEPROD(EQUIV($M$3:$O$3;$D11:$H11;0)));NB.SI($M$3:$O$3;D11)>0;FAUX)

 
Dernière édition:

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…