版权声明：本文为博主原创文章，未经博主同意不得转载。 https://blog.csdn.net/small_baby01/article/details/24028407

引言    

    通过上次的解说（教你高速入门（上））（教你高速入门（下））。相信一些简单的问题大家已经能够使用宏和VBA来攻克了。那假设遇到大数据时怎么办？在日常生活中我们须要处理的可不止简单的一两张表，非常可能是20。30张表甚至很多其它！假设你遇到这种问题不要着急，这里我就来给大家解说一下Excel中的大数据处理问题。

实例

    接着上次简单演示样例来说，假设如今须要处理的表单不再是一个，而是多个，比如，须要统计多个年龄段的篮球运动员其身体素养成绩，那么原先的代码就不能实现了。由于之前的sheet名已经写“死”，仅仅能是特定附表的数据。当然解决问题也非常easy，仅仅要简单的改动一下代码，使其自己主动获得当前表（所需表）的sheet名，然后把sheet名作为參数传递就可以。

OK，改动代码例如以下：

    模块代码：

Option Explicit

'激活时，<span style="font-family: KaiTi_GB2312; ">求和，</span><span style="font-family: KaiTi_GB2312; ">自己主动获取所有成绩</span>

Public Sub WorksheetActivate()

    Dim selectedCol As Integer

    Dim r As Integer

    Dim c As Integer

    Dim j As Integer

    Dim isUpdate As Boolean

    Dim FileName As String

    FileName = GetName() + "附"

    '行循环

    For r = 5 To ActiveSheet.UsedRange.Rows.Count

       '列循环

       For c = 4 To ActiveSheet.UsedRange.Columns.Count - 1 Step 2

            isUpdate = False

            selectedCol = (c - 4) \ 2 + 2 '获取 当前表附表 中相应列号

            For j = 2 To Worksheets(FileName).UsedRange.Rows.Count

                If ActiveSheet.Cells(r, c).Value <> "" And ActiveSheet.Cells(r, c).Value = Worksheets(FileName).UsedRange.Cells(j, selectedCol).Value Then

                    ActiveSheet.Cells(r, c + 1).Value = Worksheets(FileName).UsedRange.Cells(j, 1).Value

                    isUpdate = True

                End If

            Next

            '假设没有更新。值为""

            If Not isUpdate Then

                ActiveSheet.Cells(r, c + 1).Value = ""

            End If

            Cells(r, c + 1).Select

       Next

    Next

End Sub

'点击时，获取成绩

Public Sub CellsClick(ByVal Target As Range)

    Dim FileName As String

    FileName = GetName() + "附"

    '仅仅能选择（5，4）到有效表格的区域。否则跳过

    If Target.Column < 4 Or Target.Row < 5 Or Target.Column > ActiveSheet.UsedRange.Columns.Count - 1 Or Target.Row > ActiveSheet.UsedRange.Rows.Count Then

        Exit Sub

    End If

    Dim selectedCol As Integer

    Dim j As Integer

    Dim isUpdate As Boolean

    selectedCol = (Target.Column - 4) \ 2 + 2  '获取 <span style="font-family: KaiTi_GB2312; ">当前表附表</span><span style="font-family: KaiTi_GB2312; "> 中相应列号</span>

    If Target.Column Mod 2 = 0 Then

        '行循环

        For j = 2 To Worksheets(FileName).UsedRange.Rows.Count Step 1

            If ActiveSheet.Cells(Target.Row, Target.Column).Value <> "" And ActiveSheet.Cells(Target.Row, Target.Column).Value = Worksheets(FileName).UsedRange.Cells(j, selectedCol).Value Then

                ActiveSheet.Cells(Target.Row, Target.Column + 1).Value = Worksheets(FileName).UsedRange.Cells(j, 1).Value

                isUpdate = True

            End If

        Next

        If Not isUpdate Then

           ActiveSheet.Cells(Target.Row, Target.Column + 1).Value = ""

        End If

    End If

     '列循环。自己主动求和

    Dim sum As Double

     sum = 0

    For j = 5 To ActiveSheet.UsedRange.Columns.Count - 1 Step 2

        sum = sum + Val(ActiveSheet.Cells(Target.Row, j).Value)

    Next

    ActiveSheet.Cells(Target.Row, ActiveSheet.UsedRange.Columns.Count).Value = sum

End Sub

<span style="color:#ff0000;">

</span>'获取当前操作的文件名

Private Function GetName() As String

    GetName = ActiveSheet.Name

End Function

    主表代码：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = Selection.Row - 1 And Target.Column = Selection.Column Or Target.Row = Selection.Row And Target.Column = Selection.Column - 1 Then

    '改动马上获取成绩

    CellsClick Target

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    '点击时获取成绩

    CellsClick Target

End Sub

总结

    多表操作思想：

    1.多表操作时要注意分类，一般各表之间不是孤立存在的。把同类型（比方按性别分类。按年龄段分类，按年级 分类）放到一个Excel里去处理；

    2.一个Excel中多表之间操作要注意參数传递，数据获取方式和激活问题，多做測试。

    优化思想：

    一项工作反复多遍时，肯定能够优化，优化方式非常easy。提取公共部分!