对 Excel 表格使用结构化引用

创建 Excel 表格时，Excel 将为表格及表格中的每个列标题指定名称。 当你将公式添加到 Excel 表格时，这些名称会在输入公式时自动显示并选择表格中相应的单元格引用，而不必手动输入。 以下是 Excel 功能示例：

Excel 不使用显式单元格引用，	而使用表格和列名称
=Sum(C2:C7)	=SUM(部门销售[销售额])

这些表格和列名称的组合称为结构化引用。 因为每当添加或删除表中的数据时，结构化引用中的名称会进行调整。

当您的 Excel 表格之外创建一个引用表格数据的公式时，也会显示结构化引用。 引用可更易于在大型工作簿中定位表格。

若要在公式中包含结构化引用，请选择要引用的表单元格，而不是在公式中键入单元格引用。 让我们使用以下示例数据输入一个公式，该公式自动使用结构化引用来计算销售佣金金额。

销售人员	地区	销售额	佣金比率	佣金金额
彭德威	北部	260	10%
Robert	南部	660	15%
柏隼	东部	940	15%
孔西明	西部	410	12%
康霓	北部	800	15%
Rob	南部	900	15%

1. 复制上表中的示例数据（包括列标题），然后将其粘贴到新 Excel 工作表的单元格 A1 中。
2. 若要创建表格，请选择数据区域内的任意单元格，然后按 Ctrl+T。
3. 确保选中 “我的表包含标题 ”框，然后选择“ 确定”。
4. 在单元格 E2 中，键入等号 (=) ，然后选择单元格 C2。
   在编辑栏中，结构化引用 [@[销售金额]] 出现在等号后。
5. 直接在右括号后面键入星号 (*) ，然后选择单元格 D2。
   在编辑栏中，结构化引用 [@[佣金比率]] 出现在星号之后。
6. 按 Enter。
   Excel 会自动为你创建一个计算列并将公式向下复制到整列，同时调整每一行。

当我使用显式单元格引用，会发生什么情况?

如果您在计算列中输入显式单元格引用，将很难看到正在计算的公式。

1. 在示例工作表中，选择单元格 E2
2. 在公式栏中输入 =C2*D2，然后按 Enter。

注意，当将公式向下复制到整列时，Excel 不使用结构化引用。 例如，如果您在现有列 C 和 D 之间添加一列，则要对公式进行修订。

如何更改表名称？

当您创建 Excel 表格时，Excel 会创建默认的表名称（Table1、Table2 等），但您可以更改表名称使其更有意义。

1. 选择表格中的任何单元格，以显示功能区上的“ 表格设计 ”选项卡。
2. 在“表名称”框中，键入所需的名称，然后按 Enter。

在示例数据中，我们使用了名称 DeptSales。

对于表名称，使用以下规则：

• 使用有效字符 始终以字母、下划线字符 (_) 或反斜杠 (\) 开头。 在名称的其余部分中，可使用字母、数字、句点和下划线字符。 不能使用“C”、“c”、“R”或“r”作为名称，因为在“ 名称 ”或“ 转到 ”框中输入列或行时，它们已被指定为选择活动单元格的列或行的快捷方式。

• 不要使用单元格引用 名称不能与单元格引用相同，例如 Z$100 或 R1C1。
• 不要使用空格来分隔单词 不能在名称中使用空格。 可以使用下划线字符 (_) 和句点 (.) 作为单词分隔符。 例如：DeptSales、Sales_Tax 或 First.Quarter。
• 使用的字符数不超过 255 个 表名称最长可以包含 255 个字符。
• 使用唯一的表名 不允许重复的名称。 Excel 不会区分名称中的大写和小写字符，因此，如果输入“Sales”，但同一工作簿中已有另一个名为“SALES”的名称，系统将提示你选择唯一名称。
• 使用对象标识符 如果计划混合使用表、数据透视表和图表，最好在名称前面加上对象类型。 例如：tbl_Sales 代表销售表，pt_Sales 代表销售数据透视表，chrt_Sales 代表销售图表，ptchrt_Sales 代表销售数据透视图。 这会将你的所有名称保存在名称管理器中的排序列表中。

结构化引用语法规则

你也可以在公式中手动输入或更改结构化引用，但要执行此操作，了解结构化引用语法会比较有帮助。 我们来看看下面的公式示例：

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

此公式具有以下结构化引用组成部分：

