Excel中统计单列多行逗号分隔城市名称的出现次数

以下是几种适配「后续新增行自动/便捷更新」需求的解决方案：

方法一：动态数组公式（Excel 365/2021 适用，自动更新） #

假设城市数据在A列，在空白单元格（比如C1）输入以下公式，会自动生成包含所有城市及其出现次数的动态表格，新增行后数据自动刷新：

=LET(
    all_cities, TOCOL(TEXTSPLIT(A:A, ", ", , TRUE), 1),
    unique_cities, UNIQUE(all_cities),
    HSTACK(unique_cities, COUNTIF(all_cities, unique_cities))
)

• 公式说明：
  • TEXTSPLIT(A:A, ", ", , TRUE)：按逗号+空格拆分A列所有单元格内容，忽略空值
  • TOCOL(..., 1)：将拆分后的多列数据合并为单列，跳过空单元格
  • UNIQUE：提取不重复的城市名称
  • HSTACK：将城市名称和对应计数横向拼接成表格

方法二：Power Query（全Excel版本适用，一键刷新） #

1. 选中存储城市的列，点击「数据」选项卡 →「从表格/区域」（旧版本选「自表格」），勾选「我的表格有标题」后进入Power Query编辑器
2. 在编辑器中：
   • 选中数据列，点击「转换」→「拆分列」→「按分隔符」，选择「逗号」，勾选「拆分为行」
   • 点击「转换」→「修整」，清除城市名称前后的空格
   • 点击「开始」→「分组依据」，分组列选拆分后的城市列，新列名设为「出现次数」，操作选择「计数行」
3. 点击「关闭并上载」，将统计结果导出到新工作表。后续新增数据后，右键结果表格 →「刷新」即可更新统计

方法三：VBA宏（一键更新，适合批量操作） #

按Alt+F11打开VBA编辑器，插入模块后粘贴以下代码，运行宏即可完成统计，新增数据后再次运行宏即可更新：

Sub CountCities()
    Dim ws As Worksheet, dataRng As Range, cell As Range
    Dim cityDict As Object, cities As Variant, i As Integer
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") '替换为你的工作表名称
    Set dataRng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) '假设数据从A2行开始
    Set cityDict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dataRng
        If cell.Value <> "" Then
            cities = Split(Trim(cell.Value), ", ")
            For i = LBound(cities) To UBound(cities)
                cityDict(cities(i)) = cityDict(cities(i)) + 1
            End If
        End If
    Next cell
    
    '输出统计结果到B、C列
    ws.Range("B1:C1") = Array("城市", "出现次数")
    ws.Range("B2:C" & ws.Rows.Count).ClearContents
    ws.Range("B2").Resize(cityDict.Count, 1).Value = Application.Transpose(cityDict.Keys)
    ws.Range("C2").Resize(cityDict.Count, 1).Value = Application.Transpose(cityDict.Items)
End Sub

内容的提问来源于stack exchange，提问作者design vogue