想按照年度、月份生成报表，应该如何设置字典或数组？

zorsite · 发表于 2018-1-28 00:15

有如下格式的数据：

现在想按照任务单产生时间的年、月生成报表，报表格式如下：
报表格式.png

其中：没有批准人签字时间的就是“未出报告量 ”。
我现在想实现自动生成所有年份、月份的报表，并且自动引用上期数、自动计算年度累计数，自动填充“本月（2017/12/1-2017/12/31）”、“上月（2017/11/1-2017/11/30）”、“本年（2017/1/1-2017/12/31）”等表头数据。
系统于2016年5月7日，所以第一份报表的表头为：“本月（2016/5/7-2016/5/31）”，上月数为空，本年累计数为本月数。
以后每年第一月本年累计数取本月数（即第一月的数据），上月数取上一年12月的数据。

我先写了一段代码，实现了生成某月的“当月数”，但是在多月份、跨年度的处理上遇到了问题，不知道该用什么样的方式来设置字典或者数组？
先贴上我的初步代码，下一步想要再此代码之外加上年份月份的循环来实现上上述功能，请高手赐教。

Sub count()
'**********************************前期准备工作，创建字典，预先把所有报表部门写入字典备用*****************************
With Sheets("报表模板")
lastrow = .Range("A" & .Rows.count).End(xlUp).Row - 2 '找到报表部门的最后一行
departmentarr = .Range("A7:A" & lastrow).Value '把所有部门赋值给数组
departmentnum = UBound(departmentarr) '部门数量
End With
Set d = CreateObject("scripting.dictionary")
For i = 1 To departmentnum
dkey = departmentarr(i, 1)
keyvaluearr = Array(dkey, "", 0, 0) '字典键值是维度为4的数组：部门、空、产出的报告量、未产出的报告量
d(dkey) = keyvaluearr '先把所有报表部门写入字典，数据为空
Next
'*******************************************统计2017年12月份数据*******************************************
With Sheets("台账")
'把原始数据写入数组
lastrow = .Range("A" & .Rows.count).End(xlUp).Row '定位最后一行
lastcol = .Cells(1, 1).End(xlToRight).Column '定位最后一列
arr = .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).Value '把本月请假记录写入arr数组
For i = 2 To lastrow '遍历所有数据
If year(taskcreatedtime) = 2017 And month(taskcreatedtime) = 12 Then '以2017年12月份数据为例
department = arr(i, 1) '部门
taskcreatedtime = arr(i, 2) '任务单产生时间
approvedtime = arr(i, 3) '无批准时间表示未出报告
keyvaluearr = d(department)
keyvaluearr(2) = keyvaluearr(2) + 1
If approver = "" Then keyvaluearr(3) = keyvaluearr(3) + 1
d(department) = keyvaluearr
End If
Next
End With
'*******************************************把统计结果写入报表*******************************************
Application.StatusBar = "正在写入统计结果……"
For Each sht In Worksheets
If sht.Name = "2017年12月报表" Then sht.Delete
Next
Sheets("报表模板").Copy after:=Worksheets(Worksheets.count) '把模板拷贝一份
ActiveSheet.Name = "2017年12月报表" '更改工作表名
With Sheets("2017年12月报表")
lastrow = d.count
.Range("A7").Resize(lastrow, 4).Value = Application.Transpose(Application.Transpose(d.items))
End With
End Sub

复制代码

汇总表.rar (1.81 MB, 下载次数: 17)

码农熊 · 发表于 2018-1-28 01:25

建议用SQL
月报用Month(xxx)=12
年报用Year（xxx)=2017
快、准、狠
：）

zorsite · 发表于 2018-1-28 07:11

码农熊发表于 2018-1-28 01:25
建议用SQL
月报用Month(xxx)=12
年报用Year（xxx)=2017

谢谢回答。
为了能够让大家看起来更轻松，我把实际问题经过了简化处理。
实际情况是这样，汇总的时候某些部门是合并在一起汇总的。
举个例子：
龙岗部数据包括：龙岗部、坪山部、大亚湾。
特种部包括：特种部、特种二部。
所以用SQL可能会比较折腾。
我之前也一直在考虑使用SQL，现在想想，用SQL确实比较方便，计数、求和、最大最小值基本都不用写程序了，用一段查询语言就搞定。回头我试试看。

鄂龙蒙 · 发表于 2018-1-28 10:06

码农熊 · 发表于 2018-1-28 14:36

zorsite 发表于 2018-1-28 07:11
谢谢回答。
为了能够让大家看起来更轻松，我把实际问题经过了简化处理。
实际情况是这样，汇总的时候某 ...

部门科室
龙岗部龙岗
龙岗部坪山
龙岗部大亚湾
特种部特种1
特种部特种2
怎么会比较折腾，你取的名称就把自己给绕进去了。
按照这样的方式，可以按部门或者科室汇总都行，想怎么汇就怎么汇：）

码农熊 · 发表于 2018-1-28 15:31

zorsite 发表于 2018-1-28 07:11
谢谢回答。
为了能够让大家看起来更轻松，我把实际问题经过了简化处理。
实际情况是这样，汇总的时候某 ...

我写了个帖子和我简化处理的思路，也许不是你想要的：），抛砖引玉吧。

http://club.excelhome.net/thread-1394908-1-1.html

zorsite · 发表于 2018-2-2 23:33

码农熊发表于 2018-1-28 15:31
我写了个帖子和我简化处理的思路，也许不是你想要的：），抛砖引玉吧。

http://club.excelhome.net/th ...

我自己写了一段代码可以按年、月、部门汇总。代码如下：

Sub SQL_Excel()
Dim cnn, SQL$
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
SQL = "SELECT year(任务单产生时间) ,month(任务单产生时间),所属部门,count(任务单产生时间) as 产生的报告量 ,count(批准人签字时间) as 已出报告量 FROM [台账$] group by 所属部门,year(任务单产生时间) ,month(任务单产生时间) order by year(任务单产生时间) ,month(任务单产生时间)"
Sheets("结果").Range("a2").CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
End Sub

复制代码

可是还有两个问题没解决：
1.报表中需要统计“未出报告量”，这个可以通过“count(批准人签字时间='')”来查询，但是我通过代码无法实现，也尝试了sum等方法，但都不成功。
2.在您的帖子中没有发现SQL语句，目前还是不知道如何把部分部门合并。请您指点一二。

汇总表.rar (1.82 MB, 下载次数: 6)

		自动登录	找回密码
密码			免费注册

[求助] 想按照年度、月份生成报表，应该如何设置字典或数组？