L’outil d’audit et le complément Inquire de Microsoft Excel

■ Je viens de recevoir une feuille de calcul de cinq mille lignes qui calcule la valeur actuelle nette (VAN) d’une nouvelle voiture. Dans la feuille de calcul, mon analyste financier a fait une hypothèse sur le pourcentage annuel de la croissance du prix du produit. Quelles cellules de la feuille de calcul sont affectées par cette hypothèse?

■ Je pense que mon analyste financier a fait une erreur de calcul du bénéfice avant impôt de l’année 1. Quelles cellules du modèle de feuille de calcul ont été utilisées pour ce calcul?

■ Comment l’outil d’audit fonctionne-t-il lorsque je travaille avec des données dans plusieurs feuilles de calcul ou classeurs?

■ Qu’est-ce que le nouveau complément Inquire et comment puis-je l’installer?

■ Quelles informations le complément Inquire affiche-t-il sur un classeur?

■ Comment le complément Inquire compare-t-il les classeurs?

■ Comment le complément Inquire affiche-t-il les relations entre les formules de cellule?

■ Comment le complément Inquire affiche-t-il les relations entre les feuilles de calcul?

Lorsque vous entendez le mot structure, vous pensez souvent à la structure d’un bâtiment. La structure d’un modèle de feuille de calcul se réfère à la façon dont les hypothèses d’entrée (données telles que les ventes unitaires, le prix et le coût unitaire) sont utilisées pour calculer les sorties d’intérêt telles que la VAN, le profit ou le coût. L’outil d’audit Microsoft Excel 2013 fournit une méthode simple pour documenter la structure d’une feuille de calcul ou d’un classeur, ce qui facilite la compréhension de la logique sous-jacente aux modèles de feuille de calcul complexes. Pour afficher les options d’audit dans Excel, cliquez sur l’onglet Formules du ruban, puis affichez le groupe Audit des formules. (Voir figure 1.)

Ce chapitre présente les commandes Trace Precedents, Trace Dependents et Remove Arrows. Ici, ce chapitre présente une brève discussion des autres éléments du groupe d’audit de formule. La commande Évaluer la formule est abordée à l’article 21, «La fonction DECALER».

FIGURE 1  : Cette figure montre la barre d’outils Audit de formule.

Le fait de cliquer sur l’icône Afficher les formules, illustré à la figure ci- dessus, sert de commutateur qui bascule l’affichage des formules dans les cellules et l’affichage des valeurs qui résultent des formules. Vous pouvez également appuyer sur Ctrl + ~ pour basculer entre l’affichage des formules dans les cellules et l’affichage des résultats des formules. Les formules peuvent également être affichées dans une feuille de calcul à l’aide de la nouvelle fonction FORMULATEXT dans Excel . Le fichier  (voir figure  ci- dessous) illustre l’utilisation de cette fonction.

FIGURE 2 : Cette figure montre l’utilisation des fonctions  EST UNE FORMULE et FORMULETEXTE.

Les nombres 1, 2 et 3 sont entrés dans la colonne A et, dans la colonne B, ils sont multipliés par 5. Copie de la formule = FORMULETEXTE (B4) de E4 à E5: E6 fait apparaître les formules de la colonne B dans la colonne E .

Les colonnes C et D illustrent l’utilisation d’une autre nouvelle fonction Excel, ESTFORMULE. Cette formule renvoie VRAI si une cellule contient une formule et FAUX sinon. Si vous copiez la formule = ESTFORMULE(A4) de C4 vers C4: D6, vous voyez que la colonne B contient des formules et pas la colonne A.

La vérification des erreurs, illustrée à la figure ci- dessus, vérifie la présence d’erreurs dans votre feuille de calcul et vous aide à résoudre les erreurs. Pour illustrer le fonctionnement de la fonction Excel Error Trap, consultez à nouveau le fichier Errortrap.xlsx du chapitre 10, «Références circulaires». (Voir figure ci-dessous.)

Cliquez sur la flèche déroulante pour obtenir trois options: Vérification des erreurs, Tracer les erreurs et Références circulaires. Votre classeur ne contient aucune référence circulaire, la référence circulaire est donc grisée. Si le classeur contient des références circulaires, Excel les met en surbrillance. Si vous sélectionnez Vérification des erreurs, Excel commence à rechercher des erreurs. La première erreur détectée se trouve dans la cellule E13; la boîte de dialogue illustrée à la figure 4 s’ouvre. En cliquant sur Aide sur cette erreur, vous accédez à plus d’informations sur l’erreur. Afficher les étapes de calcul vous permet de parcourir le calcul de la formule. Ignorer l’erreur ferme la  boite de dialogue. Modifier dans la barre de formule vous permet de modifier la formule. La sélection de Précédent vous ramène à l’erreur trouvée précédemment et Suivant vous envoie à la boîte de dialogue pour l’erreur suivante.

FIGURE 3 : Cette figure illustre la vérification des erreurs et les erreurs de trace.

FIGURE 4: Cette figure montre la boîte de dialogue Vérification des erreurs.

Si vous sélectionnez Tracer les erreurs dans le menu déroulant et que la cellule active contient une erreur, les cellules nécessaires pour calculer la cellule active sont indiquées par des flèches bleues ou un rectangle bleu. Par exemple, dans la figure  ci-dessous, la cellule D13 et la plage de cellules D3: E7 sont nécessaires pour calculer la cellule E13. (Cette cellule a généré une erreur # N / A.)

En utilisant le Wa Dans la fenêtre tch, vous pouvez sélectionner une ou plusieurs cellules, puis, peu importe où vous vous trouvez dans le classeur, vous voyez comment la valeur des cellules sélectionnées change. Par exemple, après avoir sélectionné la fenêtre de surveillance, vous pouvez sélectionner Ajouter une surveillance et ajouter une formule (par exemple, à la cellule C3 de la feuille 1). Maintenant, même si vous travaillez dans un autre classeur, la fenêtre de surveillance vous montre comment la cellule C3 de la feuille 1 change.

Les précédents de trace et les dépendants de trace localisent et affichent les précédents et les dépendants des cellules ou des formules de la feuille de calcul. Un précédent est une cellule dont la valeur est nécessaire pour calculer la valeur d’une formule sélectionnée. Par exemple, si vous analysiez une campagne de publipostage, vous feriez des hypothèses sur le nombre de lettres envoyées et le taux de réponse pour l’envoi. Ensuite, vous pouvez calculer le nombre de réponses sous forme de taux de réponse * lettres envoyées. Dans ce cas, le taux de réponse et le nombre total de lettres envoyées sont des précédents de la cellule contenant la formule utilisée pour calculer le nombre total de réponses. Une personne à charge est une cellule contenant une formule dont les valeurs ne peuvent pas être calculées sans connaître la cellule sélectionnée. Dans l’exemple précédent, la cellule contenant le nombre total de réponses est une dépendance de la cellule contenant le taux de réponse. Excel marque les précédents et les dépendants avec des flèches bleues lorsque vous utilisez l’outil d’audit. Le bouton Supprimer les flèches efface l’affichage des flèches.

Appliquons l’outil d’audit à certains problèmes pratiques.

Réponses aux questions de ce chapitre

Cette section fournit les réponses aux questions répertoriées au début du chapitre.

Je viens de recevoir une feuille de calcul de cinq mille lignes qui calcule la valeur actuelle nette (VAN) d’une nouvelle voiture. Dans la feuille de calcul, mon analyste financier a fait une hypothèse sur le pourcentage annuel de la croissance du prix du produit. Quelles cellules de la feuille de calcul sont affectées par cette hypothèse?

La feuille de calcul Modèle d’origine dans le fichier NPVaudit.xIsx contient des calculs qui calculent la VAN des bénéfices après impôt pour une voiture qui devrait être disponible auprès du fabricant pendant cinq ans. (Voir figure 5.) Le prix et le coût sont en milliers de dollars. Les valeurs des paramètres supposées pour l’analyse sont données dans les cellules C1: C8 (avec les noms de plage associés répertoriés dans les cellules B1: B8). Supposons que le prix du produit augmentera de 3% par an. Quelles cellules de la feuille de calcul dépendent de cette hypothèse?

Pour répondre à cette question, sélectionnez la cellule C8 (la cellule contenant l’hypothèse d’une croissance des prix de 3%), puis cliquez sur le bouton Tracer les dépendants dans le groupe Audit de formule sous l’onglet Formules. Excel affiche l’ensemble de flèches, illustré à la figure 6, pointant vers les personnes à charge directes de la cellule C8. Notez que pour que les traces dépendantes ou les traces précédentes (voir ci-dessous) fonctionnent correctement, le ruban entier doit s’afficher avant de cliquer sur les traces dépendantes. Appuyez sur Ctrl + F1 sert à basculer entre uniquement les onglets du ruban et toutes les options affichées sur le ruban.

FIGURE 5 : Vous pouvez utiliser l’outil d’audit pour tracer des formules dans des feuilles de calcul complexes.

En cliquant une fois sur le bouton Tracer les dépendants, Excel pointe vers les cellules qui dépendent directement de l’hypothèse de croissance des prix. Dans la figure 6, vous pouvez voir que seuls les prix unitaires pour les années 2 à 5 dépendent directement de cette hypothèse. Cliquer sur Trace Dependents affiche à plusieurs reprises toutes les formules dont le calcul nécessite la valeur de la croissance annuelle des prix, comme le montre la figure ci-dessous

FIGURE 6: Tracez les cellules directement dépendantes.

FIGURE 7:  En cliquant sur Trace Dependants, vous voyez à plusieurs reprises toutes les dépendances de l’hypothèse de croissance des prix.

Vous pouvez voir qu’en plus du prix unitaire des années 2 à 5, l’hypothèse de croissance des prix affecte les revenus des années 2 à 5, les bénéfices avant impôts, les impôts payés, les bénéfices après impôts et la VAN. Vous pouvez supprimer les flèches en cliquant sur le bouton Supprimer les flèches.

La combinaison de touches de CONTROL +] met en évidence toutes les personnes à charge directes de la cellule active en bleu; la combinaison de touches CONTROL + SHIFT +] met en surbrillance en bleu toutes les dépendances de la cellule active.

Je pense que mon analyste financier a fait une erreur dans le calcul du bénéfice avant impôt de l’année 1. Quelles cellules du modèle de feuille de calcul ont été utilisées pour ce calcul?

Vous voulez maintenant trouver les précédents pour la cellule B15, les cellules nécessaires pour calculer le bénéfice avant impôt de l’année 1. Sélectionnez la cellule B15, puis cliquez une fois sur le bouton Tracer les précédents. Vous voyez les flèches illustrées à la figure ci-dessous.

Comme vous pouvez le voir, les cellules nécessaires pour calculer directement le bénéfice de l’année 1 avant impôt sont les revenus de l’année 1 et le coût de l’année 1. (Le bénéfice avant impôt de l’année 1 est égal au chiffre d’affaires de l’année 1 moins le coût de l’année 1.) En cliquant plusieurs fois sur le bouton Tracer les précédents, vous obtenez tous les précédents du bénéfice avant impôt de l’année 1, comme le montre la figure 2 ci-dessous.

FIGURE 8 :  Il s’agit des précédents directs du bénéfice avant impôt de l’année 1.

FIGURE 9 :  Cliquez sur Tracer les précédents à plusieurs reprises pour afficher tous les précédents du bénéfice avant impôt de l’année 1.

Ici, les seules hypothèses d’entrée qui influencent le bénéfice avant impôt de l’année 1 sont les ventes de l’année 1, le prix de l’année 1 et le coût de l’année 1.

Comment fonctionne l’outil d’audit lorsque je travaille avec des données dans plusieurs feuilles de calcul ou classeurs?Considérez le modèle de feuille de calcul simple dans le classeur , illustré à la figure 10. La formule de la feuille de calcul Profit calcule le profit d’une entreprise (ventes unitaires * (prix – coût variable) – coût fixe) à partir des informations contenues dans la feuille de calcul Data.

FIGURE 10:  Cette figure présente les données d’utilisation de l’outil d’audit sur plusieurs feuilles de calcul.

Supposons que vous souhaitiez connaître les précédents de la formule de profit. Sélectionnez la cellule D7 dans la feuille de calcul Profit, puis cliquez sur Tracer les précédents dans le groupe Audit de formule sous l’onglet Formules. Vous voyez une ligne pointillée, une flèche et l’icône de feuille de calcul illustrée à la figure ci-dessous.

FIGURE 11 : Ceci est le résultat du traçage des précédents avec des données sur plusieurs feuilles de calcul.

L’icône de la feuille de calcul indique que les précédents de la formule de profit se trouvent dans une autre feuille de calcul. Double-cliquez sur la ligne pointillée pour ouvrir la boîte de dialogue Atteindre, illustrée à la figure .

Vous pouvez maintenant cliquer sur l’un des précédents répertoriés (cellules D4: D7 dans la feuille de calcul des données) et Excel vous enverra vers le précédent que  vous avez sélectionné.

FIGURE 12 : Avec la boîte de dialogue Aller à, vous pouvez auditer les données dans plusieurs feuilles de calcul.

Qu’est-ce que le nouveau complément Inquire et comment puis-je l’installer?

Si vous possédez Excel 2016 Professional Plus, vous avez accès au complément Inquire, une version plus sophistiquée de l’outil d’audit qui vous permet de:

■ Dérivez de nombreuses statistiques intéressantes sur le classeur actuel.

■ Comparez deux classeurs pour déterminer en quoi ils diffèrent.

■ Créez un diagramme sophistiqué des cellules qui sont des précédents ou des dépendants de la cellule actuelle.

■ Créez un diagramme sophistiqué des interrelations entre les feuilles de calcul dans le classeur actuel.

■ Nettoyer la mise en forme excessive d’un classeur.

■ Répertoriez les mots de passe associés au classeur.

■ Pour installer le complément Inquire, procédez comme suit:

  1. 1. Sélectionnez Fichier dans le ruban et choisissez Options.
  2. 2. Choisissez Compléments. Sélectionnez Compléments COM et cliquez sur OK.
  3. 3. Sélectionnez Demander un complément et cliquez sur OK. Vous devriez maintenant voir l’onglet DEMANDER sur le ruban.

Quelles informations le complément Inquire affiche-t-il sur un classeur?

Utilisez Inquire pour analyser votre classeur NPVaudit.xIsx. Ouvrez le classeur et sélectionnez l’onglet Ruban d’information. Le menu illustré à la figure ci-dessous apparaît.

FIGURE 13 : Il s’agit du menu Inquire.

La sélection de l’analyse du classeur produit les statistiques résultantes.

Après avoir sélectionné Analyse de classeur, la boîte de dialogue Rapport d’analyse de classeur s’ouvre. Vous pouvez vérifier toutes les fonctionnalités pour lesquelles vous souhaitez des détails ou sélectionner Résumé pour cocher toutes les options. La figure  ci-dessous montre les résultats lorsque Formules est sélectionné. Sur la droite, vous pouvez voir toutes les 12 cellules qui contiennent des formules dans le classeur.

La FIGURE 14: Inquire vous indique l’emplacement de toutes les formules dans un classeur.

Comment Inquire compare-t-il les classeurs?

Souvent, l’analyste remet son classeur à un collègue qui apporte des modifications au classeur. Le complément Inquire permet de comparer facilement deux classeurs et d’identifier les différences entre eux. Pour illustrer la façon dont Inquire compare les classeurs, ouvrez le fichier Prodmixtemp.xlsx. Ce classeur (qui est abordé plus en détail au chapitre 29, «Utilisation du solveur pour déterminer la combinaison de produits optimale») contient des données mais pas de formules, qui sont nécessaires pour déterminer une combinaison de produits maximisant les bénéfices. 

Le fichier contient les formules nécessaires pour évaluer la gamme de produits. Après avoir ouvert ce classeur, le complément Inquire peut être utilisé pour comparer ces classeurs. Sélectionnez Comparer les fichiers; vous êtes invité à répertorier les fichiers que vous souhaitez comparer. Après avoir sélectionné les fichiers, vous obtenez les résultats indiqués dans la figure 15. À droite, les cellules modifiées  qui sont modifiées sont mises en surbrillance.

FIGURE 15 : Inquire compare deux classeurs.

Comment Inquire montre-t-il les relations entre les formules de cellule?

Pour illustrer comment Inquire affiche les relations entre les cellules, revenez au fichier NPVaudit.xlsx. Si vous souhaitez examiner les relations entre les cellules impliquant la cellule B15, placez votre curseur dans la cellule B15 et sélectionnez Cellule; choisissez Tracer les deux pour afficher les dépendants et les précédents de la cellule B15. Le diagramme résultant est illustré à la figure  ci-dessous.

FIGURE 16: Cette figure montre les relations entre les cellules impliquant la cellule B15.

Comment Inquire montre-t-il les relations entre les feuilles de calcul?

Lorsqu’un classeur implique plusieurs feuilles de calcul, il est souvent difficile de comprendre comment les feuilles de calcul sont liées. Le complément Inquire permet de déterminer facilement comment les feuilles de calcul sont liées.

Pour illustrer comment Inquire met en lumière la relation entre deux feuilles de calcul, rouvrez le fichier Audittwosheetstemp.xlsx et sélectionnez Relations entre les feuilles de calcul dans l’onglet du ruban Inquire.

La figure ci-dessous montre les résultats. D’après la figure ci-dessous, il apparaît que les informations de la feuille de calcul Données sont nécessaires pour calculer les résultats dans la feuille de calcul Profit.

La FIGURE  17: Inquire montre les relations entre les feuilles de calcul.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x