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

2024年03月优秀创作者

在成功地整合了分别存储于三个独立页面报表中的数据（具体操作可参考《运用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))

效果如下图所示：