XL 2019 Mise en forme conditionnelle (dégradé de couleurs) sur un ensemble de cellules déterminé par une formule

gthe

XLDnaute Junior
Bonjour,

Je souhaite réaliser une mise en forme (dégradé de couleur selon les valeurs des cellules) qui ne s'applique uniquement qu'à des cellules spécifiquement mises en forme à l'aide d'une formule.

En d'autres termes le tableau ci-dessous contient plein de cases grisées et d'autres actives (et j'avais fait la mise en forme "dégradé" sur l'intégralité du tableau. Mais certaines cases avec des valeurs super élevées sont "masquées" car elles ne m'intéressent pas.

L'idée serait, que pour seulement les cases affichées (sachant que celles-ci changent en fonction d'une liste déroulante) et quelque soit l'état de la liste déroulante, la mise en forme conditionnelle "dégradée de couleurs" ne s'applique seulement qu'à l'ensemble des cellules "visibles" (je dois voir une cellule tout en rouge et une cellule tout en vert et toutes les valeurs intermédiaires, pour chacun des cas de la liste déroulante).

1620149452647.png


Voici un fichier réduit avec les infos nécessaires. La commande en haut à droite permet d'afficher les différents cas de figure (X/Y n'affiche que les cases telles que X = 1 ou 2 selon les "1" ou "2" affichés en colonne B ; et telles que Y = 1 ou 2 selon les "1" ou "1" affichés en ligne 4)

En vous remerciant :)
 

Pièces jointes

  • Copie réduite fichier.xlsm
    239.6 KB · Affichages: 50

Dranreb

XLDnaute Barbatruc
Profitez en donc pour afficher quelque part ces Min et Max dans la feuille, il me semble que ça ne peut qu'être intéressant. Moi je les nommerais tant qu'à faire. Ainsi dans les MFC il ne restera plus qu'à mettre =MonMini et =MonMaxi (si toutefois c'est comme ça que vous les aurez nomméees)
Pour le mini, peut être une formule de la forme =MIN(SI(Condition;LaPlage;E1+50)) validée en matriciel
 

Dranreb

XLDnaute Barbatruc
Validé en matriciel ça veut dire par combinaison de touches Ctrl+Shift+Entrée
et le 1E+50 c'est pour être sûr d'être supérieur à tout nombre à considérer quand la condition n'est pas remplie
j'avais inversé, désolé.
 

gthe

XLDnaute Junior
Je ne vais pouvoir répondre que demain, mais j'obtiens 1E+50 en guise de min ou de max.

1620254625931.png


En utilisant mon autre fonction conditionnelle, j'obtiens les valeurs min et max de l'ensemble du tableau 100x100

1620254785303.png


A creuser... :)
 

Dranreb

XLDnaute Barbatruc
Pour le MAX il faut prendre -1E+50 si la condition n'est pas remplie bien sûr. Ou même 0 tout simplement s'il ne peut pas y avoir de nombre négatif. Si vous obtenez 1E+50 pour le MIN c'est que la condition n'est jamais remplie et qu'il ne prend donc jamais de valeur plus petite.
 

Dranreb

XLDnaute Barbatruc
Je viens seulement de voir qu'on peut apercevoir des formules sur vos images.
Avec ces formules ce n'est pas la peine de valider en matriciel: soit on prend le MIN(… soit on prend 1E+50.
La validation matricielle ne se justifierait que si des comparaisons portaient sur toutes les lignes d'une colonne, afin qu'il fabrique un tableau de Boolean pour l'ensemble des lignes dont celles soit à appliquer soit non.
Au lieu de joindre des images vous auriez mieux fait de joindre le classeur.
Comme ça j'aurais pu corriger, tester et vous indiquer la bonne formule.
À priori dans votre 1ère formule c'est $B6 qui est à remplacer par $B6:$B105 pour qu'il puisse faire les liens ligne à ligne avec K6:DF105
Là oui, la validation matricielle est nécessaire.
 
