2.10 PivotTables II

Loading...

En provenance du cours de University of Houston System

Math behind Moneyball

36 notes

University of Houston System

Math behind Moneyball

36 notes

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.

À partir de la leçon

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.10 PivotTables II8:38

Rencontrer les enseignants

Professor Wayne Winston
Visiting Professor
Bauer College of Business

Okay, let's pick up where we left off last time in the file

PivotTableDataFinal1, the worksheet Assists Rebounds.

So if you recall, we had the percentage of rebounds and assists by position.

Well, the Chinese say a picture is worth 1000 words, so

wouldn't we like to summarize this with a chart?

So to create a pivot chart, as it's called, you can go to the Analyze

tab, In 2013.

Or, I believe it's the options tab in Excel 2010.

So what you gotta do is go in here, and if I go Analyze, there's Pivot Chart.

And this kind of chart is fine, you can always right-click,

change the chart type, but there is a nice pivot chart there.

Okay, and that's going to show me, oh by the way,

that field list only shows up if you're in the pivot table.

And if you don't see it, you can right-click, Show Field List, and

it will come back.

But you now can see the centers don't get many assists and

the point guards get a lot of assists and not many rebounds.

The centers get a lot of rebounds.

Power-forwards are getting more rebounds,

a higher percentage of the rebounds than centers, which is sort of interesting.

I guess it's sort of nebulous who's a power forward and who's a center.

Okay.

So now what I would like to do, I guess I'll make a copy of this worksheet.

Okay.

So I can call this I guess PivotChart.

Okay, let's suppose I want to analyze this stuff by team, the statistics by position,

rebounds and assists.

Well first of all, let's get rid of the percentages, go back to the raw totals.

So I'm going to show values as no calculation.

I can see, right-click > Show Values As, and then No Calculation.

Okay, so I want to see how we did on teams.

So I go in by team.

So I go in here, I don't see the field because the field only shows up

when you're in the pivot table.

But if I go right-click > Show Field List, then it pops back up.

So let's suppose if I want to put in filters, Teams.

So I can drag that right here.

Okay.

So now up here this is the filters, I can pick any subset of teams.

Select multiple items.

So I could pick Deselect All.

I could pick the Bulls and I could pick the Mavs.

Okay, so now my stats are for the Bills, or Bulls,

sorry, thinking football, the Bills should be pretty good this year.

So the totals are for the Bulls and the Mavs.

And I can filter here and

I can take a subset of position, just center and point guards.

So now the totals are for the Bulls and the Mavs, centers and point guards.

I can clear the filters by clicking right here.

Well, the problem with the filters is, you don't know what you're filtered on, okay?

I mean here like you, I don't know what teams I picked.

Here I can see what I picked, but let's Unfilter > Select All, so

I cleared the filter.

But see, I picked Brooklyn, and what was it I picked?

Brooklyn and I picked, sorry, Dallas and Chicago.

Okay, but I didn't even remember that.

So now, a really cool thing to do with Excel 2010 or newer.

You could do Insert Slicers.

And then you can see what actually what subset of a given category you clicked on.

So by going here go Insert, okay, Slicer.

And Excel 2013 does that list timeline as a slicer for time.

I don't think we need that.

But I would like to slice it on team and position.

So I'd say Team, and where is Position.

Right here.

Okay.

So now I see these really cool slicers.

Okay, I don't really need the chart.

Although the chart would update.

Well, the chart would update, let's leave it down here.

It would update based on what I pick.

So now you can see I picked Chicago and Dallas.

Now if I clear that filter over here I see them all.

Then I could pick any subset of teams.

With the Shift key, I can pick ones that are contiguous,

with the Ctrl key I can pick those that are not contiguous.

So I could pick center and point guard here.

You can see exactly what I filtered on.

And if I go Dallas, and

then I hold down the Shift key, I've got Dallas through Houston.

And now I know those totals are for Dallas, Denver, Detroit, Golden State, and

Houston, and they're for the centers and the point guards.

And the pivot table is basically just showing centers and point guards for

those teams, then.

So those slicers that really provide a kind of dashboard which is really cool

because today everybody likes to click on stuff.

And again, one thing you can do is you go to, if you're inside a slicer, okay?

You should be able to see basically slicer tools, well,

it's not showing up on my screen I think because the screen capture, but

from slicer tools you can change the number of columns.

So right-click I can get it here, Slicer Settings.

Okay, so, there are various settings, I can go A through Z,

okay, and, stuff like that.

Well, I mean, I should be able to see, when I'm clicking inside this slide.

Oh, here it is.

Slicer Tools, I apologize, it wasn't showing up.

I think my video recorder was hiding.

But if I go slicer tools I can say for those teams, let's say five columns.

And if I click there.

Sorry, I was in the wrong one there.

I want to be in this one.

I don't want to go five columns there.

Let's just clear that.

So if I go Slicer Tools > Options, I'll go back to one column.

Okay, but now here, if I can click in this slicer,

this might be useful to have five columns.

Now, I need to make it bigger.

Right there.

So now I could pick any subset of teams really easily because I

can see all those teams on the screen at once.

So again, if you're inside a slicer, you should see Slicer Tools.

For some reason, it wasn't showing up.

Okay, so we've gone through a lot of stuff on pivot tables, but

basically, they do let you do a lot more than the sumifs and the countifs, but

when you add new data you have to click refresh, okay.

Or you change data you have to click refresh,

make the data table if you want things to update when you add data to the bottom.

And the Excel formulas automatically update.

And sometimes it's easier to use the formulas,

because they give you exactly what you want.

Sorry, the pivot tables give you a lot more information than you need.

Okay. So we taught you a lot

of useful Excel stuff, so I think we're ready to get back to sports.

So we're going to introduce you to the powerful tool of Monte Carlo simulation

and show you how that can be used to build a simple model

of how an inning in baseball goes, based on how good the hitters are.

And sophisticated Major League teams have complicated and

very realistic baseball simulations.

And you may have, like me, grown up playing those simulations.

Diamond Mind Baseball.

I grew up on Stratomatic and Big League Manager.

You might have played APBA.

I think a lot of general managers in sports grew up playing table

baseball games.

So for those of you who are in middle school and you want a job in sports,

maybe you should play those table sports games.

Okay, we'll see you in the next video on Monte Carlo simulation.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play