VBA 用SQL统计年龄段人数

乐乐2006201505

cunfu2010 发表于 2018-1-24 15:05
下面两句是一个热心的朋友提供的，我的电脑上提醒有错误，请帮忙看看：

strSQL = "SELECT 年龄段, cou ...

getdate()是不是提醒此函数未定义？
请说说你的具体错误。
我提供的代码不能用？

as42065300

getdate函数是SQL Server中的，在这里使用不了.改成Date()

cunfu2010

乐乐2006201505 发表于 2018-1-24 15:18
getdate()是不是提醒此函数未定义？
请说说你的具体错误。
我提供的代码不能用？

刚才同事试用了您的代码，能用，不好意思回复晚了。
我在调试过程中就如您所说：getdate()提醒此函数未定义
另，第一句提示：语法错误，在“ (select year(getdate())-year(出生年月)>=36 and year(getdate())-year(出生年月)<40) From [Sheet1$A1:D] ”中

cunfu2010

as42065300 发表于 2018-1-24 15:29
getdate函数是SQL Server中的，在这里使用不了.改成Date()

谢谢，我再试试。

cunfu2010

as42065300 发表于 2018-1-24 13:35
SQL应该还能优化，

谢谢，改成 date()后果然不再弹出错误提示，我学习了你38楼的代码，很爱益，想再请教一下：如果不用Partition函数，而用datediff函数代码如何写？

as42065300

版主不是已经给你了SQL吗？

cunfu2010

as42065300 发表于 2018-1-24 16:58
版主不是已经给你了SQL吗？

不会连接，现在明白了，以后类似的应该会了。谢谢

乐乐2006201505

用下面代码计算出准确年龄，然后用后边代码统计年龄段。
Sub test11()
    For i = 2 To  Cells(Rows.Count, 2).END(3).Row
        Range("e" & i) = DateDiff("yyyy", Range("d" & i), Date) + (Date < DateSerial(Year(Date), Month(Range("d" & i)), Day(Range("d" & i))))
    Next
End Sub


Sub b2Test4统计年龄段人数()
    Dim Conn As Object, Rst As Object
    Dim strConn As String, strSQL As String
    Dim i As Integer, PathStr As String
    Set Conn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    PathStr = ThisWorkbook.FullName   '设置工作簿的完整路径和名称
    Select Case Application.Version * 1    '设置连接字符串,根据版本创建连接
    Case Is <= 11
        strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
    Case Is >= 12
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    End Select
    '设置SQL查询语句 datediff(yyyy,col1,getdate()) as
    strSQL = "select partition(年龄,36,90,5) as 年龄段, count(年龄段) as 个数 from [Sheet1$] GROUP BY partition(年龄,36,90,5)"  ' where DateDiff(yyyy,出生年月,getdate())>=25
    Conn.Open strConn    '打开数据库链接
    Set Rst = Conn.Execute(strSQL)    '执行查询，并将结果输出到记录集对象
    With Sheet2.Range("f:g")
        .Cells.Clear
        For i = 0 To Rst.Fields.Count - 1    '填写标题
            .Cells(1, i + 1) = Rst.Fields(i).Name
        Next i
        .Range("A2").CopyFromRecordset Rst
        .Cells.EntireColumn.AutoFit  '自动调整列宽
    End With
    Rst.Close    '关闭数据库连接
    Conn.Close
    Set Conn = Nothing
    Set Rst = Nothing
End Sub

编号	姓名	性别	出生年月	年龄	职称	部门	基本工资	婚否	奖金
1103	陈红	女	1976-2-3	41	助教	电路实验室	270	F	30
1602	冯卫东	男	1960-1-24	58	讲师	培训中心	341	T	50
0802	何兵	男	1972-11-23	45	副教授	软件中心	560	F	35
2208	景平	女	1950-7-7	67	研究员	仿真实验室	501	T	60
0800	吕一平	男	1963-3-12	54	工程师	软件中心	360	T	70
1102	王军	男	1938-11-23	79	高工	电路实验室	720	T	30
1107	陶玉蓉	女	1979-7-8	38	助工	电路实验室	240	F	30
1611	王军旗	男	1971-7-26	46	高工	培训中心	380	F	40
0801	吴刚	男	1970-1-1	48	研究员	软件中心	560	F	20
2212	陈磊	男	1968-11-23	49	工程师	仿真实验室	340	T	60
1104	许玉琳	女	1954-5-1	63	研究员	电路实验室	500	T	40
1609	杨华	男	1975-4-25	42	助工	培训中心	220	F	50
1610	张武	男	1955-8-8	62	高工	培训中心	400	T	80
1105	赵强	男	1966-6-8	51	工程师	电路实验室	330	T	30
0807	杨华	女	1970-5-15	47	工程师	软件中心	301	T	50
2216	张山	男	1935-11-11	82	教授	仿真实验室	820	T	50
0805	杨华	女	1954-2-12	63	副教授	软件中心	500	T	60
1115	张三丰	女	1968-7-11	49	研究员	电路实验室	540	T	30

