如何把多个列数不定的 Excel 合并成汇总表
例题描述
有电镀检验报表.xlsx，当月每日的检验数据各存在一个sheet中，从F列开始是电镀缺陷类型，各sheet中的缺陷类型和列数不完全相同。11月16日数据如下图所示：
现在需要用这些每日数据做汇总报表，按日期汇总统计每天的投入数、合格数、合格率，列出所有的缺陷种类每天发生的次数，目标报表如下图所示：
这个汇总的棘手问题是每日数据中缺陷种类列数不定，数据结构不相同，需要对每天的数据做合并处理。解决的思路是先把每日数据转置为行式，形成检验日期、缺陷种类、缺陷数共三列的数据，如下图。最后把这些数据合并到一起做交叉分组统计表。
实现步骤
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 打开电镀检验报表.xlsx为Excel对象
A3 循环读取A1中的Excel对象的每个sheet
B3 读取当前循环的sheet数据，参数2表示从第2行开始读，选项@t表示所读的第1行是列标题
B4 只选出规格列有值的数据行
B5 设置每行的检验日期为第1行的检验日期，并转成字符串
B6 按检验日期分组，统计每日投入数总和，命名列名为投入数
B7 把每日投入数依次保存到A2单元格
B8 以检验日期为分组，对B5的数据集进行转置，选项@r表示列转行，转换后的新列名命名为缺陷种类、缺陷数。转换后的数据如下图所示：
B9 筛选出缺陷种类不是规格、投入数、合格数、合格率、备注的行，意即只留下缺陷数据。
B10 把B9中的数据依次保存到B2单元格
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
