VBA-第 13.3 课。使用数组 (Arrays)（练习）

为了练习使用数组，我们将创建我们自己的宏版本，用于逐步演示数组的速度优势......

这是本练习的起点（您将看到数据集已减少到 1000 行）：

您可以在这里下载Excel示例文件：arrays_exercise.xls

练习目标：该过程应使用循环处理数据集中的数据，并计算每年和每个客户编号的“是”或“否”响应数量（“是”或“否”，取决于用户的选择）并输入该数量在工作表的指定单元格中。

完成以下宏，将“DS”工作表中的数据保存到数组中：

Sub actualize()
     Dim last_row As Integer
     
     '数据集的最后一行
     '...
        
     '将一组数据保存在动态数组中
     Dim array_db()
     '...
        
End Sub

下面是一个示例解决方案：

Sub actualize()
     Dim last_row As Integer
     
     '数据库最后一行
     last_row = Sheets("DS").Range("A1").End(xlDown).Row

     '将一组数据保存在动态数组中
     Dim array_db()
     ReDim array_db(last_row - 2, 2)
     
     For row_number = 2 To last_row
         array_db(row_number - 2, 0) = Sheets("DS").Range("A" & row_number)
         array_db(row_number - 2, 1) = Sheets("DS").Range("B" & row_number)
         array_db(row_number - 2, 2) = Sheets("DS").Range("C" & row_number)
     Next
End Sub

这基本上重复了我们在上一课中所做的事情......

但现在我们需要通过添加以下操作来修改宏：

下面是一个示例解决方案：

Sub actualize()
     Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
     
     '数据库最后一行
     last_row = Sheets("DS").Range("A1").End(xlDown).Row

     '搜索值（YES 或 NO）
     If Sheets("RES").OptionButton_yes.Value = True Then
         search_value = "YES"
     Else
         search_value = "NO"
     End If
     
     '回复数 YES 或 NO
     rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
     
     '将数据集保存到数组
     Dim array_db()
     ReDim array_db(rows_number - 1, 1)

     insert_row = 0
     
     For row_number = 2 To last_row
         value_yes_no = Sheets("DS").Range("C" & row_number)
         
         If value_yes_no = search_value Then
             array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
             array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
             insert_row = insert_row + 1
         End If
     Next
End Sub

用户选择的搜索是在程序开始时通过以下代码确定的：

 '搜索值（YES 或 NO）
 If Sheets("RES").OptionButton_yes.Value = True Then
     search_value = "YES"
 Else
     search_value = "NO"
 End If

我们将使用 CountIF 函数来确定“是”或“否”响应的数量：

 '回复数 YES 或 NO
 rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)

该数组已调整大小以适合“是”或“否”响应的数量，并减少为两列：

ReDim array_db(rows_number - 1, 1)

当第三列与用户的选择匹配时，该数据现在将存储在数组中：

 '将数字插入数组
 insert_row = 0

 '数据集处理
 For row_number = 2 To last_row
     '列 C 值（YES 或 NO）
     value_yes_no = Sheets("DS").Range("C" & row_number)
     
     '如果该值与用户的选择匹配，则该字符串将存储在数组中
     If value_yes_no = search_value Then
         '将值保存到 A 列
         array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
         '将值保存到 B 列
         array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
         '已保存一行=>数组中的插入数加1
         insert_row = insert_row + 1
     End If
 Next

我们的数组仅包含我们感兴趣的数据。

剩下要做的就是：

下面是一个示例解决方案：

 '回复数量“YES”/“NO”
 For no_years = 2011 To 2026
     For no_client = 1 To 30
         counter = 0
         
         For i = 0 To UBound(array_db)
             If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                 counter = counter + 1
             End If
         Next
         
         Cells(no_years - 2009, no_client + 1) = counter
     Next
 Next

解决了问题并详细解释了评论：

 '每行循环
 For no_years = 2011 To 2026
 
     '每列循环
     For no_client = 1 To 30
         '计数器复位
         counter = 0
         
         '数组处理
         For i = 0 To UBound(array_db)
             '检查表中的行是否对应于年份和客户编号
             If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                 '如果年份和客户编号匹配，则计数器加 1
                 counter = counter + 1
             End If
         Next
         
         '处理完数组后，将结果输入到对应的单元格中
         Cells(no_years - 2009, no_client + 1) = counter
     Next
 Next

最后，我们整个宏的代码：

Sub actualize()
     Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer, counter As Integer
     
     '删除内容
     Range("B2:AE17").ClearContents
     
     '数据集中的最后一行
     last_row = Sheets("DS").Range("A1").End(xlDown).Row

     '搜索值（YES 或 NO）
     If Sheets("RES").OptionButton_yes.Value = True Then
         search_value = "YES"
     Else
         search_value = "NO"
     End If
     
     '回复数 YES 或 NO
     rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
     
     '将值保存在数组中
     Dim array_db()
     ReDim array_db(rows_number - 1, 1)

     insert_row = 0
     
     For row_number = 2 To last_row
         value_yes_no = Sheets("DS").Range("C" & row_number)
         
         If value_yes_no = search_value Then
             array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
             array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
             insert_row = insert_row + 1
         End If
     Next
     
     '计算答案 YES 或 NO
     For no_years = 2011 To 2026
         For no_client = 1 To 30
             counter = 0
             
             For i = 0 To UBound(array_db)
                 If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                     counter = counter + 1
                 End If
             Next
             Cells(no_years - 2009, no_client + 1) = counter
         Next
     Next
End Sub

您可以在这里下载Excel示例文件：arrays_exercise_completed.xls