Excel 中的交叉引用

MS Excel 或 Microsoft Excel 是一款流行的电子表格软件，用于按行和列组织数据。由于 Excel 允许用户跨多个工作表组织大量数据，因此它允许通过在其他工作表、相同工作表甚至不同工作簿中的单元格引用来使用单元格值。单元格的交叉引用允许用户在其他单元格中使用单元格的值，而不是放入实际值，而是使用它们的单元格引用。这有助于简化在工作簿中的大型数据集中的数据计算。

在本文中，我们将简要介绍 Excel 中的交叉引用。本文还讨论了在不同场景下进行 Excel 交叉引用的分步过程，包括示例。

Excel 中的交叉引用是什么？

Excel 工作簿中的每个单元格都由一个唯一的地址表示，称为单元格引用。单元格引用通常是特定列和行地址的组合。它包含代表相应单元格列的字母和代表相应行的数字。在 Excel 工作簿中，不同工作表中的相同位置单元格代表相同的单元格引用。

交叉引用只不过是对同一 Excel 工作簿中其他位置信息的引用。要跨不同的工作簿工作表进行交叉引用，我们必须了解如何通过扩展地址来识别单元格。扩展引用帮助我们指定确切的单元格，包括其工作表名称、列和行。

让我们通过一个简单的示例来理解交叉引用，在该示例中我们将使用工作表中的第一个单元格进行交叉引用。

• 首先，我们必须识别要应用交叉引用的单元格的列。例如，Excel 工作表中的第一个单元格是 A 列的一部分。
  
• 接下来，我们必须识别相应单元格所属的行。例如，Excel 工作表中的第一个单元格是第一行的部分。
  
• 我们需要组合上述两点来形成工作表上第一个单元格的单元格地址。因此，当我们组合列和行的字符时，第一个单元格的单元格地址变为 'A1'。
  
• 之后，我们需要识别相应的工作表名称。单元格工作表的默认名称是“Sheet1”、“Sheet2”、“Sheet3”等。在我们的示例中，它是 'Sheet1'。
  
• 为了识别特定单元格的扩展引用，我们必须将工作表名称附加到已识别的单元格地址，并用感叹号分隔。在我们的示例中，工作表中的第一个单元格属于以下扩展引用："Sheet1!A1"。
  
• 假设 Sheet1 的第一个单元格中有一个数值（30），我们想将其与另一个数值（20）相加，并在我们工作簿的 Sheet2 的第一个单元格中得到结果。因此，我们必须使用第一个工作表中第一个单元格的扩展引用，并在第二个工作表的第一个单元格中添加公式，如下所示："=Sheet1!A1 + 20"，不带引号。
  
  在上面的图片中，我们可以看到使用交叉引用将工作簿中不同工作表的值相加。

如何在 Excel 中交叉引用单元格？

根据具体情况，在 Excel 中使用交叉引用的方法可能有所不同。以下是在 Excel 工作表中使用的最常见的交叉引用方法：

• 在 Excel 中交叉引用单个单元格
• 在 Excel 中引用单元格范围
• 在 Excel 中引用命名单元格

现在让我们详细了解每一个。

在 Excel 中交叉引用单个单元格

在位置图（地图）中，使用了网格参考的概念。地图被分成各种网格，帮助我们定位地图上的任何特定位置。我们通常通过使用单独的网格参考来指定地图上的任何位置。在 Excel 中，电子表格的工作方式几乎相同。

Excel 电子表格包含各种单元格，我们可以通过它们的单个引用来指定它们，这些引用是通过组合一个代表相应列的字母和一个代表特定行的数字而形成的。例如，单元格 B4 位于与第二列（B 列）和第 4 行相交的网格中。假设 B4 是我们工作簿 Book1.xlsx 中的一个单元格。

要在同一工作表中的任何其他单元格中引用 B4，我们必须在结果单元格中输入 =B4。此外，我们可以键入任何所需的公式，其中包含多个单元格引用，以合并来自不同单元格的值来获得结果。例如，我们可以键入 "=B4*B5"（不带引号）来将单元格 B4 和单元格 B5 中的数据相乘，如以下所示：

