EXCEL高手请进（二维数据转化成一维数据）。。。

Explorer

现在需要把“行”为年、“列”为月的二维数据，转化为按顺序月份排列的一维数据。
比如“行”为2001年～2010年，“列”为1～12月，结果是按“行”2001.1、2001.2、2001.3。。。2010.12顺序排列数据。
如果需要处理的数据很大的话，COPY的速度太慢，所以应该会有某些公式、宏或者PIVOT的方法使问题简单很多。
请高手赐教啊

[ 本帖最后由 Explorer 于 2012-3-18 23:21 编辑 ]

Lucifer

Index

Explorer

刚才试了一下，好像一次只能返回一个值，不能批处理，fill的话也不行
能不能麻烦Lu师傅写个公式，比如：
         A  B
1      1  2
2      3  4
如何能实现
         A
1        1
2        2
3        3
4        4
的结果，批处理的公式或者先取一个值再用fill拖下来。
谢谢！

[ 本帖最后由 Explorer 于 2012-3-18 23:08 编辑 ]

floodp

如果只是一次性的转换，而且只有几年的数据，可以考虑用transpose copy。

gcc

能否用 concaternate 把列跟行合并, 然后再把这些数据排列(sort)?

floodp

对不起，没注意到数据量很大，transpose可能不行。

wsfqy

不太明白你要什么。是不是可以用"&"，比如你这个例子，可以用"=A1&B1&A2&B2"，记得去掉引号。

如果要在1 2 3 4 之间插入空格，加一个&" "&就可以了。

gcc

原帖由 gcc 于 2012-3-18 22:50 发表
能否用 concaternate 把列跟行合并, 然后再把这些数据排列(sort)?

你试试这样行么:
        1991        1992
1        =CONCATENATE($B$1,".",A2)        =CONCATENATE($C$1,".",A2)
2        =CONCATENATE($B$1,".",A3)        =CONCATENATE($C$1,".",A3)
3        =CONCATENATE($B$1,".",A4)        =CONCATENATE($C$1,".",A4)
4        =CONCATENATE($B$1,".",A5)        =CONCATENATE($C$1,".",A5)
5        =CONCATENATE($B$1,".",A6)        =CONCATENATE($C$1,".",A6)
6        =CONCATENATE($B$1,".",A7)        =CONCATENATE($C$1,".",A7)
7        =CONCATENATE($B$1,".",A8)        =CONCATENATE($C$1,".",A8)
8        =CONCATENATE($B$1,".",A9)        =CONCATENATE($C$1,".",A9)
9        =CONCATENATE($B$1,".",A10)        =CONCATENATE($C$1,".",A10)
10        =CONCATENATE($B$1,".",A11)        =CONCATENATE($C$1,".",A11)
11        =CONCATENATE($B$1,".",A12)        =CONCATENATE($C$1,".",A12)
12        =CONCATENATE($B$1,".",A13)        =CONCATENATE($C$1,".",A13)

wsfqy

如果你的行列每次都是固定的，可以用=TRANSPOSE(array)，如果不固定，我建议你编一段VB就可以了，不复杂的。

Explorer

LS几位辛苦了
顶楼更新了个附件，看得应该比较直观。
如果没有非常懒的办法，只能费点儿功夫了

红色

编个MACRO，一共4行语句就解决了。。

KXYS

请看图, 红色框中的公式依次为

[ 本帖最后由 KXYS 于 2012-3-18 23:56 编辑 ]

Explorer

大伙儿集思广益，看看有没有最懒的办法。
明天过来再看

rabbitpoint

我觉得最简单的办法是先把表格横竖一颠倒，然后copy-paste比较傻瓜方便。并不慢，一分钟搞定10几年的数据没问题，行的名字往下一拉自动填充就行。你可以直接横向扩展，然后再横竖颠倒，小心横向限制（超过21年就不灵了）。当然，你要是有超过255年的数据，当我没说。

longyu68

Pivot table solution

bowensyd

我有最懒的办法， 我做了个小程序，几秒钟搞定所有的

bowensyd

解压文件后，生成read.php ,和 Test.CSV ,把的你xls文件转成Test.CSV，按照Test.CSV里面的内容格式。把两个文件放在同一目录下，用apache服务运行read.php文件，你就能得到想要的结果

红色

For Each aCell In Selection
  [Z1].Offset(i, 0) = Range(aCell.Row, 1) & "." & aCell
  i = i + 1
Next aCell

[ 本帖最后由 红色 于 2012-3-19 23:18 编辑 ]

红色

运行此macro的时候，把所有月份的数据作为Selection
假设年都在第一列。。
结果放在Z1（当然你放哪里都可以）。。

Lucifer

不需要这么麻烦的，把结果数据里的年月分开，然后用index和match就可达到想要结果。

euroblue

用index和match.

KXYS

我试了, 总出错, 不知道哪里有问题. 可不可以麻烦你把micro做成 XLSM后缀的EXCEL文件. 谢谢.

bowensyd

Lz 用我的方法解决了问题没有？ 主要思路就是将所有的相关元素提取出来，然后再重新展示出来， 非常灵活，任何你想要的数据排列形式都是可以做出来的。 用excel的VBA编程早已成为历史了，所有不建议用宏！

Explorer

不好意思啊，apache不太懂，还得找时间搞定。
相信你的小程序肯定没问题
BWT，等分分回来再慢慢感谢大伙儿啊

bowensyd

很简单的，去下载个apache的软件！ 或者你直接把xls发给我邮箱info@oz3d.biz ，我帮你搞定后把结果给你发过去！

红色

原帖由 KXYS 于 2012-3-20 15:48 发表
我试了, 总出错, 不知道哪里有问题. 可不可以麻烦你把micro做成 XLSM后缀的EXCEL文件. 谢谢.

不好意思，我只有Excel 2002, 没有2007以上高版本的。。。
所以我只能凭借LZ的描述写这个Macro。。。不一定理解对了。
我把低版本的放在附件里了。。。（绿色的部分是要转换的部分，所以先要Selection，然后run Macro）。

[ 本帖最后由 红色 于 2012-3-20 20:13 编辑 ]

红色

用第二个ZIP文件，稍微不一样，根据我不同的理解。。

bowensyd

本人不推荐用宏的原因是：
1,你要降低安全设置来接受未签署的宏的运行，一旦安全防范降低后， windows平台下的病毒会乘虚而入，宏病毒伴随windows一直到今日。
2.宏的运行会破坏原始数据文件（比如：xls）的完整性，因为宏是在excel环境下运用。最好的做法是将数据和处理后的结果分开。
3.宏的全自动化程度不高，楼上的做法中还要你select区域，明显要手动！

Inner

进来学习了。

oceann

学习了