一. 前言
问题来源于
=LEFT(P2,MIN(FIND({"省","市","区"}, P2&"省市区")))
被问及一个问题, find()中的大括号是什么意思.
这个, 很简单, 大括号是excel在表格公式实现数组的符号, 具体见Excel有趣函数系列-lookup | Lian (kyouichirou.github.io).
关键不在于此, 而是突然对于excel公式如何处理地址切割问题起了兴趣.
检索了一下必应, 并未发现相对可行的演示案例, 大部分的示例excel公式不是过于简陋, 就是各种函数层层叠叠, 令人望而生畏. 有没有简单易于理解的切割方式呢, 于是动手尝试一下.
二. 问题解析
以下内容并不是严谨讨论如何精确将各类型地址的不同部分切割开, excel公式也还没强大到这个程度.
# 四川省成都市武侯区状元坊A栋1010号
将上述的地址拆分成四列:
| 省份 | 城市 | 区 | 详情 |
|---|---|---|---|
| 四川省 | 成都市 | 武侯区 | 状元坊A栋1010号 |
对于文本进行不同位置, 不同长度的截断, 首选是mid()函数.
简单的想法, 就是分段截取:
= mid(a1, 1, find("省", a1))
= mid(a1, find("省", a1) + 1, find("市", a1))
= mid(a1, find("市", a1) + 1, find("区", a1))
= mid(a1, find("区", a1) + 1, len(a1))
这种方法简单, 易于理解, 但显然不够酷, 需要输入多段公式.
仔细观察会发现上述公式呈现一定的规律性, 每个mid()的起始位置都是上一段的结尾位置 + 1, 关键就在于能否实现一个类似于循环的操作来处理.
暂时先来看看, 上面的公式, 实现些什么, 如何实现.
=LEFT(a1,MIN(FIND({"省","市","区"}, a1&"省市区")))
find(), 按顺序检索{"省","市","区"}三个字出现的位置
min(), 返回找到的第一个位置的值
a1&"省市区", 防止找的时候, 没有值, 出现错误的情况
left(), 截取
这个公式只能实现简单的开头部分省市区截取.
三. 解决
在上面已经提及, 最简单的方式实现, 需要实现类似的循环操作, 那么就尝试构建出这个循环.
# 第一次, 没有思路, 逐步构建, 较为粗糙
=MID(A2, IF({1,0,0,0} =1, 1, FIND({"","省","市","区"}, "" & A2) + 1), FIND({"省","市","区","#"}, A2 & "#") - IFERROR(FIND({"?","省","市","区","#"}, A2 & "#"), 0) )
第一次, 是从1开始, "省"结束
第二次, "省" + 1开始, "市"结束
之后的起始位置都是, 结尾的位置 + 1
...
{1,0,0,0} =1, 让第一次等于1, 单独执行
之后的执行都是
FIND({"","省","市","区"}, "" & A2) + 1, 第一次执行, 随意让find()检索内容, 反正不返回结果;
注意find()找不到就会返回na值
截取的长度, 第一次截取的是"省"出现的位置, 第二次截取的长, 是"市"出现的位置减去"省"出现的位置, 之后循环往复
FIND({"?","省","市","区","#"}, "?"号的作用是第一次让find()找不到值, 返回na被IFERROR函数捕获, 返回0
FIND({"省","市","区","#"}, 最后一次时, "#"是返回整个字符的长度
| 省份 | 城市 | 区 | 详情 | |
|---|---|---|---|---|
| 四川省 | 成都市 | 武侯区 | 状元坊A栋1010号 | #N/A |
实现之后, 将上面的公式整理一下, 因为很多内容是多余的.
# 整理之后就很简洁了, 理解也不难
# 简单的思路就是, 第一次时, 注意开始的位置,/截取长度 最后一次时, 注意截取的长度
# IFERROR(FIND({"?","省","市","区"},A2)+1,1), find()查找起始的位置, IFERROR捕获故意导致的错误用于第一次的起始位置的设置, ?号只是随意的字符
# FIND({"省","市","区","#"},A2&"#")-IFERROR(FIND({"?","省","市","区"},A2),0), 返回截取的字符长度
# 关键主要还是在于第一次
=MID(A2,IFERROR(FIND({"?","省","市","区"},A2)+1,1),FIND({"省","市","区","#"},A2&"#")-IFERROR(FIND({"?","省","市","区"},A2),0))
| 地址 | 省份 | 市 | 区 | 详情 |
|---|---|---|---|---|
| 山东省青岛市市南区香港西路63号65号汇融广场12号楼1单元501号 | 山东省 | 青岛市 | 市南区 | 香港西路63号65号汇融广场12号楼1单元501号 |
| 山西省大同市平城区红旗街与开源街交汇处东北万向城3号楼2单元505号 | 山西省 | 大同市 | 平城区 | 红旗街与开源街交汇处东北万向城3号楼2单元505号 |
| 江苏省无锡市滨湖区太湖大道景华苑4号楼2单元404号 | 江苏省 | 无锡市 | 滨湖区 | 太湖大道景华苑4号楼2单元404号 |
| 黑龙江省大庆市萨尔图区友谊大街友谊1区9号楼1单元104号 | 黑龙江省 | 大庆市 | 萨尔图区 | 友谊大街友谊1区9号楼1单元104号 |
| 江西省上饶市信州区东市街道茶圣路209号路桥家园11号楼1单元405号 | 江西省 | 上饶市 | 信州区 | 东市街道茶圣路209号路桥家园11号楼1单元405号 |
| 黑龙江省哈尔滨市道外区南直路322号淮河小区4号楼1单元402号 | 黑龙江省 | 哈尔滨市 | 道外区 | 南直路322号淮河小区4号楼1单元402号 |
| 浙江省温州市洞头区北岙街道复兴路1号碧海云天7号楼3单元501号 | 浙江省 | 温州市 | 洞头区 | 北岙街道复兴路1号碧海云天7号楼3单元501号 |
四. 小结
拆分地址是相对麻烦的, 上述只是非常理想, 可以满足特定格式要求(省-市-区-详情)的字符串, 假如出现北京, 重庆等直辖市, 或者是地址中的县级市, 不带有市, 区的地址, 单纯在excel公式上操作是不现实的.
以上仅作为演示, 如何处理这种近似于循环的操作在公式中.
在python中, 使用cpca库, 效果不错.
@Lian ➜ ~\Desktop ( base 3.9.12) pip install cpca
@Lian ➜ ~\Desktop ( base 3.9.12) 6.504s python
Python 3.9.12 (main, Apr 4 2022, 05:22:27) [MSC v.1916 64 bit (AMD64)] :: Anaconda, Inc. on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cpca
>>> location_str = ["徐汇区虹漕路461号58号楼5楼", "泉州市洛江区万安塘西工业区", "北京朝阳区北苑华贸城"]
>>> df = cpca.transform(location_str)
>>> df
省 市 区 地址 adcode
0 上海市 市辖区 徐汇区 虹漕路461号58号楼5楼 310104
1 福建省 泉州市 洛江区 万安塘西工业区 350504
2 北京市 市辖区 朝阳区 北苑华贸城 110105