• **表名称：**DeptSales 是自定义表名。 它引用表数据（不包含任何标题或汇总行）。 您可以使用默认的表名称，如 Table1，也可更改为使用自定义名称。
• 列说明符：[销售金额]和[佣金金额]是使用列表示的列名称的列说明符。 它们引用列数据（不包含任何列标题或汇总行）。 始终将说明符用所示的方括号括起来。
• 项目说明符：[#汇总] 和 [#数据] 是引用表的特定部分（如汇总行）的特殊项目说明符。
• 表说明符：[[#汇总],[销售额]] 和 [[#数据],[佣金金额]] 是表示结构化引用外层部分的表说明符。 外部参照跟在表名称之后，并括在方括号中。
• 结构化引用：部门销售[[#汇总],[销售额]] 和 部门销售[[#数据],佣金金额]] 是结构化引用，使用以表名称开头、以列说明符结尾的字符串表示。

若要手动创建或编辑结构化引用，请使用以下语法规则：

• 在说明符周围使用括号 所有表、列和特殊项说明符都需要括在匹配的括号 ([ ]) 中。 对于包含其他说明符的说明符，需要在其他说明符的内方括号对的外侧使用方括号对括起。 例如，=部门销售[[销售人员]:[区域]]
• 所有列标题都是文本字符串 但是，在结构化引用中使用时，它们不需要引号。 数字或日期，例如 2014 或 2014/1/1，也被视为文本字符串。 不能对列标题使用表达式。 例如，表达式 DeptSalesFYSummary[[2014]：[2012]] 将不起作用。

使用带有特殊字符的列标题周围的括号 如果有特殊字符，则需要将整个列标题括在方括号中，这意味着列说明符中需要双括号。 例如：=DeptSalesFYSummary[[Total $ Amount]]

下面是公式中需要额外括号的特殊字符的列表：

• Tab
• 换行符
• 回车符
• 逗号（,）
• 冒号（:）
• 句号 (.)
• 左中括号 ([)
• 右中括号 (])
• 井号 (#)
• 单引号 (')
• 双引号 (")
• 左大括号 ({)
• 右大括号 (})
• 美元符号 ($)
• 脱字符号 (^)
• 与号 (&)
• 星号（*）
• 加号 (+)
• 等于号 (=)
• 减号 (-)
• 大于符号 (>)
• 小于符号 (<)
• 除号 (/)
• at sign (@)
• 反斜杠 (\)
• 感叹号 (！)
• 左括号 ( ()
• 右括号 () )
• 百分号 (%)
• 问号 (?)
• 反引号 (')
• 分号 (;)
• 波形符 (~)
• 下划线 (_)

• 对列标题中的某些特殊字符使用转义字符 某些字符具有特殊含义，需要使用单引号 ( ) 作为转义字符。 例如：=DeptSalesFYSummary['#OfItems]

下面是公式中需要转义字符 ( ) 的特殊字符的列表：

• 左中括号 ([)
• 右中括号 (])
• 井号 (#)
• 单引号 (')
• at sign (@)

使用空格字符提高结构化引用中的可读性 可以使用空格字符来提高结构化引用的可读性。 例如：=部门销售[[销售人员]:[区域]] 或 =部门销售[[#标题],[#数据],[佣金比率]]

建议使用一个空格：

• 在第一个左中括号 ([) 之后；
• 在最后一个右中括号 (]) 之前；
• 在逗号之后。

引用运算符

为了在指定单元格区域时增加灵活性，可以使用以下引用运算符来组合列说明符。

以下结构化引用：	引用：	通过使用：	单元格区域：
=部门销售[[销售人员]:[区域]]	两个或更多个相邻列中的所有单元格	:（冒号）区域运算符	A2:B7
=部门销售[销售额],部门销售[佣金金额]	两个或更多个列的组合	，（逗号）联合运算符	C2:C7， E2:E7
=部门销售[[销售人员]:[销售额]] 部门销售[[区域]:[佣金比率]]	两个或更多个列的交集	（空格）交叉运算符	B2:C7

特殊项目说明符

要引用表格的特定部分（例如只引用汇总行），可以在结构化引用中使用下列任意特殊项目说明符。

以下特殊项目说明符：	引用：
#All	整个表格，包括列标题、数据和汇总（如果有）。
#数据	仅数据行。
#页眉	仅标题行。
#总计	仅汇总行。 如果此行不存在，它将返回 Null。
#This Row 或者 @ 或者 @[列名]	仅与公式位于同一行的单元格。 这些说明符不能与任何其他特殊项说明符组合使用。 它们可以用于强制执行引用的隐式交叉行为，或替代隐式交叉行为并引用列中的单个值。 在包含多行数据的表格中，Excel 会自动将 #This Row 说明符更改为更短的 @ 说明符形式。 但是，如果表中只有一行，Excel 不会替换 #This 行说明符，这可能会导致添加更多行时出现意外的计算结果。 为了避免计算问题，请确保在输入结构化引用公式之前在表格中输入多行。

计算列中的限定结构化引用

创建计算列时，通常使用结构化引用来创建公式。 此结构化引用可以是非限定的，也可以是完全限定的。 例如，要创建一个名为“佣金金额”的计算列来计算以人民币表示的佣金额，可以使用以下公式：

结构化引用的类型	示例	批注
非限定	=[销售额]*[佣金比率]	乘以当前行中的相应值。
完全限定	=部门销售[销售额]*部门销售[佣金比率]	为两个列的每一行都乘以相应值。

要遵循的一般规则是：如果在表中使用结构化引用（例如创建计算列时），则可以使用非限定的结构化引用，但如果在表外部使用结构化引用，则需要使用完全限定的结构化引用。

使用结构化引用的示例

以下是使用结构化引用的一些方法。

以下结构化引用：	引用：	单元格区域：
=部门销售[[#全部],[销售额]]	“销售额”列中的所有单元格。	C1:C8
=部门销售[[#标题],[%佣金比率]]	“佣金比率”列的标题。	D1
=DeptSales[[#Totals],[Region]]	“Region”列的汇总。 如果不存在汇总行，将返回 Null。	B8
=部门销售[[#全部],[销售额]:[佣金比率]]	“销售额”和“佣金比率”中的所有单元格。	C1:D8
=部门销售[[#数据],[佣金比率]:[佣金金额]]	“佣金比率”和“佣金金额”列的数据。	D2:E7
=部门销售[[#标题],[区域]:[佣金金额]]	仅“区域”与“佣金金额”之间的列标题。	B1:E1
=部门销售[[#汇总],[销售额]:[佣金金额]]	从“销售额”到“佣金金额”列的汇总。 如果无汇总行，则返回 Null。	C8:E8
=部门销售[[#标题],[#数据],[佣金比率]]	仅“佣金比率”列的标题和数据。	D1:D7
=部门销售[[#This Row], [佣金金额]] 或者 =部门销售[@佣金金额]	位于当前行和“佣金金额”列的相交部分的单元格。 如果在与标题或汇总行相同的行中使用，将返回 #VALUE! 错误。 如果在包含多行数据的表格中键入此结构化引用的较长形式 (#This Row)，Excel 会自动将其替换为短形式 (@)。 两者的效果完全相同。	E5（如果当前行为第 5 行）

使用结构化引用的策略

使用结构化引用时，请考虑以下几点。

• 使用“公式记忆式键入” 在输入结构化引用时，你可能会发现使用公式记忆式键入功能非常有用，因为它可以确保使用了正确的语法。 如需了解详细信息，请参阅使用公式记忆式键入。

• 确定是否为半选定内容中的表生成结构化引用 默认情况下，创建公式时，选择表格中的单元格区域将半选择单元格，并自动在公式中输入结构化引用而不是单元格区域。 此部分选择行为使输入结构化引用更加容易。 可以通过在“文件>选项>>公式使用公式”对话框中选择或清除“在公式中使用表名检查”框来打开或关闭此行为。

• 使用具有指向其他工作簿中 Excel 表的外部链接的工作簿 如果工作簿包含指向另一个工作簿中 Excel 表的外部链接，则必须在 Excel 中打开该链接的源工作簿，以避免包含链接的目标工作簿出现 #REF！ 错误。 如果你先打开目标工作簿，并出现 #REF! 错误，那么随后打开源工作簿将解决此问题。 如果您先打开源工作簿，则应该不会看到错误代码。

• 将区域转换为表，将表转换为区域 将表转换为区域时，所有单元格引用将更改为其等效的绝对 A1 样式引用。 将区域转换为表格时，Excel 不会自动将此区域的任何单元格引用更改为其等效的结构化引用。

• 关闭列标题可以从“表格设计”选项卡“>标题行”中打开和关闭表列标题。 如果关闭表列标题，则使用列名称的结构化引用不受影响，你仍然可以在公式中使用它们。 直接引用表标题的结构化引用（如 =部门销售[[#标题],[佣金比率]]）将导致 #REF。

• 向表添加或删除列和行 由于表数据范围经常更改，因此结构化引用的单元格引用会自动调整。 例如，如果您在公式中使用表格名称对表格中的所有数据单元格进行计数并添加一行数据，则单元格引用会自动调整。

• 重命名表格或列 如果重命名列或表格，Excel 会自动在工作簿中使用的所有结构化引用中更改该表格和列标题的使用。

• 移动、复制和填充结构化引用 当复制或移动使用结构化引用的公式时，所有结构化引用将保持不变。

  注意

  复制结构化的引用和执行结构化引用填充不是一回事。 复制时，所有结构化引用将保持不变，而填充公式时，完全限定的结构化引用可以调整列说明符，就像一个系列一样。下表对此进行了简要介绍。

如果填充方向为：	在填充时，按：	则：
向上或向下	无任何操作	不调整列说明符。
向上或向下	Ctrl	像一个系列一样调整列说明符。
向右或向左	无	像一个系列一样调整列说明符。
向上、向下、向右或向左	Shift	移动当前单元格值并插入列说明符，而不是覆盖当前单元格中的值。

需要更多帮助吗？

你随时可以在 Excel 技术社区 中咨询专家或在 社区中获取支持。

相关主题

Excel 表概述
创建表以及设置表格式
汇总 Excel 表格中的数据
设置 Excel 表格的格式
通过添加或删除行和列来调整表格的大小
筛选区域或表格中的数据
将表格转换为区域
Excel 表格兼容性问题
将 Excel 表格导出到 SharePoint
Excel 中公式的概述