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

Although you can use formulas and worksheet functions to summarize your data in various ways — including sums, averages, counts, maximums, and minimums — if you’re in a hurry, or if you just need a quick summary of your data, you can get Excel to do the work for you. The secret here is a feature called automatic subtotals, which are formulas that Excel adds to a worksheet automatically.

Excel sets up automatic subtotals based on data groupings in a selected field. For example, if you ask for subtotals based on the Customer field, Excel runs down the Customer column and creates a new subtotal each time the name changes. To get useful summaries, you should sort the range on the field containing the data groupings you’re interested in.

Follow these steps to summarize your data with subtotals:

1 Select a cell within the range you want to subtotal.

2 Choose Data ⇒ Subtotal.If you don’t see the Subtotal command, choose Outline ⇒ Subtotal. The Subtotal dialog box appears.

3 In the At Each Change In list, select the column you want to use to group the subtotals.

4 In the Use Function list, select Sum.

5 In the Add Subtotal To list, select the check box for the column you want to summarize.In Figure 1-8, for example, each change in the Customer field displays the sum of that customer’s Total cells.

6 Click OK.Excel calculates the subtotals and adds them into the range. Note, too, that Excel also adds outline symbols to the range. I talk about outlining in a bit more detail in the next section.

FIGURE 1-8: Use the Subtotal dialog box to apply subtotals to a range.

Figure 1-9 shows some subtotals applied to a range.

FIGURE 1-9: Some subtotals applied to the Total column for each customer.

Note that in the phrase, automatic subtotals, the word subtotals is misleading because it implies that you can summarize your data only with totals. Not even close! Using “subtotals,” you can also count the values (all the values or just the numeric values), calculate the average of the values, determine the maximum or minimum value, and calculate the product of the values. For statistical analysis, you can also calculate the standard deviation and variance, both of a sample and of a population. To change the summary calculation, follow Steps 1 to 3, open the Use Function drop-down list, and then select the function you want to use for the summary.