ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 24233|回复: 65

[分享] 几个例子-有关行列转置(收集归纳自用)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2015-12-4 09:27 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 autumnalRain 于 2016-1-14 14:20 编辑

说明:本贴子只是个人收集归纳。无奈主题没有自定义,建议加个主题叫做“总结”或“归纳”吧,要不就得写成分享。但一般分享都是很有技术含量的东西,如果用到了哪位老师的代码忘记写出自哪里,请见谅!
============================================================================================================
第一个:将多行数据重排为单行数据。主要运用的知识点--ARRAY函数
http://club.excelhome.net/thread-1242204-1-1.html
  1. Sub test()
  2. For I = 3 To Sheet1.[A2].End(xlDown).Row
  3.     For J = 2 To Sheet3.[A1].End(xlDown).Row
  4.         With Sheet3
  5.            If Sheet1.Cells(I, 2) & Sheet1.Cells(I, 7) = .Cells(J, 2) & .Cells(J, 4) Then
  6.                  Sheet1.Cells(I, X + 21).Resize(1, 6) = Array(.Cells(J, 1), .Cells(J, 9), .Cells(J, 6), .Cells(J, 7), .Cells(J, 8), .Cells(J, 12))
  7.                   X = X + 6
  8.             End If
  9.         End With
  10.      Next J
  11.       X = 0
  12. Next I
  13. End Sub
复制代码






补充内容 (2016-1-25 11:37):
希望此贴能涵盖大部分类型的行列转置情况,将来能当成本人的工具贴!

利用ARRAY数组.rar

91.52 KB, 下载次数: 413

评分

2

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-12-4 09:29 | 显示全部楼层
本帖最后由 autumnalRain 于 2016-1-14 14:17 编辑

第二个:代码出自zhong1218老师 。POWER QUERY也可实现。brr行数可以计算出……

  1. Sub Transform()    Dim brr(1 To 1500, 1 To 3)
  2.     brr(1, 1) = "班级": brr(1, 2) = "科目": brr(1, 3) = "教师"
  3.     arr = Sheets("原始").Range("a1").CurrentRegion
  4.     For i = 2 To UBound(arr, 2)
  5.         For j = 2 To UBound(arr)
  6.             If arr(j, i) <> "" Then
  7.                 n = n + 1
  8.                 brr(n + 1, 1) = arr(1, i)
  9.                 brr(n + 1, 2) = arr(j, 1)
  10.                 brr(n + 1, 3) = arr(j, i)
  11.             End If
  12.         Next j
  13.     Next i
  14.     Sheets("整理").Cells.ClearContents
  15.     Sheets("整理").Range("a1").Resize(UBound(brr), 3) = brr
  16. End Sub
复制代码



原格式.png
转置效果.png

转置.rar

47.29 KB, 下载次数: 337

评分

2

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-12-4 09:46 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 autumnalRain 于 2015-12-30 11:58 编辑

第三个:这个例子因为数据不够规范,比如同列>>数据类型不同,数据稀疏NULL值等 ,所以用VBA SQL需要重新加工数据。略过此法
  1. Dim d As Object
复制代码
  1. Sub 装入字典()
  2.     Dim r As Integer
  3.     Set d = CreateObject("scripting.dictionary")
  4.     r = 2
  5.     With Sheet1
  6.        Do While .Cells(r, 1) <> ""
  7.           If .Cells(r, 1).Value <> "小计:" Then
  8.              d(.Cells(r, 1).Value) = ""
  9.           End If
  10.           r = r + 1
  11.        Loop
  12.     End With
  13. End Sub
复制代码
  1. Sub 写行列标题()
  2.     Sheets.Add after:=Sheet1
  3.     ActiveSheet.Name = "最终格式"
  4.     [a1:f1] = Array("设备号码", "固定电话月租费", "来电显示", "区内通话费", "区间通话费", "传统国内长途费")
  5.     [a2].Resize(d.Count) = Application.Transpose(d.keys)
  6. End Sub
