如何提取多个 Excel 中的格值合并汇总
我们经常要汇总多个 Excel 的数据,Excel 来源可能是由不同部门(或下级单位)上报的,如各门店销售情况;也可能是由不同时段产生的,如每个月的销售数据。这些 Excel 的结构格式一模一样,要得到汇总结果就需要归并,可能是简单求和,也可能按分类汇总。
解决这类问题的思路一般需要遍历多个文件后读入进行汇总,文件数不固定和格式不规整都会增加难度。
例题描述
现有 12 个月的报表存放在 1-12 月的文件夹中,每月 Excel 名称相同,如“1 月 / 报表.xls”,“2 月 / 报表.xls”,现在要生成各月报汇总数据。
文件存放结构:
月报数据,如 1 月 / 报表.xls 内容:
目标汇总表:
实现步骤
1. 下载集算器职场版,官网就能找到,
2. 安装后运行软件,会提示加载授权,也是在官网下载,免费版足够。
3. 在集算器中编写脚本如下:
列出来看得清楚点:
A |
B |
|
1 |
D:/ 销售 |
=create(月份, 数量, 金额, 螺纹销量, 线材销量) |
2 |
for 12 |
=file(A1+"/"+string(A2)+"月 / 报表.xls").xlsimport@t() |
3 |
>B1.insert(0,string(A2)+"月",B2(3). 数量,B2(3). 金额,B2(1). 数量,B2(2). 数量 ) |
|
4 |
=file(A1+"/ 汇总表.xls").xlsexport@t(B1) |
A1:文件上层路径,可以自己改成别的
B1:结果表,后面数据会填充到这里
A2:循环 12 次,分别处理每个 Excel 文件
B2:循环内,使用 file 函数读取 1-12 月 Excel 数据
B3:循环内,将每个月的数据插入结果表(B1)中,其中“B2(3). 数量”表示读取第 3 条记录的数量列值
A4:将结果表输出 Excel。
按 F9 运行这段脚本,可以看到 D:/ 销售 / 汇总表.xls 已经正确生成了:
我们再看一下如何处理非标准行式表格(带标题、页眉页脚等),同时不固定 Excel 数量也一起说了。
问题描述
有多个 Excel 文件分别存储各个单位利润表,如“甲利润表”为:
这个表格不是规整的行式表格了。
现在要将各个单位(Excel)的“主营业务收入”进行汇总,生成如下利润项目归集表:
实现脚本
A |
B |
|
1 |
D:/ 归集数据 |
=directory@s(A1) |
2 |
=create(项目, 本月发生额(主营业务收入), 本年累计数(主营业务收入)) |
|
3 |
for B1 |
=file(A1+A3).xlsimport@t(;,4:5) |
4 |
=A2.insert(0,left(A3,1),B3. 本月发生额,B3. 本年累计数 ) |
|
5 |
=file(A1+"利润项目归集表.xls").xlsexport@t(A2) |
这次使用了 directory@s 来列出指定目录及其子目录下的所有文件(这样就可以读取任意数量的 Excel 了):
然后再循环(A3 到 B4)这个文件列表,分别读取 Excel 数据,读取时指定读取第 4 行到第 5 行(B3 中“4:5”的写法)。
最后 A5 输出结果 Excel:
搞定。