在Microsoft Excel中从另一张工作表提取数据的5种方法

想要从 Excel 中的另一个工作表或工作簿中提取数据来构建表格、图表等？继续阅读以了解一些快速有效的方法。

Excel 除了是领先的数据分析和可视化软件之外，还是一款强大的数据库管理工具。它提供了多种方法来汇总来自不同工作表、工作簿以及存储在服务器或云存储上的工作簿的数据。

因此，您可以制作动态仪表板和表格，从原始数据中获取数据洞察。您无需将原始数据保存在保存数据可视化的同一工作表中。工作簿和工作表引用使这成为可能。

以下是使用手动和自动方法从另一张工作表或工作簿中提取数据的多种方法。

为什么要从 Excel 中的另一张工作表中提取数据

当在单个工作簿中维护大量工作表模型变得不切实际时，从其他工作表或工作簿中提取数据尤其有用。该技术可帮助您：

您可以在不同用户或部门使用的工作簿之间建立链接，并将相关数据合并到摘要工作簿中。
将所有数据输入一个或多个源工作簿，然后创建一个单独的报告工作簿，利用外部引用仅提取所需的数据。
通过将复杂的模型分解为相互关联的工作簿，您无需打开所有相关工作表即可处理特定方面。

使用工作表和单元格引用

如果遵循以下 Excel 约定来引用源，您可以轻松地从不同的工作表和工作簿中提取数据：

引用同一工作簿中的工作表时，请输入工作表的名称，后跟感叹号。例如，如果您要从单元格区域导入表格 A1:B4 在 Sheet2 到 Sheet1，在目标单元格中键入以下语法 Sheet1。

=Sheet2!A1:B4

如果工作表名称中有空格，请按原样输入名称，然后将其放在单引号中。例如，工作表名称 Sheet 2 必须称为 ='Sheet 2'! A1:B4。
引用工作簿时，将工作簿名称放在方括号内，然后按原样引用该工作簿中的目标工作表。例如，如果您从另一个工作簿中提取数据 pulldata2.xlsx 到 pulldata.xlsx，然后突出显示目标单元格并输入以下语法：

=[pulldata2.xlsx]Sheet1!A1:B4

如果你正在使用过时的 Excel 桌面应用程序（如 Excel 2007、Excel 2010 等），则必须点击 Ctrl + 转移 + 进入 引用工作表或工作簿。因为这些引用公式作为 Excel 数组公式工作。

但是，如果你使用的是 Excel 网页版或 Excel for Microsoft 365 桌面应用程序，则只需点击 Enter 钥匙。

从同一工作簿中提取数据

请参阅以下说明，了解如何使用指数和匹配公式。此示例说明如何使用带有工作表引用的公式。

在 Sheet1，我得到了员工姓名、工资等级和工资列。我必须从工资等级和实际工资数据库中导入员工的工资 Sheet2。

在细胞内 C2，我输入以下公式，使用指数和匹配从中提取正确的数据 Sheet2 到 Sheet1：

=INDEX(Sheet2!$A$2:$A$4,MATCH(B2,Sheet2!$B$2:$B$4,0))

现在，我可以向下拖动填充柄 C 栏 填充其他员工的工资。

在您自己的数据集中使用该公式时，请按如下所述进行修改：

Sheet2!：将其更改为工作簿中确切的工作表名称。
$A$2:$A$4：根据需要索引的数据集修改单元格范围。
B2：这是 Excel 从索引数据集中提取数据时所匹配的参考数据。
$B$2:$B$4：这个是查找值或者数组，根据你自己的数据集进行修改。

从其他工作簿中提取数据

当您从与您正在处理的工作簿不同的工作簿中提取数据时，您只需将其名称包含在上面显示的相同公式中。现在，可能有两种情况。一种是您打开了源工作簿，另一种是您没有打开源工作簿。

如果源工作簿也打开了，请使用以下公式：

=INDEX([pulldata2.xlsx]Sheet1!$A$2:$A$4,MATCH(B2,[pulldata2.xlsx]Sheet1!$B$2:$B$4,0))

