如何根据B列和C列中的值删除Excel中的行

I have 15,000 rows in the below spreadsheet and I need to keep the rows:

我在下面的电子表格中有15,000行,我需要保留行:

where status > 0 and lastvalidationattemptdistance < 50

其中status> 0且lastvalidationattemptdistance <50

3 个解决方案

#1

Simple data manipulation in pandas. It's really simple and useful, you can learn it in 10 min.

熊猫中的简单数据操作。它非常简单实用,你可以在10分钟内学会它。

import pandas as pd

df = pd.read_excel('excel.xlsx', 'sheet_name', index_col=None, na_values=['NA'])

df = df.loc[df['status'] > 0]
df = df.loc[df['lastValidationAttemptDistance'] < 50]

writer = pd.ExcelWriter('new_execel.xlsx')
df.to_excel(writer, 'sheet_name', index=False)
writer.save()

#2

You can use VBA in excel to delete what you need then find a UserId.

您可以在Excel中使用VBA删除所需内容然后找到UserId。

Sub Delete()
'
' Delete and Find Macro
'


    Dim aRows As Integer, LVAD As Integer, Stat As Integer, UserId As Integer, UIDCount As Integer
    Dim Rng As Range, Rng2 As Range

    LVAD = 50 'Min value to keep
    Stat = 0 'Min value to keep
    UIDCount = 0 'Initial count number
    UserId = 3526 'Exact number of userId

    With ActiveSheet
    aRows = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For i = 1 To aRows
        If Range("B" & i).Value <= 0 Then
            If Range("C" & i).Value > 50 Then
                If Rng Is Nothing Then
                    Set Rng = Range("A" & i & ":C" & i)
                Else
                    Set Rng2 = Range("A" & i & ":C" & i)
                    Set Rng = Application.Union(Rng, Rng2)
                End If
            End If
        End If
    Next

    For i = 1 To aRows
        If Range("A" & i).Value = UserId Then
            UIDCount = UIDCount + 1
        End If
    Next

    If Not Rng Is Nothing Then
        Rng.Select
        Selection.Delete Shift:=xlUp
    End If
    MsgBox "UserId: " & UserId & " was found " & UIDCount & " times."
End Sub

To count the userId for each user individually you could count all unique id's then do a loop iteration for each one to count the occurrences, then maybe set those values into columns.

要单独计算每个用户的userId,您可以计算所有唯一ID,然后对每个用户进行循环迭代以计算出现次数,然后可以将这些值设置为列。

#3

Since you use Excel for Windows, consider an SQL solution by connecting to the Jet/ACE SQL Engine (Windows .dll files usually installed on most PCs and the very engine of MS Access). You can use your exact criteria in a WHERE clause. No For looping or nested If logic or formula is needed.

由于您使用Excel for Windows,请考虑通过连接到Jet / ACE SQL引擎的SQL解决方案(通常安装在大多数PC上的Windows .dll文件和MS Access的引擎)。您可以在WHERE子句中使用您的确切条件。否用于循环或嵌套如果需要逻辑或公式。

Below assumes data resides in a sheet named DATA and an empty worksheet named RESULTS exists in workbook which will hold the output of the SQL query including headers. Two connection types are included namely ODBC Driver and OLEDB Provider. Simply change path to Excel data file.

下面假设数据驻留在名为DATA的工作表中,工作簿中存在一个名为RESULTS的空工作表,它将保存SQL查询的输出,包括头文件。包括两种连接类型,即ODBC驱动程序和OLEDB提供程序。只需更改Excel数据文件的路径即可。

Public Sub RunSQL()
    Dim conn As Object, rst As Object
    Dim strConnection As String, strSQL As String
    Dim i As Integer

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' DRIVER AND PROVIDER CONNECTION TYPES
'    strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
'                      & "DBQ=C:\Path\To\Workbook.xlsm;"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='C:\Path\To\Workbook.xlsm';" _
                       & "Extended Properties=""Excel 12.0;HDR=YES;"";"

    strSQL = " SELECT * FROM [DATA$]" _
                & " WHERE [status] > 0 AND [lastvalidationattemptdistance] < 50;"

    ' OPEN DB CONNECTION
    conn.Open strConnection
    rst.Open strSQL, conn

    ' COLUMN HEADERS
    For i = 1 To rst.Fields.Count
        Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
    Next i    

    ' DATA ROWS
    Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

    ' CLOSE OBJECTS AND FREE RESOURCES
    rst.Close: conn.Close
    Set rst = Nothing: Set conn = Nothing
End Sub

#1