Excel 2019 (partie 28) : analyse de simulation (analyse si-alors)

Excel comprend des outils puissants pour effectuer des calculs mathématiques complexes, notamment une analyse de simulation. Cette fonctionnalité peut vous aider à expérimenter et à répondre aux questions avec vos données, même si elles sont incomplètes. Dans cet article, vous apprendrez à utiliser un outil d'analyse appelé Goal Seek.

Recherche d'objectif

Chaque fois que vous créez une formule ou une fonction dans Excel, vous devez combiner différentes parties pour calculer le résultat. Goal Seek fonctionne dans le sens inverse : il vous permet de commencer avec un résultat souhaité et le moteur calculera la valeur d'entrée qui vous donne ce résultat. L'article utilisera quelques exemples pour montrer comment utiliser Goal Seek.

Exemple 1 d'utilisation de la recherche d'objectif

Disons que vous êtes inscrit à un cours. Vous avez actuellement 65 points et vous devez obtenir au moins 70 points pour réussir cette matière. Heureusement, vous avez une dernière mission qui peut vous aider à augmenter votre GPA. Vous pouvez utiliser Goal Seek pour connaître le score dont vous avez besoin lors de l'exercice final pour réussir le sujet.

Dans l'image ci-dessous, vous pouvez voir que les scores des 4 premiers exercices sont 58, 70, 72 et 60. Bien que l'on ne sache pas quel sera le score du 5ème exercice, vous pouvez écrire une formule - ou une fonction - pour calculer le score final nécessaire. Dans ce cas, chaque devoir est pondéré de manière égale, il suffit donc de faire la moyenne des cinq scores en entrant =AVERAGE(B2:B6). Lorsque vous utilisez Goal Seek, la cellule B6 affichera le score minimum requis pour cette mission finale.

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

2. Dans l' onglet Données , cliquez sur la commande Analyse de simulation , puis sélectionnez Recherche d'objectif dans le menu déroulant.

3. Une boîte de dialogue apparaîtra avec trois champs. Le premier champ, Set cell: , contiendra le résultat souhaité. Dans l'exemple, la cellule B7 a été sélectionnée.

Le deuxième champ, To value: , est le résultat souhaité. Par exemple, nous entrerions 70 parce que nous devons gagner au moins ce nombre pour réussir le cours.

Le troisième champ, En changeant de cellule : , est la cellule où Goal Seek placera sa réponse. Par exemple, nous choisirons la cellule B6 car nous voulons déterminer le score que nous devons atteindre lors de l’exercice final.

4. Lorsque vous avez terminé, cliquez sur OK.

5. La boîte de dialogue vous indiquera si Goal Seek a réussi à trouver la réponse ou non. Cliquez sur OK.

6. Les résultats apparaîtront dans la cellule spécifiée. Dans l'exemple, Goal Seek a calculé qu'un score d'au moins 90 pour le devoir final serait nécessaire pour réussir.

Exemple 2 d'utilisation de la recherche d'objectif

Disons que vous planifiez un événement et que vous souhaitez inviter autant de personnes que possible sans dépasser votre budget de 500 $. Vous pouvez utiliser Goal Seek pour savoir combien de personnes inviter. Dans l'exemple ci-dessous, la cellule B5 contient la formule =B2+B3*B4 pour calculer le coût total de la réservation plus le coût par personne.

1. Sélectionnez la cellule dont vous souhaitez modifier la valeur. Par exemple, nous sélectionnerons la cellule B5.

2. Dans l' onglet Données , cliquez sur la commande Analyse de simulation , puis sélectionnez Recherche d'objectif dans le menu déroulant.

3. Une boîte de dialogue apparaîtra avec trois champs. Le premier champ, Set cell: , contiendra le résultat souhaité. Dans l'exemple, la cellule B5 a été sélectionnée.

Le deuxième champ, To value: , est le résultat souhaité. Par exemple, je saisirais 500 parce que je souhaite seulement dépenser 500 $.

Le troisième champ, En changeant de cellule : , est la cellule où Goal Seek placera sa réponse. Par exemple, je choisirais la cellule B4 parce que je veux savoir combien d'invités je peux inviter sans avoir à dépenser plus de 500 $.

4. Lorsque vous avez terminé, cliquez sur OK.

5. La boîte de dialogue vous indiquera si Goal Seek a réussi à trouver la réponse ou non. Cliquez sur OK.

6. Les résultats apparaîtront dans la cellule spécifiée. Dans l'exemple, Goal Seek a calculé que la réponse était d'environ 18,62. Dans ce cas, la réponse finale doit être un nombre entier, il est donc nécessaire d’arrondir la réponse vers le haut ou vers le bas. Étant donné que l'arrondi dépasserait le budget, l'exemple serait d'arrondir à 18 invités.

Comme vous pouvez le voir dans l’exemple ci-dessus, certaines situations nécessiteront une réponse entière. Si Goal Seek vous donne un nombre décimal, vous devrez l'arrondir vers le haut ou vers le bas, selon la situation.

Autres types d'analyses de simulation

Pour les projets plus avancés, vous souhaiterez peut-être envisager d’autres types d’analyses de simulation : scénarios et tableaux de données. Au lieu de partir du résultat souhaité et de travailler à rebours, comme avec Goal Seek, ces options vous permettent de tester plusieurs valeurs et de voir comment les résultats changent.

Les scripts vous permettent de remplacer les valeurs de plusieurs cellules (jusqu'à 32) à la fois. Vous pouvez créer autant de scénarios que vous le souhaitez puis les comparer sans modifier les valeurs manuellement. L'exemple ci-dessous utilise des scénarios pour comparer différents emplacements pour un événement à venir.

Les tableaux de données vous 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 à la fois, contrairement aux scénarios ou à la recherche d'objectifs. Dans l’exemple ci-dessous, 24 résultats possibles peuvent être observés pour un prêt automobile.