Tìm Hàm Lọc Duy Nhất (1 người xem)

[hophoang]

Xin chào cả nhà
Lâu quá mới thăm cả nhà.Chúc cả nhà vui vẻ và thành đạt.
Và cũng lâu lắm em cũng không đụng tới excell nên quên hết rồi.
Em có học được một hàm lọc duy nhất trên diễn đàn,hàm này lọc vài chục dòng thì đúng và nếu lọc lên đến vài trăm dòng thì lọc không hết.
Mong anh chị chỉ cho.Em cám ơn!

 

[befaint]

một hàm lọc duy nhất trên diễn đàn

Trích dẫn từ một bài trên diễn đàn:
"Công thức MẢNG loại trùng cách thường được sử dụng là:
=INDEX("Vùng dữ liệu cần lọc", MATCH(0,COUNTIF( "Vùng động chứa dữ liệu đã lọc trùng", "Vùng dữ liệu cần lọc") ,0)) "

C2=INDEX($B$2:$B$480,MATCH(0,COUNTIF($C$1:C1,$B$2:$B$480),0))
'Ctrl + Shift + Enter

 

[vanthinh3101]

Xin chào cả nhà
Lâu quá mới thăm cả nhà.Chúc cả nhà vui vẻ và thành đạt.
Và cũng lâu lắm em cũng không đụng tới excell nên quên hết rồi.
Em có học được một hàm lọc duy nhất trên diễn đàn,hàm này lọc vài chục dòng thì đúng và nếu lọc lên đến vài trăm dòng thì lọc không hết.
Mong anh chị chỉ cho.Em cám ơn!

Em thấy kết quả đúng mà.
em dùng công thức này:

C2=IFERROR(INDEX($B$2:$B$480,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$480),0)),"")

Ctrl+Shift+Enter

 

[quocgiacan]

Xin chào cả nhà
Lâu quá mới thăm cả nhà.Chúc cả nhà vui vẻ và thành đạt.
Và cũng lâu lắm em cũng không đụng tới excell nên quên hết rồi.
Em có học được một hàm lọc duy nhất trên diễn đàn,hàm này lọc vài chục dòng thì đúng và nếu lọc lên đến vài trăm dòng thì lọc không hết.
Mong anh chị chỉ cho.Em cám ơn!

Công thức trong file bạn gửi (không cần nhấn Ctrl+Shift+Enter vì đã sử dụng Index(,) trong hàm Match() rồi) hoàn toàn chung ý với hai bạn @befaint và @vanthinh3101
1. Tuy nhiên, để hoàn chỉnh bạn phải thêm trong điều kiện lọc đoạn công thức: ($B$2:$B$480=0) để loại bỏ các giá trị rỗng nằm lẫn lộn trong Vùng B2:B480, cụ thể:

=IFERROR(INDEX($B$2:$B$480,MATCH(0, INDEX( COUNTIF($C$1:C1,$B$2:$B$480)+($B$2:$B$480=0) ,),0)),"") chỉ nhấn Enter khi kết thúc.
Nếu bỏ Index(...,) (màu nâu) thì sẽ giống công thức của hai bạn trên, tức đồng nghĩa bạn phải nhấn Ctrl+Shift+Enter.​

2. Góp vui thêm cho bạn vài cách lọc "khử trùng":

• Giữ nguyên thứ tự phát sinh từ trên xuống dưới của cột cần lấy dữ liệu, kết quả trả về giống kết quả từ công thức bạn đang sử dụng:
  • =OFFSET($C$1,AGGREGATE(15,6,ROW($1:$500)/(COUNTIFS(OFFSET($C$2,,,ROW($1:$500)),$C$2:$C$502)=1),ROW(A1)),) chỉ nhấn Enter khi kết thúc
• Loại trùng đồng thời sắp xếp từ nhỏ đến lớn:
  • Không dùng cột phụ:
    • =AGGREGATE(15,6,$C$2:$C$502/(COUNTIFS(OFFSET($C$2,,,ROW($1:$500)),$C$2:$C$502)=1),ROW(A1)) chỉ nhấn Enter khi kết thúc
  • Dùng cột phụ:
    • Cột phụ: B2=COUNTIF($C$2:$C2,$C2) chỉ nhấn Enter khi kết thúc.
    • Kết quả: D2=AGGREGATE(15,6,$C$2:$C$480/($B$2:$B$480=1),ROW(A1)) chỉ nhấn Enter khi kết thúc.

