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

古哥计划
古哥计划

创作者俱乐部成员

在PMC的日常工作中,工作人员频繁地与各类表格进行交互,每日需处理的数据量极为庞大。面对诸如一维报表和二维报表等各种数据报表,常常需要在这两种不同类型的报表之间进行相互转换,而实现这一转换的方法多样。今日,古老师特别将重点放在介绍二维报表转换为一维报表的三种常用方法上,这三种方法各自具有独特的特点。转换前学了解学习这两类报表的定义和优缺点。

报表定义

一维报表:

指的是一种扁平化的数据组织形式,其中每条记录包含一组完整的相关信息,并且这些信息沿着单一线性轴排列。在表格中,一维报表可以表现为一行记录(即一列)或者一列记录(即一行),每个记录的字段彼此独立,不需要通过与其他列的关系来解读。

优点为是数据格式紧凑,有利于数据导入导出和程序处理,缺点是对于涉及多个变量或分类的数据展示能力有限,无法直观反映复杂关系。也不适合做横向对比分析

二维报表:

表现为传统的表格形式,拥有行和列两个维度,其中行用来表示不同的记录,列用来表示记录的不同属性或指标。在一个二维报表中,每一列代表一个特定的字段(如部门、姓名、月份、销售额等),每一行则包含了一个实体(如员工、产品)的所有相关属性值。

优点:结构清晰,能同时展示多个变量之间的关系,便于多维度数据分析。可视化效果良好,用户可以快速理解并对比不同类别的数据。

缺点:当数据量过大时,展示和阅读可能变得困难。数据结构相对复杂,若设计不当,可能会出现信息冗余或混乱的情况。

方法一:

这个方法是最容易理解的,如果没有空值的话不需要用到其它函数,它就是二维转一维的专用函数TOCOL。

分别在以三个单元格中录入以下函数:

H3=TOCOL(B3:B5&C1:F1)

I3=TOCOL(C3:F5)

J3=--TOCOL(C2:F2&A3:A5)

函数释义:

H3和J3通过与文本连接符号“&”连接对应的水平空值,与垂直空值形成一个新的二维数组,最后用TOCOL转成一列。需要注意的是,两个空值需要和高度和宽度与二维数组的高度和宽度一致(C3:F5)。

效果如下图1所示:

图1

这里有0值,如果需要筛选去除0,继续用函数HSTACK合并,合并后再筛选不为空的数据。如果二维数据里没有空值,这一步骤可以不用。

录入公式:

=LET(A,TOCOL(C3:F5),FILTER(HSTACK(TOCOL(B3:B5&C1:F1),A,--TOCOL(C2:F2&A3:A5)),A>0))

效果如下图2所示:

图2

方法二

这种方法存在潜在风险,当所处理的数据总量超过了WPS表格中单个单元格所能容纳的最大字符数——即32,767个字符时,将会导致错误发生。这是因为该方法的本质是先将数据通过特定的关键符号拼接至一个单元格内,随后利用分列函数按照该关键符号将数据拆分开来,最后借助TOCOL函数的功能将分列后的数据转化为一列,以此实现从二维报表向一维报表的转换过程。

第1步:

录入公式:

=B3:B5&"-"&C2:F2

函数释义:

行和列相互连接,形成一个新的二维数组,这个数组与二维数组中C3:F5是一个的高度与宽度;

第2步:

录入公式:

=TEXTJOIN("#",,B3:B5&"-"&C2:F2)

函数释义:

把这个二维数组合并到一个单元格,用符号“#”。

第3步:

录入公式:

=TEXTSPLIT(TEXTJOIN("#",,B3:B5&"-"&C2:F2),"-","#")

函数释义:

通过函数进行分列,其中列用符号“-”,这里分成了两列,行用符号“#”,这样就实现了分行。

效果如下图3所示:

图3

有了上面思路的话,就可以用HSTACK连接用TOCOL转换C3:C5的一列,形成了一个新的数组,最后定义此名称为A,用固定模板(FILTER(A,CHOOSECOLS(A,2)>0))筛选不为0的数组。

此方法不推荐,只提供一种思路。

录入以下公式:

=LET(A,CHOOSECOLS(HSTACK(TEXT(TEXTSPLIT(TEXTJOIN(",",,B3:B5&"-"&C2:F2),"-",","),"M/D"),TOCOL(C3:F5)),1,3,2),FILTER(A,CHOOSECOLS(A,2)>0))

效果如下图4所示:

图4

方法三:

此方法是推荐的方法,不需要额外连接垂直与水平方向对应的空单元格,只需要换一个思路用IF来判断就可以了

第一步:

录入公式:

=IF(C3:F5="",NA(),B3:B5)

函数释义:

如果C3:F5等于空,就返回错误,否则就返回垂直方向的数组

第二步:

录入公式:

=TOCOL(IF(C3:F5="",NA(),B3:B5),3)

函数释义:

把上面的二维数组转成一组后,用参数屏蔽错误。

效果如下图5所示:

图5

同时分别把日期与数据按此思路加上去,分别录入以下公式:

数量:TOCOL(C3:F5,3)

日期:TOCOL(IF(C3:F5="",NA(),C2:F2),3)

最后用HSTACK转成一个公式:

=HSTACK(TOCOL(IF(C3:F5="",NA(),B3:B5),3),TOCOL(C3:F5,3),TOCOL(IF(C3:F5="",NA(),C2:F2),3))

效果如下图6所示:

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

表格中录入简称查找包含全称的所有数据 No.274

如何快速提取不同单元格的内容,并汇总到一列 No 273

如何统计指定年和月后,汇总对应产品的销售数据 No272

浙江省
浏览 162
收藏
5
分享
5 +1
1
+1
全部评论 1
 
Mr Chen
Mr Chen

11月优秀创作者

学习
· 甘肃省
回复