Nesting

Loading...

来自 PwC 的课程

Problem Solving with Excel

3053 个评分

PwC

Problem Solving with Excel

3053 个评分

课程 2（共 5 门，Specialization Data Analysis and Presentation Skills: the PwC Approach）

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

从本节课中

vLookups and Data Cleansing

In this module you will learn about VLookup, value cleansing and text functions. We will also introduce you to PwC's perspective on the value in cleansing data and using the appropriate functions. Finally, we will provide you an opportunity to perform a problem solving exercise using VLookup, value cleansing and text function. Note: We recommend viewing videos in full-screen mode by clicking the double arrow in the lower right hand corner of your screen.

Welcome to Week 21:53

Use VLOOKUP, data validation and multiple VLOOKUPs together2:24

与讲师见面

Alex Mannella
Alumni / Former Principal

[MUSIC]

We are now going to experiment with one of the issues you'll encounter when

using a VLOOKUP.

Sometimes, the data you are trying to look up does not exist within your data set.

And therefore the function returns an error message.

We have an example of this in the table above,

which is looking up information about specific distributors from the sales data,

some of which exist in our data set, and others that don't.

You'll see that there is no distributor with an ID of 3000 or 4000.

So, Excel is returning an error message.

We see this quite frequently in business.

Since a product may not have been sold during a specific period or

the specific variable set we are working with does not apply to all the situations.

It also happens when data cleansing has not been performed correctly.

The error message does not only look poor.

It is also not helpful in explaining what the issue is and it could cause further

downstream issues when you're trying to run computations on the dataset.

For example, if I do =sum on a range containing even a single error message,

the sum function will return an error.

However, Excel has given us a tool to work around this.

It's called the IFERROR function.

The IFERROR function tells Excel what value you would like to return

if an error occurs.

So let's try rebuilding the table above using the IFERROR function.

We're going to start with cell C107 and

type the formula we want to start working with.

Since Excel works with the order of operations PEMDAS,

the IFERROR should be enacted last.

I'm going to start with my IFERROR statement

as the outermost argument to follow this logic.

So let's type =IFERROR.

As you can see, the syntax is comprised of two parts.

The first part being what argument or

series or arguments does the statement apply to?

And what do you want Excel to do if it encounters an error at that statement?

The value we care about here is the output from the VLOOKUP.

Let's start by typing the view lookup formula we have been working with.

The view lookup value is the distributor ID in B107.

We know that we want to anchor B107 so that we can use it again for other cells.

The table array if we remember from earlier is the sales data,

which is a named range.

Open the Name Manager using F3, and double-click on this cell's data.

Now, remember from previous exercises that the name is in column 2, and

we don't want an approximate match,

so we are going to type false.

When I close the parenthesis,

you'll notice that I'm no longer working in the VLOOKUP statement.

I'm back to working in the IFERROR statement.

When I hit comma, I shift from the value field to the value that Excel will

display in case my view lookup formula results in an error.

I would like it to say data not available.

Know that since this is text,

I have to add quotes to each side of my text string.

I'm going to insert a quotation mark and

then type, Data Not Available, close the quotes.

Close the second formula that I'm working with and click Enter.

The name comes up because there's a match.

However, we know there is nothing for the second distributor.

So let's see what happens when we paste it down.

Distributor ID 4000 comes back with Data Not Available as we would expect.

Now since we properly locked this information,

we can use this formula to fill in revenue as well.

Let's work with the revenue and copy and paste a formula over.

It's coming back with the name because we still need to update the column number.

We remember from earlier that the revenue can be found in column 10,

so I'm going to change my 2 to 10.

For the error statement here, I'm going to change the error statement from

Data Not Available to the number 0 as we are working with a numerical output.

To accomplish this, I will exchange the error statement to a 0 and

remove the quotes as we are no longer dealing with a text input.

You will notice that we have revenue values for everywhere there's a match and

where the data is not available, the output is 0.

That now makes it possible to run math off our new revenue column.

For example, we can even sum the revenue column and

find that the total revenue for these distributors is $184.86.

Now it's your turn to practice problem 3b on your own.

Work up the average unit price for the product, and then use an average formula

to calculate the average unit price across all the distributors found in column B.

[MUSIC]

探索我们的目录

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

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

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

通过 App Store 下载

通过 Google Play 获取