跨工作簿引用数据的痛点
在实际工作中的数据引用不可能都是来自同个一工作簿。可能要从很多个工作簿中提取想要的数据。
跨工作簿引用的过程中，会出现的以下几个问题：
1、被引用的工作簿需要打开，否则公式出现错误
2、如果“被引用工作簿需要打开”这件事情，为了工作还能忍，但是公式变得超级长，出错了还不知道怎么改，就不能忍了。
其实要想解决上述的痛点，也是可以的。我前面所写的一篇文章《跨工作薄引用数据必需打开引用工作簿？不可能！根本不可能！》可以是大家的一个切入点。
今天小编就用一个很浅显的实例来给大家讲解跨工作簿引用数据如何解决上述的痛点。
实例前的准备
1、准备几个不同文件夹，里面各有一个表结构相同的表，分别是三年级1班部份同学期中和期末考试语数外三科成绩表以及一个学生信息表。为了让实例不致于复杂，特地将难度降低，共计15名学生。
以上是我准备的几个不同文件夹下的工作簿。
2、实例演示的目标：
班主任李老师要根据班上学生的各科成绩，做个汇总报告：分别需要分析学生的期中期各科成绩对比以及成绩变化；期中期末总分对比以及成绩变化。
我们需要根据上面的目标做一个数据汇总表。
汇总表的表头大约就是这个样子：
示例分析
1、根据汇总表表头，我们需要从其他工作簿中提取的数据有：学生的姓名、语数外各科期中期末成绩。这是需要从期他表提取的数据。
2、数据加工我们需要做的有：各科成绩根据期中与期末的成绩变化，在变化指示中给出相应提示；根据各科成绩分别计算期中期末总分成绩，并在成绩变化指示中给出相应提示；对期末总分做一个排名。
3、数据加工需要用到的知识点：查找函数、排名函数，求和函数
4、数据引用需要用到的重要知识点：名称管理器定义数据区域的名称
5、可能涉及到的知识点：lambda函数。
讲解步骤
第一步：在汇总表所在工作簿中添加自定义名称。
我们需要用到的名称：学生姓名、语文学生名册、期中语文、期末语文、数学学生名册、期中数学、期末数学、英语学生名册、期中英语、期末英语、期中总分、期末总分。关于如何添加自定义名称，在此小编做一个“学生姓名”演示，期他演示大家照葫芦画瓢，实有不会的同学，可以参考《跨工作薄引用数据必需打开引用工作簿？不可能！根本不可能！》一文
名称管理器设置完之后是这样的。
此时可以把不相关的表都关闭，我们只需要打开“成绩汇总”这个工作簿。
第二步：录入学生姓名。
在成绩汇总这个工作表中的学生姓名一列的第一个空单元格内输入“=学生姓名”，表格会自动从学生花名册中按顺序读取学生的姓名，并列于成绩汇总表中
第三步，填充各科成绩。
我们再分别用xlookup函数来查找并填充各科成绩，语文期中的读取公式如下：
=XLOOKUP(A4#,语文学生名册，期中语文，0，0，1)所有成绩填充完成之后的效果图如下：
第四步，填充各科成绩变化指示
期末成绩比期中成绩高的，表示成绩“进步”、期末成绩比期中成绩低的：表示成绩“退步”、相等的表示成绩“持平”。
公式设置如下：
IF(B4>C4,"退步",IF(B4<C4,"进步","持平"))为了能更好的调用这个指示器公式，我计划将其用lambda函数自定义一个对比函数。
lambda函数设置为
=LAMBDA(前数,后数,IF(前数>后数,"退步",IF(前数<后数,"进步","持平")))
将各科成绩对比填充完之后效果如下：
第五步，计算期末总分排名，使用公式Rank
rank函数：返回一个数字在数字列表中的排位。
RANK(number,ref,order)
Number 为需要找到排位的数字。
Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。
Order 为一数字，指明排位的方式。
￭如果 order 为 0（零）或省略，WPS表格 对数字的排位是基于 ref 为按照降序排列的列表。
￭如果 order 不为零，WPS表格 对数字的排位是基于 ref 为按照升序排列的列表。
说明
￭函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。
例如，在一列整数里，如果整数10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。 排完名之后总体效果如下图：
演示小结：
我们用到如下知识点：
1、名称管理器：通过名称管理器跨表引用，并且不需要打开被引用的工作簿。
2、xlookup函数：当然也可以改成兼容性更强的index match组合。
3、lambda函数：成绩变化对比，是进步还是退步，定义成一个固定公式，凡是在本工作簿中需要用到对比，都可以使用
4、rank函数：一个数在一组数中的排名。这对于顺序是乱的数据中理清顺序很有帮助。
以上只是一个很简单的跨工作簿引用数据的例子，解决了我们文章开头所说的痛点。希望对大家有帮助。
评论 (0)