cunfu2010

乐乐2006201505 发表于 2018-1-25 10:24
用下面代码计算出准确年龄，然后用后边代码统计年龄段。
Sub test11()
    For i = 2 To  Cells(Rows.Cou ...

这两天整理发现一个问题：各统计结果比较不太一样？

乐乐2006201505

cunfu2010 发表于 2018-1-28 20:34
这两天整理发现一个问题：各统计结果比较不太一样？

这是透视表的结果，我核对了，前两个代码没问题，后边有问题的应该是在代码中计算年龄部分出现了非精确计算导致的。比如2017年12月31日到2018年1月1日之间，前两个代码由于我在源数据表格中精确计算，所以没有问题。后边的是代码自己计算，非精确计算，才会出现统计不相同的情况。

部门	年龄	汇总
电路实验室	38	1
	41	1
	49	1
	51	1
	63	1
	79	1
电路实验室 汇总		6
软件中心	45	1
	47	1
	48	1
	54	1
	63	1
软件中心 汇总		5
培训中心	42	1
	46	1
	58	1
	62	1
培训中心 汇总		4
仿真实验室	49	1
	67	1
	82	1
仿真实验室 汇总		3
总计		18

红色数据有问题，我再查查问题。

部门	年龄段	个数		部门	年龄段	个数		部门	分段	人数		部门	分段	人数		部门	分段	人数
电路实验室	36:40	1		电路实验室	35: 39	1		电路实验室	36:40	1		电路实验室	36-40	1		电路实验室	35-39	1
电路实验室	41:45	1		电路实验室	40: 44	1		电路实验室	41:45	1		电路实验室	41-45	1		电路实验室	40-44	1
电路实验室	46:50	1		电路实验室	45: 49	1		电路实验室	46:50	1		电路实验室	46-50	1		电路实验室	50-54	2
电路实验室	51:55	1		电路实验室	50: 54	1		电路实验室	51:55	1		电路实验室	51-55	1		电路实验室	60-64	1
电路实验室	61:65	1		电路实验室	60: 64	1		电路实验室	61:65	1		电路实验室	61-65	1		电路实验室	80-84	1
电路实验室	76:80	1		电路实验室	75: 79	1		电路实验室	76:80	1		电路实验室	76-80	1		软件中心	45-49	3
软件中心	41:45	1		软件中心	45: 49	3		软件中心	46:50	3		软件中心	46-50	3		软件中心	55-59	1
软件中心	46:50	2		软件中心	50: 54	1		软件中心	51:55	1		软件中心	51-55	1		软件中心	60-64	1
软件中心	51:55	1		软件中心	60: 64	1		软件中心	61:65	1		软件中心	61-65	1		培训中心	40-44	1
软件中心	61:65	1		培训中心	40: 44	1		培训中心	41:45	1		培训中心	41-45	1		培训中心	45-49	1
培训中心	41:45	1		培训中心	45: 49	1		培训中心	46:50	1		培训中心	46-50	1		培训中心	55-59	1
培训中心	46:50	1		培训中心	55: 59	1		培训中心	56:60	1		培训中心	56-60	1		培训中心	60-64	1
培训中心	56:60	1		培训中心	60: 64	1		培训中心	61:65	1		培训中心	61-65	1		仿真实验室	50-54	1
培训中心	61:65	1		仿真实验室	45: 49	1		仿真实验室	46:50	1		仿真实验室	46-50	1		仿真实验室	65-69	1
仿真实验室	46:50	1		仿真实验室	65: 69	1		仿真实验室	66:70	1		仿真实验室	66-70	1		仿真实验室	80-84	1
仿真实验室	66:70	1		仿真实验室	80: 84	1		仿真实验室	81:85	1		仿真实验室	81-85	1
仿真实验室	81:85	1