Lọc dữ liệu

heocon · 21/1/08

Chào các anh chị giải pháp Excel!
Hôm nay heocon lại có 1 bài toán muốn nhờ anh chị giúp đỡ.
Em ko bít đặt tiêu đề như thế đã đúng với bài toán này chưa. Nêu ko phù hợp nhờ các Mod thay đổi giùm.

Em có 1 cột khách hàng ghi sai ỏ gần đúng. Em muốn sữa chúng lại cho chính xác.
Em lập ra 1 thư viện tên các khách hàng, để các tên sai sẽ tham chiếu vào.
Em ko bit viet VBA như thế nào, mong anh chị giúp em.
Chân thành cám ơn tất cả mọi người!
Chúc mọi người luôn vui vẽ.
Thân!
heo

heocon · 21/1/08

Gộp

Lại gặp được gặp anh SA_DQ rùi, cám ơn anh rất nhiều.
Nhưng sao heocon copy vào nó ko chạy vậy? Anh làm cho em luôn được ko?
Cám ơn anh!

Oh em làm được rồi anh ạ. Cám ơn anh rất nhiều
Nhưng sao em nhấn nút Lọc 2 lần thì nó lại x2 dữ liệu vậy anh?
Bây giờ em muốn tạo thêm nút Xóa baocao thì viết code như thế nào vậy anh?
Và anh giúp em tạo thông báo lổi nếu tên khách hàng ko có trong thư viện.
Mong anh chỉ giúp.

Em cám ơn mọi người rất nhiều.
Các anh chị có thể giúp em thêm 2 ý nữa ko?
1. Chỉ ra các dữ liệu không lọc được (không có trong thư viện), để em có thể biết mà sữa lại.
2. Em đổ 5000 dòng dữ liệu vào thì chạy chậm quá (treo máy luôn)

Nhờ MOD gộp bài lại giúp em.
Thân!

ThuNghi · 21/1/08

heocon đã viết:
Oh em làm được rồi anh ạ. Cám ơn anh rất nhiều
Nhưng sao em nhấn nút Lọc 2 lần thì nó lại x2 dữ liệu vậy anh?
Bây giờ em muốn tạo thêm nút Xóa baocao thì viết code như thế nào vậy anh?
Mong anh chỉ giúp.

Bạn dùng thử

PHP:

Sub XoaBaoCao()
Sheets("BaoCao").Range("A2:A65000").ClearContents
End Sub

SA_DQ · 21/1/08

heocon đã viết:
Em cám ơn mọi người rất nhiều.
1. Chỉ ra các dữ liệu không lọc được (không có trong thư viện), để em có thể biết mà sữa lại.
2. Nhờ MOD gộp bài lại giúp em. Thân!

1./

PHP:

Option Explicit:            Option Base 1

Sub DichTen()
' By Sa_DQ (GiaiPhapExcel.COM)'
 Dim lRow As Long, jZ As Long
 Dim lRw1 As Long, Jw As Long
  
 Application.ScreenUpdating = False
 Sheets("BaoCao").Range("A2:A9999").Clear  '!!'
 
 Sheets("Thu Vien").Select
 lRow = Range("B65432").End(xlUp).Row
 ReDim MDL(lRow, 2) As String
 For jZ = 2 To lRow
    MDL(jZ, 1) = Cells(jZ, 1):          MDL(jZ, 2) = Cells(jZ, 2)
 Next jZ
 Sheets("DuLieu").Select
 lRw1 = Range("A65432").End(xlUp).Row
 ToMau Range(Cells(2, 1), Cells(lRw1, 1)) 'N!!!'
 For jZ = 2 To lRw1
    For Jw = 1 To lRow
        If Cells(jZ, 1) = MDL(Jw, 1) Then
            Sheets("BaoCao").Range("A" & Sheets("BaoCao"). _
                Range("A65432").End(xlUp).Row + 1) = UCase$(MDL(Jw, 2))
            Exit For
        End If
    Next Jw
    If Jw = 1 + lRow Then ToMau Cells(jZ, 1), 33 + Weekday(Date) 'N!!!'
 Next jZ
