【雪中送炭】行业实例答疑:查找学生单科成绩
创作者俱乐部成员
困难如山,我有金山。
欢迎收看本期【雪中送炭】新专题,我是学弟。
【雪中送炭】专题,均为实际生产过程中的各行业的案例作为素材,进行实例答题,虽不一定能“药到病除”,但至少能“雪中送炭”,略尽学弟一点绵薄之力。
社区ID:清华学弟任泽岩
WPS办公软件培训讲师
- 金山办公最有价值专家(KVP)、金山办公认证讲师(KCT)、金山WPS社区创作者俱乐部成员、金山文档行业金鹰社社员、表格菁培班-函高一期毕业生、中国管理科学学会-高级企业培训师、微博认证职场博主;
- 中国南方航空公司十佳教员、金牌微课制作师,全国培联/培协推优大赛金奖获得者,《培训》杂志“师道匠心”全国金牌内训师称号获得者,两度获得Toastmasters国际演讲会International Speech Contest项目冠军。
今天我们来帮助一位同事,将源数据表的一维位数据形式,变成汇总表上所示的二维形式:
变成:
💡 | 学弟说一句题外话: 规范数据“三表结构”是WPS表格菁培班的核心纲领之一,以“1个单维源数据表+1个参数表+N个汇总表”的形式,从数据记录源头上提高表格的规范程度,打造数据记录、整理、输出、视觉化的一站式高级处理模式,极大提升表格用户的工作效率、降低数据处理的难度、减小出错概率,是每一位用户尽量遵循的指南。 |
因此本例中的“源数据表”,使用“姓名-科目-成绩”的一维的效果,是非常规范的;
而“汇总表”将根据企业需求的不同,做不同形式的处理,在本例中,生成“姓名-不同科目-不同成绩”的二维表模式。
(但现状是,有很多企业把二维表当成源数据表来使用,这种情况屡见不鲜!)
好了,回到正题。
WPS表格菁培班的另外一个核心纲领,是尽可能地拓宽接题的思路,采用不同的角度切入同一道问题。因此,在“雪中送炭”专题中,大家也看到了,学弟尽可能激发灵感,找到多种解法,为使用者提供接题思路的拓宽。本例学弟使用3个方法进行操作:
首先,我们需要先在第一列,生成姓名列,使用UNIQUE函数生成动态数组,这一步非常简单:
UNIQUE的第二参数默认为“按列”扫描数据;
UNIQUE的第三参数默认为“返回所有不同的值”;
因此,本例中,第二、第三参数均可以忽略,得到一个姓名的动态数组。
💡为方便读者阅读,无论使用哪种方法,均对本橙色单元格的G8:G11姓名列和H7:J7科目行进行单元格引用。
【法一】
法一使用两步筛选法:
使用FILTER函数筛选出某一位考生对应的所有成绩;
筛选出某一科目对应的成绩;
下拉拖拽。
这个方法非常容易理解。
俗话讲“好想的方法不好做,好做的方法不好想”,因此法一的公式步骤和长度都比较多:
第一步筛选某考生的所有成绩,使用FILTER函数,方法都是一样的;
第二步,有两种思路:
使用大家熟悉的VLOOKUP函数,查找科目,但是VLOOKUP要求被查找值必须在待查找数组的第一列,因此需要用DROP函数,将第一列姓名列舍弃;如未找到成绩值,则会返回#N/A,需要再嵌套一个IFNA函数进行屏蔽。
使用功能更为强大的XLOOKUP函数,待查找数组(科目列)在第二列、返回数组(成绩列)在第三列,因此需要用CHOOSECOLS函数,分别返回2、3列进行;如未找到成绩值,可以直接在XLOOKUP中进行屏蔽。
💡 | 由此,我们可以看出两种变化分别的优点: VLOOKUP好处是简单易懂,国民度高、上手门槛低,缺点是不能直接屏蔽错误值,需要再一次嵌套;XLOOKUP好处是功能强大,直接一个大函数就可以完成所有操作,缺点是需要写两遍完全一样的FILTER,公式显得比较长。(高手可以玩下LET新函数,解决这个问题) 不过这两个方法都有一个共同的缺点,就是不能一步到位,一次只能生成一个同学的所有成绩,需要向下拖拽。这也是【法一】的弊端。 |
【法二】
法二使用单点查找法:
使用FILTER函数,直接同时查找姓名、科目都满足条件时,对应的(唯一)成绩;
向下、向右拖拽,注意单元格锁定符号$的位置。
这个方法可以直接查找某一位同学的某一科成绩,方法十分简单,不会动态数组的同学也可以轻松上手;不过这个方法的难点在与后面的拖拽,会考察绝对引用和相对引用的知识,这也是金山KOS高级认证考试中比较常考的知识点。如果你这个$符号用得比较6,那么这个方法很好理解,也很好上手:
这个方法同样有弊端,就是只能生成单点的结果,需要向右、向下两轮拖拽。
【法三】
法三属于“两步筛选变一步筛选”法,属于一个十分讨巧的方法:
将源数据表的姓名列与科目列混合,形成一个“姓名科目”的新数组1,这个新数组是N行1列的列数组,里面的值都是唯一的;
将汇总表的姓名列与科目行混合,形成一个“姓名科目”的新数组2,这个新数组是4行3列(4名同学、3个科目,因此组合出12种结果)的数组矩阵,里面的值也都是唯一的。
使用XLOOKUP函数,以新数组1作为“待查找数组”、新数组2作为“待查找数组”、源数据表的成绩列作为“返回数组”,屏蔽空值,即可在一个函数内完成查找。
这个方法是一步到位、无需拖拽的,最为简洁。虽然不太容易想到,但是却能轻松解决“一维表转为二维表”的通用问题,学弟建议大家收藏。
下面是本次教程的练习文档链接:
【金山文档】 行业实例答疑:查找学生单科成绩
https://kdocs.cn/l/cca3Ai9uRvTB
如果你觉得今天的教程对你有帮助,欢迎在下方为我点一颗小红心💖💖💖哦!
同时,你也可以将感悟或者收获在下方留言,或者在评论区留下你的困惑,我会挑选有代表性的问题,推出更多的教程跟大家分享哦!
>>>欢迎收看【清华学弟任泽岩】WPS社区个人帖子合集<<<
https://home.wps.cn/topic/14780?chan=share&fromshare=copylink
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员