Trích lọc danh sách duy nhất từ các phần tử duy nhất (1 người xem)

[hoangdanh282vn]

Mình có thêm một bài toán về trích lọc, mời các bạn cùng tham gia

 

[boyxin]

dùng công thức thôi, có cho xài cột phụ không vậy?

 

[hoangdanh282vn]

dùng công thức thôi, có cho xài cột phụ không vậy?

À, do đây là topic tìm giải pháp nên mọi giải pháp đều được hoan nghênh.
Nhưng nếu không dùng cột phụ thì càng hay.

 

[boyxin]

À, do đây là topic tìm giải pháp nên mọi giải pháp đều được hoan nghênh.
Nhưng nếu không dùng cột phụ thì càng hay.

Em xin góp 1 cách. Các bác góp ý bổ xung thêm để em tiến bộ nha​

1. Không dùng cột phụ​
2. Sort dữ liệu theo cột A​
3. Dùng Name động để xử lý​

 

[boyxin]

Sau khi dùng bộ thu Macro em thấy như sau

1. Nhập dữ liệu
2. Sort dữ liệu theo cột A
3. Vào Insert\Name\Define... đặt các name
   • Ten =OFFSET($A$2,,,COUNTA($A:$A)-1)
   • SP =OFFSET($B$1,MATCH($A8,TEN,0),,COUNTIF(TEN,$A8))
   • SL =OFFSET(SP,,1)
   • STT =IF(MATCH(SP,SP,0)=ROW(INDIRECT("1:"&ROWS(SP))), MATCH(SP,SP,0),"")
   • KQ =OFFSET($F$2,,,ROWS(TEN)+1,3)
4. Chạy đoạn Macro
   

   Sub Loc()
       Range("F2").FormulaR1C1 = "=IF(OR(RC[-5]="""",RC[-5]=R[-1]C[-5]),"""",RC[-5])"
       Range("G2").FormulaR1C1 = _
           "=IF(ROW()=ROWS(TEN)+2,""TONG CONG"",IF(ROW()>ROWS(TEN)+2,"""",IF(RC[-1]<>"""",""Tong SL SP"",IF(ROW()-ROW(SP)>COUNT(STT),"""",INDEX(SP,MATCH(SMALL(STT,ROW()-ROW(SP)),STT,0),1)))))"
       Range("H2").FormulaR1C1 = _
           "=IF(RC[-1]=""TONG CONG"",SUM(OFFSET(R2C[-5],,,ROWS(TEN))),IF(RC[-2]<>"""",SUM(OFFSET(R[1]C,,,COUNT(STT))),SUMIF(SP,RC[-1],SL)))"
       Range("F2:H2").AutoFill Destination:=Range("KQ"), Type:=xlFillDefault
   End Sub

Được kết quả như ý muốn

 

[anhtuan1066]

Boyxin ơi! Record macro kiểu này khó đọc quá!
Hi...hi...
Bài toán này gần tương tự với:
http://www.giaiphapexcel.com/forum/showthread.php?t=12619
Làm đc cái kia thì làm đc cái này (chẳng khác nhau gì mấy)
Phuùuuuuuu

 

[boyxin]

Boyxin ơi! Record macro kiểu này khó đọc quá!
Hi...hi...
Bài toán này gần tương tự với:
http://www.giaiphapexcel.com/forum/showthread.php?t=12619
Làm đc cái kia thì làm đc cái này (chẳng khác nhau gì mấy)
Phuùuuuuuu

Thì đã nói là không biết gì về Macro, chỉ dùng bộ thu của Microsoft thôi, không biết chế biến ra sao cả
Ai rành khoản này thì góp thêm phương án đi

 

[hoangdanh282vn]

Em xin góp 1 cách. Các bác góp ý bổ xung thêm để em tiến bộ nha​

1. Không dùng cột phụ​
2. Sort dữ liệu theo cột A​
3. Dùng Name động để xử lý​

Bác làm gần ra rồi đó, nhưng chỉ mỗi cái là Bác lại làm thay đổi dữ liệu gốc, mà cái này lại là cái quan trong nhất. Bác thử làm không dùng đến cột phụ mà vẫn không làm thay đổi dữ liệu gốc xem.
To Bác Anhtuan1066 : Về ý nghĩa thì 2 bài toán gần giống nhau nhưng về thuật toán thì bài này khác bài kia và dĩ nhiên là khó hơn, bài kia em đã gửi file lên rồi mà.
Bác làm tiếp luôn bài này nha. Sau đợt này chắc tay nghề VBA của Bác tiến bộ lắm đây.

 

[anhtuan1066]

Bác làm gần ra rồi đó, nhưng.......

