XL 2019 Mise en forme conditionnelle d'une intersection entre lignes et colonnes

gthe

XLDnaute Junior
Bonjour,
Un petit problème qui va prendre 5 minutes aux initiés, mais que je n'arrive pas à mettre en oeuvre.
Dans le tableau que je vous ai envoyé en PJ, dont les coordonnées sont de 1 à 10 en horizontal (→) et de 1 à 10 en vertical (↓), je souhaiterais mettre en surbrillance (par exemple en jaune clair), l'intersection entre une ligne de coordonnées X et une colonne de coordonnées Y, en fonction des valeurs des coordonnées X (→) et Y (↓).

Quelle manipulation devrais-je effectuer ?

En vous remerciant !
G.
 

Pièces jointes

  • démo excel.xlsx
    8.9 KB · Affichages: 38
Solution
@gthe, @Lolote83, @Marcel32, le fil,

A) pour les bordures colorées : tant mieux si c'était que pour expliquer le propos : ça simplifie ! :)

B) pour l'adaptation, voici la procédure à suivre :

1) sélectionne toute la plage du tableau dont la 1ère ligne est juste sous les numéros de lignes sur fond orange et dont la 1ère colonne est juste à droite des numéros de colonnes sur fond orange ; ici, c'est donc : F3:O12 ; bien sûr, cette plage sera plus grande pour ton tableau avec plus de colonnes et de lignes ; si tu as par exemple 3 colonnes de plus et 5 lignes de plus, ce sera : F3:R15 ; la suite de cette démo est toujours pour l'exemple...

Lolote83

XLDnaute Barbatruc
SAlut GTHE,
Voici un résultat en 2 formules ou voir résultat CISCO que je salut au passage
.
1619269063334.png

@+ Lolote83
 

soan

XLDnaute Barbatruc
Inactif
@gthe, le fil,

au cas où tu aurais inversé le sens des flèches de A2 et A3,
utilise plutôt cette autre version de mon fichier précédent.​

règle de MFC pour $E$2:$N$11 : =ET($B$2<>"";$B$3<>"";E$1=$B$2;$D2=$B$3)

c'est la règle de MFC du post #2 de CISCO, complétée un peu.

couleur de remplissage : jaune clair

Image.jpg




si tu préfères une mise en évidence plus significative, c'est mieux que tu choisisses la solution de Lolote83 avec 2 règles de MFC ➯ une couleur verte pour la colonne, et une couleur orange pour la ligne. :)

si tu préfères que K4 soit en orange, tu peux remonter la règle de MFC du orange au-dessus de la règle de MFC du vert ; puis pour la 1ère règle de MFC du orange, coche la case de droite "Interrompre si Vrai".​

soan
 

Pièces jointes

  • démo excel v2.xlsx
    9.4 KB · Affichages: 9
Dernière édition:

gthe

XLDnaute Junior
Bonjour,
Je viens de voir vos réponses !
C'est super, un grand merci @Lolote83, @CISCO, @soan ! je me sens bête de pas y avoir pensé ! Et merci @soan pour les deux versions du fichier !

Voici ma dernière question à ce sujet (un peu plus compliquée), mais on reste dans le même thème :)

Chacune des coordonnées 1 à 10 de mon tableau est en fait affectée à un nombre (1 ou 2) que j'ai affiché sur fond orange.
Une liste conditionnelle me permet d'afficher les intersections 1 / 1, 1 / 2, 2 / 1 et 2 / 2.

Q.1
1) Ainsi, je souhaiterais réussir, par exemple si la liste indique "1 / 2", à afficher en surbrillance (ou avec une mise en forme spécifique), l'intersection des "1" dans les chiffres sur fond orange disposés en colonne (en ↓) et des "2" sur fond orange disposés en ligne (en →).
2) Je souhaiterais ainsi réussir à afficher les intersections possibles en fonction des instructions de la liste conditionnelle.

Q.2
Faut-il réaliser une manipulation spéciale ou est-ce les mêmes formules si mon graphique est présenté "en triangle" (pour éviter les valeurs qui seraient redondantes par symétrie) ? Ou est-ce la même formule ? Comment l'appliquer.


Voici un nouveau fichier !
Merci beaucoup !
 

Pièces jointes

  • démo excel2.0.xlsx
    12.3 KB · Affichages: 8
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
@gthe, le fil,

voici une 1ère partie ; règle de MFC pour $F$3:$O$12 :

=ET($B$3<>"";$E3=--GAUCHE($B$3);F$2=--DROITE($B$3))

couleur de remplissage : vert

Image.jpg


note que j'ai préféré ne pas mettre tes bordures bleues de lignes et tes bordures oranges de colonnes ; car sinon, ça fait bien trop surchargé, ce qui nuit à la lisibilité ! :confused:

