新手求助，比较麻烦。

sd06116 · 发表于 2009-6-8 10:11

现实碰到的，要根据对方的表格整理。祥见附件，有办法解决么？
麻烦各位看看了，先谢过。

bolliy · 发表于 2009-6-8 10:49

一种比较简单的方法，复制——选择性粘贴——转置

sd06116 · 发表于 2009-6-8 11:12

能这么简单到好了。。。有几十种产品呢。

开心萝卜惠州 · 发表于 2009-6-8 11:26

应该用函数可以实现，建议到函数板块求助

fdd · 发表于 2009-6-8 13:31

SQL:

(select '铰链' as 类别,编号,null as 单价,null as 总价,普通铰链 as 数量,'普通铰链' as 型号 from [sheet1$a11:g14] where 普通铰链<>null) union all
(select '铰链' as 类别,编号,null as 单价,null as 总价,快装铰链 as 数量,'快装铰链' as 型号 from [sheet1$a11:g14] where 快装铰链<>null) union all
(select '铰链' as 类别,编号,null as 单价,null as 总价,半盖铰链 as 数量,'半盖铰链' as 型号 from [sheet1$a11:g14] where 半盖铰链<>null) union all
(select '铰链' as 类别,编号,null as 单价,null as 总价,半盖铰链 as 数量,'半盖铰链' as 型号 from [sheet1$a11:g14] where 半盖铰链<>null) union all
(select '拉篮' as 类别,编号,null as 单价,null as 总价,MA03080 as 数量,'MA03080' as 型号 from [sheet1$a11:g14] where MA03080<>null) union all
(select '拉篮' as 类别,编号,null as 单价,null as 总价,MA04080 as 数量,'MA04080' as 型号 from [sheet1$a11:g14] where MA04080<>null) union all
(select '拉篮' as 类别,编号,null as 单价,null as 总价,MA09130 as 数量,'MA09130' as 型号 from [sheet1$a11:g14] where MA09130<>null)

[ 本帖最后由 fdd 于 2009-6-8 13:33 编辑 ]

penghaifeng · 发表于 2009-6-8 16:11

这样行不行？

[ 本帖最后由 penghaifeng 于 2009-6-8 16:19 编辑 ]

sjds333 · 发表于 2009-6-8 16:21

简单点：F列前插入一列，将A列复制到此插入的列，新生成的F:J列剪切，粘贴到此表的尾部，再按一般的透视操作就行了。

fdd · 发表于 2009-6-8 16:24

原帖由 penghaifeng 于 2009-6-8 16:11 发表
这样行不行？

你的数据源其实是楼主的结果表。

sd06116 · 发表于 2009-6-9 10:36

先谢谢各位了，我看了下，虽然没什么问题，但是我的源数据很大，有几万条数据。。
看来用数据透视表的话不怎么方便，我还是去函数区求助吧。
再次感谢各位

		自动登录	找回密码
密码			免费注册