excel怎么创建外部引用

excel外部引用技巧，如何创建外部引用

可以通过创建外部引用公式来引用另一个工作簿中的单元格内容。 外部引用（也称为链接）是对另一个 Excel 工作簿中的工作表上的单元格或区域的引用，或对另一个工作簿中的已定义名称的引用。
-----------------------------------------------------------------------------------------------------------------------
在不同工作簿中的单元格之间创建外部引用  
打开将包含外部引用（目标工作簿）的工作簿，以及包含要链接到的数据的工作簿（源工作簿）。
选择要在其中创建外部引用的一个或多个单元格。
键入= （等号）。
如果要使用函数（如 SUM），请键入函数名称，后跟一个左括号。 例如， = SUM （。切换到源工作簿，然后单击包含要链接的单元格的工作表。
选择要链接到的一个或多个单元格，然后按enter。
注意: 如果您选择多个单元格，例如 = [SourceWorkbook] Sheet1！ $A $1： $A $10，并且拥有最新版本的Microsoft 365，则只需按enter将公式确认为动态数组公式即可。 否则，必须按CTRL + SHIFT + ENTER将公式输入为旧数组公式。 有关数组公式的详细信息，请参阅数组公式指南和示例。
Excel 将返回到目标工作簿，并显示源工作簿中的值。
请注意，Excel 将返回具有绝对引用的链接，因此，如果要将公式复制到其他单元格，则需要删除美元符号（$）：
= [SourceWorkbook] Sheet1！
$1
$1
如果关闭源工作簿，Excel 会自动将文件路径追加到公式：
= "C:\Reports\ [SourceWorkbook] Sheet1"！ $A $1
-----------------------------------------------------------------------------------------------------------------------
在另一个工作簿中创建对已定义名称的外部引用  
打开将包含外部引用（目标工作簿）的工作簿，以及包含要链接到的数据的工作簿（源工作簿）。
选择要在其中创建外部引用的一个或多个单元格。
键入= （等号）。
切换到源工作簿，然后单击包含要链接的单元格的工作表。
按F3，选择要链接到的名称，然后按enter。
注意: 如果命名区域引用多个单元格，并且您有一个最新版本的Microsoft 365，则只需按enter将公式确认为动态数组公式即可。 否则，必须按CTRL + SHIFT + ENTER将公式输入为旧数组公式。 有关数组公式的详细信息，请参阅数组公式指南和示例。
Excel 将返回到目标工作簿，并在源工作簿中显示命名区域中的值。
 -----------------------------------------------------------------------------------------------------------------------
定义一个名称，该名称包含对另一个工作簿中的单元格的外部引用  
打开目标工作簿和源工作簿。
在目标工作簿中，转到 "公式" >定义的名称> "定义名称"。

在 "新名称" 对话框的 "名称" 框中，键入区域的名称。
在 "引用位置" 框中，删除内容，然后将光标放在框中。
如果希望名称使用函数，请输入函数名称，然后将光标放在要放置外部引用的位置。 例如，键入= SUM （），然后将光标置于括号之间。
切换到源工作簿，然后单击包含要链接的单元格的工作表。
选择要链接的单元格或单元格区域，然后单击"确定"。

