Mừng Sinh Nhật GPE 11: sáng 25/6 Hà Nội; 1/7 Huế/Đà Nẵng; Trưa 2/7 TPHCM

So sánh trùng và không trùng trên 2 cột.

Thảo luận trong 'Cấu trúc dữ liệu Excel và việc Xử lý dữ liệu' bắt đầu bởi viendo, 18 Tháng ba 2008.

  1. viendo

    viendo Tầm sư học Excel

    Tôi làm file có các cột như sau :
    1. Cột A : Dữ liệu so sánh 1
    2. Cột B : Dữ liệu so sánh 2
    3. Cột C : Lọc dữ liệu có trong cột A mà không có trong cột B
    4. Cột D : Lọc dữ liệu có trong cột B mà không có trong cột A
    5. Cột E : Lọc dữ liệu trùng cột A và B

      Sau khi thử dữ liệu 300 dòng thì nó đã "lết bánh" rồi, trong khi công việc đang cần so sánh số liệu khoảng 50.000 dòng.
      Nhờ các Anh Chị xem hướng dẫn sửa Name và công thức dùm, làm bằng BVA cũng tốt, miễn sao nó chạy lẹ lẹ 1 tí. Thanks
     

    Các file đính kèm:

  2. SA_DQ

    SA_DQ Thành viên danh dự

    Bạn Thử Với 2 Cái Ni Xem Tốc Độ Ra Răng?

    PHP:
    Option Explicit
     Dim lRow 
    As LongjZ As Long
     Dim lRow0 
    As LongjW As Long
      
    Sub FilterAll
    ()
     
    Dim SChu As String
     Dim XH 
    As String
     
     XH 
    Chr(13) & Chr(10)
     
    lRow Range("A65432").End(xlUp).Row
     lRow0 
    Range("B65432").End(xlUp).Row
     SChu 
    "A: Co O Cot 'A', Khong Co O Cot 'B'" XH
     SChu 
    SChu "B: Co O Cot 'B', Khong Co O Cot 'A'"
     
    SChu SChu XH "C: Co O Ca 2 Cot"
     
    SChu InputBox(SChu"GPE - SaDQ")
     
    SChu UCase$(Left(SChu1))
     
    Application.ScreenUpdating False
     
    If SChu "A" Or SChu "B" Then
        TongHop SChu
     
    ElseIf SChu "C" Then
        Dim tRow 
    As Long
        
    If lRow lRow0 Then tRow lRow Else tRow lRow0
        Range
    ("E3:C" tRow).ClearContents
        
    For jZ 3 To lRow
            
    For jW 3 To lRow0
                
    If Cells(jZ1) = Cells(jW2Then _
                    Range
    ("E" Range("E65432").End(xlUp).Row 1) = Cells(jZ1)
            
    Next jW
        Next jZ
     
    Else
        
    XH "BAN CAN CHON 1 TRONG 3 FUONG AN!" XH
        MsgBox XH 
    "           BYE!", , "GPE - SaDQ"
     
    End If
    End Sub 
    PHP:
    Sub TongHop(MainCol As String)
     
    Dim Cot1 As LongCot2 As Long
     Dim StrC1 
    As StringStrC2 As String
     
     Select 
    Case UCase$(MainCol)
     Case 
    "A"
        
    Cot1 lRow:        Cot2 lRow0
        StrC1 
    "C":        StrC2 "C65432"
        
    Range("C3:C" lRow).ClearContents
     
    Case "B"
        
    Cot1 lRow0:        Cot2 lRow
        StrC1 
    "D":        StrC2 "D65432"
        
    Range("D3:d" lRow0).ClearContents
     End Select
     
    For jZ 3 To Cot1
        
    For jW 3 To Cot2
            
    If Cells(jZ1) = Cells(jW2Then _
                
    Exit For
        
    Next jW
        
    If jW Cot2 Then _
            Range
    (StrC1 Range(StrC2).End(xlUp).Row 1) = Cells(jZ1)
     
    Next jZ
    End Sub 
     
  3. feelingyes

    feelingyes Thành viên tiêu biểu

    bạn có thê dùng công thức mảng
    ={if(Or(phần tử cột A = cột B),"trùng","ko trùng")}
    Bạn làm tương tự cho cột B
    Dùng Filter, lọc và paste sang một vùng mới.
    Cái này tôi đã dùng rồi (Công ty tôi có danh sách gốc gần 200 người, danh sách tham gia cứu thương lần 1, .........tạo danh sách học cho những người chưa tham gia lần 1 sẽ tham gia lần 2 tôi cũng dùng cách này.

    Bạn có thể tham khảo ở bài viết, trong đó có những cách của AnhTuan 1006 cũng rất hay mà chưa cần phải dùng đến VBA

    http://www.giaiphapexcel.com/forum/showthread.php?t=8170
     
    Lần chỉnh sửa cuối: 19 Tháng ba 2008
  4. viendo

    viendo Tầm sư học Excel

    Sư phụ SA_DQ ơi tôi làm chưa được, phải nhờ các sư phụ chỉ tiếp và đừng la nhé vì chưa đủ hiểu cái code này và làm sao cho nó chạy, đang bị vướng thế này đây :
    1. Chép thử code 1 và code 2 lần lượt vào vùng Immediate để chạy thử nó báo lỗi là Invalid in Immediate pane.
    2. Chép code 1 lần lượt vào Module1, ThisworkkbbookkSheet1. Khi nhấn chạy nó đều báo lỗi là Sub or Function not defined. Ngoài ra nó tô vàng dòng Sub FillterAll() và tô đen Tonghop ở dòng TongHop Schu.
    3. Chép code 2 lần lượt vào Module1, ThisworkkbbookkSheet1. Khi nhấn chạy nó hiện bảng Macro mà chẳng có cái Macro nào để chọn.

      Phiền các sư phụ chỉ giáo tiếp dùm. Thanks
     
  5. SA_DQ

    SA_DQ Thành viên danh dự

    Bạn nhấn tổ hợp fím {ALT} +{F11} để mở CS VBE
    Và chép cả 2 macro vô module 1
    Nói thêm: Macro TongHop được gọi bỡi macro còn lại & phải được nhận 1 đối số (biến) thích hợp 'A'/'B')
    Như vậy bạn cần chạy cái macro còn lại kia (tên nó là FilterAll)
    Khi đó 1 hộp thoại sẽ hỏi bạn cần chép vố cột nào tương ứng 'C', 'D' hay 'E'
    Nếu bạn nạp biến 'A' hay 'B' thì macro này sẽ gọi macro còn lại (TongHop) truyền cho nó đối số tương ứng để nó chạy ra kết quả (KQ sẽ thể hiện trên cột 'C' hay 'D' tương ứng)
    Nếu bạn nhập phương án 'C' thì macro FilterAll sẽ tiếp tục cho đến khi xong chuyện lọc ~ giá trị trùng trong 2 cột 'A' & 'B' ghi => 'E'
    Bổ sung:
    Nếu bạn muốn trường hợp 'C' nhanh hơn 1 tẹo nữa, thì trong macro FilterAll, tại dòng lệnh
    Mã:
    If Cells(jZ, 1) = Cells(jW, 2) Then _ 
                    Range("E" & Range("E65432").End(xlUp).Row + 1) = Cells(jZ, 1) 
    
    Nên sửa lại là (Chú í: tôi đã bỏ dấu gạch dưới dùng nối 2 dòng lệnh)
    PHP:
        If Cells(jZ1) = Cells(jW2Then 
                    Range
    ("E" Range("E65432").End(xlUp).Row 1) = Cells(jZ1
                    Exit For
        
    End If
     
    Lần chỉnh sửa cuối: 19 Tháng ba 2008
  6. viendo

    viendo Tầm sư học Excel

    Cám ơn bạn nhiều, tôi đã tham khảo cách làm trên rồi, file tôi làm cũng đa phần là học từ các file của Anhtuan, trường hợp hiện tại của tôi là phải xử lý số liệu rất lớn và phải làm khoảng 4 lần (mỗi lần làm 1 quí khoảng 40 đến 50 ngàn dòng) rồi mới tổng hợp nên nó chạy không nổi luôn.
    Ý tôi làm file này để đối chiếu sai sót của số lượng kiểm kê thực tế và số liệu trong chương trình xuất ra có chênh lệch. Một mặt phiền các sư phụ giúp file xử lý, một mặt tôi đang đề nghị chỉ kiểm tra trong từng tháng thì số liệu còn khoảng mười mấy ngàn dòng thôi.

    To Sư phụ SA_DQ : để làm theo sư phụ chỉ, có gì sẽ phiền tiếp nhé, do công lực yếu kém quá nên tiếp thu cũng kém luôn hichic..
     
  7. viendo

    viendo Tầm sư học Excel

    Đã làm theo hướng dẫn của Sư phụ và chạy rất ok với dữ liệu <1.000 dòng. Cám ơn rất nhiều.
    Cho chạy thử Cột A 10.000 dòng và cột B 5.000 dòng nó chạy gần 20' Sư phụ ơi. (với cấu hình máy sau Processor: Intel(R) Pentium(R) 4 CPU 1.50GHz. Memory: 512MB RAM)
    Có cách nào để cải thiện tiếp về tốc độ xử lý phiền các Sư phụ chỉ giáo tiếp. Thanks
     
  8. SA_DQ

    SA_DQ Thành viên danh dự

    1*/ Hãy cho biết t1, t2 & t3 tương ứng là bao nhiêu; Để còn định hướng cải thiện í mà!

    2*/ Dữ liệu của bạn có thể cho phép sắp xếp hay không?
    Nếu trong trường hợp được phép, thì thử trước khi chạy macro, cho xếp 2 cột này lại 1 cách riêng rẽ xem tình hình có cải thiện hơn không?
    [​IMG]
     
  9. viendo

    viendo Tầm sư học Excel

    1. Dữ liệu hàng tháng dao động từ 10.000 đến cao nhất là 15.000 dòng.
    2. Dữ liệu này có thể sắp xếp, sort đều được vì mục đích là tìm ra các mã bị chênh lệch giữa 2 cột mà. Dữ liệu so sánh 2 cột thế này đây :
      .....
      SJL436MDFQ0030
      SJL436MDFQ0025
      HUL773ZABJ0006
      HUL773ZABJ0010
      HUL773ZABJ0004
      ADXN001EABB0019
      ADXN001EABB0020
      AEJL064ZAEJ0006
      AEJL064ZAEJ0007
      .....
    Hy vọng Sư phụ tìm ra giải pháp giúp. Thanks
     
  10. SA_DQ

    SA_DQ Thành viên danh dự

    Chúng ta thử từng phần việc, vậy!

    Bạn vô CS VBE như trên đã nêu; Vô menu Insert & thêm 1 module nữa (tạm là module2) Và chép 2 macro này & chạy thử, so sánh với macro FilterAll -> TongHop (A) & báo kết quả nha!
    Chú í: Mình mới tách & thử với trường hợp hình thành KQ trên cột 'C' thôi nha! Hai trường hợp còn lại sẽ tiếp, một khi cái này có xu hướng không tồi!

    PHP:
    Option Explicit
    Dim lRow 
    As LongjZ As Long
    Dim lRowB 
    As LongjW As Long
    Dim Timer_ 
    As Double

    Sub SearchColumnC
    ()
     
    Dim lTemp As Long
     
     Timer_ 
    Timer
     lRow 
    Range("A65432").End(xlUp).Row
     lRowB 
    Range("b65432").End(xlUp).Row
     Application
    .ScreenUpdating False
     Sort1Col Range
    ("A2:A" lRow), Range("A3")
     
    Sort1Col Range("B2:B" lRow), Range("B3")
     
    Range("C2") = "List1<>List2"
     
    Range("C3:C" lRowB).ClearContents
     lTemp 
    3
     
    For jZ 3 To lRow
        
    For jW lTemp To lRowB
            
    If Cells(jZ1) = Cells(jW2Then
                lTemp 
    jW:         Exit For
            
    End If
        
    Next jW
        
    If jW lRowB Then _
            Range
    ("C" Range("C65432").End(xlUp).Row 1) = Cells(jZ1)
     
    Next jZ
     MsgBox Str
    (Timer Timer_)
    End Sub 
    PHP:

    Sub Sort1Col
    (Rng As RangeClls As Range)
      
    Rng.Sort Key1:=CllsOrder1:=xlAscendingHeader:=xlGuess_
        OrderCustom
    :=1MatchCase:=FalseOrientation:=xlTopToBottom_
        DataOption1
    :=xlSortNormal
    End Sub 
     
  11. minhlev

    minhlev A - Bờ - Cờ

    To bác Sa_DQ: Em nhận thấy trong code của bác có 2 vòng lặp For...Next.
    PHP:
    ....
        For 
    jZ 3 To lRow
              
    For jW 3 To lRow0
                    
    If Cells(jZ1) = Cells(jW2Then _
                    Range
    ("E" Range("E65432").End(xlUp).Row 1) = Cells(jZ1)
              
    Next jW
        Next jZ
    Theo em, nếu thay cái vòng lặp For...Next con bằng 1 cái CountIf thì tốc độ có thể cải thiện hơn ạ.
    PHP:
    ....
        For 
    jZ 3 To lRow
            
    If WorksheetFunction.CountIf(Range("B3:B" lRow0), Cells(jZ1)) > 0 Then _
                    Range
    ("E" Range("E65432").End(xlUp).Row 1) = Cells(jZ1)
        
    Next jZ
     
    Lần chỉnh sửa cuối: 19 Tháng ba 2008
  12. viendo

    viendo Tầm sư học Excel

    Nó báo lỗi không chạy Sư phụ ơi, khi ở cửa sổ VBE nhấn run thì nó báo lỗi Compile error : Variable not defined. Khi ở cửa sổ excel chạy thì nó báo lỗi Can't execute code in break mode.
    Cả 2 trường hợp trên đều báo lỗi ở Sub Sort1Col(). Sư phụ xem lại dùm với. Thanks
    Bây giờ chạy macromodule 1 nó cũng báo lỗi luôn Can't execute code in break mode.
     
    Lần chỉnh sửa cuối: 19 Tháng ba 2008
  13. phamduylong

    phamduylong Thành viên danh dự

    Bạn thử Sub SoSanh này:

    Mã:
    Sub SoSanh()
    Dim List1 As Range, List2 As Range, List12 As Range
    Sheets("Check").Select
    Range(Cells(3, 3), Cells(65536, 5)).ClearContents
    rc = Cells(3, 1).End(xlDown).Row
    Set List1 = Range(Cells(3, 1), Cells(rc, 1))
    Set List2 = Range(Cells(3, 2), Cells(rc, 2))
    Set List12 = Range(Cells(3, 5), Cells(rc, 5))
    r1 = 3
    r2 = 3
    r12 = 3
    For r = 3 To rc
    [COLOR=blue]  'So sanh list1 voi list2[/COLOR]
      kiemtra = Application.WorksheetFunction.CountIf(List2, Cells(r, 1))
      If kiemtra = 0 Then 'list1<>list2
        Cells(r1, 3) = Cells(r, 1)
        r1 = r1 + 1
      Else                  [COLOR=blue] 'list1=list2
    [/COLOR]    kiemtra = Application.WorksheetFunction.CountIf(List12, Cells(r, 1))
        If kiemtra = 0 Then
          Cells(r12, 5) = Cells(r, 1)
          r12 = r12 + 1
        End If
      End If
      
     [COLOR=blue] 'So sanh list1 voi list2[/COLOR]
      kiemtra = Application.WorksheetFunction.CountIf(List1, Cells(r, 2))
      If kiemtra = 0 Then 'list2<>list1
        Cells(r2, 4) = Cells(r, 2)
        r2 = r2 + 1
      Else                  [COLOR=blue] 'list1=list2
    [/COLOR]    kiemtra = Application.WorksheetFunction.CountIf(List12, Cells(r, 2))
        If kiemtra = 0 Then
          Cells(r12, 5) = Cells(r, 2)
          r12 = r12 + 1
        End If
      End If
    Next
    End Sub
    
     

    Các file đính kèm:

  14. SA_DQ

    SA_DQ Thành viên danh dự

    Macro chinh & Macro phụ.

    Một khi báo lỗi thì nên thoát ra đâu đó, như windows chẵng hạn, hút điếu thuốc/làm ly cà phê mới quay lại excel, bình tâm mà chạy tiếp;
    Chú í các macro cần được cung cấp biến, ta không thể dùng {F5} để chạy trực tiếp nó được;
    Bạn nhớ chép tất thẩy các dòng lệnh, kể các các biến khai báo trên cả các macro; đó là ~ biến dùng chung;
    Chép xong thử vô menu Debug & biên dịch xem thử, VBA có cự nự gì về biến hay không.
    Trước khi chạy macro bằng fím {F5} phải nhớ mở sheets chứa dữ liệu cho excel nó thấy, để nó tìm, chứ không nó trách chúng ta thờ ơ chuyện này!!
    Nếu bạn biết làm nút lệnh trên trang tính thì thực thi đi; để đỡ tốn thời gian!

    NHỜ CÁC MOD/SMOD XÓA BÀI NÀY SAU 2 GIỜ, XIN CẢM ƠN!
    [​IMG]
     
  15. dmtdmtbb

    dmtdmtbb Biệt danh: xDelx

    Xem thử cái này coi có nhanh hơn chút nào không !
    Kiểm tra dữ liệu với 10.000 dòng sẽ thấy kết quả.
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 19 Tháng ba 2008
  16. Mr Okebab

    Mr Okebab Ngon Ngất Ngây

    Dùng Countif quả thật nhanh hơn For (đã có bài về vấn đề này), tuy nhiên hàm MATCH sẽ nhanh hơn nhiều.

    Đơn giản bởi Countif luôn luôn phải quét hết mảng, còn MATCH thì không, đặc biệt là với mảng được sắp xếp.

    Thân!
     
  17. viendo

    viendo Tầm sư học Excel

    Xin cám ơn các sư phụ đã tận tình.
    Test 3 file của 3 Sư phụ luôn trên máy Intel(R) Pentium(R) 4 CPU 3.00GHz (2CPUs), Memory 256MB RAM với dữ liệu là mỗi list 15.000 dòng :
    1. SA_DQ : Chạy code ở module1 cho cột C hơn 05' thế là đệ tử cho End luôn, Chạy code ở module2 cho cột C chỉ có 25". Vậy sư phụ cải tiến tiếp cho cột D, E nữa nhé, vì đôi khi cũng chỉ cần lấy kết quả 1 cột.
    2. Thầy Long : Chạy ra 3 cột luôn mất 03'09".
    3. Dmtdmtbb : Chạy ra 3 cột luôn mất 08'20".

      Trong đó có 1 điểm khác là ở kết quả cột E của Thầy Long và Dmtdmtbb :
      1. File Thầy Long lấy danh sách duy nhất không có trùng.
      2. Dmtdmtbb lấy danh sách có trùng luôn, vì vậy mà chạy lâu hơn.
        Không lường trước tình huống này nhưng đều sử dụng được cả 2 file này vì 2 list cột A và B bản thân không có trùng.

        Vậy là đệ tử lấy luôn 3 file này nhé, phiền sư phụ SD_QD làm tiếp cột D, E nữa nhé. Một lần nữa xin cám ơn sự tận tình giúp đỡ của các Thầy, các sư phụ.
     
  18. SA_DQ

    SA_DQ Thành viên danh dự

    Đúng BAB tuyệt chiêu!
    Mình thử 3 phương án với cột 'C' khi dữ liệu cột 'A' = 1.000 dòng; cột 'B' = 400 dòng
    Mcr của mình mất: 1.1094'' (làm tròn)
    Theo hàm COUNTIF(): 0.3438''
    Theo hướng dẫn của BAB: 0.1875''
    Mà dùng 2 hàm này thấy nhẹ hẵn macro đi rất nhiều
    Sau đây xin giới thiệu đến VienDo, nha:
    PHP:
    Option Explicit
    Public Timer_ As Double
    Dim lRow 
    As LongjZ As Long
    Dim lRowB 
    As LongjW As Long
    Sub SearchColumnF
    ()
     
    Dim lTemp As Long
     
     Timer_ 
    Timer
     lRow 
    Range("A65432").End(xlUp).Row
     lRowB 
    Range("b65432").End(xlUp).Row
     Application
    .ScreenUpdating False
     Sort1Col Range
    ("A2:A" lRow), Range("A3")
     
    Sort1Col Range("B2:B" lRow), Range("B3")
     
    Range("C2") = "List1<>List2"
     
    Range("C3:C" & (lRowB lRow)).ClearContents
     lTemp 
    3
     
    For jZ 3 To lRow
    '    If WorksheetFunction.CountIf(Range("B3:B" & lRowB), Cells(jZ, 1)) = 0 Then '
        
    If WorksheetFunction.Match(Cells(jZ1), Range("B3:B" lRowB)) = lRowB 2 Then
            Range
    ("C" Range("C65432").End(xlUp).Row 1) = Cells(jZ1)
        
    End If
     
    Next jZ
     Range
    ("F7") = Timer Timer_
    End Sub 
    PHP:
     Public Sub Sort1Col(Rng As RangeClls As Range)
      
    Rng.Sort Key1:=CllsOrder1:=xlAscendingHeader:=xlGuess_
        OrderCustom
    :=1MatchCase:=FalseOrientation:=xlTopToBottom_
        DataOption1
    :=xlSortNormal
    End Sub 
    VienDo chú í:
    Mình đã khai báo 1 biến toàn cục Timer_ ; Coi chừng báo lỗi khi biên dịch nha;
    Cũng như vậy với macro Sort1Cot ;
    Có thể xóa macro Sort1Cot & biến Timer_ cũ đi; Một khi đã chép toàn bộ 2 macro này vô module thứ 3 của CS VBE
     
  19. dmtdmtbb

    dmtdmtbb Biệt danh: xDelx

    Để lấy kết quả cột E có 3 cách :
    C1 : so sánh A và B tìm những phần tử trùng nhau cho qua cột E,lâu hơn nhưng độc lập.
    C2 : sau khi lọc được A<>B, dựa vào cột này loại bỏ những phần tử vừa lọc => (A=B),nhanh hơn nhiều.
    C3 : Trong khi lọc A<>B thì cho qua E những phần tử A=B, bỏ qua bước lọc A=B, cực nhanh.
     
    Lần chỉnh sửa cuối: 20 Tháng ba 2008
  20. SA_DQ

    SA_DQ Thành viên danh dự

    Nhưng chúng ta chưa hỏi tác gia Topic cho biết đặt thù của dữ liệu tại cột 'A' & 'B' NTN (!)
    Nếu 'A' gấp 12 lần 'B' hay ngược lại Hoặc thường chỉ tìm thấy 1/10 trên 'C' thì cách giải có thể # so với chương trình chuẩn.
    Tiện đây, cho hỏi về dữ liệu của VD luôn, hàng tháng thường sẽ NTN?
     

Chia sẻ trang này