VBA：循环遍历行，如果满足条件则总结某些行

VBA: loop through rows, sum up certain rows if condition is met

提问by DEFCON123

I have been using the forums for a couple of weeks now while learning VBA. But now I am stuck and need help. So here is my problem:

在学习 VBA 的同时，我已经使用论坛几个星期了。但现在我被困住了，需要帮助。所以这是我的问题：

I have a workbook with several worksheets in it. For simplicity let's assume that I have two worksheets. One with a lot of raw data material and one which is nicely formatted and will be the receiving sheet.

我有一个工作簿，里面有几个工作表。为简单起见，我们假设我有两个工作表。一种带有大量原始数据材料，另一种格式很好，将作为接收表。

The raw data table looks like this: I have 8 columns and I need to check for two conditions in each row (columns B and E) and sum up the values in column H. Column B contains different names and column E contains a working status, e.g. ready or talking. I do have about 3.000 rows and a set of about 20 different names with different status. So overall I have, let's say 100 rows with the name "Smith" and the status "talking" in no particular order. I now need to sum up all the numbers (column H) in each of those rows where the name "Smith" and the status "talking" are put in. After that, I would like to have the name (column B), the status (column E) and the sum (column H), to be written into cells of the same workbook in a dynamic range, starting at I3:K3. I have to admit that I am totaly in the dark on this one.. I really hope that you guys can help me with some code and/or point me into the right direction. Most of all I hope that you could also explain what your code does, so I can understand it properly and learn. Thanks in advance guys!

原始数据表如下所示：我有 8 列，我需要检查每行（B 列和 E 列）中的两个条件并将 H 列中的值相加。B 列包含不同的名称，E 列包含工作状态，例如准备好或正在说话。我确实有大约 3.000 行和一组大约 20 个不同状态的不同名称。所以总的来说，我有 100 行，名称为“Smith”，状态为“talking”，没有特定的顺序。我现在需要对输入“Smith”名称和状态“talking”的每一行中的所有数字（H 列）求和。之后，我想要名称（B 列）、状态（E 列）和总和（H 列），以动态范围写入同一工作簿的单元格，从 I3:K3 开始。我不得不承认我对这个完全一无所知..我真的希望你们能帮我写一些代码和/或指出我正确的方向。最重要的是，我希望您也能解释一下您的代码是做什么的，这样我才能正确理解和学习。在此先感谢各位！

Here is my code so far:

到目前为止，这是我的代码：

Private Sub SumNumbers()

Dim tbl As Worksheet
Dim x As Integer
Dim lrow As Long
Dim lastname As String
Dim astatus As String
Dim number As Integer
Dim counter As Integer

Set wb = ThisWorkbook
Set tbl = wb.Sheets("Sheet1")
lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row
lastname = tbl.Cells("C2:C" & lrow).Text
astatus = tbl.Cells("E2:E" & lrow).Text
number = tbl.Cells("H2:H" & lrow)

For x = 2 To lrow

    If lastname = "Smith" And astatus = "Talking" Then
        counter = counter + number
    End If

Next x

End Sub

回答by Portland Runner

You're close, but you need to test each cell individually inside your loop. Something like this:

您已经接近了，但是您需要在循环中单独测试每个单元格。像这样的东西：

Sub Macro1()
    Dim tbl As Worksheet
    Dim x As Integer
    Dim lrow As Long
    Dim lastname As String
    Dim astatus As String
    Dim number As Integer
    Dim counter As Integer: counter = 3

    Set wb = ThisWorkbook
    Set tbl = wb.Sheets("Sheet1")
    lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row

    For x = 2 To lrow
        If tbl.Range("C" & x) = "Smith" And tbl.Range("E" & x) = "Talking" Then
            Range("I" & counter).Value = Range("C" & x)
            Range("J" & counter).Value = Range("E" & x)
            Range("K" & counter).Value = Range("H" & x)
            counter = counter + 1
        End If
    Next x
End Sub

---

---

Per @Tim comment you can use SumIfs to return the total of 'H' column that meet multiple criteria like this:

根据@Tim 评论，您可以使用 SumIfs 返回满足多个条件的“H”列的总数，如下所示：

=SUMIFS(H:H,C:C,"=Smith",E:E,"=Talking")

回答by DEFCON123

I found a way that does the job for me so far, but this seems pretty messed up since I have to do it for every case manually. So now I am looking for a simpler and more elegant way to do this. If you could provide me with some code or point me into the right direction, I would highly appreciate it.

