Excel 简明教程

Data Tables in Excel 2010

Data Tables

在 Excel 中，数据透视表是一种通过修改公式中的输入单元格来查看不同结果的方法。数据透视表在 MS Excel 的 Data Tab » What-If analysis dropdown » Data table 中可用。

In Excel, a Data Table is a way to see different results by altering an input cell in your formula. Data tables are available in Data Tab » What-If analysis dropdown » Data table in MS Excel.

protect workbook

Data Table with Example

现在，我们通过一个示例来看数据透视表的概念。假设您有许多值的价格和数量。此外，您还有一个用于计算净价格的折扣作为第三个变量。您可以借助数据透视表以组织表格格式保留净价格值。您的价格水平向右展开，而数量垂直向下展开。我们正在使用公式来计算净价格，计算方式为价格乘以数量减去总折扣（数量 * 每个数量的折扣）。

Now, let us see data table concept with an example. Suppose you have the Price and quantity of many values. Also, you have the discount for that as third variable for calculating the Net Price. You can keep the Net Price value in the organized table format with the help of the data table. Your Price runs horizontally to the right while quantity runs vertically down. We are using a formula to calculate the Net Price as Price multiplied by Quantity minus total discount (Quantity * Discount for each quantity).

data table example1

现在，要创建数据透视表，请选择数据透视表范围。选择 Data Tab » What-If analysis dropdown » Data table 。它将为您提供一个对话，询问输入行和输入列。将输入行指定为价格单元格（在本例中为单元格 B3），将输入列指定为数量单元格（在本例中为单元格 B4）。请参阅下面的屏幕截图。

Now, for creation of data table select the range of data table. Choose Data Tab » What-If analysis dropdown » Data table. It will give you dialogue asking for Input row and Input Column. Give the Input row as Price cell (In this case cell B3) and Input column as quantity cell (In this case cell B4). Please see the below screen-shot.

data table example2

单击“确定”将生成数据透视表，如下面的屏幕截图所示。它将生成表公式。您可以水平更改价格或垂直更改数量，以查看净价格的变化。

Clicking OK will generate data table as shown in the below screen-shot. It will generate the table formula. You can change the price horizontally or quantity vertically to see the change in the Net Price.

data table example3