如何使用Excel VBA合并文件名倒数两连字符间值匹配的多行

匹配规则 #

提取每个单元格中图片文件名最后两个连字符之间的内容作为匹配键，将连续且匹配键相同的行合并为一行；不连续或匹配键不同的行保持独立。

示例 #

示例1

原始数据：

<img src="abcd-efgh-1234-567.jpg">
<img src="ijklmn-opqrst-1234-9876.jpg">
<img src="aabb-ccd-eeffgh-1234-000.jpg">

合并后：

<img src="abcd-efgh-1234-567.jpg"><img src="ijklmn-opqrst-1234-9876.jpg"><img src="aabb-ccd-eeffgh-1234-000.jpg">

示例2

原始数据：

<img src="abcd-efgh-432-567.jpg">
<img src="ijklmn-opqrst-432-9876.jpg">
<img src="aabb-ccd-eeffgh-765-000.jpg">
<img src="aabb-ccd-eeffgh-3210-000.jpg">
<img src="aabb-ccd-eeffgh-987-567.jpg">
<img src="aabb-ccd-eeffgh-987-7657.jpg">

合并后：

<img src="abcd-efgh-432-567.jpg"><img src="ijklmn-opqrst-432-9876.jpg">
<img src="aabb-ccd-eeffgh-765-000.jpg">
<img src="aabb-ccd-eeffgh-3210-000.jpg">
<img src="aabb-ccd-eeffgh-987-567.jpg"><img src="aabb-ccd-eeffgh-987-7657.jpg">

方法一：Excel公式实现（小批量数据适用） #

• 步骤1：提取匹配键
  假设数据在A列，从A1开始。在B1单元格输入以下公式，下拉填充到所有行：

  =MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))-1))+1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))-1))-1)
  

  这个公式会自动提取每个文件名最后两个-之间的内容，作为匹配的依据。

• 步骤2：标记连续匹配行
  在C1单元格输入公式，下拉填充：

  =IF(B1=B2,"合并","独立")
  

  用来标记当前行是否需要和下一行合并。

• 步骤3：合并内容
  在D1单元格输入数组公式（输入完成后按Ctrl+Shift+Enter确认），范围根据你的数据调整（示例中是A1:A6）：

  =TEXTJOIN("",TRUE,IF($B$1:$B$6=B1,$A$1:$A$6,""))
  

  之后可以添加辅助列E1，输入公式筛选出最终结果：

  =IF(B1<>B2,TRUE,IF(ROW()=1,TRUE,FALSE))
  

  下拉填充后，筛选E列为TRUE的行，就是合并后的内容。

方法二：Power Query实现（大批量数据适用） #

Power Query能自动化处理大量数据，步骤如下：

• 步骤1：导入数据到Power Query
  选中数据列，点击「数据」选项卡→「从表格/范围」，确认数据导入（如果没有表头可以手动添加）。

• 步骤2：提取匹配键
  点击「添加列」→「自定义列」，输入以下公式，命名为「匹配键」：

  let
      fileName = Text.BetweenDelimiters([Column1], "src="", ".jpg""),
      dashPositions = List.Reverse(Text.PositionOf(fileName, "-", Occurrence.All)),
      startPos = dashPositions{1} + 1,
      endPos = dashPositions{0},
      key = Text.Range(fileName, startPos, endPos - startPos)
  in
      key
  

  这段代码会先提取文件名，再定位最后两个-的位置，取出中间的文本作为匹配键。

• 步骤3：分组合并连续行

  • 添加索引列：点击「添加列」→「索引列」→「从0开始」。
  • 添加分组ID：再次添加自定义列，命名为「分组ID」，输入公式：

    let
        currentKey = [匹配键],
        prevKey = if [索引] = 0 then null else #"添加索引"{[索引]-1}[匹配键],
        groupID = if currentKey = prevKey then #"添加索引"{[索引]-1}[分组ID] else [索引]
    in
        groupID
    

    这里的#添加索引是上一步的步骤名，如果你的步骤名不同，记得对应修改。
  • 分组合并：点击「转换」→「分组依据」，设置：
    • 分组依据：「分组ID」
    • 新列名：「合并内容」
    • 操作：「所有行」
      然后将聚合公式修改为Text.Combine([Column1], "")，这样会把同一分组的行内容直接拼接。

• 步骤4：加载回Excel
  点击「关闭并上载」，合并后的结果就会出现在新的工作表中。

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