-----------------------------------------------------------------------------------------------------------------------
可在哪里有效使用外部引用  
当在同一工作簿中将大型工作表模型放在不可行时，外部引用尤其有用。
合并多个工作簿中的数据 ???您可以链接多个用户或部门的工作簿，然后将相关数据集成到一个摘要工作簿中。 这样，当源工作簿发生更改时，您无需手动更改摘要工作簿。
创建数据的不同视图 您可以将所有数据输入到一个或多个源工作簿中，然后创建仅包含相关数据的外部引用的报表工作簿。
优化大型复杂模型 通过将复杂模型分解为一系列相互依赖的工作簿，你可以处理模型，而无需打开其所有相关工作表。 较小的工作簿更易于更改，无需占用大量内存，并且打开、保存和计算的速度更快。
-----------------------------------------------------------------------------------------------------------------------
对另一个工作簿的外部引用的外观  
对其他工作簿的外部引用的公式以两种方式显示，具体取决于源工作簿（为公式提供数据的源工作簿）是打开还是关闭。
当源处于打开状态时，外部引用包括工作簿名称（方括号（[]）），后跟工作表名称、感叹号（！）和公式所依赖的单元格。 例如，下面的公式将从名为 "C25" 的工作簿中添加单元格 C10：。
外部引用
= SUM （[预算 .xlsx] 年度！C10： C25）
当源未打开时，外部引用包含整个路径。
外部引用
= SUM （' C:\Reports\ [[[[.xlsx] 年度 "！C10： C25）
注意: 如果其他工作表或工作簿的名称中包含空格或非字母字符，则必须用单引号（或路径）将名称（或路径）括在单引号中，就像在上述示例中所示。 选择源区域时，Excel 会自动为您添加这些源区域。
链接到另一个工作簿中的已定义名称的公式使用工作簿名称，后跟感叹号（！）和名称。 例如，下面的公式将从名为 ".xlsx" 的工作簿中的 "销售额" 区域添加单元格。
外部引用
= SUM （预算为 .xlsx！卖
-----------------------------------------------------------------------------------------------------------------------
创建指向其他工作表的链接  
选择要在其中创建外部引用的一个或多个单元格。
键入= （等号）。

如果要使用函数（如 SUM），请键入函数名称，后跟一个左括号。 例如， = SUM 

切换到包含要链接到的单元格的工作表。

选择要链接到的一个或多个单元格，然后按enter。
注意: 如果您选择多个单元格（= Sheet1！A1： A10），并且具有Microsoft 365的最新版本，您只需按enter将公式确认为动态数组公式即可。 否则，必须按CTRL + SHIFT + ENTER将公式输入为旧数组公式。 有关数组公式的详细信息，请参阅数组公式指南和示例。
Excel 将返回到原始工作表，并显示源工作表中的值。   

excel如何在公式中直接引用单元格数据

当创建简单公式，或通过的公式使用了函数时，您可以通过在公式参数中包含单元格引用来引用工作表单元格中的数据。例如，当您输入或选择的单元格引用A2，公式将使用该单元格的值来计算结果。您也可以引用单元格区域。

有关单元格引用的详细信息，请参阅创建或更改单元格引用。

单击要在其中输入公式的单元格。

在编辑栏 中，键入= （等号）。

执行以下操作之一，选择包含所需值的单元格或键入其单元格引用。

可引用单个单元格、单元格区域、另一个工作表或工作簿中的相应位置。

当选择单元格区域时，可以拖动所选单元格的边框来移动选定区域，或者拖动边框上的角来扩展选定区域。



1. 第一个单元格引用为 B3，颜色为蓝色，单元格区域有方角的蓝色边框。

2. 第二个单元格引用为 C3，颜色为绿色，单元格区域有方角的绿色边框。

注意: 如果用颜色标记的边框没有方角，则引用的是命名区域。
  
按 Enter。

提示: 您也可以输入已命名的单元格或区域的引用。
  

示例

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。要使公式显示结果，请选中它们，按 F2，然后按 Enter。如果需要，可调整列宽以查看所有数据。使用“定义名称”命令（“公式”选项卡、“已定义名称”组中）定义“资产”(B2:B4) 和“负债”(C2:C4)。

部门	资产	负债
IT 部	274000	71000
管理员	67000	18000
人力资源	44000	3000
公式	说明	结果
'=SUM(资产)	在已定义名称“资产”中返回三个部门的资产总计，定义为单元格区域 B2:B4。(385000)	=SUM(资产)
'=SUM(资产)-SUM(负债)	从已定义名称“资产”的总和中减去已定义名称“负债”的总和。(293000)	=SUM(资产)-SUM(负债)

