Excel Data Analysis 简明教程

Working with Multiple Sheets

在某些情况下,你可能需要

In certain situations, you might have to

  1. Set up workbooks in which several worksheets have a similar format or structure.

  2. Get information for these worksheets from another worksheet.

  3. Summarize the results from these worksheets into a summary worksheet.

例如,你可能需要按区域明智和按月明智在单独的工作表中跟踪销售信息。每个产品价格都是从公司中独立的工作表中设置的产品目录中提取的。最后,你必须将所有区域的结果汇总到一个汇总表格中。

For example, you might have to track sales information region wise and month wise in separate worksheets. The price of each product is taken from the product catalog that is set up across the company in a separate worksheet. Finally, you have to summarize the results across all the regions into a summary sheet.

在本章中,你将学习如何通过简单的步骤完成此操作。你将汇总 2015 年 4 月至 2016 年 3 月(即 2015-16 财政年度)的结果。

In this chapter, you will learn how you can accomplish this in easy steps. You are going to summarize results from April 2015 to March 2016, i.e. financial year 2015-16.

The First Step

第一步是设置产品目录。执行以下步骤−

The first step is to set up a product catalog. Follow the steps given below −

  1. Start with a blank workbook.

  2. Set up product catalog worksheet with products and prices.

  3. Name the worksheet Product Catalog.

  4. Assume the catalog is revised on the first of every month.

  5. Provide a place holder for last updated on.

销售时产品的价格由产品的当前成本决定。

The price of a product at selling time is determined by the current cost of the product.

first step

Multiple Worksheets with same Structure

接下来,您必须按照相同的结构按顺序为各个区域(东、北、南、西)设置工作表。

Next, you have to set up worksheets for the Regions – East, North, South, and West, in that order with same structure.

  1. Add 4 blank worksheets.

  2. Name the worksheets East, North, South and West.

multiple worksheets with same structure

这四个工作表应该具有相同的结构。

These four worksheets should have the same structure.

  1. Click the tab East. The East worksheet opens.

  2. Press the shift key and click on the tab West. All the 4 tabs will be selected.

select tabs

现在,您在 East 工作表中所做的任何编辑都将自动反映在其他三个选中的工作表中。

Now, whatever editing you do in the East worksheet will get automatically reflected in the other three selected worksheets.

在 East 工作表中,

In the East worksheet,

  1. Add the column headers – S. No., Month, Product, Price, No. of Units, Total Amount.

  2. Add the S. No., Month April and the 4 Product Names.

  3. Format the Table.

east worksheet

相同结构出现在其他工作表 North、South 和 West 中。

The same structure appears in the other worksheets North, South and West.

Creating a Formula across Multiple Worksheets

要在多个工作表中创建公式−

To create a formula across multiple worksheets −

  1. Define names for the price values of the products in the product catalog worksheet.

  2. Set the Scope as Workbook for all the names.

create formula
  1. Once again select all the four worksheets – East, North, South and West.

  2. In the East worksheet, for each product, in the price column, give the formula as the Price Value Name.

正如我们之前定义的一样,产品价格按照每月 1 日更新的产品目录设定。

As we defined earlier, the price of a product is as per the product catalog that is updated on the first of every month.

product price
  1. Repeat the same steps for each Month

repeat same steps

因此,对于东部、北部、南部和西部区域的工作表,您已成功设置相同的结构,并根据产品目录工作表将每个产品的价格信息按月份放置。

Therefore, for the worksheets for the regions East, North, South, and West, you have successfully set the same structure and placed the price information for each product based on the month from product catalog worksheet.

产品目录也可以在另一个工作簿中。

The Product Catalog can be in another Workbook too.

Calculations in the Worksheets

下一步是填入每个月和每个区域中每个产品的已售出单位数的信息。因此,您需要在这些工作表上分别进行操作。

The next step is to fill in the information of No. of Units sold for each Product in each Month and in each Region. Therefore, you need to work separately on these worksheets.

对于每个区域,对于每个产品——

For each region, for each product −

  1. Fill No. of Units sold.

  2. Calculate the corresponding Total Amount as Price*No. of Units.

calculate total amount

在每个工作表(东部、北部、南部和西部)中,按月计算小计——

In each worksheet (East, North, South and West), calculate subtotals month-wise −

calculate subtotals

Note ——您可以在单个工作表中使用小计,但不能在多个工作表中使用。因此,您必须对北部、南部和西部工作表重复此步骤。

Note − You can use Subtotal on a single worksheet but not on multiple worksheets. Hence, you have to repeat this step for North, South and West worksheets.

subtotal

单击大纲级别 2。您将获得全部按月计算的总计。

Click the Outline Level 2. You will get all month-wise Totals.

outline level 2

现在,您可以准备将所有四个工作表——东部、北部、南部和西部中的结果汇总。

Now, you are ready to summarize the results from all the four worksheets – East, North, South and West.

Summarizing Data in Multiple Worksheets

以下步骤将向您展示如何汇总来自多个工作表的的数据。

The following steps will show you how to summarize data from multiple worksheets.

  1. Add a worksheet and name it Summary.

  2. Create the structure for Summary worksheet.

summarizing data

在列 Total Sales 中,在单元格 C3 中,键入 = sum(

In the column- Total Sales, in the cell C3, type =sum(

total sales
  1. Select the worksheet East.

  2. Select the cell G7.

  3. With East tab pressed, click the tab West.

  4. The tabs East to West get selected.

  5. The formula in the formula bar appears as

=sum(‘East:West’!G7)

=sum(‘East:West’!G7)

select worksheet east

请注意,您仍处于 East 工作表中。按 Enter 键。

Note that you are still in the East worksheet. Press Enter.

您将进入 Summary 工作表。在公式栏中,您将看到公式为

You will be in the Summary worksheet. In the formula bar, you will see the formula as

=SUM(East:West!G7)

=SUM(East:West!G7)

计算值显示在单元格 C3 中。

The calculated value appears in the cell C3.

calculated value in cell
  1. Copy the formula to the cells C4 to C14.

  2. Click Show Formulas in the Formula Auditing group under the FORMULAS tab.

将显示“Total Sales”列中的所有公式。

All the formulas in the column Total Sales appear.

total sales appear

这正是您想要汇总各个区域的结果的方式。

This is exactly how you wanted to summarize the results from each region.

  1. Click in the cell C15.

  2. Type =sum(C3:C14)

summarize results

您的汇总结果已在“Summary”工作表中准备好。

Your summarized results are ready in the Summary worksheet.

summarized results ready