如何加速Excel中CONCAT公式的运算速度？

针对你提供的CONCAT拼接公式运算缓慢的问题，可从公式优化、Excel设置、替代方案三个方向入手提速：

一、优化公式本身 #

1. 改用TEXTJOIN简化结构 #

TEXTJOIN比CONCAT在处理多元素拼接时效率更高，且能直接指定分隔符，减少手动拼接分隔符的重复操作。优化后的公式示例：

=TEXTJOIN(";",, 
  'Calls'!$A$1&","&'Calls'!A31,
  'Calls'!$B$1&","&TEXT('Calls'!B31,"mm/dd/yy"),
  'Calls'!$C$1&","&'Calls'!C31,
  'Calls'!$D$1&","&TEXT('Calls'!D31,"mmmm dd, yyyy"),
  'Calls'!$E$1&","&'Calls'!E31,
  'Calls'!$F$1&","&'Calls'!F31,
  'Calls'!$G$1&","&'Calls'!G31,
  'Calls'!$H$1&","&'Calls'!H31,
  'Calls'!$I$1&","&'Calls'!I31,
  'Calls'!$J$1&","&'Calls'!J31,
  'Calls'!$K$1&","&'Calls'!K31,
  'Calls'!$L$1&","&'Calls'!L31,
  'Calls'!$M$1&","&'Calls'!M31,
  'Calls'!$N$1&","&'Calls'!N31,
  'Calls'!$O$1&","&'Calls'!O31,
  'Calls'!$P$1&","&'Calls'!P31
)

2. 用数组公式批量处理（适用于Excel 365/2021及以上） #

通过数组一次性匹配标题与对应行内容，减少重复的单元格引用，下拉时自动适配行号：

=TEXTJOIN(";",, 
  'Calls'!$A$1:$P$1&","&
  IF(COLUMN('Calls'!$A$1:$P$1)=2, TEXT('Calls'!A31:P31,"mm/dd/yy"),
  IF(COLUMN('Calls'!$A$1:$P$1)=4, TEXT('Calls'!A31:P31,"mmmm dd, yyyy"),
  'Calls'!A31:P31))
)

注：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入。

二、调整Excel设置 #

• 切换手动重算：点击「公式」选项卡 → 「计算选项」→ 选择「手动」，需要计算时按F9触发，避免每次修改数据后自动重复计算。
• 禁用不必要加载项：点击「文件」→ 「选项」→ 「加载项」，禁用闲置的第三方加载项，释放系统资源。
• 转为结构化表格：选中数据区域按Ctrl+T转为Excel表格，使用结构化引用（如[@列名]），Excel会优化计算引擎对表格数据的处理逻辑。

三、替代方案（适合大量数据场景） #

1. Power Query批量处理 #

1. 选中「Calls」工作表的数据区域，点击「数据」→ 「从表格/区域」导入Power Query编辑器；
2. 添加自定义列，输入拼接逻辑（针对B列和D列单独设置格式）；
3. 完成后点击「关闭并上载」，将结果加载回Excel。Power Query采用批量运算模式，比逐单元格公式快数倍。

2. VBA宏批量生成结果 #

按下Alt+F11打开VBA编辑器，插入模块后粘贴以下代码（根据实际数据范围调整行号）：

Sub GenerateConcatenatedText()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim result As String
    
    Set ws = ThisWorkbook.Worksheets("Calls")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 31 To lastRow '从第31行开始处理，可按需修改起始行
        result = ws.Range("A1").Value & "," & ws.Cells(i, "A").Value & ";"
        result = result & ws.Range("B1").Value & "," & Format(ws.Cells(i, "B").Value, "mm/dd/yy") & ";"
        result = result & ws.Range("C1").Value & "," & ws.Cells(i, "C").Value & ";"
        result = result & ws.Range("D1").Value & "," & Format(ws.Cells(i, "D").Value, "mmmm dd, yyyy") & ";"
        result = result & ws.Range("E1").Value & "," & ws.Cells(i, "E").Value & ";"
        result = result & ws.Range("F1").Value & "," & ws.Cells(i, "F").Value & ";"
        result = result & ws.Range("G1").Value & "," & ws.Cells(i, "G").Value & ";"
        result = result & ws.Range("H1").Value & "," & ws.Cells(i, "H").Value & ";"
        result = result & ws.Range("I1").Value & "," & ws.Cells(i, "I").Value & ";"
        result = result & ws.Range("J1").Value & "," & ws.Cells(i, "J").Value & ";"
        result = result & ws.Range("K1").Value & "," & ws.Cells(i, "K").Value & ";"
        result = result & ws.Range("L1").Value & "," & ws.Cells(i, "L").Value & ";"
        result = result & ws.Range("M1").Value & "," & ws.Cells(i, "M").Value & ";"
        result = result & ws.Range("N1").Value & "," & ws.Cells(i, "N").Value & ";"
        result = result & ws.Range("O1").Value & "," & ws.Cells(i, "O").Value & ";"
        result = result & ws.Range("P1").Value & "," & ws.Cells(i, "P").Value & ";"
        
        '将结果写入目标单元格，例如写入第i行的Q列
        ws.Cells(i, "Q").Value = result
    Next i
End Sub

运行宏即可一次性生成所有行的拼接结果，速度远快于公式计算。

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