Excel跨工作簿数据引用实战：从名称管理到成绩汇总

Excel跨工作簿数据引用实战：从名称管理到成绩汇总

在实际工作中，我们常常需要从多个Excel工作簿中提取数据。但是，跨工作簿引用数据时，经常会遇到一些困扰：被引用的工作簿需要保持打开状态，否则公式会出错；公式变得异常复杂，难以理解和修改。本文将通过一个具体实例，教你如何轻松解决这些问题。

实例背景

假设班主任李老师需要根据班上学生的各科成绩，制作一个汇总报告，分析学生的期中期末成绩对比及变化。为此，我们需要从不同文件夹下的多个工作簿中提取学生信息和成绩数据，制作一个数据汇总表。

实例准备

1. 准备几个不同文件夹，每个文件夹中包含一个表结构相同的成绩表，内容为三年级1班15名学生期中和期末考试的语数外三科成绩，以及一个学生信息表。
2. 实例目标：制作一个汇总表，展示学生的姓名、各科成绩、成绩变化情况以及总分排名。

汇总表的表头设计如下：

实现步骤

第一步：定义名称

在汇总表所在的工作簿中，使用名称管理器定义以下名称：

• 学生姓名
• 各科成绩（期中和期末）
• 总分

以“学生姓名”为例，演示如何添加自定义名称：

1. 在公式选项卡中点击“名称管理器”
2. 点击“新建”，输入名称“学生姓名”
3. 在引用位置中输入对应的工作簿路径和范围，例如：

   ='C:\Users\Username\Documents\[学生信息表.xlsx]Sheet1'!$A$2:$A$16
   

完成名称设置后，可以关闭所有其他工作簿，只保留汇总表工作簿。

第二步：录入学生姓名

在汇总表的“学生姓名”列中，输入公式：

=学生姓名

表格会自动从学生信息表中读取并列出所有学生姓名。

第三步：填充各科成绩

使用XLOOKUP函数从其他工作簿中提取各科成绩。以“语文期中”成绩为例：

=XLOOKUP(A4#, 语文学生名册, 期中语文, 0, 0, 1)

其中：

• A4# 是当前行的学生姓名
• 语文学生名册 是之前定义的名称
• 期中语文 是之前定义的名称
• 0 表示如果未找到匹配项则返回0
• 0 表示精确匹配
• 1 表示查找范围按升序排列

第四步：填充成绩变化指示

使用IF函数判断成绩变化情况：

=IF(B4>C4, "退步", IF(B4<C4, "进步", "持平"))

为了方便复用，可以使用LAMBDA函数定义一个对比函数：

=LAMBDA(前数, 后数, IF(前数>后数, "退步", IF(前数<后数, "进步", "持平")))

第五步：计算总分排名

使用RANK函数计算期末总分排名：

=RANK(期末总分, 期末总分范围, 0)

其中：

• 期末总分 是当前学生的期末总分
• 期末总分范围 是所有学生的期末总分范围
• 0 表示降序排列

最终效果如下：

总结

通过这个实例，我们学习了以下关键知识点：

1. 名称管理器：实现跨工作簿数据引用，无需保持被引用工作簿打开。
2. XLOOKUP函数：快速查找并提取数据。
3. LAMBDA函数：定义自定义函数，简化复杂公式。
4. RANK函数：计算数值在一组数据中的排名。

这些技巧不仅解决了跨工作簿引用数据时的痛点，还提高了数据处理的效率和准确性。希望这些方法能帮助你在工作中更高效地处理Excel数据。