Các câu hỏi về lọc ra danh sách duy nhất (loại bỏ dữ liệu trùng)

vsk · 8/11/10

thật cảm ơn nhiều ....
Cái này đúng cái em đang tìm...thật excel hàm đa dạng quá kết hợp phức tạp mới ra cái mình cần.

Lê Duy Thương · 8/11/10

volga đã viết:
bạn thử tìm hiểu về advanced filter đi .
100000 dòng ,thật là kinh khủng ,công thức chắc à ì ạch ...

chỉ có excel 2007 trở lên mới có số dòng này thôi bác volga à

vsk · 9/11/10

Em áp dụng công thức trong file này cho file công việc rất tốt. chỉ là không hiểu về nó lắm.. cái này hơi chuyên sâu nhỉ?
Có thể giải thích cho em hàm indirect dùng như thế nào và trong trường hợp như thế nào mới dùng. kèm ví dụ đơn giản được không ạ? Em đọc nhiều về hàm này nhưng vẫn cảm thấy lơ tơ mơ....

Le duy Thuong đã viết:
chỉ có excel 2007 trở lên mới có số dòng này thôi bác volga à

Em sử dụng Office 2010 bác ạ....

Hai Lúa Miền Tây · 9/11/10

vsk đã viết:
Em sử dụng Office 2010 bác ạ....

Trong Office 2007 và 2010 có chức năng loại bỏ dòng trùng, còn nếu như không muốn dùng bạn có thể dùng PivotTable để thực hiện điều bạn muốn.

MinhCong · 9/11/10

vsk đã viết:
em có file dữ liệu cần nhập rất nhiều nên hơi nặng mọi người giúp em thêm hàm trong file. cám ơn mọi người.

Bài lọc dữ liệu trùng thường dùng Advanced Filter, Consolidate....để lọc. Nếu dữ liệu lớn mà dùng công thức thì e rằng file sẽ nặng và chạy không nỗi.
Thêm cho Bạn 1 cách dùng Consolidate để lọc.

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Range("[COLOR=red]B2[/COLOR]:C" & [[COLOR=red]B65536[/COLOR]].End(xlUp).Row)
     Range("[COLOR=red]F2[/COLOR]").Consolidate .Address(, , 2), Function:=xlSum, LeftColumn:=True
  End With
End Sub

vsk · 9/11/10

MinhCong đã viết:
Bài lọc dữ liệu trùng thường dùng Advanced Filter, Consolidate....để lọc. Nếu dữ liệu lớn mà dùng công thức thì e rằng file sẽ nặng và chạy không nỗi.
Thêm cho Bạn 1 cách dùng Consolidate để lọc.

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Range("[COLOR=red]B2[/COLOR]:C" & [[COLOR=red]B65536[/COLOR]].End(xlUp).Row) Range("[COLOR=red]F2[/COLOR]").Consolidate .Address(, , 2), Function:=xlSum, LeftColumn:=True End With End Sub

Theo bài này thì dữ liệu bảng tính không hiểu khi có thêm loại giày mới bác ạ?

MinhCong · 9/11/10

vsk đã viết:
Theo bài này thì dữ liệu bảng tính không hiểu khi có thêm loại giày mới bác ạ?

Bạn cứ thử sửa lại như vầy xem sao:

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("[COLOR=red]F2:G65536[/COLOR]").Clear
  With Range("[COLOR=red]B2:C[/COLOR]" & [[COLOR=red]B65536[/COLOR]].End(xlUp).Row)
     Range("[COLOR=red]F2[/COLOR]").Consolidate .Address(, , 2), Function:=xlSum, LeftColumn:=True
  End With
End Sub

Lê Duy Thương · 9/11/10

vsk đã viết:
Em áp dụng công thức trong file này cho file công việc rất tốt. chỉ là không hiểu về nó lắm.. cái này hơi chuyên sâu nhỉ?
Có thể giải thích cho em hàm indirect dùng như thế nào và trong trường hợp như thế nào mới dùng. kèm ví dụ đơn giản được không ạ? Em đọc nhiều về hàm này nhưng vẫn cảm thấy lơ tơ mơ....

