Petite astuce pour repérer automatiquement les formules

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


Sujets que vous pourriez aussi aimer :


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.



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.

• Télécharger le classeur reperer-formules.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

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,

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),

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,

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,

De cette manière, le contenu de la cellule ressortira suffisamment explicitement.

• Valider ces attributs de format en cliquant sur le bouton Ok,

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.



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.