excel怎么引用单元格，使用追踪引用单元格技巧

在工作中常常需要处理各种各样的数据表格，需要查看表格数据之间的关系时，通常会双击单元格，被引用的区域会被标记出来，但是这样容易造成对公式的误编辑。

使用WPS表格“追踪引用单元格”功能，可以追踪被此单元格引用区域。

具体方法与步骤如下：

以此表格为例，假若我们想知道员工A“Q2季度销售额”E2单元格引用过哪些数据。

将鼠标放在E2，点击上方菜单栏公式-追踪引用单元格，此时表格中出现箭头。

我们便可得知E2的数据是引用了D2和E9的数据。

要是我们想去除箭头，点击上方菜单栏公式-移去箭头-移去引用单元格追踪箭头。

这个实用的功能你学会了吗？

excel怎么将一个工作表内容引用到另一个工作表

使用表格处理数据时，常涉及跨表引用的情况，

使用函数引用其他工作簿数据时，要求被引用的工作簿必须同时打开，否则就会出错。

WPS表格中的导入数据功能可以解决这个烦恼。

接下来操作帮助大家理解。

具体方法与步骤如下：

此处有两个工作簿，现在要将两个表的部分数据进行汇总。

新建一个空白工作簿，点击菜单栏“数据”-“导入数据”。

在弹出的对话框中我们可见有四种连接数据源方式：

直接打开数据文件、ODBC DSN、其他高级和手工输入连接语句。

此处我们选择直接打开数据文件。

点击选择数据源，在弹出的对话框中我们选择打开数据源，可见有表名、可用字段等。

选择所需字段，点击下一步，此时可以选择排序方式和筛选功能。

选择继续下一步，在第四步中我们可以预览表单。

点击完成，就可以将外部数据表导入到此表格中了。

若无法导入外部工作簿数据，可能是未安装ACCESS2010数据引擎，我们从网上下载安装即可。

如果我们更改数据源表格，怎么样同步更新呢？

回到引用表中，点击全部刷新，导入数据源。

此时就可以更新表格中的数据源了！

导入外部数据既可以节省时间，又快捷安全，快快学起来吧~

excel怎么引用其他单元格数据

创建引用其他单元格中的值的公式

选择单元格。

键入等号“=”。

注意: Excel 中的公式始终以等号开头。
  
选择一个单元格，或在所选单元格中键入其地址。



输入运算符。 例如，- 代表相减。

选择下一单元格，或在所选单元格中键入其地址。



按 Enter。 计算结果将显示在包含公式的单元格中。

查看公式

在单元格中输入公式时，该公式还会出现在编辑栏中。

要查看公式，请选择一个单元格，该单元格会出现在编辑栏中。


输入包含内置函数的公式

选择一个空单元格。

键入一个等号“=”，然后键入函数。 例如，用“=SUM”计算销售总额。

键入左括号“(”。

选择单元格区域，然后键入右括号“)”。



按 Enter 获取结果。
----------------------------------------------------------------------------------------------------------------------
Excel 公式的组成部分

公式还可包含下列所有内容或其中之一：函数、引用、运算符和常量。


公式的各部分 

1. 函数：PI() 函数返回 pi 值：3.142...

2. 引用：A2 返回单元格 A2 中的值。

3. 常量：直接输入到公式中的数字或文本值，例如 2。

4. 运算符：^（脱字号）运算符表示数字的乘方，而 *（星号）运算符表示数字的乘积。

-----------------------------------------------------------------------------------------------------------------------

在 Excel 公式中使用常量

常量是一个不是通过计算得出的值；它始终保持相同。 例如，日期 10/9/2008、数字 210 以及文本“季度收入”都是常量。 表达式或从表达式得到的值不是常量。 如果在公式中使用常量而不是对单元格的引用（例如 =30+70+110），则仅在修改公式时结果才会变化。 通常，最好在各单元格中放置常量（必要时可轻松更改），然后在公式中引用这些单元格。

