如何在Excel中将分散的同类键值对整理到对应列？

针对你提到的大型Excel表格中，键值对分散在每行不同单元格的情况，以下是三种实用的解决方法：

方法一：使用Excel函数（适合中小数据量） #

在目标列（比如你要放borough的第一列空白单元格）输入以下公式，下拉即可批量提取：

• Borough列：
  =IFERROR(TRIM(MID(TEXTJOIN(" ",TRUE,$C2:$Z2),SEARCH("borough': '",TEXTJOIN(" ",TRUE,$C2:$Z2))+11,SEARCH("'",TEXTJOIN(" ",TRUE,$C2:$Z2),SEARCH("borough': '",TEXTJOIN(" ",TRUE,$C2:$Z2))+11)-SEARCH("borough': '",TEXTJOIN(" ",TRUE,$C2:$Z2))-11)),"")
• Postal Code列：
  =IFERROR(TRIM(MID(TEXTJOIN(" ",TRUE,$C2:$Z2),SEARCH("postal_code': '",TEXTJOIN(" ",TRUE,$C2:$Z2))+15,SEARCH("'",TEXTJOIN(" ",TRUE,$C2:$Z2),SEARCH("postal_code': '",TEXTJOIN(" ",TRUE,$C2:$Z2))+15)-SEARCH("postal_code': '",TEXTJOIN(" ",TRUE,$C2:$Z2))-15)),"")
• Street列：
  =IFERROR(TRIM(MID(TEXTJOIN(" ",TRUE,$C2:$Z2),SEARCH("street': '",TEXTJOIN(" ",TRUE,$C2:$Z2))+9,SEARCH("'",TEXTJOIN(" ",TRUE,$C2:$Z2),SEARCH("street': '",TEXTJOIN(" ",TRUE,$C2:$Z2))+9)-SEARCH("street': '",TEXTJOIN(" ",TRUE,$C2:$Z2))-9)),"")

注意：公式中的$C2:$Z2是原数据的单元格范围，根据你的表格实际列范围调整。

方法二：使用Power Query（适合大型表格，效率高） #

Power Query是Excel处理批量数据的利器，步骤如下：

1. 选中原数据区域，点击「数据」选项卡 → 「从表格/区域」，导入Power Query编辑器（若提示表格有表头，按需勾选）。
2. 选中所有数据列，点击「转换」选项卡 → 「合并列」，分隔符选择「空格」，将合并后的列命名为「合并内容」。
3. 点击「添加列」→ 「自定义列」，分别创建三个列：
   • Borough：= Text.BetweenDelimiters([合并内容], "'borough': '", "'")
   • Postal Code：= Text.BetweenDelimiters([合并内容], "'postal_code': '", "'")
   • Street：= Text.BetweenDelimiters([合并内容], "'street': '", "'")
4. 删除多余的原数据列和「合并内容」列，点击「关闭并上载」，即可得到整理好的表格。

方法三：VBA宏（适合自动化批量处理） #

如果需要重复处理这类表格，可以用VBA宏实现一键整理：

1. 打开Excel，按Alt+F11进入VBA编辑器。
2. 右键点击左侧工程窗口的当前工作表 → 「插入」→ 「模块」。
3. 粘贴以下代码：

Sub OrganizeKeyValuePairs()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim cellText As String
    Dim boroughVal As String, postalVal As String, streetVal As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 添加目标列表头
    ws.Cells(1, lastCol + 1).Value = "borough"
    ws.Cells(1, lastCol + 2).Value = "postal_code"
    ws.Cells(1, lastCol + 3).Value = "street"
    
    For i = 2 To lastRow
        boroughVal = ""
        postalVal = ""
        streetVal = ""
        ' 遍历当前行所有单元格
        For j = 1 To lastCol
            cellText = ws.Cells(i, j).Value
            If InStr(cellText, "'borough': ") > 0 Then
                boroughVal = Trim(Mid(cellText, InStr(cellText, "'borough': '") + 11, InStr(InStr(cellText, "'borough': '") + 11, cellText, "'") - InStr(cellText, "'borough': '") - 11))
            ElseIf InStr(cellText, "'postal_code': ") > 0 Then
                postalVal = Trim(Mid(cellText, InStr(cellText, "'postal_code': '") + 15, InStr(InStr(cellText, "'postal_code': '") + 15, cellText, "'") - InStr(cellText, "'postal_code': '") - 15))
            ElseIf InStr(cellText, "'street': ") > 0 Then
                streetVal = Trim(Mid(cellText, InStr(cellText, "'street': '") + 9, InStr(InStr(cellText, "'street': '") + 9, cellText, "'") - InStr(cellText, "'street': '") - 9))
            End If
        Next j
        ' 写入目标列
        ws.Cells(i, lastCol + 1).Value = boroughVal
        ws.Cells(i, lastCol + 2).Value = postalVal
        ws.Cells(i, lastCol + 3).Value = streetVal
    Next i
End Sub

4. 回到Excel，按Alt+F8调出宏窗口，选择OrganizeKeyValuePairs并执行。注意：运行前请备份数据，避免意外。

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