在成功地整合了分别存储于三个独立页面报表中的数据(具体操作可参考《336运用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年的发出数据进行了汇总,方便查看,效果如下图所示:
图文看不明白,每天晚上:20:00-20:30
抖音关注 “古哥计划”,古老师直播讲解
我是古哥计划,专注生产计划18年,头条号作者,职场问答专家,优质职场领域创作者。关注我,每天学习PMC相关知识。