VBA，需要基于另一列中的更改的有条件总和 [英] VBA, Need a conditional Sum based on a change in another column

问题描述

VBA的新功能.尝试遍历唯一ID和与每个ID相关的小计值的列表.

New to VBA. Trying to run through a list of unique id's and subtotal values related to each id.

此后，该总和需要在第一行的不同列中显示.

Afterwards, that sum total needs to be displayed in a different column on the first line.

注意事项:

• 当它找到新的ID时，小计需要重新开始，并且仅小计与之相关的值块.
• 每个小计仅需在列表中唯一ID的第一个实例旁边显示一次.
• 每个新工作表将具有5 k行或更多，并且数据将在相同的列中.大量数据.
• 工作表中的各列实际上并不是彼此相邻，而是在同一工作表中.
• 在每张印刷品中，监护权将在不同的行上更改.它需要遍历id并找到更改并仅求和该custid的值. 然后，该总和需要显示在第一行的另一列中.

• As it finds a new id, the subtotal needs to start over and only subtotal the block of values related to it.
• Each subtotal needs to be displayed only once next to the first instance of the unique id in the list.
• each new sheet will have 5 k rows or more and the data will be in the same columns. Lots of data.
• The columns in my sheet are not actually physically next to each other but are in the same sheet.
• in each print the custid will change at a different row. It needs to iterate through the id's and find the change and sum the values for that custid only. Afterwards, that sum total needs to be displayed in a different column on the first line.

这是一些基本的示例数据:

This is some basic sample data:

Totl Subttl CustID Amt.

Totl Subttl CustID Amt.

        123456  55.74
        123456  61.47
        223456  44.53
        223456  142.11
        223456  -142.11
        333456  44.53
        333456  52.89
        333456  118.37
        333456  354.80
        443456  6.49
        443456  44.53
        443456  162.74

为此:

Totl Subttl CustID Amt.

Totl Subttl CustID Amt.

946.09      117.21  123456  55.74
                    123456  61.47
            44.53   223456  44.53
                    223456  142.11
                    223456  -142.11
            570.59  333456  44.53
                    333456  52.89
                    333456  118.37
                    333456  354.80
            213.76  443456  6.49
                    443456  44.53
                    443456  162.74

推荐答案

事实证明，这比我最初预期的要简单.此代码假定唯一ID号列的顺序是这样的，以使它们始终分组在一起，而不是随机分布在整个工作表中. (如果不是这种情况，请说出来，我将首先包括一个排序选项)

This turned out to be simpler than I first expected. This code assumes that the column of unique id numbers is ordered such that they are always grouped together and not randomly distributed throughout the sheet. (If this is not the case please say and I will include a sort option first)

编辑 我更新了代码以首先包含排序.它还会将其复制到第二张工作表(Sheet2)上，以防万一出问题时不会丢失原始数据列表.

EDIT I updated the code to include a sort first. It also copies it onto a second sheet (Sheet2) so that you do not lose you original list of data in case something goes wrong.

编辑2 刚刚想到，如果要对大型数据集执行此操作，则将需要关闭屏幕更新以加快速度

EDIT 2 Just had a thought, If you are doing this on large data sets then you will want screen updating turned off to speed things up

Sub sumAndFormat()

Dim lastRow As Long
Dim activeRow As Long
Dim uniqueID As Long
Dim totalSum As Currency
Dim subRow As Long
Dim subTotal As Currency

Application.ScreenUpdating = False

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B12")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

lastRow = Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, "A").End(xlUp).row


totalSum = 0
subTotal = 0
subRow = 1

uniqueID = Sheets("Sheet1").Cells(1, 1).value

For i = 1 To lastRow

    totalSum = totalSum + Sheets("Sheet1").Cells(i, 2).value

    If uniqueID = Sheets("Sheet1").Cells(i, 1) Then
        subTotal = subTotal + Sheets("Sheet1").Cells(i, 2).value
        Sheets("Sheet2").Cells(subRow, 2).value = subTotal
        MsgBox (subTotal)
    Else
        uniqueID = Sheets("Sheet1").Cells(i, 1).value
        subTotal = Sheets("Sheet1").Cells(i, 2).value
        subRow = i
    End If



    Sheets("Sheet2").Cells(i, 3).value = Sheets("Sheet1").Cells(i, 1).value
    Sheets("Sheet2").Cells(i, 4).value = Sheets("Sheet1").Cells(i, 2).value

Next i

Sheets("Sheet2").Cells(1, 1).value = totalSum

Application.ScreenUpdating = True

End Sub

这篇关于VBA，需要基于另一列中的更改的有条件总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！