-----------------------------------------------------------------------------------------------------------------------

在 Excel 公式中使用引用

引用的作用在于标识工作表上的单元格或单元格区域，并告知 Excel 在何处查找要在公式中使用的值或数据。 你可以使用引用在一个公式中使用工作表不同部分中包含的数据，或者在多个公式中使用同一个单元格的值。 还可以引用同一个工作簿中其他工作表上的单元格和其他工作簿中的数据。 引用其他工作簿中的单元格被称为链接或外部引用。
  
A1 引用样式

默认情况下，Excel 使用 A1 引用样式，此样式引用字母标识列（从 A 到 XFD，共 16,384 列）以及数字标识行（从 1 到 1,048,576）。 这些字母和数字被称为行号和列标。 要引用某个单元格，请输入列标，后跟行号。 例如，B2 引用列 B 和行 2 交叉处的单元格。
  

若要引用	用途
列 A 和行 10 交叉处的单元格	A10
在列 A 和行 10 到行 20 之间的单元格区域	A10:A20
在行 15 和列 B 到列 E 之间的单元格区域	B15:E15
行 5 中的全部单元格	5:5
行 5 到行 10 之间的全部单元格	5:10
列 H 中的全部单元格	H:H
列 H 到列 J 之间的全部单元格	H:J
列 A 到列 E 和行 10 到行 20 之间的单元格区域	A10:E20

引用同一工作簿中另一个工作表上的单元格或单元格区域

下例中，AVERAGE 函数将计算同一个工作簿中名为 Marketing 的工作表的 B1:B10 区域内的平均值。

1. 对名为 Marketing 的工作表的引用

2. 引用 B1 到 B10 的单元格区域

3. 感叹号 (!) 将工作表引用与单元格区域引用分开
  
