欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Pandas与VBA筛选数据的比较

程序员文章站 2022-07-04 13:39:22
...

Pandas与VBA筛选数据的比较

Author:Collin_PXY

需求:

Pandas与VBA筛选数据的比较将B列里值为Completed 和 Pending的A,B,D三列数据筛选出来,新建一个名为 Filited_data的worksheet来存放筛选数据。

Python解决:

from pandas import DataFrame
import pandas as pd
import openpyxl

# 处理数据
df=pd.read_excel(file_path)
df=df.loc[df['status']!="Cancelled",['CNUM','status',"Tax"]]
df=df.reset_index(drop=True)
df['Tax']=df['Tax'].astype("float")

# 保存数据
writer = pd.ExcelWriter(file_path,engine='openpyxl')
writer.book = openpyxl.load_workbook(writer.path)  # 此句如果缺少,后面语句执行时会覆盖文件里原有内容。
df.to_excel(excel_writer=writer,sheet_name="Filited_data",index=False)    # 会新建一个新建 Filited_data,如果已经存在,则加数字后缀。
writer.save()
writer.close() 

VBA解决

Sub FilterData()
On Error GoTo errorhandling
    Dim wb_data As Workbook
    Dim ws_data As Worksheet
    Dim ws_filted As Worksheet
    Dim file_path As String
    Dim usedrows As Integer

    Dim arr_filter As Variant
    arr_filter = Array("status", "Completed", "Pending")

    Dim arr_filter As Variant
    arr_filter = Array("status", "Completed", "Pending")
    
    usedrows = getLastValidRow(ws_data, "B")
    ws_data.Range("$A$1:$D$" & usedrows).AutoFilter Field:=2, Criteria1:=arr_filter,   Operator:=xlFilterValues

    'Copy column A,B,D to new sheet:
    'ws_data.Range("A1:D" & usedrows).SpecialCells(xlCellTypeVisible).Copy ws_filted.Range("A1")
    'Copy column A,B,D to new sheet:
    Union(ws_data.Range("A1:B" & usedrows), ws_data.Range("D1:D" & usedrows)).Copy ws_filted.Range("A1")
    ws_filted.Cells.WrapText = False
    ws_filted.Columns("A:AF").AutoFit

    checkAndCloseWorkbook file_path, True
Exit Sub
errorhandling:
    checkAndCloseWorkbook file_path, False
End Sub 

'辅助函数:
'Get last row of Column N in a Worksheet
Function getLastValidRow(in_ws As Worksheet, in_col As String)
    getLastValidRow = in_ws.Cells(in_ws.Rows.count, in_col).End(xlUp).Row
End Function

Function checkAndAttachWorkbook(in_wb_path As String) As Workbook
    Dim wb As Workbook
    Dim mywb As String
    mywb = in_wb_path
    For Each wb In Workbooks
        If LCase(wb.FullName) = LCase(mywb) Then
            Set checkAndAttachWorkbook = wb
            Exit Function
        End If
    Next
    Set wb = Workbooks.Open(in_wb_path, UpdateLinks:=0)
    Set checkAndAttachWorkbook = wb
End Function    

Function checkAndCloseWorkbook(in_wb_path As String, in_saved As Boolean)
    Dim wb As Workbook
    Dim mywb As String
    mywb = in_wb_path
    For Each wb In Workbooks
        If LCase(wb.FullName) = LCase(mywb) Then
            wb.Close Savechanges:=in_saved
            Exit Function
        End If
    Next
End Function

结果

Pandas与VBA筛选数据的比较