VBA Excel - 将小计公式插入特定行 [英] VBA Excel - Inserting Subtotal Formulas into Specific Rows

问题描述

我的问题是发生了什么事情这里和这里，但也不是。我有一个奇怪的数据集，其结构更像一个层次结构，而不是纯系列的数据点。这样的东西：

My issue is kind of a blend of what's happening here and here, but is very much neither. I've got a weird dataset that is structured more like a hierarchy than a pure series of data points. Something like this:

Item                           Budget      Sales
GROUP - Cats                      0         120
FY 13 Persian                     0           0
FY 13 Maine Coon                 12           0
FY 14 Maine Coon                 50           0
FY 12 Tabby                       1           0
FY 13 Tabby                       1           0
FY 14 Tabby                       2           0
FY 14 Alley                      12           0
GROUP - Dogs                      0         201
FY 14 Collie                     20           0
FY 14 Lab                        31           0
FY 13 Golden Retriever           12           0
FY 12 Golden Retriever            0           0
GROUP - Gold Fish                 0          50
FY 14 Goldfish                  100           0
FY 13 Clown Fish                 20           0
Tanks Fees                      150           0

我需要一个宏，可以整齐地识别GROUP行，然后对其下面的组进行求和，而不会捕获下一个组。换句话说，我需要猫预算线来计算猫的预算。

I need a macro that can neatly identify the GROUP lines and then sum the group underneath it -- without capture the next group. In other words, I need the cat budget line to sum only the cat budgets.

所以宏需要用GROUP *标识行，然后搜索直到找到下一个GROUP *，并将猫和狗之间的空格（优选作为一个功能）加在GROUP - Cats行上。

So the macro would need to identify the line with the "GROUP*", then search down until it finds the next "GROUP*", and sum the space in between Cats and Dogs -- preferably as a function -- on the "GROUP - Cats" line.

我知道这是可能的，但我担心这对VBA的基本能力来说太复杂了。

I know this has to be possible, but I'm worried it's too complicated for my basic abilities in VBA.

编辑：最终产品将是预算列中简单的= SUM（B3：B9）的公式。那么B10（狗群）将具有诸如= SUM（B11：B14）的公式。对于金鱼：= SUM（B16：18）。

The end product would be a formula in the Budget column that is simply =SUM(B3:B9). Then B10 (the Dogs GROUP) would have a formula such as =SUM(B11:B14). For Gold Fish: =SUM(B16:18).

但是由于我的每个数据集总是在变化（例如，本周在Cats部分可能有20行而不是前一周的18），我需要一个宏，可以找到GROUP行之间的空格。

But since each my dataset is always changing (for instance, this week there may be 20 lines in the Cats section instead of 18 the previous week), I need a macro that can find the space between the GROUP lines.

编辑2： VBA I目前使用的操作与我正在寻找的内容类似 - 它基本上是根据销售列中显示的数字分组和折叠我的部分：

EDIT 2: The VBA I'm using currently does something similar to what I'm looking for -- it essentially groups and collapses my sections based on the numbers that appear in the Sales column:

Dim rStart As Range, r As Range
Dim lLastRow As Long, sColumn As String
Dim rColumn As Range
'### The Kittens everywhere! thing is just to make sure the last group has an end point
Range("C1").End(xlDown).Offset(1).Value = "Kittens everywhere!"
    sColumn = "C"

With ActiveSheet.Outline
        .AutomaticStyles = False
        .SummaryRow = xlAbove
        .SummaryColumn = xlRight

lLastRow = Cells(Rows.Count, sColumn).End(xlUp).Row
With ActiveSheet
    Set rColumn = .Range(.Cells(1, sColumn), Cells(lLastRow, sColumn))
    With rColumn
        Set r = .Cells(1, 1)
        Do Until r.Row > lLastRow
            If rStart Is Nothing Then
                If r.Value = "0" Then
                    Set rStart = r
                End If
            Else
                If r.Value <> "0" Then
                    Range(rStart, r.Offset(-1, 0)).Rows.Group
                    Set rStart = Nothing
                End If
            End If
            Set r = r.Offset(1, 0)
        Loop
    End With
End With
ActiveSheet.Outline.ShowLevels RowLevels:=1
End With

Range("C:C").Find("Kittens everywhere!").ClearContents

还有更多的宏 - 因为它也在做一些事情，如突出显示GROUP行 - 但我确定如果我可以阻止这个SUM功能的东西到该部分或不。

There's more to the macro -- because it's also doing some things like highlighting the GROUP rows -- but I'm sure if I can jam this SUM function stuff into that section or not.

推荐答案

这是一个可能或可能不合适的建议，但它为您提供了一个选项。它使用内置的Excel SubTotal函数。有一些利弊（见下文）。

This is a suggestion that may or may not be appropriate but it does offer an option for you. It uses the built in Excel SubTotal function. There are some pros and cons (see below).

需要两个Subs，一个应用SubTotals：

It requires two Subs, one to apply SubTotals:

Sub STPets()
Dim ws As Worksheet
Dim strow As Long, endrow As Long, stcol As Long, endcol As Long
Dim drng As Range

strow = 3
stcol = 3  'Col C
endcol = 6  'Col F

Set ws = Sheets("Sheet1")

    With ws
        'find last data row
        endrow = Cells(Rows.Count, stcol).End(xlUp).Row

        'sort data
        Set drng = .Range(.Cells(strow, stcol), .Cells(endrow, endcol))
        drng.Sort Key1:=.Cells(strow + 1, stcol), Order1:=xlAscending, Header:=xlYes

        'apply Excel SubTotal function
        .Cells.RemoveSubtotal
        drng.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3)

    End With

End Sub 

和一个清除例如添加更多数据：

and one to Clear e.g. to add more data:

Sub RemoveSTPets()
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
ws.Cells.RemoveSubtotal

End Sub

可以通过使用分配给这些宏的两个按钮将数据总计和清除：

The data can be totalled and cleared at will, by using two buttons assigned to these macros:

正如你所看到的，需要稍微重新安排数据，但可以说这样做会使其更加灵活和一致数据库格式（展望未来？）。在列表中的任何地方添加新数据也更容易，更容易添加/更改组（代码），即清除>添加更多数据> re-Total。您可以根据Excel SubTotal功能进一步自定义。

As you can see it requires a slight re-arrangement of your data but arguably this makes it more flexible and consistent with database format (looking to the future?). It is also arguably easier to add new data anywhere in the list and easier to add/change groups (Codes) i.e. Clear > Add more data > re-Total. You can customise further in accordance with the Excel SubTotal functionality.

最后，有可能超越短缺，但还有进一步的想法可能 - 也可能是一个好主意，分离出13财年和 FY 14'标识符分为FY列。然后，您可以找到更灵活的方式对您的数据进行进一步分析。

Finally, at the risk of overstepping the brief, but further food for thought perhaps - it might also be a good idea to separate-out your 'FY 13' and 'FY 14' identifiers into a separate "FY" column. You may then find it more flexible to do further analysis on your data over time.

这篇关于VBA Excel - 将小计公式插入特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！