Feature Summary

The Solver feature is used to solve multivariable equations or optimization problems under constraints, supporting maximum, minimum, and target value solving to help users tackle complex mathematical programming and data analysis problems.

Feature Description

Feature Dimension Description
Membership Restriction No membership restriction
Version Restriction No version restriction

Operation Steps

Open Solver Parameters Dialog

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 appears

image

Set Target Cell

Feature Entry

"Data" tab → "What-If Analysis" dropdown menu → "Solver" button → "Set Objective"

  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 appears
  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

image

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 appears
  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

image

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 appears
  5. Click the "Add" button under "Subject to the Constraints" to open the Add Constraint dialog
  6. Select the constraint cell
  7. Set the constraint relationship (>=, =, <=, =, integer, and so on.)
  8. Enter the constraint value and click OK
  9. Repeat the operation to add all constraints

image

Solve and View Results

Feature Entry

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

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

image

FAQ

Question 1: What should I do if the solution result is inaccurate?

  • Solution: Ensure the original data is accurate beforehand, 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 Method".

Notes

  • Before solving, 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