步步详解:运用WPS函数整合工厂报表,实现全年度产品数据深度挖掘

古哥计划
古哥计划

创作者俱乐部成员

在成功地整合了分别存储于三个独立页面报表中的数据(具体操作可参考《运用WPS新函数实现工厂产销存报表的智能化整合与数据分析》一文),我们还需对这些数据进行进一步加工以完成对产品各月的深度分析。合并阶段仅仅是将各个月份的二维数据报表叠加到一起,接下来的关键步骤是对这些二维数据进行转换,将其转化为一维数据格式。

分析原表结构

在进行转换之前,我们需要先剖析原始表格的二维结构。从图表中可以看到,在标记为1的位置显示的是水平方向上的第二行数据,它代表的是日期信息;而在标记为2的地方,则显示出第3行固定不变的数据,该行连续四个单元格的内容分别是“收入”、“发出”、“报废”和“结存”,这四项内容呈规律性重复出现。

前面在合并并添加了月份辅助列之后,数据已涵盖全年各个月份的信息,因此在水平方向上的日期信息中的月份部分就不再有效。此处可以利用函数生成从1到31的数字序列,代表相应的天数。至于第3行由于其具有规律性,我们仅需使用函数一次性生成31乘以4,即总共124个单元格的循环数据即可满足需求。

创建日期辅助

创建日期对应天数的循环,录入以下公式:

=TOROW(INT((SEQUENCE(4*31)-1)/4+1))

函数释义:

SEQUENCE(,4*31-1):序列生成后,整体减去1,这样原本从1开始的序列变成了从0开始。

(...)/4+1:将上述序列的每个数除以4后再加1并取整。这样操作后,每4个数一组,将会分别得到1、2、3和4(因为 (0/4 + 1 = 1), (1/4 + 1 = 1.25) ... (3/4 + 1 = 1.75) 舍入后变为1至4)。

最后用TOROW转成行的显示方式,形成数组中的元素依次是1到4循环排列,共计31个周期,代表31天。

这一步操作也同时完成了合并单元格取消。效果如下图所示:

创建收入发出辅助

创建收入发出辅助的循环与刚刚的日期不一样,需要配合选择列函数来进行,录入以下公式:

=CHOOSECOLS({"收入","发出","报废","结存"},MOD((SEQUENCE(4*31))-1,4)+1)

函数解释:

上面的公式难点在后面选择列的参数,分步解释:

(SEQUENCE(4*31))-1:先生成从1开始到124的序列,然后整体减去1,使得序列从0开始。

MOD(..., 4):MOD函数用于计算前述序列中每个数除以4的余数。例如,对于0到123的数字,它们除以4的余数会形成一个循环序列0, 1, 2, 3, 0, 1, 2, 3, ..., 0, 1, 2, 3。

MOD(..., 4)+1:将上述计算出的余数结果加1,因此余数序列变成了1, 2, 3, 4,继续循环。

函数会生成一个长度为1234的序列,其中的值按1、2、3、4的顺序循环填充。这可以用于创建一个周期性的序列。

理解为重复选择第1列、第2列,一直重重4*31次;效果如下图:

创建一维数据

通过先前针对日期以及收入、发出等数据所进行的两项调整步骤,现已成功将原始表格整理为符合标准的二维数据格式。接下来,只需运用TOCOL函数配合 IF 判断语句,即可高效地将二维数据转换为一维数据。 关于公式的原理基本相似,针对日期部分,我们运用了日期函数DATE,将辅助的天数和月份数字分别进行转换,以生成符合标准格式的日期。请分别输入以下相关公式:

日期:

=DATE(2024,TOCOL(IF(S3:EL32>0,I3:I32,NA()),3),TOCOL(IF(S3:EL32>0,S1#,NA()),3))

零件号:

=TOCOL(IF(S3:EL32>0,K3:K32,NA()),3)

物料名称:

=XLOOKUP(C3#,K:K,L:L)

分类:

=TOCOL(IF(S3:EL32>0,S2#,NA()),3)

数量:

=LET(A,TOCOL(S3:EL32,3),FILTER(A,A<>0))

效果如下图所示:

至此,一份标准的一维报表就已经制作完成。接下来,我们可以极其便捷地进行各种数据分析操作,例如按月分析、按周分析,或者进行类似销售PQ分析的排序分析等。

数据分析:

鉴于工厂目前库存较高,需要密切关注每日的发出数据,不仅包括当月数据,还包括历史数据。为此,我们借助上述转化后的一维数据,采用合适的公式创建了一个模板,实现了数据间的联动效应。这样一来,也确保了不会改变工厂工人现有的数据录入方式。

分别录入以下公式:

零件号:=UNIQUE(C3#)

物料名称:=XLOOKUP(EN3#,C3#,D3#)

日期:=TOROW(UNIQUE(FILTER(B3#,E3#=EN1)))

汇总:=SUMIFS(F3#,C3#,EN3#,B3#,EP2#,E3#,EN1)

这样就把2024年的发出数据进行了汇总,方便查看,效果如下图所示:

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

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实现出货货柜合并的表格变形

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