借助WPS动态数组公式实现智能扩展填充与数据转换自动化 293
2024年03月优秀创作者
最天完成前3天的答案公布，今天继续剩下2道题目。
第4题：多单条件汇总
要求：在填充颜色区域录入公式，汇总1#和2#的当日排程数量
难点：汇总的数据是一个二维数据，不是标准的一维数据，而填充黄颜色区域对应的是1月1日到1月10日的排程，需要汇总对应1号线和2号线的数量。相当于有两个条件，一个条件是线体，一个条件是日期。
实际的运用只需要用到绝对引用和相对引用的技巧，通过向右填充公式即可。
答案：
录入公式：
=SUMIFS(E5:E17,$B$5:$B$17,$D$2:$D$3)
向右填充后，就可以得到下图1的答案。
技巧：
这里用了三个技巧：
技巧1：相对引用求和区域，也就是1月1日排程这个区域，当公式向右填充的时候，就自动填充到右边日期上，如1月2日这个区域，以此类推。
技巧2：条件区域线体使用的是绝对引用，这样向右填充区域就不会变动位置了。绝对引用就有两美元符号分别在列和行号左边。
技巧3：条件这里用的动态数组写法，一次引用两个条件并且绝对锁定。$D$2:$D$3对应的是1#和2#。
图1
第5题：工序转换
要求：在填充颜色区域录入公式，用一个公式把上面的二维数据转换成一维数据，有新的图号增加的时候，能够自动扩展。题目如下图2所示
难点：二维数据转一维数据，转换过程中不能使用辅助列，还需要用一个公式解决，并且支持新数据自动推展。
图2
答案：
录入公式：
=LET(A,HSTACK(TOCOL(B3:B1000&EXPAND("",,6,""),3),TOCOL(OFFSET(B2,1,1,ROWS(TOCOL(B3:B1000,3)),6))),FILTER(A,CHOOSECOLS(A,2)<>""))
一键填充，得到下图3的结果。
图3
技巧：
用了多个技巧来实现一键自动扩展填充；
技巧1：TOCOL 配合参数3屏蔽空值实现自动推展。参考公式：TOCOL(B3:B1000,3)，到B1000的单元格范围内实现自动扩展，也就是图号新加信息的时候，也自动转成一维数据。
技巧2：EXPAND扩展6个空值，转一维的过程中为配合TOCOL函数转一维的维度，需要连接6个水平方向的空值，来实现图号与工序的维度一致，利用公式EXPAND("",,6,"")，快速得到空值，并与TOCOL连接。
技巧3：OFFSET偏移，利用ROWS判断高度（10），和固定宽度6，实现工序明细数据的显示。公式：=OFFSET(B2,1,1,ROWS(TOCOL(B3:B1000,3)),6)
图4
技巧4：利用HSTACK函数把两列数据并在一起形成一个新的数组。公式：
HSTACK(TOCOL(B3:B1000&EXPAND("",,6,""),3),TOCOL(OFFSET(B2,1,1,ROWS(TOCOL(B3:B1000,3)),6)))
如下图5所示：
图5
技巧6：定义名称A，配合筛选函数去除空值，上面的数据包含空值，把上面的结果定义为A，配合筛选函数录入：
=FILTER(A,CHOOSECOLS(A,2)<>"")
最终得到下图结果：
最后总结：
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
277 超预期，原来WPS AI也可以写出复杂的多层嵌套公式
276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形
275 录入查询产品型号的首字，提取对应首字相同的所有型号，并按销量降序
2024年03月优秀创作者