Boyxin ơi cố lên... sắp... sắp... rồi... tôi ũng hộ nè...
he...he...

 

[boyxin]

Bác làm gần ra rồi đó, nhưng chỉ mỗi cái là Bác lại làm thay đổi dữ liệu gốc, mà cái này lại là cái quan trong nhất. Bác thử làm không dùng đến cột phụ mà vẫn không làm thay đổi dữ liệu gốc xem.
To Bác Anhtuan1066 : Về ý nghĩa thì 2 bài toán gần giống nhau nhưng về thuật toán thì bài này khác bài kia và dĩ nhiên là khó hơn, bài kia em đã gửi file lên rồi mà.
Bác làm tiếp luôn bài này nha. Sau đợt này chắc tay nghề VBA của Bác tiến bộ lắm đây.

đây là topic tìm giải pháp nên mọi giải pháp đều được hoan nghênh.

Khổ cái đề bài không nói chỗ nào quan trọng nhất không được thay đổi nên vô tình không may lại chạm tay vào đúng huyệt hiểm. hiiiiiiii chán thật

Boyxin ơi cố lên... sắp... sắp... rồi... tôi ũng hộ nè...
he...he...

Okie, đang trông chờ mọi sự ủng hộ, tiếp tay nè...

 

[ThuNghi]

Thấy dùng Pivot Table cũng hay, làm thử 1 Macro về Pivot. Các bạn góp ý!

 

[ndu96081631]

Sửa 1 tí bài số #5 tại:
http://www.giaiphapexcel.com/forum/showthread.php?t=12619
Ta được 1 giãi pháp VBA:

Option Explicit
Sub Loc()
  Dim DS As Range, MH As Range, KH As Range, TempDS As Range
  Dim SL As Range, TempKH As Range, LocKH As Range
  Dim Er1 As Long, Er2 As Long, i As Long
  Dim CT As String
  Dim Luu As Variant
  Application.ScreenUpdating = False
  Call Xoa
  Er1 = [A65536].End(xlUp).Row
  Set DS = [A1].Resize(Er1, 3)
  Set TempDS = [A1].Resize(Er1, 2)
  Set KH = [A1].Resize(Er1, 1)
  Set SL = KH.Offset(, 2)
  Luu = DS.Value
  DS.Sort Key1:=[A2], Key2:=[B2], Order1:=1, Order2:=1, Header:=1
  TempDS.AdvancedFilter Action:=2, CopyToRange:=[F1:G1], Unique:=True
  [C1].Copy Destination:=[H1]
  Er2 = [G65536].End(xlUp).Row
  Set LocKH = Range("F1:F" & Er2)
  CT = "=SUMPRODUCT((R2C1:R1000C1=RC[-2])*(R2C2:R1000C2=RC[-1])*(R2C3:R1000C3))"
  For i = Er2 To 2 Step -1
     Set TempKH = LocKH(i).Resize(1, 3)
     If LocKH(i) <> LocKH(i - 1) Then
       TempKH.Copy
       TempKH.Insert Shift:=xlDown
       With LocKH(i)
          .Offset(, 1) = "Tong SP"
          .Offset(, 2) = Application.WorksheetFunction.SumIf(KH, .Value, SL)
          .Offset(1, 2).Formula = CT
          .Offset(1, 2).Value = .Offset(1, 2).Value
          .Offset(1, 0).Clear
          With .Resize(1, 3)
            .Font.Bold = True
            .Font.ColorIndex = 5
            .Interior.ColorIndex = 35
          End With
       End With
     Else:
       With LocKH(i)
         .Offset(, 2).Formula = CT
         .Offset(, 2).Value = .Offset(, 2).Value
         .Clear
       End With
     End If
   Next i
   With [G65536].End(xlUp).Offset(1, 0)
     .Value = "TONG CONG"
     .Font.Bold = True
     .Offset(, 1) = Application.WorksheetFunction.Sum(SL)
     With .Resize(1, 2).Font
        .Bold = True
        .ColorIndex = 3
     End With
   End With
   DS.Value = Luu
   Set DS = Nothing
   Set TempDS = Nothing
   Set KH = Nothing
   Set SL = Nothing
   Set LocKH = Nothing
   Set TempKH = Nothing
   Application.ScreenUpdating = True
End Sub
Sub Xoa()
  Columns("F:H").Clear
End Sub

 

[hoangdanh282vn]

Mình gửi một cách dùng công thức, các bạn tham khảo nha.

 

[hoangdanh282vn]

Dùng VBA

