Comment modifier, évaluer et déboguer des formules Excel

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.

Table des matières

Edition de formules Excel : touche F2

Modification directement dans la barre de formule

Evaluation des parties d’une formule avec la touche F9

Utilisations de 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

Edition de formules Excel : touche F2

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 :

  • Double-cliquer sur la cellule ;
  • Ou, cliquer n'importe où dans la barre de formule.

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

Modification directement dans la barre de formule

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 :

  1. Cliquez sur le menu « Fichier».
  2. Ensuite, cliquez sur « Options» dans le volet gauche.
  3. Dans le volet gauche de la boite de dialogue « Options Excel», sélectionnez « Options avancées ».
  4. Dans le volet droit, décochez l'option « Modification directe» sous « Options d'édition » :
  1. Cliquez sur « OK» pour enregistrer les modifications et fermer la boîte de dialogue.

Evaluation des parties d’une formule avec la touche F9

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 :

  1. Sélectionnez la cellule avec la formule (D2dans cet exemple).
  2. Appuyez sur la touche « F2» ou double-cliquez sur la cellule sélectionnée pour passer en mode d'édition.
  3. Sélectionnez la partie de formule que vous souhaitez tester et appuyez sur F9.

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.

Utilisations de la touche F9

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 :

  • Découvrir comment la formule fonctionne : parfois, nous trouvons des formules Excel en ligne et les utilisons sans être capables de savoir comment elles fonctionnent. Ici, la touche F9 peut vous aider à décoder ces formules.
  • Détecter les erreurs Excel : Il arrive souvent que nous écrivions une formule correctement mais nous obtenons des erreurs en retour. Dans de telle situations, nous pouvons utiliser le débogueur de formule F9pour voir quelle partie de la formule est à l'origine de l'erreur.
  • Déboguer les erreurs logiques : ce sont les types d'erreurs les plus difficiles. Elles ne montrent aucune erreur et renvoient simplement une sortie incorrecte. Vous devez déboguer pourquoi la formule renvoie une sortie incorrecte. Ici, la touche F9 peut vous aider à évaluer chaque segment de la formule pour voir si elle renvoie la sortie attendue ou non.

Tracer les relations entre les formules et les cellules dans Excel

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 :

  • Cellules antécédentes : ce sont les cellules référencées par une formule dans une autre cellule. Par exemple, si la cellule A1 contient la formule =B1, la cellule B1 est une cellule antécédente de la cellule A1.
  • Cellules dépendantes : ces cellules contiennent des formules qui font référence à d'autres cellules. Par exemple, si la cellule A1 contient la formule =B1, la cellule A1 dépend de la cellule B1.

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 :

  1. Ouvrez le menu « Fichier».
  2. Ensuite, cliquez sur « Options» dans le volet gauche.
  3. Dans la boite de dialogue « Options Excel» qui s’ouvre, cliquez sur « Options Avancées ».
  4. Dans la section « Options d'affichage du classeur», sélectionnez le classeur, puis vérifiez que l’option « Tout » est choisie dans « Pour des objets, afficher ». Pour spécifier des cellules de référence dans un autre classeur, ce classeur doit être ouvert. Microsoft Office Excel ne peut pas accéder à une cellule d'un classeur qui n'est pas ouvert.
  1. Effectuez l'une des actions suivantes.

Tracer les antécédents (cellules qui fournissent des données à une formule)

Pour tracer uniquement les liens vers les cellules utilisées dans une formule comme références, suivez ces étapes :

  1. Sélectionnez la cellule contenant la formule pour laquelle vous souhaitez rechercher les cellules antécédentes.
  2. Sous l'onglet « Formules », dans le groupe « Vérification des formules», cliquez sur « Repérer les antécédents » pour afficher des flèches de suivi pointant vers chaque cellule qui fournit directement des données à la cellule active.

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

Tracer les dépendants (formules qui référencent une cellule particulière)

Pour tracer des flèches pointant vers les dépendants d’une cellule, suivez ces étapes :

  1. Sélectionnez la cellule pour laquelle vous souhaitez identifier les cellules dépendantes.
  2. Sous l'onglet « Formules », dans le groupe « Vérification des formules», cliquez sur « Repérer les dépendants ». Cela affichera une flèche de suivi vers chaque cellule qui dépend de la cellule active.

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

Déboguer une formule à l'aide de la fonctionnalité Évaluer la formule

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 :

  1. Sélectionnez la cellule en question, puis accédez à l’onglet « Formules».
  2. Cliquez sur « Évaluer la formule» dans le groupe « vérification des formules ».
  1. Notre formule est maintenant affichée dans la boite de dialogue « évaluer la formule», et la partie qui est soulignée est celle à évaluer en premier :
  1. Cliquez sur « Evaluer».
  2. Moyenne(B2:C2)a été évaluée à 14. Cliquez sur « Evaluer » :
  1. La formule 14>12est évaluée à « VRAI ». Cliquez sur « Evaluer » :
  1. Étant donné que la formuleSI a reçu un « VRAI », Excel l'a évaluée comme « Bon » en résultat final :

Ainsi, vous venez de voir comment la formule nous a donné le résultat en quelques étapes faciles !

Exécuter une vérification des erreurs

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.

  1. Exécutez une vérification des erreurs en cliquant sur « Formules» puis sur « Vérification des erreurs » :
  1. Lorsque l'outil de vérification des erreurs est lancé, il recherchera immédiatement dans votre classeur toute erreur de formule. Toutes les erreurs détectées seront répertoriées une par une. Dans cet exemple, une erreur sera trouvée dans B15:
  1. La cellule B15contient une erreur de dénomination : la référence de cellule « B » est incomplète. Vous pouvez utiliser les boutons à droite pour :
  • Ouvrir le fichier d'aide pour obtenir des informations spécifiques sur cette erreur ;
  • Afficher les étapes de calcul qui ont été suivies pour atteindre ce point ;
  • Ignorer cette erreur pour le moment ;
  • Corriger l'erreur à la main dans la barre de formule ;
  • De plus, le bouton « Options » ouvrira la boîte de dialogue « Options Excel » dans la catégorie « Formules », où vous pourrez personnaliser les paramètres de gestion des erreurs d’Excel.
  1. Cliquez sur « Suivant » pour passer à l'erreur suivante.
  2. Si aucune autre erreur n'a été trouvée dans la feuille de calcul actuelle, une petite boîte de dialogue s'affiche. Cliquez sur « OK» :

Télécharger le fichier Excel de ce tutoriel

Article publié le 23 Août 2020par Hanane Mouqqadim