End Sub

Mã:

[b]Sub ToMau(Rng As Range, Optional CIndex = 2)[/b]
    With Rng.Interior
        .ColorIndex = CIndex:               .Pattern = xlSolid
    End With[b]
End Sub [/b]

heocon · 21/1/08

Hix... Vẫn không được anh SA_DQ ơi.
EM đổ 5000 dòng dữ liệu nó treo máy luôn nè. Không phát hiện lổi
Mong mọi người giúp em bài này.
Cám ơn tất cả rất nhiều.

Thân!

ThuNghi · 21/1/08

heocon đã viết:
Hix... Vẫn không được anh SA_DQ ơi.
EM đổ 5000 dòng dữ liệu nó treo máy luôn nè. Không phát hiện lổi
Mong mọi người giúp em bài này.
Cám ơn tất cả rất nhiều.

Thân!

Ý là bạn muốn kiểm tra DuLieu có trong ThuVien??
Tại B2 Sh Dulieu bạn dùng countif là OK, Filter lại những cái = 0
B2=COUNTIF('Thu Vien'!$A$2:$A$61,DuLieu!$A2)
Làm xong rôi mới chạy code đầu tiên của Bác Sa.

heocon · 21/1/08

Cám ơn mọi người đã giúp em.

Ý là bạn muốn kiểm tra DuLieu có trong ThuVien??
Tại B2 Sh Dulieu bạn dùng countif là OK, Filter lại những cái = 0
B2=COUNTIF('Thu Vien'!$A$2:$A$61,DuLieu!$A2)

Em cũng làm như vậy, nhưng sao lại có 2,3,4 nhỉ?
Số 0 là không có trong thư viện, số là co trong thư viện. Vậy các số còn lại nghĩa gì vậy ạ?

Tại Sheet Thu Vien em đã dùng hàm Upper() chuyển tất cả dữ liệu thành chữ HOA rồi.

Dữ liệu của em chính xác là 57406 dòng, file nặng quá em ko gửi lên được.

Vẫn bị treo máy các anh ơi. Làm cách nào để khắc phục tinh trạng này bây giờ ạh?

Thân!

dat2007 · 21/1/08

heocon đã viết:
Dữ liệu của em chính xác là 57406 dòng, file nặng quá em ko gửi lên được.
Vẫn bị treo máy các anh ơi. Làm cách nào để khắc phục tinh trạng này bây giờ ạh?

Phương án 1: Copy toàn bộ các dòng có công thức sau đó PasteSpecial -> Values

Phương án 2: Em ra cửa hàng bán máy vi tính bảo họ cắm thêm Ram cho máy

Phương án 3: nếu muốn gửi File thì làm theo PA1 sau đó dùng WinZip hoặc WinRar nén File lại

ThuNghi · 21/1/08

heocon đã viết:
Cám ơn mọi người đã giúp em.

Em cũng làm như vậy, nhưng sao lại có 2,3,4 nhỉ?
Số 0 là không có trong thư viện, số là co trong thư viện. Vậy các số còn lại nghĩa gì vậy ạ?

Tại Sheet Thu Vien em đã dùng hàm Upper() chuyển tất cả dữ liệu thành chữ HOA rồi.

Dữ liệu của em chính xác là 57406 dòng, file nặng quá em ko gửi lên được.

Vẫn bị treo máy các anh ơi. Làm cách nào để khắc phục tinh trạng này bây giờ ạh?

Thân!

Mèn ơi! nếu count > 0 là có hơn 0 lần thì OK còn chỉ sửa những cái mà ra số 0 thôi.

ThuNghi đã viết:
Mèn ơi! nếu count > 0 là có hơn 0 lần thì OK còn chỉ sửa những cái mà ra số 0 thôi.

Bác Sa ơi, em không biết cách chuyển sang phần code.

SA_DQ · 21/1/08

