Dans les derniers tutoriels, nous avons étudié comment écrire et étendre des formules dans Excel, comment afficher ou masquer des formules, comment verrouiller une formule, etc. Dans ce tutoriel, vous apprendrez différentes astuces et méthodes efficaces pour évaluer et déboguer vos formules dans Excel. Vous apprendrez également à évaluer des parties d’une formule en utilisant la touche F9 et bien d’autres techniques.
Vous pouvez échouer la plupart du temps en essayant de résoudre des problèmes complexes dans Excel. Lorsque vous décidez de rechercher sur Internet, vous pouvez trouver plusieurs solutions prêtes à l'emploi, mais qui sont très difficiles à comprendre en raison des formules imbriquées qui ont été utilisées.
Bien que vous ayez obtenu la solution que vous cherchiez, ne pas comprendre la formule n'est pas un bon signe. En effet, si vous souhaitez modifier ou changer les références de la formule, vous devez savoir et comprendre d’abord comment elle fonctionne.
Pour cette raison, Excel fournit des fonctionnalités qui vous aident à évaluer vos formules en quelques clics. Dans ce tutoriel, nous vous guiderons à travers le processus d'évaluation de formules étape par étape.
Edition de formules Excel : touche F2
Modification directement dans la barre de formule
Evaluation des parties d’une formule avec la touche F9
Tracer les relations entre les formules et les cellules dans Excel
Tracer les antécédents (cellules qui fournissent des données à une formule)
Tracer les dépendants (formules qui référencent une cellule particulière)
Déboguer une formule à l'aide de la fonctionnalité Évaluer la formule
Exécution d'une vérification des erreurs
Lorsqu'une cellule est sélectionnée, appuyez sur la touche « F2 » pour la mettre en mode « Modifier ». Si la cellule contient une formule, vous verrez la formule dans la cellule et pourrez facilement la modifier :
Vous pouvez sélectionner une cellule ou une plage de cellules en utilisant les touches fléchées pour modifier une référence. Lorsque vous appuyez sur les touches fléchées en mode « Modifier », le curseur de texte se déplace dans la formule :
Le mode est affiché dans le coin inférieur gauche de la fenêtre Excel (dans la barre d'état) comme illustré ci-dessous :
À ce stade, vous pouvez appuyer à nouveau sur F2 pour revenir au mode « Entrer ».
Maintenant, les touches fléchées sélectionneront les cellules au lieu de déplacer le curseur de texte dans la formule :
Vous pouvez appuyer à nouveau sur F2 pour revenir au mode « Modifier » pour déplacer le curseur de texte. Cette bascule vous permet de modifier les formules en utilisant simplement le clavier.
L'utilisation de la touche F2 dépendra du scénario. Parfois, il est plus rapide d'utiliser la souris. Cependant, les modes « Entrer » et « Modifier » facilitent la sélection de texte ou de plages avec des touches de raccourcis telles que Ctrl + Shift + Fin.
La touche F2 est souvent considérée comme un moyen démodé de modifier les formules. Deux autres façons d'accéder au mode d'édition dans Excel sont :
La méthode à utiliser dépend de vous. Certaines personnes préfèrent travailler avec le clavier la plupart du temps, tandis que d'autres trouvent plus pratique d'utiliser la souris.
Quelle que soit la méthode d'édition choisie, une indication visuelle du mode d'édition se trouve dans le coin inférieur gauche de l'écran. Dès que vous appuyez sur F2, double-cliquez sur la cellule ou cliquez sur la barre de formule.
Appuyez sur Ctrl + A pour passer de la modification d'une formule dans une cellule à la barre de formule. Cela ne fonctionne que lorsque vous modifiez une formule, pas une valeur.
Lorsque vous avez terminé les modifications, appuyez sur Entrée pour terminer la formule.
Pour quitter le mode « Modifier » sans apporter de modifications à la formule, appuyez sur la touche « Echap ».
Par défaut, appuyer sur la touche F2 dans Excel positionne le curseur à la fin de la formule dans une cellule. Si vous préférez toujours modifier les formules dans la barre de formule Excel, procédez comme suit :
Lorsque vous travaillez sur une formule, il existe un certain nombre d'options pour vous aider à éviter les erreurs ou les corriger.
Lorsqu'une formule est en mode édition, les cellules correspondantes du classeur sont codées par couleur. Cela rend la formule facile à déboguer :
Lorsqu’une formule est en mode « Entrer » ou « Edition », vous pouvez vérifier l'exactitude au fur et à mesure à l'aide de la touche F9. Dans Microsoft Excel, la touche F9 est un moyen simple et rapide de vérifier et de déboguer les formules. Elle est utilisée pour évaluer les segments sélectionnés de la formule et voir ce qu'ils renvoient en les remplaçant par les valeurs réelles des références ou par le résultat calculé.
Prenons un exemple. Dans la capture ci-dessous, nous utilisons la fonction SI pour afficher des remarques sur les notes :
=SI(MOYENNE(B2:C2)>12; "Bon"; "Faible")
Pour évaluer la fonction « Moyenne » incluse dans la formule « Somme », procédez comme suit :
Sélectionnez par exemple la plage B2:C2 et appuyez ensuite sur la touche F9. Vous verrez un tableau contenant les valeurs de la plage Excel B2:C2 comme illustré ci-dessous :
Lorsque vous sélectionnez MOYENNE(B2:C2) et appuyez sur la touche F9, la moyenne des notes contenues dans la plage B2:C2 s’affiche comme illustré ci-dessous :
Quelle que soit la partie de la formule que vous sélectionnerez, la touche F9 montrera ce que cette partie signifie pour cette formule.
Si vous sélectionnez une partie qui n'est pas une expression valide pour la formule Excel et appuyez sur F9, Excel vous indiquera que la formule est incorrecte.
Après avoir utilisé la touche F9, si vous appuyez sur la touche « Entrée », la formule sera codée en dur par les valeurs renvoyées par ces segments de la formule.
Si vous souhaitez simplement vérifier la formule, appuyez sur « Echap » pour quitter.
La touche F9 est particulièrement utile pour tester des formules longues et complexes, telles que des formules imbriquées ou des formules matricielles, où il est difficile de comprendre comment la formule calcule le résultat final car elle comprend quelques calculs intermédiaires ou des tests logiques. Ci-dessous quelques utilisations pratiques de cette technique :
Vérifier l'exactitude des formules ou trouver la source d'une erreur peut être difficile si la formule utilise des cellules antécédentes ou dépendantes :
Pour vous aider à vérifier vos formules, vous pouvez utiliser les commandes « Repérer les antécédents » et « Repérer les dépendants » pour afficher et tracer graphiquement les relations entre les cellules et les formules avec des flèches de suivi, comme illustré dans la capture ci-dessous :
Suivez les étapes ci-dessous pour afficher les relations de formule entre les cellules :
Pour tracer uniquement les liens vers les cellules utilisées dans une formule comme références, suivez ces étapes :
Les flèches bleues montrent les cellules sans erreur. Les flèches rouges indiquent les cellules qui provoquent des erreurs. Si la cellule sélectionnée est référencée par une cellule dans une autre feuille de calcul ou classeur, une flèche noire pointe de la cellule sélectionnée vers une icône de feuille de calcul. L'autre classeur doit être ouvert afin qu'Excel puisse suivre ces dépendances.
Pour supprimer toutes les flèches des antécédents à la fois, accédez à l'onglet « Formules ». Ensuite, dans le groupe « Vérification des formules », cliquez sur la flèche en regard de « Supprimer les flèches », puis cliquez sur « Supprimer les flèches antécédentes ».
Pour tracer des flèches pointant vers les dépendants d’une cellule, suivez ces étapes :
Les flèches bleues montrent les cellules sans erreur, et si vous avez des cellules contenant des erreurs, vous aurez des flèches rouges.
Pour supprimer les flèches de suivi des dépendants, allez dans l'onglet « Formules », et puis dans le groupe « Vérification des formules », cliquez sur la flèche en regard de « Supprimer les flèches », puis cliquez sur « Supprimer les flèches dépendantes ».
Le traçage des antécédents et des dépendants de formule vous permet de suivre rapidement et précisément ce que fait une formule sélectionnée. La fonctionnalité « Evaluer les formules » va encore plus loin en vous montrant chaque calcul qui a été utilisé par la formule pour atteindre sa valeur finale.
Prenons la formule de l’exemple ci-dessus, dans la colonne « Remarques ». Nous verrons comment cette formule se résout en une série d'étapes :
=SI(MOYENNE(B2:C2)>12; "Bon"; "Faible")
Vous pouvez voir que notre formule utilise à la fois la formule SI et la formule Moyenne.
Le but de cette formule est d'évaluer si la moyenne des notes de chaque élève est supérieure à 12.
Par exemple : 13,5 est la moyenne de la plage B2:C2 : {10.18}.
Pour bien comprendre cette formule, suivez les étapes ci-dessous :
Ainsi, vous venez de voir comment la formule nous a donné le résultat en quelques étapes faciles !
L’option de vérification des erreurs fonctionne de la même manière que la fonction de vérification orthographique de la plupart des applications de traitement de texte. Bien qu'Excel recherche activement les erreurs lors de la création de formules, la vérification automatique d'Excel ne peut pas prendre en compte certains problèmes.