如果源 Excel 文件未打开但位于同一台计算机上，请改用以下公式：

=INDEX('C:\[pulldata2.xlsx]Sheet1'!$A$2:$A$4,MATCH(B2,'C:\[pulldata2.xlsx]Sheet1'!$B$2:$B$4,0))

上述两个公式的区别在于提到了源工作簿在 PC 内部存储器中的完整位置。

首次创建两个工作簿之间的链接时，请保持两个工作簿都打开，并使用第一个不显示源 Excel 工作簿地址的公式。现在，下一次，如果您保持源工作簿关闭，则如果工作簿未打开，Excel 会将目录地址添加到公式中。

使用填充柄提取数据

在 Excel 中，另一种无需任何公式即可提取数据的简单方法是使用填充柄。假设您需要经常将数据集从一个工作表复制到另一个工作表。

您只需单击几下即可完成此操作，而无需记住 Excel 中的任何工作表或工作簿引用约定：

双击目标单元格并输入等号 (=）。
现在，转到源工作表或工作簿并突出显示表或数据集的起始单元格。
打 进入。
Excel 将带您进入目标工作表并从源中提取选定的单元格数据。

现在，垂直使用填充柄从源工作表或工作簿填充数据。

然后，水平拖动填充柄以获取数据集或表的其余部分。

您可能会看到一些包含数字的单元格 0。这代表源工作表或工作簿中的空白单元格。
再次使用填充柄水平和垂直调整单元格范围以删除零。

Sub RemoveZeros()
    Dim rng As Range
    Dim cell As Range

    Set rng = ActiveSheet.UsedRange 
    For Each cell In rng
        If IsNumeric(cell.Value) And cell.Value  "" Then
            If cell.Value = 0 Then
                cell.Value = ""
            End If
        End If
    Next cell
End Sub

或者，使用上面的 Excel VBA 代码去掉零。

您可以按照以下方法快速使用上述代码：

右键点击 工作表名称上。
点击 查看代码 在上下文菜单上。

将上述 VBA 脚本复制并粘贴到空白模块内。
点击节省按钮。

现在，按 Alt + F8 提出 宏对话框 并运行 删除零 宏。

使用命名范围提取数据

Excel 中的命名范围允许您为工作簿中的表或数据集定义名称。现在，您可以引用名称而不是单元格范围来从其他工作表或工作簿中提取数据。以下是创建和链接命名范围以将数据导入工作表的步骤：

突出显示源工作表或工作簿中的数据集。
点击公式选项卡上的 Excel 功能区菜单。
在 – 的里面 定义的名称 命令块，点击 定义名称。
在里面 新名字 在对话框中，为突出显示的数据集输入一个你能记住的名称姓名场地。
点击好的完成该过程。

=puuldata.xlsx!sales

现在，转到工作表中的目标单元格并输入上述公式。

按 进入 或者 Ctrl + 转移 + 进入 从 Excel 中的另一张表中提取数据。

从另一张表复制数据

从另一个工作表或工作簿中提取数据的最简单方法是复制粘贴方法。但是，当您需要提取大量数据集时，这种方法可能不方便。更不用说，手动复制和粘贴存在人为错误的风险。

以下是两种在 Excel 中自动执行复制粘贴过程以从源工作表中提取数据的方法：

使用 VBA 在工作表之间复制数据

按 Alt + F11 调用 Excel VBA 编辑器 界面。
点击插入菜单并选择模块。

Sub PullDataFromSheet4ToSheet3()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet4")
    Set destinationSheet = ThisWorkbook.Worksheets("Sheet3")
    Set sourceRange = sourceSheet.Range("A1:H5")
    Set destinationCell = destinationSheet.Range("A1")
    sourceRange.Copy destinationCell
End Sub

在新模块中，复制并粘贴此内容 VBA 脚本：
点击节省按钮并关闭 VBA 编辑器。

现在，提出宏对话框，按 Alt + F8 键。
选择 将数据从 Sheet4 拉至 Sheet3 宏并点击跑步按钮。

Excel 将立即将源数据复制到目标工作表中。下面介绍如何自定义 VBA 宏使其适用于您的工作表：

Sheet4：因为是数据来源，所以要修改sheet名称。
Sheet3：这是 Excel 从源中提取数据的目的地。
A1:H5：这是 Excel 将复制的源数据的单元格范围。根据源工作表更改它以包含更多或更少的数据。
A1：这是目标工作表中 Excel 开始粘贴复制数据的第一个单元格。您可以根据需要修改它。

使用 Office 脚本在工作表之间复制数据

假设您想在 Excel 在线应用程序上自动执行从一个工作表到另一个工作表的拉取数据过程。由于 VBA 无法在那里工作，因此您可以使用 Office 脚本。以下是您应该尝试的步骤：

转到目标工作表并单击 自动化 在 Excel 功能区。
点击 新脚本 在 – 的里面 脚本工具 命令块。
这 代码编辑器 Excel 右侧将出现面板。

function main(workbook: ExcelScript.Workbook) {
	let sheet3 = workbook.getWorksheet("Sheet3");
	let sheet4 = workbook.getWorksheet("Sheet4");
	let selectedSheet = sheet3; // Set Sheet3 as the selected sheet

	// Paste to range E1 on Sheet4 from range E1:H5 on the selectedSheet (Sheet3)
	sheet4.getRange("E1").copyFrom(selectedSheet.getRange("E1:H5"), ExcelScript.RangeCopyType.all, false, false);
}

在那里复制并粘贴上述 Office 脚本代码。
点击 保存脚本 按钮保存代码。
现在，点击跑步按钮自动从 Excel 中的另一个工作表中提取数据。

您可以通过以下方式个性化上述 Office 脚本代码。

更改所有出现的 Sheet3 使用源数据的实际工作表名称。
同样，修改所有出现的 Sheet4 根据目标工作表名称。
代替 "E1" 在代码元素中 sheet4.getRange("E1") 使用您想要开始粘贴源数据的单元格地址。
替换单元格区域 "E1:H5" 在代码元素中 selectedSheet.getRange("E1:H5") 与实际源数据范围一样 "A1:F100"， "B2:E10"， ETC。

Office 脚本仅适用于 Microsoft 365 订阅 Business Standard 或更高版本。此外，如果您想在 Excel for Microsoft 365 桌面应用程序中使用 Office Scripots，则需要互联网连接。如果您在 Excel 上看到“自动化”选项卡，则可以使用 Office 脚本。

使用 Power Query 从另一张表中提取数据

Power Query 是 Excel 的另一个很酷的数据库查询工具，可让您从同一工作簿的不同工作表或不同的工作簿导入数据。

使用以下方式从 Excel 文件中提取数据 Power Query，您可以使用命名范围使该过程更有效率。请参阅上面说明的命名范围创建过程，为源数据集或表命名。

一旦你得到了 定义名称 您需要，请按照下列步骤操作：

转到目标工作表并单击数据标签。
打 获取数据 按钮并选择 从 Excel 工作簿。

导航到源工作簿并在 导入数据 对话框。
点击进口将工作簿的内容加载到 航海家 对话框。

从左侧面板中选择命名范围。
现在，点击 下拉箭头 的加载按钮并选择 加载至。

选择 现有工作表 在 导入数据 对话框并单击好的。

Excel 将立即将选定的命名范围导入为具有活动过滤器的表格。选择所有列标题，然后按 Ctrl + 转移 + 大号 停用过滤器。

结论

现在，您已经了解了从 Excel 中的另一张工作表中提取数据的所有炫酷且直观的方法。您还可以应用这些步骤从计算机上其他工作簿中的工作表中导入数据。

如果您刚刚开始使用 Excel，则可以使用工作表和单元格引用方法或填充柄方法。这些方法适用于小型数据集。

如果您是专家级 Excel 用户，并且不介意编写几行代码，请使用基于 Excel VBA 和 Office 脚本的方法。这些方法可让您自动化整个过程。

最后，如果您需要在将源数据导入活动工作表之前将其转换为有组织的结构，请使用基于 Power Query 的方法。它带有可视化界面，因此您无需编写代码即可执行复杂的数据管理活动。