[ Pobierz całość w formacie PDF ]
.Consolidating by PositionWhen you consolidate by position, Excel applies the consolidation function (Sum, Average, orwhatever else you select) to the same cell references in each supporting worksheet.This is thesimplest way to consolidate, but your supporting worksheets must have exactly the same layout.Figure 9-35 shows a simple example of a workbook containing a master worksheetConsolidated that matches the layout of 12 supporting monthly worksheets.These work-sheets can be consolidated by position because each contains identically structured data.f09ie35Figure 9-35.All the worksheets in this workbook are identical, which is necessary whenconsolidating by position.275Chapter 9Part 3: Formatting and Editing WorksheetsMicrosoft Office Excel 2003 Inside OutNote You can find the sample file used in this example, Pacific Brass Sales.xls, on thecompanion CD.To consolidate the monthly worksheets in Figure 9-35 into the sheet named Consolidated,follow these steps:1 Open the consolidation worksheet and select the block of cells that will receive the con-solidated data.In Figure 9-35, the destination area is the range B5:I10.2 Choose Data, Consolidate.3 Use the default Sum function in the Function list.Leave the options in the Use LabelsIn section and the Create Links To Source Data option unselected.4 Select each source range with the mouse.Tip Open supporting workbooksIf you are consolidating data in more than one workbook, you have to type references tothe data ranges you want to consolidate in any workbooks that are not currently open.For this reason, make sure all supporting workbooks are open while you are building yourconsolidation sheet.Once you have the consolidation set up and the workbook is saved,supporting workbooks can stay closed during future consolidations.If you do have to typea reference, it must use the form [Filename]Sheetname!Reference.If the reference is inthe same workbook, the file name (and its surrounding brackets) is unnecessary.If thesource range has been assigned a name, you can use this name in place of Reference(highly recommended).If you use the mouse to select your source ranges, click the button on the right end ofthe Reference box to collapse the Consolidate dialog box and get it out of the way.Open worksheets by clicking their tabs.If a workbook is open but obscured by otherworkbooks on the screen, you can get to it by choosing its name from the Windowmenu.All these window maneuvers can be performed while you make your selectionsin the Consolidate dialog box; the dialog box remains active until you close it.5 Click Add in the Consolidate dialog box.Excel transfers the reference from the Refer-ence edit box to the All References list.Figure 9-36 shows the completed dialog box.Repeat for each sheet you want to consolidate.After you add the first range $B$5:I$10 in the Jan sheet Excel selects the same range ineach sheet when you click its tab.Just click a sheet tab and then click Add to add references.Figure 9-37 shows the resulting consolidation.276Chapter 9Part 3: Formatting and Editing WorksheetsAdvanced Formatting and Editing TechniquesTip Preserve consolidation referencesAfter you perform a consolidation, the references you enter in the Consolidate dialog boxare retained when you save the workbook.The next time you open the workbook and wantto refresh the consolidated values, rather than entering the references again, choose Con-solidate and click OK.f09ie36Figure 9-36.The Consolidate command uses the references in the All References box tocreate the consolidated totals.f09ie37Figure 9-37.Range B5:I10 in the Consolidated worksheet now contains totals of the corre-sponding cells in the 12 supporting worksheets.Consolidating by CategoryNow let s look at a more complex example.The Pacific Sales Staff workbook containsmonthly sales totals for each salesman, but each monthly sheet has different salespeople anda different number of salespeople, as shown in Figure 9-38.277Chapter 9Part 3: Formatting and Editing WorksheetsMicrosoft Office Excel 2003 Inside Outf09ie38Figure 9-38.Use the categories in the left column of each source worksheet as the basisfor this consolidation.Note You can find the sample file used in this example, Pacific Sales Staff.xls, on thecompanion CD.The consolidation worksheet has column headings for Units and Sales each worksheet isthe same in this respect.However, the consolidation worksheet has no row headings.Youneed to omit the row headings because they are not consistently arranged in the source work-sheets.As you ll see, the Consolidate command enters the row headings for you.To consolidate by category, follow these steps:1 Select the destination area.This time the destination area must include the row headingsbut how many rows? To answer that, you can look at each source worksheet and deter-mine how many unique line items you have.An easier way, however, is to select a singlecell in this case, cell A4 as the destination area.When you specify a single cell as yourdestination area, the Consolidate command fills in the area below and to the right ofthat cell as needed.In the example, to preserve the formatting, we inserted more thanenough rows to accommodate the data.2 Choose Data, Consolidate.3 To consolidate by row categories in this example, select Left Column in the Use LabelsIn section.Use the default Sum function in the Function list.278Chapter 9Part 3: Formatting and Editing WorksheetsAdvanced Formatting and Editing Techniques4 The consolidation worksheet already has column labels, so you can omit them fromthe source worksheet references.Each source reference must include row headings,Units and Sales.Select these ranges on each monthly worksheet.For example, on theJan sheet, we selected $A$4:$C$8.Unlike when consolidating by position, you have tomanually select the ranges in each supporting sheet, because Excel selects the lastrange you added, which will not necessarily be the same in each sheet.5 Click OK, and Excel fills out the Consolidated worksheet, as shown in Figure 9-39.f09ie39Figure 9-39.The Consolidate command created a separate line item in the consolidationworksheet for each unique item in the source worksheets.The consolidation worksheet now includes a category that corresponds to each unique lineitem in the source worksheets.If two or more worksheets have the same line item, the con-solidation worksheet performs the selected mathematical operation on the correspondingfigures for each column position.Note It s important that your categories in our example, the names of salespeopleare spelled identically on each supporting sheet.Otherwise, Excel will create a separateline and consolidation for each spelling variation.Creating Links to the Source WorksheetsThe previous examples consolidated numbers with the Sum function, resulting in a range ofconsolidated constants.Subsequent changes to the source worksheets will not affect the con-solidation worksheet until you repeat the consolidation.You can also use the Consolidate command to create links between the consolidation andsource worksheets [ Pobierz całość w formacie PDF ]