Aujourd’hui, je partage avec vous un truc Excel qui vous sera utile si vous utilisez de la mise en forme conditionnelle dans des fichiers qui peuvent être altérés par des utilisateurs, notamment par l’ajout de lignes. Dans l’exemple ci-dessous, je souhaite que la cellule “Statut” soit hachurée, lorsqu’il y a un changement de client (colonne B). Autrement dit, je veux que l’utilisateur du fichier indique le statut à l’aide d’un menu déroulant, pour chaque client.
Dans un premier temps, voici un petit extrait d’un tel tableau.
Format conditionnel : Solution 1
Si les utilisateurs du fichier étaient dans l’incapacité de procéder à l’ajout de lignes, la formule suivante serait très efficace comme règle de mise en forme conditionnelle ($B5=$B4). Mais dans notre exemple, les utilisateurs du fichier peuvent insérer des lignes. Observons quand même l’effet de cette première solution.
Sur l’image ci-dessous, on voit que toutes les données concernent le client 1, donc le deuxième et le troisième statut sont hachurés. À noter que dans ce fichier, les numéros des clients doivent être en ordre croissant.
De même, on voit que lorsque l’on change de numéro de client, la cellule de statut n’est plus hachurée, ce qui indique à l’utilisateur qu’il faut se prononcer sur la valeur de cette cellule.
Même chose sur l’image ci-dessous, avec une variation dans les numéros de clients. Donc jusque-là, tout fonctionne comme prévu!
Mais supposons maintenant que quelqu’un procède à un ajout de ligne au-dessus de la ligne 7. Dans ce cas, tout pourrait paraître normal mais ne pas l’être. En effet, si vous placez votre curseur dans la cellule D8 et regardez la formule de mise en forme conditionnelle, celle-ci est erronée et tente de rapprocher les valeurs de B8 et de B6, plutôt que de comparer la ligne actuelle avec la ligne précédente.
Ce faisant, dans le contexte ci-dessous, on voit bien que la mise en forme conditionnelle ne fonctionne pas bien. En effet, la cellule D8 devrait être hachurée et elle ne l’est pas.
Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel. |
|---|
Format conditionnel: Solution 2
C’est pourquoi il est plutôt recommandé de travailler avec la fonction INDIRECT() et la fonction LIGNE() (ou ROW() en anglais), qui fonctionnera à tout coup, même lorsqu’un utilisateur ajoutera des lignes dans le tableau. Dans l’image ci-dessous, on voit qu’avec cette formule, l’ajout de ligne n’abîme pas la mise en forme conditionnelle des cellules hachurées. Bien sûr, cela suppose que vous insériez une ligne “copiée”.
Vous aimez cet article?
Je vous invite à le partager dans vos différents réseaux sociaux et également à laisser un commentaire plus bas.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Afin d’approfondir vos connaissances avec les tableaux Excel, nous vous invitons à vous inscrire à notre formation Excel – Tableaux de bord (niveau 2).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
