前阵子进行数据采集,有一个软件输出的数据是以每行249个来输出的,而我们当时的数据采集要求是每行240个,这样就需要将这些数据重新排列。当时采用了笨方法,纯手工打造,每行用手重新输入的,费了好大力气。
这阵子有些轻闲,所以上网去搜寻了下方法。没想到碰到高人指点,茅塞顿开。不敢独享,特此拿出来大家共勉。
我这里举个简单的例子吧:
原始数据(4行8列):
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 |
准备转换成为每行5个数据(即:6行5列),目标数据:
1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 |
在Excel中目标区的公式输入为:=OFFSET($A$1,INT(((ROW()-5)*5+COLUMN()-1)/8),MOD((ROW()-5)*5+COLUMN()-1,8))
这样就可以在目标区域得到我们想要的数据了。
上图中A5到E10的公式就是“=OFFSET($A$1,INT(((ROW()-5)*5+COLUMN()-1)/8),MOD((ROW()-5)*5+COLUMN()-1,8))”
下面说说这样做的原理:
1.用(ROW()-1)*5+COLUMN()-1根据行列的变化生成一个序列
第一行是0,1,2,3,4
第二行是5,6,7,....依次类推
2.用INT(((ROW()-1)*5+COLUMN()-1)/8)来使上述序列在是8的多少整数倍,从而得到行偏移量
3.用MOD((ROW()-1)*5+COLUMN()-1,8)来得到列偏移量
4.利用Offset得到结果。