对Excel表格使用结构化引用.docx

对 Excel 表格使用结构化引用 当您使用引用表格的公式时，无论该公式引用了部分表格还是整个表格，结构化引用都可以使表格数据的处理变得容易、直观得多。结构化引用特别有用，因为表格数据区域经常变化，而结构化引用的单元格引用可随之自动调整。这样便在最大程度上减少了在表格中添加和删除行和列时或刷新外部数据时重写公式的需要。以下结构化引用更易于理解：以下单元格引用较难理解：=SUM(DeptSalesSaleAmt)=Sum(C2:C7)本文内容 “部门销售”表格示例 结构化引用的组件 表名称和列说明符 引用运算符 特殊项目说明符 计算列中的限定结构化引用 使用结构化引用的示例 使用结构化引用 结构化引用语法规则“部门销售”表格示例本文中将通篇引用以下表格示例，它基于由六位员工组成的部门的销售情况，包括最新的销售额和佣金。“部门销售”表格整个表格 (A1:E8)表格数据 (A2:E7)某列和列标题 (D1:D8)某计算列 (E1:E8)汇总行 (A8:E8)返回页首结构化引用的组件要有效使用表格和结构化引用，需要了解如何在创建公式时创建结构化引用的语法。结构化引用的组件将在以下示例中进行说明，示例中的公式将对总销售额和佣金额求和：表名称是指提供用于引用实际表格数据（若有标题行和汇总行，将不包括它们）的有意义的名称。列说明符从列标题派生而来，它由括号括起，并引用列数据（若有列标题和汇总，将不包括它们）。特殊项目说明符是一种引用表的特定部分（例如汇总行）的方法。表说明符是结构化引用的外层部分，它跟在表名称之后，用方括号括起。结构化引用是以表名称开始、以表说明符结尾的整个字符串。返回页首表名称和列说明符每次插入表格时，Excel 都会在全局工作簿级别或范围创建默认表名称（Table1、Table2 等）。您可以轻松更改该名称，使其对您更富有意义。例如，要将 Table1 改为 DeptSales，可以使用“编辑名称”对话框。（在“设计”选项卡上“属性”组的“表名称”框中，编辑表名称。）表名称引用表格中除标题行和汇总行之外的整个数据区域。在“部门销售”表格示例中，表名称 DeptSales 引用单元格区域 A2:E7。与表名称相似，列说明符代表对除列标题和汇总之外的整列数据的引用。在“部门销售”表格示例中，列说明符 Region 引用单元格区域 B2:B7，而列说明符 ComPct 引用单元格区域 D2:D7。返回页首引用运算符为了在指定单元格区域时增加灵活性，可以使用以下引用运算符来组合列说明符。以下结构化引用：引用：通过使用：它在示例中为单元格区域：=DeptSalesSalesPers:Region两个或更多个相邻列中的所有单元格:（冒号）区域运算符A2:B7=DeptSalesSaleAmt,DeptSalesComAmt两个或更多个列的组合,（逗号）联合运算符C2:C7, E2:E7=DeptSalesSalesPers:SaleAmt DeptSalesRegion:ComPct两个或更多个列的交集（空格）交叉运算符B2:C7返回页首特殊项目说明符为增加便利性，还可以使用特殊项目来引用表格的各个部分，例如只引用汇总行，以使在公式中引用这些部分更加容易。以下是可以在结构化引用中使用的特殊项目说明符：以下特殊项目说明符：引用：它在示例中为单元格区域：=DeptSales#All整个表格，包括列标题、数据和汇总（如果有）。A1:E8=DeptSales#Data仅数据。A2:E7=DeptSales#Headers仅标题行。A1:E1=DeptSales#Totals仅汇总行。如果不存在汇总行，它将返回 null。A8:E8=DeptSales#This Row仅为当前行中的列部分。#ThisRow 不能与任何其他特殊项目说明符组合。它可以用于强制执行引用的隐式交叉行为，或替代隐式交叉行为并引用列中的单值。有关更多示例，请参阅使用结构化引用的示例。A5:E5（如果当前行为第 5 行）返回页首计算列中的限定结构化引用创建计算列时，通常使用结构化引用来创建公式。此结构化引用可以是非限定的，也可以是完全限定的。例如，要创建一个名为 ComAmt 的计算列来计算以人民币表示的佣金额，可以使用以下公式：结构化引用的类型示例注释非限定=SaleAmt*ComPct乘以当前行中的相应值。完全限定=DeptSalesSaleAmt*DeptSalesComPct为两个列的每一行都乘以相应值。应遵循的一般规则为：如果在表格内使用结构化引用，例如，在创建计算列时，则可以使用非限定的结构化引用，但如果在表格之外使用结构化引用，则需要使用完全限定的结构化引用。返回页首使用结构化引用的示例可以通过多种方式来使用这些特殊项目以及将它们与表名称和列引用组合，如以下信息所示：以下结构化引用：引用：它在示例中为单元格区域：=DeptSales#All,SaleAmt“SaleAmt”列中的所有单元格。C1:C8=DeptSales#Headers,ComPct“ComPct”列的标题。C1=DeptSales#Totals,Region“Region”列的汇总。如果不存在汇总行，将返回 null。 B8=DeptSales#All,SaleAmt:ComPct“SaleAmt”和“ComPct”列中的所有单元格。C1:D8=DeptSales#Data,ComPct:ComAmt仅“ComPct”和“ComAmt”列的数据。D2:E7=DeptSales#Headers,Region:ComAmt仅“Region”和“ComPct ComAmt”之间的列的标题。B1:E1=DeptSales#Totals,SaleAmt:ComAmt从“SaleAmt”到“ComAmt”列的汇总。如果不存在汇总行，将返回 null。 C8:E8=DeptSales#Headers,#Data,ComPct仅“ComPct”列的标题和数据。D1:D7=DeptSales#This Row, ComAmt位于当前行和“ComAmt”列的相交部分的单元格。E5（如果当前行为第 5 行）返回页首使用结构化引用使用结构化引用时，请考虑以下几点。 使用“公式记忆式键入”在输入结构化引用时，您可能会发现使用公式记忆式键入非常有用，因为它可以确保使用了正确的语法。有关详细信息，请参阅使用“公式记忆式键入”。 确定是否要在进行部分选择时生成表格的结构化引用默认情况下，当您创建公式时，单击表格中的某一单元格区域将对单元格进行部分选择并在公式中自动输入结构化引用，而不是输入该单元格区域。此部分选择行为使输入结构化引用更加容易。通过选中或清除“在公式中使用表名”复选框（位于“Excel 选项”对话框中“公式”类别的“使用公式”部分中）可以打开或关闭此行为。在区域和表格之间进行转换将表格转换为区域时，所有单元格引用都将更改为它们的等效 A1 样式引用。将区域转换为表格时，Excel 不会自动将对此区域的任何单元格引用更改为它们的等效表名称和列引用。关闭列标题如果您关闭表格列标题（在表格“设计”选项卡上的“表格样式选项”组中，清除“标题行”），将不影响使用这些标题的结构化引用，您仍可以在公式中使用它们。在表格中添加或删除列和行由于表格数据区域经常变化，结构化引用的单元格引用也自动随之调整。例如，如果在公式中使用表名称来对“部门销售”表格中的所有数据单元格进行计数（例如，在“部门销售”表格示例中为 =COUNTA(DeptSales)），则返回的值为 30，因为数据区域为 A2:E7。然后，如果您添加一行数据，则单元格引用将自动调整为 A2:E8，新返回的值为 35。重命名表格或列如果重命名列或表格，Excel 会自动在工作簿中使用的所有结构化引用中更改该表格和列标题的使用。移动、复制和填充结构化引用复制或移动使用结构化引用的公式时，所有结构化引用都将保持不变。 填充公式时，完全限定的结构化引用可以调整列说明符，就像一个系列一样。下表对此进行了简要介绍。如果填充方向为：并且在填充时按：则：向上或向下无不调整列说明符。向上或向下Ctrl像一个系列一样调整列说明符。向右或向左无像一个系列一样调整列说明符。向右或向左Ctrl不调整列说明符。向上、向下、向右或向左Shift移走当前单元格的值并插入列说明符，而不是覆盖当前单元格中的值。返回页首结构化引用语法规则以下是在创建和编辑结构化引用时需要了解的语法规则列表。注释表名称与定义的名称遵循相同的规则。有关详细信息，请参阅在公式中定义和使用名称。方括号在说明符中的用法所有表格、列和特殊项目说明符都必须使用一对方括号 ( ) 括起。对于包含其他说明符的说明符，需要在其他说明符的内方括号对的外侧使用方括号对括起。示例=DeptSalesSalesPers:Region列标题为文本字符串所有列标题都是文本字符串，但在结构化引用中使用列标题时，不需要使用引号。如果列标题包含数字或日期，例如，2004 或 1/1/2004，会仍将它们视为文本字符串。由于列标题是文本字符串，因此不能在方括号内使用表达式。示例=DeptSalesFYSummary2004:2002表格列标题中的特殊字符如果表格列标题包含以下特殊字符之一，则必须用方括号将整个列标题括起。实际上，这意味着，在包含以下特殊字符的列说明符中需要使用双重方括号：空格、制表符、换行、回车、逗号 (,)、冒号 (:)、句点 (.)、左方括号 ()、右方括号 ()、井号 (#)、单引号 ()、双引号 ()、左大括号 ()、右大括号 ()、美元符号 ($)、异或号 ()、与号 (&)、星号 (*)、加号 (+)、等号 (=)、减号 (-)、大于号 ()、小于号 () 和除号 (/)。示例=DeptSalesFYSummaryTotal$Amount此规则的唯一例外是，当使用的唯一特殊字符为空格字符时。示例=DeptSalesTotal Amount列标题中需要使用转义字符的特殊字符以下字符具有特殊的含义，需要使用单引号 () 作为转义字符：左方括号