注意: 如果引用的工作表中有空格或数字，则需要在工作表名称前后添加单撇号 (')，如='123'!A1 或 ='January Revenue'!A1。
  
绝对引用、相对引用和混合引用之间的区别
  
相对引用 :公式中的相对单元格引用（如 A1）是基于包含公式和单元格引用的单元格的相对位置。 如果公式所在单元格的位置改变，引用也随之改变。 如果多行或多列地复制或填充公式，引用会自动调整。 默认情况下，新公式使用相对引用。 例如，如果将单元格 B2 中的相对引用复制或填充到单元格 B3，将自动从 =A1 调整到 =A2。

复制的公式具有相对引用 

绝对引用 :公式中的绝对单元格引用（如 $A$1）总是在特定位置引用单元格。 如果公式所在单元格的位置改变，绝对引用将保持不变。 如果多行或多列地复制或填充公式，绝对引用将不作调整。 默认情况下，新公式使用相对引用，因此您可能需要将它们转换为绝对引用。 例如，如果将单元格 B2 中的绝对引用复制或填充到单元格 B3，则该绝对引用在两个单元格中一样，都是 =$A$1。

复制的公式具有绝对引用 


混合引用 :混合引用具有绝对列和相对行或绝对行和相对列。 绝对引用列采用 $A1、$B1 等形式。 绝对引用行采用 A$1、B$1 等形式。 如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用将不变。 如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用将不作调整。 例如，如果将一个混合引用从单元格 A2 复制到 B3，它将从 =A$1 调整到 =B$1。

复制的公式具有混合引用 
    
三维引用样式

便于引用多个工作表:如果要分析同一工作簿中多个工作表上相同单元格或单元格区域中的数据，请使用三维引用。 三维引用包含单元格或区域引用，前面加上工作表名称的范围。 Excel 使用存储在引用开始名和结束名之间的任何工作表。 例如，=SUM(Sheet2:Sheet13!B5) 将计算 B5 单元格内包含的所有值的和，单元格取值范围是从工作表 2 到工作表 13。
  
可以使用三维引用来引用其他工作表中的单元格、定义名称，还可以通过使用下列函数来创建公式：SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV.P、STDEV.S、STDEVA、STDEVPA、VAR.P、VAR.S、VARA 和 VARPA。
  
三维引用不能用于数组公式中。
  
不能与交集运算符（单个空格）一起使用三维引用，也不能在使用了绝对交集的公式中使用三维引用。
  
在移动、复制、插入或删除工作表时出现的情况 ???以下示例演示在移动、复制、插入或删除三维引用中包括的工作表时出现的情况。 该示例使用公式 =SUM(Sheet2:Sheet6!A2:A5) 对从工作表 2 到工作表 6 的每个工作表中的 A2 到 A5 单元格求和。
  
插入或复制 :如果在 Sheet2 和 Sheet6（本示例中的起止工作表）之间插入或复制工作表，则 Excel 将在计算中包含所添加的工作表中从单元格 A2 到 A5 的所有值。
  
删除: 如果删除了 Sheet2 和 Sheet6 之间的工作表，Excel 将在计算过程中删除相应的值。
  
移动 :如果将 Sheet2 和 Sheet6 之间的工作表移动到引用工作表区域之外的位置，Excel 将在计算过程中删除相应的值。
  
移动起止工作表:如果将 Sheet2 或 Sheet6 移到同一工作簿中的其他位置，Excel 将对计算进行调整以包含它们之间的新工作表区域。
  
删除起止工作表:如果删除了 Sheet2 或 Sheet6，Excel 将对计算进行调整以包含它们之间的工作表区域。
    
R1C1 引用样式

也可以使用同时统计工作表上的行和列的引用样式。 R1C1 引用样式对于计算位于宏内的行和列的位置很有用。 在 R1C1 样式中，Excel 指出了行号在“R”后而列号在“C”后的单元格的位置。

引用	含义
R[-2]C	对同一列中上面两行的单元格的相对引用
R[2]C[2]	对在下面两行、右面两列的单元格的相对引用
R2C2	对位于第二行、第二列的单元格的绝对引用
R[-1]	对活动单元格整个上面一行单元格区域的相对引用
R	对当前行的绝对引用

当您录制宏时，Excel 将使用 R1C1 引用样式录制一些命令。 例如，如果录制这样的命令：单击“自动求和”按钮插入对某区域中单元格求和的公式，则 Excel 将使用 R1C1 引用样式而不是 A1 引用样式来录制该公式。

可以通过设置或清除“R1C1 引用样式”复选框来打开或关闭 R1C1 引用样式，该复选框位于“选项”对话框的“公式”类别中的“使用公式”部分下。 若要显示此对话框，请单击“文件”选项卡。

excel怎么创建三维引用，如何引用多张工作表单元格内容

引用同一单元格或范围在多个工作表的引用是名为三维引用。三维引用是引用多个工作表，遵循相同的模式，并且包含相同类型的数据的有用且方便方法-例如，在您的组织中合并来自不同部门的预算数据。


   -----------------------------------------------------------------------------------------------------------------------

了解三维引用

可以使用以下三维引用将“销售部”、“人事部”和“营销部”这三个部门的预算分配（每个部门的预算分配分别位于不同的工作表上）相加：

=SUM(Sales:Marketing!B3)

可以甚至添加另一个工作表，并将其移动到您的公式中引用的区域。例如，若要添加的参考设施工作表中的单元格 B3，设施表之间移动通过拖动其选项卡，如下图所示的销售和人力资源工作表：




由于公式包含三维引用的工作表名称区域销售部： 营销部 ！B3，所有工作表区域中的新计算的一部分。



   -----------------------------------------------------------------------------------------------------------------------

了解移动、复制、插入或删除工作表时三维引用如何更改

下面的示例说明插入、 复制、 删除或移动三维引用中包含的工作表时，会发生什么情况。每个这些示例使用公式 = SUM (Sheet2:Sheet6 ！在 a2: a5) 2 到 6 的工作表上添加单元格区域 A2 到 A5:
  
插入或复制  —如果您插入或复制工作表 Sheet2 和 Sheet6 之间的 Excel （在本例中端点） 包含在单元格区域 A2 到 A5 中计算构成的工作表中的所有值。
  
删除—  如果删除了 Sheet2 和 Sheet6 之间的工作表，Excel 将在计算过程中删除相应的值。
  
移动—  如果将 Sheet2 和 Sheet6 之间的工作表移动到引用工作表区域以外的位置，Excel 将在计算过程中删除相应的值。
  
移动起止—  如果将 Sheet2 或 Sheet6 移到同一工作簿中的其他位置，Excel 将调整要包括在它们之间的新工作表，除非您反转顺序的工作簿中的终结点的计算。如果您反转结束点，三维引用更改端点工作表。例如，您可能必须对 Sheet2:Sheet6 的引用。如果将 Sheet2 移动以便后，这是 Sheet6 工作簿中，公式将调整到指向 Sheet3:Sheet6。如果移动前面 Sheet2 Sheet6，该公式将调整到指向 Sheet2:Sheet5。
  
删除起止—  如果删除了 Sheet2 或 Sheet6，Excel 将在计算过程中删除该工作表中的值。
  

   -----------------------------------------------------------------------------------------------------------------------

创建三维引用

请按以下步骤操作：
  
单击将在其中输入函数的单元格。
  
= （等号） 后, 跟函数名称的类型 （请参阅下表），然后左括号。
  
单击要引用的第一张工作表的标签。
  
按住 Shift 键，然后单击您想要引用的最后一个工作表选项卡。
  
选择要引用的单元格或单元格区域。
  
完成公式，然后按 Enter。
  
您可以在三维引用中使用以下函数：

函数	说明
AVERAGE 函数	计算数值的平均值（算术平均值）。
AVERAGEA 函数	计算数值（包括文本和逻辑值）的平均值（算术平均值）。
COUNT 函数	统计包含数值的单元格数。
COUNTA 函数	统计非空单元格数。
HARMEAN 函数	返回一组正数的调和平均值：与倒数的算术平均值互为倒数。
KURT 函数	返回一组数据的峰值。
LARGE 函数	返回数据集中的第 k 个最大值。例如，第五个最大值。
MAX 函数	查找一组数值中的最大值。
MAXA 函数	查找一组数值中的最大值（包括文本和逻辑值）。
MEDIAN 函数	返回中值，即位于一组给定数字中间的数字。
MIN 函数	查找一组数值中的最小值。
MINA 函数	查找一组数值中的最小值（包括文本和逻辑值）。
PERCENTILE 函数	返回某个区域中的数值的第 k 个百分点值，此处的 k 的范围为 0 到 1 为了与早期版本的 Excel 相兼容而提供。
PERCENTILE.EXC 函数	返回区域中数值的第 K 个百分点的值，其中 k 为 0 到 1 之间的值，不包含 0 和 1。
PERCENTILE.INC 函数	返回区域中数值的第 K 个百分点的值，K 为 0 到 1 之间的百分点值，包含 0 和 1。
PERCENTRANK 函数	将某个数值在数据集中的排位作为数据集的百分点值返回，此处的百分比值的范围为 0 到 1。为了与早期版本的 Excel 相兼容而提供。
PERCENTRANK.EXC 函数	返回某个数值在一个数据集中的百分比（0 到 1，不包括 0 和 1）排位。
PERCENTRANK.INC 函数	将某个数值在数据集中的排位作为数据集的百分比值返回，此处的百分比值的范围为 0 到 1（含 0 和 1）。
QUARTILE 函数	根据 0 到 1 之间的百分点值，返回数据值的四分位数。为了与早期版本的 Excel 相兼容而提供。
QUARTILE.EXC 函数	基于百分点值返回数据集的四分位，此处的百分点值的范围为 0 到 1（不含 0 和 1）
QUARTILE.INC 函数	根据 0 到 1 之间的百分点值（包含 0 和 1）返回数据集的四分位数。
PRODUCT 函数	将数值相乘。
RANK 函数	返回一个数字在数字列表中的排位：数字的排位是其大小与列表中其他值的比值。为了与早期版本的 Excel 相兼容而提供。
RANK.EQ 函数	返回一个数字在数字列表中的排位：数字的排位是其大小与列表中其他值的比值。
RANK.AVG 函数	返回一个数字在数字列表中的排位：数字的排位是其大小与列表中其他值的比值。
SKEW 函数	返回分布的偏斜度。
SMALL 函数	返回数据集中的第 k 个最小值。
STDEV.S 函数	基于样本计算标准偏差。
STDEV.P 函数	计算总体的标准偏差。
STDEVA 函数	基于样本（包括文本和逻辑值）计算标准偏差。
STDEVPA 函数	计算总体（包括文本和逻辑值）的标准偏差。
TRIMMEAN 函数	返回数据集的内部平均值。
SUM 函数	将数值相加。
VAR.S 函数	计算基于给定样本的方差。
VAR.P 函数	计算总体的方差。
VARA 函数	基于样本（包括文本和逻辑值）估算方差。
VARPA 函数	计算总体的方差包括文本和逻辑值

   -----------------------------------------------------------------------------------------------------------------------

为三维引用创建名称

请按以下步骤操作：
  
在公式选项卡上，单击定义名称（在已定义名称组中）。
  
在新名称弹出窗口，供您参考输入名称。可以为名称的长度超过 255 个字符。
  
在引用位置列表中，选择等号 （=） 和引用，，然后按 Backspace 键。
  
单击您想要引用的第一个工作表选项卡。
  
按住 Shift 键，然后单击引用的工作表的选项卡。
  
选择要引用的单元格或单元格区域。

什么是结构化引用，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%

    
复制上表中的示例数据（包括列标题），然后将其粘贴到新 Excel 工作表的单元格 A1 中。

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

确保已选中“表包含标题”框，然后单击“确定”。

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

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

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

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

按 Enter。

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


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

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

在示例工作表中，单击单元格 E2

在公式栏中输入 =C2*D2，然后按 Enter。

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

如何更改表名称？

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

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

在“表名称”框中，键入所需的名称，然后按 Enter。

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

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

使用有效字符 名称始终以一个字母加一个下划线字符 (_) 或一个反斜杠 (\) 开头。 在名称的其余部分中，可使用字母、数字、句点和下划线字符。 名称中不能使用“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]])

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

表名称： “部门销售”是自定义表名称。 它引用表数据（不包含任何标题或汇总行）。 您可以使用默认的表名称，如 Table1，也可更改为使用自定义名称。

列说明符： [销售额]和 [佣金金额] 是使用其所表示的列名称的列说明符。 它们引用列数据（不包含任何列标题或汇总行）。 始终将说明符用所示的方括号括起来。

项目说明符： [#汇总] 和 [#数据] 是引用表的特定部分（如汇总行）的特殊项目说明符。

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

结构化引用： 部门销售[[#汇总],[销售额]] 和 部门销售[[#数据],佣金金额]] 是结构化引用，使用以表名称开头、以列说明符结尾的字符串表示。

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

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

所有列标题都为文本字符串  但它们用于结构化引用中时不需要使用引号。 数字或日期，例如 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 不会自动将对此区域的任何单元格引用更改为它们的等效结构化引用。

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

在表格中添加或删除列和行  由于表格数据区域经常变化，结构化引用的单元格引用也自动随之调整。 例如，如果您在公式中使用表格名称对表格中的所有数据单元格进行计数并添加一行数据，则单元格引用会自动调整。

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

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

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

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