Excel : Mise en forme conditionnelle avec référence supprimée

Dans la plupart des cas d’application de la mise en forme conditionnelle dans Excel, la condition et la mise en forme se réfèrent à la même cellule : (Forme mise en demeure). Si les deux divergent, il faut prendre des précautions particulières, ce que cet article explique à l’aide d’un plan d’équipes.

La mise en forme conditionnelle avec sa propre formule

Supposons que vous ayez un plan construit dans Excel pour répartir les employés dans différentes équipes. Il faut maintenant le mettre en valeur de manière à ce que les champs situés en dessous d’une cellule contenant une date deviennent rouges si la date au-dessus est un dimanche. Pour cela, il n’est pas possible d’utiliser une fonction prédéfinie, mais il faut utiliser une formule que l’on a écrite soi-même dans la condition. Voici un tableau de sortie possible, qui ne représente qu’une structure de base :

Tout d’abord, il est important de savoir comment reconnaître qu’une date tombe sur un dimanche. Cela se fait simplement à l’aide de la fonction JOURS DE LA SEMAINE(), qui crache le jour sous forme numérique pour une cellule. Pour un dimanche, elle renvoie la valeur “1”.

Pour utiliser WOCHENTAG() dans une mise en forme conditionnelle, procédez comme suit

Sélectionnez toute la partie intérieure du tableau. Dans notre exemple, ce serait B4 à E6.

Dans l’onglet de départ, cliquez sur Mise en forme conditionnelle – Nouvelle règle – Formule de détermination….

Comme formule, entrez : =JOUR DE LA SEMAINE(B$3)=1

Ensuite, vous choisissez encore une mise en forme, par exemple le remplissage avec un fond rougeâtre.

Le résultat ressemble alors à ceci :

Plan d’équipe dans Excel après l’intégration d’une mise en forme conditionnelle

Pour essayer de voir si cela fonctionne vraiment de cette manière, vous pourriez écrire la date du 11 juin 2017 comme valeur de date au-dessus d’une des colonnes encore blanches. Comme c’est aussi un dimanche, les champs en dessous doivent devenir rouges dès que vous avez saisi la nouvelle date.

Si vous souhaitez mettre en couleur non seulement le dimanche, mais aussi le samedi, modifiez la formule de la manière suivante : =JOUR($B3;2) 5  Si vous souhaitez savoir comment cette variante peut saisir les deux jours, lisez ici : Excel : Distinguer visuellement le week-end et les jours de la semaine.

C’est pourquoi la formule a besoin du signe dollar

Si vous sélectionnez une plage pour la mise en forme conditionnelle et que vous spécifiez une cellule de référence dans la formule, comme ici B3, Excel utiliserait cette formule pour la cellule la plus à gauche en haut et adapterait la référence pour chaque autre cellule. Dans la cellule B5, par exemple, la mise en forme testerait le contenu de B4, qui ne contient aucune valeur de date.

Avec le $, Excel laisse cette adaptation se faire dans le sens vertical. Par la suite, toutes les mises en forme en dessous de B3 se réfèrent donc exactement à cette cellule. Une colonne plus à droite, Excel modifie automatiquement la référence à C3, car il n’y a pas de signe dollar devant la partie adresse. Le résultat est donc exactement ce que nous voulons obtenir.

Si vous écriviez par exemple =JOUR DE LA SEMAINE($B$3)=1 comme contenu de la formule, Excel formaterait tous les champs sélectionnés uniquement en référence à B3. Le signe du dollar fixe pour ainsi dire la coordonnée correspondante.

Si vous souhaitez lire davantage sur le sujet, vous trouverez ici un aperçu de la mise en forme conditionnelle avec Excel.