复制代码
  1. Sub 写入数据()
  2.     Dim arr, brr,  dic As Object, i&, j&
  3.     Set dic = CreateObject("scripting.dictionary")
  4.     arr = Sheet1.Range("a1").CurrentRegion '原始数据
  5.     brr = Sheets("最终格式").Range("a1").CurrentRegion '目标数据

  6.     For i = 2 To UBound(arr, 1)
  7.         If arr(i, 1) <> "小计:" Then
  8.               dic(arr(i, 1) & "|" & arr(i, 2)) = arr(i, 3)
  9.         End If
  10.     Next
  11.     'Sheet1.Range("A40").Resize(UBound(dic.keys) + 1, 2) = Application.Transpose(Array(dic.keys, dic.items))
  12.         For i = 2 To UBound(brr, 1)
  13.         For j = 2 To UBound(brr, 2)
  14.                brr(i, j) = dic(brr(i, 1) & "|" & brr(1, j))
  15.         Next
  16.     Next
  17.      Sheets("最终格式").Range("a1").Resize(UBound(brr), UBound(brr, 2)) = brr
  18. End Sub
复制代码



原格式.png
转置效果.png

公共变量使用.rar

19.91 KB, 下载次数: 369

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-12-4 09:48 | 显示全部楼层
本帖最后由 autumnalRain 于 2015-12-5 12:12 编辑

第三个例子,下棋法代码,代码出自yeminqiang老师。
  1. Sub 下棋法之数据透视表式汇总()
  2. Set d = CreateObject("scripting.dictionary")
  3. Dim 棋盘(1 To 10000, 1 To 6)
  4. Dim 行数, 列数
  5. Dim arr, x, k
  6. arr = Range("a2:g" & Range("a65536").End(xlUp).Row)
  7.   For x = 1 To UBound(arr)
  8.   列数 = (InStr("费用来电区内区间传统", Left(arr(x, 2), 2)) + 1) / 2 + 1
  9.   If arr(x, 1) <> "小计:" Then
  10.     If d.Exists(arr(x, 1)) Then
  11.       行数 = d(arr(x, 1))
  12.       棋盘(行数, 列数) = 棋盘(行数, 列数) + arr(x, 3)
  13.    Else
  14.       k = k + 1
  15.       d(arr(x, 1)) = k
  16.       棋盘(k, 1) = arr(x, 1)
  17.       棋盘(k, 列数) = arr(x, 3)
  18.    End If
  19.    End If
  20. Next x
  21. Range("J2").Resize(k, 6) = 棋盘
  22. End Sub
复制代码

下棋法.rar

31.7 KB, 下载次数: 195

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-12-4 11:41 | 显示全部楼层
autumnalRain 发表于 2015-12-4 09:48
第三个例子,下棋法代码,代码出自yeminqiang老师

下棋法,点按钮1提示下标越界

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-12-4 11:43 | 显示全部楼层

向兰色幻想老师致敬

本帖最后由 autumnalRain 于 2015-12-4 13:28 编辑
renahu 发表于 2015-12-4 11:41
下棋法,点按钮1提示下标越界

这个例子是其他老师的代码。兰色幻想老师的下棋法代码通用性很强,可以套写。请参考兰色老师下棋法代码。见附件!

VBA数组之下棋法.rar

144.68 KB, 下载次数: 263

TA的精华主题

TA的得分主题

发表于 2015-12-4 12:43 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
autumnalRain 发表于 2015-12-4 09:48
第三个例子,下棋法代码,代码出自yeminqiang老师。抱歉,还没有找到出处就点了保存

此附件中的 zhong1218老师代码   有错误,brr数组下限的问题,不知道跟 下棋法之数据透视表式汇总 有什么关联?是不是用的也是下棋法?

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-12-4 13:20 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 autumnalRain 于 2015-12-4 13:32 编辑
renahu 发表于 2015-12-4 12:43
此附件中的 zhong1218老师代码   有错误,brr数组下限的问题,不知道跟 下棋法之数据透视表式汇总 有什么 ...

http://club.excelhome.net/forum. ... 1&extra=#pid8448597
第二个例子,我重新试了下,没有问题啊,看下原贴吧。这个例子没有用下棋法,纯数组方法解决;
下棋法是数组和字典的综合运用。第二个例子第二种方法用的是下棋法。

TA的精华主题

TA的得分主题

发表于 2015-12-4 17:32 | 显示全部楼层
autumnalRain 发表于 2015-12-4 13:20
http://club.excelhome.net/forum. ... 1&extra=#pid8448597
第二个例子,我重新试了下,没有问题啊,看 ...

是第三个例子,附件是“下棋法”,点击sheet1的按钮1

TA的精华主题

TA的得分主题

发表于 2015-12-4 17:38 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
autumnalRain 发表于 2015-12-4 09:48
第三个例子,下棋法代码,代码出自yeminqiang老师。抱歉,还没有找到出处就点了保存

代码不是我原创,我也只是改的。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-11-15 06:43 , Processed in 0.051625 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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