不知道大家平时整理数据是使用什么样的方法, 我经常需要整理一些网络数据, 网络数据整理一直是个难题, 导出的网络数据要不是单列的, 要不就是有一些特殊符号, 都需要整理后才能使用, 这不, 领导让小红把后套导出的数据都整理下, 2000 多条数据, 还这么乱, 小红当时看到就慌了, 不过还好, 能手还没走;
现在很多公司都在网上处理业务, 因此会有很多从网络上导出或者直接从网页上复制粘贴得到的数据, 例如下图:
A 列是网络上导出的原始数据, 现需要整理成 C,D,E 列的形式. 为了便于理解, 这里用颜色对前三组数据进行了标注.
需求我想大家都看明白了, 对于这个问题该如何快速的处理, 就是我们今天要分享的内容.
方案一:
要想高效使用 Excel 解决问题, 善于发现规律是每个 Excel 人必须具备的能力!
就本例来说, 规律算是比较明显的, 如图所示:
只要能理解这个规律, 即便是不懂公式函数的新手, 也能很快解决这个问题, 不信看动画演示:
动画中的操作非常简单, 就是利用查找替换实现了有规律地引用数据. 但该法也有一些不足, 替换以后的公式不能下拉, 如果数据源增加了还得重新操作一次.
虽然这个方法要比手动复制粘贴要方便了得多, 但对于那些有一定经验的用户来说, 还是显得有些麻烦, 因此我们继续介绍第二个方法.
方案二:
使用三个公式, 就可以将 A 列的数据按要求提取到对应的列中. 第一个公式:=INDEX(A:A,ROW(A1)*6-5)
公式解析:
INDEX(数据区域, 数据所在的行), 按指定行从指定区域中提取数据. 这里要提取的数据是交易类型, 其数据分别位于 A 列的 1,7,13 等行, 使用 ROW(A1)6-5 可以得到对应的行号. 把 ROW(A1)6-5 作为 INDEX 函数的第 2 参数就有了第一个公式.
按照这个思路, 第二条公式为:=INDEX(A:A,ROW(A1)*6-3)
ROW(A1)6-3 这部分可以得到 3,9,15...... 这些行.
第三条公式为:=INDEX(A:A,ROW(A1)6-1)
原理都是一样的, 不再赘述.
该方案对比第一个使用查找替换的方案, 效率有了进一步的提升, 公式也很好理解, 适合绝大多数用户. 但是对于更高级别的用户来说, 用三个公式太麻烦, 他们追求的是一步到位, 使用一个公式得到三列数据.
方案三:
一个公式得到三列数据, 这样的公式真的有吗?
答案是肯定的, 请看截图:
公式为:
=INDEX($A:$A,ROW(A1)6+2COLUMN(A1)-7)
在这个公式中, 构造行号的部分明显要比前面分开的三个公式复杂很多, 不过只要明白了之前的思路, 这个公式还是不难理解的. 我们来对比一下之前那三个公式:
- =INDEX(A:A,ROW(A1)*6-5)
- =INDEX(A:A,ROW(A1)*6-3)
- =INDEX(A:A,ROW(A1)*6-1)
三个公式只有一点区别, ROW 后面减的数字依次是 5,3,1. 这三个数是随着列 (C,D,E 列) 对应递减的. 因此我们使用 COLUMN 获取列值, 然后用 7-2COLUMN(A1)得到数据 5, 右拉填充公式就得到 3,1. 用 7-2COLUMN(A1)取代之前公式中的 5, 右拉填充, 就实现一个公式得到三列数据的效果, 怎么样是不是很神奇?
来源: http://www.bubuko.com/infodetail-3111112.html