Em sử dụng Office 2010 bác ạ....

nếu 2010 thì thử cái recode này xem 150000 dòng đấy

PHP:

Private Sub CommandButton1_Click()
  With Application
 .ScreenUpdating = False
  ActiveSheet.Range("e:f").Clear
  Range("B2:C" & [B150000].End(xlUp).Row + 1).Copy
[e2].PasteSpecial Paste:=xlPasteValuesAndNumberFormats: [f3].FormulaR1C1 = "=SUMIF(C[-4],RC[-1],C[-3])"
: [f3].AutoFill Destination:=Range("F3:F" & [f150000].End(xlUp)): Range("$E1:$f150000").RemoveDuplicates Columns:=1
selection.Copy
    selection.PasteSpecial Paste:=xlPasteValues
    .CutCopyMode = False
    .ScreenUpdating = True
   End With
End Sub

xem thêm file

ThuNghi · 9/11/10

MinhCong đã viết:

Bạn cứ thử sửa lại như vầy xem sao:

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("[COLOR=red]F2:G65536[/COLOR]").Clear
  With Range("[COLOR=red]B2:C[/COLOR]" & [[COLOR=red]B65536[/COLOR]].End(xlUp).Row)
     Range("[COLOR=red]F2[/COLOR]").Consolidate .Address(, , 2), Function:=xlSum, LeftColumn:=True
  End With
End Sub

Nâng lên 65000 dòng và # 1000 mã thì dùng Dictionary nhanh nhiều hơn đó. Nhanh hơn cả AdFi. Còn kg biết Ex 2010 thì thế nào.

PHP:

Sub thuDic()
Dim t, i As Long, s As Long, Dic As Object, Arr, ArrKQ(1 To 65000, 1 To 2)
Set Dic = CreateObject("Scripting.Dictionary")
t = Timer
Range("I2:J65536").Clear
Arr = Range("B2:C" & [B65536].End(xlUp).Row)
s = 0
For i = 1 To UBound(Arr, 1)
  If Not Dic.Exists(Arr(i, 1)) Then
    s = s + 1
    ArrKQ(s, 1) = Arr(i, 1)
    Dic.Add Arr(i, 1), s
  End If
  ArrKQ(Dic.Item(Arr(i, 1)), 2) = ArrKQ(Dic.Item(Arr(i, 1)), 2) + Arr(i, 2)
Next i
Range("I2").Resize(s, 2) = ArrKQ
Erase Arr, ArrKQ: Set Dic = Nothing
MsgBox Timer - t
End Sub

MinhCong · 9/11/10

Anh ThuNghi có thể giải thích từng dòng lệnh để Anh em học hỏi được không (sao thấy giống như đưa vào mảng quá) nhưng không hiểu nhiều dòng lệnh? Thấy cái CreateObject("Scripting.Dictionary")anh NDU và Anh hay dùng để lọc hay quá.

Lê Duy Thương · 9/11/10

MinhCong đã viết:
Anh ThuNghi có thể giải thích từng dòng lệnh để Anh em học hỏi được không (sao thấy giống như đưa vào mảng quá) nhưng không hiểu nhiều dòng lệnh? Thấy cái CreateObject("Scripting.Dictionary")anh NDU và Anh hay dùng để lọc hay quá.

đúng rồi Bác thu nghi hưỡng dẫn từng dòng lệnh cho anh em học với
cái code cua bác chạy có 0.203125 giây à kể cả khi em sửa range lên đến 165536 . phải nói là cực nhanh.
còn code của Minh công hết 65.58594 giây , le duythương hết -72716.66.

ndu96081631 · 10/11/10

ThuNghi đã viết:
Nâng lên 65000 dòng và # 1000 mã thì dùng Dictionary nhanh nhiều hơn đó. Nhanh hơn cả AdFi. Còn kg biết Ex 2010 thì thế nào.

