Feature Summary

The Solver feature is used to solve multivariable equations or optimization problems under constraints. It supports solving for maximum, minimum, and target values, helping users tackle complex mathematical programming and data analysis problems.

Feature Description

Feature Dimension Description
Membership Restrictions No membership restrictions
Version Restrictions No version restrictions

Operation Steps

Open the Solver Parameters Dialog Box

Feature Entry
"Data" tab → "What-If Analysis" dropdown menu → "Solver" button

  1. Click the Data tab
  2. Click the What-If Analysis dropdown menu
  3. Select the "Solver" button in the pop-up menu
  4. The "Solver Parameters" dialog box will appear

截图

Set Target Cell

Feature Entry
"Data" tab → "What-If Analysis" dropdown menu → "Solver" button → "Set Target"

  1. Click the Data tab
  2. Click the What-If Analysis dropdown menu
  3. Select the Solver button in the pop-up menu
  4. The Solver Parameters dialog box will appear
  5. Choose according to your needs:
  • "Max": Solve for the maximum value of the target cell
  • "Min": Solve for the minimum value of the target cell
  • "Value Of": Fixed result, enter the corresponding value

截图

Set Variable Cells

Feature Entry
"Data" tab → "What-If Analysis" dropdown menu → "Solver" button → "By Changing Variable Cells"

  1. Click the Data tab
  2. Click the What-If Analysis dropdown menu
  3. Select the Solver button in the pop-up menu
  4. The Solver Parameters dialog box will appear
  5. Click the "By Changing Variable Cells" input box
  6. Select the variable cells that can be adjusted
  7. For multiple non-contiguous cells, hold down Ctrl and click to select them one by one

截图

Add Constraints

Feature Entry
"Data" tab → "What-If Analysis" dropdown menu → "Solver" button → Click the "Add" button under Subject to the Constraints

  1. Click the Data tab
  2. Click the What-If Analysis dropdown menu
  3. Select the Solver button in the pop-up menu
  4. The Solver Parameters dialog box will appear
  5. Click the "Add" button under Subject to the Constraints, and the Add Constraint dialog box will appear
  6. Select the constraint cell
  7. Set the constraint relationship (>=, <=, =, integer, etc.)
  8. Enter the constraint value and click OK
  9. Repeat the operation to add all constraints

截图

Select Solving Method

Feature Entry
"Data" tab → "What-If Analysis" dropdown menu → "Solver" button → "Select a Solving Method" dropdown menu

  1. Click the Data tab
  2. Click the What-If Analysis dropdown menu
  3. Select the Solver button in the pop-up menu
  4. The Solver Parameters dialog box will appear
  5. Find the "Select a Solving Method" dropdown menu
  6. Choose according to the problem type:
  • Linear problems: Select "Simplex LP" or "Simple Iteration"
  • Nonlinear problems: Select "GRG Nonlinear" or "Nonlinear Interior Point"

截图

Solve and View Results

Feature Entry
"Data" tab → "What-If Analysis" dropdown menu → "Solver" button → "Solve" button

  1. After all parameters are set, click "Solve"
  2. After the calculation is complete, a results dialog box will appear
  3. Select Keep Solver Solution and click OK
  4. The final optimal result will be displayed in the spreadsheet
  5. You can also choose to output the result report to a new worksheet

截图

Frequently Asked Questions### Question 1: What should I do if the solution result is inaccurate?

  • Solution: Ensure the original data is accurate in advance, as erroneous data will directly cause the solution result to deviate from expectations. Complex optimization problems can be broken down into multiple smaller problems and solved step by step.

Question 2: How to choose a solving method?

  • Solution: For linear problems, choose "Simplex LP" or "Simple Iteration"; for nonlinear problems, choose "GRG Nonlinear" or "Nonlinear Interior Point".

Notes

  • Before using the solver, prepare the objective function, variable cells, and constraints.
  • Different solving methods should be selected for linear and nonlinear problems.
  • Complex optimization problems can be broken down into multiple smaller problems and solved step by step.
  • The solution result can be output to a new worksheet for analysis.