VBA 如何多条件查询汇总

如何根据医院名称、报表时间和报表类别查询,按定点医院名称、分类进行分组,计算各项费用之和。相关的数据与统计表如下:
表数据:

a100png

a101png

希望点击 "查询" 按钮后就能自动计算,将相应的数据按上面形式填充到 A4:L18 之间的表格。

可以使用集算器, 按所给条件查询汇总后再被 VBA 调用。
集算器安装包可去润乾网站下载职场版,运行时需要一个授权,免费版本就够用。

我们将上述事例实现步骤:
1. 在集算器新建脚本,命名为 hospital.dfx,设置脚本参数:

a102png
设置参数分别是 Excel 文件名,医院名称、报表日期、报表类别。

2. 编写脚本

A B
1 >name=(if(name!="" && name, name, null)) /条件医院名称
2 >pdate=(if(pdate!="" && pdate,   date(pdate,"yyyy/MM/dd"), null)) /条件报表时间
3 >type=(if(type!="" && type, type, null)) /条件报表类别
4 =file(arg1).xlsimport@t(;2).select((name==null || 定点医疗机构名称 ==name) && (pdate==null || 报表时间 ==pdate)   && (type==null || 报表类别 == type) && (报表类别) )  /根据条件查询, 无条件时用报表类别去掉空记录
5 =A4.groups(定点医疗机构名称, 分类;sum( 发生人次): 发生人次,sum(总费用): 总费用,sum(统筹支付): 统筹支付,sum(IC 卡支付):IC 卡支付,sum(公务员补助): 公务员补助,sum(大额补助): 大额补助,sum(扣减费用): 扣减费用,sum(实际应付): 实际应付 ) /按定点医疗机构名称、分类进行分组汇总
6 =A5.group(定点医疗机构名称) /按定点医疗机构名称
7 =A6.news(~;A6.#: 序号, 定点医疗机构名称, 分类, 发生人次, 总费用, 统筹支付,IC 卡支付, 公务员补助, 大额补助, 扣减费用, 实际应付,A6.~.sum( 实际应付): 合计支付 ) /按每组生成序号及合计支付处理
8 >A7.run(if( 序号 == 序号 [-1], 序号 = 定点医疗机构名称 = 合计支付 =null)) /将每组非首行记录的序号、合计支付改为空
9 return A7 /返回值
  3.    设置参数后,调试运行一下,可看到 A4 格值:
A4 报表时间 费用所属经办机构 定点医疗机构名称 报表类别 。。。
2014-08-01 思上县社会保险事业局 江南市妇幼保健院 城镇职工
2014-08-01 湖山港市社会保险事业局 江南市妇幼保健院 城镇职工
2014-08-01 港口区社会保险事业局 江南市妇幼保健院 城镇职工
….
也能看到其它格值,在此就不再一一列出。 4.       执行脚本返回结果
A7 序号 定点医疗机构名称 分类 发生人次 总费用 。。。 合计支付
1 江南市妇幼保健院 住院 29 29511.37 49500.73
(null) (null) 普通门诊 249 29760.57 (null)
按所提供的三个条件组合查询,对查询结果汇总统计,其中 A7 增加了序号、合计支付。

4 、加载插件 xll

在 Excel 下,需要加载集算器插件 ExcelRaq.xll,插件存放在集算器安装目录 raqsoft\esProc\bin 下。若已加载则可跳过此步。

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

a103png

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

a104png

vba 脚本函数为 CommandButton1_Click (),程序根据 hospital.dfx 脚本传递对应的 4 个参数。调用 dfx 脚本后,程序将汇总数据自动填充表格。Application.Run() 中的参数分别是 SPL 接口函数 esproc, 要调用的 d:/app/hospital.dfx 脚本、当前的 Excel 文件、医院名称、报表日期和报表类别。

由于 vba 脚本函数命名已与按钮关联,只需点击 "查询" 按钮执行脚本,生成效果如下:

a105png

除了最后一行求总支付金额外,其它数据均由 dfx 脚本提供,省去了由于分类数不一致,合计支付计算时,行之间还需要相应处理。