Xin hỗ trợ lọc dữ liệu theo điều kiện bằng VBA. Một khách hàng có 2 loại bệnh.

[anh1234]

Dear cả nhà
Em có 1 file ( gồm 100 cột, khoảng 100.000 dòng)
Đề bài:
Sheet dữ liệu gồm có 100 cột, 100.000 dòng.
1. Tìm các khách hàng ( theo mã khách hàng) : 1 khách hàng có từ 2 loại bệnh trở lên( 2,3,4,5 loại bệnh. Có tất cả 5 loại bệnh. (1-2-3-4-5).
Copy toàn bộ dữ liệu trên 1 dòng của khách hàng thỏa mãn điều kiện ( 1 khách hàng có 2 loại bênh trở lên) sang sheet mới. Copy toàn bộ các dòng cùng mã khách hàng thuộc ds vừa lọc được.
( tương tự sheet kết quả).
File mẫu em chỉ để 14 cột và 63 dòng cho nhẹ, nhưng data của em phải 100 cột, 100.000 dòng.
2. Tạo ô điền tham chiếu cột mã khác hàng và cột loại bệnh để xác định đúng dữ liệu trong công thức VBA khi có sự thay đổi.
Ở ví dụ trên thì ô đó sẽ điền Cột mã khách hàng"Cột C" và cột Bệnh "Cột D". ( Thay vì việc phải sửa công thức thì chỉ cần điền thông tin bên ngoài và ấn click
Tạo nút Run để chạy công thức ạ
Thay vì việc phải sửa công thức thì chỉ cần điền thông tin bên ngoài và ấn click ( cái này đòi hỏi hơi quá, nhưng đã tham thì phải ko thâm ạ!!
Em xin đã tạ các pro ạ.

 

[Hau151978]

Bạn thử đưa dữ liệu lớn lên xem. Trường hợp dữ liệu giả có thể dùng code.

Sub xxx()
    Dim arrData(), arrCotPhu(), m&, n&, i&, k&
    Dim Dic As New Dictionary
    Sheets("Data").Activate
    n = Range("A" & Columns(1).Rows.Count).End(xlUp).Row
    m = Range("A1").End(xlToRight).Column
    arrData = Range("A2", Cells(n, m)).Value2
    ReDim arrCotPhu(1 To n - 1, 1 To 2)
    For i = 1 To n - 1
        If Not Dic.Exists(arrData(i, 3)) Then
            Dic.Add arrData(i, 3), arrData(i, 4)
        ElseIf Dic.Item(arrData(i, 3)) <> arrData(i, 4) Then
            Dic.Item(arrData(i, 3)) = "ok"
        End If
    Next
    For i = 1 To n - 1
        arrCotPhu(i, 1) = (Dic.Item(arrData(i, 3)) = "ok")
        arrCotPhu(i, 2) = i
        If arrCotPhu(i, 1) Then k = k + 1
    Next
    Range(Cells(2, m + 1), Cells(n, m + 2)) = arrCotPhu
    Range("A2", Cells(n, m + 2)).Sort key1:=Cells(2, m + 1), order1:=xlDescending, Header:=xlNo
    Range("A2", Cells(k, m)).Copy Sheets("KQ").Range("A2")
    Range("A2", Cells(n, m + 2)).Sort key1:=Cells(2, m + 2), order1:=xlAscending, Header:=xlNo
    Range(Cells(2, m + 1), Cells(n, m + 2)).Clear
    Set Dic = Nothing
End Sub

 

[befaint]

Dim Dic As New Dictionary

. Thêm hướng dẫn để dùng nó.

Sheets("Data").Activate

. Không nên dùng cách này.

m = Range("A1").End(xlToRight).Column

. Nên dùng xlToleft.
. Cần kiểm tra giá trị m, n so với cell đầu tiên của vùng dữ liệu cần xét.
. Sao không dùng usedrange?
.
. Thêm biến gán cho arrData(i,3) để không gọi lại nhiều lần.

 

[Hau151978]

. Thêm hướng dẫn để dùng nó.


. Không nên dùng cách này.

. Nên dùng xlToleft.
. Cần kiểm tra giá trị m, n so với cell đầu tiên của vùng dữ liệu cần xét.
. Sao không dùng usedrange?
.
. Thêm biến gán cho arrData(i,3) để không gọi lại nhiều lần.

Mình thường sử dụng xltoright để tiện thể kiểm tra luôn dòng tiêu đề có ô trống không?
Ý bác kiểm tra m, n để trường hợp không có dữ liệu tránh lỗi phải không, trường hợp này chắc chỉ phần mềm thương mại mới nên làm còn người sử dụng thông thường có lỗi tý ở đó vẫn chấp nhận được.
Arr(i,3) không phải là biểu thức, nó không khác gì 1 biến, gán vào 1 biến chỉ làm code ngắn hơn còn tốc độ chưa chắc cải thiện, sử dụng intelli sense để gõ code cũng không chậm mấy.
Còn tại sao không nên dùng sheet.activate mình không rõ, bác giải thích hộ với?

 

[truongvu317]

Còn tại sao không nên dùng sheet.activate mình không rõ, bác giải thích hộ với?

activate tùm lum thì sẽ có ngày gặp họa:
+Chạy code mà màn hình excel nhảy linh tinh, bạn có thấy chức năng nào của excel khi chạy mà các sheet kích hoạt linh tinh không?
+Nếu cứ có thới quen vậy, khi viết code lớn sẽ dễ gây mất kiểm soát. Sẽ tương tác vào cách sheet không chuẩn ( có thể phải trả giá đắt do mất dữ liệu)
+Đây chính là hậu quả của dân macro ( là những người mới học vba, chỉ thích lập trình theo kiểu mô tả lại các thao tác trên màn hình). Đã là xử lý dữ liệu thì chỉ cần làm những cái tối thiếu, không gây ảnh hướng đến bất cứ cái gì, chỉ ngoài vùng chứa kết quả, code chạy mà im thin thít mà vẫn cho ra kết quả. Như đên cái ".copy" tui còn ngại không dùng.

 

[VetMini]

. Thêm hướng dẫn để dùng nó.


. Không nên dùng cách này.

. Nên dùng xlToleft.
. Cần kiểm tra giá trị m, n so với cell đầu tiên của vùng dữ liệu cần xét.
. Sao không dùng usedrange?
.
. Thêm biến gán cho arrData(i,3) để không gọi lại nhiều lần.

Tất cả cái này đều không quan trọng. Thớt làm việc tới 100 cột và 100000 dòng => 10 triệu mẫu dữ liệu
Cốt kiếc gì đó chỉ là góp ý thôi. Người sử dụng phải tự viết để nắm vững nó mới kiểm soát được những thay đổi và biết đề phòng những chỗ sai.

 

[Hau151978]

activate tùm lum thì sẽ có ngày gặp họa:
+Chạy code mà màn hình excel nhảy linh tinh, bạn có thấy chức năng nào của excel khi chạy mà các sheet kích hoạt linh tinh không?
+Nếu cứ có thới quen vậy, khi viết code lớn sẽ dễ gây mất kiểm soát. Sẽ tương tác vào cách sheet không chuẩn ( có thể phải trả giá đắt do mất dữ liệu)
+Đây chính là hậu quả của dân macro ( là những người mới học vba, chỉ thích lập trình theo kiểu mô tả lại các thao tác trên màn hình). Đã là xử lý dữ liệu thì chỉ cần làm những cái tối thiếu, không gây ảnh hướng đến bất cứ cái gì, chỉ ngoài vùng chứa kết quả, code chạy mà im thin thít mà vẫn cho ra kết quả. Như đên cái ".copy" tui còn ngại không dùng.

Activate đúng sheet thì sao lại tương tác vào sheet không chuẩn được bạn. Còn để tránh nhảy linh tinh thì thêm screenupdating=false. Mình chưa thêm vì những thứ đó chỉ khi hoàn thiện code, viết thật trau chuốt rồi người ta không sử dụng thì phí công, mình đặt dic=new dictionary mà không giải thích phải khai báo trong reference cũng là vì lý do này. Mình thích copy range hơn là dùng mảng. Dữ liệu lớn thì mảng hay chết, đặc biệt có khi nạp vào mảng thì được mà gán lại mảng đó vào sheet lại gây lỗi runtime error 1004 application defined or object defined rất khó chịu.

 

[VetMini]

Activate đúng sheet thì sao lại tương tác vào sheet không chuẩn được bạn. Còn để tránh nhảy linh tinh thì thêm screenupdating=false. Mình chưa thêm vì những thứ đó chỉ khi hoàn thiện code, viết thật trau chuốt rồi người ta không sử dụng thì phí công, mình đặt dic=new dictionary mà không giải thích phải khai báo trong reference cũng là vì lý do này.

Activate sheet chả sao cả (*) - trừ môt vài trường hợp hết sức đặc biệt cần phải tránh. Theo tôi thì nó còn rõ hơn là dùng block With nhiều.
Tuy nhiên, nếu tôi làm thì ở đầu sấp tôi save lại cái sheet đang active. Cuối sấp tôi trả nó về trạng thái cũ.

(*) tôi không rõ bài #5 muốn nói "khi viết code lớn" là như thế nào cho nên không thể bàn thêm nữa.

 

[gttrongvn]

Code này, nếu xử lý dữ liệu 100.000 dòng thì em thấy nhanh hơn, nhưng em nhìn hơi rườm rà, ko biết có cách nào để gộp 2 cái vòng lặp i vào một không nhỉ, nhờ mấy anh chị trên đây giúp đỡ để tối ưu hơn.

Option Base 1
Option Explicit
Sub GPE()
Dim Arr1(), Arr2()
Dim i,  k, l, a, b As Integer
Arr1 = Sheet1.UsedRange
For i = 1 To UBound(Arr1, 1)
      a = WorksheetFunction.CountIf(Sheet1.Range("C:C"), Arr1(i, 3))
      b = WorksheetFunction.SumIfs(Sheet1.Range("D:D"), Sheet1.Range("C:C"), Arr1(i, 3))
      If a > 1 And b > a Then k = k + 1
Next i
ReDim Arr2(1 To k, 1 To UBound(Arr1, 2))
k = 0
For i = 1 To UBound(Arr1, 1)
      a = WorksheetFunction.CountIf(Sheet1.Range("C:C"), Arr1(i, 3))
      b = WorksheetFunction.SumIfs(Sheet1.Range("D:D"), Sheet1.Range("C:C"), Arr1(i, 3))
      If a > 1 And b > a Then
            k = k + 1
            For l = 1 To UBound(Arr1, 2)
                  Arr2(k, l) = Arr1(i, l)
            Next l
      End If
Next i
Sheet3.Range("A2").Resize(UBound(Arr2, 1), UBound(Arr2, 2)) = Arr2
End Sub

 

[SA_DQ]

Là mình thì sẽ thực hiện các bước sau:
1./ Duyệt CSDL, lưu lại những mã khách hàng có 2 loại bệnh trỡ lên;
2./ Tạo vòng lặp duyệt theo danh sách vừa có (DSM);
Lồng trong nó là 1 vòng lặp duyệt toàn CSDL;
Nếu gặp đúng mã bệnh nhân trong DSM thì ghi lại (vô mảng tất nhiên rồi)
2./ Xem hay lưu vô đâu đó.

 

[be_09]

Dear cả nhà
Em có 1 file ( gồm 100 cột, khoảng 100.000 dòng)
Đề bài:
......................................................
File mẫu em chỉ để 14 cột và 63 dòng cho nhẹ, nhưng data của em phải 100 cột, 100.000 dòng.
....................................................

Góp ý cho bạn:
1/ Bạn nên đưa File có 100 cột lên để mọi người xem và góp ý hoặc thiết kế lại (có thể còn 30 cột) để việc nhập liệu làm sao cho nhanh, dễ dàng (nhập liệu đến 100 cột nội cái chuyện di chuyển đã hết hơi rồi, chưa kể đến việc nhập liệu sai cột).
2/ Nếu File có 100 cột kia là cái mẫu làm theo quy định thì khi cần tổng hợp báo cáo thì ta chạy cho nó ra để báo cáo.
3/ Sau khi thiết kế lại thì việc tìm 1 khách hàng nào đó có bao nhiêu loại bệnh thì chả có gì khó, thậm chí có thể liệt kê số người có từ 2 loại bện trở lên là bao nhiêu người.
4/ Vì chưa biết được quy trình theo dõi của bạn qua bao nhiêu công đoạn nên chỉ góp ý bấy nhiêu, nếu File có tiêu đề và dữ liệu thật và bạn quy trình, công đoạn thì có thể phát sinh thêm.

Nói thêm:
- Tôi thấy hầu như mọi thành viên khi có vướng mắt việc gì thì cứ chăm bẫm hỏi vào việc đó mà không hỏi với cách theo dõi như thế thì có cách nào thiết kế lại dữ liệu để nhập liệu cho nhanh, dễ dàng. Khi cần thì chạy ra cái mẫu báo cáo theo quy định.
- Nói chung cái này là bệnh của những người làm trong nhà nước thường mắc phải, cứ theo dõi rập khuông theo quy định mà không nghĩ đến cách xây dựng CSDL đến khi cần báo cáo khác đi một tí thì rối như tơ vò.

 

[SA_DQ]

Bác thông cảm; Đa số do xuất fát điểm thiếu kiến thức tối thiểu về CSDL & xây dựng nó sao cho trường tồn.

Mà cũng tại ngành GD của ta không quan tâm truyền đạt kiến thức này cho HS & đại đa số SV; Chú tâm của họ đào tạo không fải để SV ra làm việc. Đào tạo họ có kiến thức fù hợp với iêu cầu của tấm bằng sẽ fát mà thôi!

 

[be_09]

Bác thông cảm; Đa số do xuất fát điểm thiếu kiến thức tối thiểu về CSDL & xây dựng nó sao cho trường tồn.

Mà cũng tại ngành GD của ta không quan tâm truyền đạt kiến thức này cho HS & đại đa số SV; Chú tâm của họ đào tạo không fải để SV ra làm việc. Đào tạo họ có kiến thức fù hợp với iêu cầu của tấm bằng sẽ fát mà thôi!

Đào tạo của ta hiện nay mang tính thi đua thành tích là chủ yếu (không có học sinh ở lại lớp) nên có một số trường hợp học đến lớp 7, 8 mà đọc chữ không trôi. Nó chỉ có cái vỏ bọc, cái vỏ ngoài thì kêu, oách lắm nhưng trong ruột lại trống rỗng, cách dạy phi thực tế, khi học sinh nhận tấm bằng tốt nghiệp xong vào thực tế thì lóng ngóng chẳng biết làm cái gì và phải được hướng dẫn lại từ đầu.

 

[Hau151978]

Nếu csdl có 100 cột thì không ai nhập bằng tay vào đâu bác @be09 ơi. Đây thường là file xuất ra từ phần mềm, định dạng theo yêu cầu báo cáo, chủ thớt lấy để tổng hợp theo mục đích riêng.
Ban @gttrongvn code hình như chưa đúng (mình chưa chạy thử). Ví dụ dữ liệu có 2 dòng giống nhau mã khách hàng x loại bệnh 2 thì a=2>1, b=4>a. Mà loại bệnh có thể không phải là số nên chưa chắc sumifs được. Nếu cần có thể so sánh a= countif theo mã khách hàng và b=countifs theo mã khách hàng với loại bệnh, nếu a>b là có >= 2 loại bệnh.
Hôm qua mình dùng mảng gán vào sheet (trường hợp dữ liệu của mình không lớn lắm có hơn 40 cột x 5000 dòng) mà bị lỗi runtime 1004, chỉ gán được hơn 100 dòng, những dòng sau bị cắt dù mảng lưu được cả 5000 dòng x 40 cột. Mình đã thử chỉ đọc range vào mảng, chưa xử lý gì copy luôn vào sheet khác vẫn bị vậy. Tìm Google có nói khi chiều dài chuỗi trong ô >911 sẽ gây lỗi đó nhưng trường hợp của mình độ dài không quá 40 ký tự. Vì vậy dùng mảng chưa chắc ra kết quả chính xác đâu.

 

[gttrongvn]

Ban @gttrongvn code hình như chưa đúng (mình chưa chạy thử). Ví dụ dữ liệu có 2 dòng giống nhau mã khách hàng x loại bệnh 2 thì a=2>1, b=4>a. Mà loại bệnh có thể không phải là số nên chưa chắc sumifs được. Nếu cần có thể so sánh a= countif theo mã khách hàng và b=countifs theo mã khách hàng với loại bệnh, nếu a>b là có >= 2 loại bệnh.

Mình chạy thử rồi, nếu dựa vào số liệu của chủ thớt thì ra, mình lợi dụng hàm sẵn có trong excel với loại bệnh là số mà chủ thớt đưa ra để thỏa điều kiện này "1 khách hàng có từ 2 loại bệnh trở lên( 2,3,4,5 loại bệnh. Có tất cả 5 loại bệnh. (1-2-3-4-5). ".Nếu loại bệnh là chữ thì mình sẽ tìm phương án khác.
a=CountIf để đếm số lần loại bệnh, để biết số lần khách hàng khám bệnh, nếu lớn hơn 1 thì tính tiếp coi số lần khám đó có loại bệnh nào lớn hơn 1 không.
b= sumifs để đếm tổng của loại bệnh, nếu b>a thì có 1 loại bệnh lớn hơn 1=> khách hàng có từ 2 loại bệnh trở lên

Về vấn đề " dữ liệu có 2 dòng giống nhau mã khách hàng x loại bệnh 2 thì a=2>1, b=4>a ", để mình xem lại hướng xử lý. Hình như chỉ có duy nhất trường hợp này xảy ra lỗi phải không bạn?

 

[be_09]

Nếu csdl có 100 cột thì không ai nhập bằng tay vào đâu bác @be09 ơi. Đây thường là file xuất ra từ phần mềm, định dạng theo yêu cầu báo cáo, chủ thớt lấy để tổng hợp theo mục đích riêng.
Ban @gttrongvn code hình như chưa đúng (mình chưa chạy thử). Ví dụ dữ liệu có 2 dòng giống nhau mã khách hàng x loại bệnh 2 thì a=2>1, b=4>a. Mà loại bệnh có thể không phải là số nên chưa chắc sumifs được. Nếu cần có thể so sánh a= countif theo mã khách hàng và b=countifs theo mã khách hàng với loại bệnh, nếu a>b là có >= 2 loại bệnh.
Hôm qua mình dùng mảng gán vào sheet (trường hợp dữ liệu của mình không lớn lắm có hơn 40 cột x 5000 dòng) mà bị lỗi runtime 1004, chỉ gán được hơn 100 dòng, những dòng sau bị cắt dù mảng lưu được cả 5000 dòng x 40 cột. Mình đã thử chỉ đọc range vào mảng, chưa xử lý gì copy luôn vào sheet khác vẫn bị vậy. Tìm Google có nói khi chiều dài chuỗi trong ô >911 sẽ gây lỗi đó nhưng trường hợp của mình độ dài không quá 40 ký tự. Vì vậy dùng mảng chưa chắc ra kết quả chính xác đâu.

Tôi dám chắc là nhập tay vì mấy vấn đề sau:
1/ Phần mềm xuất ra tiêu đề không có vụ tiếng Việt có dấu.
2/ Hầu hết những người viết phần mềm là cử nhân tin học họ học rất bài bản nên họ có cách để xây dựng dữ liệu ngắn gọn, súc tích chứ không có cái vụ tiêu đề dài lê thê.
3/ Những ý kiến tôi nêu lên cũng là học lóm từ họ rồi từ mày mò thêm để làm theo cách của mình, tôi đãm bảo nếu có File với tiêu đề thực tế đang theo dõi tôi thiết kế lại cao lắm chỉ còn chưa đến 30 cột.

 

[Hau151978]

Tôi dám chắc là nhập tay vì mấy vấn đề sau:
1/ Phần mềm xuất ra tiêu đề không có vụ tiếng Việt có dấu.
2/ Hầu hết những người viết phần mềm là cử nhân tin học họ học rất bài bản nên họ có cách để xây dựng dữ liệu ngắn gọn, súc tích cứ không có cái vụ tiêu đề dài lê thê.
3/ Những ý kiến tôi nêu lên cũng là học lóm từ họ rồi từ mày mò thêm để làm theo cách của mình, tôi đãm bảo nếu có File với tiêu đề thực tế đang theo dõi tôi thiết kế lại cao lắm chỉ còn chưa đến 30 cột.

Bác ơi đây là dữ liệu giả định thôi. Chẳng hạn như em làm bhyt thường xuyên trên 40 cột. Mặc dù có những cột có thể suy ra từ cột khác nhưng phần mềm phải xuất ra đủ cột theo báo cáo

Bài đã được tự động gộp: 25/7/18

Mình chạy thử rồi, nếu dựa vào số liệu của chủ thớt thì ra, mình lợi dụng hàm sẵn có trong excel với loại bệnh là số mà chủ thớt đưa ra để thỏa điều kiện này "1 khách hàng có từ 2 loại bệnh trở lên( 2,3,4,5 loại bệnh. Có tất cả 5 loại bệnh. (1-2-3-4-5). ".Nếu loại bệnh là chữ thì mình sẽ tìm phương án khác.
a=CountIf để đếm số lần loại bệnh, để biết số lần khách hàng khám bệnh, nếu lớn hơn 1 thì tính tiếp coi số lần khám đó có loại bệnh nào lớn hơn 1 không.
b= sumifs để đếm tổng của loại bệnh, nếu b>a thì có 1 loại bệnh lớn hơn 1=> khách hàng có từ 2 loại bệnh trở lên

Về vấn đề " dữ liệu có 2 dòng giống nhau mã khách hàng x loại bệnh 2 thì a=2>1, b=4>a ", để mình xem lại hướng xử lý. Hình như chỉ có duy nhất trường hợp này xảy ra lỗi phải không bạn?

Có nhiều dòng giống nhau đều có khả năng xảy ra lỗi mà bạn.

 

[be_09]

Bác ơi đây là dữ liệu giả định thôi. Chẳng hạn như em làm bhyt thường xuyên trên 40 cột. Mặc dù có những cột có thể suy ra từ cột khác nhưng phần mềm phải xuất ra đủ cột theo báo cáo

Vậy thì đưa phải cái File từ phần mềm xuất ra với tiêu đề đầy đủ thì mới hình dung được cấu trúc của nó rồi mới tính đến việc khác, còn đưa tiêu đề nữa chừng là thua, không ai hình dung được những cột còn thiếu là cái gì?

 

[anh1234]

cứ chăm bẫm hỏi và

Tôi dám chắc là nhập tay vì mấy vấn đề sau:
1/ Phần mềm xuất ra tiêu đề không có vụ tiếng Việt có dấu.
2/ Hầu hết những người viết phần mềm là cử nhân tin học họ học rất bài bản nên họ có cách để xây dựng dữ liệu ngắn gọn, súc tích chứ không có cái vụ tiêu đề dài lê thê.
3/ Những ý kiến tôi nêu lên cũng là học lóm từ họ rồi từ mày mò thêm để làm theo cách của mình, tôi đãm bảo nếu có File với tiêu đề thực tế đang theo dõi tôi thiết kế lại cao lắm chỉ còn chưa đến 30 cột.

>>>>
Cám ơn bác đã quan tâm.
File mẫu em đưa lên là dữ liệu giả.Đúng như bác
Hau151978 đã nói data của em là hệ thống xuất ra. Cũng chính vì số lượng cột và dòng của nó rất nhiều lên khi thực hiện rất hay bị lỗi. Do đó em đưa lên đây để mong các cao thủ góp ý và giúp em ạ.

 

[be_09]

>>>>
Cám ơn bác đã quan tâm.
File mẫu em đưa lên là dữ liệu giả.Đúng như bác
Hau151978 đã nói data của em là hệ thống xuất ra. Cũng chính vì số lượng cột và dòng của nó rất nhiều lên khi thực hiện rất hay bị lỗi. Do đó em đưa lên đây để mong các cao thủ góp ý và giúp em ạ.

Thì bài 18 tôi nêu vầy:
Vậy thì đưa phải cái File từ phần mềm xuất ra với tiêu đề đầy đủ thì mới hình dung được cấu trúc của nó rồi mới tính đến việc khác, còn đưa tiêu đề nữa chừng là thua, không ai hình dung được những cột còn thiếu nó là cái gì?