Analyse des données Excel - Audit de formules

Vous voudrez peut-être vérifier l'exactitude des formules ou trouver la source d'une erreur. Les commandes d'audit de formule Excel vous permettent de trouver facilement

  • Quelles cellules contribuent au calcul d'une formule dans la cellule active.
  • Quelles formules font référence à la cellule active.

Ces résultats sont représentés graphiquement par des lignes de flèches qui facilitent la visualisation. Vous pouvez afficher toutes les formules de la feuille de calcul active avec une seule commande. Si vos formules font référence à des cellules d'un autre classeur, ouvrez également ce classeur. Excel ne peut pas accéder à une cellule d'un classeur qui n'est pas ouvert.

Définition des options d'affichage

Vous devez vérifier si les options d'affichage des classeurs que vous utilisez sont correctement définies.

  • Cliquez sur FILE > Options.
  • Dans la boîte de dialogue Options Excel, cliquez sur Avancé.
  • Dans les options d'affichage du classeur -
    • Sélectionnez le classeur.
    • Vérifiez que sous Pour les objets, afficher, Tout est sélectionné.
  • Répétez cette étape pour tous les classeurs que vous auditez.

Tracer les précédents

Les cellules précédentes sont les cellules désignées par une formule dans la cellule active.

Dans l'exemple suivant, la cellule active est C2. En C2, vous avez la formule=B2*C4.

B2 et C4 sont des cellules précédentes pour C2.

Pour retracer les précédents de la cellule C2,

  • Cliquez dans la cellule C2.
  • Cliquez sur l'onglet Formules.
  • Cliquez sur Tracer les précédents dans le groupe Audit de formule.

Deux flèches, une de B2 à C2 et une autre de C4 à C2 seront affichées, retraçant les précédents.

Notez que pour le traçage des précédents d'une cellule, la cellule doit avoir une formule avec des références valides. Sinon, vous obtiendrez un message d'erreur.

  • Cliquez dans une cellule qui ne contient pas de formule ou cliquez dans une cellule vide.
  • Cliquez sur Tracer les précédents dans le groupe Audit de formule.

Vous recevrez un message.

Suppression des flèches

Cliquez sur Supprimer les flèches dans le groupe Audit de formule.

Toutes les flèches de la feuille de calcul disparaîtront.

Suivi des personnes à charge

Les cellules dépendantes contiennent des formules qui font référence à d'autres cellules. Cela signifie que si la cellule active contribue à une formule dans une autre cellule, l'autre cellule est une cellule dépendante de la cellule active.

Dans l'exemple ci-dessous, C2 a la formule =B2*C4. Par conséquent, C2 est une cellule dépendante des cellules B2 et C4

Pour tracer les dépendants de la cellule B2,

  • Cliquez dans la cellule B2.
  • Cliquez sur l'onglet Formules.
  • Cliquez sur Tracer les dépendants dans le groupe Audit de formule.

Une flèche apparaît de B2 à C2, indiquant que C2 dépend de B2.

Pour retracer les dépendants de la cellule C4 -

  • Cliquez dans la cellule C4.
  • Cliquez sur l'onglet Formule> Tracer les dépendants dans le groupe Audit de formule.

Une autre flèche apparaît de C4 à C2, montrant que C2 dépend également de C4.

Cliquez sur Remove Arrowsdans le groupe Audit de formules. Toutes les flèches de la feuille de calcul disparaîtront.

Note- Pour le suivi des dépendants d'une cellule, la cellule doit être référencée par une formule dans une autre cellule. Sinon, vous obtiendrez un message d'erreur.

  • Cliquer dans la cellule B6 n'est référencé par aucune formule ou cliquer dans une cellule vide.
  • Cliquez sur Tracer les dépendants dans le groupe Audit de formule. Vous recevrez un message.

Travailler avec des formules

Vous avez compris le concept de précédents et de personnes à charge. Maintenant, considérez une feuille de calcul avec plusieurs formules.

  • Cliquez dans une cellule sous Catégorie de réussite dans le tableau Résultats de l'examen.
  • Cliquez sur Tracer les précédents. La cellule à sa gauche (Marques) et la plage E4: F8 seront mappées comme les précédents.
  • Répétez pour toutes les cellules sous Catégorie de réussite dans le tableau Résultats de l'examen.

  • Cliquez dans une cellule sous Catégorie de réussite dans le tableau Notes des étudiants.

  • Cliquez sur Tracer les personnes à charge. Toutes les cellules sous Catégorie de réussite dans le tableau Résultats d'examen seront mappées en tant que personnes à charge.

