巧妙应用Excel函数实现省份城市数据分离

本文由微博@Excel报表顾问 投稿。@Excel报表顾问 大名薛奔，是位电商数据分析师，擅长于报表自动化等，Excel技巧是其强项之一

---

今天分享一个做电商零售的童鞋常常遇到的数据处理问题。为方便演示，我把源数据内容弄的少些。

源数据如图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报表顾问 在我的知了帮上共开发了两款产品

产品1：电商|零售数据处理10大技巧

共10集，每集15-20分钟。详情请点击最下方的阅读原文，购买后我们会给你发送视频你也可以自行下载。

产品2：Excel在人力资源应用：高效录入员工信息

共10集，每集15-20分钟。可以复制这个网址进行购买：http://www./goods.php?id=32