Excel 2010 basics of planning and solving

Today, I would like to share with you the planning and solving function in excel2010. It is hard for you to understand what this function is, next, let's look at a practical problem to illustrate the problems that can be solved by planning and solving.

We have two kinds of wind turbines (it doesn't matter what it is). Each of the two products requires three hours and seven hours of work, with a power consumption of 4 KW and, and 9 tons and 5 tons of raw materials. The company can provide 300 hours of work, 250 KW of power consumption, and 420 tons of raw materials. The profits of the two products are RMB 2 million and RMB 2.1 million respectively (the above data is fictitious ). How can we arrange the production of the two products to maximize the profits ????

 

Planning and solving is used to solve such problems. In fact, I really want to apply questions, set unknown numbers, and then write functions. The first step in planning and solving is to matalize and model the problems described. Next, follow the problem-solving format to do the above application questions.

The output of FAN 1 is X. The output of FAN 2 is Y. the maximum profit is Pmax.

X, Y> = 0

3X + 7y <= 300

4x + 5y <= 250

9x + 5y <= 420

Pmax = 200x + 200y

Next we will talk about these function expressions expressed in Excel cells.

650) This. width = 650; "style =" width: 646px; Height: 329px; "Title =" 150.jpg" alt = "wkiol1qjcivrhdewaagb0pkvn7a198.jpg" src = "http://s3.51cto.com/wyfs02/M00/48/8C/wKioL1QJcIvRhdewAAGB0pKVn7A198.jpg" width = "497" Height = "250"/>

 

Before solving the problem, we set the random output to 15 and 30 (remember the random output)

650) This. width = 650; "style =" width: 635px; Height: 254px; "Title =" 156.jpg" alt = "wKiom1QJcR-h60QmAACeJy-OGqk897.jpg" src = "http://s3.51cto.com/wyfs02/M02/48/8A/wKiom1QJcR-h60QmAACeJy-OGqk897.jpg" width = "465" Height = "206"/>

We can see that the current total profit of 9300 is based on the random 15*200 + 30*210, which is the value before the planning and solution.

Then click data --- solver, which is not displayed on the toolbar by default, you can follow the steps below to add and click File ---- options --- add-ins ---- solver add-in ------- click the go below and select Solver

Such as 650) This. width = 650; "Title =" 160.jpg" alt = "wkiol1qjdvhsgbq8aahjkcjt04o039.jpg" src = "http://s3.51cto.com/wyfs02/M02/48/8D/wKioL1QJdvHSgBq8AAHJKcjt04o039.jpg" width = "679" Height = "631"/>

 

 

Then fill in the conditions

650) This. width = 650; "style =" width: 741px; Height: 495px; "Title =" 158.jpg" alt = "wKioL1QJclOAUj2hAAI6hgkY-tI355.jpg" src = "http://s3.51cto.com/wyfs02/M02/48/8C/wKioL1QJclOAUj2hAAI6hgkY-tI355.jpg" width = "989" Height = "673"/>

First set objective: the following is your goal, that is, the maximum profit to be achieved.

By changing variable cell is the variable cell, that is, the change in output when the maximum profit is reached.

Subject to the constraints here we need to add constraints, such as B7 and C7 are both greater than or equal to 0. After all, the number of production cannot be negative.

Then, the demand must be less than its upper limit.

Finally, we will obtain the maximum total profit and the production quantity of the corresponding FAN 1 and FAN 2. For example

650) This. width = 650; "Title =" 159.jpg" alt = "wkiol1qjc8pzji98aacwucmair4955.jpg" src = "http://s3.51cto.com/wyfs02/M02/48/8D/wKioL1QJc8PzJI98AACwucmAiR4955.jpg" width = "462" Height = "268"/>

 

I will share it here today. You are welcome to correct the errors in this article. There is no high or low level of learning and communication, but there is only a specialization in the skill industry.

Coming soon

 

 

 

This article from the "GUO Yong technology blog" blog, please be sure to keep this source http://guoyong.blog.51cto.com/408495/1549400

Excel 2010 basics of planning and solving