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

[gthe]

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).

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

 

[gthe]

Une formule commençant par =ET ne saurait être un nombre.
Essayez en spécifiant des cellule, éventuellement nommées, contenant les limites, obtenues éventuellement par des formules exploitant les mêmes conditions que la MFC qui grise.

Je vais voir ça, mais ça me paraît bien plus compliqué que ce que j'imaginais...
Mais je comprends l'idée : si j'arrive à extraire le min et le max de ma sélection, le dégradé de couleurs ne s'opèrera que pour cet intervalle... C'est vraiment malin !

Sachant que ma formule improbable =ET(...) permet de sélectionner un ensemble de cases, comment puis-je mettre dans une cellule le min de cette sélection ? (je ferai pareil pour le max).
Ensuite, il me suffira de monter la MFC grise en position n°1 puis à cocher interrompre si vrai.
Et même si la plage de la MFC colorée restera inchangée, elle ne s'appliquera de toute manière qu'entre les minimas et les maximas des cases écrites en bleu... C'est brillant !

Si la question en gras est résolue, il y a donc moyen de résoudre ce problème.
Merci !

 

[Dranreb]

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

 

[gthe]

Je vais explorer cette piste dès demain soir ! Mais en effet, on touche quelque chose d'intéressant !
Après je ne comprends pas le "E1+50" ni le "validée en matriciel", mais oui l'idée est là.

 

[Dranreb]

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]

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

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

A creuser...

 

[Dranreb]

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.

 

[gthe]

Même en mettant -1E+50 dans le premier cas, la valeur -1E+50 m'est retournée.
C'est peut être la condition qui ne passe pas, je ne sais pas
Pas si simple j'ai l'impression.

 

[Dranreb]

Dans la barre de formule, la formule de la cellule est-elle bien encadrée d'accolades ?
Sinon c'est que vous ne l'avez pas validée en matriciel.

 

[Dranreb]

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.

 

[gthe]

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 K6F105
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]

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 :

=SI($B$1<>"";MIN(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;1E+50));"")

trouve 1,329313
et en D5 :

=SI($B$1<>"";MAX(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;-1E+50));"")

trouve 50,46924

 

[gthe]

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)

 

[Dranreb]

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]

Il faut que j'arrive à regarder, mais ça ne me paraît pas simple tout ça

 

[gthe]

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 :

=SI($B$1<>"";MIN(SI($B6:$B105=--GAUCHE($B$1;1);$K$6:$DF$105;1E+50));"")

trouve 1,329313
et en D5 :

=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