To HeoCon

1.* Hãy xài các file nén đi; Trên DĐ cũng nói đến nhiều rồi!
2.* Trước mắt tách ra làm nhiều file gởi lên:
a./ Sheets("Thu Vien")
b./ Sheets("DuLieu") tách tối đa mà diễn đàn cho phép tải lên & tải lần lượt theo trật tự từng workbook(i)
(Bạn nào có cách khác hay hơn chỉ giúp bạn í với!)

Chú ý: TRong macro mình chỉ ghi '9999'; Bạn tìm đến đó & sửa lại là '99999' (để phù hợp với dữ liệu của bạn mà!)

Cách chạy thử macro: Lấy đơn vị là 5.000 record, thêm lần lượt từng đơn vị records & cho diễn đàn biết nó đứng ở đơn vị nào;
Tiếp theo: Xóa hết các đơn vị n-1 đi; Bắt đầu lấy từ đơn vị thứ n (khi máy đứng) trở đi; Sau mỗi lần nối 1 đơn vị records thì chạy macro xem như thế nào!
Chú í thứ hai: Một khi đã đổi hết cột thứ 2 trong 'Thu Vien' sang chữ hoa, thì bỏ hàm Ucase$() trong macro giúp nha!

(hờ tin thắng lợi từ bạn!

workman · 22/1/08

Tôi dùng tạm công thức ... vlookup nhé. Tôi không viết VBA gì cả, nên bạn đừng nhấn vào nút lọc.

Thực ra nếu có thể giải quyết bằng công thức thì chắc chắn sẽ nhanh hơn dùng VBA (kinh nghiệm thực tế).

Nếu sheet của bạn có hơn 57 ngàn dòng (tức là gần đến giới hạn của EX) thì chắc chắn sẽ chạy chậm, không có cách nào khắc phục tốt hơn là ... đừng làm bằng Excel nữa, mà nên chuyển sang một Cơ sở dữ liệu chuyên nghiệp hơn, vd Access.

heocon · 22/1/08

Treo Máy

Hix...Em bi treo máy hoài.
Em gửi file nhỏ nhất ên cho mọi người xem và xữ lý giúp em. Em chia làm nhiều file nhỏ, mọi người cố gắng nối lại nha.
Chân thành cám ơn sự nhiệt tình của các anh chị!

ThuNghi · 22/1/08

Theo tôi dùng vlookup như workman là ok và dễ nhất, thêm dk if nếu isna thì =1.
Với lại bạn muốn có tên đúng bên sh dulieu thì hay hhơn, như vậy mới biết tên gần đúng thế nào và phải thay thế = tên đúng. Chớ còn KQ ở Sh BaoCao chả có nghĩa gì.

SA_DQ · 22/1/08

Hix...Em bi treo máy hoài.
Em gửi file nhỏ nhất ên cho mọi người xem và xữ lý giúp em. Em chia làm nhiều file nhỏ, mọi người cố gắng nối lại nha.
Chân thành cám ơn sự nhiệt tình của các anh chị!

Sau khi nối lại & dùng macro sau cùng, máy của mình mất 3 phút để hoàn tất.

Một việc khác nửa là thư viện của bạn còn chưa đạt ở chổ, có rất nhiều khoảng trống thừa trong các từ ở cột 'A';
Để thấy khiếm khuyết này, bạn hãy sort dữ liệu theo cột 'A' sẽ rõ ngay!
Mình viết cho bạn macro này để khỏi làm bằng tay, cái chuyện cắt bỏ các khoảng trắng í đi:

PHP:

Option Explicit

Sub ThuVien()
 Dim lRow As Long, iJ As Long
 Sheets("Thu Vien").Select
 Application.ScreenUpdating = False
 lRow = Range("A65432").End(xlUp).Row
 For iJ = 2 To lRow
    With Cells(iJ, 1)
        If Len(.Value) > Len(Trim(.Value)) Then
            .Value = Trim(.Value)
        End If
    End With
 Next iJ
End Sub

$:=\+$

Bổ sung:
HeoCon chạy thử file này xem mất bao nhiêu phút?!

heocon · 22/1/08

Cám ơn anh và mọi người!

Em chép 2 đoạn Code mới nó không chạy. $-\\/.$
Cột A bên sheet Thuvien là du liệu gần đúng, dùng để tham chiếu mà. Nếu xóa khoảng trắng thì các dữ liệu # ko thể dựa và để tham chiếu. (Ko nên thay đổi dữ liệu cột A)
Em đã thử bài này với nhiều máy, KQ máy nào cũng treo
Xóa dữ liệu trùng là ý kiến hay, Tại cột A nếu thấy 2 dòng trùng nhau thì lấy 1 dòng (cái này em làm dc)

Tóm lại bài của "Sư phụ" rất tuyệt, nhưng làm sao cho nó nhanh (ko treo máy) thì tuyệt vời }}}}}

