实战演练:10种创新策略解锁WPS表格函数在成绩查询中的高效应用
优秀创作者
学习WPS表格函数的途径丰富多样,实践中,不断练习和频繁应用这一工具无疑是一种极好的学习方法。但古老师特别指出,培养清晰且灵活的解题思路,才是精通WPS表格函数的最关键路径。
比如,在处理同一问题时,我们应当积极拓宽思维,探究是否存在着更多不同的解题方案。针对每一种解法,深入剖析其背后的逻辑思路,明确其是如何巧妙地利用了函数的各种特性,并进一步评估各种解法在运算效率上的差异,这样才能真正提升对WPS表格函数的理解与应用能力。
为了更好地理解和掌握运用表格函数的解决策略,今天我们选择了一道颇具代表性的实例题目进行探讨。在这道题目中,情境描述为:表1详尽收录了某中学初三一班每位学生的各项考试成绩汇总数据。现要求我们在另一个表格——表2中设计并实现一个高效的查询模块,只需用户输入指定学生的姓名,该模块就能够迅速、准确地检索到该生的所有科目总分,并将其呈现出来。(为了截图方便,截取部分数据作为案例展示)
这里,我们将采用十种不同的方法来应对这一需求,每种方法都涉及到了不同的函数组合。通过这种方式,我们可以明显观察到,通过不同函数间的灵活搭配,能够衍生出众多解决问题的策略。然而,对于不熟悉WPS表格函数的人来说,或是对于函数储备不够丰富的使用者而言,要想熟练自如地应用这些多元化的解决方案,的确是一项挑战。
思路解释:
鉴于此题目的需求涉及到对学生各科成绩的汇总计算,因而核心表格函数自然会涉及到SUM函数的应用。然而,由于加入了查询模块这一元素,我们需要结合使用查询相关的函数以满足需求,这就极大地拓展了解决方案的可能性。例如,可以借助引用函数、筛选函数乃至数组函数等多种手段。接下来,我们将分别运用这些方法逐一解决这个问题。
SUM数组的解法
录入公式:
I3=SUM((B3:B8=H3)*C3:F8)
函数解释:
(B3:B8=H3):这部分创建了一个布尔数组(逻辑数组),用于比较“表1”中B列(学生姓名列)的每个单元格是否与H3单元格(是要查询的学生姓名)相等。如果B列中的单元格内容与H3相等,则对应的结果是TRUE(在WPS中等同于1),否则是FALSE(等同于0)。
*C3:F8:接着,这个表达式将布尔数组与C到F列的数据范围相乘。在WPS表格中,任何数与TRUE相乘的结果是该数本身,与FALSE相乘的结果是0。因此,这个操作将只选择C到F列中与H3单元格相匹配的学生姓名对应的行的数据。
SUM( ...):最后,SUM函数对上一步得到的数组中的所有数值进行求和。由于只有匹配的学生姓名对应的数值会被计算(即1乘以数值),这个函数实际上返回的是特定学生姓名在C到F列的总和。
SUMPRODUCT解法
录入公式:
I3=SUMPRODUCT((B3:B8=H3)*C3:F8)
函数释义:
思路与SUM 差不多,需要区别就是SUMPRODUCT 函数计算其参数数组间的对应元素乘积之和。在这个例子中,它将布尔数组与C3:F8范围内的数值相乘的结果求和。由于只有匹配的行(即布尔值为TRUE的行)会被计算,SUMPRODUCT 函数实际上返回的是特定条件(B3:B8等于H3)下C3:F8范围内数值的总和。
SUM+筛选的解法
录入公式:
I3=SUM(FILTER(C3:F8,B3:B8=H3))
函数释义:
FILTER 函数接收两个参数。第一个参数 C3:F8 是包含学生成绩的数据区域,这些成绩是需要被求和的数值。第二个参数 B3:B8=H3 是一个条件表达式,用于告诉 FILTER 函数只保留与特定条件匹配的数据。在这个例子中,条件是B列(学生姓名列)中的每个单元格是否与H3单元格中指定的学生姓名相等。如果B列中的某个单元格内容与H3单元格的内容相匹配,那么对应的C3:F8范围内的成绩将被保留;如果不匹配,这些成绩将被过滤掉。
SUM( ...):SUM 函数用于计算其参数中的所有数值之和。在这个例子中,SUM 函数作用于 FILTER 函数返回的数组,即只包含满足条件(B列中的姓名等于H3单元格中的姓名)的学生的成绩。
SUM+CHOOSEROWS+MATCH
录入公式:
I3=SUM(CHOOSEROWS(C3:F8,MATCH(H3,B3:B8,)))
函数解释:
MATCH 函数在这里用于查找H3单元格中的学生姓名在B3:B8范围内的相对位置。MATCH 函数返回的是匹配项在数组中的位置(返回数字3),如果没有找到匹配项,它会返回错误。
CHOOSEROWS(C3:F8, ...):函数接收两个参数,第一个参数 C3:F8 是包含学生成绩的数据区域,第二个参数是由 MATCH 函数返回的位置索引(数字3),用于选择 C3:F8 中的特定行。 将返回与该位置索引对应的行,即包含该学生成绩的行。
SUM(...):SUM 函数计算其参数中的所有数值之和。就是CHOOSEROWS 函数返回的单个行数组(学生成绩)
SUM+INDEX+MATCH
录入函数:
I3=SUM(INDEX(C3:F8,MATCH(H3,B3:B8,),))
函数解释:
首先使用 MATCH 找到H3单元格中的学生姓名在B3:B8范围内的行号,然后 INDEX 函数根据这个行号从C3:F8中返回对应的行,最后 SUM 函数计算这个行中的所有数值的总和。与CHOOSEROWS 差不多
SUM+OFFSET+MATCH
录入函数:
I3=SUM(OFFSET(B2,MATCH(H3,B3:B8,),1,,4))
函数解释:
OFFSET 函数用于从指定的起始单元格(B2)开始,根据给定的行偏移量(MATCH 函数的结果 3),列偏移量(这里是1),以及行数和列数(这里是4,表示返回4列宽的数据区域,表示4列学科,语、数、英、道)来返回一个范围。在这个例子中,OFFSET 函数将返回一个4列宽的数据区域,该区域从与H3单元格中的学生姓名匹配的行开始,向右偏移1列(即从C列开始)。最后 SUM 函数计算这个行中的所有数值的总和。
SUM+XLOOKUP
录入函数:
I3=SUM(XLOOKUP(H3,B3:B8,C3:F8))
函数解释:
这个表达式使用 XLOOKUP 函数来查找H3单元格中的值在B3:B8范围内的位置,并返回对应的C3:F8范围内的一系列值。XLOOKUP 的第一个参数是查找值“张晓婷”,第二个参数是查找的数组(B列姓名),第三个参数是返回值的数组(对应的各科成绩),最后 SUM 函数计算这个行中的所有数值的总和。
SUM+VLOOKUP
录入函数:
I3=SUM(VLOOKUP(H3,B3:F8,SEQUENCE(,4,2,1),0))
函数解释:
VLOOKUP 的第一个参数是查找值,即H3单元格中的查找值“张晓婷”;第二个参数是查找范围,即B3:F8;第三个参数是列索引号,用于指定从查找范围中返回哪一列的值;第四个参数是精确匹配或近似匹配的选项,这里使用的是0,表示精确匹配。
SEQUENCE(,4,2,1):SEQUENCE 函数用于生成一个序列数组。在这个例子中,SEQUENCE 函数的参数指定了生成数字2,3,4,5,用于VLOOKUP的第3参数;最后 SUM 函数计算这个行中的所有数值的总和。
SUM+TOCOL+IF
录入函数:
I3=SUM((TOCOL(IF(C3:F8>0,B3:B8))=H3)*TOCOL(C3:F8))
函数解释:
这个思路是先把原来的二维成绩表转换成一维表来汇总求和。如果C3:F8中的成绩都大于0,返回学科,并用TOCOL转成一列后等于查询条件,得到一个逻辑值结果是TRUE(在WPS中等同于1),否则是FALSE(等同于0)。
TOCOL(C3:F8),把所有成绩转成一列,与上面的结束相乘,得到满足查询条件的结果,最后用SUM汇总。
SUM+BYROW
录入函数:
=INDEX(BYROW(C3:F8,LAMBDA(X,SUM(X))),MATCH(H3,B3:B8,))
函数解释:
先用BYROW函数计算出每名学生成绩的汇总。
BYROW 是一个WPS新版本动态数组函数,它允许你对一组单元格的每一行应用指定的函数。参数 C3:F8 指定了一个矩形区域,该区域内包含了多行的数据。
LAMBDA 函数在这里定义了一个匿名函数,该函数接受一个参数 X。
在 LAMBDA 函数内部,SUM(X) 对参数 X 执行求和操作。这里的 X 代表了 BYROW 函数遍历 C3:F8 区域时,每一行的内容作为一个整体传递给 LAMBDA 函数。
综上所述,BYROW(C3:F8,LAMBDA(X,SUM(X))) 这个函数的作用是对 C3:F8 区域内的每一行数据分别求和,返回的结果将会是一个与原始数据区域高度相同的新数组,其中每个元素是相应行数据的和。 C3:F8 区域内是四列的数据,那么对于每一行来说,都会计算这四列数据的总和。
最后结合INDEX和MATCH来锁定位置,最后用SUM求和
以上就是全部十种解法,如有新的解决方法,留言继续补充……
304 步步详解:运用WPS函数整合工厂报表,实现全年度产品数据深度挖掘
303 运用WPS新函数实现工厂产销存报表的智能化整合与数据分析
302 BOM转换策略:树型BOM与父件子件BOM的优缺点分析及其相互转换方法
298 输入法配合WPS表格公式,轻松实现虚拟数据批量生产:助力讲师快速培训演示
297 探究二维与一维报表的奥秘:掌握三种实用转换策略,应对庞大数据量挑战
296 从理论到实践:WPS 内测SCAN函数在销售金额稳定性评估中的神奇运用
295 新旧交织,效率升级:WPS表格新函数REDUCE、BYROW等在复杂业务场景下的灵动
294 WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践
293 借助WPS动态数组公式实现智能扩展填充与数据转换自动化
运用WPS/Lambda及Scan函数实现动态数组累计需求分析 No291
巧用WPS AI,为上百名女员工打造3·8女神节专属祝福与精美卡片 290
从单一公式到复杂逻辑:通过实战案例揭示PMC面试中数据分析与解决问题的能力区分 No 289
MRP多阶需求运算报表-WPS表格版本 (7) No 287
MRP多阶需求运算报表-WPS表格版本 (6) No 286
MRP多阶需求运算报表-WPS表格版本 (5) No 285
MRP多阶需求运算报表-WPS表格版本 (4) No 284
MRP多阶需求运算报表-WPS表格版本 (3) No 283
MRP多阶需求运算报表-WPS表格版本 (2) No 282
MRP多阶需求运算报表-WPS表格版本 (1) No 281
巧用WPS中UNIQUE与SUM函数,一步解决跨门店商品库存成本合计问题 No 280
利用XLOOKUP快速实现培训未参加人员的扣分 NO 279