如何按分类循环处理空行分隔行块并计算品类销售占比

表格结构 #

不同品类的行块以含~~~~~~~~的空行分隔：

需求 #

通过VBA循环按品类处理每个行块，计算每个商品销量占所属品类总销量的百分比，将结果写入Z列。

现有代码 #

全品类总占比代码（可正常运行） #

Last = Cells(Rows.Count, "A").End(xlUp).Row
orderSold = Application.Sum(Range("D:D"))
orderStock = Application.Sum(Range("E:E"))
Order = orderSold - orderStock
totalSales = Application.Sum(Range("C:C"))

For i = Last To 1 Step -1
Cells(i, "Z").FormulaR1C1 = "=RC[-23]/ " & totalSales
Next

尝试的错误代码（导致程序崩溃） #

'Each Category
Last = Cells(Rows.Count, "A").End(xlUp).Row
Do Until Cells(i + 1).Value = "" & Cells(i + 2).Value = ""
totalSales = Cells(i).End(xlUp).Value
Cells(i, "Z").FormulaR1C1 = "=RC[-23]/&" & totalSales
Loop

解决方案代码 #

Sub CalculateCategorySalesPercentage()
    Dim lastRow As Long
    Dim currentRow As Long
    Dim categoryStart As Long
    Dim categoryTotal As Double
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    categoryStart = 2 ' 假设表头在第1行，数据从第2行开始
    
    ' 遍历所有行，处理每个品类块
    For currentRow = 2 To lastRow
        ' 遇到分隔行时，计算当前品类占比并切换到下一个品类
        If Cells(currentRow, "A").Value = "~~~~~~~~" Then
            categoryTotal = Application.Sum(Range("C" & categoryStart & ":C" & (currentRow - 1)))
            Range("Z" & categoryStart & ":Z" & (currentRow - 1)).FormulaR1C1 = "=RC[-23]/ " & categoryTotal
            categoryStart = currentRow + 1
        ' 处理最后一个无分隔行的品类块
        ElseIf currentRow = lastRow Then
            categoryTotal = Application.Sum(Range("C" & categoryStart & ":C" & currentRow))
            Range("Z" & categoryStart & ":Z" & currentRow).FormulaR1C1 = "=RC[-23]/ " & categoryTotal
        End If
    Next currentRow
End Sub

代码说明 #

• 先获取数据最后一行，设定品类块起始行（默认表头在第1行）
• 逐行遍历，遇到~~~~~~~~分隔行时：
  1. 计算当前品类块的总销量
  2. 批量给该品类所有商品的Z列写入占比公式
  3. 更新下一个品类的起始行
• 单独处理末尾没有分隔行的品类块
• 按品类块批量处理，避免单循环逻辑混乱，提升运行效率

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