Affichage des formules

La feuille de travail ci-dessous contient le résumé des ventes des vendeurs dans les régions Est, Nord, Sud et Ouest.

  • Cliquez sur l'onglet FORMULES sur le ruban.

  • Cliquez sur Afficher les formules dans le groupe Audit des formules. Les formules dans la feuille de calcul apparaîtront, afin que vous sachiez quelles cellules contiennent des formules et quelles sont les formules.

  • Cliquez dans une cellule sous TotalSales.

  • Cliquez sur Tracer les précédents. Une icône de feuille de calcul apparaît à la fin de la flèche. L'icône de feuille de calcul indique que les précédents se trouvent dans une feuille de calcul différente.

Double-cliquez sur la flèche. UNEGo TO La boîte de dialogue apparaît, affichant les précédents.

Comme vous le constatez, il y a quatre précédents, sur quatre feuilles de calcul différentes.

  • Cliquez sur une référence de l'un des précédents.
  • La référence apparaît dans la zone Référence.
  • Cliquez sur OK. La feuille de calcul contenant ce précédent apparaît.

Évaluer une formule

Pour savoir comment une formule complexe dans une cellule fonctionne étape par étape, vous pouvez utiliser la commande Evaluer la formule.

Considérez la formule NPV (année intermédiaire) dans la cellule C14. La formule est

=SQRT (1 + C2)*C10

  • Cliquez dans la cellule C14.
  • Cliquez sur l'onglet FORMULES sur le ruban.
  • Cliquez sur Evaluer la formule dans le groupe Audit de formule. La boîte de dialogue Evaluer la formule s'affiche.

dans le Evaluate Formulaboîte de dialogue, la formule s'affiche dans la zone sous Évaluation. En cliquant sur leEvaluatebouton plusieurs fois, la formule est évaluée par étapes. L'expression soulignée sera toujours exécutée ensuite.

Ici, C2 est souligné dans la formule. Donc, il est évalué à l'étape suivante. Cliquez surEvaluate.

La cellule C2 a la valeur 0,2. Par conséquent, C2 sera évalué à 0,2.1+0.2est souligné en le montrant comme étape suivante. Cliquez surEvaluate.

1 + 0,2 sera évalué comme 1,2. SQRT(1.2)est souligné en le montrant comme étape suivante. Cliquez surEvaluate.

SQRT (1,2) sera évalué comme 1,09544511501033. C10est souligné en le montrant comme étape suivante. Cliquez surEvaluate.

C10 sera évalué comme 4976.8518518515.

1.09544511501033 * 4976.8518518515 est souligné en le montrant comme étape suivante. Cliquez surEvaluate.

1.09544511501033 * 4976.8518518515 sera évalué comme 5.451.87.

Il n'y a plus d'expressions à évaluer et c'est la réponse. leEvaluate le bouton sera remplacé par Restart bouton, indiquant la fin de l'évaluation.

Vérification des erreurs

Il est recommandé d'effectuer une vérification des erreurs une fois que votre feuille de calcul et / ou votre classeur est prêt avec les calculs.

Considérez les calculs simples suivants.

Le calcul dans la cellule a abouti à l'erreur # DIV / 0 !.

  • Cliquez dans la cellule C5.

  • Cliquez sur l'onglet FORMULES sur le ruban.

  • Cliquez sur la flèche en regard de Vérification des erreurs dans le groupe Audit de formule. Dans la liste déroulante, vous trouverez queCircular References est désactivé, indiquant que votre feuille de calcul n'a pas de références circulaires.

  • Sélectionner Trace Error dans la liste déroulante.

Les cellules nécessaires pour calculer la cellule active sont indiquées par des flèches bleues.

  • Cliquez sur Supprimer les flèches.
  • Cliquez sur la flèche en regard de Vérification des erreurs.
  • Sélectionnez Vérification des erreurs dans la liste déroulante.

le Error Checking la boîte de dialogue apparaît.

Observez ce qui suit -

  • Si vous cliquez sur Help on this error, L'aide d'Excel sur l'erreur sera affichée.

  • Si vous cliquez sur Show Calculation Steps, La boîte de dialogue Evaluer la formule s'affiche.

  • Si vous cliquez sur Ignore Error, la boîte de dialogue Vérification des erreurs se ferme et si vous cliquez sur Error Checking à nouveau, il ignore cette erreur.

  • Si vous cliquez sur Edit in Formula Bar, vous serez redirigé vers la formule dans la barre de formule, afin que vous puissiez modifier la formule dans la cellule.