☰

我需要將 2 列 excel 中不匹配的數字提取到新工作表中

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
我需要將 2 列 excel 中不匹配的數字提取到新工作表中

我每列有 2 個大列（139456 筆記錄）。我需要將不匹配的記錄提取到新的工作表“呼叫表”中。隨著時間的推移，我將添加到兩個清單的底部。我希望將不匹配的記錄添加到“調用表”的底部。我已經快要完成了，但 Excel 似乎超載了。我不斷收到無回應錯誤。

=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")

因記錄數量而導致過載

答案1

我臨時操縱了我的一些舊程式碼，以將所有資料從“第一列”中取出不匹配「第 2 列」中的任何資料並將其列印在另一張紙中。
也許它會有一些用處，你可能需要稍微調整一下。

它從列表所在位置以及新列表所在位置的變量開始 - 可能想要執行這些變量，以隨列一起增長。

Sub sort()
Dim list1 As Range, list2 As Range, c As Range, outSht As Worksheet, outCol As String, Lrow As Long
'---Options---
Set list1 = Range("A2:A1999")   'Range of first column
Set list2 = Range("B2:B399")    'Range of second column
Set outSht = Sheets("Sheet2")   'Output Sheet (Create one first)
outCol = "A"                    'Output Column

Application.ScreenUpdating = False
oCN = Columns(outCol).Column
For Each c In list1
    If list2.Find(c.Value) Is Nothing Then
        lRow = outSht.Range(outCol & ActiveSheet.Rows.Count).End(xlUp).Row
        outSht.Cells(lRow + 1, oCN).Value = c.Value
    End If
Next c
Application.ScreenUpdating = True
End Sub

雖然速度不是很快，但至少不會出現錯誤。我嘗試在第 1 列中輸入 45000 個條目，在第 2 列中輸入 400 個條目，這花了我大約6秒產生列表。

警告 嘗試用 220k 行檢查 100k 行。 15 分鐘後，它仍在運行。所以是的，如果您想使用它，我希望您只需運行一次。

您可以使其自動化，但您可能需要更快的方法，或只查看最後一個附加價值的方法。
另請注意，如果運行兩次，這只會將所有內容添加兩次。它不會先清除清單。

編輯2

一種更快的方法是使用宏，將其設為表，整理相關數據，複製數據，然後刪除表。它以幾秒的速度管理了所有 220,000 個條目。我只需要弄清楚如何複製與清單不匹配的內容，而不是相反。

編輯3

還沒弄清楚自動過濾器的東西。但是，如果您還沒有使用其他程式碼，但仍然想使用，請改用以下程式碼：

Sub ArrayIt()
Dim aArray As Variant, bArray As Variant
aArray = [transpose(A2:A139456)]
bArray = [transpose(B2:B139456)]
Set outSht = Sheets("Sheet2")   'Output Sheet (Create one first)
outCol = "A"                    'Output Column
Application.ScreenUpdating = False
oCN = Columns(outCol).Column
For Each c In aArray

    If IsError(Application.Match(c, bArray, 0)) Then
        Lrow = outSht.Range(outCol & outSht.Rows.Count).End(xlUp).Row
        outSht.Cells(Lrow + 1, oCN).Value = c   
    End If
Next c
Application.ScreenUpdating = True
End Sub

基本上是一樣的，但是首先將資料轉換為數組，然後使用它們來遍歷它。它仍然很慢，但至少快了 20 倍，甚至更多。在不到 2 分鐘的時間內管理了 220k x 220k 條目。

編輯4

好的，所以我對自動過濾器做了一個解決方法。
問題：
只可以展示我的過濾器中的值，而不是*隱藏它們。
只能使用顯示的值。
無法刪除包含或不包含資料的行（太慢）。

解決方案：
新程式碼的作用如下：
首先，它將我們想要使用的範圍（列“A”）處理為兩個新列，只是為了確保它不會弄亂原始列表。
然後它將第一個副本放入表格中，並使用我們的第二個範圍 - 列“B”對其進行過濾。
然後，它清除表中每個可見單元格的內容並刪除該表。
現在第一個副本只有我們想要的數據，還有一堆漏洞，它清除了所有不需要的數據。所以現在我們將該範圍作為我們的新過濾器。
現在，第二個副本成為一個表，並使用新的篩選器進行排序。
然後可見的單元格（現在是我們想要的資料）被複製到另一列。

目前，代碼在同一張紙上完成所有操作。並且它佔據了M列Q。因此，在測試它時要小心，因為如果那裡有其他數據，而且如果工作表中存在某種排序和隱藏行，它也會弄亂事情。

肯定有更好的方法來編寫實際程式碼，但這是我有時間的最好方法。它設法運行當前設定（225 000 行數據，100 000 個過濾器參數）12秒。

Sub aaTablefiltering()
Dim LO As ListObject, tName As String, rOne As Range, rTwo As Range, rThree As Range, rFour As Range, fArr As Variant

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Set rOne = Range("A2:A225000")
Set rTwo = Range("B2:B100000")
Set rThree = Range("M2:M225001")
Set fFour = Range("O2:O225001")
fArr = [transpose(B2:B100000)]
tName = "DTable"

rOne.Copy Destination:=Range("M2")
rOne.Copy Destination:=Range("O2")

Set LO = ActiveSheet.ListObjects.Add(xlSrcRange, rThree, , xlNo)
LO.Name = tName
ActiveSheet.ListObjects("DTable").Range.AutoFilter Field:=1, Criteria1:=fArr, Operator:=xlFilterValues
ActiveSheet.ListObjects("DTable").Range.SpecialCells(xlCellTypeVisible).ClearContents
ActiveSheet.ListObjects("DTable").Unlist
fArr = [transpose(M2:M225001)]

Set LO = ActiveSheet.ListObjects.Add(xlSrcRange, fFour, , xlNo)
LO.Name = tName
ActiveSheet.ListObjects("DTable").Range.AutoFilter Field:=1, Criteria1:=fArr, Operator:=xlFilterValues
ActiveSheet.ListObjects("DTable").Range.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=ActiveSheet.Range("Q1")
ActiveSheet.ListObjects("DTable").Unlist
Range("M:Q").ClearFormats
Range("M:O").ClearContents

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

相關內容