输入组合了单元格引用的公式后，我们必须按 Enter 键以获得结果值。上面的公式提供了以下结果：

这样，我们就可以在同一个工作表中进行单个单元格的交叉引用。但是，要引用另一个工作表中的单元格，我们必须在单元格前加上工作表名称，后跟感叹号。例如，如果在 Sheet2 中的任何单元格中使用公式 =Sheet1!B4*Sheet2!B5，我们将得到与上面相同的结果（即 600）。相应的公式将乘以 Sheet1 中单元格 B4 和 B5 的值，并在 Sheet2 的选定单元格中提供结果值。

按下 Enter 键后，结果相同。

这样，我们就可以在不同的工作表中进行单个单元格的交叉引用。此外，Excel 还允许我们引用另一个工作簿（Excel 文件）中的单元格。为此，我们必须使用方括号来指定特定的 Excel 工作簿名称，后跟扩展引用。例如，=[Book1.xlsx]Sheet2!B2 在同一文件夹中的另一个工作簿中使用时，将代表与上图相同的值（600）。

假设我们在与上述文件（Book1.xlsx）相同的文件夹中创建了一个新的 Excel 文件 (Book2.xlsx)。然后在 Book2.xlsx 中，我们在任何单元格中应用公式 =[Book1.xlsx]Sheet1!B4*[Book1.xlsx]Sheet1!B5，如下所示：

按下 Enter 键后，得到的结果值是通过将 Excel 工作簿 Book1.xlsx 的 Sheet1 中单元格 B4 和 B5 的值相乘得到的。

这样，我们就可以在 Excel 中引用同一工作表、不同工作表甚至不同工作簿/电子表格中的单个单元格。

在 Excel 中引用单元格范围

Excel 还允许用户通过引用多个单元格来应用特定公式，前提是这些单元格是连续的。在这种情况下，我们可以在相应的公式中使用整个单元格范围。

例如，当我们需要在我们的示例工作簿（Book1.xlsx）中对单元格 B3 到 B7 中的值求和时，我们通常会选择整个范围并将其提供给如下公式：=SUM(B3:B7)。这意味着我们通过使用冒号连接起始单元格和结束单元格来指定范围。

输入公式后，我们必须按 Enter 键以获得结果值，如以下所示：

我们可以通过它们的引用在其他工作表和工作簿中使用单元格范围。方法几乎与我们上面讨论的单个单元格的引用方法相同。

当我们需要引用同一工作簿中另一个工作表中的单元格范围时，我们必须在单元格范围之前指定相应的工作表名称。例如，要计算 Sheet1 中单元格 B3 到 B7 的总和，并在 Sheet2 的任何所需单元格中得到结果，我们可以使用如下公式：=SUM(Sheet1!B3:B7)。

按下 Enter 键后，我们在 Sheet2 中得到的结果值与我们在 Sheet1 中计算的上述结果总和相同。

当引用不同 Excel 工作簿中的单元格范围时，我们需要在框内键入方括号和相应的文件名，后跟扩展引用和单元格范围。假设我们需要对单元格 B3 到 B7 的值求和，并在我们主文件（Book1.xlsx）所在的相同文件夹中的另一个 Excel 文件 (Book2.xlsx) 的任何单元格中获得结果值。然后在 Book2.xlsx 中，我们需要在任何单元格中应用公式 =SUM([Book1.xlsx]Sheet1!B3:B7)，如下所示：

按下 Enter 键后，在另一个工作簿 (Book2.xlsx) 中得到的结果值是通过将主 Excel 工作簿 (Book1.xlsx) 的 Sheet1 中单元格 B3 到 B7 的值相加得到的。

这样，我们就可以在 Excel 中引用同一工作表、不同工作表甚至不同工作簿或电子表格中的单元格范围。

在 Excel 中引用命名单元格

Excel 允许用户为电子表格中的特定单元格指定任何特定名称。它通过替换网格式引用方法来简化引用方法，并允许使用特定名称来引用单个单元格以及单元格范围。

