Analyse de simulation dans Excel

Excel contient de nombreux outils puissants pour effectuer des calculs mathématiques complexes, tels que Et si l'analyse. Cet outil est capable de trouver expérimentalement une solution à vos données d'origine, même si les données sont incomplètes. Dans ce tutoriel, vous apprendrez à utiliser l'un des outils analyse "et si" appelé Sélection des paramètres.

Sélection des paramètres

Chaque fois que vous utilisez une formule ou une fonction dans Excel, vous collectez les valeurs d'origine pour obtenir le résultat. Sélection des paramètres fonctionne dans l'autre sens. Il permet, en fonction du résultat final, de calculer la valeur initiale qui donnera un tel résultat. Ci-dessous, nous donnons quelques exemples pour montrer comment cela fonctionne. Sélection des paramètres.

Comment utiliser la sélection de paramètres (exemple 1) :

Imaginez que vous allez dans un certain établissement d'enseignement. Pour le moment, vous avez marqué 65 points et vous avez besoin d'un minimum de 70 points pour réussir la sélection. Heureusement, il existe une tâche finale qui peut augmenter vos points. Dans cette situation, vous pouvez utiliser Sélection des paramètrespour savoir quel score vous devez obtenir lors de la dernière mission afin d'entrer dans un établissement d'enseignement.

Dans l'image ci-dessous, vous pouvez voir que vos scores pour les deux premières tâches (test et écriture) sont 58, 70, 72 et 60. Bien que nous ne sachions pas quel sera votre score pour la dernière tâche (test 3) , nous pouvons écrire une formule qui calcule le score moyen pour toutes les tâches à la fois. Tout ce dont nous avons besoin est de calculer la moyenne arithmétique des cinq notes. Pour cela, entrez l'expression =CŒUR(B2:B6) à la cellule B7. Après avoir postulé Sélection des paramètres Pour résoudre ce problème, la cellule B6 affichera le score minimum que vous devez obtenir pour entrer dans un établissement d'enseignement.

1. Sélectionnez la cellule dont vous souhaitez obtenir la valeur. Chaque fois que vous utilisez l'outil Sélection des paramètres, Vous devez sélectionner une cellule qui contient déjà une formule ou une fonction. Dans notre cas, nous allons sélectionner la cellule B7 car elle contient la formule =CŒUR(B2:B6).
2. Dans l'onglet Avancé Données choisis une équipe Et si l'analyse, puis dans le menu déroulant, cliquez sur Sélection des paramètres.
3. Une boîte de dialogue apparaîtra avec trois champs :
   • Bouchemise à jour dans la cellule est la cellule qui contient le résultat souhaité. Dans notre cas, il s'agit de la cellule B7 et nous l'avons déjà sélectionnée.
   • Valeur est le résultat souhaité, c'est-à-dire le résultat qui devrait se trouver dans la cellule B7. Dans notre exemple, nous entrerons 70 car vous devez obtenir un score minimum de 70 pour entrer.
   • Changer la valeur d'une cellule – la cellule où Excel affichera le résultat. Dans notre cas, nous sélectionnerons la cellule B6 car nous voulons connaître la note que nous voulons obtenir sur la dernière tâche.
4. Après avoir terminé toutes les étapes, cliquez sur OK.
5. Excel calculera le résultat et dans la boîte de dialogue Résultat de la sélection des paramètres fournir une solution, le cas échéant. Cliquez sur OK.
6. Le résultat apparaîtra dans la cellule spécifiée. Dans notre exemple Sélection des paramètres définissez que vous devez obtenir un minimum de 90 points pour la dernière tâche afin de passer à autre chose.

Comment utiliser la sélection de paramètres (exemple 2) :

Imaginons que vous planifiez un événement et que vous souhaitez inviter autant d'invités que possible pour respecter le budget de 500 $. Vous pouvez utiliser Sélection des paramètrespour calculer le nombre d'invités que vous pouvez inviter. Dans l'exemple suivant, la cellule B4 contient la formule =B1+B2*B3, qui résume le coût total de la location d'une chambre et le coût de l'hébergement de tous les invités (le prix pour 1 invité est multiplié par leur nombre).

1. Sélectionnez la cellule dont vous souhaitez modifier la valeur. Dans notre cas, nous sélectionnerons la cellule B4.
2. Dans l'onglet Avancé Données choisis une équipe Et si l'analyse, puis dans le menu déroulant, cliquez sur Sélection des paramètres.
3. Une boîte de dialogue apparaîtra avec trois champs :
   • Уmettre dans une cellule est la cellule qui contient le résultat souhaité. Dans notre exemple, la cellule B4 est déjà sélectionnée.
   • Valeur est le résultat recherché. Nous entrerons 500 puisqu'il est acceptable de dépenser 500 $.
   • Modificationsje valeur de cellule – la cellule où Excel affichera le résultat. Nous allons mettre en surbrillance la cellule B3 car nous devons calculer le nombre d'invités que nous pouvons inviter sans dépasser notre budget de 500 $.
4. Après avoir terminé toutes les étapes, cliquez sur OK.
5. Fenêtre de dialogue Résultat de la sélection des paramètres vous indiquera si une solution a été trouvée. Cliquez sur OK.
6. Le résultat apparaîtra dans la cellule spécifiée. Dans notre cas Sélection des paramètres calculé le résultat 18,62. Puisque nous comptons le nombre d'invités, notre réponse finale doit être un nombre entier. Nous pouvons arrondir le résultat vers le haut ou vers le bas. En arrondissant le nombre d'invités, nous dépasserons le budget accordé, ce qui signifie que nous nous arrêterons à 18 invités.

Comme vous pouvez le voir dans l'exemple précédent, certaines situations nécessitent un nombre entier comme résultat. Si un Sélection des paramètres renvoie une valeur décimale, arrondissez-la vers le haut ou vers le bas selon le cas.

Autres types d'analyse de simulation

D'autres types peuvent être utilisés pour résoudre des problèmes plus complexes. analyse "et si" – Scénarios ou tableaux de données. Contrairement à Sélection des paramètres, qui s'appuie sur le résultat souhaité et fonctionne à l'envers, ces outils vous permettent d'analyser plusieurs valeurs et de voir comment le résultat change.

• Дgestionnaire de script vous permet de substituer des valeurs dans plusieurs cellules à la fois (jusqu'à 32). Vous pouvez créer plusieurs scripts, puis les comparer sans modifier manuellement les valeurs. Dans l'exemple suivant, nous utilisons des scénarios pour comparer plusieurs lieux différents pour un événement.
• Tables données vous permettent de prendre l'une des deux variables dans la formule et de la remplacer par n'importe quel nombre de valeurs, et de résumer les résultats dans un tableau. Cet outil a les possibilités les plus larges, car il affiche plusieurs résultats à la fois, contrairement à Gestionnaire de script or Sélection des paramètres. L'exemple suivant montre 24 résultats possibles pour les remboursements mensuels :