Lọc duy nhất theo điều kiện, đồng thời sắp xếp tăng dần (không dùng cột phụ) (7 người xem)

[LinDan]

Bài toán của em dữ liệu thật có khoảng 400-500 dòng, yêu cầu bài toán:

- Trích lọc duy nhất (loại bỏ ô trống và số 0);
- Đồng thời với nó là sắp xếp dữ liệu theo thứ tự tăng dần

Nhờ các bác giúp đỡ cho.

 

[Hoàng Trọng Nghĩa]

Dùng ADO đi, khi lọc sẽ tự sắp xếp tăng dần!

Tham khảo tại bài 113:

http://www.giaiphapexcel.com/forum/showthread.php?75143-Bài-tập-về-ADO-căn-bản&p=474034#post474034

 

[Hoàng Trọng Nghĩa]

Theo như yêu cầu của bài, là lọc duy nhất, loại bỏ giá trị rỗng, loại bỏ giá trị 0, sắp xếp tăng dần.

Tương tự ở bài 113 như tôi đã nói ở bài trước, cấu trúc chỉ thay đổi tí xíu:

        sSQL = "SELECT DISTINCT" & FieldName & "FROM" & SheetName [COLOR=#ff0000]& _
               "WHERE" & FieldName & "<> NULL AND" & FieldName & "<>0"
[/COLOR]

Thủ tục bây giờ sẽ như sau:

Sub LocDuyNhat()

    Dim FileName As String, SheetName As String, FieldName As String, _
        AppPath As String, sSQL As String, ObjRcs As Object
    
    AppPath = ThisWorkbook.Path
    FileName = "LocDuyNhat.xlsm"
    SheetName = " [Sheet1$] "
    FieldName = " [Title] "
    
    If ExcelConnect(AppPath, FileName) = False Then
        MsgBox "Không kêt nôi", vbOKOnly + vbExclamation, "Thông báo"
        GoTo ExitSub
    Else
        sSQL = "SELECT DISTINCT" & FieldName & "FROM" & SheetName & _
               "WHERE" & FieldName & "<> NULL AND" & FieldName & "<>0"
        
        Set ObjRcs = CreateObject("ADODB.Recordset")
        
        On Error GoTo SheetFieldNameErr
        
        ObjConnect.Open
        
        ObjRcs.Open sSQL, ObjConnect, 0, 1, 1
        
        If ObjRcs.EOF Then
            MsgBox "Không có dieu kien này", vbOKOnly + vbInformation, "THÔNG BÁO"
            GoTo ExitSub
        Else
            Sheet1.Range("C2:C1048576").ClearContents
            Sheet1.Range("C2").CopyFromRecordset ObjRcs
        End If
    End If
      
ExitSub:
      
    Set ObjRcs = Nothing
    
    If Not ObjConnect Is Nothing Then
        If (ObjConnect.State And adStateOpen) = adStateOpen Then ObjConnect.Close
        Set ObjConnect = Nothing
    End If
    Exit Sub
    
SheetFieldNameErr:
    
    MsgBox "Ten Sheet hoac ten Tieu de cot chua dung, xin kiem tra lai!", vbCritical, "THÔNG BÁO"
    Resume ExitSub
End Sub

 

[bimbim1412]

Tham khảo bài tương tự nhé

Bạn tham khảo bài tương tự như ở dưới xem có đúng không nhé

 

[Hoàng Trọng Nghĩa]

Cũng nói thêm, nếu bạn muốn sắp xếp ngược lại thì câu lệnh SQL sẽ thêm ORDER BY (ASC|DESC) nha bạn:

        sSQL = "SELECT DISTINCT" & FieldName & _
               "FROM" & SheetName & _
               "WHERE" & FieldName & "<> NULL AND" & FieldName & "<>[COLOR=#0000cd]0 " & _[/COLOR]
               [COLOR=#0000cd]"ORDER BY" & FieldName & "DESC"[/COLOR]

Mặc định của nó là sắp xếp tăng dần nên không cần thêm ASC đâu.

 

[ndu96081631]

Dùng ADO đi, khi lọc sẽ tự sắp xếp tăng dần!

Tham khảo tại bài 113:

http://www.giaiphapexcel.com/forum/showthread.php?75143-Bài-tập-về-ADO-căn-bản&p=474034#post474034

Có lẽ chúng ta phải xem lại code về ADO sao cho code viết ở mức tổng quát
Bảng dữ liệu mà tôi thường thấy nhất nó có kiểu thế này:









































Tức 5, 6 dòng trên cùng người ta chừa ra để ghi gì đó (tên cty, tiêu đề....) và bên dưới mới là CSDL
Trường hợp này bắt buộc ta phải chỉ chính xác vùng dữ liệu mà ta cần lọc (chứ đâu phải lọc nguyên sheet). Vậy trường hợp này ta tính sao?
Code tổng quát tôi nghĩ phải được viết theo dạng:
Sub/Function Tên Sub/Function(vùng dữ liệu, tiêu đề cần lọc....)
Ít nhất phải thế
Nếu không thì chỉ còn cách:
- Copy vùng dữ liệu ra 1 sheet mới (khi ấy UsedRange của sheet mới chính bằng vùng dữ liệu vừa paste)
- Xong, dùng ADO để lọc (khi ấy ta không cần quan tâm đến vùng dữ liệu nằm ở đâu)
Ẹc... Ẹc... rườm rà quá!

 

[LinDan]

Bạn tham khảo bài tương tự như ở dưới xem có đúng không nhé

Cách này chưa chuẩn bác ah, bác xem lại dùm em nhé.

Em đọc bài giải VBA như của các thày trên diễn đàn, em có hiểu cách làm. Nhưng quả là không thể nào nghĩ ra cách giải bằng công thức thông thường như thế nào (em định dùng hàm Index nhưng hàm này chỉ cho kết quả duy nhất, không cho dưới dạng mảng). Em có cảm giác thuật toán của bài này nếu giải bằng công thức có khi còn khó hơn VBA?

Xin Trân trọng nhờ mọi người chỉ cho em cách giải bằng công thức (không dùng VBA, không dùng Sort bằng công cụ có sẵn trong Excel). Bởi em đang cần luyện thêm về phần công thức, mặt khác với dữ liệu hằng ngày em xử lý thông thường chỉ khoảng 200 dòng thì có lẽ cách này tỏ ra phù hợp hơn nhât.

 

[ndu96081631]

Cách này chưa chuẩn bác ah.

Em đọc bài giải VBA như của các thày trên diễn đàn, em có hiểu cách làm. Nhưng quả là không thể nào nghĩ ra cách giải bằng công thức thông thường như thế nào (em định dùng hàm Index nhưng hàm này chỉ cho kết quả duy nhất, không cho dưới dạng mảng). Em có cảm giác thuật toán của bài này nếu giải bằng công thức có khi còn khó hơn VBA?

Xin Trân trọng nhờ mọi người chỉ cho em cách giải bằng công thức (không dùng VBA, không dùng Sort bằng công cụ có sẵn trong Excel). Bởi em đang cần luyện thêm về phần công thức, mặt khắc với dữ liệu hằng ngày em xử lý thông thường chỉ khoảng 200 dòng thì có lẽ cách này tỏ ra phù hợp hơn nhât.

Unique bằng công thức đã khó, Unique and sort lại càng khó hơn (chứ không phải không làm được)
Xin hỏi bạn: Dữ liệu mà bạn cần lọc thuộc dạng number hay text?

 

[ptm0412]

Trường hợp này bắt buộc ta phải chỉ chính xác vùng dữ liệu mà ta cần lọc (chứ đâu phải lọc nguyên sheet). Vậy trường hợp này ta tính sao?

ADO có thể dùng cho name range mà

 

[ndu96081631]

ADO có thể dùng cho name range mà

Dạ em biết. Ác cái nếu range vượt quá 65536 dòng thì code báo lỗi (dù file là xlsm đàng hoàng)
Sư phụ xem cái này sẽ biết:
http://www.giaiphapexcel.com/forum/showthread.php?75143-Bài-tập-về-ADO-căn-bản/page12

 

[VMH0307]

Xin Trân trọng nhờ mọi người chỉ cho em cách giải bằng công thức (không dùng VBA, không dùng Sort bằng công cụ có sẵn trong Excel). Bởi em đang cần luyện thêm về phần công thức, mặt khắc với dữ liệu hằng ngày em xử lý thông thường chỉ khoảng 200 dòng thì có lẽ cách này tỏ ra phù hợp hơn nhât.

Nếu dữ liệu của bạn là số, giống như trong file ví dụ của bạn thì dùng công thức có thể như thế này. Đặt các Name sau cho tiện:

Data=Sheet1!$A$2:$A$13

Ma=SMALL(IF(Data=0,"",Data),ROW(INDIRECT("1:"&COUNTIF(Data,">0"))))

Ví dụ ở ô D2 cho công thức trả kết quả như sau:

=INDEX(Ma,MATCH(0,COUNTIF($D$1:D1,Ma),0))

và fill xuống. Bạn xem thêm theo file đính kèm.

 

[ptm0412]

Dạ em biết. Ác cái nếu range vượt quá 65536 dòng thì code báo lỗi (dù file là xlsm đàng hoàng)
Sư phụ xem cái này sẽ biết:
http://www.giaiphapexcel.com/forum/showthread.php?75143-B%C3%A0i-t%E1%BA%ADp-v%E1%BB%81-ADO-c%C4%83n-b%E1%BA%A3n/page12

Hm, hôm nay mới test thử trên 65536 dòng, bị lỗi mà không biết nguyên nhân.

 

[concogia]

Em đọc bài giải VBA như của các thày trên diễn đàn, em có hiểu cách làm. Nhưng quả là không thể nào nghĩ ra cách giải bằng công thức thông thường như thế nào (em định dùng hàm Index nhưng hàm này chỉ cho kết quả duy nhất, không cho dưới dạng mảng). Em có cảm giác thuật toán của bài này nếu giải bằng công thức có khi còn khó hơn VBA?

Xin Trân trọng nhờ mọi người chỉ cho em cách giải bằng công thức (không dùng VBA, không dùng Sort bằng công cụ có sẵn trong Excel). Bởi em đang cần luyện thêm về phần công thức, mặt khắc với dữ liệu hằng ngày em xử lý thông thường chỉ khoảng 200 dòng thì có lẽ cách này tỏ ra phù hợp hơn nhât.

Một cách dùng công thức theo kiểu dữ liệu trong bài
Công thưc ở [C2]:

=IF(C12="","",IF(C12=MAX($A$2:$A$13),"",SMALL($A$2:$A$13,COUNTIF($A$2:$A$13,"<="&N(C12))+1)))

Thân

 

[LinDan]

Unique bằng công thức đã khó, Unique and sort lại càng khó hơn (chứ không phải không làm được)
Xin hỏi bạn: Dữ liệu mà bạn cần lọc thuộc dạng number hay text?

Cảm ơn sư phụ đã quan tâm, dữ liệu của em hoàn toàn là số cả, bởi đây là bài toán thực tế em hay gặp nên dù dữ liệu giả định, bao giờ em cũng cố gắng post đầu bài sát với thực tế nhất.

Hic, nếu bài này dữ liệu mà dạng Text thì chỉ có hướng sử dụng bộ lọc Filter hoặc VBA chứ công thức nếu có làm được chắc là rất phức tạp. Tuy nhiên, nếu có thể làm được trong trường hợp này em rất mong thày chỉ giúp em bài này (có thể thày không giải ra kết quả ngay, chỉ gợi ý các các bước cơ bản để em có thể mày mò tự làm, bởi cái mà em cần đối với dạng dữ liệu này chỉ là phương pháp thôi).

Một cách dùng công thức theo kiểu dữ liệu trong bài
Công thưc ở [C2]:

=IF(C12="","",IF(C12=MAX($A$2:$A$13),"",SMALL($A$2  :$A$13,COUNTIF($A$2:$A$13,"<="&N(C12))+1)))

Thân

cái hay trong công thức này là việc thày sử dụng hàm N để chuyển Text --> 0 (chắc hàm này rất ít ai để ý), Countif để loại bỏ dữ liệu trùng. Đúng là em hôm nay em học cách mới mà trước kia chưa từng nghĩ ra được ý tưởng như vậy.
------
Thời gian qua được các thày, các anh chị trên diễn đàn quan tâm, giúp đỡ; gần đây cứ rỗi công việc em lại Test về công thức mảng. Đến nay, kiến thức của em về phần này đã tốt hơn nhiều so với trước kia mặc dù chưa đạt yêu cầu như mong muốn, em sẽ cố gắng luyện thêm khoảng 1, 2 tháng nữa trước khi bước sang nghiên cứu về VBA.

Một lần nữa, em xin chân thành cảm ơn tất cả mọi người, chúc các thành viên trong đại gia đình GPE đón năm mới an khang, thịnh vượng.

 

[bimbim1412]

Cách này chưa chuẩn bác ah.

Xin lỗi, vì hôm nọ Test không kỹ nên em gửi nhầm, em đã thay thế bằng file chuẩn (đính kèm tại bài viết số 4).

 

[quykiemsau]

Hỏi về Hàm lọc những giá trị trùng nhau và sắp xếp thứ tự?

Xin chào GPE!

E có một bài toàn trong file kèm mong GPE xem và giúp Em với ạ.
Xin cảm ơn!

 

[bebo021999]

Xem file đính kèm. Nhấn Ctrl-F3 xem Name mình đặt nhé.
P/S: mình muốn add nick mình cho thành 11 người cám ơn bạn mà không được.

 

[ndu96081631]

Xin chào GPE!

E có một bài toàn trong file kèm mong GPE xem và giúp Em với ạ.
Xin cảm ơn!

Bài toán này nếu dùng công thức cũng không phải là không làm được (GPE cũng đã từng có bài dạng này). Tuy nhiên khuyên bạn đừng nghĩ đến, vì lý do:
- Công thức trích lọc duy nhất là 1 công thức thuộc dạng... KHỦNG
- Cộng thêm phần Sort sẽ làm cho công thức mảng nặng thêm
- Dữ liệu giả lập của bạn có đúng với dữ liệu thật chưa? Tức luôn là Number hay còn dạng khác ---> Nếu phải sort thêm Text lại càng mệt
-------------
Dùng công cụ có sẵn là lựa chọn tốt nhất. Muốn hoành tráng hơn, có thể dùng VBA

 

[bebo021999]

Gửi các bạn file mẫu tham khảo: Sort dạng Text hay số cho mảng có 1 hay nhiều cột.

 

[MinhKhai]

Xin chào GPE!
Mong mọi người xem file kèm và giúp em với ạ!
Em xin cảm ơn!

Bạn xem file gửi kèm rồi tự làm