Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 31

Here are the steps to trudge through if you want to consolidate multiple worksheets by position:

1 Create a new worksheet that uses the same layout — including row and column labels — as the sheets you want to consolidate.The identical layout in this new worksheet is your destination range.

2 If necessary, open the workbooks that contain the worksheets you want to consolidate.If the worksheets you want to consolidate are in the current workbook, you can skip this step.

3 In the new worksheet from Step 1, select the upper-left corner of the destination range.

4 Choose Data ⇒ Consolidate.The Consolidate dialog box appears.

5 In the Function list, select the summary function you want to use.

6 In the Reference text box, select one of the ranges you want to consolidate.

7 Click Add.Excel adds the range to the All References list, as shown in Figure 1-11.

8 Repeat Steps 6 and 7 to add all the consolidation ranges.

9 Click OK.Excel consolidates the data from the source ranges and displays the summary in the destination range.

FIGURE 1-11: Consolidate multiple worksheets by adding a range from each one.

If the source data changes, you probably want to reflect those changes in the consolidation worksheet. Rather than run the entire consolidation over again, a much easier solution is to select the Create Links to Source Data check box in the Consolidate dialog box. You can then update the consolidation worksheet by choosing Data ⇒ Refresh All.