Excel Data Analysis 简明教程

Excel Data Analysis - Subtotals with Ranges

如果您有想要分组并汇总的数据列表，则可以使用 Excel Subtotal 和 Outline 来显示汇总行或列。您也可以使用 PivotTable 来实现此目的，但是使用 Subtotal 和 Outline 是分析数据范围的最快速方法。请注意， Subtotal 和 Outline 只能用于范围，而不能用于表格。

If you have a list of data that you want to group and summarize, you can use Excel Subtotal and Outline to display summary rows or columns. You can use PivotTable also for this purpose, but using Subtotal and Outline is the quickest way to analyze a range of data. Note that Subtotal and Outline can be used only on a range and not on a table.

您可以创建一个最多包含八个级别的 Outline ，每个组一个级别。外部级别表示为较低数字，内部级别表示为较高级数字。每个内部级别都会显示上一外部级别的详细数据。

You can create an Outline of up to eight levels, one for each group. Outer Levels are represented by Lower Numbers and Inner Levels by Higher Numbers. Each inner level displays detailed data for the preceding outer level.

若要了解如何使用 Subtotal 和 Outline ，请考虑以下示例，其中按销售人员和地点给出了不同产品的销售数据。总共有 1891 行数据。

To understand how to use Subtotal and Outline, consider the following example wherein the sales data of various items is given salesperson wise and location wise. In total, there are 1891 rows of data.

rows data

Subtotals

您可以使用 Subtotal 获得按销售地点进行汇总的销售额。

You can obtain the sum of sales location wise using Subtotal.

首先，按数据按地点进行排序。

First, sort the data location wise.

  1. Click anywhere on the data range.

  2. Click the DATA tab.

  3. Click Sort.

选择了数据。 Sort 对话框出现。

Data is selected. The Sort dialog box appears.

subtotals

在 Sort 对话框中，

In the Sort dialog box,

  1. Select Location for Sort by

  2. Select Values for Sort On

  3. Select A to Z for Order

sort dialog box

点击 OK 。数据按位置排序。

Click OK. The data is sorted location wise.

data sorted

  1. Click anywhere on the Data Range.

  2. Click DATA tab.

  3. Click Subtotal in the Outline group. The data gets selected and the Subtotal dialog box appears.

subtotal dialog box

在 Subtotal dialog 框中，

In the Subtotal dialog box,

  1. Select Location under At each change in:

  2. Select Sum under Use function:

  3. Select Unit and Amount under Add subtotal to:

  4. Select Replace current subtotals

  5. Select Summary below data

add subtotal

点击 OK 。数据按三个级别分组，小计按位置计算。

Click OK. The data is grouped with three levels and the subtotals are calculated location wise.

Note - 显示的数据是第 3 级 - 即整个数据。

Note − The data that is displayed is of Level 3 – i.e. entire data.

calculate subtotal

点击大纲级别 2。将按单位和金额按位置显示 Totals 。

Click the Outline Level 2. The Totals will be displayed location wise for units and amount.

dispaly totals

点击大纲级别 1。将按单位和金额显示 Grand Totals 。

Click Outline Level 1. The Grand Totals will be displayed for units and amount.

grand totals

您可以通过点击 Outline Levels 或数据左侧的 + 符号来放大或缩小数据。

You can zoom-in or zoom-out the data by clicking the Outline Levels or by clicking the + Symbols to the left of the data.

Nested Subtotals

您可以使用 Nested Subtotals 根据每个销售人员按位置获得销售总和。

You can obtain the sum of sales by each salesperson, location wise using Nested Subtotals.

根据 Sort 数据按位置，然后按销售人员。

Sort the data location wise and then salesperson wise.

  1. Click anywhere on the data range.

  2. Click the DATA tab.

  3. Click Sort. The data is selected and the Sort dialog box appears.

nested subtotals

在 Sort 对话框中，

In the Sort dialog box,

  1. Select Location for Sort by

  2. Select Values for Sort On

  3. Select A to Z for Order

  4. Click on Add Level

Then by 行显示

Then by row appears

  1. Select Name for Then by

  2. Select Values for Sort On

  3. Select A to Z for Order

add level

点击 OK 。数据按位置分类，再按名称分类。

Click OK. The data is sorted by location and then by name.

sort by location

  1. Click anywhere on the Data Range

  2. Click on DATA tab

  3. Click on Subtotal in the Outline group

数据得到选中。 Subtotal 对话框出现。

Data gets selected. Subtotal dialog box appears.

data selected

在 Subtotal 对话框中，

In the Subtotal dialog box,

  1. Select Location under At each change in:

  2. Select Sum under Use function:

  3. Select Unit and Amount under Add subtotal to:

  4. Select Replace current subtotals

  5. Select Summary below data

location

单击 OK 。数据按三层分组，子总计按前文所述的方式按位置计算。

Click OK. The data is grouped with three Levels and the subtotals are calculated location wise as described earlier.

outline levels

  1. Click Subtotal.

在 Subtotal 对话框中，

In the Subtotal dialog box,

  1. Select Name under At each change in:

  2. Select Sum under Use function:

  3. Select Unit and Amount under Add subtotal to:

  4. Unselect Replace current subtotals

  5. Select Summary below data

click subtotal

单击 OK 。数据按四层分组，子总计按位置和名称计算。

Click OK. The data is grouped with four levels and the subtotals are calculated location wise and name wise.

calculated location and name wise

单击 Outline Level 3 。 Totals 将显示按单元和金额的名称和位置。

Click Outline Level 3. The Totals will be displayed name wise and location wise for Units and Amount.

outline level 3

单击 Outline Level 2 。 Totals 将显示按单元和金额的位置。

Click on Outline Level 2. The Totals will be displayed location wise for Units and Amount.

sub total outline level 2

单击 Outline Level 1 。 Grand Totals 将显示单元和金额。

Click Outline Level 1. The Grand Totals will be displayed for Units and Amount.

outline level 1

通过单击 Outline Levels ，或者单击数据左侧的+号，可以放大或缩小数据。

You can zoom-in or zoom-out the data by clicking the Outline Levels or by clicking the + symbol to the Left of the data.