Comment utiliser les outils d’analyse What-If dans Microsoft Excel

Lorsque vous analysez vos données dans Microsoft Excel, vous souhaiterez peut-être effectuer des comparaisons, telles que « Et si je choisis l’option A au lieu de l’option B ? » À l’aide des outils d’analyse What-If intégrés dans Excel, vous pouvez comparer plus facilement des nombres et des montants, par exemple pour évaluer les salaires, les options de prêt ou les scénarios de revenus et de dépenses.

Les outils d’analyse What-If dans Excel incluent le gestionnaire de scénarios, la recherche d’objectifs et le tableau de données. Pour mieux expliquer le but de ces outils, examinons des exemples de chacun.

Gestionnaire de scénarios

À l’aide du gestionnaire de scénarios, entrez des valeurs que vous pouvez modifier pour voir différents résultats. En prime, créez un rapport de synthèse de scénario pour comparer les montants ou les chiffres côte à côte.

Par exemple, disons que vous planifiez un événement et que vous décidez entre quelques thèmes qui ont des coûts différents. Configurez les prix de chaque thème pour voir combien ils coûteront pour les comparer.

Créez divers scénarios pour différentes situations pour vous aider dans votre prise de décision.

Comment utiliser le gestionnaire de scénarios

Si vous êtes prêt à comparer différentes situations, comme dans l’exemple ci-dessus, suivez ces étapes pour utiliser le gestionnaire de scénarios dans Excel.

• Entrez les données de votre premier scénario dans votre feuille. En utilisant l’exemple précédent, nous comparons les coûts de thème pour notre événement et entrons les dépenses pour notre thème de plage dans les cellules A2 à A6 et leurs coûts dans les cellules B2 à B6. Nous additionnons les prix dans la cellule B7 pour voir le coût total.

• Pour ajouter ces détails au gestionnaire de scénarios, accédez à l’onglet « Données » et à la section « Prévisions » du ruban. Ouvrez le menu déroulant « What-If Analysis » et choisissez « Scenario Manager ».

• Cliquez sur « Ajouter ».

• Donnez un nom à votre scénario (nous utilisons « Beach Theme ») et entrez les cellules que vous ajusterez dans le champ « Changer les cellules ». Vous pouvez également faire glisser votre curseur dans les cellules de votre feuille pour remplir ce champ. Si vous le souhaitez, saisissez un commentaire autre que celui par défaut, puis cliquez sur « OK ».

• Les valeurs du champ « Modifier les cellules » doivent correspondre à celles de votre feuille, mais vous pouvez les ajuster ici. Cliquez sur « OK » pour continuer.

• Maintenant que vous avez ajouté le premier scénario, vous le verrez répertorié dans le gestionnaire de scénarios. Sélectionnez « Ajouter » pour configurer votre prochain scénario.

• Saisissez les détails du deuxième scénario comme vous l’avez fait pour le premier. Incluez le nom, les cellules changeantes et le commentaire facultatif, puis cliquez sur « OK ». Dans notre exemple, nous entrons dans « Vegas Theme » et la même plage de cellules, B2 à B6, pour voir facilement une comparaison sur place.

• Saisissez les valeurs de votre deuxième scénario dans la fenêtre Valeurs du scénario. Si vous utilisez les mêmes cellules que la première, vous les verrez remplies. Entrez ceux que vous souhaitez utiliser et cliquez sur « OK ».

• Choisissez le scénario que vous souhaitez afficher dans la liste de la fenêtre du gestionnaire de scénarios, puis cliquez sur « Afficher ».

• Les valeurs de votre feuille seront mises à jour pour afficher le scénario sélectionné.

• Continuez à ajouter et à afficher des scénarios supplémentaires pour afficher les valeurs mises à jour dans votre feuille. Une fois que vous avez trouvé celui que vous souhaitez conserver dans votre feuille, sélectionnez « Fermer » pour quitter le gestionnaire de scénarios.

Afficher le résumé du scénario

Affichez le résumé du scénario pour voir tous vos scénarios à la fois afin d’effectuer une comparaison côte à côte.

• Revenez à « Données -> Analyse What-If -> Gestionnaire de scénarios », puis cliquez sur « Résumé ».