到目前为止，我找到了一种可以为我完成工作的方法，但这似乎很混乱，因为我必须为每个案例手动执行此操作。所以现在我正在寻找一种更简单、更优雅的方法来做到这一点。如果您能为我提供一些代码或为我指明正确的方向，我将不胜感激。

Here is my code so far:

到目前为止，这是我的代码：

For Each cell In Range("H2:H" & lrow)
   cell.Offset(0, 1).Value = cell.Value + cell.Offset(-1, 1).Value
      If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Talking" Then
      cell.Offset(0, 2).Value = cell.Value
      End If

      If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Ready" Then
         cell.Offset(0, 3).Value = cell.Value
      End If

      If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Not Ready" Then
      cell.Offset(0, 4).Value = cell.Value
      End If
Next

As mentioned before, it is a workaround which I would like have to be simplified. It looks for the condition "Smith" and the status "Talking", "Ready" and "Not Ready" and puts the value of the H Column into the respective cells of the columns to it's right to be summed in the later process. So, is there any way to make that more elegant?

如前所述，这是一种我希望必须简化的解决方法。它查找条件“Smith”和状态“Talking”、“Ready”和“Not Ready”，并将 H 列的值放入列的相应单元格中，以便在后面的过程中求和。那么，有没有办法让它更优雅呢？

回答by DEFCON123

Sure thing Portland Runner! My logic is simple. I've got list of recorded activities for each name with certain statusses. e.g. "Smith" (column B) and "Talking" (column C) and a number (column H). And I need to sum up the numbers in column H for every row where the conditions name and status are equal. Let's say I have 10k rows. 2k of them have the name "Smith" in it. But only 500 of them have the name "Smith" and the status "Talking". So I need to figure out a simpler way to sum up those numbers. There are also rows where the name is "Smith" but the status is "Ready". The difficulty here is, that those lines are basically in a random order.

当然是波特兰赛跑者！我的逻辑很简单。我有每个名称的记录活动列表，具有某些状态。例如“Smith”（B 列）和“Talking”（C 列）和一个数字（H 列）。我需要为条件名称和状态相等的每一行总结 H 列中的数字。假设我有 10k 行。其中 2k 个里面有“Smith”这个名字。但其中只有 500 个拥有“史密斯”的名字和“会说话”的状态。所以我需要找出一种更简单的方法来总结这些数字。也有名称为“Smith”但状态为“Ready”的行。这里的困难在于，这些行基本上是随机顺序的。

So the table would look like this:

所以表格看起来像这样：

Row    Name     Status      Number     
2      Smith    Talking     105
3      Smith    Talking     67
4      Smith    Ready       75
5      Smith    Talking     94
6      Jones    Ready       89
7      Jones    Talking     224
8      Jones    Not Ready   75
9      Jones    Talking     99

So in this case row number 1 would be blocked for the headline and the data would start in row number 2. For "Smith" and "Talking" the code would need to sum the numbers of rows 2,3 and 5 for the condition "Smith" and "Talking". Rows 4 for "Smith" and "Ready". Rows 7 and 9 for "Jones" and "Talking" and so on.

因此，在这种情况下，第 1 行将被阻止作为标题，数据将从第 2 行开始。对于“史密斯”和“谈话”，代码需要将第 2,3 行和第 5 行的行数相加，以符合条件“史密斯”和“谈话”。“Smith”和“Ready”的第 4 行。第 7 行和第 9 行是“Jones”和“Talking”等等。

What I can do, is create a new worksheet and put a list of all names in column A and a list of all statusses into column B and reference to it. That would save me the trouble of using the exact names and statusses in the code, but wouldn't solve my problem.

我能做的是创建一个新的工作表，并将 A 列中的所有名称列表和所有状态的列表放入 B 列并对其进行引用。这将省去我在代码中使用确切名称和状态的麻烦，但不会解决我的问题。

回答by Den

A. Get all unique Name and place in a new column: this will be your array used to search each row.

A. 获取所有唯一的名称并放置在新列中：这将是您用于搜索每一行的数组。

B. Set up another array for Status since you know what the status could be.

B. 为 Status 设置另一个数组，因为您知道状态可能是什么。

C. Set up outer loop for Name Array
For each item in array

C. 为Name Array 设置外循环对于数组中的
每一项

D. Nested loop look at each row and search on each status

D. 嵌套循环查看每一行并搜索每个状态

C. When status matches then another nested loop to add time and place in a new column called total time.

C. 当状态匹配时，另一个嵌套循环在名为总时间的新列中添加时间和地点。