Chắc chắn Dictionary luôn nhanh hơn luôn, cho dù Excel 2007 hoặc Excel 2010 có dùng Advanced Filter hay RemoveDuplicate cũng vẫn chậm
-------------------------

MinhCong đã viết:
Anh ThuNghi có thể giải thích từng dòng lệnh để Anh em học hỏi được không (sao thấy giống như đưa vào mảng quá) nhưng không hiểu nhiều dòng lệnh? Thấy cái CreateObject("Scripting.Dictionary")anh NDU và Anh hay dùng để lọc hay quá.

Le duy Thuong đã viết:
đúng rồi Bác thu nghi hưỡng dẫn từng dòng lệnh cho anh em học với
cái code cua bác chạy có 0.203125 giây à kể cả khi em sửa range lên đến 165536 . phải nói là cực nhanh.
còn code của Minh công hết 65.58594 giây , le duythương hết -72716.66.

Dictionary Object đã được nói rất nhiều lần rồi (kê cả diễn giải chi tiết)... giờ xin đưa 1 code đơn giản nhất cho các bạn tham khảo nhằm hiểu thêm
- Đầu tiên mở 1 bảng tính mới, gõ công thức này vào cell A1:

PHP:

=CHAR(INT(RAND()*5)+65)

- Kéo fill công thức đến cell A30 (mục đích là tạo các ký tự ngẫu nhiên từ A đến E)
- Copy A1:A30 rồi Paste Special\Values vào chính nó
- Xong, chạy thử code này

PHP:

Sub TestDic()
  Dim Clls As Range, Dic, i As Long
  Set Dic = CreateObject("Scripting.Dictionary")
  For Each Clls In Range("A1:A30")
    Clls.Select
    If Not Dic.Exists(Clls.Value) Then  ''<--- kiem tra xem Clls.Value có ton tại trong Dictionary hay khong?
      MsgBox "Gia tri '" & Clls.Value & "' chua ton tai trong Dictionary"
      Dic.Add Clls.Value, ""  ''<--- Add giá tri Clls.Value vào Dictionary (vì nó chua ton tai)
      i = i + 1
      Cells(i, "C") = Clls.Value
    Else
      MsgBox "Gia tri '" & Clls.Value & "' da ton tai trong Dictionary"
    End If
  Next
End Sub

Có MsgBox dùng để diễn giải, các bạn tự suy nghĩ thêm nhé

concogia · 10/11/10

MinhCong đã viết:
Anh ThuNghi có thể giải thích từng dòng lệnh để Anh em học hỏi được không (sao thấy giống như đưa vào mảng quá) nhưng không hiểu nhiều dòng lệnh? Thấy cái CreateObject("Scripting.Dictionary")anh NDU và Anh hay dùng để lọc hay quá.

Hình như bài này Thu Nghi chỉ sử dụng "đít to" làm điều kiện để gán dữ liệu từ mảng Arr sang mảng ArrKQ _dùng "đít to" kiểm tra xem dữ liệu nào là duy nhất ở mảng Arr thì "xơi" nó đem gán qua "thằng" ArrKQ _
Lấy kết quả ở mảng ArrKQ chứ không lấy ở "đit to"
Xin phép Thu Nghi sửa lại code đó một tí tẹo cho Minh Công & Duy Thương dễ nhìn, dễ hiểu ( hình như tốc độ cũng được cải thiện)

Mã:

Sub thuDic2()
    Dim t, i As Long, s As Long, Dic As Object, Arr, ArrKQ(1 To 65000, 1 To 2)
    Set Dic = CreateObject("Scripting.Dictionary")
    t = Timer
        Range("I2:J65536").Clear
        Arr = Range("B2:C" & [B65536].End(xlUp).Row)
            For i = 1 To UBound(Arr, 1)
                If Not Dic.Exists(Arr(i, 1)) Then
                    s = s + 1
                    ArrKQ(s, 1) = Arr(i, 1)
                    ArrKQ(s, 2) = Arr(i, 2)
                    Dic.Add Arr(i, 1), s
                Else
                    ArrKQ(s, 2) = ArrKQ(s, 2) + Arr(i, 2)
                End If
            Next i
    Range("I2").Resize(s, 2) = ArrKQ
    Erase Arr, ArrKQ: Set Dic = Nothing
