|
Option Explicit
Sub reformatting()
Dim A '数据源
Dim B '结果
Dim groupCount '组的总个数
Dim memberX '每组中的行数
Dim memberY '每组中的列数
Dim groupX '组矩阵的行数
Dim groupY '组矩阵的列数
Dim currentGroup '当前组
Dim i, j, r, c, tmp
'1)已知数据
A = Sheets(1).Range("a1").CurrentRegion
memberX = 43
memberY = 4
groupY = 3
'2)相关数据
groupCount = getX(UBound(A) - 1, memberX) '求有多少组
groupX = getX(groupCount, groupY)
ReDim B(1 To groupX * memberX, 1 To groupY * memberY) 'B的大小
'3)生成数组B
For i = 2 To UBound(A)
currentGroup = getX(i - 1, memberX) '序号属于第几组
'求r
r = getX(currentGroup, groupY) '当前组,在组矩阵中是第几行
r = (r - 1) * memberX '当前组,在数组B中从第几行开始(未加偏移行数)
r = r + getY(i - 1, memberX) '加上偏移行数
'求c
c = getY(currentGroup, groupY) '当前组,在组矩阵中是第几列
c = (c - 1) * memberY '当前组,在数组B中从第几列开始
'确定
B(r, c + 1) = A(i, 1)
B(r, c + 2) = A(i, 2)
B(r, c + 3) = A(i, 7)
B(r, c + 4) = A(i, 11)
Next i
'4)输出
With Sheets(4)
.Rows("3:65536").ClearContents
.Range("a3").Resize(UBound(B), UBound(B, 2)) = B
End With
'5)打印
Call myPrint(3, 3 + UBound(B) - 1, memberX)
End Sub
'获取横坐标
Function getX(x, y)
Dim tmp
tmp = x \ y
getX = IIf(tmp = x / y, tmp, tmp + 1)
End Function
'获取纵坐标
Function getY(x, y)
Dim tmp
tmp = x Mod y
getY = IIf(tmp, tmp, y)
End Function
'打印
Sub myPrint(x, y, z)
Dim i
With Sheets(4)
.PageSetup.PrintArea = .Range("a1").CurrentRegion.Address
.PageSetup.Orientation = xlPortrait '纵向
.PageSetup.PrintTitleRows = "$1:$2" '标题行
.ResetAllPageBreaks '重置分页符
For i = x To y Step z
If i > 1 Then .HPageBreaks.Add Cells(i, 1)
Next i
End With
End Sub
提取数据并均分为多列151222d.rar
(187.46 KB, 下载次数: 7)
|
评分
-
查看全部评分
|