ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: cunfu2010

[求助] VBA 用SQL统计年龄段人数

[复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-1-23 20:46 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
t7856 发表于 2018-1-23 19:33
在Access中调好SQL语句,然后在EXCEL的VBA中运行SQL,可以配上参数。具体看《EXCEL SQL》那本书。

网店上有买的吗?

TA的精华主题

TA的得分主题

发表于 2018-1-23 23:55 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
不知为什么,我电脑上datediff("YYYY",日期1,日期2)和GETDATE()都用不成。折腾了半晚上了还没成功,所以没办法了。期待高手写出代码并上传附件。

点评

这股好学的精神难得!赞一个  发表于 2018-1-24 08:44

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-1-24 05:56 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
我电脑上datediff("YYYY",日期1,日期2)和GETDATE()都用不成,所以只能退而求其次,在原表中添加一列,用公式算出年龄。然后再做统计。当然这样一来,方法就多了。
Sub Test4统计年龄段人数()
    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)"
    Conn.Open strConn    '打开数据库链接
    Set Rst = Conn.Execute(strSQL)    '执行查询,并将结果输出到记录集对象
    With Sheet3.Range("b:c")
        .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

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-1-24 10:24 | 显示全部楼层
乐乐2006201505 发表于 2018-1-23 23:55
不知为什么,我电脑上datediff("YYYY",日期1,日期2)和GETDATE()都用不成。折腾了半晚上了还没成功,所以 ...

非常感谢,上午一上班一直忙到现在,回复晚了,不好意思。真心的向您学习。

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-1-24 12:20 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
jsgj2023 发表于 2018-1-23 14:21
对了,excel里的sql语句没有when then这样的写法,得另外改写!
你这种情形的查询我就倾向于直接用数据库啦 ...

能再指点一下吗?我从网上搜索了没找到相关的,不知道如何将 when then改写

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-1-24 12:21 | 显示全部楼层
jiangxiaoyun 发表于 2018-1-23 18:28
可以试试partition函数,vba应该可以用的。

麻烦给写个关键语句,谢谢!

TA的精华主题

TA的得分主题

发表于 2018-1-24 12:55 来自手机 | 显示全部楼层
partition(年龄,36,90,5) as 年龄段,
getdate()无法用,求解直接用sql代码获取年龄的方法。

TA的精华主题

TA的得分主题

发表于 2018-1-24 13:35 | 显示全部楼层
SQL应该还能优化,
  1.   CountMax = Format(Date, "yyyy") - Format(Application.Max(Sheet1.Range("d:d")), "yyyy")
  2.     CountMin = Format(Date, "yyyy") - Format(Application.Min(Sheet1.Range("d:d")), "yyyy")

  3.     strSQL = "select 部门,分段,count(*) as 人数 from " _
  4.              & " (select 部门,Partition(年龄," & CountMax & "," & CountMin & ",5) as 分段  From" _
  5.              & " (select 部门,year(Date())-year(出生年月) as 年龄 From [sheet1$A1:I])) group by 部门,分段"
复制代码

2.png

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-1-24 14:25 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
谢谢上楼的代码,整理如下:
问题2代码。
Sub bTest4统计年龄段人数()
    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查询语句
    CountMax = 36
    CountMin = 85
    strSQL = "select 分段,count(*) as 人数 from " _
             & " (select Partition(年龄," & CountMax & "," & CountMin & ",5) as 分段  From" _
             & " (select year(date())-year(出生年月) as 年龄 From [sheet1$A1:I])) group by 分段"
    Conn.Open strConn    '打开数据库链接
    Set Rst = Conn.Execute(strSQL)    '执行查询,并将结果输出到记录集对象
    With Sheet3.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
问题3代码。
Sub cTest4按部门统计年龄段人数()
    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查询语句
    CountMax = 36
    CountMin = 85
    strSQL = "select 部门,分段,count(*) as 人数 from " _
             & " (select 部门,Partition(年龄," & CountMax & "," & CountMin & ",5) as 分段  From" _
             & " (select 部门,year(Date())-year(出生年月) as 年龄 From [sheet1$A1:I])) group by 部门,分段"
    Conn.Open strConn    '打开数据库链接
    Set Rst = Conn.Execute(strSQL)    '执行查询,并将结果输出到记录集对象
    With Sheet3.Range("b:d")
        .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


TA的精华主题

TA的得分主题

 楼主| 发表于 2018-1-24 15:05 | 显示全部楼层
乐乐2006201505 发表于 2018-1-24 14:25
谢谢上楼的代码,整理如下:
问题2代码。
Sub bTest4统计年龄段人数()

下面两句是一个热心的朋友提供的,我的电脑上提醒有错误,请帮忙看看:

strSQL = "SELECT 年龄段, count(*) as 人数 From [Sheet1$A1:D] where (select year(getdate())-year(出生年月)>=36 and year(getdate())-year(出生年月)<40) From [Sheet1$A1:D]  group by 年龄段"

strSQL = "SELECT datediff('yyyy',year(出生年月),getdate()) From [Sheet1$A1:D] group by datediff('yyyy',year(出生年月),getdate())"
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 17:56 , Processed in 0.059019 second(s), 12 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表