style="font-size:15px;"> 2 Use the Set Objective box, the To group, and the By Changing Variable Cells box to set up Solver as I describe in the preceding section, “Optimizing a result with Solver.”3 Click Add.Excel displays the Add Constraint dialog box.
4 In the Cell Reference box, enter the address of the cell you want to constrain.You can type the address or select the cell on the worksheet.
5 In the drop-down list, select the operator you want to use.Most of the time, you use a comparison operator, such as equal to (=) or greater than (>). Use the int (integer) operator when you need a constraint, such as total employees, to be an integer value instead of a real number (that is, a number with a decimal component; you can't have 10.5 employees!). Use the bin (binary) operator when you have a constraint that must be either TRUE or FALSE (or 1 or 0).
6 If you chose a comparison operator in Step 5, in the Constraint box, enter the value by which you want to restrict the cell.Figure 2-14 shows an example of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.FIGURE 2-14: The completed Add Constraint dialog box.
7 To specify more constraints, click Add and repeat Steps 4 through 6, as needed.For the example, you add a constraint that asks for the Dog Polisher product profit (cell C12) to be 0.
8 Click OK.Excel returns to the Solver Parameters dialog box and displays your constraints in the Subject to the Constraints list box.
9 Click Solve.
10 In any Show Trial Solution dialog box that appears, click Continue to move things along.Figure 2-15 shows the example break-even solution with the constraints added. Note that not only is the Total Profit cell (B14) set to 0, but so are the two Product Profit cells (B12 And C12).
11 Select the Keep Solver Solution option.If you don’t want to accept the result, select the Restore Original Values option instead.
12 Click OK.
FIGURE 2-15: The Solver Results dialog box and the final solution to the break-even problem.
Save a Solver solution as a scenario
Whenever you have a spreadsheet model that uses a coherent set of input values — known as changing cells — you have what Excel calls a scenario. With Solver, these changing cells are its variable cells, so a Solver solution amounts to a kind of scenario. However, Solver does not give you an easy way to save and rerun a particular solution. To work around this problem, you can save a solution as a scenario that you can then later recall using Excel’s scenario manager feature.
Follow these steps to save a Solver solution as a scenario:
1 Choose Data ⇒ Solver.Excel opens the Solver Parameters dialog box.
2 Use the Set Objective box, the To group, the By Changing Variable Cells box, and the Subject to the Constraints list to set up Solver as I describe in the “Optimizing a result with Solver” section, previously in this chapter.
3 Click Solve.
4 Anytime the Show Trial Solution dialog box appears, choose Continue.When the optimization is complete, Excel displays the Solver Results dialog box.
5 Click Save Scenario.Excel displays the Save Scenario dialog box.
6 In the Scenario Name dialog box, type a name for the scenario and then click OK.Excel returns you to the Solver Results dialog box.
7 Select the Keep Solver Solution option.If you don’t want to accept the result, select the Restore Original Values option instead.
8 Click OK.
Конец ознакомительного фрагмента.
Текст предоставлен ООО «ЛитРес».
Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.
Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.