Anh chỉnh lại giúp em nhé.

Thân!
$-\\/.$ $-\\/.$ $-\\/.$ $-\\/.$ $-\\/.$ $-\\/.$
Bổ sung:
Em đã chạy thử file mới của anh Sa_DQ rùi, KQ gần 10 phút mới xong. Còn file 57000 dòng thì 45 phút. hix....

heocon · 23/1/08

File mới của "sư phụ" máy em chạy gần 10 phút.
Với dữ liệu 57000 dòng thì 45 phút hix....
"Sư phụ" còn nhớ bài kế toán lần trước không? Bài đó cũng dựa vào thư viện đó.

Em đã thư file của anh Workman rùi, tốc độ cũng chậm & cho kết quả thì double. Em ko bit tại sao nữa.

Thân!

SA_DQ · 23/1/08

Nổ lực tăng tốc đã được đền đáp

HeoCon thử đoạn mã sau xem tăng tốc được mấy lần!
Chú ý các dòng lệnh có đánh số là mới thêm hay sửa đổi so với trước

(Xin chuyển sang xem Code tại bài 18 & file tại bài 17, cảm ơn!)

PHP:

Option Explicit:          Option Base 1

Sub ToMau(Rng As Range, Optional CIndex = 2)
    With Rng.Interior
        .ColorIndex = CIndex:               .Pattern = xlSolid
    End With
End Sub

heocon · 25/1/08

Em đã chạy thử File mới kết quả chạy rất tốt và nhanh.
Nhưng em ko hiểu anh bôi màu và đánh số bên sheet Thu viện để làm gì? Em nhìn rối quá ko hiểu. Sao các dữ liệu sai bên thu vien anh làm "sạch" hết rồi.
Sau này em thêm bớt dữ liệu bên thư viện thì làm sao?
Anh đánh thêm dấu (x) vào dữ liệu ko lôc đc nhé
Em gửi file anh xem nha.