Bạn nên nhớ rằng dùng công thức Mảng lọc khử trùng sẽ ảnh hưởng đến tốc độ xử lý của máy tính, do vậy nếu dùng <1000 còn gắng gượng, ngược lại sẽ có hiện tượng 'đơ máy' hoặc thậm chí treo máy.

Trong trường hợp dữ liệu nhiều nên dùng VBA để khử trùng.

Chúc anh em ngày thiệt vui.

 

[dhn46]

Nếu không bắt buộc phải sử dụng công thức thì bạn dùng Data|Remove Duplicate hay Pivot Table để lấy danh sách duy nhất vừa gọn nhẹ lại hiệu quả.

 

[SA_DQ]

Bài này chắc có nhiều cách làm;
Ngay cả việc áp dụng fương thức AdvancedFilter đơn giản cũng có thể hoàn thành nhiệm vụ này mà!

 

[hophoang]

Công thức trong file bạn gửi (không cần nhấn Ctrl+Shift+Enter vì đã sử dụng Index(,) trong hàm Match() rồi) hoàn toàn chung ý với hai bạn @befaint và @vanthinh3101
1. Tuy nhiên, để hoàn chỉnh bạn phải thêm trong điều kiện lọc đoạn công thức: ($B$2:$B$480=0) để loại bỏ các giá trị rỗng nằm lẫn lộn trong Vùng B2:B480, cụ thể:

=IFERROR(INDEX($B$2:$B$480,MATCH(0, INDEX( COUNTIF($C$1:C1,$B$2:$B$480)+($B$2:$B$480=0) ,),0)),"") chỉ nhấn Enter khi kết thúc.
Nếu bỏ Index(...,) (màu nâu) thì sẽ giống công thức của hai bạn trên, tức đồng nghĩa bạn phải nhấn Ctrl+Shift+Enter.​

2. Góp vui thêm cho bạn vài cách lọc "khử trùng":

• Giữ nguyên thứ tự phát sinh từ trên xuống dưới của cột cần lấy dữ liệu, kết quả trả về giống kết quả từ công thức bạn đang sử dụng:
  • =OFFSET($C$1,AGGREGATE(15,6,ROW($1:$500)/(COUNTIFS(OFFSET($C$2,,,ROW($1:$500)),$C$2:$C$502)=1),ROW(A1)),) chỉ nhấn Enter khi kết thúc
• Loại trùng đồng thời sắp xếp từ nhỏ đến lớn:
  • Không dùng cột phụ:
    • =AGGREGATE(15,6,$C$2:$C$502/(COUNTIFS(OFFSET($C$2,,,ROW($1:$500)),$C$2:$C$502)=1),ROW(A1)) chỉ nhấn Enter khi kết thúc
  • Dùng cột phụ:
    • Cột phụ: B2=COUNTIF($C$2:$C2,$C2) chỉ nhấn Enter khi kết thúc.
    • Kết quả: D2=AGGREGATE(15,6,$C$2:$C$480/($B$2:$B$480=1),ROW(A1)) chỉ nhấn Enter khi kết thúc.

Bạn nên nhớ rằng dùng công thức Mảng lọc khử trùng sẽ ảnh hưởng đến tốc độ xử lý của máy tính, do vậy nếu dùng <1000 còn gắng gượng, ngược lại sẽ có hiện tượng 'đơ máy' hoặc thậm chí treo máy.

Trong trường hợp dữ liệu nhiều nên dùng VBA để khử trùng.

Chúc anh em ngày thiệt vui.

Vậy bạn cho mình cái VBA đó đi. Mỗi tháng file mình trên 3 nghìn dòng
Cám ơn bạn

 

[Ba Tê]

Vậy bạn cho mình cái VBA đó đi. Mỗi tháng file mình trên 3 nghìn dòng
Cám ơn bạn

Bạn đưa file cụ thể lên đi, yêu cầu làm gì, số lượng "tiêu biểu" vài trăm dòng được rồi.

 

[hophoang]

Bạn đưa file cụ thể lên đi, yêu cầu làm gì, số lượng "tiêu biểu" vài trăm dòng được rồi.

Như bài 1 lọc duy nhất đó thầy

 

[Ba Tê]

Như bài 1 lọc duy nhất đó thầy

Bạn làm thủ công với Remove Duplicates cũng được mà.
Muốn VBA thì Record Macro lại:

