精华内容
下载资源
问答
  • 大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第72讲,内容是:转置函数在查询中的利用。数据处理的结果是要形成一个比较好看易懂的报表,如何能做出我们需要的格式,这就要我们灵活的运用所学到的知识,大胆的...

    241d520253d9f7dd624500db99557344.png

    大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第72讲,内容是:转置函数在查询中的利用。数据处理的结果是要形成一个比较好看易懂的报表,如何能做出我们需要的格式,这就要我们灵活的运用所学到的知识,大胆的进行尝试。今天讲的是转置函数,这个函数在数组中经常的用到,我们看看在数据库中,这个函数是怎么利用的 。

    实例,如下的工作表中数据,我们要形成一个汇总的表格, 这个汇总的表格中含有姓名,和每个人的销售总额,

    8830f79fa50ce276523bdcf48bc123ff.png

    同时我们要从数据中反馈出每个人每个月的销售情况,也就是说还要把日期B列转换为记录集的字段进行汇总,如何实现这个功能呢?这就要用到转置函数了.下面先看我的代码,然后我会在讲解中给大家讲到.

    Sub mynzRecords_72() '第72讲 查询后的汇总与转置

    Dim cnADO, rsADO As Object

    Dim strPath, strSQL As String

    Worksheets("72").Select

    Cells.ClearContents

    Set cnADO = CreateObject("ADODB.Connection")

    Set rsADO = CreateObject("ADODB.Recordset")

    strPath = ThisWorkbook.FullName

    cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';" _

    & "data source=" & strPath

    '建立起一个转置的查询

    strSQL = "TRANSFORM SUM(销售额) SELECT 姓名,SUM(销售额) AS 合计 " _

    & "FROM [数据6$] GROUP BY 姓名 PIVOT FORMAT(日期,'mm月')"

    '打开记录集

    rsADO.Open strSQL, cnADO, 1, 3

    For i = 1 To rsADO.Fields.Count

    Cells(1, i) = rsADO.Fields(i - 1).Name

    Next

    '提出数据

    Range("a2").CopyFromRecordset rsADO

    '释放内存

    rsADO.Close

    cnADO.Close

    Set rsADO = Nothing

    Set cnADO = Nothing

    End Sub

    代码截图:

    cda7233386c02d84039bb0d82eaa088d.png

    代码讲解:

    1 上述代码将完成一个转置的查询结果,将月份的值作为字段来进行分类。

    2 strSQL = "TRANSFORM SUM(销售额) SELECT 姓名,SUM(销售额) AS 合计 " _

    & "FROM [数据6$] GROUP BY 姓名 PIVOT FORMAT(日期,'mm月')"

    这句的代码是利用一个转置函数TRANSFORM+ PIVOT的格式来实现我们的要求。

    所谓转置函数,即TRANSFORM...PIVOT... 语句,这是Microsoft JET Engine 3.5以上版本所特有的SQL查询语句,该语句的特点是可以建立一个交叉表格式的查询。该语句可以将表中的某些数据作为行,某些数据作为列建立交叉表。

    该语句的语法如下:

    TRANSFORM condition [select opreation] PIVOT column

    特别注意点:

    ① TRANSFORM和Pivot后面都只能有一个字段。

    ② condition是在交叉表中要显示的数据,select opreation 是一个SELECT...FROM... 查询,该查询形成交叉表的行信息,PIVOT中column为表中的一个字段,PIVOT语句使用该字段形成交叉表的列。

    ③ 上面的格式用另外的表达方式为:Transfrom 数据区域 select 行字段 from (数据源) group by 分组依据 pivot 列字段

    下面看代码的运行结果:

    28211e379eedfbf7de19f62fa1778f38.png

    今日内容回向:

    1 转置函数在什么情况下应用?

    2 转置函数的语法是什么样的?

    展开全文
  • 大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第76讲,内容是:记录集赋值给数组后,利用转置函数处理多维数组的方法。我在第一册的第43讲中,我讲了将记录集存入数组的方案,并通过一个Index函数在工作表中恢复...

    大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第76讲,内容是:记录集赋值给数组后,利用转置函数处理多维数组的方法。我在第一册的第43讲中,我讲了将记录集存入数组的方案,并通过一个Index函数在工作表中恢复数组数据的方法。今日讲的是利用转置函数来完成同样的功能。

    实例讲解,我们有下面的工作表数据,

    004e607729737d4e03a4f3122de03885.png

    我要首先把上述的数据放入ADO的记录集中,然后将记录集的数据赋值给数组,最后将数组的数据在工作表中显示出来。为什么要讲这个方法呢?确实,我们可以在工作表中可以直接显示记录集的数据,但数组的功能强大,我们必须要引入这个知识点的讲解。下面看我给出的代码:

    Sub mynzRecords_76() '第76讲 记录集赋值给数组后,利用转置函数处理多维数组的方法

    Dim cnADO, rsADO As Object

    Dim strPath, strSQL As String

    Dim myData() As Variant

    Dim myTitle() As Variant

    Worksheets("76").Select

    Cells.ClearContents

    Set cnADO = CreateObject("ADODB.Connection")

    Set rsADO = CreateObject("ADODB.Recordset")

    strPath = ThisWorkbook.FullName

    cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';" _

    & "data source=" & strPath

    strSQL = "SELECT * FROM [数据7$]"

    rsADO.Open strSQL, cnADO, 1, 3

    myTitle = Array("员工编号

    展开全文
  • 大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第76讲,内容是:记录集赋值给数组后,利用转置函数处理多维数组的方法。我在第一册的第43讲中,我讲了将记录集存入数组的方案,并通过一个Index函数在工作表中恢复...

    326b1fad1b11039d2a162d303ebd55fc.png

    大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第76讲,内容是:记录集赋值给数组后,利用转置函数处理多维数组的方法。我在第一册的第43讲中,我讲了将记录集存入数组的方案,并通过一个Index函数在工作表中恢复数组数据的方法。今日讲的是利用转置函数来完成同样的功能。

    实例讲解,我们有下面的工作表数据,

    fe5a3a6bb388d89d20b9886eebc16507.png

    我要首先把上述的数据放入ADO的记录集中,然后将记录集的数据赋值给数组,最后将数组的数据在工作表中显示出来。为什么要讲这个方法呢?确实,我们可以在工作表中可以直接显示记录集的数据,但数组的功能强大,我们必须要引入这个知识点的讲解。下面看我给出的代码:

    Sub mynzRecords_76() '第76讲 记录集赋值给数组后,利用转置函数处理多维数组的方法

    Dim cnADO, rsADO As Object

    Dim strPath, strSQL As String

    Dim myData() As Variant

    Dim myTitle() As Variant

    Worksheets("76").Select

    Cells.ClearContents

    Set cnADO = CreateObject("ADODB.Connection")

    Set rsADO = CreateObject("ADODB.Recordset")

    strPath = ThisWorkbook.FullName

    cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';" _

    & "data source=" & strPath

    strSQL = "SELECT * FROM [数据7$]"

    rsADO.Open strSQL, cnADO, 1, 3

    myTitle = Array("员工编号", "姓名", "年龄")

    myData = rsADO.GetRows(-1, 1, myTitle)

    Range("a1:C1") = myTitle

    Range("a2").Resize(UBound(myData, 2) + 1, UBound(myData) + 1) = Application.Transpose(myData)

    rsADO.Close

    cnADO.Close

    Set rsADO = Nothing

    Set cnADO = Nothing

    End Sub

    代码截图:

    aebdc244bee9b5fefd7da6edae02fc41.png

    代码讲解:

    1 上述的过程中首先打开一个用ADO连接,然后打开记录集,并将记录集赋值给数组,最后在工作表中还原数组。

    2 myTitle = Array("员工编号", "姓名", "年龄")

    myData = rsADO.GetRows(-1, 1, myTitle)

    上述语句中在记录集中按myTitle数组的格式,讲记录集赋值给myData,要注意的是myData要是变体变量。

    3 Range("a2").Resize(UBound(myData, 2) + 1, UBound(myData) + 1) = Application.Transpose(myData)

    上述语句中UBound(myData, 2) 获得myData的第2维数的最大数值;UBound(myData) 获得myData的第1维数的最大数值.用Resize()函数扩展了工作表区域;Transpose(myData)将数组myData转置后填入区域。

    下面看代码的执行结果:

    000b121530f327e4b028fe9ba04f6326.png

    今日内容回向:

    1 如何将记录集的数据赋值给数组?

    2 转置函数是如何利用的?

    展开全文
  • 大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第76讲,内容是:记录集赋值给数组后,利用转置函数处理多维数组的方法。我在第一册的第43讲中,我讲了将记录集存入数组的方案,并通过一个Index函数在工作表中恢复...

    e2a1266ad192817756e6781a381b4b53.png

    大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第76讲,内容是:记录集赋值给数组后,利用转置函数处理多维数组的方法。我在第一册的第43讲中,我讲了将记录集存入数组的方案,并通过一个Index函数在工作表中恢复数组数据的方法。今日讲的是利用转置函数来完成同样的功能。

    实例讲解,我们有下面的工作表数据,

    93724dca847d67f5f8da32db931e3094.png

    我要首先把上述的数据放入ADO的记录集中,然后将记录集的数据赋值给数组,最后将数组的数据在工作表中显示出来。为什么要讲这个方法呢?确实,我们可以在工作表中可以直接显示记录集的数据,但数组的功能强大,我们必须要引入这个知识点的讲解。下面看我给出的代码:

    Sub mynzRecords_76() '第76讲 记录集赋值给数组后,利用转置函数处理多维数组的方法

    Dim cnADO, rsADO As Object

    Dim strPath, strSQL As String

    Dim myData() As Variant

    Dim myTitle() As Variant

    Worksheets("76").Select

    Cells.ClearContents

    Set cnADO = CreateObject("ADODB.Connection")

    Set rsADO = CreateObject("ADODB.Recordset")

    strPath = ThisWorkbook.FullName

    cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';" _

    & "data source=" & strPath

    strSQL = "SELECT * FROM [数据7$]"

    rsADO.Open strSQL, cnADO, 1, 3

    myTitle = Array("员工编号", "姓名", "年龄")

    myData = rsADO.GetRows(-1, 1, myTitle)

    Range("a1:C1") = myTitle

    Range("a2").Resize(UBound(myData, 2) + 1, UBound(myData) + 1) = Application.Transpose(myData)

    rsADO.Close

    cnADO.Close

    Set rsADO = Nothing

    Set cnADO = Nothing

    End Sub

    代码截图:

    306771a9839cfbb1694696d270619e29.png

    代码讲解:

    1 上述的过程中首先打开一个用ADO连接,然后打开记录集,并将记录集赋值给数组,最后在工作表中还原数组。

    2 myTitle = Array("员工编号", "姓名", "年龄")

    myData = rsADO.GetRows(-1, 1, myTitle)

    上述语句中在记录集中按myTitle数组的格式,讲记录集赋值给myData,要注意的是myData要是变体变量。

    3 Range("a2").Resize(UBound(myData, 2) + 1, UBound(myData) + 1) = Application.Transpose(myData)

    上述语句中UBound(myData, 2) 获得myData的第2维数的最大数值;UBound(myData) 获得myData的第1维数的最大数值.用Resize()函数扩展了工作表区域;Transpose(myData)将数组myData转置后填入区域。

    下面看代码的执行结果:

    f4e40e8238b157473e0ac706b0973c5e.png

    今日内容回向:

    1 如何将记录集的数据赋值给数组?

    2 转置函数是如何利用的?

    展开全文
  • 大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第72讲,内容是:转置函数在查询中的利用。数据处理的结果是要形成一个比较好看易懂的报表,如何能做出我们需要的格式,这就要我们灵活的运用所学到的知识,大胆的...
  • 大家好,今日继续讲解VBA数据库解决方案,今日讲解的是第72讲,内容是:转置函数在查询中的利用。数据处理的结果是要形成一个比较好看易懂的报表,如何能做出我们需要的格式,这就要我们灵活的运用所学到的知识,大胆的...
  • 在Excel工作表中有个强大的函数Transpose,可以直接实现数组行列转置,作为VBA开发者来说,肯定不会忘记用这个。 Sub Demo1() Dim aData(1 To 3, 1 To 2) For i = 1 To 3 For j = 1 To 2 aData(i, j) = i + j *...
  • Transpose实现数组行列转置的限制

    千次阅读 2018-02-24 13:25:46
    提到数组,大家都不陌生,什么是数组行列转置呢?其实也很简单,如下图所示,行列转置... 在Excel工作表中有个强大的函数Transpose,可以直接实现数组行列转置,作为VBA开发者来说,肯定不会忘记用这个。Sub Dem...
  • VSTO里转置C#数组

    2010-04-23 20:50:56
    大家都知道,C#里没有直接的方法能转置一个数组,所以一般都用...今天,突然闪过一个念头,VBA里可以调用EXCEL的工作表函数,那VSTO里是否也可以呢?试了一下,原来真可以,郁闷了,以前转了那么多弯路。测试代码如...
  • 04200转置复制单元格区域 04201复制单元格的边框 04202对单元格进行加减乘除运算 04203将单元格区域作为图像对象进行复制(无链接) 04204将单元格区域作为图像对象进行复制(有链接) 04205复制大小可变的单元格...
  • 就考虑用VBA批量转置。 掌握几个东西,就比较容易了: 1、首先要用Dir函数遍历文件夹中的excel文件。从excel2007开始就取消了FileSearch函数。只能Dir函数了。 2、Dir函数的目的就是遍历这个目录下的文件。第一次...
  • Excel百宝箱8.0

    2011-06-07 21:32:17
    【生成系统图标】【获取内置命令】【修复Excel】【破解VBA密码】【删除空单元格】【转置选区】【按列倒置】【按列倒置】【字母大小写转换】【小写金额转大写】【大写金额转小写】【区域数据加密】【多区域复制】【按...
  • 函数运算符符语法查询】:VBA函数以及运算符相关的帮助查询 【反向选择】:选择当前区域中未选择的区域 【千年日历】:工单元格中生成千年日期。默认显示本月的日历,可以自由调整年月 【保护公式】:保护当前工作...
  • 转置选区】将选区转置一个方向存放,即纵向转换成横向 【字母大小写转换】将选区的英文单词在大写与小写间切换,也可以首字母大写其它小写 【小写金额转大写】将小写金额批量转换成大写 【大写金额转小写】将大写...
  • │ 技巧158 数组的转置和变换.xls │ ├─第5篇-函数技巧 │ ├─第16章-信息处理 │ │ 技巧161 取得单元格信息.xls │ │ 技巧162 取得当前工作表表名.xls │ │ │ ├─第17章-文本处理 │ │ 技巧164 ...
  • Excel百宝箱9.0无限制破解版

    热门讨论 2012-02-03 19:05:29
    函数运算符符语法查询】:VBA函数以及运算符相关的帮助查询 【反向选择】:选择当前区域中未选择的区域 【千年日历】:工单元格中生成千年日期。默认显示本月的日历,可以自由调整年月 【保护公式】:保护当前工作表...
  • ║ 【转置选区值】 【取型号使用范围】 【选中所有图片】 【建文件夹目录】 【下载地址转换】 ║ ║ 【数字符分离】 【批量添加前后缀】 【导入多列图片】 【建工作表及目录】 【星号密码查看】 ║ ║ 【按背景色...
  • Excel百宝箱

    2012-10-27 17:09:21
    函数运算符符语法查询】:VBA函数以及运算符相关的帮助查询 【反向选择】:选择当前区域中未选择的区域 【千年日历】:工单元格中生成千年日期。默认显示本月的日历,可以自由调整年月 【保护公式】:保护当前工作表...
  • 转置选区值】 将选中存储格区域内容自动识别行转置为列与列转置为行,支持多行多列相互转置并且能保留转置前的多样格式。 【数字符分离】 将存储格的内容按需求进行字符分离与表达式计算。(1-只保留数字. 2-只...
  • excel百宝箱 正版

    2013-04-04 23:39:28
    ║ 【转置选区值】 【取型号使用范围】 【选中所有图片】 【建文件夹目录】 【下载地址转换】 ║ ║ 【数字符分离】 【批量添加前后缀】 【导入多列图片】 【建工作表及目录】 【星号密码查看】 ║ ║ 【按背景色...

空空如也

空空如也

1 2
收藏数 23
精华内容 9
关键字:

vba转置函数