soan
 

Pièces jointes

  • démo excel2.0.xlsx
    12.2 KB · Affichages: 6

gthe

XLDnaute Junior
@soan les bordures, c'était juste pour expliquer le propos, mais en réalité je n'ai juste besoin que de la coloration des cases :)
Merci, beaucoup, je vais déjà regarder ça :D

Edit : =ET($B$3<>"";$E3=--GAUCHE($B$3);F$2=--DROITE($B$3))
==> Si je souhaite le reproduire sur un fichier bien plus volumineux (4Mo...), d'où mon petit fichier "exemple", comment devrais-je adapter cette formule ? (Je n'arrive pas très bien à comprendre comment elle fonctionne).

Pour le graphique "triangle", ce sera peut être plus difficile ! Merci :)
 
Dernière édition:

TooFatBoy

XLDnaute Barbatruc
Bonjour,

GAUCHE($B$3) prend le premier caractère du contenu de la cellule $B$3, donc ici 1.

DROITE($B$3) prend le dernier caractère du contenu de la cellule $B$3, donc ici 2.

$E3 permet d'utiliser la colonne E à partir de la ligne 3, pour comparer avec le 1er caractère de la cellule $B$3.

F$2 permet d'utiliser la ligne 2 à partir de la colonne F, pour comparer avec le dernier caractère de la cellule $B$3.
 
Dernière édition:

Lolote83

XLDnaute Barbatruc
Bonjour Soan.
Juste une question, pourquoi les -- pour tes formules =ET($B$3<>"";$E3=--GAUCHE($B$3);F$2=--DROITE($B$3))
Est-ce par rapport à une correspondance de format texte/nombre ?
Si c'est ça, on aurait pu obtenir le même résultat par =ET($B$3<>"";$E3=CNUM(GAUCHE($B$3));F$2=CNUM(DROITE($B$3)))
Ai-je bien compris car je ne connaissais pas les --
Merci
@+ Lolote83
 

gthe

XLDnaute Junior
@Marcel32 merci pour ces précisions et en particulier pour avoir fait le lien avec les données d'entrée ! je les connaissais effectivement ces formules mais merci de les avoir remis en contexte, après, ce qui m'a troublé, comme le dit @Lolote83, ce sont les symboles "--". Mais bon, si c'est juste pour adapter le format texte ou quelque chose comme ça, je prends pour acquis. En tout cas c'est super intéressant, merci :)

Edit : merci @Lolote83 pour la fonction CNUM !
 
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
@gthe, @Lolote83, @Marcel32, le fil,

A) pour les bordures colorées : tant mieux si c'était que pour expliquer le propos : ça simplifie ! :)

B) pour l'adaptation, voici la procédure à suivre :

1) sélectionne toute la plage du tableau dont la 1ère ligne est juste sous les numéros de lignes sur fond orange et dont la 1ère colonne est juste à droite des numéros de colonnes sur fond orange ; ici, c'est donc : F3:O12 ; bien sûr, cette plage sera plus grande pour ton tableau avec plus de colonnes et de lignes ; si tu as par exemple 3 colonnes de plus et 5 lignes de plus, ce sera : F3:R15 ; la suite de cette démo est toujours pour l'exemple concret du fichier, donc pour F3:O12.

2) si une de ces cellules a déjà une couleur de remplissage, enlève-là : clic droit sur une cellule quelconque de la plage F3:O12 ; choisis l'item "Format de cellule" ; onglet "Remplissage" ; clique sur "Aucune couleur" ; clique sur le bouton "OK".

3) F3:O12 est toujours sélectionné ; onglet "Accueil", groupe "Style", clique sur "Mise en forme conditionnelle", puis sur "Gérer les règles..." ; si la règle de MFC existe déjà, tu dois juste la sélectionner puis cliquer sur le bouton "Modifier la règle..." ; sinon, tu dois la créer à partir de rien : clique sur le bouton "Nouvelle règle...".

4) dans la fenêtre "Nouvelle règle de mise en forme", sélectionne le dernier type de règle :
"▶ Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué"

5) un peu plus bas, clique sur la longue case vide qui est sous ce texte :
"Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie".

6) là, tout le problème est de mettre la bonne formule ; ici, c'est :
=ET($B$3<>"";$E3=--GAUCHE($B$3);F$2=--DROITE($B$3))
attention : cette formule ne changera pas, même si ton tableau est plus grand ; car tout est déjà ok, y'a aucune adaptation à faire ; il suffisait juste de choisir la bonne plage au point 1)

