The information in this article applies to:
- Microsoft Excel 97 for Windows
SUMMARY
Microsoft Excel 97 has a new worksheet function, GetPivotData, that allows
you to work with PivotTable data outside of a PivotTable.
This article contains an example that uses the GetPivotTable function to
retrieve data from a PivotTable.
MORE INFORMATION
The GetPivotData function allows you to retrieve summary data from a
PivotTable, provided that the data is visible in the PivotTable.
The syntax for the GetPivotData function is as follows:
=GETPIVOTDATA(pivot_table, name)
where the "pivot_table" argument is a reference to a cell in the
PivotTable you want to analyze. The "pivot_table" argument can also be a
range of cells in the PivotTable, a name for the range that contains the
PivotTable, or a label stored in a cell above the PivotTable. The "name"
argument is a text string that is enclosed in quotation marks and describes
the data you want to summarize.
Using the GetPivotData Function
- Save and close any open workbooks, and then create a new workbook.
- Type the following data in Sheet1:
A1: Name B1: Sales C1: Region
A2: bob B2: 1 C2: east
A3: sue B3: 2 C3: west
A4: bob B4: 3 C4: west
A5: mary B5: 4 C5: west
A6: sue B6: 5 C6: north
A7: bob B7: 6 C7: south
A8: sue B8: 7 C8: east
- Select A1:C8, and click PivotTable Report on the Data menu.
- In the PivotTable Wizard - Step 1 of 4 dialog box, select "Microsoft
Excel list or database" if it is not selected, and then click Next.
- In the PivotTable Wizard - Step 2 of 4 dialog box, click Next.
- In the PivotTable Wizard - Step 3 of 4 dialog box, drag the Name button
into the ROW field, drag the Sales button into the DATA field, and drag
the Region button into the COLUMN field. Click Next.
- In the PivotTable Wizard - Step 4 of 4 dialog box, click Existing
Worksheet, click cell A10 on Sheet1 (this step places the reference
Sheet1!$A$10 in the RefEdit box), and then click Finish.
- Select cell A10 (this step selects the entire PivotTable), point to
Name on the Insert menu, and then click Define.
- In the Define Name dialog box, type "PT1" (without the quotation marks)
in the Names In Workbook box, and then click OK.
This step defines the range for the PivotTable as "PT1" (without the
quotation marks).
- In cell E1, type the following formula:
=GETPIVOTDATA(PT1,"bob east")
The result is a value of 1, because the total for "bob" in the "east"
region is 1.
- In cell E2, type the following formula:
=GETPIVOTDATA(PT1,"west")
The result is a value of 9, because the total of "west" region is 9.
- In cell E3, type the following formula:
=GETPIVOTDATA(PT1, "sum of sales")
The result is a value of 28, because the total of all the sales in all
regions is 28.
REFERENCES
For more information about the GETPIVOTDATA worksheet function, click the
Index tab in Microsoft Excel Help, type the following text
getpivotdata
and then double-click the selected text to go to the "GETPIVOTDATA" topic.
|