这是知乎上的一个问题¹：EXCEL如何实现不规则地址模糊查找精准匹配城市名？。题主给了两张表，表一是收货地址信息，表二是省市对照表。

• 表一：收获地址

• 表二：对照表

梳理解决思路

这个问题现实中还是比较常见的，题主需要知道在 Excel 里如何实现，如果用 Excel VBA 实现估计比较难，其他答主也做了一些尝试，但是代码恶心到不想再看第二眼，所以这里给出 R 语言优雅的解决方案：

• 方案一：通过 fuzzyjoin 包实现模糊匹配，但是需要提前给出对照表，最终从对照表里找到“距离最近的”结果。

• 方案二：调用高德地图 API 获取逆地理信息，这种方法不需要提前给出对照表，但是需要用户去高德地图开发者官网创建 API 应用并获取相应的 KEY。

• 方案三：利用正则表达式提取省、市信息，但是前提是收货地址数据里有省市这样的字符，对原始数据的质量要求较高。

代码实现

方案一：fuzzyjoin 包实现模糊匹配

# 加载相关 R 包
library(dplyr)

# 读取数据
table1 = readxl::read_xlsx("./data/table1.xlsx")  # 待匹配表
table2 = readxl::read_xlsx("./data/table2.xlsx")  # 省-市对照表

# 省市对照表处理（把省市放一起，增强信息）
table2 |> 
  tidyr::pivot_longer(
    everything(),
    names_to = "省份",
    values_to = "市县"
  ) |> 
  mutate(
    省市 = paste0(省份, 市县)
  ) -> ref

# 地址信息与省市信息模糊匹配
table1 |> 
  fuzzyjoin::stringdist_left_join(
    y = ref, 
    by = c(地址 = "省市"),
    max_dist = 0.3, 
    method = "jw"
  ) |> 
  select(-省市) -> result

方案二：调用高德地图 API

# 加载相关 R 包
library(xml2)
library(purrr)

# 先定义一个函数调用高德 API 获取省市信息
get_city = function(address) {

  # 定义一个高德API Key
  key = Sys.getenv("key")

  # 根据 address 信息生成逆地理信息网址
  url = paste0(
    'https://restapi.amap.com/v3/geocode/geo?address=',
    address, '&output=XML', '&key=', key
  )
  
  # 从逆地理信息网址获取城市信息
  city_single = \(url) {
    url %>%
      read_html(encoding = 'utf-8') |> 
      xml_find_all(".//city") |> 
      xml_text() -> city
    city[1]
  }
  
  # 提前对可能存在的报错信息进行设置
  safe_city_single = safely(
    city_single, 
    otherwise = NA_real_
  )
  
  # 批量获取城市信息
  url |>  
    map(safe_city_single) |>  
    list_transpose() -> city_info
  
  #返回最终结果
  return(city_info$result)
  
}

# 读取数据
df = readxl::read_xlsx("./data/table1.xlsx")  # 待匹配表

# 利用自定义函数处理数据并返回结果
df |>  
  dplyr::mutate(
    # 去掉后面部分无用信息
    市 = get_city(address = substr(地址, 1, 15))
  ) -> result

方案三：正则表达式

# 读取数据
df = readxl::read_xlsx("./data/table1.xlsx")  # 待匹配表

df |> 
  dplyr::mutate(
    市 = stringr::str_extract(地址, "(?<=省).*?市"), 
    直辖市 = stringr::str_extract(地址, "^[^省]*?市")
  ) -> result