Aperçu des sections

  • VII. LA RECHERCHE DE SOLUTIONS

    A. VALEUR CIBLE

    La valeur cible prend en compte trois éléments :

    *     une cellule à définir

    *     un objectif à atteindre

    *     des cellules à modifier

    Bien évidemment, il doit y avoir une relation (directe ou indirecte) entre la cellule à définir (qui contient une formule) et les cellules à modifier (qui contiennent des valeurs).

     onglet "données"
    groupe "prévision"
    (6ème bloc)
    <clic g> sur la cellule à définir
    <clic g> sur  
    <clic g> sur
    indiquer la valeur à atteindre
    désigner la cellule à modifier
     pour valider

     Ouvrir "valcib" (dossier solveur)
    Sachant que le salaire net est égal au salaire brut moins les charges sociales salariales
    Déterminer le salaire brut permettant d'obtenir un salaire net de 1500 € pour 150 heures de travail

    B. GESTIONNAIRE DE SCÉNARIOS

    Le gestionnaire de scénarios permet d'enregistrer des combinaisons de valeurs de cellules d'une feuille de travail et de donner un nom à ce "scénario".
    Les solutions, proposées par la recherche d'une "valeur cible" ou par le "solveur" en fonction des hypothèses et des contraintes données, peuvent ainsi être conservées.

     onglet "données"
    groupe "prévision"
    (6ème bloc)
    <clic g> sur la cellule à définir
    <clic g> sur
    <clic g> sur
    indiquer la valeur à atteindre
    désigner la cellule à modifier
     pour valider

    paramètres

     : joue le scénario sélectionné en remplaçant les valeurs des cellules variables

     : ajoute un scénario en proposant les valeurs actuelles des cellules variables

     : supprime le scénario sélectionné

     : modifie le scénario sélectionné

     : fusionne les scénarios de feuilles de travail ouvertes

     : propose une synthèse des scénarios existants sur une feuille de travail

    1.ENREGISTRER UN SCÉNARIO

    Pour enregistrer un scénario, il faut auparavant afficher dans la feuille les valeurs voulues, soit directement, soit par l'intermédiaire de la valeur cible ou du solveur.

     onglet "données"
    groupe "prévision"
    (6ème bloc)
    <clic g> sur la cellule à définir
    <clic g> sur
    <clic g> sur
    <clic g> sur
    nommer
    le scénario
    accepter ou modifier les cellules variables
     pour valider
    (pour indiquer plusieurs cellules variables, utiliser <ctrl>)

     Les boîtes de dialogue "modifier un scénario" et "ajouter un scénario" sont identiques

     Enregistrer directement le scénario correspondant aux solutions du solveur à partir de ce dernier à l'issue de la résolution du problème en choisissant "enregistrer le scénario".

    2.AFFICHER UN SCÉNARIO

    Un scénario, une fois enregistré, peut être affiché à volonté ; Pour conserver les scénarios d'une session à l'autre, il faut enregistrer la feuille de travail.

     onglet "données"
    groupe "prévision"
    (6ème bloc)
    <clic g> sur la cellule à définir
    <clic g> sur
    <clic g> sur
    <clic g> sur
    les cellules prennent les valeurs indiquées dans le scénario

     Dans "valcib", Enregistrer en tant que scénarios les solutions aux 2 problèmes suivants
    1 -Déterminer le salaire brut correspondant à un salaire net de 1500 € pour 150 h de travail
    2 - Déterminer le nombre d'heures de travail nécessaire pour obtenir un salaire net de 1500 € pour à partir d'un salaire brut de 2500 €
    Les afficher et comparer avec "valcib fini"

    3.LA SYNTHÈSE DES SCÉNARIOS

    La synthèse des scénarios est un tableau croisé synthétisant toutes les options.

     onglet "données"
    groupe "prévision"
    (6ème bloc)
    <clic g> sur la cellule à définir
    <clic g> sur
    <clic g> sur
    <clic g> sur
    indiquer
    la cellule résultante
     pour valider
    une feuille de travail nommée "Synthèse de scénarios" s'affiche

     Dans le classeur "valcib", afficher la synthèse des scénarios

    C. SOLVEUR

    Le solveur de Microsoft Excel permet d'effectuer des simulations. Le plus difficile est cependant de modéliser préalablement le problème dans une feuille de calcul.

     La modélisation d'un problème dans un classeur Excel peut être complexe mais si le problème est bien posé, l'utilisation du solveur est simple :
    Consacrer le temps nécessaire à une analyse approfondie du problème et à la manière de le transcrire dans Excel avant de commencer à travailler

    1.INSTALLER LE SOLVEUR

    Le solveur est une application complémentaire. Il faut souvent l'installer.

     menu fichier
    dans le volet de gauche, <clic g> sur  
    dans le volet de gauche, <clic g> sur  
    dans le volet de droite, sélectionner   (en bas)
    <clic g> sur
    cocher
     pour valider

     le solveur s'installe et l'outil  s'affiche à la fin du ruban "données"

     Vérifier que le solveur est fonctionnel et s'il ne l'est pas, l'installer

    2.LANCER LE SOLVEUR

    Le solveur est inclus dans les outils d'analyse de données.

     onglet "données"
    groupe "analyse"
    (dernier bloc)
    <clic g> sur
    paramétrer le solveur
    <clic g> sur

     Le classeur "Pub" est un tableau de gestion d'entreprise. Dans ce tableau, le montant des investissements publicitaires détermine, par le biais d'une formule, les unités vendues. Le problème est donc de calculer le budget publicitaire qui permet de maximiser le résultat de l'entreprise (produit des ventes moins coût des ventes)

    3.PARAMÉTRER LE SOLVEUR

    Seuls une transposition adaptée du problème dans Excel et un paramétrage correct du solveur vont permettre d'obtenir des solutions exploitables.

    paramètres

    *      Peut être maximisé, minimisé ou fixe.

    *      Valeurs qui varient pour obtenir la solution.

    *      Définissent le cadre du problème

    *      Rétablit les paramètres par défaut

    *      Lance le solveur

    *      Voir "Contrôle de la recherche d'une solution"

      

    a)LA CELLULE CIBLE

    C'est la cellule que l'on veut optimiser. Cette cellule doit atteindre un maximum, un minimum ou une valeur donnée.

    cellule cible

    feuille de travail

    fenêtre "solveur"

    b)LES CELLULES VARIABLES

    La valeur de ces cellules va être modifiée par le solveur pour optimiser la cellule cible.

    cellules variables

    feuille de travail

    fenêtre "solveur"

    c)LES CONTRAINTES

    Une contrainte est l'affectation de valeurs ou de normes définies à une cellule ou une plage de cellules contenant une formule (et dont le résultat dépend des valeurs prises par une ou plusieurs cellules variables). Des contraintes peuvent être ajoutées à celles d'origine ; de même, elles peuvent être modifiées.

    contraintes

    feuille de travail

    fenêtre "solveur"

    options :

     Ajoute une contrainte supplémentaire

     Modifie la contrainte sélectionnée

     Supprime la contrainte sélectionnée

    4.RECHERCHER LA SOLUTION

    Une fois l'objectif défini, les cellules variables désignées et les contraintes établies, il ne reste plus qu'à lancer le solveur pour trouver la solution.

     onglet "données"
    groupe "analyse"
    (dernier bloc)
    <clic g> sur
    <clic g> dans la zone "objectif à définir de la fenêtre "solveur"
    <clic g> sur la cellule à définir dans la feuille de travail
    indiquer la nature de l'objectif dans la fenêtre "solveur" (max ,min…)
    <clic g> dans la zone "cellules variables" de la fenêtre "solveur"
    <faire glisser> sur les cellules à faire varier dans la feuille
    <clic g> sur  de la fenêtre "solveur"
    <clic g> sur la cellule à définir dans la feuille de travail
    choisir l'opérateur dans la fenêtre "contraintes"
    indiquer une valeur ou une cellule dans la fenêtre "contraintes"

    <clic g>  sur  de la fenêtre "solveur"

    a)LES RAPPORTS DE RÉSULTATS

    Les rapports résument les résultats de la recherche d'une solution.

     onglet "données"
    groupe "analyse"
    (dernier bloc)
    <clic g> sur
    paramétrer le solveur
    <clic g> sur
    sélectionner les rapports à créer
    <clic g> sur chacun des rapports pour les sélectionner
     activé
     pour valider
    les valeurs trouvées par le solveur peuvent être enregistrées sous forme de scénario

    b)CONTRÔLE DE LA RECHERCHE D'UNE SOLUTION

    Le bouton  de la fenêtre "solveur" gère le processus de recherche.