公式实现多表格数据汇总-filter+unique+sort+torow的组合运用

数据透视表格在实际使用中存在一下几个弊端

  1. 数据源表更新后,透视表需手动点击刷新后才能更新数据

  1. 引用透视表数据进行计算时必须使用“插入计算字段”功能,不能直接通过在单元格中录入公式进行计算

  1. 多数据源表格数据透视汇总无法实现,尤其在数据表行标题不统一的情况下无法实现透视表格功能。

在上述情况下,结合个人有限能力,提出一个filter+unique+sort+torow的组合运用方案。

探讨案例

在实际业务情况下,需要把不同表格的不同数据汇总在一个总表中进行汇总分析,借用我所在行业汽车销售行业,对单车(个别计价法)进行收入成本费用的汇总,及毛利计算,以及汇总数据的看板制作。

如图需要汇总已经销售或已经回款的车辆收入成本数据,未销售未回款的数据不需要体现:

销售明细表中的销售信息

回款明细表中的累计回款金额

车辆采购表中的车辆采购成本

费用明细表中的车辆费用

需要计算计算列车辆毛利、毛利率、是否收齐销售款,如图:

看板数据月度销量销售额毛利、业务员销售情况

汇总表表头列抓取

表头需要提取销售明细表中的品牌、车架号,回款明细表中的品牌、车架号,费用表中的品牌、车架号,并根据品牌排序。

公式思路:

  1. 通过VSTACK()函数合并三个表中的品牌车架号(图中高亮重复值)

  1. 通过unique()函数对数据去重

  1. 通过sort()函数对数据排序

销售情况数据提取

采用filter()函数提取相应车架号的销售金额

  1. 销售表销售日期和金额不相邻,使用choosecols函数提取数据列,再同filter()提取销售日期及金额

  1. 日期列提取格式改为短日期,并向下填充

  1. 空白数据显示错误#CALC!,通过设置filter()第三个条件,设置错误返回值

回款信息、成本信息数据提取

使用UNIQUE()+FILTER()对回款人进行提取,使用sum()+filter()对回款金额进行求和提取,并向下填充

同理采用使用sum()+filter()对费用成本进行求和提取,并向下填充

车辆成本不存在重复项,可以直接采用vlookup()提取,如有重复项也可选用sum()+filter()对数据提取。如暂估入库、暂估退库又重新入库,或者多批次入库情况。

成本小计、毛利、毛利率计算

直接录入公式计算成本小计、毛利、毛利率。在这里可以通过数组公式进行录入

增加IF()函数,对未销售车辆数据进行替换

收款明细数据抓取

采用filter()对单车收款明细进行提取,再通过TOROW()转换为横向展示

附言:

一般多表格合并多时不同月份的同格式表格合并,在实际工作使用环境中,局限于数据的统计汇总,不利于数据加工处理。

使用filter函数可以将通过搭配其他公式实现数据的去重unique(),字符合并textjion(),求和sum(),计数counta()等基本满足一个数据透视表的常用功能。

但是filter()函数在使用过程中,尤其表格中存在多个filter()函数时,经常计算卡顿,缓慢。另外filter()不支持#数组引用(新出的map()函数似乎可以解决数组引用问题,目前还没有尝试)

我在这里暂做抛砖引玉,也希望各位表哥表姐们多多指导。

最后目前使用的wps版本为17158内存版本,系统版本为win11

天津
浏览 939
3
12
分享
12 +1
4
3 +1
全部评论 4
 
LI XIRUI
已学习
· 四川省
回复
 
1231393578237
学习
· 四川省
回复
 
1231393578237
· 四川省
回复
 
1231393578237
· 四川省
回复