借助WPS动态数组公式实现智能扩展填充与数据转换自动化 293

古哥计划
古哥计划

创作者俱乐部成员

最天完成前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 更新动态数组公式后,可以改变思路。以前如果增加了数据,而公式没有填充的话需要继续手动填充。在有动态数组的基础上,提前把需要更新数据的单元格范围引用写到动态数组公式上面,这样就不会因为数据有增加而继续需要手动填充公式了。

这样就减少了工作量,间接也提高了工作效率。

292 MRP原理课后的表格制作练习题目:技能与实战双丰收

运用WPS/Lambda及Scan函数实现动态数组累计需求分析 No291

巧用WPS AI,为上百名女员工打造3·8女神节专属祝福与精美卡片 290

从单一公式到复杂逻辑:通过实战案例揭示PMC面试中数据分析与解决问题的能力区分 No 289

MRP多阶需求运算报表-WPS表格版本 (8 No 288

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

278 挑战WPS AI 写出复杂的多层嵌套公式

277 超预期,原来WPS AI也可以写出复杂的多层嵌套公式

276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形

275 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序

表格中录入简称查找包含全称的所有数据 No.274

如何快速提取不同单元格的内容,并汇总到一列 No 273

如何统计指定年和月后,汇总对应产品的销售数据 No272

表格中在一个单元格内有多个条件,如何快速求和 No271

WPS新函数:巧妙利用DROP 去重UNIQUE函数留下的“0” NO 270

快速计算出每名员工参与了几个项目No.269

统计每个城市的唯一商品明细No.268

WPS更新后的TAKE函数轻松实现动态求和 No 267

WPS 新函数 EXPAND 实现工单快速分拆 No 266

浙江省
浏览 176
2
4
分享
4 +1
8
2 +1
全部评论 8
 
QQ
打卡
· 河南省
回复
 
云云星羽
哪个版本呀,为什么金山文档和手机版本还没有更新
· 上海
回复
 
陈小飞
打卡学习
· 浙江省
回复
 
饼干头
学习
· 四川省
回复
 
漩涡卡洛特
学习
· 北京
回复
 
清华学弟任泽岩
清华学弟任泽岩

社区优秀创作者

打卡学习
· 广东省
回复
 
ChenYes
学习了
· 浙江省
回复
 
HC.旋
学习了
· 江苏省
回复