Sub loc()
Dim i As Long, r As Long
Dim KH As Range, SL As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set KH = [a2].Resize([a65536].End(xlUp).Row, 1)
Set SL = KH.Offset(, 2)
Range("F1:H65536").ClearContents
[A1].CurrentRegion.Resize(, 2).AdvancedFilter Action:=2, CopyToRange:=[F1:G1], Unique:=True
r = [F65536].End(xlUp).Row
[F2].Resize(r - 1, 2).Sort Key1:=[F2], Order1:=xlAscending, Key2:=[G2], Order2:=xlAscending
For i = r To 2 Step -1
    If Cells(i, 6) = Cells(i - 1, 6) Then
        Cells(i, 8).Formula = "=SUMPRODUCT((R2C1:R50000C1=RC[-2])*(R2C2:R50000C2=RC[-1])*(R2C3:R50000C3))"
            Cells(i, 8) = Cells(i, 8).Value: Cells(i, 6).Clear
       ElseIf Cells(i, 6) <> Cells(i - 1, 6) Then With Cells(i, 6)
        .Resize(1, 3).Copy: .Resize(1, 3).Insert Shift:=xlDown
        .Offset(, 2).Formula = "=SUMPRODUCT((R2C1:R50000C1=RC[-2])*(R2C2:R50000C2=RC[-1])*(R2C3:R50000C3))"
        .Offset(, 2) = .Offset(, 2).Value: .Clear
        .Offset(-1, 1) = "Tong SL SP"
        .Offset(-1, 2) = Application.WorksheetFunction.SumIf(KH, Cells(i, 6).Value, SL)
        End With
    End If
Next i
With Cells([G65536].End(xlUp).Row, 7)
.Value = "TONG CONG": .Offset(, 1) = Application.WorksheetFunction.Sum(SL)
End With
[H1] = [C1]
Set KH = Nothing: Set SL = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[ThuNghi]

Cách của Danh cũng chậm hơn là VBA từ Pivot. Chả hiểu sao!

 

[hoangdanh282vn]

Cách của Danh cũng chậm hơn là VBA từ Pivot. Chả hiểu sao!

Chắc là Pivot thì tích hợp sẵn thằng Sumproduct theo dữ liệu có nên sẽ nhanh hơn.
Nếu trong code của em mà chèn được số dòng hiện hữu có trong DATA vào thằng Sumproduct thì sẽ nhanh hơn, vì em đưa tới 50.000 dòng vào nên nó tính chậm là đúng rồi.
Em chưa thử là đưa biến đếm số dòng của dữ liệu vào Sumproduct có được hay không, nếu được thì chắc sẽ nhanh không kém Pivot đâu

 

[ndu96081631]

Chắc là Pivot thì tích hợp sẵn thằng Sumproduct theo dữ liệu có nên sẽ nhanh hơn.
Nếu trong code của em mà chèn được số dòng hiện hữu có trong DATA vào thằng Sumproduct thì sẽ nhanh hơn, vì em đưa tới 50.000 dòng vào nên nó tính chậm là đúng rồi.
Em chưa thử là đưa biến đếm số dòng của dữ liệu vào Sumproduct có được hay không, nếu được thì chắc sẽ nhanh không kém Pivot đâu

Đương nhiên là được rồi! Nối chuổi thôi mà hoangdanh282vn
Ví dụ ta tính được Sodong K = 50 và ta muốn đưa trị số K này vào SUMPRODUCT thì:

"=SUMPRODUCT((R2C1:R" & K & "C1=RC[-2])*(R2C2:R" & K & "C2=RC[-1])*(R2C3:R" & K & "C3))"

 

[yeudoi]

ndu96081631
Bạn xem lại bài 12 thử file mình kéo xuống 5000 dòng thấy dữ liệu không thấy đúng.
Nếu như mình có dữ liệu như file đính kèm thì làm sao trích xuất dữ liệu là hay nhất.
P/s Tôi đã sửa lại file cho rõ hơn mọi người xem giúp nha.

 

[ndu96081631]

ndu96081631
Bạn xem lại bài 12 thử file mình kéo xuống 5000 dòng thấy dữ liệu không thấy đúng.
Nếu như mình có dữ liệu như file đính kèm thì làm sao trích xuất dữ liệu là hay nhất.

Có thể sai ở hàm SUMPRODUCT!
Tôi đặt:

CT = "=SUMPRODUCT((R2C1:R1000C1=RC[-2])*(R2C2:R1000C2=RC[-1])*(R2C3:R1000C3))"

Tới dòng 1000, bạn sửa lại thành số bao nhiêu tùy theo dử liệu của bạn

 

[hoangdanh282vn]

ndu96081631
Bạn xem lại bài 12 thử file mình kéo xuống 5000 dòng thấy dữ liệu không thấy đúng.
Nếu như mình có dữ liệu như file đính kèm thì làm sao trích xuất dữ liệu là hay nhất.