• Choisissez le type de rapport que vous souhaitez afficher : « Résumé du scénario » ou  » Rapport de tableau croisé dynamique du scénario « . Facultativement, si vous souhaitez afficher votre résultat, entrez la cellule qui le contient et cliquez sur « OK ».

Dans notre exemple, nous sélectionnons « Scenario Summary », ce qui place le rapport dans un nouvel onglet de feuille. Vous remarquerez également que le rapport peut éventuellement inclure un groupement de cellules pour masquer certaines parties du rapport.

Notez que si vous ajustez les détails dans le gestionnaire de scénarios, le rapport ne se met pas à jour automatiquement, vous devez donc générer un nouveau rapport.

Recherche d’objectif

L’outil de recherche d’objectifs fonctionne un peu à l’opposé du gestionnaire de scénarios. Avec cet outil, vous avez un résultat connu et entrez différentes variables pour voir comment vous pouvez arriver à ce résultat.

Par exemple, vous vendez peut-être des produits et avez un objectif de profit annuel. Vous voulez savoir combien d’unités vous devez vendre ou à quel prix pour atteindre votre objectif. Goal Seek est l’outil idéal pour trouver des réponses.

Avec Goal Seek, une seule variable ou valeur d’entrée peut être utilisée. Utilisez ceci pour les scénarios où vous avez les valeurs restantes à l’avance.

Comment utiliser la recherche d’objectifs

Dans un exemple de l’outil Goal Seek, nous avons 1 500 produits à vendre et souhaitons réaliser un bénéfice de 52 000 $. Nous utilisons Goal Seek pour déterminer à quel prix nous devrions vendre notre produit pour atteindre cet objectif.

• Commencez par entrer les valeurs et les formules dans votre feuille, selon votre scénario. En utilisant notre exemple, nous saisissons la quantité actuelle dans la cellule B2, le prix estimé dans la cellule B3 et une formule pour le bénéfice dans la cellule B4, qui est =B2*B3.

• Allez dans l’onglet « Données », ouvrez le menu déroulant « Analyse de simulation » et choisissez « Recherche d’objectif ».

• Entrez les valeurs suivantes, puis cliquez sur « OK »:
  • Définir la cellule : la référence de cellule (contenant la formule) pour la valeur que vous souhaitez modifier pour atteindre le résultat souhaité. Dans notre exemple, il s’agit de la cellule B4.
  • Valoriser : la valeur de votre résultat souhaité. Pour nous, c’est 52000.
  • En changeant de cellule : la référence de cellule que vous souhaitez modifier pour atteindre le résultat. Nous utilisons la cellule B3, car nous voulons modifier le prix.

• Cliquez sur « OK » pour voir la mise à jour de la boîte « Statut de recherche d’objectif » pour afficher une solution et votre feuille changer pour contenir les valeurs ajustées. Dans notre exemple, nous devons vendre notre produit à 35 $ pour atteindre notre objectif de 52 000 $. Sélectionnez « OK » pour conserver les nouvelles valeurs dans votre feuille.

Le saviez-vous : il y a beaucoup de choses que vous pouvez faire dans Microsoft Excel, y compris l’insertion de graphiques sparkline et de mini-graphiques .

Tableau de données

Utilisez un tableau de données dans Excel pour afficher une gamme de situations numériques possibles.

Pour un exemple idéal, vous examinez peut-être les options de prêt. En saisissant différents taux d’intérêt, vous pouvez voir quel serait votre paiement mensuel avec chacun. Cela vous aide à déterminer le taux à acheter ou à discuter avec votre prêteur.

Avec un tableau de données, vous ne pouvez utiliser qu’un maximum de deux variables. Si vous avez besoin de plus, utilisez le gestionnaire de scénarios.

Comment utiliser un tableau de données

Suivez les étapes ci-dessous pour utiliser un tableau de données, le troisième outil d’analyse What-If. Notez la configuration des données.

Par exemple, nous utilisons un tableau de données pour voir le montant de nos remboursements de prêt avec différents taux d’intérêt via les données suivantes :

• Taux d’intérêt, nombre de paiements et montant du prêt dans les cellules B3 à B5.
• Une colonne Taux avec les taux d’intérêt à explorer dans les cellules C3 à C5.
• Une colonne Paiement avec la formule du paiement actuel dans la cellule D2.
• Les cellules de résultat sous la formule dans la colonne Paiement, saisies automatiquement à l’aide de l’outil Tableau de données. Cela nous montre les montants des paiements par taux d’intérêt.

