请教如何把横向的数值汇转到纵向

yufei14

见附件，请问用什么办法能把表1中的数据加工成表2中的格式

gslkf

1.复制，２．选择性粘贴，３．选转置

亡者天下

直接用透视表完成

最基本操作

Book1.rar (39.65 KB, 下载次数: 28)

2011-1-17 14:44 上传

点击文件名下载附件

zm0115

Book1.rar (26.33 KB, 下载次数: 17)

2011-1-17 14:45 上传

点击文件名下载附件

laowuwyx

=SUMPRODUCT((B2&C2='1'!A$2:A$29&'1'!B$2:B$29)*('1'!C$1:N$1=A2&"月")*'1'!C$2:N$29)

yufei14

感觉都好复杂啊，谢谢！

suwenkai

数据透视表如果要做成你所说的哪个样子估计比较难，要通达SQL语句做，或者用VBA ，前几天看了一位大侠用VBA做的转置，今天用来练一下手。做了二种方法。

1. Sub 测试()
   
2. Dim a, b, Arr, Brr(1 To 10000, 1 To 4)
   
3. Arr = Sheet1.Range("a1").CurrentRegion
   
4. For b = 3 To UBound(Arr, 2)
   
5.     For a = 2 To UBound(Arr)
   
6.         n = n + 1
   
7.         Brr(n, 1) = Arr(1, b)
   
8.         Brr(n, 2) = Arr(a, 1)
   
9.         Brr(n, 3) = Arr(a, 2)
   
10.         Brr(n, 4) = Arr(a, b)
    
11.     Next
    
12. Next
    
13. Sheet2.Range("a2").Resize(10000, 4) = Brr
    
14. End Sub
    

复制代码

1. 
   
2. select 部门, 项目,  [1月] as 金额,"1月" as 月份 from [1$] union all
   
3. select 部门, 项目,  [2月] as 金额,"2月" as 月份 from [1$] union all
   
4. select 部门, 项目,  [3月] as 金额,"3月" as 月份 from [1$] union all
   
5. select 部门, 项目,  [4月] as 金额,"4月" as 月份 from [1$] union all
   
6. select 部门, 项目,  [5月] as 金额,"5月" as 月份 from [1$] union all
   
7. select 部门, 项目,  [6月] as 金额,"6月" as 月份 from [1$] union all
   
8. select 部门, 项目,  [7月] as 金额,"7月" as 月份 from [1$] union all
   
9. select 部门, 项目,  [8月] as 金额,"8月" as 月份 from [1$] union all
   
10. select 部门, 项目,  [9月] as 金额,"9月" as 月份 from [1$] union all
    
11. select 部门, 项目,  [10月] as 金额,"10月" as 月份 from [1$] union all
    
12. select 部门, 项目,  [11月] as 金额,"11月" as 月份 from [1$] union all
    
13. select 部门, 项目,  [12月] as 金额,"12月" as 月份 from [1$]
    

复制代码

[ 本帖最后由 suwenkai 于 2011-1-17 16:21 编辑 ]