Quand vous travaillez avec un tableur, vous créez des formules de calcul qui exploitent le contenu de plusieurs cellules. Il suffit ensuite de modifier le contenu de ces cellules pour observer la conséquence de ces changements. Mais il est parfois nécessaire de prendre le problème à l’envers. En d’autres termes, de se demander : “ Quel doit être le contenu de la cellule xxx pour que le résultat de ma formule atteigne la valeur yyy ? ”.C’est le rôle du solveur d’Excel. Avec cet outil, vous désignez une formule et vous indiquez quelle valeur elle doit atteindre. Vous choisissez ensuite les cellules qui peuvent varier, vous ajoutez, le cas échéant, des contraintes, par exemple “ le contenu de A1 doit être inférieur à 50 ”. Excel cherche alors les solutions de cette équation (ou inéquation) et les affiche si elles existent.
01. Notre exemple
Pour illustrer le fonctionnement du solveur, nous prendrons l’exemple illustré ci-dessous : l’entreprise Loca Vélos loue des bicyclettes et cherche à calculer son chiffre d’affaires pour les trois premiers mois de l’année. Pour chaque mois, le tableau recense le nombre d’heures de location et le tarif horaire, ces deux données variant chaque mois. Dans le tableau, seules les cellules sur fond orange sont modifiables, les autres résultant de formules.
02. Utilisez la fonction valeur cible
Dans un premier exemple, nous ne faisons varier qu’une seule cellule. Par exemple, nous voulons que le total général des locations (la cellule E4 dans notre exemple) atteigne 24 000 euros. Pour cela, nous nous demandons à combien nous devons fixer le tarif horaire du mois de mars (cellule D3), toutes les autres cellules conservant leur valeur d’origine. Dans ce cas, une version “ légère ” du solveur fait l’affaire : la Valeur Cible.À la différence du solveur, cette fonction ne nécessite aucune installation comme celle décrite dans le paragraphe suivant. Activez le menu Données, cliquez sur Analyse de scénarios et choisissez Valeur cible. Dans le champ Cellule à définir, tapez E4 (ce doit être l’adresse d’une cellule contenant une formule). Dans le champ Valeur à atteindre, tapez 24000. Enfin, dans le champ Cellule à modifier, tapez D3 et cliquez sur OK (D3 sera sans doute remplacé par $D$3, ignorez ce changement). La fonction Valeur Cible vous informe alors qu’elle a trouvé une solution. Cliquez sur OK. La valeur cherchée (3,8383… arrondie à 3,84 euros) s’affiche en D3.
03. Installez le complément solveur
Pour permettre à plusieurs cellules de varier simultanément, il faut utiliser le Solveur. Ce dernier n’est pas actif par défaut à l’installation d’Excel. Pour l’installer, cliquez sur le bouton Office et choisissez Options Excel. Activez l’onglet Compléments. Sélectionnez Complément Solveur, puis cliquez sur Atteindre. Cochez l’option Complément Solveur puis cliquez sur OK.
Ajoutez une condition
Nous imposons maintenant une contrainte supplémentaire : nous voulons toujours obtenir un total de 24 000 euros, mais il faut que le nombre d’heures de location, en mars, (cellule D2) ne dépasse pas 3 000 pour permettre la maintenance des vélos. Nous cherchons combien d’heures de location (cellules B2 à D2) nous devons totaliser pour atteindre cet objectif. Pour lancer le solveur, activez le menu Données et cliquez sur Solveur.Indiquez la valeur cible à définir (ici E4), cochez Valeur et saisissez 24000 dans le champ voisin. Dans le champ Cellules variables, tapez B2:D2. Pour insérer une contrainte, cliquez sur Ajouter. Modifiez les listes qui s’affichent jusqu’à obtenir D2 <= 3000 et cliquez sur Ajouter. Cliquez sur Résoudre. Si le solveur vous affiche qu’il a trouvé une solution, cochez Garder la solution du solveur et cliquez sur OK. Si, au contraire, aucune solution n’existe, compte tenu de vos contraintes, Excel vous en avertit par un message. Cochez alors Rétablir les valeurs d’origine et cliquez sur OK.
🔴 Pour ne manquer aucune actualité de 01net, suivez-nous sur Google Actualités et WhatsApp.
