作者: 大数据观察来源: 大数据观察时间:2017-09-17 18:06:560
我是电商数据分析师薛奔,前几天我给亲爱的萝卜网投递了Excel内战:数据透视表PK函数应用,据说反响不错,他们说希望我多多投稿给他们。
那今天就来分享一个做电商的童鞋常常遇到的数据处理问题。为方便演示,我把源数据内容弄的少些。
源数据如图1:看到这数据大家肯定不陌生,可以说常常和这些打交道。因为我们不可能直接对A列进行分析,我们只能对B,C列进行透视表分类汇总。然后分析我们的订单分布情况,这应该是物流运营者或淘宝店主经常干的事情。不夸张地说,很多人说自己在做数据分析时,但其实他们对数据处理这块都搞不定。数据无法处理成功,如何下一步分析,是吧。
回到正题:那怎么把A列里的数据进行分离呢。好在这数据还算规范,没有什么多余的空格,省市之间都有严格的省市进行区分。
所以我们差不多可以确定可以用省和区来对他们进行区分。有人问那上海市北京市这些呢,我们先不研究那么复杂,先解决这2个。最后谈复杂的。
问题1:解决省份或自治区的提取
分析思路如下:
我们用find函数来查找省或区的位置,如果是山东省这种的话,find区时就会出错。这里用上一个函数isnumber,也就是判断是不是数字的意思。如果是山东省这种的话,find区就会出错,返回false。那么再嵌套一个if判断,false就返回区,正确就返回省。
所以第一步的函数公式为:=IF(ISNUMBER(FIND("区",A2)),"区","省")
如何分步分析公式内部的东西,请活用快捷键F9。把你要分析的公式部分选中,然后按F9.比如这样:
这里我们已经完成了对省和区的判断了。
那第二步就很方便了:
Left函数返回区或省的位置就行了。=LEFT(A2,FIND(B2,A2))
至此,我们就把省份或自治区完美的提取了。组合函数公式为:=LEFT(A2,FIND(IF(ISNUMBER(FIND("区",A2)),"区","省"),A2))
总结思路:
1 返回单元内的位置必然想到find。Find返回是数值,find不到就返回value错误,所以要想到isnumber。所以函数的认知要多要全,本人认知函数108个,2副牌的数量,所以信手拈来。
2 要注意文本的引号是英文状态的,括号也是英文状态,原因你懂的,美国人发明的excel当然英文状态。
问题2:提取城市
既然前面已经有了省或自治区,那剩下的就是城市了,所以很简单。直接晒结果了。
Len是返回文本字符串或单元格内容的长度,就这么简单。
问题3:直辖市的数据处理
最后来谈下如果是上海市上海市这种怎么办:比如:
我的思路是为他专门开辟一列辅助列,然后计算单元格内市的数量。为2的话,就属于上海市这种。最后的公式为:
=LEFT(A11,IF(LEN(A11)-LEN(SUBSTITUTE(A11,"市",""))=2,FIND("市",A11),""))
以上是我为大家设计的省份地区分离公式,不一定是最简洁的,但一定是思路清晰而有效的,拿来就能用。
总结公式:
1.提取省或自治区公式,以A2作为数据源:
=LEFT(A2,FIND(IF(ISNUMBER(FIND("区",A2)),"区","省"),A2))
2.提取剩下的城市:=RIGHT(A2,LEN(A2)-LEN(E2))
3.提取直辖市那种特殊的情况:=LEFT(A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,"市",""))=2,FIND("市",A2)))
函数的字符除非太长,不然长一点,短一点对运算影响不大,关键是嵌套的思路清晰。
本篇结束:也希望大家多多关注我的Excel视频教程作品:
Excel2010职场基础制表应用 。
我是萝卜网的特邀讲师薛奔。我目前就职于国内大型电商平台网站运营分析师,擅长报表设计和数据处理自动化。
永洪科技
致力于打造全球领先的数据技术厂商