Born to be proud
8/19
2017

利用 VBA 处理 Excel 数据

Visual Basic for Applications(VBA)是Visual Basic的一种宏语言,是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展Windows的应用程序功能,特别是Microsoft Office软件。

VBA 编写的宏命令可以取代手动对 Excel 的大量操作,也可以实现 Excel 未提供的函数。相比其他语言, VBA 天然的支持 Excel 大规模数据处理,对 Excel 格式数据处理更快,省去了格式字符匹配等问题。同时 VBA 可与 Excel 中自带的函数混合使用,提高了易用性。

示例一:不同文件间拷贝数据

Sub Electric()
    Dim target As Workbook
    Set target = Workbooks("a.xlsm")

    Dim source As Workbook
    Set source = Workbooks("b.xlsx")

    For i = 6 To target.Sheets("2014").Cells(Rows.Count, 1).End(xlUp).Row
        For j = 3 To target.Sheets("2014").Cells(Rows.Count, 1).End(xlUp).Row
            If target.Sheets("2014").Cells(i, 3) = source.Sheets("2014").Cells(j, 1) Then   '如果单元格内容相同
                target.Sheets("2014").Cells(i, 24) = source.Sheets("2014").Cells(j, 2)
                Exit For
            End If
        Next
    Next
End Sub

示例二:筛选出连续 5 行数据的项

Sub Statistic()

    Dim target As Workbook
    Set target = Workbooks("data.xlsm")

    '使用“高级筛选”功能将不重复公司Id数据显示在sheet4中
    target.Sheets("sheet3").Columns(2).AdvancedFilter 2, , target.Sheets("sheet8").Cells(1, 1), 1
    s1 = target.Sheets("sheet8").Cells(Rows.Count, 1).End(xlUp).Row
    '下面代码用COUNTIF函数统计重复次数
    For i = 1 To s1
        target.Sheets("sheet8").Cells(i, 2) = WorksheetFunction.CountIf(target.Sheets("sheet3").Columns(2), target.Sheets("sheet8").Cells(i, 1))
    Next


End Sub
Sub SelectByYear()

    Dim target As Workbook
    Set target = Workbooks("data.xlsm")
    m = 1

    For i = 2 To target.Sheets("sheet8").Cells(Rows.Count, 1).End(xlUp).Row
        If target.Sheets("sheet8").Cells(i, 2) > 5 Then '筛选5年以上的数据
            sstr = target.Sheets("sheet8").Cells(i, 1)

                With Worksheets(3).Range("b:b")
                    Set c = .Find(what:=sstr)
                    If Not c Is Nothing Then
                        firstAddress = c.Address
                        Do
                            r = c.Row
                            target.Sheets("sheet3").Rows(r).copy target.Sheets("sheet9").Cells(m, 1)
                            m = m + 1
                            Set c = .FindNext(c)
                        Loop While Not c Is Nothing And c.Address <> firstAddress
                    End If
                End With
        End If
    Next

End Sub