Lorsque vous saisissez vos données et votre formule dans votre feuille, gardez à l’esprit les points suivants :

• Utilisez une mise en page orientée ligne ou colonne. Il déterminera le placement de votre formule.
• Pour une mise en page orientée ligne, placez votre formule dans la cellule une colonne à gauche de la valeur initiale et une cellule en dessous de la ligne contenant les valeurs.
• Pour une mise en page orientée colonne, placez votre formule dans la cellule une ligne au-dessus et une cellule à droite de la colonne contenant les valeurs.

Dans notre exemple, nous utilisons une seule variable (le taux d’intérêt) dans une mise en page orientée colonne. Notez le placement de notre formule dans la cellule D2 (une ligne au-dessus et une cellule à droite de nos valeurs).

• Entrez vos propres données et sélectionnez les cellules contenant la formule, les valeurs et les cellules de résultat. Dans notre exemple, nous sélectionnons les cellules C2 à D5.

• Allez dans l’onglet « Données », ouvrez le menu déroulant « Analyse de simulation » et choisissez « Tableau de données ».

• Entrez la cellule contenant la variable changeante pour vos données dans la zone Tableau de données. Pour une mise en page orientée ligne, utilisez la « Cellule d’entrée de ligne » et pour une mise en page orientée colonne, utilisez la « Cellule d’entrée de colonne ». Dans notre exemple, nous utilisons ce dernier et entrons « B3 », qui est la cellule contenant le taux d’intérêt.

• Après avoir cliqué sur « OK » dans la zone Tableau de données, vous devriez voir les cellules de résultat se remplir avec les données que vous attendez. Notre exemple comprend le montant de notre paiement pour chaque taux d’intérêt différent.

Notez que vous pouvez utiliser deux variables dans votre tableau de données au lieu d’une, essayer la mise en page orientée ligne ou afficher plus de détails et les limitations de cet outil d’analyse What-If sur la page d’assistance de Microsoft pour la fonctionnalité .

Questions fréquemment posées

Comment modifier un scénario existant dans Excel ?

Vous pouvez modifier le nom et les valeurs d’un scénario à l’aide du gestionnaire de scénarios. Ouvrez l’outil en sélectionnant « Données -> Analyse de simulation -> Gestionnaire de scénarios ». Choisissez le scénario dans la liste et cliquez sur « Modifier » à droite. Effectuez vos modifications et choisissez « OK » pour les enregistrer.

Si vous avez initialement créé un rapport Résumé du scénario, vous devrez régénérer le rapport pour afficher les détails mis à jour.

Puis-je empêcher Excel de recalculer un tableau de données ?

Si votre classeur contient une table de données, Excel recalcule automatiquement cette table de données, même s’il n’y a aucune modification. Cependant, vous pouvez désactiver cette option si vous le souhaitez.

Allez dans l’onglet « Formule », ouvrez le menu déroulant « Options de calcul » dans le groupe Calcul et sélectionnez « Automatique sauf pour les tableaux de données ».

Pour recalculer votre tableau de données manuellement, sélectionnez la ou les formules et appuyez sur F9.

Quels autres outils d’analyse Excel propose-t-il ?

Excel fournit de nombreux types d’outils d’analyse de données, en fonction de vos besoins. Pour n’en nommer que quelques-uns, vous pouvez utiliser la mise en forme conditionnelle pour mettre en évidence des données spécifiques, l’analyse rapide pour la mise en forme, les graphiques et les tableaux, et Power Query pour une analyse de données robuste.

Vous pouvez également utiliser les fonctionnalités de base d’Excel, telles que les filtres , pour affiner vos données, les segments pour filtrer les tableaux et les graphiques, et l’outil Analyser les données pour obtenir des réponses aux questions sur vos données.

Pour une assistance supplémentaire sur ces fonctionnalités et bien plus encore, accédez à l’onglet « Aide » dans Excel sur Windows ou utilisez l’option de menu « Dis-moi » dans Excel sur Mac.

Crédit image : Pixabay . Toutes les captures d’écran de Sandy Writtenhouse.