Using Excel's PivotTable Tool

Описание: 1

PivotTables enable you to explore large amounts of data easily. In this
video, I'd like to show how to create and work with the PivotTable tool
inside Windows Excel. These instructions are tailored for Excel 2007 or
newer, but the basics should apply even to earlier versions of Excel.

2

First, start Excel and open the data set that you'd like to explore.
I'll assume that your data is arranged with the variables as columns and
that your data also includes column headings.

3

In this video, I'll be working with a data set involving home sales.
Each row in this data set represents a single home, and each column
gives a characteristic of each home -- for example, the price of the
home, the number of bedrooms, etc. Note that this data set is fairly
large, containing over 2,700 rows. A PivotTable will be a nice tool for
getting a handle on this amount of data.

4

When you're ready, select the rectangular array of cells around the
entire data set. Then click on the Insert tab and choose PivotTable.

5

In the next dialog box, you'll notice that Excel has already referenced
the data you selected. You can change several options here, but clicking
OK is a good default. You'll then be presented with a blank PivotTable.

6

In the list of variables on the right, click and drag the main variable
that you'd like to measure into the Values section. In this case, I'd
like to examine Price.

7

You'll see on the left that an initial PivotTable has been automatically
created. At the moment, it simply tells us the sum of all price data in
the spreadsheet.

8

Our first modification is to make sure the PivotTable is showing
the average price. So click on the Value Field in the bottom right and
choose Value Field Settings. Then ensure that Average is chosen. ...
While in this dialog box, I'll also choose the number format to match
the data. ... When ready, click OK.

9

Our second change is to examine the average price by city. So click
and drag the corresponding column heading into the Rows section. The
PivotTable updates to show not only the overall average price -- but
also the average price by city. Here we can see which city has the
highest or the lowest average price, for example.

10

Our last change is to also examine the average price by age. Here, click
and drag the corresponding column heading into the Columns section.
Again, the PivotTable updates, but you can see that -- because there are
so many different Age values -- the table has become quite large.

11

To make the PivotTable more manageable, we need to group the columns
into several intervals ... or bins ... as a means of aggregating the
data. Make sure you have selected one of the cells in the column data
... Then on the Analyze tab, choose Group Selection.

12

In the next dialog box, you can select the starting value of the first
bin, the ending value of the last bin, and the width of each bin. You
can play around with these numbers to set them however you like. ...
When ready, click OK. ... The PivotTable should then update with the
specified bins.

13

We now have a PivotTable that examines average price by city and by
age. We also see the so-called Grand Totals at the edges of the tables.
Overall, this gives us a nice breakdown of how average price behaves
based on changing the city and changing the age.

14

An optional, extra step is to choose the Analyze tab and then
PivotChart. Then choose a basic Column chart and click OK. Excel will
then prepare the chart that goes along with the table. This can be used
to help visualize the information in the PivotTable.

15

Overall, you should feel free to experiment with PivotTables. You may
have to examine your data in many different ways before you find the
exact answer to the question you care about. PivotTables can always be
de-constructed by just clicking and dragging column headings out of the
various PivotTable sections.