如何把多个列数不定的 Excel 合并成汇总表

 

例题描述

有电镀检验报表.xlsx,当月每日的检验数据各存在一个sheet中,从F列开始是电镀缺陷类型,各sheet中的缺陷类型和列数不完全相同。1116日数据如下图所示:

..

现在需要用这些每日数据做汇总报表,按日期汇总统计每天的投入数、合格数、合格率,列出所有的缺陷种类每天发生的次数,目标报表如下图所示:

..

这个汇总的棘手问题是每日数据中缺陷种类列数不定,数据结构不相同,需要对每天的数据做合并处理。解决的思路是先把每日数据转置为行式,形成检验日期、缺陷种类、缺陷数共三列的数据,如下图。最后把这些数据合并到一起做交叉分组统计表。

..

 

实现步骤

1、  运行集算器

可去润乾官网下载职场版,同时下载一个免费授权就够了,首次运行时会提示加载。

2、  编写脚本:

..

把代码列出来看得清楚点:


A

B

1

=file("E:/电镀检验/电镀检验报表.xlsx").xlsopen()

2



3

for A1

=A1.xlsimport@t(;A3.stname,2)

4


=B3.select(规格)

5


=B4.run(检验日期=string(B4(1).检验日期))

6


=B5.groups(检验日期;sum(投入数):投入数)

7


>A2=A2|B6

8


=B5.pivot@r(检验日期;缺陷种类,缺陷数)

9


=B8.select(缺陷种类!="规格" && 缺陷种类!="投入数" && 缺陷种类!="合格数" && 缺陷种类!="合格率" && 缺陷种类!="备注")

10


>B2=B2|B9

11

return   A2,B2


A1   打开电镀检验报表.xlsxExcel对象

A3   循环读取A1中的Excel对象的每个sheet

B3   读取当前循环的sheet数据,参数2表示从第2行开始读,选项@t表示所读的第1行是列标题

B4   只选出规格列有值的数据行

B5   设置每行的检验日期为第1行的检验日期,并转成字符串

B6   按检验日期分组,统计每日投入数总和,命名列名为投入数

B7   把每日投入数依次保存到A2单元格

B8   以检验日期为分组,对B5的数据集进行转置,选项@r表示列转行,转换后的新列名命名为缺陷种类、缺陷数。转换后的数据如下图所示:

B9   筛选出缺陷种类不是规格、投入数、合格数、合格率、备注的行,意即只留下缺陷数据。

B10   B9中的数据依次保存到B2单元格

A11   返回A2B2两个数据集供报表使用

 

3、  运行报表设计器

可去润乾官网下载职场版,同时下载一个免费授权就够了,首次运行时会提示加载。

4、  新建报表:

新建报表后,按F11弹出新建数据集窗口,如下图:

 ..

 

按增加按钮,选择脚本数据集,再按确定按钮,弹出下图窗口,把在集算器中调试好的脚本复制粘贴到窗口中

..

脚本代码写好以后,按确定按钮回到新建数据集窗口,由于脚本返回了两个数据集,所以要将新建的数据集名称改成两个,如下图所示,改为ds1,ds2

..

按上图中的确定按钮后,回到新建报表界面,设计一个如下图所示的报表(报表文件可下载附件得到)

..

A3   纵向扩展,表达式为:=ds2.group(检验日期;检验日期:1)

B3   表达式为:=ds1.select@1(投入数,检验日期==A3)

C3   表达式为:=B3-sum(E3{})

D3   表达式为:=C3/B3*100

E2   横向扩展,表达式为:=ds2.group(缺陷种类;缺陷种类:1)

E3   表达式为:=ds2.sum(缺陷数),设置显示值表达式为:if(value()>0,value(),""),当统计值为0时显示为空

B4   表达式为:=sum(B3{})

C4   表达式为:=sum(C3{})

D4   表达式为:=C4/B4*100

E4   表达式为:=sum(E3{})

 

5、  F9预览报表,然后选择菜单:文件--导出—Office2007--Excel文件,将报表导出为excel文件。

 

也可以按规格实现汇总统计,实现步骤和原理与按日期汇总基本类似。

 

 

【附件】 ddjy.zip