| You can consolidate data from different locations in the same notebook or even from different notebooks. When you consolidate data, Quattro Pro combines the information in cells you specify to provide the results. Suppose, for example, that you have collected and entered sales information for three products across three regions. Now, you need the total sales for each product across all regions. Your data might look something like Figure 13.14, in which I've set up an area in the spreadsheet where I'd like the consolidated information to appear. Although I'm going to show you how to add like values in each set of data, Quattro Pro can also combine the values and average, count, and calculate the minimum or maximum values, the standard deviation, the variance, the sample standard deviation, or the sample variance of the data. Figure 13.14. A spreadsheet containing individual product sales for four regions that needs to be consolidated into a company total. 
To successfully consolidate data, your data should have common row or column labels in each set of data you want to consolidate. The values in each set of data do not need to appear in the same order nor do you need to have the same number of values in each data set. In my example, I used three products in each region to make it easy for you to see how consolidation works, but I could just as easily have had two products in one region, three products in two regions, and four products in another region. Quattro Pro combines the values of cells with common labels. To consolidate the data, follow these steps: 1. | Open the Tools menu, point to Consolidate, and click New. Quattro Pro displays the first step of the Consolidate Expert.
| 2. | Click the Range Picker in the Source Cells box to collapse the Consolidate Expert and identify the first set of data to consolidate. In this example, I selected A3.B6.
| 3. | Click the Maximize button in the collapsed window to redisplay the Consolidate Expert and click the Add button. Quattro Pro adds the range in the box below the Source cells box.
| 4. | Repeat steps 2 and 3 for each range you want to consolidate. In Figure 13.15, I am just about to add the fourth region's sales data to the consolidation.
Figure 13.15. Define the regions to consolidate. 
| 5. | Click Next. step 2 of 4 of the Consolidate Expert appears (see Figure 13.16).
Figure 13.16. Select an operation for the consolidation. 
| 6. | Click the list box arrow to select the operation you want Quattro Pro to perform when it consolidates the data.
| 7. | Click Next. step 3 of 4 of the Consolidate Expert appears (see Figure 13.17).
Figure 13.17. Identify the destination for the consolidated cells. 
| 8. | Click the in the Destination Cells box to collapse the Consolidate Expert and identify the destination for the consolidated data. You can identify a single cell, and Quattro Pro will use surrounding cells as needed for the consolidated data. In this example, I selected F3.
Caution  | Quattro Pro will overwrite any data in the destination range with the consolidation information, so be sure to select an empty area of the spreadsheet. |
| 9. | Click Next. step 4 of 4 of the Consolidate Expert appears (see Figure 13.18).
Figure 13.18. Assign a name to the consolidation. 
| 10. | Type a name for the consolidation in the Consolidation Name box.
| 11. | Click Consolidate. Quattro Pro performs the consolidation and places the results in the destination cell range (see Figure 13.19).
Figure 13.19. Consolidated data appears in F3.G6. 
|
Tip  | If the source cell range includes labels, you can assign those labels to the destination cell range by selecting the Top Row check box, the Left Column check box, or both. Quattro Pro will organize the data in the destination range using the order of the first source cell range. |
In Figure 13.19, I highlighted G3 so that you can see its contents in the Formula bar; notice that Quattro Pro used the @SUM function to add the values for widgets in each of the three regions and present their consolidated total. Tip | You can change consolidation settings by opening the Tools menu, pointing to Consolidate, and clicking Edit. Then, select the consolidation you named and change settings. |
The Absolute Minimum As you learned in Chapter 12, a database is a collection of similar information like your local telephone book. In this chapter, you learned how to sort and filter the information in a Quattro Pro database, and then you looked at some of the analysis tools available in Quattro Pro. You saw how to: Transpose information in rows and columns using the Transpose Cells dialog box, opened by clicking Tools, Numeric Tools, and then Transpose. Use the What-If Expert, another tool on the Numeric Tools menu, to see what happens if you substitute different values into a formula. Combine values in different cells using the Consolidate Expert.
In the next chapter, we'll explore some more of the analysis tools available in Quattro Pro. |
|
