对 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. Enter

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

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

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

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

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

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

如何更改表名称?

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

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

  2. 键入要在表名称框中的名称,然后按Enter

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

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

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

  • 不使用单元格引用 名称不能与单元格引用,例如 Z$ 100 或 R1C1。

  • 不使用空格分隔的单词 在名称中不能使用空格。您可以使用下划线 (_) 和句点 (.) 作为 word 分隔符。例如,部门销售、 Sales_Tax 或 First.Quarter。

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

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

  • 使用对象标识符 如果您计划在混用不同的表、 数据透视表和图表,最好您名前缀与对象类型。例如: tbl_Sales sales 表、 销售数据透视表,pt_Sales 和 chrt_Sales 的销售图表或 ptchrt_Sales 销售数据透视图的。这将保留您的所有名称在名称管理器中的排序列表。

结构化引用语法规则

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

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

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

  • 表名称:   部门销售是自定义表名称。它引用表数据时,不带任何页眉或总计行。您可以使用默认的表名称,如 Table1,或将它更改为使用自定义的名称。

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

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

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

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

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

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

  • 所有列标题都为文本字符串    但它们用于结构化引用中时不需要使用引号。 数字或日期,例如 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 表格式
调整通过添加或删除行和列的表格大小
筛选区域或表中的数据
将表格转换为区域
Excel 表兼容性问题
导出 Excel 表格转换成 SharePoint
中的公式的概述Excel

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

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

此信息是否有帮助?

谢谢您的反馈!

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

×