2.1 Excel Range Names

Loading...

来自 University of Houston System 的课程

Math behind Moneyball

39 个评分

University of Houston System

Math behind Moneyball

39 个评分

Learn how probability, math, and statistics can be used to help baseball, football and basketball teams improve, player and lineup selection as well as in game strategy.

从本节课中

Module 2

You will concentrate on learning important Excel tools including Range Names, Tables, Conditional Formatting, PivotTables, and the family of COUNTIFS, SUMIFS, and AVERAGEIFS functions. You will concentrate on learning important Excel tools including Range Names, Tables, Conditional Formatting, PivotTables, and the family of COUNTIFS, SUMIFS, and AVERAGEIFS functions.

2.1 Excel Range Names5:02

与讲师见面

Professor Wayne Winston
Visiting Professor
Bauer College of Business

In this video, we're going to learn about how to use range names in Excel and

we'll see the use of this throughout the class.

Okay so, I've downloaded from basketballreference.com,

a great site, the 2014-2015 Warrior stats.

By the time you hear this video, they may have won the NBA Championship,

they're up 3-2 on Memphis as I speak right here.

And what we're going to cover here is covered in chapter one of the data

analysis business modeling book, but you should be able to follow it quite simply.

Okay, so you can name cells in your spreadsheet.

Why is that nice?

Because you can use them in your formulas.

Okay it also makes it easier to basically do calculations, as we'll see in a minute.

because once you named a cell or

range of cells using the F3 key, you can paste a range name in a formula.

Okay, so let's suppose you want to work with Stephen Curry's three point

field goals made and Andre Iguodala's three point field goals made.

So you can name these cells or

range of cells using the name box which is right here.

So you put your cursor here, you click on the name box,

you can type in Curry three point and hit enter.

Go anywhere else on the spreadsheet, click on that name box,

it goes right to that cell.

Now we can say Andre Iguodala three point to go to that cell,

we could type, AI3point, hit enter.

If I go anywhere else, I'll see AI3point, it sends you to that cell.

Now if I want to add those cells, I can simply point to them, and

it says, Curry three point plus Andre Iguodala three point and

that's pretty nice, shows up in English.

Okay, well you can name a whole column of cells if you want a range of cells

using create from selection.

So, I could name this column age, this column games, this column games started.

So I'll select all this range here, you select the range you want to name and

then you go to formulas, create from selection.

By the way, in the name manager,

you can see the names we've created and you can edit those if you want.

And there's a lot more to range names and

I urge you to read chapter one of my book if you want to learn a lot more with that.

What we'll do in this video will get us by.

So I'm going to select this range here, Ctrl+Shift+Right arrow,

Ctrl+Shift+Down arrow.

Go Create From Selection, and it does names in top row so

it'll name this column age, this column G for games, etc.

Okay, now suppose I want to add up

the three point field goals, so

i can say sum and I type three point.

All right, let's point to this column.

What this will do.

Okay, it put an underscore in front of 3P, why?

Because 3P is sort of like a cell address that exists in Excel.

Okay, so if I type there I could say some three feed and

I get 883 three pointers for the Warriors.

Now look at the names that have been created under the name manager.

See the 3P the 3PA, it put an underscore and

if you have spaces it'll put an underscore between them.

Suppose someone had an offensive rebounds.

Okay, so I could say sum, and I could start typing in offensive rebounds.

See, it shows it in the auto-complete, and

I can just fill it in there or I could use F3.

So if I want to add up free throws, I could say, equals sum,

hit F3, and see all my range names show up there.

So I could say sum, let's say free throws attempted, and it pops right in there.

See with that F3 I can paste, arranging in the formula.

The beauty of this is that I could be anywhere in the the workbook and

if I hit that F3 key, I could point to this data.

I could be in the 20th worksheet of the workbook and still make that work out.

So, you'll see how the range names make our life easier,

particularly with that F3 key when we do some calculations.

Once we name a column of data, or we don't need to basically go back to that data and

use the F3 key to paste that in so that'll help us a lot throughout the course.

探索我们的目录

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

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

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

通过 App Store 下载

通过 Google Play 获取