对 Excel 表格使用结构化引用

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

当您创建 excel 表格时, Excel 会为表格和表格中的每个列标题分配一个名称。当您将公式添加到 Excel 表时, 这些名称可以在输入公式时自动显示, 并在表格中选择单元格引用, 而不是手动输入。下面是 Excel 功能的一个示例:

Excel 不使用显式单元格引用,

而使用表格和列名称

=Sum(C2:C7)

=SUM(部门销售[销售额])

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

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

要在您的公式中包含结构化引用,请单击要引用的单元格,而不必在公式中键入其单元格引用。 让我们使用以下示例数据输入一个公式,该公式自动使用结构化引用计算销售佣金金额。

销售 人员

地区

销售

佣金比率

佣金金额

彭德威

北部

260

10%

何石

南部

660

15%

柏隼

东部

940

15%

孔西明

西部

410

12%

康霓

北部

800

15%

张伟

南部

900

15%

  1. 复制上表中的示例数据 (包括列标题), 并将其粘贴到新 Excel 工作表的单元格 A1 中。

  2. 若要创建表, 请选择数据区域内的任意单元格, 然后按Ctrl + T

  3. 请确保选中 "表包含标题" 框, 然后单击"确定"

  4. 在单元格 E2 中,键入一个等号 (=),并单击单元格 C2。

    在编辑栏中,结构化引用 [@[销售金额]] 出现在等号后。

  5. 在右方括号之后直接键入星号 (*), 然后单击单元格 D2。

    在编辑栏中,结构化引用 [@[佣金比率]] 出现在星号之后。

  6. 输入

    Excel 会自动为你创建一个计算列并将公式向下复制到整列,同时调整每一行。

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

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

  1. 在示例工作表中, 单击单元格 E2

  2. 在编辑栏中, 输入= C2 * D2 , 然后按enter

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

如何更改表名称?

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

  1. 选择表格中的任意单元格以在功能区上显示 "表格工具>设计" 选项卡。

  2. 在 "表名称" 框中键入所需的名称, 然后按enter

在示例数据中,我们使用名称“部门销售”

对于表名称,使用以下规则:

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

  • 不要使用单元格引用 名称不能与单元格引用 (如 Z $ 100 或 R1C1) 相同。

  • 不要使用空格分隔单词 名称中不能使用空格。您可以将下划线字符 (_) 和句点 (.) 用作单词分隔符。例如, "部门销售"、"Sales_Tax" 或 "第一季度"。

  • 使用的字符数不超过 255 个 表名称最长可以包含 255 个字符。

  • 使用唯一的表名称 不允许重复的名称。 Excel 对名称中的字符并不区分大小写,因此如果您输入“Sales”,但同一个工作簿中已经有另一个名称为“SALES”,您就会收到选择一个唯一名称的提示。

  • 使用对象标识符 如果你计划混合使用表格、数据透视表和图表, 最好使用对象类型为你的姓名加上前缀。例如: sales 表的 tbl_Sales、用于销售数据透视表的 pt_Sales, 以及销售图表的 chrt_Sales 或用于销售数据透视图的 ptchrt_Sales。这会将你的所有名称保存在名称管理器中的排序列表中。

结构化引用语法规则

