二维码 购物车
部落窝在线教育欢迎您!

匪夷所思,妙到毫巅——一位Excel新人的网络数据整理办法

 

作者:老菜鸟来源:部落窝教育发布时间:2018-12-06 11:30:07点击:219

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

网络数据整理一直是个难题。导出的网络数据要么是单列的,要么是有一些特殊符号,都需要整理后才能使用。譬如如何把单列数据按属性变成多列数据? Excel新人、老手各有自己的一套方法,从匪夷所思的查找替换到一个公式横推所有数据都让人大开眼界。

 

 

现在很多公司都在网上处理业务,由此会有很多从网络上导出或者直接从网页上复制粘贴得到的数据,例如下图:

 

 

A列是网络上导出的原始数据,现需要整理成CDE列的形式。为了便于理解,这里用颜色对前三组数据进行了标注。

需求我想大家都看明白了,对于这个问题该如何快速的处理,就是我们今天要分享的内容。下面是三个不同用户,新手、熟手、老手分别提出的方案。

新手的方案:

不管是什么程度的用户,要想高效使用Excel解决问题,善于找发现规律都是必须具备的能力!

就本例来说,规律算是比较明显的,如图所示:

 

 

只要能理解这个规律,即便是不懂公式函数的新手,也能很快解决这个问题,不信看动画演示:

 

 

动画中的操作非常简单,就是利用查找替换实现了有规律地引用数据。作为Excel新手,能想到这种方法,真的是妙!但是该法也有一些不足,替换以后的公式不能下拉,数据源增加了还得重新操作一次。(想深入了解查找替换法的请看小白中秋福利:妙用“=”进行查找替换,一些Excel函数不用学了!》)

虽然这个方法要比手动复制粘贴方便了很多,但对于那些有了一定经验的用户来说,还是显得有些麻烦,因此我们继续介绍第二个方法。

熟手的方案:

使用三个公式,就可以将A列的数据按要求提取到对应的列中。第一个公式:=INDEX(A:A,ROW(A1)*6-5)

 

 

公式解析:

INDEX(数据区域,数据所在的行),按指定行从指定区域中提取数据。要提取的是交易类型,其数据分别位于A列的1713等行,使用ROW(A1)*6-5可以得到对应的行号。把ROW(A1)*6-5作为INDEX函数的第2参数就有了第一个公式。

按照这个思路,第二条公式为:=INDEX(A:A,ROW(A1)*6-3)

 

 

ROW(A1)*6-3这部分可以得到3915……这些行。

第三条公式为:=INDEX(A:A,ROW(A1)*6-1)

 

 

原理都是一样的,不再赘述。

该方案对比第一个使用查找替换的方案,效率有了进一步的提升,公式也很好理解,适合绝大多数用户。但是对于更高级别的用户来说,用三个公式太麻烦,他们追求的是一步到位,使用一个公式得到三列数据。

老手方案:

一个公式得到三列数据,这样的公式真的有吗?

答案是肯定的,请看截图:

 

Excel教程

 

公式为:=INDEX($A:$A,ROW(A1)*6+2*COLUMN(A1)-7)

在这个公式中,构造行号的部分明显要比前面分开的三个公式复杂很多,不过只要明白了之前的思路,这个公式还是不难理解的。我们来对比一下之前那三个公式:

=INDEX(A:A,ROW(A1)*6-5)

=INDEX(A:A,ROW(A1)*6-3)

=INDEX(A:A,ROW(A1)*6-1)

三个公式只有一点区别,ROW后面减的数字依次是531。这三个数是随着列(CDE列)对应递减的。因此我们使用COLUMN获取列值,然后用7-2*COLUMN(A1)得到数据5,右拉填充公式就得到31。用7-2*COLUMN(A1)取代之前公式中的5,右拉填充,就实现一个公式得到三列数据的效果,怎么样是不是很神奇?

最后这个公式虽然很厉害,但对于一部分用户来说还是有点困难的,原因是他们对数列构造的方法还不太熟悉。这部分用户可以参考之前发过的一篇教程Excel教程:等差数列、循环数列和重复数列构造》学习

看到最后,新手、熟手、老手,不知你是否赞同还是新手的方案最为匪夷所思?!

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

看不懂查找替换法的看这篇《小白中秋福利:妙用“=”进行查找替换,一些Excel函数不用学了!

根据数列规律写公式Excel教程:等差数列、循环数列和重复数列构造

网络数据用函数出错的解决方法 你一定要了解:公式没错Vlookup仍找不到数据的3大原因