La mise en forme d'un tableau Excel ne doit jamais être négligée. Cependant, lorsque cette mise en forme peut intervenir automatiquement par reconnaissance dynamique des informations, il s'agit d'un gain de temps fort appréciable. Excel offre des outils puissants, notamment la mise en forme conditionnelle, pour automatiser l'application de bordures en fonction du contenu des cellules. Ce tutoriel explore diverses méthodes pour attribuer des bordures automatiques, en détaillant les formules et les logiques sous-jacentes.
Comprendre les fondamentaux de la mise en forme conditionnelle pour les bordures
Les règles de mise en forme conditionnelle permettent de faire réagir les cellules dynamiquement en fonction de leur contenu. Ces règles peuvent s'empiler, et la dernière posée est prioritaire. Il est donc crucial de respecter une certaine chronologie lors de la création de multiples règles. L'objectif est d'attribuer automatiquement une bordure d'encadrement à une saisie standard, puis de laisser les nouveaux réglages prendre le pas sur les anciens lorsqu'il s'agit d'un autre type de données, par empilement.
Pour que ta formule de mise en forme conditionnelle soit correcte, il faut qu'elle renvoie VRAI ou FAUX ; c'est tout ce dont a besoin ta mise en forme conditionnelle, tu as donc pas mal de possibilités. Une formule comme =NBVAL($A1)=1 permet de vérifier si la cellule A1 est remplie ou non. Il est important de noter que la colonne de A1 est fixée, mais pas la ligne. Lorsque tu écris une formule de cette manière, sache qu'elle ne vaut que pour la première cellule en haut à gauche qui recevra la mise en forme. Ensuite, c'est comme pour une formule : quand tu passeras de B1 à C1, A1 restera A1 ici car la colonne a été fixée. Sinon, la formule aurait vérifié que B1 soit remplie, et pour la cellule B2, la formule vérifiera que A2 est remplie.
Application de bordures basées sur le contenu d'une autre cellule
Pour attribuer des bordures (cadre fin) en fonction d'une autre cellule, une mise en forme conditionnelle peut être appliquée sur une plage spécifique, par exemple B1:L20. La formule =NBVAL($A1)=1 permet de vérifier si la cellule A1 est remplie. La fixation de la colonne de A1 (avec le signe $) est essentielle pour que la règle s'applique correctement sur la plage sélectionnée. Ainsi, lorsque la formule est évaluée pour B1, elle vérifie A1. Pour B2, elle vérifie A2, et ainsi de suite. Cette approche simple offre une grande flexibilité pour conditionner l'apparition des bordures.
Utiliser la MISE EN FORME automatique CONDITIONNELLE dans EXCEL - Formation vidéo
Automatisation des bordures pour des saisies standards
Une saisie standard concerne n'importe quelle cellule. Nous proposons de lui attribuer automatiquement une bordure d'encadrement. Pour ce faire, il faut sélectionner toutes les cellules en considérant que la cellule A1 est la première d'entre elles. Cette remarque est importante. L'inégalité ainsi posée consiste à vérifier que la cellule n'est pas vide. La cellule A1 doit absolument être défigée, c'est-à-dire qu'aucun dollar ne doit préfixer ses coordonnées. Ainsi, la mise en forme conditionnelle pourra vérifier cet état sur toutes les cellules de la feuille.
La règle de mise en forme conditionnelle devra utiliser une formule simple qui vérifie si la cellule n'est pas vide. Par exemple, si la plage d'application commence en A1, la formule sera =A1<>"". Cette formule sera évaluée pour chaque cellule de la plage sélectionnée, et si elle est VRAI, la bordure sera appliquée.
Gérer les titres avec des règles de mise en forme conditionnelle empilées
Dans un tableau, les titres peuvent nécessiter une mise en forme spécifique avec des bordures particulières. Dans l'exemple illustré, les titres sont automatiquement formatés avec un remplissage, des attributs de police et de bordure. Les règles de mise en forme conditionnelle peuvent être utilisées pour gérer différents types de titres : premier titre, titre du milieu, dernier titre et titre isolé.
Comme l'analyse doit être réalisée sur les cellules de la ligne du dessus et de la colonne en amont, la règle doit débuter son implication à partir de la cellule B2. Il ne s'agit pas d'attribuer un nom à une plage, mais bien de la sélectionner. Avec cette borne inférieure arbitraire, nous prévoyons suffisamment de marge pour impacter l'ensemble des travaux sur une feuille.
Quatre règles de mise en forme conditionnelle sont nécessaires, et pour recouper tous les critères, la fonction ET est indispensable.
Règle 1 : Le premier titre du tableau
Le premier titre du tableau doit posséder une bordure sauf à droite, avec un remplissage et une police adaptés. Il est décelé en débutant le raisonnement chronologique à partir de la cellule B2, en cherchant à savoir si la cellule du dessus (B1) et la cellule sur sa gauche (A2) sont vides, quand dans le même temps, son propre contenu existe (B2<>'').
La formule pour cette règle pourrait être similaire à :=ET(B2<>"",B1="",A2="") (pour la cellule B2 comme point de départ de l'analyse).Les attributs de format à appliquer incluent une bordure supérieure, inférieure et gauche, un remplissage spécifique et une police adaptée.
Règle 2 : Le titre isolé
Le titre isolé doit posséder une bordure intégrale avec les mêmes attributs de format que les autres. Il est décelé lorsque les cellules du dessus et à droite sont vides.
La deuxième règle concerne le titre isolé. Elle doit donc proposer une bordure d'encadrement complète. La formule pour détecter un titre isolé (en partant de B2) pourrait être :=ET(B2<>"",B1="",C2="",A2="",B3="") ou une variation en fonction des critères spécifiques de "titre isolé".
Règle 3 : Le titre du milieu
Le titre du milieu doit posséder une bordure supérieure et inférieure seulement. Les attributs de remplissage et de police doivent être identiques aux précédents. Les bordures latérales doivent disparaître pour assurer la continuité de l'information.
La troisième règle fait référence au titre du milieu. Elle doit seulement offrir une bordure supérieure et une bordure inférieure. La formule pour un titre du milieu (en partant de B2) pourrait être :=ET(B2<>"",B1="",A2<>"",C2<>"") ou =(B2<>"")*(B1="")*(A2<>"")*(C2<>"")(les multiplications peuvent simuler le ET pour certaines conditions).
Règle 4 : Le dernier titre du tableau
Le dernier titre du tableau doit posséder une bordure sauf à gauche avec des attributs de fond et de texte identiques aux précédents. Il est décelé lorsque les cellules du dessus et à droite sont vides.
La quatrième et dernière règle fait référence au dernier titre, celui situé le plus à droite. Il doit donc offrir une bordure supérieure, une bordure inférieure ainsi qu'une bordure latérale droite. La formule pour un dernier titre (en partant de B2) pourrait être :=ET(B2<>"",B1="",C2="",A2<>"")
Mise en forme spécifique pour les valeurs numériques
Pour les valeurs numériques, nous souhaitons simplement et logiquement appliquer un alignement à droite tout en conservant le retrait dans la cellule. Et malgré cette simplicité apparente, la réalisation de cette règle n'est pas si simple que cela. Vous l'avez sans doute déjà constaté, parmi les attributs conditionnels qu'il est possible de régler, l'alignement est absent. Or, il est prédéfini sur la gauche pour l'ensemble des cellules de la feuille.
Nous exploitons simplement la fonction Excel logique ESTNUM sur la cellule B2 totalement défigée. Elles sont ainsi toutes évaluées dans l'enchaînement.
Pour contourner l'absence d'alignement direct dans la mise en forme conditionnelle, une astuce consiste à utiliser un format de nombre personnalisé. Nous cherchons à forcer l'alignement des nombres sur la droite tout en conservant les données comme des valeurs numériques.
Le format personnalisé à appliquer est * 0" ".Attention, l'espace après l'étoile est impératif. De même, il doit y avoir trois espaces consécutifs dans les guillemets.Le chiffre 0 est le format standard pour les nombres. Le symbole de l'étoile en préfixe, suivi d'un espace, repousse le contenu de la cellule sur sa droite. Les espaces entre les guillemets permettent de conserver un retrait dans la cellule, pour observer un décalage par rapport à la bordure droite.
Comme vous pouvez le voir, nous avons parfaitement réussi à forcer l'alignement des nombres sur la droite. Et comme l'astuce a consisté à utiliser un format, les données sont toujours considérées comme des valeurs numériques.
Mise en forme des résultats de calculs
Les résultats de calculs revêtent forcément une importance supérieure aux données standard. Nous exploitons donc la fonction logique ESTFORMULE sur la cellule B2 complètement défigée pour les analyser toutes.
Si une cellule contient une formule, elle peut être formatée différemment, par exemple avec une couleur de fond distincte ou une bordure particulière pour les identifier rapidement comme des résultats plutôt que des saisies directes. La formule de mise en forme conditionnelle serait =ESTFORMULE(B2) pour la plage applicable.
Enregistrer le fichier en tant que modèle
Pour parfaire la solution et réutiliser facilement ces mises en forme complexes, il convient d'enregistrer le fichier en tant que modèle Excel (.xltx). Cela permet de créer de nouveaux classeurs avec toutes les règles de mise en forme conditionnelle déjà configurées, assurant une cohérence visuelle et un gain de temps considérable.
Considérations supplémentaires pour une mise en forme dynamique efficace
- Priorité des règles : Toujours se rappeler que la dernière règle de mise en forme conditionnelle appliquée est prioritaire. Il est donc essentiel de bien ordonner les règles pour obtenir le résultat souhaité.
- Performance : Bien que la mise en forme conditionnelle soit puissante, un trop grand nombre de règles ou des formules trop complexes sur de grandes plages peuvent affecter les performances d'Excel. Il est conseillé de tester et d'optimiser les formules.
- Clarté des formules : Utiliser des formules claires et bien structurées facilite la maintenance et la compréhension des règles. La fonction
ETest indispensable pour combiner plusieurs critères. - Tests exhaustifs : Après avoir configuré les règles, il est crucial de réaliser des tests exhaustifs avec différents types de données et de scénarios pour s'assurer que la mise en forme s'applique comme prévu.
- Documentation : Documenter les règles de mise en forme conditionnelle et leur logique peut être très utile, surtout si d'autres utilisateurs sont amenés à travailler sur le même classeur.
En maîtrisant ces techniques, il est possible de créer des tableaux Excel non seulement visuellement attrayants, mais aussi dynamiques et intelligents, réagissant automatiquement aux données saisies. Cela transforme la saisie de données en une expérience plus fluide et plus efficace, réduisant les erreurs de formatage et améliorant la lisibilité globale.