Practice Video: Introduction to Lookups: CHOOSE

Loading...

来自 Macquarie University 的课程

Excel Skills for Business: Intermediate II

318 个评分

Macquarie University

Excel Skills for Business: Intermediate II

318 个评分

课程 3（共 4 门，Specialization Excel Skills for Business）

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

从本节课中

Automating Lookups

How do find information from different parts of a workbook? This module introduces you to functions like CHOOSE, VLOOKUP, INDEX, MATCH and other dynamic lookups to find and display data from several sources.

Practice Video: Introduction to Lookups: CHOOSE3:45

Practice Video: Approximate Matches: Range VLOOKUP5:17

Practice Video: Exact Matches: Exact Match VLOOKUP6:40

Practice Video: Finding a Position: MATCH7:28

Practice Video: Dynamic Lookups: INDEX, MATCH7:06

Week 3 Wrap-Up1:28

与讲师见面

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

This week, Alex is going to be helping a network manufacturing company,

develop a template to speed up the process of developing customer quotes.

Work is already well underway.

Alex has already created a sheet to hold the current price list,

and he's created another sheet to contain the quote itself.

This contains a table, some data validation, and

some conditional formatting.

What the customer is looking for

is that the sales engineers can enter the minimum amount of information.

So just the item code, the location code and the quantity, and

have all the rest of the information automatically populated.

To do this, Alex is going to use some of Excel's LOOKUP functions.

And that's what we're going to be focusing on this week.

Now, the first piece of information we're concerned with is a description

of the installation location.

The sales engineer is going to simply enter a code 1 through 5.

And what we need to do is get one of these descriptions to populate automatically.

So number one is building core, number two is data center, etc.

Now, a function that can help us with this is the CHOOSE function.

CHOOSE works by retrieving a value from a list,

based on a given numeric value.

And that's exactly what we have here.

We have a number describing our location code, and

we're going to use that to retrieve one of these values based on position.

Let's watch it in action.

So we're going to click into C6 and type =CHO and then press Tab.

The first value the CHOOSE is asking for is an index number.

That is the numeric value that's going to provide the position within the list.

And in this case, it's our Location Code, so we're going to click on that.

Note the referencing, that's because we're working in a table.

Now, I type comma.

After that, we have to specify our list.

This can be up to 254 items, and you can either type it in or

click on values in your workbook.

We have all the values in the worksheet so

I'm going to click on building core, data center, fiber distribution,

main flows and finally, remote office buildings.

Make sure that your comma separate them,

but don't put an additional comma at the end.

Now, I also need to make this absolute, so

I'm going to just select all of them and press my F4 key.

The one thing to be aware of is you can't just select K6 to K10.

You have to individually list each list item.

All right, I can now close my brackets.

And when I click Enter, because it's a table,

this formula is going to automatically copy down.

And there we go, we now have the correct installation location description to

each of our different locations.

And a little bit of conditional formatting to divide the data and make it look nice.

So the CHOOSE function is a good choice.

When you need to do a LOOKUP from a very finite list,

that is not likely to change very often.

We can see in the moment though that we have much more complex problems we

need to solve.

And the CHOOSE function is not going to be the right LOOKUP function for these.

So in the next few videos,

we're going to look at some even more powerful and versatile LOOKUP functions.

[SOUND]

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

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

通过 App Store 下载

通过 Google Play 获取