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.

Loading...

来自 University of Houston System 的课程

Math behind Moneyball

36 评分

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.

- Professor Wayne WinstonVisiting Professor

Bauer College of Business

Okay in this video, we

are going to discuss pivot tables, and the next few videos also, which

are probably the single most used tool to summarize any kind of data in Excel.

Now a detailed discussion of pivot tables is in Chapter 43 of the Data Analysis and

Business Modeling book,

the longest chapter in the book, but we'll see it through several examples,

I think the main things that you need to know about pivot tables, the handle,

let's say 80 or 90% of the problems that might come up in sports analytics.

So look at the NBA data which I cleaned up.

I deleted multiple rows here.

In other words, the title row appears multiple times.

I threw that out.

Now just let me question you on one thing here.

Some players that were traded like Tayshaun Prince.

They're listed for each team they're on.

Or Brandon Davies here.

He was on Brooklyn and Philadelphia.

So some of the calculations I do here like how many players play each position?

If a guy was traded I'm going to count him twice but

I'm just not going to worry about that.

Okay, solve two problems in this video and then we'll go onto some others.

Let's say, how many players play each position that's listed in column F?

Okay and the positions are just basketball reference positions.

How many players, again I'm counting guys who have traded more than once,

play each position?

And what fraction of the players play each position?

And then I want to know for each position

Total rebounds and assists.

Centers get the most rebounds, point guards the most assists.

And what percentage of total rebounds and assists come from each position?

Now there I do need each player listed for each team they played on.

Okay.

So trick in a pivot table, I should have a blank row here.

You'll see why in a second.

because Excel can basically understand the range you want for

a pivot table as long as you have headings in the first row.

So in other words, this is the data we want to work with.

We'd want to analyze for again the files pivot data.slxx.

Pivot data is the file.

Pivot table data's the file.

So as long as you've got blank areas around the data you want to analyze and

you click anywhere inside the data,

Excel will recognize the data you want to work with.

Okay, now if you make your data table like we talked about, then you can

refresh not a data table, but remember those Excel tables.

Then Refresh automatically updates your totals.

We'll show you Refresh in a couple minutes.

And if your original data changes then refreshing will automatically update.

Pivot table also.

Okay.

So let's focus on this.

How many players play each position?

So put your cursor anywhere in here and you go insert pivot table, insert menu.

So put your cursor here, you go insert, and you go pivot table.

Okay.

So it gives you the range of data.

It's got that good, new worksheet is fine.

Don't worry about the data model.

Okay, so we've got a new worksheet.

And then you'll see what's called the field list.

And this is the key thing.

Let's talk about, we'll talk about rows and values for now.

And then we'll learn about columns and filters a little bit later.

But you think about how you want your data to be summarized, how should it look?

Well, we want to count how many players play each position so

we should list positions going down a row, so we'll put them right there.

There's all the positions that they've got there.

Okay, and basically we also want to know basically how many people there were.

So we just want to count how many players.

Now if you're counting, it doesn't matter what you drag to values.

Usually the values will be something you want to calculate.

But if I just drag position there, I'll get how many people played

each position according to basketball reference.

So there were 85 centers.

There was one forward.

There were two guards, etc.

Now suppose I want to see the percentage of the total.

If you right click and you do show values

as, you can do this as a percentage of the total.

So this looks all this sheet position.

Okay.

So I would go right click, show values as, and I can just do percentage of column.

There's a lot of choices there.

But now I see what basketball reference of what percentage of the players played

each position.

Okay, so that's nice.

So now let's get to the next problem which is a bit harder.

For each position, total rebounds, total assists,

which we could do with some men from the last two videos.

But let's just do it with a pivot table, so

we're going to want to have the row be the position.

And then we want the rebounds and the assists to be the value fields, but

you'll see there's some little quark that we have to fix.

So we put our cursor in here, we do insert pivot table.

We've got our data, new worksheet is fine.

Okay, so I'm going to say position.

Okay, and now what we want to know is rebounds and assists.

So where's total readout is, here they are.

So that goes right there.

Okay, now it's doing sum.

It might do count, I'll show you how to fix that.

When I did this the first time it did a count, it didn't do a sum.

If you double click on the column heading.

Sorry, I clicked on the wrong thing there.

Okay so this will be assist.

Let's go here, assists and rebounds.

Okay but if you go, you can change the data

is summarized using value field settings.

So just let me show you.

See, sometimes Excel might do a count instead of doing a sum.

So if you right click value field settings,

you could change that from sum to count.

We don't want to, but originally when I did this to practice it was on count.

And by the way, we use show values as, you could do the percentage of column.

Let's do it that way.

Remember we could do the Show Values As right off the bat, okay.

But basically let's do right click > Value Field Settings > Show Values As,

and then I could say percentage of column.

Okay, so now I know the assists, what percentage of the assist by each position?

So the point guards have by far the most assists and if I do that over here,

right click, let's go back to show values as, percentage a column.

Okay, so we see for the centers they got 26% of the rebounds,

but only 9% of the assists.

At the point guard it's got 42% of the assists and 14% of the rebounds.

Okay.

So that's pretty nice.

Now, if you would go right click > Refresh, if you changed your data it would

update the pivot table, and if you added new data and you

made it an Excel table it would refresh automatically to include the new data.

But we don't have to worry about that here.

But one little trick on pivot tables before we go on to the next example.

If you double click in a cell in a pivot table,

Excel drills down.

I think of going to the dentist, I just had two implants put in.

Drills down to the source data in a separate worksheet.

So for example, if I would click here on the rounds for centers and double click.

Okay and this sheet six here, I'll call it center data.

You would see all the rows corresponding to centers, which is pretty nice.

Okay, so

we'll continue with this discussion of pivot tables in the next video.

Which I hope you'll enjoy.