告别杂乱:基于WRAPROWS与REDUCE的二维项目跟进表高效重整

古哥计划

优秀创作者

项目管理跟进表转换分享案例:原某工厂使用的项目跟进表设计为二维结构,其特点是将各项目的开票信息沿水平方向排列。具体而言,每一行代表一个项目,其开票时间与金额以列形式依次呈现,如“第一次开票时间”、“第一次开票金额”、“第二次开票时间”、“第二次开票金额”,以此类推,最多记录一个项目的前10次开票情况。

现需将其调整为以时间为主线的一维跟进表,即按照开票时间的先后顺序逐行记录每个项目的开票信息。新的表格将以“项目”、“开票时间”和“开票金额”作为固定的列标题,确保数据清晰有序,便于查阅与管理。

具体的模拟转换效果如下图:

需求分析

经过初步分析,该需求实质上涉及将二维项目跟进表转化为一维表。进一步审视细节,我们注意到开票信息部分包含每个项目的10次开票记录,分别对应“开票时间”与“开票金额”。这意味着在转化过程中,这10组时间与金额数据需各自独立成列,即“开票时间”与“开票金额”应各有10列对应各自的10次记录。

显然,采用原有函数TOCOL(转置到列)的方法进行转换并不合适,因其会导致数据维度错乱,无法准确对应每一次的开票时间和金额。因此,我们需要寻求其他更为恰当的方法来实现这一二维至一维表的精准转换。

此处可选用一个适用于此类场景的函数——WRAPROWS,其功能是将一维数组按指定行数转化为二维数组。鉴于“开票时间”与“开票金额”始终为两个固定字段,我们可直接运用此函数,按每行包含这两个字段的方式进行转换。如此操作,即可便捷地将数据整理为符合要求的标准一维格式。

完成上述转换后,为进一步整合所有项目的开票记录,可搭配使用REDUCE函数进行多项目数据的堆叠操作。如此,便能高效地将所有项目的开票信息统一转换为一维数据形态,便于后续管理和分析。

一维转二维

在正式应用REDUCE函数进行堆叠操作之前,我们首先应明确其第一个Y值对应的函数计算结果。也就是写第出第一个一维转二维的函数公式。录入以下公式:

=WRAPROWS(F3:Y3,2)

函数释义:

该公式利用WRAPROWS函数将F3:Y3范围内(涵盖10次开票日期与金额)的数据按每行2列进行重新排列。若某个项目不存在相应的开票记录,对应位置将显示为空。实际效果如附图所示。

去除空行

为避免在后续使用REDUCE函数进行堆叠时引入大量空值,对于存在未开票记录的项目,需先行去除相关空值。为此,我们可结合筛选函数进行处理,输入以下公式:

=LET(a,WRAPROWS(F3:Y3,2),FILTER(a,CHOOSECOLS(a,1)<>0))

函数释义:

首先,通过LET函数将上述一维转二维的结果(即包含10次开票日期与金额的重新排列数据)定义为变量a。接着,运用FILTER函数对变量a进行筛选,条件设定为选取其中第一列不为空的所有数据行。执行此操作后,效果图如下所示。

拼接项目

在已筛选去除空值的数据基础上,还需将项目合同、项目名称、履约部门等信息与之关联。为此,我们可以运用HSTACK函数结合IFNA函数实现信息的合并,输入以下公式:

=LET(a,WRAPROWS(F3:Y3,2),b,B3:E3,IFNA(HSTACK(b,FILTER(a,CHOOSECOLS(a,1)<>0)),b))

函数释义:

首先,依旧通过LET函数将一维转二维的结果定义为变量a。其次,设定变量b为B3:E3区域,该区域包含项目合同、项目名称、履约部门等信息。接着,使用HSTACK函数尝试将b与筛选后的a(即不含空值的数据)水平拼接成一个数组。由于两数组维度可能不一致导致拼接时出现错误值,故在此过程中配合IFNA函数,将可能出现的错误值替换为变量b的相应内容。最终,成功实现两部分数据的拼接,效果如下图所示。

转换Y值

