之前已经在大语言模型解放生产力里用Kimi实现了在Excel中，将 G 列中每个合并单元格对应的 L 列数据进行求和，并将结果放在 M 列中。过了一年了，又在原聊天里继续要求增加功能：对于G列合并的单元格，从上往下每隔一个对所在行填充颜色。经过几轮交流，完美搞定。

Sub CalculateSumsInMergedCells()
    Dim ws As Worksheet
    Dim columnLetter As String
    Dim resultColumnLetter As String
    Dim dataColumnLetter As String
    Dim cell As Range
    Dim sum As Double
    Dim cellValue As Variant
    Dim mergedArea As Range
    Dim mergeStart As Long
    Dim mergeEnd As Long
    Dim colorIndex As Long
    Dim col As Long
    Dim firstColumn As Long
    Dim lastColumn As Long
    Dim lastRow As Long

    Set ws = ActiveSheet
    columnLetter = "G" ' 定义要处理的列，可以更改为 "A", "B", "C" 等
    resultColumnLetter = "M" ' 定义结果要输出的列，可以更改为其他列
    dataColumnLetter = "L" ' 定义包含数据的列，可以更改为其他列

    ' 获取第一列和最后一列的列号
    firstColumn = 1 ' 第一列
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' 最后一列

    ' 清除结果列已有的数据并拆分所有合并的单元格，但保留第一行
    With ws.Range(resultColumnLetter & "2:" & resultColumnLetter & ws.Cells(ws.Rows.Count, resultColumnLetter).End(xlUp).Row)
        .UnMerge
        .ClearContents
    End With

    ' 初始化颜色索引
    colorIndex = 0

    ' 遍历指定列中的所有单元格
    For Each cell In ws.Range(columnLetter & "1:" & columnLetter & ws.Cells(ws.Rows.Count, columnLetter).End(xlUp).Row)
        ' 初始化求和变量
        sum = 0

        ' 检查单元格是否是合并单元格的一部分
        If cell.MergeCells Then
            If cell.Address = cell.MergeArea.Cells(1, 1).Address Then
                ' 计算合并区域中数据列的数据总和
                Dim mergedCell As Range
                For Each mergedCell In cell.MergeArea
                    cellValue = ws.Cells(mergedCell.Row, Columns(dataColumnLetter).Column).Value
                    If IsNumeric(cellValue) Then
                        sum = sum + Val(cellValue)
                    End If
                Next mergedCell

                ' 将计算结果放在结果列的相应位置
                ws.Cells(cell.Row, Columns(resultColumnLetter).Column).Value = sum

                ' 合并结果列的单元格
                ws.Range(ws.Cells(cell.Row, Columns(resultColumnLetter).Column), ws.Cells(cell.MergeArea.Rows(cell.MergeArea.Rows.Count).Row, Columns(resultColumnLetter).Column)).Merge

                ' 获取合并区域的起始和结束行
                mergeStart = cell.Row
                mergeEnd = cell.MergeArea.Rows(cell.MergeArea.Rows.Count).Row

                ' 根据颜色索引决定是否填充颜色
                If colorIndex = 1 Then
                    ' 填充从第一列到最后一列的相应行
                    For col = firstColumn To lastColumn
                        ws.Range(ws.Cells(mergeStart, col), ws.Cells(mergeEnd, col)).Interior.Color = RGB(255, 255, 0) ' 黄色
                    Next col
                Else
                    ' 清除从第一列到最后一列的相应行的颜色
                    For col = firstColumn To lastColumn
                        ws.Range(ws.Cells(mergeStart, col), ws.Cells(mergeEnd, col)).Interior.colorIndex = xlNone
                    Next col
                End If

                ' 更新颜色索引
                colorIndex = 1 - colorIndex
            End If
        Else
            ' 对于未合并的单元格，直接计算和赋值
            cellValue = ws.Cells(cell.Row, Columns(dataColumnLetter).Column).Value
            If IsNumeric(cellValue) Then
                sum = Val(cellValue)
                ws.Cells(cell.Row, Columns(resultColumnLetter).Column).Value = sum
            End If
        End If
    Next cell

    ' 为所有单元格添加框线
    lastRow = ws.Cells(ws.Rows.Count, firstColumn).End(xlUp).Row
    With ws.Range(ws.Cells(1, firstColumn), ws.Cells(lastRow, lastColumn)).Borders
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0) ' 黑色
        .Weight = xlThin
    End With
End Sub