如何用Python代码拆分Excel单列公司数据至对应列？

针对你这种数千行的单列公司数据，以包含“Certified”的行的下一行作为新公司起始的拆分需求，推荐两种高效实现方法：

方法一：使用Power Query（无需代码，适合普通用户） #

Power Query是Excel内置的强大数据处理工具，能批量完成分组和拆分：

1. 导入数据到Power Query
   选中包含数据的单列区域，点击「数据」选项卡 → 「自表格/区域」，在弹出窗口中根据实际情况勾选「我的表格有标题」，点击「确定」进入Power Query编辑器。

2. 添加索引列
   点击「添加列」→ 「索引列」→ 「从0开始」，生成一列递增索引值，方便后续分组定位。

3. 标记公司起始行
   点击「添加列」→ 「自定义列」，输入公式：

   = if [Index] = 0 then 1 else if Text.Contains(Previous([Column1]), "Certified") then 1 else 0
   

   该公式会把第一个公司的起始行和每个“Certified”行的下一行标记为1，其余行标记为0。

4. 生成公司分组ID
   再次添加自定义列，输入累计求和公式生成每个公司的唯一ID：

   = List.Accumulate(List.Range(#"添加自定义"[Index], 0, [Index]+1), 0, (state, current) => state + #"添加自定义"[自定义]{current})
   

   重命名该列为「分组ID」。

5. 按分组提取各字段

   • 点击「转换」→ 「分组依据」，分组列选「分组ID」，操作选「所有行」，新列名设为「公司数据」，点击「确定」。
   • 添加自定义列过滤空行：

     = List.RemoveNulls(List.ReplaceMatchingItems([公司数据], {{"", null}}))
     

     重命名为「过滤后数据」。
   • 依次添加自定义列提取各字段：
     • 公司名：= [过滤后数据]{0}
     • 地址1：= [过滤后数据]{1}
     • 地址2：= [过滤后数据]{2}
     • 城市邮编：= [过滤后数据]{3}
     • 国家：= [过滤后数据]{4}
     • 网址：= [过滤后数据]{5}
     • 电话：= [过滤后数据]{6}
     • 传真：= [过滤后数据]{7}
     • 专长内容：= [过滤后数据]{9}（跳过索引8的"Specialties:"标签）

6. 加载回Excel
   删除不需要的辅助列，点击「主页」→ 「关闭并上载」，拆分好的数据会保存到新工作表。

方法二：使用VBA宏（适合有基础用户，处理速度更快） #

针对超大数据量，VBA宏的处理效率更高：

1. 打开VBA编辑器
   按下Alt + F11打开编辑器，右键点击左侧工作簿名称 → 「插入」→ 「模块」。

2. 粘贴宏代码
   替换代码中的Sheet1为你的源工作表名称，然后粘贴：

   Sub SplitCompanyData()
       Dim wsSource As Worksheet, wsDest As Worksheet
       Dim lastRow As Long, i As Long, companyStart As Long, destRow As Long
       Set wsSource = ThisWorkbook.Sheets("Sheet1") '替换为你的源工作表名
       Set wsDest = ThisWorkbook.Sheets.Add
       
       '设置目标列标题
       wsDest.Range("A1:J1") = Array("公司名", "地址1", "地址2", "城市邮编", "国家", "网址", "电话", "传真", "专长标签", "专长内容")
       destRow = 2
       companyStart = 1
       lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
       
       For i = 1 To lastRow
           If InStr(wsSource.Cells(i, "A").Value, "Certified") > 0 Then
               '提取当前公司有效数据（过滤空行）
               Dim dataArr As Variant
               dataArr = Filter(Application.Transpose(wsSource.Range(wsSource.Cells(companyStart, "A"), wsSource.Cells(i - 2, "A")).Value), "", False)
               
               '写入目标工作表
               If UBound(dataArr) >= 0 Then
                   wsDest.Cells(destRow, "A").Value = dataArr(0)
                   If UBound(dataArr) >= 1 Then wsDest.Cells(destRow, "B").Value = dataArr(1)
                   If UBound(dataArr) >= 2 Then wsDest.Cells(destRow, "C").Value = dataArr(2)
                   If UBound(dataArr) >= 3 Then wsDest.Cells(destRow, "D").Value = dataArr(3)
                   If UBound(dataArr) >= 4 Then wsDest.Cells(destRow, "E").Value = dataArr(4)
                   If UBound(dataArr) >= 5 Then wsDest.Cells(destRow, "F").Value = dataArr(5)
                   If UBound(dataArr) >= 6 Then wsDest.Cells(destRow, "G").Value = dataArr(6)
                   If UBound(dataArr) >= 7 Then wsDest.Cells(destRow, "H").Value = dataArr(7)
                   If UBound(dataArr) >= 8 Then wsDest.Cells(destRow, "I").Value = dataArr(8)
                   If UBound(dataArr) >= 9 Then wsDest.Cells(destRow, "J").Value = dataArr(9)
                   destRow = destRow + 1
               End If
               
               '更新下一个公司的起始行
               companyStart = i + 1
           End If
       Next i
       
       '处理最后一组未被循环覆盖的公司数据
       If companyStart <= lastRow Then
           dataArr = Filter(Application.Transpose(wsSource.Range(wsSource.Cells(companyStart, "A"), wsSource.Cells(lastRow, "A")).Value), "", False)
           If UBound(dataArr) >= 0 Then
               wsDest.Cells(destRow, "A").Value = dataArr(0)
               If UBound(dataArr) >= 1 Then wsDest.Cells(destRow, "B").Value = dataArr(1)
               If UBound(dataArr) >= 2 Then wsDest.Cells(destRow, "C").Value = dataArr(2)
               If UBound(dataArr) >= 3 Then wsDest.Cells(destRow, "D").Value = dataArr(3)
               If UBound(dataArr) >= 4 Then wsDest.Cells(destRow, "E").Value = dataArr(4)
               If UBound(dataArr) >= 5 Then wsDest.Cells(destRow, "F").Value = dataArr(5)
               If UBound(dataArr) >= 6 Then wsDest.Cells(destRow, "G").Value = dataArr(6)
               If UBound(dataArr) >= 7 Then wsDest.Cells(destRow, "H").Value = dataArr(7)
               If UBound(dataArr) >= 8 Then wsDest.Cells(destRow, "I").Value = dataArr(8)
               If UBound(dataArr) >= 9 Then wsDest.Cells(destRow, "J").Value = dataArr(9)
           End If
       End If
       
       MsgBox "数据拆分完成！"
   End Sub
   

3. 运行宏
   按下F5运行，或回到Excel点击「开发工具」→ 「宏」→ 选择SplitCompanyData→ 「执行」，拆分后的数据会自动生成在新工作表。

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