先上图,白色格子用来填数,灰色格子由计算而得。我是有多无聊,居然用VBA写了个算法,excel拉公式不香么。

20220324014630.png

看不清的右键-新标签页中打印图片。

上代码:

Sheet8.Cells(7, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C7:L7"))
Sheet8.Cells(8, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C8:L8"))
Sheet8.Cells(9, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C9:L9"))
Sheet8.Cells(12, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C12:L12"))
Sheet8.Cells(14, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C14:L14"))
Sheet8.Cells(15, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C15:L15"))
Sheet8.Cells(16, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C16:L16"))
Sheet8.Cells(17, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C17:L17"))
Sheet8.Cells(19, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C19:L19"))
Sheet8.Cells(20, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C20:L20"))
Sheet8.Cells(21, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C21:L21"))
Sheet8.Cells(23, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C23:L23"))
Sheet8.Cells(24, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C24:L24"))
Sheet8.Cells(25, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C25:L25"))
Sheet8.Cells(27, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C27:L27"))
Sheet8.Cells(28, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C28:L28"))
Sheet8.Cells(29, 13) = Application.WorksheetFunction.Sum(Sheet8.Range("C29:L29"))

For i = 3 To 13
    For j = 6 To 9
        Sheet8.Cells(10, i) = Sheet8.Cells(10, i) + Sheet8.Cells(j, i)
        Sheet8.Cells(13, i) = Sheet8.Cells(13, i) + Sheet8.Cells(j + 8, i)
    Next j
    For j = 19 To 21
        Sheet8.Cells(18, i) = Sheet8.Cells(18, i) + Sheet8.Cells(j, i)
        Sheet8.Cells(22, i) = Sheet8.Cells(22, i) + Sheet8.Cells(j + 4, i)
    Next j
    For j = 27 To 28
        Sheet8.Cells(26, i) = Sheet8.Cells(26, i) + Sheet8.Cells(j, i)
    Next j
    Sheet8.Cells(11, i) = Sheet8.Cells(12, i) + Sheet8.Cells(13, i) + Sheet8.Cells(18, i) + Sheet8.Cells(22, i) + Sheet8.Cells(26, i) + Sheet8.Cells(29, i)
    Sheet8.Cells(30, i) = Sheet8.Cells(10, i) + Sheet8.Cells(11, i)
    For j = 6 To 30
        If Val(Sheet8.Cells(j, i)) = 0 Then
            Sheet8.Cells(j, i) = ""
        End If
    Next j
Next i