Có phải ý của anh là trích lọc duy nhất, tách SP ra khỏi tên, sắp xếp, liệt kê và tính tổng theo từng người và từng ngày phải không ạ.
Anh thử minh họa một ngày xem, để có thể hiểu rõ hơn về yêu cầu

 

[ndu96081631]

ndu96081631
Bạn xem lại bài 12 thử file mình kéo xuống 5000 dòng thấy dữ liệu không thấy đúng.
Nếu như mình có dữ liệu như file đính kèm thì làm sao trích xuất dữ liệu là hay nhất.

Ah... quên nữa!
Dử liệu cột A của bạn có dạng: Ten - Mã (Nguyễn Văn A - A001).. Vậy tôi nghĩ trong code của bạn phải làm 1 việc tách Tên và Mã ra riêng trước... Dùng công cụ Text to Columns là ngon ăn nhất
------------------------------------
Tạm sửa lại file của bạn theo tiêu chí Text to Columns như tôi nói ở trên!

 

[yeudoi]

Bác làm gần đúng ý em rồi, nhưng ở đây em danh mục em không phụ thuộc vào sheet tim mà phụ thuộc vào sheet Data. Có lúc em cần xem 2 người, lúc lại xem 3 người lúc lại xem toàn bộ. Bác xem giúp em, file em đã sửa lại ở trên.

 

[ndu96081631]

Bác làm gần đúng ý em rồi, nhưng ở đây em danh mục em không phụ thuộc vào sheet tim mà phụ thuộc vào sheet Data. Có lúc em cần xem 2 người, lúc lại xem 3 người lúc lại xem toàn bộ. Bác xem giúp em, file em đã sửa lại ở trên.

Tôi không hiểu cho lắm ý bạn! Bạn nói rõ hơn 1 chút được không? Tôi thấy cách bố trí dử liệu ở sheet Data còn dể lọc hơn nữa ấy chứ

 

[Mr Okebab]

Mình có thêm một bài toán về trích lọc, mời các bạn cùng tham gia

Sẽ có các cách sau đây :

1. Công thức : Cũng hơi dài
2. Subtotal : Rất ngắn gọn, đòi hỏi phải Sort Khách hàng trước
3. Pivot Table : Rất ngắn gọn
4. VBA1 : Dùng UDF : Khá mất thời gian, tuy không khó lắm
5. VBA2 : Dùng Sub : Rất dễ dàng với nhiều người
6. Không biết còn cách nào nữa không ???

Thân!

1. Đã có người làm
2. Chưa có ai làm
3. Đã có người làm
4. Chưa có ai làm
5. Đã có người làm
6. Chưa có ai làm

Không biết có ai muốn cống hiến thêm các phương án 2; 4; 6 không nhỉ ??

VD : Cách 2 :

Thân!

 

[hoangdanh282vn]

Trích lọc dùng Subtotal

Code tính Subtotal

Sub subtotal()
Application.ScreenUpdating = False
Range("F1:I65536").ClearContents
[A1].CurrentRegion.Copy Destination:=[F1]
With [F1].CurrentRegion
    .Sort Key1:=[F2], Order1:=xlAscending, Key2:=[G2], Order2:=xlAscending

    .subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
Application.ScreenUpdating = True
End Sub

Code remove subtotal

Sub removeSubtotal()
With [F1].CurrentRegion
.removeSubtotal: .Clear
End With
End Sub

 

[yeudoi]

Tôi không hiểu cho lắm ý bạn! Bạn nói rõ hơn 1 chút được không? Tôi thấy cách bố trí dử liệu ở sheet Data còn dể lọc hơn nữa ấy chứ

Thì vẫn như bạn làm nhưng tôi có yêu cầu thêm là theo ngày và bố tri dữ liệu theo sheet Data bởi khi sheet tìm nhiều khi ít hoặc nhiều dữ liệu hơn sheet Data hoặc khi cần xem 1 hoặc nhiều người. Nếu dễ hơn thì bạn làm thử xem làm sao kết quả đúng như yêu cầu.

 

[ndu96081631]

Thì vẫn như bạn làm nhưng tôi có yêu cầu thêm là theo ngày và bố tri dữ liệu theo sheet Data bởi khi sheet tìm nhiều khi ít hoặc nhiều dữ liệu hơn sheet Data hoặc khi cần xem 1 hoặc nhiều người. Nếu dễ hơn thì bạn làm thử xem làm sao kết quả đúng như yêu cầu.

Vậy bạn thử trích bằng tay vào file xem thử (vì tôi vẩn chưa hình dung kết quả mà bạn cần sẽ thế nào)

 

[yeudoi]

Bạn xem lại file mình gửi. Mình đã trích bằng tay và đã sửa lại rồi