formateur informatique

Petite astuce pour repérer automatiquement les formules

Accueil  >  Bureautique  >  Excel  >  Excel Astuces  >  Petite astuce pour repérer automatiquement les formules
Livres à télécharger


Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :

Abonnez-vous gratuitement à la chaîne Youtube Rossetti Stéphane .
Sujets et formations similaires :


Repérer dynamiquement les formules

Sur les feuilles de calcul complexes, savoir identifier l'emplacement des formules et de leurs dépendances est un enjeu intéressant. Il n'est pas question de les afficher toutes par le biais de la fonctionnalité Afficher les formules. Celle-ci déstructure la mise en page de la feuille et complique sa lecture. Il est question de les repérer par des jeux de couleurs qui réagissent dynamiquement à la création ou à la suppression de calculs.

Repérer dynamiquement les positions des formules dans la feuille Excel par des jeux de couleurs

Dans l'exemple finalisé illustré par la capture, le cas est complexe. En conséquence, les formules sont nombreuses. Par défaut, il est très difficile de les identifier et de les repérer. Mais comme vous pouvez le voir ici, elles sont toutes repérées sur un fond jaune clair avec un texte jaune foncé. Dès lors, il suffit de sélectionner l'une ou l'autre case pour comprendre le mécanisme de la feuille, en consultant les barres de formules respectives.



Source et présentation
Pour dévoiler cette astuce, nous proposons de récupérer cette feuille de calcul. Sans surprise, cette feuille Excel est relativement dense. Nous l'avions exploitée à l'occasion de démonstrations sur des techniques matricielles pour identifier les trois meilleurs et trois moins bons étudiants. Le tableau situé entre les colonnes B et L livre les notes par matière pour chaque élève. Les tableaux sur la droite dressent des synthèses pour identifier les meilleures et pires notes. Mais en l'état et vous en conviendrez, il est difficile de se faire une idée sur l'emplacement de toutes les formules imbriquées servant à livrer les conclusions finales.
  • En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour afficher son ruban,
  • Dans la section Vérification des formules du ruban, cliquer sur le bouton Afficher les formules,
Afficher toutes les formules de la feuille Excel avec les syntaxes

Certes, toutes les formules apparaissent. Mais plusieurs problèmes se posent. Majoritairement, leur affichage est tronqué dans les cases respectives. La mise en page est déstructurée car les colonnes sont élargies. De fait, l'intégralité du cas ne tient plus à l'affichage sur l'écran. La lecture et la compréhension de la feuille deviennent compliquées.
  • Cliquer de nouveau sur le bouton Afficher les formules,
Ainsi, nous récupérons la feuille dans son état d'origine.



Repérer les formules en couleur
Pour identifier l'emplacement de toutes les formules dans une feuille de calcul, l'astuce consiste à exploiter une règle de mise en forme conditionnelle. Cette règle doit être appliquée sur toutes les cellules de la feuille pour qu'elles soient toutes concernées. Ainsi, quel que soit son emplacement, si un nouveau calcul venait à être créé, il serait automatiquement repéré. La règle doit exploiter une fonction logique capable d'analyser le contenu de chaque cellule. Il s'agit de la fonction Excel EstFormule.
  • En haut de la fenêtre Excel, cliquer sur l'onglet Accueil pour revenir sur son ruban,
  • En haut à gauche de la feuille Excel, cliquer à l'intersection des étiquettes (Entre le 1 et le A),
Sélectionner toutes les cellules de la feuille Excel en cliquant à l-intersection des règles

De cette manière, nous sélectionnons l'intégralité des cellules de la feuille Excel. Et la première d'entre elles, la cellule A1, est bien active par défaut. Cette remarque est loin d'être anodine. L'analyse d'une mise en forme conditionnelle est chronologique. Pour passer toutes les cellules en revue, elle doit débuter son étude à partir de la première.
  • Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
  • En bas de la liste des propositions, choisir la commande Nouvelle règle,
  • Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
  • Puis, cliquer dans la zone de saisie du dessous pour l'activer,
  • Ensuite, taper le symbole égal (=) pour initier la syntaxe de la règle,
  • Taper le nom de la fonction logique suivie d'une parenthèse, soit : EstFormule(,
  • Cliquer sur la première cellule de la feuille, ce qui donne : $A$1,
  • Enfoncer trois fois la touche F4 du clavier pour la libérer, ce qui donne : A1,
En effet et nous le disions, l'analyse d'une mise en forme conditionnelle est chronologique. Pour que toutes les cellules de la feuille puissent être étudiées tour à tour avec cette fonction, la cellule A1 doit être défigée.
  • Fermer la parenthèse de la fonction EstFormule,
Dès que cette fonction renvoie Vrai, la cellule concernée doit réagir automatiquement. Et pour cela, nous proposons de modifier son arrière-plan ainsi que sa couleur de texte.
  • Cliquer sur le bouton Format en bas de la boîte de dialogue,
  • Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
  • Dans la palette de couleurs, choisir un jaune clair,
Choisir une couleur de fond pour repérer automatiquement les cellules des formules dans la feuille Excel

Il est aussi possible de définir des dégradés de couleurs ou des motifs de remplissage.
  • Cliquer sur l'onglet Police de la boîte de dialogue,
  • Choisir tout d'abord un style gras,
  • Puis, avec la liste déroulante, choisir un jaune-or pour le texte,
Choisir une couleur de texte pour identifier visuellement les cellules avec des formules dans les tableaux Excel

De cette manière, le contenu de la cellule ressortira suffisamment explicitement.
  • Valider ces attributs de format en cliquant sur le bouton Ok,
Mise en forme conditionnelle Excel pour faire ressortir automatiquement toutes les cellules de la feuille portant des calculs



Nous sommes de retour sur la première boîte de dialogue. Elle explicite parfaitement le contexte. Toute cellule portant une formule doit se parer des attributs de format que nous venons de définir.
  • Cliquer de nouveau sur le bouton Ok pour valider la création de la règle,
De retour sur la feuille, vous constatez que de nombreuses cellules sont désormais surlignées automatiquement. Nous prenons immédiatement conscience de l'ampleur des calculs engagés sur cette feuille. Et pour vérifier la cohérence de cette détection, il suffit de cliquer sur l'une et l'autre cellule mise en valeur et de consulter le contenu de la barre de formule.

Colorier automatiquement les cellules portant des formules dans la feuille de calcul Excel

Si vous ajoutez une nouvelle formule sur la feuille, à validation sa cellule est automatiquement repérée. Enfin, sachez qu'Excel propose la fonction FormuleTexte. Elle permet de retranscrire la formule de la cellule qui lui est passée en paramètre. C'est un bon moyen de prendre les renseignements tout en conservant les calculs dans leur état.

Fonction Excel FormuleTexte pour afficher la syntaxe des formules de la feuille Excel par calcul

 
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales



Abonnement à la chaîne Youtube
Partager la formation
Partager sur Facebook
Partager sur Twitter
Partager sur LinkedIn