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

古哥计划

优秀创作者

某工厂的PMC接到销售要求,原来每天需要出6个货柜的计划现在变更成每天出3个货柜,这3个货柜不是直接减少,而是把原计划中的4号货柜到6号货柜自动换行到货柜1号到3号的下方,日期累加。

希望设计一个函数公式,实现表格的一键变形转换

效果如下图1所示:

图1

需求分析

分析一下这个需求,从源数据中观察,可以发现这是一个对等的数据区域,也就是把货柜1到货柜6从中间分开,并累加。累加好的同时还需要按日期排序,这样就实现了上述问题的表格变形转换。

根据这个需求立即想到WPS更新的新函数VSTACK和HSTACK,用这两个函数可以实现数组重新堆叠,完成后再用排序函数SORT对日期排序就可以了。

货柜垂直合并

货柜1号到3号前面是有日期的,而货柜3号到6号前面是没有日期的,如果直接垂直合并就会导致没有日期,从而无法进行按日期排序,所以合并前录入水平合并函数HSTACK:

录入公式:

=HSTACK(B3:B10,F3:H10)

公式释义:

把两个区域进行合并(水平方向)

效果如下图2所示:

图2

水平方向合并完成后,就需要垂直方向合并了,这次的合并函数是VSTACK:

录入公式:

=VSTACK(B3:E10,HSTACK(B3:B10,F3:H10))

公式释义:

把两个区域进行合并(垂直方向)

效果如下图3所示:

图3

按日期排序

通过上面的两次区域合并,还不能算成功,还需要要进行排序,排序的目的是把同一天的日期连在一起,这样就实现了源数据中一天出6个货柜的一行,变成一天出3个货柜的二行。

录入函数:

=SORT(VSTACK(B3:E10,HSTACK(B3:B10,F3:H10)))

函数释义:

SORT函数如果不录入第二参数,代表默认排序的数据为第一列(日期),排序的方式为升序(从小到大)。这里用的是直接法,相当于把日期进行升序排序,这样因为日期大小的原因,排序完成后就实现日期相同的在一起了。

效果如下图4所示:

加上标题

如果标题选择的手工录入的话,到上一步已经完成了表格转换变形的需求了,这里为了让大家更加好理解VSTAKC和HSTACK,继续在嵌套一层标题。

录入函数:

=VSTACK(B2:E2,SORT(VSTACK(B3:E10,HSTACK(B3:B10,F3:H10))))

函数释义:

把B2:E2,也就是源表中的标题按垂直方向合并到排序后的结果中。

效果如下图5所示:

最后总结

VSTAKC和HSTACK这两个函数的应运场景非常多,最为常见的就是区域重组,可以进行任意方向的合并,如上面的案例中的水平合并,垂直合并。

合并不仅仅是区域,有时候不想在不同的单元格录入多个函数,可以用这两个函数进行函数合并,从而实现一个“大”的公式,这样的效果就是表格函数建模中的“一键转换”,也就是一个公式搞定需求。后续有需求变化的时候,只需要在这个公式中变更就可以了。

和古哥一起学习PMC生产计划运营,一辈子够不够?

关注古哥计划

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

WPS 新函数 CHOOSECOLS 快速对齐错乱列 No 265

WPS新函数LET让公式的长度大大的简化了. No.264

自动分配客户对应业务的奖金No.263

快速查询出销售前2的销售员和销售金额并排序 No.262

WPS 新函数 WRAPROWS 把错乱的数据整理成标准二维数据 NO 261

多表指定日期与时间的生产数据查询No.260

WPS 新函数 TOCOL将二维数组转化成一行【No.259】

WPS 新函数 VSTACK 多表查询合并【No.258】

257 一招搞定请假时间的区间转换及人数统计

256 快速匹配不同的采购量对应的结算量

255 WPS新函数案例:复杂的产品欠料运算

254 WPS新函数案例:灵活多变的万年日历

广东省
浏览 1037
收藏
6
分享
6 +1
4
+1
全部评论 4
 
打卡
· 广东省
回复
 
也可以这样 =LET(日期,B3:B10, 数据,C3:H10, 标题,B2:E2,   VSTACK(标题, HSTACK(TOCOL(CHOOSECOLS(日期,1,1)), WRAPROWS(TOCOL(数据),3))))
· 河南省
回复
古哥计划

优秀创作者

这个思路非常不错,感谢您的支持
· 广东省
回复
 
清华学弟任泽岩

创作者俱乐部成员

学会了!
· 辽宁省
回复