Excel : Gare à l’ajout de lignes dans vos formats conditionnels!

Publié le 29 janvier 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Excel Format Conditionnel

Excel : Gare à l’ajout de lignes dans vos formats conditionnels!

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.

 

Excel Format Conditionnel

 

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.

 

Excel Format Conditionnel

 

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.

 

Excel Format Conditionnel

 

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.

 

Excel Format Conditionnel

 

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!

 

Excel Format Conditionnel

 

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.

 

Excel Format Conditionnel

 

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.

 

Excel Format Conditionnel

 

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

 

Excel Format Conditionnel

 

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 :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Tableaux de bord - Niveau 2
CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Scroll to Top