[i1] = Timer - t
End Sub

ndu96081631 · 10/11/10

concogia đã viết:
Hình như bài này Thu Nghi chỉ sử dụng "đít to" làm điều kiện để gán dữ liệu từ mảng Arr sang mảng ArrKQ _dùng "đít to" kiểm tra xem dữ liệu nào là duy nhất ở mảng Arr thì "xơi" nó đem gán qua "thằng" ArrKQ _
Lấy kết quả ở mảng ArrKQ chứ không lấy ở "đit to"
Xin phép Thu Nghi sửa lại code đó một tí tẹo cho Minh Công & Duy Thương dễ nhìn, dễ hiểu ( hình như tốc độ cũng được cải thiện)

Mã:

Sub thuDic2() Dim t, i As Long, s As Long, Dic As Object, Arr, ArrKQ(1 To 65000, 1 To 2) Set Dic = CreateObject("Scripting.Dictionary") t = Timer Range("I2:J65536").Clear Arr = Range("B2:C" & [B65536].End(xlUp).Row) For i = 1 To UBound(Arr, 1) If Not Dic.Exists(Arr(i, 1)) Then s = s + 1 ArrKQ(s, 1) = Arr(i, 1) ArrKQ(s, 2) = Arr(i, 2) Dic.Add Arr(i, 1), s Else ArrKQ(s, 2) = ArrKQ(s, 2) + Arr(i, 2) End If Next i Range("I2").Resize(s, 2) = ArrKQ Erase Arr, ArrKQ: Set Dic = Nothing [i1] = Timer - t End Sub

Code này sai nha anh!
Sai ở chổ này:
ArrKQ(s, 2) = ArrKQ(s, 2) + Arr(i, 2)
Định vị như thế vô tình chỉ có "em cuối cùng" được cộng dồn mà thôi
Phải như ThuNghi:
ArrKQ(Dic.Item(Arr(i, 1)), 2) = ArrKQ(Dic.Item(Arr(i, 1)), 2) + Arr(i, 2)
thì mới chính xác

concogia · 10/11/10

ndu96081631 đã viết:
Code này sai nha anh!
Sai ở chổ này:
ArrKQ(s, 2) = ArrKQ(s, 2) + Arr(i, 2)
Định vị như thế vô tình chỉ có "em cuối cùng" được cộng dồn mà thôi
Phải như ThuNghi:
ArrKQ(Dic.Item(Arr(i, 1)), 2) = ArrKQ(Dic.Item(Arr(i, 1)), 2) + Arr(i, 2)
thì mới chính xác

Hình như dữ liệu trong bài của chủ topic đã được sắp xếp . Code này vẫn chạy đúng mà Thầy, nó cộng dồn "thằng" nào không duy nhất thôi, S luôn đúng vì không đổi cho tới khi "đít to" tìm ra "thằng" duy nhất tiếp theo chứ không phải chỉ cộng dồn em cuối cùng đâu
Híc

ndu96081631 · 10/11/10

concogia đã viết:
Hình như dữ liệu trong bài của chủ topic đã được sắp xếp . Code này vẫn chạy đúng mà Thầy, nó cộng dồn "thằng" nào không duy nhất thôi, S luôn đúng vì không đổi cho tới khi "đít to" tìm ra "thằng" duy nhất tiếp theo chứ không phải chỉ cộng dồn em cuối cùng đâu
Híc

Đâu có được anh!
Khi anh viết code, anh phải tính đến trường hợp dữ liệu lộn xộn chứ (mà chuyện này là rất thường xuyên xảy ra) ---> Hên nó sort trước nên đúng thôi mà!
Còn không thì ít nhất anh cũng phải cho code sỏrt dữ liệu trước chứ
Ẹc... Ẹc..

ThuNghi · 10/11/10

