Excel 取两页中相同的数据项算法二 时间复杂度O(n)

      将Sheet3 和Sheet5 中的数据项同时按关键列排序, 一般默认按A-Z升序排列.  (关于数组的排序可以参考高效的QuickSort排序算法). 在此不将Sheet3 和Sheet5 的排序的时间计算在内.

      下面先给出按指定名称建立新Sheet的函数, 若不存在指定名称的Sheet, 则新建;  若Sheet已存在且用户同意删除, 则删除后新建一页Sheet, 若用户不同意删除, 则对新建的Sheet命名为指定名称后加Sheets.Count.

       Public Function CreatNewSheet(name As String) exist = IsSheetExist(name) Set newSh = Sheets.Add If exist = True Then newSh.name = name & Sheets.Count Else newSh.name = name End If CreatNewSheet= newSh.name End Function Public Function IsSheetExist(name As String) Dim exist As Boolean exist = False For i = 1 To Sheets.Count If Sheets(i).name = name Then Dim Msg, Style, Title, Help, Ctxt, Response Msg = name & " is exist. Do you want to delete it?" Style = vbYesNo + vbDefaultButton2 Title = "Tips" Ctxt = 1000 Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Application.DisplayAlerts = False Sheets(name).Delete Application.DisplayAlerts = True exist = False Else exist = True End If Exit For End If Next i IsSheetExist = exist End Function 

    取数据项交集的VBA 代码:

     Public Sub MixedTest() Dim sheetName As String 'Creaate a new sheet with special name in order to store the mixed data. Dim headRows As Integer 'the rows that start of the sheet of the data items Dim endRows As Integer 'the rows that end of the sheet, currently use the SheetA's end row number Dim coloumIndex As Integer 'the key coloum of the Sheet to sort and compare Dim rowsCountA As Integer 'the data's rows count of SheetA Dim sheetNameA As String 'the name of the sheet will use to compare Dim rowsCountB As Integer 'the data's rows count of SheetB Dim sheetNameB As String 'the name of the sheet will use to compare sheetName = CreatNewSheet("mixed") headRows = 2 endRows = 54 coloumIndex = 3 rowsCountA = 53 sheetNameA = "Sheet3" rowsCountB = 251 sheetNameB = "Sheet5" GetMixedData sheetName, headRows, endRows, coloumIndex, rowsCountA, sheetNameA, rowsCountB, sheetNameB End Sub Public Function GetMixedData(sheetName As String, headRows As Integer, endRows As Integer, coloumIndex As Integer, rowsCountA As Integer, sheetNameA As String, rowsCountB As Integer, sheetNameB As String) Dim count As Integer 'count the mixed data item Dim keyword As String 'the keyword to compare count = 1 'copy the head For i = 1 To headRows Sheets(sheetNameA).Rows(i).Copy (Sheets(sheetName).Cells(count, 1)) count = count + 1 Next i 'copy the mixed body rowIndexB = headRows + 1 For rowIndexA = headRows + 1 To rowsCountA If (rowIndexB > rowsCountB) Then Exit For End If keyword = Sheets(sheetNameA).Cells(rowIndexA, coloumIndex) Do While (rowIndexB <= rowsCountB And keyword > Sheets(sheetNameB).Cells(rowIndexB, coloumIndex)) rowIndexB = rowIndexB + 1 Loop If (rowIndexB > rowsCountB) Then Exit For End If If (keyword = Sheets(sheetNameB).Cells(rowIndexB, coloumIndex)) Then Sheets(sheetNameA).Rows(rowIndexA).Copy (Sheets(sheetName).Cells(count, 1)) count = count + 1 rowIndexB = rowIndexB + 1 End If Next rowIndexA 'copy the end For i = rowsCountA + 1 To endRows Sheets(sheetNameA).Rows(i).Copy (Sheets(sheetName).Cells(count, 1)) count = count + 1 Next i End Function 

该算法的时间复杂度为 O(m+n)   = O(n),  缺点是调用GetMixedData函数需要传递的参数过于多.

 

 

Author:David

    Mail:xiang.okay@gmail.com