Practice Video: Replace blanks with repeating values

Loading...

來自 Macquarie University 的課程

Excel Skills for Business: Advanced

278 個評分

Macquarie University

Excel Skills for Business: Advanced

278 個評分

課程 4（共 4 門，Specialization Excel Skills for Business）

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.

從本節課中

Data Cleaning and Preparation

Been handed a messy spreadsheet? This module focuses on data cleaning and preparation. You will learn how to use dynamic and automated fixes, clean messy data, automate these processes through functions, and ensure data remains clean dynamically. We will use functions to fix dates, replaces blanks and remove unwanted characters from data – all to help us build an efficient solution that lasts.

Practice Video: Replace blanks with repeating values5:41

Practice Video: Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)6:21

Practice Video: Remove Unwanted Spaces (TRIM, CLEAN)4:42

Practice Video: Diagnostic Tools (ISNUMBER, LEN, CODE)7:21

Practice Video: Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)6:12

與講師見面

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

In business, we often have to work with data of which we have little or

no control of the source.

This can introduce problems like unwanted characters or unworkable formatting.

And this is exactly the problem that Alex has this week.

He's helping a client who organizes events.

They get their customer database sent to them in a report,

which they then paste into a spreadsheet.

They need to create certain pivot tables using this data.

But because of the country name only being included once, they're unable to do this.

The first problem Alex is going to help them solve is the issue with replacing

the blanks with the repeating country names.

There are a couple of approaches we can take.

If it's an occasional fix, there's quite a neat solution you can use

using the Go-To Special, and that's the first approach we're going to look at.

We're going to start by clicking in the first blank cell and typing equals.

And then clicking on the country above to get it to repeat.

We're then going to copy that formula, clicking back on that cell,

I'm going to press Ctrl and C.

We are now going to go to our Find and Select on our Home ribbon and

we are going to come down to Go To Special and we are going to select blanks.

What this will do, is find the blanks in that area and select them.

We are going to click on OK and there we go.

Now with all those cells selected

we are simply going to paste the formula we created earlier.

So Ctrl+ V and

that has immediately copied the country above into all the blank cells.

The last step is to replace the formulas with the values and

to do this we select the data.

I'm going to start by selecting from Armenia,

Ctrl +Shift+Down Arrow, and I'm going to copy Ctrl + C.

And I'm going to choose to Paste Values.

What this will do, is replace the formulas with the results.

We have now removed all the blanks, quite a neat little solution.

And if you had to do this on a semi-regular basis, you could of course

record yourself doing it in a macro and simply use your macro for future fixes.

The only problem with what we've done here is we've lost our original data.

And if you're going to have to repeat this process on a weekly basis

this is probably not the best method.

Where you have data that needs to be regularly repaired or corrected,

it is probably a better idea to pull the original data into one sheet.

And then have a separate sheet where you use functions and

formulas to correct that data.

This gives you the benefit of preserving your original data, but

it also means that you don't need to run the fixes every time

because the fixes are sitting in another sheet.

That's the approach we're going to look at next.

Let's quickly undo and duplicate the sheet by holding Ctrl+Down and

dragging the worksheet tab.

And we've done that so we can get the headings and

the formatting all done for us.

And now what we're going to do is pull through the values from the data sheet

using calculations.

A nice simple calculation, click on A2 type equals click into Sheet1,

click on Argentina, and click Enter.

And because of the blanks, we are going to have to drag it.

So we're going to drag that whole way down to the bottom and

then we're going to drag it across.

What we've done there is replaced our pasted values with formulas

that actually pull the values through from the original data sheet.

Now, we're going to look at how we can improve on these simple calculations and

repair the data in the process.

The first one we're going to look at it is how to get the country repeating again.

And for this, we're going to use two simple functions.

The IF function which we've seen before, and the IS_BLANK function.

Which simply does a check for if a cell is blank or not, and returns true or false.

I'm going to double click on A2.

And we're going to click after the equal sign and we're going to pop in an IF, and

we want to check if the value in sheet A2 is blank.

We're going to type ISBL and tab to select ISBLANK and

I am just going to copy this sheet 1 A2.

Because we're going to need this in a moment and

then I'm going to close my brackets.

If it is a blank, it is going to return in a true,

in which case I wanted it to use whatever value is in the cell above.

So I'm going to click on the one above.

If it isn't blank, so it has the correct value,

then we're going to use the value in Sheet1 A2.

Close our bracket for our IF and Enter.

Double click to copy that formula down, and now we've got all our

repeating country values and we're ready to start pivoting this data.

That's one problem solved and there are still other issues.

We're going to deal with that in the next video so keep watching.

探索我們的目錄

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

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

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

通過 App Store 下載

通過 Google Play 獲取