在运用REDUCE函数进行堆叠操作前,由于其Y值参数不支持直接接收范围,我们需要将类似F3:Y3、B3:E3的范围值转换为单一单元格引用形式。为此,可借助OFFSET函数进行适配。将原公式中的B3:E3与F3:Y3分别替换为:

把B3:E3用=OFFSET(A3,,5,,20)替换;

把F3:Y3用=OFFSET(A3,,1,,4)替换;

替换后的公式如下:

=LET(a,WRAPROWS(OFFSET(A3,,5,,20),2),b,OFFSET(A3,,1,,4),IFNA(HSTACK(b,FILTER(a,CHOOSECOLS(a,1)<>0)),b))

此公式中,OFFSET函数已根据指定偏移量对A3单元格进行定位,以适应REDUCE函数的要求。经过替换与调整后,该公式能够实现所需的数据处理与拼接效果,如下图所示。

堆叠数据

在完成上述转换后,即可使用REDUCE函数进行堆叠操作。考虑到当前正式版本尚未包含此函数(预计6月1日前更新),以下公式仅作学习参考:

=REDUCE(B2:G2,A3:A10,LAMBDA(X,Y,VSTACK(X,LET(A,WRAPROWS(OFFSET(Y,,5,,20),2),B,OFFSET(Y,,1,,4),IFNA(HSTACK(B,FILTER(A,CHOOSECOLS(A,1)<>0)),B)))))

虽然当前版本无法直接运行此公式,但待REDUCE函数正式发布后,上述公式将能有效实现所描述的数据堆叠过程。预期效果如附图所示。

最后,函数通过REDUCE函数配合LAMBDA、LET、IFNA、VSTACK、WRAPROWS、OFFSET、FILTER、CHOOSECOLS等多个函数,实现了对A3:A10范围内项目行的开票信息与附加信息的整理、筛选和堆叠操作,最终生成一个包含所有项目详细信息且按特定格式排列的新数据区域。

324 告别手动更新:WPS动态数组公式助力项目跟进表实现全自动化统计

323 WPS AI:一站式解决方案,让复杂演示汇报轻松化

322 多表并一,费用归类:WPS VSTACK函数助力PMC高效整合项目开支

321 从复杂到清晰:PMC如何优雅处理合并单元格,高效实现销售排名

320 PLM系统上线前奏:智能化模板破局BOM物料重复难题

319 XLOOKUP赋能动态联动菜单:轻松实现多级数据筛选与更新

318 整合WPS新函数:REDUCE、LAMBDA与VSTACK,构建高效BOM整理解决方案

317 【案例解析】信息化系统BOM数据不规范?看PMC如何化繁为简

316 PMC实战分享:如何应对大型零部件逐日管控难题与WPS表格模板设计

315 巧用表格函数优化销售业绩分配:提升奖金计算效率与准确性

314 巧用WPS 新函数:无辅助列情况下处理合并单元格查询统计难题

313 破译订单堆叠难题:WPS SCAN函数在PMC生产计划中的应用实例

312 PMC的日常挑战:如何用WPS表格精准抓取单元格内的数字信息

311 零件产销存报表难题:不规范数据格式与跨页数据整合的困局与破局

310 WPS最新函数技巧:精准定位与智能舍入,罗马数字秒变阿拉伯数字

309 PMC精益库存管理与订单分配自动化设计WPS一站式报表模板

308 工厂信息化升级实战:巧解无规律物料编码,动态提取字母组合

307 一招GET!借助通配符解决表格数据汇总难题:SUMIF函数实例详解

306 一键生成:RANDARRAY 函数在数据分组与数学作业个性化定制中的妙用

305 实战演练:10种创新策略解锁WPS表格函数在成绩查询中的高效应用

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

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

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

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

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

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

浙江省
浏览 604
1
4
分享
4 +1
1
1 +1
全部评论 1
 
学习到了, 尤其是这一句"在运用REDUCE函数进行堆叠操作前,由于其Y值参数不支持直接接收范围,我们需要将类似F3:Y3、B3:E3的范围值转换为单一单元格引用形式。为此,可借助OFFSET函数进行适配。"
· 河南省
回复