北京
乾学院 43 号会员
1 回帖  •  893 浏览  • 
     ...

VBA 如何多 sheet 数据按列值对齐合计

 
合并(3) excel处理(107) 汇总(13) 聚合(4)

汇总某年 12 个月的员工绩效数据，按人汇总到 Year 表中，然后计算各 Project 的绩效总和，写入最后一行，相关的数据与汇总表如下：

下面为其中一个月的数据：

a100png
汇总表 Year，希望点按 SUM 按钮后就能自动计算。麻烦的是每页的人员次序和人数都不一定。

a101png

可以使用集算器, 完成对齐汇总后再被 VBA 调用。

集算器安装包可去 润乾网站 下载职场版，运行时需要一个授权，免费版本就够用。

我们将上述事例实现步骤：

1.    在集算器中编写脚本 sheets.dfx：


A B
1 =file(arg1).xlsopen() / 打开 Excel 文件
2 =A1.xlsimport@t(;1,3:17).(Employee) / 获取 year 下的用户列表
3 =to(2,A1.len()).conj(A1.xlsimport@t(;~,2:15).select(A2.contain(Employee))) / 合并 12 个月表数据
4 =A3.groups(Employee; sum(#3):'Project   1',sum(#4):'Project 2',sum(#5):'Project 3',sum(#6):'Project 4') / 按员工分组汇总绩效
5 >A4.insert(0, "TOTAL",   A4.sum(#2),A4.sum(#3),A4.sum(#4),A4.sum(#5)) / 汇总各个 Project
6 =A4.align@s(A2, Employee) / 对齐处理
7 >A1.xlsclose() / 关闭文件
8 return A6
 2.      调试运行一下，可看到 A1 格值：
A1 stname nrows ncols

year 100262 2
month_1 785 6
month_2 604 6
… … …
A3 格值：
A3 _1 Employee Project 1 Project 2 Project 3 Project 4

(null) M 2639 3879 3293 2214
(null) K 2721 2335 1136 1115
(null) F 2247 2096 2893 1539
…
… …

 3.      执行脚本返回结果：
A6 Employee Project 1 Project 2 Project 3 Project 4

A 16595 15262 12234 15783
B 16112 17383 20242 14323
C 26194 25824 30384 31480
… … …

TOTAL 285603 273582 282986 283191

A3：to(2, A1.len()) 获取月数据 sheet 的序号，xlsimport() 将每个 sheet 的数据导入，合并数据后再过滤。
A4: 按员工分组，计算每个员工的绩效之和。
A5：将各个 Project 的汇总追加到序表。
A6：按用户列表顺序对齐。

3、加载插件 xll

在 Excel 下，若还没有加载集算器插件 ExcelRaq.xll，则需要加载它。ExcelRaq.xll 存放在集算器安装目录 raqsoft\esProc\bin 下。

在 Excel 中通过菜单的文件 --> 选项 --> 加载项 --> 转到 --> 浏览 --> 选择 ExcelRaq.xll 文件, 加载项中勾选 EsprocXll, 此时就启用了 ExcelRaq 插件了。

a102png

4、编写 VBA 脚本调用集算器代码

a103png

vba脚本函数为 merge()，其中 (nStart，nEnd) 为填充表格的开始位置 (3, 3)，程序会自动计算要填充表格的大小，只需要设置初始位置即可。Application.Run() 中的参数分别是 SPL 接口函数 esproc, 要调用的 d:/app/sheets.dfx 脚本及 Excel 文件。

Sum按钮关联函数 merge()，点击按钮执行脚本，生成效果如下：

a104png

合并(3) excel处理(107) 汇总(13) 聚合(4)