ndu96081631 đã viết:
Đâu có được anh!
Khi anh viết code, anh phải tính đến trường hợp dữ liệu lộn xộn chứ (mà chuyện này là rất thường xuyên xảy ra) ---> Hên nó sort trước nên đúng thôi mà!
Còn không thì ít nhất anh cũng phải cho code sỏrt dữ liệu trước chứ
Ẹc... Ẹc..

Tôi cũng đã thử, nếu sort trước thì kg cần

ArrKQ(Dic.Item(Arr(i, 1)), 2) = ArrKQ(Dic.Item(Arr(i, 1)), 2) + Arr(i, 2)

và kg cần Dic làm gì.
Còn không nếu chưa sort mà viết 1 code sort thì chắc chắn chậm hơn.

nnd · 10/11/10

Giúp em sử dụng Hàm để lọc Code khách hàng bị trùng ...

Chào các Anh/Chị !!!
Em có một danh sách khách hàng trên 5 ngàn người, nhưng khi xem lại trong danh sách đó quá nhiều Code khách hàng bị trùng nhau. Cho nên em muốn lọc ra 1 Sheet riêng biệt những khách hàng thực sự hiện có. Chỉ duy nhất 1 khách hàng = 1 code mà thôi. Nhưng làm hoài không được, chỉ bằng cách Delete bằng tay cho những khách hàng bị trùng như vậy thì lâu quá. Nên gởi lên đây, nhờ các anh chị giúp xem nên sử dụng Hàm gì ???
Cảm ơn rất nhiều !!!
Kèm theo file thí dụ nhé...

ThuNghi · 10/11/10

nnd đã viết:
Chào các Anh/Chị !!!
Em có một danh sách khách hàng trên 5 ngàn người, nhưng khi xem lại trong danh sách đó quá nhiều Code khách hàng bị trùng nhau. Cho nên em muốn lọc ra 1 Sheet riêng biệt những khách hàng thực sự hiện có. Chỉ duy nhất 1 khách hàng = 1 code mà thôi. Nhưng làm hoài không được, chỉ bằng cách Delete bằng tay cho những khách hàng bị trùng như vậy thì lâu quá. Nên gởi lên đây, nhờ các anh chị giúp xem nên sử dụng Hàm gì ???

1/ Đổi ct tại D4 thành =COUNTIF($B$4:$B4,$B4) và copy xuống
2/ Dùng AutoFilter để lọc những ô mà có kq =1
3/ Copy và dán sang chỗ khác.

Lê Duy Thương · 10/11/10

nnd đã viết:
chào các anh/chị !!!
Em có một danh sách khách hàng trên 5 ngàn người, nhưng khi xem lại trong danh sách đó quá nhiều code khách hàng bị trùng nhau. Cho nên em muốn lọc ra 1 sheet riêng biệt những khách hàng thực sự hiện có. Chỉ duy nhất 1 khách hàng = 1 code mà thôi. Nhưng làm hoài không được, chỉ bằng cách delete bằng tay cho những khách hàng bị trùng như vậy thì lâu quá. Nên gởi lên đây, nhờ các anh chị giúp xem nên sử dụng hàm gì ???
Cảm ơn rất nhiều !!!
Kèm theo file thí dụ nhé...

nếu bạn dùng excel 2007-2010 thì chỉ cần chọn côt b và c sau đó làm theo hình là chưa đến 10s sẽ ra kết quả

Các câu hỏi về lọc ra danh sách duy nhất (loại bỏ dữ liệu trùng)

Thành viên mới

Cạo lấy gì gội (Dịch quá không gội được)

Thành viên mới

❆❆❆❆❆❆❆❆

Thành viên gắn bó

File đính kèm

Thành viên mới

Thành viên gắn bó

File đính kèm

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

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

Thành viên gắn bó

Cạo lấy gì gội (Dịch quá không gội được)

Huyền thoại GPE

Gội rồi mới Cạo

Huyền thoại GPE

Gội rồi mới Cạo

Huyền thoại GPE

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

Thành viên mới

File đính kèm

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

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Facebook

Group