Microsoft Office 2003 Super Bible

Podstrony

[ Pobierz całość w formacie PDF ]
.This pivot table shows sales,broken down by month and sales rep.Figure 20-17: This pivot table was created from the data in Figure 20-16.The examples that follow will create&' A calculated field, to compute average sales per unit&' A calculated item, to summarize the data by quartersCreating a calculated field in a pivot tableBecause a pivot table is a special type of data range, you can t insert new rows or columnswithin the pivot table.This means that you can t insert formulas to perform calculations withthe data in a pivot table.However, you can create calculated fields for a pivot table.Acalculated field consists of a calculation that can involve other fields.486 Part III &' Beyond Mastery: Initiative within OfficeA calculated field is basically a way to display new information in a pivot table.Itessentially presents an alternative to creating a new Data field in your source database.Acalculated field cannot be used as a Row, Column, or Page field.In the sales example, for instance, suppose you want to calculate the average sales amountper unit.You can compute this value by dividing the Sales field by the Units Sold field.Theresult shows a new field (a calculated field) for the pivot table.Use the following procedure to create a calculated field that consists of the Sales fielddivided by the Units Sold field:1.Move the cell pointer anywhere within the pivot table.2.Using the PivotTable toolbar, choose PivotTable Formulas Calculated Field.Excel displays the Insert Calculated Field dialog box.3.Enter a descriptive name in the Name field and specify the formula in the Formulafield (see Figure 20-18).The formula can use other fields and worksheet functions.For this example, the calculated field name is Avg Unit Price, and the formulaappears as the following:=Sales/ Units Sold4.Click Add to add this new field.5.Click OK to close the Insert Calculated Field dialog box.Figure 20-18: The Insert Calculated Field dialog box.You can create the formula manually by typing it or by double-clicking items in the Fields listNotebox.Double-clicking an item transfers it to the Formula field.Because the Units Sold fieldcontains a space, Excel adds single quotes around the field name.After you create the calculated field, Excel adds it to the Data area of the pivot table.Youcan treat it just like any other field, with one exception: You can t move it to the Page, Row,or Column area.(It must remain in the Data area.)Chapter 20 &' Analyzing Data with Pivot Tables in Excel 487Figure 20-19 shows the pivot table after you ve added the calculated field.The new fielddisplays as Sum of Avg Unit Price.(You can change this text, if desired, by editing any ofthe cells in which that text appears.) The calculated field also appears on the PivotTableField List toolbar, along with the other fields available for use in the pivot table.Figure 20-19: This pivot table uses a calculated field.TipThe formulas that you develop can also use worksheet functions, but the functions cannot referto cells or named ranges.Inserting a calculated item into a pivot tableThe preceding section describes how to create a calculated field.Excel also enables you tocreate a calculated item for a pivot table field.Keep in mind that a calculated field can be analternative to adding a new field to your data source.A calculated item, on the other hand,uses the contents of items within a single field.The sales example uses a field named Month, which consists of text strings.You can create acalculated item (called Qtr-1, for example) that displays the sum of Jan, Feb, and Mar.You also can do this by grouping the items, but using grouping hides the individual monthsand shows only the total of the group.Creating a calculated item for quarterly totals is moreflexible because it shows the total and the individual months.488 Part III &' Beyond Mastery: Initiative within OfficeTo create a calculated item to sum the data for Jan, Feb, and Mar, follow these steps:1.Move the cell pointer to the Row, Column, or Page area of the pivot table thatcontains the item that will be calculated.In this example, the cell pointer should bein the Month area.2.Use the PivotTable toolbar, and choose PivotTable Formulas Calculated Itemfrom the shortcut menu.Excel displays the Insert Calculated Item dialog box.3.Enter a name for the new item in the Name field and specify the formula in theFormula field (see Figure 20-20).The formula can use items in other fields, but itcan t use worksheet functions.For this example, the new item is named Qtr-1, andthe formula appears as follows:=Jan+Feb+Mar4.Click Add.5.Repeat Steps 3 and 4 to create additional calculated items for Qtr-2(=Apr+May+Jun), Qtr-3 (=Jul+Aug+Sep), and Qtr-4 (=Oct+Nov+Dec).6.Click OK to close the dialog box.Figure 20-20: The Insert Calculated Item dialog box.If you use a calculated item in your pivot table, you may need to turn off the Grand Total displayCautionto avoid double counting.In this example the Grand Total includes the calculated item, so eachmonth is counted twice.To turn off Grand Totals, use the PivotTable Options dialog box (see the Pivot Table Options sidebar, earlier in this chapter).After you create the items, they appear in the pivot table.Figure 20-21 shows the pivot tableafter you ve added the four calculated items.Notice that the calculated items are added tothe end of the Month items.You can rearrange the items by selecting and dragging.Figure20-22 shows the pivot table after rearranging the items logically.(Calculated items weremade bold.)Chapter 20 &' Analyzing Data with Pivot Tables in Excel 489Figure 20-21: This pivot table uses calculated items for quarterly totals.Figure 20-22:The pivot table, after rearranging the calculated items.A calculated item appears in a pivot table only if the field on which it is based also appears.IfNoteyou remove or pivot a field from either the Row or Column category into the Data category, thecalculated item does not appear.It s also possible to get quarterly summaries by grouping items.Because the month namesare not actual dates, the grouping must be done manually.Figure 20-23 shows the pivottable after creating four groups.You create the first group by selecting the Jan, Feb, and Maritems.Then you right-click, and choose Group and Show Detail Group from the shortcutmenu.Excel inserted the default name, Group 1 which you then change to Qtr 1.Next,right-click the group item and chose Field Settings to display the PivotTable Field dialogbox.In this dialog box, you would specify the Sum function to summarize the grouped data.Finally, you then repeat this process for the other three quarters [ Pobierz całość w formacie PDF ]

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

bezglutenowo.pev.pl