Preview of Part 2 of the course

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

710 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

710 notes

Cours 1 sur 3 dans 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.

À partir de la leçon

(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

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

All right, I just wanted to, kind of,

give you a preview to part two of the course.

In part two, we're going to work more with arrays.

So, in part one,

you worked with iterating through vectors.

And the first thing we're going to do in part two of

the course is to iterate through arrays.

So, we've got multiple columns and multiple arrays.

For example, we can find fives in this array.

It says, a five was found in a row one, in column three.

It also finds another one in row two,

column one, and a third one in row four, column two.

So, while this is not tremendously useful,

you could apply this to a lot of different scenarios.

So you're trying to sort through big arrays to find different things.

Next, we're going to work with array functions.

And in part one of the course,

we did things where we would maybe,

sort this vector or shift it, up or down.

But, now what we're going to do in part two,

we're going to create array functions in which we can

create our own user-defined array functions,

where we have a function whose input is a an array.

And then we can do Control Shift Enter and we can sort that low to high.

Instead of just sorting the vector here,

we can put it over here in a new range of cells,

and this is the sorted vector.

We are also going to be working with strings.

So, a lot of folks will work with strings.

For example, here I can run a search,

and we're going to search for just a simple string like ana,

and the range to search,

I can put in this range and it searches through there.

It says, your string was found in row three and column two.

Next, you guys are going to learn how to do user forms.

So this is a generic user form.

It's similar to one of the assignments in the first part of the course.

You can put in information here,

so this is a really good way to interface with the user in a professional way.

You can enter things like the different dimensions for this tank.

You can put in the density,

and maybe we have five meters in here and we will calculate the mass.

So, it's really easy for the user to input

the different elements of the problem and run this to find the solution.

So that's a basic user form.

Another example of a user form might be the following.

This relates to assignment five on the first part,

so we can put the different elements of our problem in there,

and it will then calculate the maximum profit,

and the speed at which you need to drive to maximize your profit.

Finally, in the very end of part two,

you're going to learn how to iterate through different files and bring in information.

So, I've got sort of a grade manager example here,

where I've got a bunch of students and we have all

of their assignments and assessments in the course,

and I have five different sections where the TAs

for those sections will provide me with individual spreadsheets.

So, as an example, I have just a spreadsheet here

with the grades from just one section of the course,

and I need to then import all of this information,

and it has to correspond correctly to the right student

with the import that information into the,

kind of, the overall grade spreadsheet.

And I've got scores from five different sections.

So, I can run this,

it's a user form, I can update the grades.

It asks me to choose the files that have the grades,

so these are the five section spreadsheets.

It's going to then open up each one of those and it's going

to import the data into the main spreadsheet.

And so, it imports all the grades,

and it says, grades updated.

You can also implement a search tool.

So, we can search by last name,

and I can then see what that student got on that item.

I can also change the grades,

so I can search through using the last name,

and I can change that students grade on a certain assignment.

So, this is how you can very easily,

and very professionally, work with large sets of data and manage them quite easily.

So, in part two of the course,

you're going to learn all of the elements required to put together something like this.

So, I hope all of you will join me in the second part of the course.

Thanks again for joining me for part one.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play