VBA重复计算小时的总和直到确定的标准

[英]VBA Repeatd summing up of hours until certain criterion

本文翻译自 Izzy Schneider 查看原文 2016/12/08 48 excel/ macros/ vba/ excel-vba

I have a table with 6 columns. [ID, Status, Start Time, End Time, Hours, Sum UP]

我有一张六列的表格。[ID, Status, Start Time, End Time, Hours, Sum UP]

I have counted a total amount hours between Start Time and End Time. Now I have to count a Sum Up of this hours. The problem is, that the sum up must be counted in a special range, starting from status "Shipped" until status "Checked" appears for only the first time. Then repeat starting again with "Shipped" until "Checked" for the next ID.

我已经计算了从开始时间到结束时间的总小时数。现在我要数一下这几个小时的总数。问题是，总和必须在一个特殊的范围内计算，从状态“Shipped”开始，直到状态“Checked”只出现在第一次。然后用“Shipped”再次开始，直到“检查”下一个ID。

Sub SUMUP()
   Dim LastRow As Long
   LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

   For i = 2 To LastRow
      Cells(i, 6).Value = WorksheetFunction.SumIf(Range("A2:A" & LastRow), Range("A" & i), Range("E2:E" & LastRow))

  Next

End Sub

I have this code to sum up a total amount of hours. How can I write a VBA code for my case?

我有这个代码来总结总共的小时数。如何为我的案例编写VBA代码?

Thank you in advance for your help.

事先谢谢你的帮助。

4 个解决方案

#1

Try this one:

试试这个:

Sub SUMUP()
    Dim LastRow As Long
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow
        If Range("B" & i).Value = "Shipped" Then
            For j = i To LastRow
                If Range("B" & j).Value <> "Checked" Then
                    Sum = Sum + Range("E" & j).Value
                Else
                    Range("F" & j).Value = Sum
                    Exit For
                End If
            Next j
            i = j
            Sum = 0
        End If
    Next i

End Sub

#2

or an array

或数组

=SUM(INDIRECT(("f"&MIN(IF((($A$1:$A$10="ID")*($B$1:$B$10="shipped")),ROW($A$1:$A$10)))&":"&"f"&MIN(IF((($A$1:$A$10=1)*($B$1:$B$10="checked")),ROW($A$1:$A$10))))))

#3

you could use this formula, if the order Status is sorted in the correct order:

如果订单状态按照正确的顺序排序，您可以使用这个公式:

=SUMIF(INDIRECT("A"&MATCH(A2,A:A,0)&":A"&ROW(A2)),A2,INDIRECT("E"&MATCH(A2,A:A,0)&":E"&ROW(A2)))

This would be an vba solution

这将是一个vba解决方案

Sub SUMUP()
    Dim LastRow As Long
    Dim hours As Integer
    Dim ID As Integer
    Dim checked As Boolean

    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    ID = Cells(2, 1).Value
    For i = 2 To LastRow
         If ID = Cells(i, 1).Value Then
             If Cells(i, 2).Value <> "checked" And checked = False Then
                 hours = Cells(i, 6).Value + hours
                 Cells(i, 10).Value = hours
             ElseIf Cells(i, 2).Value = "checked" And checked = False Then
                 checked = True
                 hours = Cells(i, 6).Value + hours
                 Cells(i, 10).Value = hours
             End If
         Else

             ID = Cells(i, 1).Value
             checked = False
             hours = 0
         End If
    Next

End Sub

#4

Just for completeness, I believe this non-array formula should work:-

为了完整起见，我认为这个非数组公式应该是:-

=IF(AND(B2="checked",COUNTIFS(A$1:A2,A2,B$1:B2,"checked")=1),C2-INDEX(C$2:C$10,MATCH(A2&"shipped",INDEX(A$2:A$12&B$2:B$12,0),0)),"")

It just subtracts shipped datetime from first checked datetime.

它只是从第一次检查的datetime中减去发送的datetime。

Multiply by 24 to get the time in hours.

乘以24，以小时为单位。

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2016/12/08/9db3143560272cd83b6e490796500709.html。

猜您在找

如何计算数组中元素的总和，直到Java中的某个索引 - How to calculate the sum of elements in an array up to a certain index in Java Java - 计算所有偶数的总和，直到某个数字 - Java - Calculating the sum of all even numbers up to a certain number 添加数组数直到确定值 - Add numbers of array until certain value Excel总和不工作几个小时 - Excel sum not working for hours VBA直到循环或“”状态。 - VBA Do Until Loop with Or “” condition

赞助商链接