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

Here are the steps to follow to consolidate multiple worksheets by category:

1 Create a new worksheet for the consolidation.You use this worksheet to specify 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.When you’re selecting the range, be sure to include the row and column labels in the range.

7 Click Add.Excel adds the range to the All References list.

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

9 If you have labels in the top row of each range, select the Top Row check box.

10 If you have labels in the left-column row of each range, select the Left Column check box.Figure 1-12 shows a completed version of the Consolidate dialog box.

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

FIGURE 1-12: When consolidating by category, tell Excel where your labels are located.