求教一个关于列名转换问题

象象LOVER

求教如何将上面的表格形式转化成下面的形式   用函数还是其他什么方法  谢了~

doitbest

用公式可以实现,但应该上一附件

LangQueS

[新手必读]发帖须知、上传附件、截图动画、压缩解压、靓帖推荐:
http://club.excelhome.net/redire ... 707&ptid=184567

象象LOVER

附件已发   是这样的吧  麻烦帮看一下   谢了

LangQueS

原帖由 象象LOVER 于 2011-5-24 11:02 发表
附件已发   是这样的吧  麻烦帮看一下   谢了

需求不明。

魂断蓝桥

select * from (select "办公室" as 部门,材料,日期,单位,sum(办公室) as 数量,sum(f5) as 金额 from [sheet1$a1:i8]  group by "办公室",材料,单位,日期 union all    select "信息部" as 部门,材料,日期,单位,sum(信息部) as 数量,sum(f7) as 金额 from [sheet1$a1:i8]  group by "信息部",材料,单位,日期 UNION ALL    select "人力资源部" as 部门,材料,日期,单位,sum(人力资源部) as 数量,sum(f9) as 金额 from [sheet1$a1:i8]  group by "人力资源部",材料,单位,日期) where 金额 is not null order by 部门 desc,日期

象象LOVER

就是将附件中上面的表格数据按下面的形式转化过来  麻烦帮看下  谢谢~

象象LOVER

学习了   谢谢

doitbest

Public Sub trans()
Dim arr(), x As Range, n
With Sheets(1)
For Each x In .Range(.Cells(3, 4), .Cells(.[a65536].End(xlUp).Row, .UsedRange.Columns.Count))
If x.Column Mod 2 = 0 And x <> "" Then
n = n + 1
ReDim Preserve arr(1 To 6, 1 To n)
arr(1, n) = .Cells(x.Row, 1).Value
arr(2, n) = .Cells(x.Row, 2).Value
arr(3, n) = .Cells(x.Row, 3).Value
arr(4, n) = .Cells(1, x.Column).Value
arr(5, n) = x.Value
arr(6, n) = x.Offset(0, 1)
End If
Next x
End With
Sheets.Add after:=Sheets(Sheets.Count)
[a1:f1] = Array("日期", "材料", "单位", "部门", "数量", "金额")
[a2].Resize(n, 6) = Application.Transpose(arr)
End Sub

doitbest

附件请参考