例如，如果我们给一个单元格命名为“data”，我们可以在整个工作簿中为该定义的单元格使用此名称作为引用。在这种情况下，我们不需要定义扩展引用。这通常消除了单元格引用或单元格范围输入错误的可能性，并节省了潜在的时间。

要为工作簿中的任何单元格指定所需的名称，我们需要选择或突出显示特定单元格，按右键按钮，然后选择“定义名称”选项。如下所示：

在下一个窗口中，我们需要为选定的单元格键入名称，选择范围（将键入的名称用于整个工作簿或特定工作表），然后单击确定按钮。

保存名称后，我们可以单击特定单元格并在引用区域中看到其名称。我们还可以使用命名方法为特定单元格范围定义名称。

定义名称后，我们可以使用公式将其作为引用：=data。

由于我们在命名单元格时选择了“工作簿”作为范围，因此命名单元格引用也可以用于其他工作簿工作表。例如，如果我们使用相同的公式（=data）在 Sheet2 中，我们将得到与上图相同的结果：

假设我们要引用另一个 Excel 工作簿中的命名单元格。我们不需要使用方框在其中键入特定的工作簿名称。相反，我们可以轻松地指定相应的 Excel 工作簿名称，后跟感叹号和命名单元格。

例如，我们可以在另一个工作簿 (Book2.xlsx) 的任何特定单元格中输入公式 '=Book1.xlsx!data'（不带引号），并获得与我们在上图中获得的结果相同的结果。但是，工作簿必须保存在同一个文件夹中。

这样，我们就可以在 Excel 中引用同一工作表、不同工作表甚至不同工作簿或电子表格中的命名单元格。

使用 VLOOKUP 进行交叉引用的示例

当我们在多个列表中拥有数据时，不容易手动将所有数据保存在一个列表中。但是，我们可以使用 vlookup 来交叉引用它们。无论我们的数据列表有多长，我们都可以通过几次单击完成数百甚至数千次交叉引用。

VlookUp 在提供的表格中垂直向下搜索值。此函数的基本语法如下：

其中四个参数如下：

• lookup_value：这是用户输入的值。我们将此值提供给函数进行搜索，以交叉引用到另一个列表。
• table_array：这是表格所在的单元格范围。它主要包含要搜索的列以及我们将从中获取所需数据的列。
• col_index_num：这是包含我们所需答案的数据列。在我们的示例中，它是包含收藏夹的列。因此，列索引号为 2（提供的表格数组的第 2 列）。
• range_lookup：此值可以是 TRUE 或 FALSE。当设置为 TRUE 时，函数会在不继续查找的情况下提供最接近查找值的结果。另一方面，当设置为 FALSE 时，查找函数会在找到时返回精确匹配；否则，它将返回 #N/A。

现在让我们交叉引用两个 Excel 工作表之间的数据。

假设我们有以下工作表，其中包含一些员工 ID、姓名和出生日期。

此外，我们还有另一个工作表，其中包含几乎相同的员工及其喜欢的颜色。

我们需要从上述两个工作表中创建一个合并列表，以便在同一工作表中显示员工 ID、姓名、出生日期和喜欢的颜色。我们必须使用 VLOOKUP，方法是在 Sheet1 中创建的一个新列（Fav. Color）中使用以下数据：

• 我们示例中的查找值是员工姓名列表。
• 表格数组是第二个工作表中的表格，从单元格 B2 到 B11。
• 列索引是我们需要的答案。在我们的示例中，它是包含收藏夹的列。因此，列索引号是 2（提供的表格数组的第 2 列）。
• 由于我们想要获得精确匹配，因此我们将使用 FALSE 作为第四个参数。

应用上述值的 VLOOKUP 后，我们将获得所需的结果，如下所示：

按下 Enter 键并将公式复制（或拖动）到其他单元格后，我们得到以下结果：

在上图中，VLOOKUP 函数从 Sheet2 搜索颜色，并将结果提供给 Sheet1。这是 Excel 中交叉引用的最常见示例。