7)
explication de la formule :
* $B$3 ne doit pas être vide ; d'où la 1ère condition du ET() : $B$3<>""
* la 1ère cellule de la plage sélectionnée est F3 ; par rapport à cette cellule : a) $E3 est la cellule de la ligne 3 (le 3 est variable, donc pour les lignes 3 à 12 de F3:O12), colonne invariable E ; b) F$2 est la cellule de la colonne F (le F est variable, donc pour les colonnes F à O de F3:O12), ligne invariable 2.
* pour B3 = "1 / 2" : GAUCHE($B$3) retourne le 1er caractère de gauche de B3 : "1", qui est du texte ; le 1er signe moins le transforme en nombre -1 ; le 2ème signe moins rétablit le signe d'origine, car -(-1) = +1 = 1 ; DROITE($B$3) retourne le dernier caractère de droite de B3 : "2", qui est du texte ; même principe : double signe moins pour le transformer en nombre sans qu'il y aie un changement de signe apparent ; on pourrait aussi faire : =ET($B$3<>"";$E3=GAUCHE($B$3)*1;F$2=DROITE($B$3)*1) ; transformation correcte de texte en nombre, mais au niveau processeur, un simple changement de signe est moins gourmand qu'une multiplication (même s'il s'agit en fait d'un double changement de signe : c'est l'opération booléenne NOT qui est exécutée au lieu de l'opération de multiplication).

8) après avoir saisi la bonne règle de MFC, cliquer sur le bouton "Format..." pour mettre une couleur de remplissage vert ; cette étape est inutile pour une modification de la règle de MFC, sauf bien sûr si on veut changer le format de cellule à appliquer.

C) pour le graphique triangle, j'ai rien à proposer, alors je passe. 😜

soan
 

Lolote83

XLDnaute Barbatruc
Re bonjour.
Merci Soan pour l'explication car du coup, je n'y étais pas du tout.
Mais effectivement - par - = + (mathématiquement parlant) donc écrit comme ça dans ta formule, cela m'a interpellé.
Tout ceci est donc bien une transformation de texte en nombre que j'avais du coup contourné par la fonction CNUM(). Mais je garde sous le coude ta solution
Bien vu aussi le *1) dans l'expression =ET($B$3<>"";$E3=GAUCHE($B$3)*1;F$2=DROITE($B$3)*1)
Merci merci merci
@+ Lolote83
 

gthe

XLDnaute Junior
C'est génial, merci beaucoup pour toutes ces explications.

Il y a juste un point que je n'arrive pas à comprendre. Je suis parti du principe que par symétrie, la disposition "triangle" du graphique ne changeait rien étant donné qu'on se retrouve avec un tableau de mêmes dimensions que s'il n'était pas en "triangle". C'est juste que certaines mises en forme ne s'appliquent pas, dans la partie noire essentiellement.

Mais par exemple, si on prend l'intersection (avec les coordonnées en fond blanc) 2;1 , 24;23 ou 25;24, on a bien l'intersection de deux "2", ce qui donne "2/2", mais pourtant il n'y a pas de coloration en "blanc".
Dans la liste des MFC, j'ai bien fait remonter la priorité de cette mise en forme (formalisées en gras et blanc) en 1er. Mais elles n'apparaissent pas (pareil pour d'autres essais avec d'autres configurations : 1 / 1, ...).
Me serais-je trompé dans la formule ?


Merci beaucoup !
On y est presque :)

1619282466148.png


Interface entière (avec les n° de colonnes et de lignes) :

1619282982729.png


Liste des mises en forme appliquées à toutes les cellules "colorées :

1619282856244.png


En vous remerciant !
 

soan

XLDnaute Barbatruc
Inactif
@gthe, le fil,

attention : dans le fichier initial, la cellule où tu saisis l'instruction "1 / 2" est B3 ; donc dans la règle de MFC, il y a 3× $B$3 (avec 2 signes "$" pour fixer la colonne ET la ligne) :​

=ET($B$3<>"";$E3=--GAUCHE($B$3);F$2=--DROITE($B$3))



dans ta formule :

=ET($BT8<>"";$BM8=--GAUCHE($BT$12);BN$11=--DROITE($BT$12))

a) il manque un signe "$" pour $BT8 : ce serait plutôt $BT$8 ; b) quelle est donc la cellule où tu saisis une instruction telle que "1 / 2" ? est-ce $BT$8 ou $BT$12 ? au cas où cette « case » est une fusion de cellules, tu peux mettre seulement les coordonnées de la cellule du coin supérieur gauche de la fusion ; c'est dit selon la grosse case cyan (ou turquoise ?) située au dessus de ton graphique triangulaire.​

soan
 

Discussions similaires

Réponses
12
Affichages
158

Statistiques des forums

Discussions
311 720
Messages
2 081 897
Membres
101 833
dernier inscrit
sandra25