如何从 Excel 中的正确地址格式获取城市

Question 1

因此，这是我最接近获得 city 的方法。在 VBA 中添加反向函数，这仅适用于包含一个单词的 City

添加反向功能：

Step1. Open VBA = ALT + F11
Step2. Right click - insert module
Paste this code
Function Reverse(str As String) As String
    Reverse = StrReverse(Trim(str))
End Function

这是 Excel 公式

=MID(A2,FIND(",",A2) -FIND(" ",Reverse(LEFT(A2,FIND(",",A2))))+2,FIND(" ",Reverse(LEFT(A2,FIND(",",A2))))-2)

对于包含多个单词的城市，需要手动修复。我猜这只适用于较小的样本集。

为了进行额外的清理，我们可以使用邮政编码来获取城市。并突出显示具有多个城市的邮政编码以进行额外的清理

Answer

因此，这是我最接近获得 city 的方法。在 VBA 中添加反向函数，这仅适用于包含一个单词的 City

添加反向功能：

Step1. Open VBA = ALT + F11
Step2. Right click - insert module
Paste this code
Function Reverse(str As String) As String
    Reverse = StrReverse(Trim(str))
End Function

这是 Excel 公式

=MID(A2,FIND(",",A2) -FIND(" ",Reverse(LEFT(A2,FIND(",",A2))))+2,FIND(" ",Reverse(LEFT(A2,FIND(",",A2))))-2)

对于包含多个单词的城市，需要手动修复。我猜这只适用于较小的样本集。

为了进行额外的清理，我们可以使用邮政编码来获取城市。并突出显示具有多个城市的邮政编码以进行额外的清理

Question 2

这项任务的难易程度很大程度上取决于数据的清洁度。

话虽如此，这里有一种方法可以使用部分字符串匹配将地址连接到城市列表。这种方法并不完美，但可能有助于您更接近目标。

首先，创建一个查询来获取城市列表。这是按人口列出的 200 个美国城市列表：

let
    Source = Web.Page(Web.Contents("https://worldpopulationreview.com/us-cities")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rank", Int64.Type}, {"Name", type text}, {"State", type text}, {"2020 Pop", Int64.Type}, {"2010 Census", Int64.Type}, {"Change", Percentage.Type}, {"Density (km²)", Int64.Type}, {"Area (km²)", Int64.Type}})
in
    #"Changed Type"

要使用此代码，请使用数据>获取和转换数据>获取数据>从其他来源>空白查询。

当 Power Query 编辑器打开时，将上述代码粘贴到高级编辑器中，您可以从“主页”选项卡上的功能区访问它。在查询设置中，将查询命名为 Top200USCities。

完成后，使用 Close & Load 会将列表放入您的工作簿中：

此表将作为参考。在另一张表中，我有您的示例地址和其他几个地址：

我首先使用数据>获取和转换数据>从表/范围在该范围上创建查询。

然后我使用添加列>自定义列来定义一个名为 LookupCity 的新列，如下所示：

Table.SelectRows(
        Top200USCities,
        (lookup) => Text.Contains(Text.Lower([Address]),Text.Lower(lookup[Name]))
        )

像这样：

执行此操作后，您将在地址旁边看到一个新列，每行都显示“表格”字样。如果单击该列右上角的双箭头，则可以从查找表中选择要添加到地址表中的列。我将添加姓名、州和 2020 年人口。

结果如下：

第 1、2 和 3 行是正确的。第 4 行将道路名称与城市芝加哥匹配，第 5 行将道路名称与城市洛杉矶匹配，尽管该城市是多伦多。

此时，您可以停下来，将数据重新加载到工作簿中，然后手动解决所有错误。或者，您可以尝试再执行几个步骤，以取得更接近目标的结果。

假设我们可以说每个城市名称都以逗号结尾。如果这是真的，那么在 LookupCity 自定义列中使用它会使它更准确：

Table.SelectRows(
        Top200USCities,
        (lookup) => Text.Contains(Text.Lower([Address]),Text.Lower(lookup[Name]&","))
        )

现在我们说“地址是否包含以逗号结尾的这个城市？”结果好多了：

如果您的地址列表包含完整且拼写正确的城市名称，那么这一切都很好。如果没有，那么您可以尝试手动将城市名称变体添加到城市列表中。如果您已经有圣路易斯，但您的数据包含圣路易斯、圣路易斯、圣路易斯和 STL，那么您可以将其他城市添加到查找表中，同时复制其他元数据，这应该很有趣。像这样：

随着您向列表中添加更多城市名称的变体，您的查询将“捕获”越来越多的地址。

祝你好运。

Answer