运用WPS新函数实现工厂产销存报表的智能化整合与数据分析

古哥计划
古哥计划

优秀创作者

在对某家工厂进行PMC生产计划培训的过程中,我们梳理了工厂的产销存相关报表,发现报表中的表格设计不够合理。具体来说,在尝试筛选某个零件号在3月第一周的发出记录时遭遇显著困难;而查阅4月的相关数据时,则需要跳转至另一个页面。若按照这样的设计,要完整查看12个月的数据,操作者将不得不逐一点击并打开12个不同的页面。

此外,表格中的填充颜色过多且杂乱无章,并且存在单元格合并的现象。在数据运算方面,该表格仅支持基本的加减乘除操作,缺乏函数运用。由于采用了二维报表设计,当需要查看月底数据时,无法在一个屏幕内完整显示,用户必须拖动鼠标选择并向右滚动才能查看到所需数据。

鉴于工厂的大多数员工都是拥有超过10年工龄的老员工,他们已经形成了固有的操作习惯,因此要让他们适应新的表格并填写数据存在一定难度。为此,古老师需要在不改变现有表格结构的基础上,设计出一种转换方法,以便于自己能够便捷地查看全年的产销存数据并进行深入分析(备注:由于该工厂信息化建设尚不完善,目前的数据主要依赖于表格记录)。

接下来,古老师开始着手设计。其设计思路主要包括将合并多页面表格数据二维表格转换为一维形式、去除并避免合并单元格、进行数据汇总等操作。

合并数据

由于数据分布在不同的表格页面上,若要进行汇总分析,首要任务就是将这些表格的数据整合到同一个表格页面中。实现这一目标的最佳方法是利用WPS中的新函数VSTACK来进行多表合并。具体操作步骤为:

第1步:复制源表中数据的列宽到新表中,这样用公式批量合并的时候能够确保和原表的列宽一致。

第2步:录入以下公式

=VSTACK('1月份:3月份 '!A2:EC32)

公式释义:

为将1月、2月和3月的报表内容合并到同一个页面中,考虑到每个页面的实际行数可能不一致,有时多有时少,因此这里建议预先设定一个较大的合并范围以确保涵盖所有数据。具体而言,我们选择预计的合并范围A2:EC32。下图中空白处为预留下的空行。

第3步:由于源表格中的月份信息分布在水平方向,并且存在合并单元格的情况,这在数据分析中是较为不利的。为了便于进行数据分析,我们需要创建一列名为“月份”的辅助列,并录入以下公式:

=DROP(SCAN(0,TAKE(C2#,,1),LAMBDA(X,Y,IF(Y="序号",1,0)+X)),1)

函数释义:

由于合并后的数据中,每个月份的数据都以其对应的序号作为标识,因此我们可以利用这个序号作为关键判断依据。具体来说,当出现第一个序号时,我们就认定其对应1月;第二个序号则对应2月,以此类推,将序号与相应的月份一一对应起来。这里利用SCAN函数就可以轻松实现下图效果:

第4步:去除无效数据

在完成“月份”辅助列的设置后,下一步就需要借助筛选功能去除无用数据。由于辅助列与已合并的数据列不在同一区域,我们需要使用函数HSTACK将它们进一步合并为一个整体以便进行筛选操作。录入下方公式合并数据成为一个统一区域:

=LET(A,VSTACK('1月份:3月份 '!A2:EC32),HSTACK(VSTACK("月份",DROP(SCAN(0,TAKE(A,,1),LAMBDA(X,Y,IF(Y="序号",1,0)+X)),1)),A))

效果如下图所示:

在筛选该区域时,我们需要选取第2列不为空的数据以及不包含关键字“序号”的数据,这是运用筛选函数FILTER进行双条件筛选的关键技巧。由于上述处理后形成的是一个整体区域,因此在执行筛选操作的同时,还需配合使用选择列函数CHOOSECOLS。请按照以下公式进行操作:

=LET(B,LET(A,VSTACK('1月份:3月份 '!A2:EC32),HSTACK(VSTACK("月份",DROP(SCAN(0,TAKE(A,,1),LAMBDA(X,Y,IF(Y="序号",1,0)+X)),1)),A)),FILTER(B,(CHOOSECOLS(B,2)<>"")*(CHOOSECOLS(B,2)<>"序号")))

效果如下图所示:

至此,我们已经成功完成了将分别存储在三个不同页面报表中的数据进行数组合并的步骤。在这一过程中,通过各种公式的运用自动去除了合并单元格、统一了填充颜色,并对月份进行了标准化处理等等。接下来,只需执行二维数据转一维数据的操作即可。

未完待续……

302 BOM转换策略:树型BOM与父件子件BOM的优缺点分析及其相互转换方法

301 深入理解订单齐套率计算方法及应用实践案例解析

300 物料需求运算表优化实战:从“卡顿”到“流畅”的转变

299 WPS表格自动化编号:升序与累计编号的实用方法

298 输入法配合WPS表格公式,轻松实现虚拟数据批量生产:助力讲师快速培训演示

297 探究二维与一维报表的奥秘:掌握三种实用转换策略,应对庞大数据量挑战

296 从理论到实践:WPS 内测SCAN函数在销售金额稳定性评估中的神奇运用

295 新旧交织,效率升级:WPS表格新函数REDUCE、BYROW等在复杂业务场景下的灵动

294 WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践

293 借助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 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序

浙江省
浏览 602
收藏
3
分享
3 +1
2
+1
全部评论 2
 
527
· 北京
回复
 
亂雲飛渡
· 广东省
回复