Thân! )(&&@@

SA_DQ · 25/1/08

heocon đã viết:
Em đã chạy thử File mới kết quả chạy rất tốt và nhanh.
Nhưng em ko hiểu anh bôi màu và đánh số bên sheet Thu viện để làm gì? Em nhìn rối quá ko hiểu. Sao các dữ liệu sai bên thu vien anh làm "sạch" hết rồi.
Sau này em thêm bớt dữ liệu bên thư viện thì làm sao?
Anh đánh thêm dấu (x) vào dữ liệu ko lôc đc nhé
Em gửi file anh xem nha. Thân!

Mình chưa xem file, nhưng thế này:
1*/ Bôi màu bên file thư viện: Đó là những dòng trùng nhau, Em xem lại, nếu đúng là không cần thiết thì bỏ đi bớt, máy sẽ chạy nhanh hơn;
Hình như các dòng đã được xếp theo 1 trật tư nào đó rất dễ kiểm mà!
2*/ Em cứ việc thêm dữ liệu mới vô thư viện, có sao đâu; Chỉ có điều càng thêm thì macro càng cần nhiều thời gian cho nó hơn thôi!
3*/ Đã tô màu rồi; còn muốn đánh dấu để xếp lại đễ tìm, phải không?
Nếu không phải thì cho biết mục đích đánh dấu để tiếp tục làm gì vậy?

Bổ sung: Viết luôn cho nè!

PHP:

Sub DichTen()
' By Sa_DQ (GiaiPhapExcel.COM)'
1 Dim Timer_ As Double:                  Dim STemp As String
 Dim lRow As Long, jZ As Long
 Dim lRw1 As Long, Jw As Long
   
 Timer_ = Timer
 Application.ScreenUpdating = False
 Sheets("BaoCao").Range("A2:A" & Range("A65432").End(xlUp).Row + 9).Clear
  
 Sheets("Thu Vien").Select
 lRow = Range("B65432").End(xlUp).Row
 ReDim MDL(lRow, 2) As String
 For jZ = 2 To lRow
    MDL(jZ, 1) = Cells(jZ, 1):          MDL(jZ, 2) = Cells(jZ, 2)
 Next jZ
 Sheets("DuLieu").Select
 lRw1 = Range("A65432").End(xlUp).Row
 ToMau Range(Cells(2, 1), Cells(lRw1, 1))
 For jZ = 2 To lRw1
2    If Cells(jZ, 1) = STemp Then
3        With Sheets("BaoCao").Range("A" & Sheets("BaoCao"). _
            Range("A65432").End(xlUp).Row + 1)
4            .Value = .Offset(-1)
5        End With
6        GoTo DenDay
7    End If
    For Jw = 1 To lRow
        If Cells(jZ, 1) = MDL(Jw, 1) Then
            Sheets("BaoCao").Range("A" & Sheets("BaoCao"). _
                Range("A65432").End(xlUp).Row + 1) = MDL(Jw, 2)
            Sheets("Thu Vien").Cells(Jw, 4).Value = Jw
8            STemp = Cells(jZ, 1):                      Exit For
        End If
    Next Jw
    If Jw = 1 + lRow Then
        Cells(jZ, 2) = " x"    ' <= New'
        ToMau Cells(jZ, 1), 33 + Weekday(Date)
    End If
9 DenDay:
 Next jZ
 MsgBox Str(Timer - Timer_), , Str(jZ) & " Records"
End Sub

Hãy chép cái này đè lên cái cũ!
Chúc Vui Với Xuân Về!

heocon · 25/1/08

1*/ Bôi màu bên file thư viện: Đó là những dòng trùng nhau, Em xem lại, nếu đúng là không cần thiết thì bỏ đi bớt, máy sẽ chạy nhanh hơn;

dữ liệu trùng là do Bác SA làm sạch dữ liệu (xóa khoảng trắng), nhưng dữ liệu gốc rất nhìu rác. Chẳng lẽ trước khi Lọc mình phải làm sạch sao?

2*/ Em cứ việc thêm dữ liệu mới vô thư viện, có sao đâu; Chỉ có điều càng thêm thì macro càng cần nhiều thời gian cho nó hơn thôi!

Khi em thêm dữ liệu lại có khoảng trắng hay rác thì phãi làm sạch trước à?

3*/ Đã tô màu rồi; còn muốn đánh dấu để xếp lại đễ tìm, phải không?

Đúng như vậy đó ạ.

Lọc dữ liệu

Thành viên chính thức

File đính kèm

Thành viên chính thức

Hãy cho rồi sẽ nhận!

/(hông là gì!

Thành viên chính thức

Hãy cho rồi sẽ nhận!

Thành viên chính thức

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

Hãy cho rồi sẽ nhận!

/(hông là gì!

Thành viên chính thức

File đính kèm

Thành viên chính thức

File đính kèm

Hãy cho rồi sẽ nhận!

/(hông là gì!

Thành viên chính thức

Thành viên chính thức

/(hông là gì!

Thành viên chính thức

File đính kèm

/(hông là gì!

Thành viên chính thức

Đếm ngược thời gian