Outils d'audit de formule dans Excel
Comme nous le savons tous, MS Excel est principalement utilisé et populaire pour sa fonction, ses formules et ses macros. Mais que se passe-t-il si nous rencontrons un problème lors de l'écriture de la formule ou si nous ne sommes pas en mesure d'obtenir le résultat souhaité dans une cellule car nous n'avons pas formulé la fonction correctement. C'est pourquoi MS Excel fournit de nombreux outils intégrés pour l'audit des formules et le dépannage des formules.
Les outils que nous pouvons utiliser pour l'audit et le dépannage des formules dans Excel sont:
- Tracer les précédents
- Tracer les personnes à charge
- Supprimer les flèches
- Afficher les formules
- Vérification des erreurs
- Évaluer la formule
Exemples d'outils d'audit dans Excel
Nous découvrirons chacun des outils d'audit ci-dessus un par un en utilisant quelques exemples dans Excel.
Vous pouvez télécharger ce modèle Excel des outils d'audit ici - Modèle Excel des outils d'audit# 1 - Tracer les précédents
Supposons que nous ayons la formule suivante dans la cellule D2 pour calculer les intérêts d'un compte FD dans une banque.
Maintenant, si nous voulons vérifier les précédents pour la formule, nous pouvons appuyer sur F2 pour entrer en mode édition après avoir sélectionné la cellule requise afin que les cellules précédentes soient bordées de différentes couleurs et de la même couleur, la référence de la cellule est écrite.
Nous pouvons voir que A2 est écrit avec la couleur bleue dans la cellule de formule et avec la même couleur, la cellule A2 est bordée.
De la même manière,
La cellule B2 a une couleur rouge.
La cellule C2 a une couleur violette.
Cette méthode est bonne, mais nous avons un moyen plus pratique de vérifier les précédents pour la cellule de formule.
Pour tracer les précédents, nous pouvons utiliser la commande «Trace Precedents» dans le groupe «Formula Audit» sous l' onglet «Formulas» .
Il suffit de sélectionner la cellule de formule, puis de cliquer sur la commande «Trace Precedents» . Ensuite, vous pouvez voir une flèche comme indiqué ci-dessous.
Nous pouvons voir que les cellules précédentes sont mises en évidence par des points bleus.
# 2 - Supprimer les flèches
Pour supprimer ces flèches, nous pouvons utiliser la commande «Supprimer les flèches» dans le groupe «Audit de formule» sous l' onglet «Formules» .
# 3 - Tracer les personnes à charge
Cette commande permet de tracer la cellule qui dépend de la cellule sélectionnée.
Utilisons cette commande en utilisant un exemple.
Supposons que nous ayons 4 montants que nous pouvons investir. Nous voulons savoir combien d'intérêts, nous pouvons gagner si nous investissons.
Nous pouvons voir que dans l'image ci-dessus, nous avons appliqué une formule pour calculer les intérêts avec le montant 1 et le pourcentage d'intérêt et la durée spécifiés dans l'année.
Nous allons copier la formule et la coller dans les cellules adjacentes pour le montant 2, le montant 3 et le montant 4. On peut remarquer que nous avons utilisé une référence de cellule absolue pour les cellules G2 et I2 car nous ne voulons pas changer ces références tout en copier et coller.
Maintenant, si nous voulons vérifier si les cellules dépendent de la cellule G2. Ensuite, nous utiliserons la commande 'Trace Dependents' disponible dans le groupe 'Formula Auditing' sous l' onglet 'Formulas' .
Sélectionnez la cellule G2 et cliquez sur la commande 'Trace Dependents' .
Dans l'image ci-dessus, nous pouvons voir les lignes fléchées où les flèches indiquent quelles cellules dépendent des cellules.
Nous allons maintenant supprimer les lignes de flèches en utilisant la commande «Supprimer les flèches» .
# 4 - Afficher les formules
Nous pouvons utiliser cette commande pour afficher les formules écrites dans la feuille Excel. La touche de raccourci de cette commande est «Ctrl + ~» .
Voir l'image ci-dessous où nous pouvons voir les formules dans la cellule.
Nous pouvons voir qu'au lieu des résultats de la formule, nous pouvons voir la formule. Pour les montants, le format de devise n'est pas visible.
Pour désactiver ce mode, appuyez à nouveau sur 'Ctrl + ~' ou nous pouvons cliquer sur la commande 'Afficher les formules' .
# 5 - Vérification des erreurs
Cette commande est utilisée pour vérifier l'erreur dans la formule ou la fonction spécifiée.
Prenons un exemple pour comprendre cela.
Voir l'image ci-dessous où nous avons une erreur dans la fonction appliquée pour le résultat.
Maintenant, pour résoudre cette erreur, nous allons utiliser la commande 'Vérification des erreurs' .
Les étapes seraient:
Sélectionnez la cellule dans laquelle la formule ou la fonction est écrite puis cliquez sur «Vérification des erreurs».
Lorsque nous cliquons sur la commande, nous obtenons la boîte de dialogue suivante intitulée «Vérification des erreurs» .
Dans la boîte de dialogue ci-dessus, on peut voir qu'il y a une erreur de nom non valide. La formule contient du texte non reconnu.
Si nous utilisons la fonction ou construisons la formule pour la première fois, nous pouvons cliquer sur le bouton `` Aide sur cette erreur '' qui ouvrira la page d'aide pour la fonction dans le navigateur où nous pouvons voir toutes les informations connexes en ligne et comprendre la cause et trouvez toutes les solutions possibles.
En cliquant sur ce bouton maintenant, nous trouverons la page suivante.
Sur cette page, nous apprenons à connaître l'erreur que cette erreur survient lorsque
- La formule fait référence à un nom qui n'a pas été défini. Cela signifie que le nom de la fonction ou la plage nommée n'a pas été défini précédemment.
- La formule a une faute de frappe dans le nom défini. Cela signifie qu'il y a une erreur de frappe.
Si nous avons utilisé la fonction plus tôt et que nous connaissons la fonction, nous pouvons cliquer sur le bouton «Afficher les étapes de calcul» pour vérifier comment l'évaluation de la fonction entraîne une erreur.
Si nous cliquons sur ce bouton, les étapes suivantes s'affichent:
- La boîte de dialogue suivante s'affiche lorsque nous cliquons sur le bouton «Afficher les étapes de calcul» .
- Après avoir cliqué sur le bouton «Evaluer» , l'expression soulignée, c'est-à-dire «IIF», est évaluée et donne les informations suivantes telles qu'affichées dans la boîte de dialogue.
Comme nous pouvons le voir dans l'image ci-dessus, l' expression 'IIF' évaluée comme une erreur qui est '#NAME?'. Maintenant l'expression ou la référence suivante, c'est-à-dire, B2 a été souligné. Si nous cliquons sur le bouton 'Step In', nous pouvons également vérifier les détails internes d'une étape et sortir en appuyant sur le bouton 'Step Out' .
- Nous allons maintenant cliquer sur le bouton «Evaluer» pour vérifier le résultat de l'expression soulignée. Après avoir cliqué, nous obtenons le résultat suivant.
- Après avoir cliqué sur le bouton 'Evaluer' , nous obtenons le résultat de la fonction appliquée.
- Nous avons eu une erreur en conséquence et en analysant la fonction étape par étape, nous avons appris qu'il y avait une erreur dans 'IIF' Pour cela, nous pouvons utiliser la commande 'Insérer une fonction' dans le groupe 'Bibliothèque de fonctions' sous ' Onglet Formules.
Lorsque nous avons tapé le «si» , nous avons obtenu une fonction similaire dans la liste, nous devons choisir la fonction appropriée.
Après avoir sélectionné la fonction 'Si' , nous obtenons la boîte de dialogue suivante avec des zones de texte pour l'argument et nous remplirons tous les détails.
Après avoir cliqué sur «Ok» , nous obtenons le résultat dans la cellule. Nous recopierons la fonction pour tous les étudiants.
Choses à retenir
- Si nous activons la commande «Afficher les formules», les dates sont également affichées au format numérique.
- Lors de l'évaluation de la formule, nous pouvons également utiliser F9 comme raccourci dans Excel.
