Live solution of the Golden Search technique for solving an optimization problem

Loading...

來自 University of Colorado Boulder 的課程

Excel/VBA for Creative Problem Solving, Part 1

354 個評分

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

354 個評分

課程 1（共 3 門，Specialization Excel/VBA for Creative Problem Solving）

"Excel/VBA for Creative Problem Solving, Part 1" is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. In this course (Part 1), you will: 1) create macros to automate procedures in Excel; 2) define your own user-defined functions; 3) create basic subroutines to interface with the user; 4) learn the basic programming structures in VBA; and 5) automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems. New to computer programming? The extremely intuitive and visual nature of VBA lends itself nicely to teaching and learning - what a fun way to learn to code! No prior knowledge in programming nor advanced math skills are necessary yet seasoned programmers will pick up new and creative spreadsheet problem solving strategies. After you have learned the basics of VBA, each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

從本節課中

(OPTIONAL) Numerical techniques and live solution strategies

Week 5 is OPTIONAL! Those who complete the Week 5 assessments (Quiz 5 and Assignment 5) will earn an Honors designation on their Course Certificate (see the first reading "WEEK 5 IS OPTIONAL" below). Week 5 delves into some important numerical techniques that can be used in Excel without the use of VBA. You will also explore ways in which the targeting tools in Excel (Goal Seek and Solver tools) can be automated in VBA. Finally, you'll explore some exciting "live solution" methods that have distinct advantages, especially for case studies where you have multiple scenarios that must be solved. Week 5 represents the most math-heavy module of either part of "Excel/VBA for Creative Problem Solving" but will provide you with some extremely useful tools that you can implement in solving a variety of real world problems. Even if you choose to skip/forego Week 5, you can always use this material as reference in the future!

Week 5 preview1:47

How to use the Goal Seek and Solver tools to solve targeting problems7:55

How to solve optimization problems using the Solver tool9:11

Fuel tank example and limitations of the Goal Seek and Solver Tools7:33

Automating the Goal Seek and Solver Tools8:29

Circular Calculations in Excel7:39

Implementing the bisection method in Excel8:59

Implementing a live solution of the bisection method to solve a case study8:55

Using the Golden Section search technique for optimization problems in Excel7:56

Live solution of the Golden Search technique for solving an optimization problem7:25

Solving a case study for friction factor using a circular calculation6:18

Implementing targeting and optimization algorithms in VBA subroutines8:45

Bisection method in a VBA function5:14

Preview of Part 2 of the course4:42

與講師見面

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

So you're probably guessing that we are going to implement

the golden search technique into Excel to make a life solution.

And that's exactly what we're going to do in the screencast.

So I've set this up. This is a modification of

a spreadsheet that I showed in one of the earlier screencast.

And what I've got,

I've changed the problem statement around a little bit.

Instead of having 2640 ft for a half mile of the swamp in the forest and exactly one mile

for the width here.

Instead, what I'm doing is I'm turning that into S for the swamp.

We can change that distance F for the forest

and D for this total distance here to the getaway car.

Now the equation for that is shown here,

and I have put in cells up here in a name,

the cells appear in the name box S,

F and D. And I've put in a function here for the time.

So this is just this formula here that's going to give us

the time depending upon these different parameters.

But that's just the time for the given theta here that I guess there.

And you see that when I change theta,

I change the time.

So just like we did before there's going to be some sort of

optimal angle that the escapee can

choose and that's going to determine then through

this equation the time that it takes to get to the getaway car.

So as I showed in a previous screencast,

we can solve this scenario using the solver tool.

So we're trying to set the time to a minimum by changing the angle here.

And when we do that, actually I don't think this is quite right.

C2 we want that to be the time to a minimum by changing the angle.

And I press solve it,

turns through and finds a solution.

So with these particular parameters,

the convict should choose an angle of 65 degrees to get through the swamp and the forest.

And again the swamp he can travel two feet per second,

in the forest he can travel four feet per

second and that's where we get the two and the four from.

But we want to make this so it's a live solution.

In other words if I change s to something like 3000 then it'll automatically change.

You see that it automatically changes

the time but it doesn't automatically adjust the angle.

In order for us to adjust the angle such that we minimize

time we have to go back up here into the solver tool and we have to click solve.

So this is not a live solution and to make it a live solution,

we're going to implement the golden search technique.

So I've got this set up to put in our golden section search algorithm.

I've got my golden ratio up here which is

the square root of five minus one quantity divided by two.

I've named that GR up here in the name box.

Now when you're setting this up you always have to

sort of realize that these values here,

the a b d x1 x2 correspond to the independent variable that you can change.

So it can independently change that has an effect on the output.

So the thing that you can change the thing that you are

adjusting is theta which is the degrees.

So that's the independent variable in this function here.

And then the output is the time.

So, the minimum theta,

theta in this example would usually be I guess between

the smallest you could ever have would be

zero and the largest you would ever have would be 90.

And so I'm going to put this in in terms of degrees.

And it actually turns out that the sign of zero radians or degrees is zero.

So in here we would be dividing by zero.

So I'm just going to make this I'm just going to use one degree.

D is equal to the golden ratio times b minus a. x1 is equal to a plus

d. x2 is equal to b minus d. f(x1) is just taking x1.

Remember x1 x2 ab these are thetas.

So I'm plugging that,

I'm plugging x1 into this function above making sure to convert

the degrees because these are all in degrees to radians using

the radians function and we get f(x1).

And I can just copy this control C and paste it there.

We have our conditional statement to set the new a in the next iteration.

So if f(x1) is less than f(x2),

we're going to be cutting out a portion on the left part,

and in that case if that's true then the new a is going to be x2.

Otherwise, it's just going to be the old a.

And now I can put my conditional statement in here for the next b.

So, if f(x1) is less than f(x2),

again we're removing part on

the left side and in that case the new b is just going to be the old b.

Otherwise, it's going to be x1.

Now, I can just take this part.

This is all just relative formulas,

and I can take the entire row here and I can double click.

And you notice that as we get down towards the tenth iteration,

the average of x1 and x2 is a good approximation of the result.

I think we should probably go.

The precision here is not what we would want.

So I'm just going to go another 10 iterations which is really easy to do in Excel.

I'm going to double click on this.

And you see after total 20 iterations we're getting down to

something that's quite reasonable, 65.51.

Now, what I'm going to do up here,

I'm going to go ahead and erase these because we're not using those anymore.

And I'm going to put a pointer formula.

The result here is going to be the average of x1 and x2 at the last iteration.

So I'm just kind of making a pointer formula and the time is going to be

the average of f(x1) and f(x2).

Now these are in seconds.

So I'm going to do the average of that but then I'm going to divide

by 60 to convert 2 minutes.

And that's exactly what we got using the solver tool.

So now for the really exciting part,

remember that the solver tool is not a life solution.

If you change the different parameters using the solver tool,

it does not automatically update the theta.

It'll update the time because that's just a formula here.

But then you have to rerun the solver in order to minimize theta.

What we can do using this technique is I can,

it's a live solution so I can automatically change these values in the spreadsheet.

When I press enter, it goes through 20 rounds of

the golden search technique and then it's going to find

the angle theta that minimizes the time.

So when I press enter,

it automatically goes through that.

And I can change other things,

I can change this to something like 3500

and it automatically calculates the new solution.

So it's a live solution of an optimization problem

similar to what we did using the live solution of the bisection method.

And this does not use any sort of visual basic.

Hope you guys enjoyed. Thanks for watching.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2018 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取