你也可以在公式中手动输入或更改结构化引用, 但若要执行此操作, 它将有助于了解结构化引用语法。让我们看一下下面的公式示例:

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

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

  • 表名称:   部门销售是一个自定义表名称。它引用表数据, 不包含任何标头或汇总行。你可以使用默认表名称 (如 Table1) 或将其更改为使用自定义名称。

  • 列说明符:   [销售金额][佣金金额] 是使用它们所表示的列的名称的列说明符。它们引用列数据, 不包含任何列标题或汇总行。始终将说明符括在括号中, 如图所示。

  • 项目说明符:   [#Totals][#Data]是引用表的特定部分 (如汇总行) 的特殊项目说明符。

  • 表说明符:   [[#汇总],[销售额]][[#数据],[佣金金额]] 是表示结构化引用外层部分的表说明符。 外部参照跟在表名称之后,并括在方括号中。

  • 结构化引用:   (部门销售 [[#Totals], [销售额]]部门销售 [[#Data], [佣金金额]]是结构化引用, 由以表名称开头并以列说明符结尾的字符串表示。

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

  • 使用方括号括起说明符   所有表格、列和特殊项目说明符都需要括在匹配的方括号 ([]) 中。包含其他说明符的说明符需要外部匹配的括号来括住其他说明符的内部匹配括号。例如: = 部门销售 [[销售人员]: [Region]]

  • 所有列标题都为文本字符串    但它们用于结构化引用中时不需要使用引号。 数字或日期,例如 2014 或 2014/1/1,也被视为文本字符串。 不能对列标题使用表达式。 例如,表达式 DeptSalesFYSummary[[2014]:[2012]] 将不起作用。

用方括号将包含特殊字符的列标题括起来    如果包含特殊字符,整个列标题就需要括在括号中,这意味着列说明符中需要使用双重括号。例如:=DeptSalesFYSummary[[Total $ Amount]]

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

  • Tab

  • 换行符

  • 回车符

  • 逗号(,)

  • 冒号(:)

  • 句号 (.)

  • 左中括号 ([)

  • 右中括号 (])

  • 井号 (#)

  • 单引号 (')

  • 双引号 (")

  • 左大括号 ({)

  • 右大括号 (})

  • 美元符号 ($)

  • 脱字符号 (^)

  • 与号 (&)

  • 星号(*)

  • 加号 (+)

  • 等于号 (=)

  • 减号 (-)

  • 大于符号 (>)

  • 小于符号 (<)

  • 除号 (/)

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

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

  • 左中括号 ([)

  • 右中括号 (])

  • 井号 (#)

  • 单引号 (')

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

建议在以下位置使用一个空格:

  • 在第一个左中括号 ([) 之后;

  • 在最后一个右中括号 (]) 之前;

  • 在逗号之后。

引用运算符

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

以下结构化引用:

引用:

通过使用:

单元格区域:

=部门销售[[销售人员]:[区域]]

两个或更多个相邻列中的所有单元格

:(冒号)区域运算符

A2:B7

=部门销售[销售额],部门销售[佣金金额]

两个或更多个列的组合

,(逗号)联合运算符

C2:C7, E2:E7

=部门销售[[销售人员]:[销售额]] 部门销售[[区域]:[佣金比率]]

两个或更多个列的交集

(空格)交叉运算符

B2:C7

特殊项目说明符

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

以下特殊项目说明符:

引用:

#All

整个表格,包括列标题、数据和汇总(如果有)。

#数据

仅数据行。

#页眉

仅标题行。

#总计

仅汇总行。如果此行不存在,它将返回 Null。

#This Row

或者

@

或者

@[列名]

仅与公式位于同一行的单元格。 这些说明符不能与任何其他特殊项目说明符组合。 它们可以用于强制执行引用的隐式交叉行为,或替代隐式交叉行为并引用列中的单个值。

在包含多行数据的表格中,Excel 会自动将 #This Row 说明符更改为更短的 @ 说明符形式。 但如果您的表格中仅有一行,Excel 不会替换 #This Row 说明符,在您添加更多行后,这可能导致意外计算结果。 为了避免计算问题,请确保在输入结构化引用公式之前在表格中输入多行。

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

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

结构化引用的类型

示例

批注

非限定

=[销售额]*[佣金比率]

乘以当前行中的相应值。

完全限定

=部门销售[销售额]*部门销售[佣金比率]

为两个列的每一行都乘以相应值。

应遵循的一般规则为:如果在表格内使用结构化引用(例如在创建计算列时使用),则可以使用非限定的结构化引用,但如果在表格之外使用结构化引用,则需要使用完全限定的结构化引用。

使用结构化引用的示例

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

以下结构化引用:

引用:

单元格区域:

=部门销售[[#全部],[销售额]]

“销售额”列中的所有单元格。

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 不会自动将对此区域的任何单元格引用更改为它们的等效结构化引用。

  • 关闭列标题   您可以从 "表设计" 选项卡的 >标题行切换表格列标题。如果关闭表格列标题, 使用列名称的结构化引用不受影响, 你仍然可以在公式中使用它们。直接引用表标题的结构化引用 (例如= 部门销售 [[#Headers], [% 佣金]]) 将导致#REF

  • 在表中添加或删除列和行   由于表数据区域经常更改, 结构化引用的单元格引用将自动进行调整。例如, 如果在公式中使用表名对一个表中的所有数据单元格进行计数, 然后添加一行数据, 则单元格引用将自动调整。

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

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

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

如果填充方向为:

在填充时, 按:

则:

向上或向下

不调整列说明符。

向上或向下

Ctrl

像一个系列一样调整列说明符。

向右或向左

像一个系列一样调整列说明符。

向上、向下、向右或向左

Shift

移走当前单元格的值并插入列说明符,而不是覆盖当前单元格中的值。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

相关主题

excel 表格
概述视频: 创建
excel 表格中的数据并设置其格式通过添加或删除行和列


筛选区域或表
中的数据将表转换为区域
Excel 表格兼容性问题
excel 表格导出到
中公式的 SharePoint 概述Excel

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×