Dernière édition:

gthe

XLDnaute Junior
Je viens seulement de voir qu'on peut apercevoir des formules sur vos images.
Avec ces formules ce n'est pas la peine de valider en matriciel: soit on prend le MIN(… soit on prend 1E+50.
La validation matricielle ne se justifierait que si des comparaisons portaient sur toutes les lignes d'une colonne, afin qu'il fabrique un tableau de Boolean pour l'ensemble des lignes dont celles soit à appliquer soit non.
Au lieu de joindre des images vous auriez mieux fait de joindre le classeur.
Comme ça j'aurais pu corriger, tester et vous indiquer la bonne formule.
À priori dans votre 1ère formule c'est $B6 qui est à remplacer par $B6:$B105 pour qu'il puisse faire les liens ligne à ligne avec K6:DF105
Là oui, la validation matricielle est nécessaire.
@Dranreb le classeur est joint dès mon premier post "Copie réduite du fichier - 239,6 KB".
Je veux bien que vous le regardiez.
En vous remerciant,
G.
 

Dranreb

XLDnaute Barbatruc
Il n'y a pas de formule à corriger en C5 et D5 de votre 1er classeur.
Par ailleurs il y a de #REF! à partir de la ligne 74
Après avoir supprimé ça, en C5, validé par Ctrl+Maj+Entrée :
Code:
=SI($B$1<>"";MIN(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;1E+50));"")
trouve 1,329313
et en D5 :
Code:
=SI($B$1<>"";MAX(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;-1E+50));"")
trouve 50,46924
 
Dernière édition:

gthe

XLDnaute Junior
Mmh, au temps pour moi, c'était un fichier volant que je n'avais pas enregistré.
J'avais bidouillé à peu près ça...
(le nom du fichier est le même, mais j'ai rajouté les 2 formules en C5 et D5)
 

Pièces jointes

  • Copie réduite fichier.xlsm
    239.8 KB · Affichages: 3

Dranreb

XLDnaute Barbatruc
Est-ce que vous ne pourriez pas arriver à simplifier les formules tout en faisant en sorte qu'elle renvoient "" si en colonne B on n'a pas --GAUCHE($B$1;1) ni en ligne 4 --DROITE($B$1;1) ?
Et tâchez de mettre comme expression $J$5* …
Non, trop compliqué. Je vous assure qu'une fonction personnalisée serait beaucoup plus simple !
Quel nom pourrait-on lui donner ?
On pourrait n'avoir qu'un seul calcul d'inverse de somme d'inverses en ayant préalablement positionné les variables qui y interviennent selon ce qu'il y a en colonne B et en ligne 4, ce qu'on ne peut pas bien faire dans les formules.
 

gthe

XLDnaute Junior
Il n'y a pas de formule à corriger en C5 et D5 de votre 1er classeur.
Par ailleurs il y a de #REF! à partir de la ligne 74
Après avoir supprimé ça, en C5, validé par Ctrl+Maj+Entrée :
Code:
=SI($B$1<>"";MIN(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;1E+50));"")
trouve 1,329313
et en D5 :
Code:
=SI($B$1<>"";MAX(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;-1E+50));"")
trouve 50,46924
Oui, le tableau peut contenir 9900 données normalement (100² - 100 de la diagonale). Dans le cas que j'ai envoyé en exemple, il doit seulement avoir 73² - 100 valeurs renseignées dans le tableau je pense
 

Dranreb

XLDnaute Barbatruc
J'ai trouvé ça, plutôt, validées par Ctrl+Maj+Entrée :
En C5 :
Code:
=SI($B$1<>"";MIN(SI($B$6:$B$105&" / "&$K$4:$DF$4=$B$1;$K$6:$DF$105;1E+50));"")
En D5 :
Code:
=SI($B$1<>"";MAX(SI($B$6:$B$105&" / "&$K$4:$DF$4=$B$1;$K$6:$DF$105;-1E+50));"")
 

Discussions similaires

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T