Public Sub LocDuyNhat()
Dim Rng As Range
Set Rng = Range("B2", Range("B60000").End(xlUp))
With Range("E2").Resize(Rng.Rows.Count)
    .Value = Rng.Value
    .RemoveDuplicates Columns:=1, Header:=xlNo
    .Sort Key1:=Range("E2"), Order1:=xlAscending
End With
Set Rng = Nothing
End Sub

 

[phulien1902]

Vậy bạn cho mình cái VBA đó đi. Mỗi tháng file mình trên 3 nghìn dòng
Cám ơn bạn

1 cách bạn tham khảo:

Sub abc()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To Range("B" & Rows.Count).End(3).Row
        If Application.WorksheetFunction.CountIf(Range("B2:B" & i), Range("B" & i)) > 1 Then Range("B" & i).ClearContents
    Next
    Columns(2).SpecialCells(4).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[quocgiacan]

Vậy bạn cho mình cái VBA đó đi. Mỗi tháng file mình trên 3 nghìn dòng
Cám ơn bạn

Bạn xem các bài trên của anh @Ba Tê , và anh @phulien1902 .

Có thể tham khảo thêm cách hướng dẫn tường tận các dòng lệnh trong loạt bài của anh @befaint, cụ thể cho bài này theo link: http://www.giaiphapexcel.com/diendan/threads/bài-11-dictionary.130130/#post-816989.

Ngoài ra, còn có các bài khác về VBA nữa.

Chúc bạn ngày vui.

 

[Hai Lúa Miền Tây]

Vậy bạn cho mình cái VBA đó đi. Mỗi tháng file mình trên 3 nghìn dòng
Cám ơn bạn

Thêm 1 cách cho bạn.

Sub LocDuyNhat()
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No"";")
    Range("D2").CopyFromRecordset cn.Execute("Select distinct F1 from [Sheet2$B2:B] Where F1 Is not null")
    
End Sub

 

[hophoang]

Thêm 1 cách cho bạn.

Sub LocDuyNhat()
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No"";")
    Range("D2").CopyFromRecordset cn.Execute("Select distinct F1 from [Sheet2$B2:B] Where F1 Is not null")
   
End Sub

Nhìn vào VBA mình không biết gì luôn bạn ơi
Công thức thì máy chạy với tốc độ rất chậm
Vậy bạn cho cái VBAnày vào giùm mình nhé.Cám Ơn

 

[Ba Tê]

Nhìn vào VBA mình không biết gì luôn bạn ơi
Công thức thì máy chạy với tốc độ rất chậm
Vậy bạn cho cái VBAnày vào giùm mình nhé.Cám Ơn

Chỉ lấy dữ liệu duy nhất của cột B? Các cột khác không lấy?
Trong cột B có Text (Tâm, VŨ...) và có Number (4427,4431...) thì sao, Sort kiểu gì?

 

[hophoang]

Chỉ lấy dữ liệu duy nhất của cột B? Các cột khác không lấy?
Trong cột B có Text (Tâm, VŨ...) và có Number (4427,4431...) thì sao, Sort kiểu gì?

Chỉ lấy dữ liệu ở cột B các cột khác không lấy và gặp text thì bỏ không lấy

Cám Ơn

 

[befaint]

Chỉ lấy dữ liệu ở cột B các cột khác không lấy và gặp text thì bỏ không lấy

 

[Hai Lúa Miền Tây]

Chỉ lấy dữ liệu duy nhất của cột B? Các cột khác không lấy?
Trong cột B có Text (Tâm, VŨ...) và có Number (4427,4431...) thì sao, Sort kiểu gì?

@befaint
Dùng ADO sẽ gọn hơn rất nhiều so với cách lọc và sort bình thường

 

[Hai Lúa Miền Tây]

Chắc là là như vậy. Code của anh HLMT thêm order by

Sub LocDuyNhat()
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No"";")
    Range("D3").CopyFromRecordset cn.Execute("Select distinct F1 from [NhatKy$B6:B] Where IsNumeric(f1) order by f1")
 
End Sub

Chính xác là như thế, nhưng không cần order by nhé. Lỗi đó, vì bản thân của nó đã sort rồi.

 

[Ba Tê]

@befaint
Dùng ADO sẽ gọn hơn rất nhiều so với cách lọc và sort bình thường

Tui thì không biết ADO, với lọc và Sort Number cũng làm "thí thí" như vầy: