Cours Excel 2019 gratuit | Leçon 24 – Analyse de simulation

Le cours Excel numéro 24 dans la série cours Excel 2019.

cours Excel gratuit 2019 / cours et tutoriel Excel 2019 /formation Excel en ligne/ guide Excel en ligne

Excel comprend des outils puissants pour effectuer des calculs mathématiques complexes, y compris des analyses de simulation. Cette fonctionnalité peut vous aider à expérimenter et à répondre aux questions avec vos données, même lorsque les données sont incomplètes. Dans cette leçon, vous apprendrez à utiliser un outil d’analyse de simulation appelé Goal Seek.

Rechercher le but

Chaque fois que vous créez une formule ou une fonction dans Excel, vous assemblez différentes parties pour calculer un résultat. Goal Seek fonctionne à l’opposé – il vous permet de commencer avec le résultat souhaité et calcule la valeur d’entrée qui vous donnera ce résultat. Nous utiliserons quelques exemples pour vous montrer comment utiliser Goal Seek.

Pour utiliser la recherche d’objectif (exemple 1) :

Disons que vous êtes inscrit à un cours. Vous avez actuellement une note de 65 et vous avez besoin d’au moins 70 pour réussir le cours. Heureusement, vous avez une mission finale qui peut être en mesure d’augmenter votre moyenne. Vous pouvez utiliser Goal Seek pour savoir de quelle note vous avez besoin pour le devoir final afin de réussir le cours.

Dans l’image ci-dessous, vous pouvez voir que les notes des quatre premiers devoirs sont 58, 70, 72 et 60. Même si nous ne savons pas quelle sera la cinquième note, nous pouvons écrire une formule ou une fonction qui calcule la note finale. Dans ce cas, chaque devoir a le même poids, donc tout ce que nous avons à faire est de calculer la moyenne des cinq notes en tapant = MOYENNE (B2 : B6). Une fois que nous utilisons Goal Seek, la cellule B6 nous montrera la note minimale que nous devons donner pour cette tâche.

Sélectionnez la cellule avec la valeur que vous souhaitez modifier. Chaque fois que vous utilisez Goal Seek, vous devrez sélectionner une cellule qui contient déjà une formule ou une fonction. Dans notre exemple, nous allons sélectionner la cellule B7 car elle contient la formule = MOYENNE (B2 : B6).

Dans l’onglet Données, cliquez sur la commande Analyse d’hypothèses, puis sélectionnez Recherche d’objectif dans le menu déroulant.

Une boîte de dialogue avec trois champs apparaîtra. Le premier champ, Set Cell:, contiendra le résultat souhaité. Dans notre exemple, la cellule B7 est déjà sélectionnée.
Le deuxième champ, To value:, est le résultat souhaité. Dans notre exemple, nous entrerons 70 car nous devons gagner au moins cela pour réussir la classe.
Le troisième champ, Cellule changeante :, est la cellule dans laquelle Goal Seek entrera sa réponse. Dans notre exemple, nous sélectionnerons la cellule B6 car nous voulons déterminer la note que nous devons obtenir dans le devoir final.
Lorsque vous avez terminé, cliquez sur OK.

La boîte de dialogue vous indiquera si Goal Seek a pu trouver une solution. Cliquez sur OK.

Le résultat apparaîtra dans la cellule spécifiée. Dans notre exemple, Goal Seek a calculé que nous devrons obtenir au moins un score de 90 dans le devoir final pour obtenir une note positive.

Pour utiliser la recherche d’objectif (exemple 2) :

Disons que vous planifiez un événement et que vous souhaitez inviter autant de personnes que possible sans dépasser un budget de 500 $. Nous pouvons utiliser Goal Seek pour déterminer le nombre de personnes à inviter. Dans notre exemple ci-dessous, la cellule B5 contient la formule = B2 + B3 * B4 pour calculer le coût total d’une réservation de chambre, plus le coût par personne.

Sélectionnez la cellule avec la valeur que vous souhaitez modifier. Dans notre exemple, nous sélectionnerons la cellule B5.

Dans l’onglet Données, cliquez sur la commande Analyse d’hypothèses, puis sélectionnez Recherche d’objectif dans le menu déroulant.

Une boîte de dialogue avec trois champs apparaîtra. Le premier champ, Set Cell:, contiendra le résultat souhaité. Dans notre exemple, la cellule B5 est déjà sélectionnée.
Le deuxième champ, To value:, est le résultat souhaité. Dans notre exemple, nous entrerons 500 car nous ne voulons dépenser que 500 $.
Le troisième champ, Cellule changeante :, est la cellule dans laquelle Goal Seek entrera sa réponse. Dans notre exemple, nous sélectionnerons la cellule B4 car nous voulons savoir combien d’invités nous pouvons inviter sans dépenser plus de 500 $.
Lorsque vous avez terminé, cliquez sur OK.

La boîte de dialogue vous indiquera si Goal Seek a pu trouver une solution. Cliquez sur OK.
Le résultat apparaîtra dans la cellule spécifiée. Dans notre exemple, Goal Seek a calculé que la réponse était d’environ 18,62. Dans ce cas, notre réponse finale doit être un nombre entier, nous devrons donc arrondir la réponse vers le haut ou vers le bas. Comme arrondir nous ferait dépasser le budget, nous arrondirons à 18 invités.

Comme vous pouvez le voir dans l’exemple ci-dessus, certaines situations nécessitent que la réponse soit un nombre entier. Si Goal Seek vous donne une décimale, vous devrez arrondir vers le haut ou vers le bas, selon la situation.

Autres types d’analyse de simulation

Pour des projets plus avancés, vous voudrez peut-être envisager les autres types d’analyse de simulation : les scénarios et les tableaux de données. Au lieu de commencer par le résultat souhaité et de revenir en arrière, comme avec Goal Seek, ces options vous permettent de tester plusieurs valeurs et de voir comment les résultats changent.

Scénarios

vous permettent de remplacer les valeurs de plusieurs cellules (jusqu’à 32) en même temps. Vous pouvez créer autant de scénarios que vous le souhaitez, puis les comparer sans modifier manuellement les valeurs. Dans l’exemple suivant, nous utilisons des scénarios pour comparer différents emplacements pour un événement à venir.

Tableaux de données

permettent de prendre une ou deux variables dans une formule et de les remplacer par autant de valeurs différentes que vous le souhaitez, puis d’afficher les résultats dans un tableau. Cette option est particulièrement puissante car elle affiche plusieurs résultats en même temps, contrairement aux scénarios ou à la recherche d’objectifs. Dans l’exemple suivant, nous pouvons voir 24 résultats possibles pour un prêt auto.