Lọc bỏ trùng đối tượng và tính tổng giá trị của đối tượng đó

[vanhesing]

Dear All !
Anh Chị vui lòng xem file đính kèm .
Trong file em sử dụng công thức lọc theo điều kiện có sử dụng cột phụ (nghiên cứu và học hỏi trên diễn đàn ) , lấy data từ sheet TONGHOP và lọc qua sheeet DATA1 , rồi từ sheet DATA1 này em cùng code lọc bỏ trùng đối tượng (mã thiết bị ) và dán qua sheet BAOCAOTB, còn những cột khác thì em dùng công thức :vlookup và sum if.
Do sử dụng công thức nên số lượng dòng có công thức nhiều làm cho file nặng .
- Nhờ anh chị chỉ giáo cho em xin code lọc theo điều kiện từ sheet TONGHOP sang sheet DATA1 rồi từ sheet DATA1 bổ sung vào code cũ của em để nó vừa lọc bỏ trùng và tính tổng đối tượng đó .
- Em đi đường vòng cho dễ hiểu với lại em không biết cách nào làm cho ngắn hơn .
- Mong Các anh chị cao thủ giúp đỡ em phầm này để em có thêm bài học mới ^^
Thanks All ! Happy New Year!

 

[ChanhTQ@]

Trong file em sử dụng công thức lọc theo điều kiện có sử dụng cột phụ (. . .) , lấy data từ sheet TONGHOP và lọc qua sheeet DATA1 , rồi từ sheet DATA1 này em cùng code lọc bỏ trùng đối tượng (mã thiết bị ) và dán qua sheet BAOCAOTB, . . .

Bạn hãy cho biết bạn lọc theo những điều kiện nào?
(Chứ không fải ai nhìn vô công thức bạn cũng biết bạn lọc như thế nào!)

 

[vanhesing]

Bạn hãy cho biết bạn lọc theo những điều kiện nào?
(Chứ không fải ai nhìn vô công thức bạn cũng biết bạn lọc như thế nào!)

Em sorry anh Chánh ! em đảng trí quá
Tạm thời em muốn lọc theo :"Nhà cung cấp", "sỡ hữu ", và "xuất nhập tồn tại đơn vị " (từ sheet TONGHOP sang sheet DATA1)
Sau đó , tại sheet DATA1 em muốn lọc bỏ trùng cột " mã thiết bị " , copy qua sheet BAOCAOTB và tính Sum số lượng như tiêu đề của sheet BAOCAOTB
Nhờ anh tạo giúp em 1 file mẫu khi mún vận dụng search thêm thì em sẽ tìm hiểu
Thank Anh

 

[ChanhTQ@]

Bạn xem trang DATA1 của file đính kèm

Chọn lần lượt Nhà CC tại [S2], Sở hữu tại ô fải liền kề & đơn vị tại [Z2] & bấm vô nút 'FilterAll' để macro nó lọc;

Chúng ta sẽ tiếp sau khi bạn kiểm tra kết quả lọc nha.

 

[vanhesing]

Chọn lần lượt Nhà CC tại [S2], Sở hữu tại ô fải liền kề & đơn vị tại [Z2] & bấm vô nút 'FilterAll' để macro nó lọc;

Chúng ta sẽ tiếp sau khi bạn kiểm tra kết quả lọc nha.

Dear Anh Chánh !
Đầu tiên anh cho em hỏi là dùng code để lọc có bắt buộc phải dùng cột phụ không ? theo như em thấy thì file anh làm nhìn loạn cả mắt .hic hic copy dữ liệu tới vùng BX:BZ , còn cột DB,DD,DF là sao ? Em chưa hiểu chổ này !
Nếu lọc thì mỗi lần lọc em chỉ được lọc một trong 3 cái là : nhà cung cấp , đơn vị , quyền sỡ hữu hay có thể lọc được cả 3 cùng 1 lúc .
Chứ em tesst thì thấy lọc lần đầu nó ra 1 list,mấy lần sau nó nằm đè lên chứ ko xóa trường cũ rồi sau đó em xóa trường cũ hết lọc lại theo mỗi trường hợp thì nó không ra nữa.
Thanks Anh

 

[ChanhTQ@]

(1) Đầu tiên cho em hỏi là dùng code để lọc có bắt buộc phải dùng cột phụ không ? theo như em thấy thì file anh làm nhìn loạn cả mắt .hic hic copy dữ liệu tới vùng BX:BZ , còn cột DB,DD,DF là sao ? Em chưa hiểu chổ này !
(2) Nếu lọc thì mỗi lần lọc em chỉ được lọc một trong 3 cái là : nhà cung cấp , đơn vị , quyền sỡ hữu hay có thể lọc được cả 3 cùng 1 lúc .
Chứ em tesst thì thấy lọc lần đầu nó ra 1 list,mấy lần sau nó nằm đè lên chứ ko xóa trường cũ rồi sau đó em xóa trường cũ hết lọc lại theo mỗi trường hợp thì nó không ra nữa.

(1) Trong file mình lọc bằng fương thức AdvancedFilter với sự hỗ trợ của VBA
Khi lọc bằng AdvFilter thì ta không cần cột fụ nhưng cần vùng Criteria. Trong file đó là vùng 'TongHop'!Bx1:Bz2
Vùng kết quả tại 'TongHop'!CurentRegion sau đó được copy sang trang tính đang có nút lệnh FilterAll

Còn các cột DB, DD & DF là vùng danh sách duy nhứt từ các cột tương ứng trong CSDL được gán tên hầu fục vụ cho 3 Validation tại 'Data1', đó là [S2], [T2] & [Z2]; Chuyện này chì liên quan đến việc chọn lựa các fương án trước khi lọc thôi.

(2) Hiện macro lọc theo 3 tiêu chí cùng 1 lúc;
Nếu bạn lọc lần lượt theo từng tiêu chí thì nên khác đi 1 chút sẽ tiện cho bạn hơn trong việc thao tác lọc tiếp theo. Chổ này bạn cần nói rõ hơn để chúng ta sửa lại.

Chúc vui!

 

[vanhesing]

(1) Trong file mình lọc bằng fương thức AdvancedFilter với sự hỗ trợ của VBA
Khi lọc bằng AdvFilter thì ta không cần cột fụ nhưng cần vùng Criteria. Trong file đó là vùng 'TongHop'!Bx1:Bz2
Vùng kết quả tại 'TongHop'!CurentRegion sau đó được copy sang trang tính đang có nút lệnh FilterAll

Còn các cột DB, DD & DF là vùng danh sách duy nhứt từ các cột tương ứng trong CSDL được gán tên hầu fục vụ cho 3 Validation tại 'Data1', đó là [S2], [T2] & [Z2]; Chuyện này chì liên quan đến việc chọn lựa các fương án trước khi lọc thôi.

(2) Hiện macro lọc theo 3 tiêu chí cùng 1 lúc;
Nếu bạn lọc lần lượt theo từng tiêu chí thì nên khác đi 1 chút sẽ tiện cho bạn hơn trong việc thao tác lọc tiếp theo. Chổ này bạn cần nói rõ hơn để chúng ta sửa lại.

Chúc vui!

hêhhehe
Em hiểu rồi . nếu zậy dùng code ta tốn thêm vùng Criteria , còn công thức thì chỉ tốn 1 cột phụ . mà lợi ích từ 2 cái lại ngược nhau ^^
Do em muốn học hỏi, tìm hiểu nên lọc theo 3 tiêu chí thì em hơi rối . giờ em muốn lọc 1 tiêu chí thôi đó là nhà cung cấp . Em làm công thức thì đáp ứng đủ yêu cầu của em rồi, em mún học thêm phần code thôi .
- 3 Validation tại 'Data1', đó là [S2], [T2] & [Z2]; thì em mún biết là nó tự update khi có thêm đối tượng mới hay mình tự tay đánh vào ?
- Trong phần công thức em gán ngày tháng và 3 tiêu chí lọc ở sheeet BAOCAOTB, gián tiếp gọi lệnh lọc từ TONGHOP qua DATA1 và trục tiếp lọc bỏ trùng + sum số liệu cho sheet BACAOTB . he he he .
- Em muốn học hỏi thêm xem cách nào hay hơn nên anh viết code dễ hiểu chút và vui lòng giải thích những đoạn em ko hiểu
Thanks Anh

 

[SA_DQ]

(c) nếu zậy dùng code ta tốn thêm vùng Criteria , còn công thức thì chỉ tốn 1 cột phụ . mà lợi ích từ 2 cái lại ngược nhau ^^
(a) - 3 Validation tại 'Data1', đó là [S2], [T2] & [Z2]; thì em mún biết là nó tự update khi có thêm đối tượng mới hay mình tự tay đánh vào ?
(b) - Em muốn học hỏi thêm xem cách nào hay hơn nên anh viết code dễ hiểu chút và vui lòng giải thích những đoạn em ko hiểu
Thanks Anh

(a) Các danh sách duy nhứt này ta cũng có thể viết macro sự kiện; để khi mở trang tính 'Data1' lên thì nó cập nhựt ngay thôi.

(b) Code này chỉ le que vài 3 dòng lệnh thôi mà.

Option Explicit
Sub FilterAll()
1 Dim Sh As Worksheet:                Dim lRow As Long
        
 Set Sh = ThisWorkbook.Worksheets("TONGHOP")
 
3 lRow = Sh.Cells.Find(What:="*", After:=Sh.[A1], SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
 Sh.[A8].Resize(lRow, 29).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sh.Range( _
        "BX1:BZ2"), CopyToRange:=Sh.Range("BA8:CC8"), Unique:=False
5 Sh.[BA9].CurrentRegion.Offset(1).Copy Destination:=[A5]
End Sub

Dòng 1 là khai báo các biến cần thiết;
Dòng 2 là gán trang tính 'TongHop' vô biến đối tượng Sh; Vì chúng ta áp dụng fương thức lọc (ở dòng lệnh dưới) ở trang tính không là trang hiện hành nên vậy.
Dòng 3: Tìm dòng cuối cùng có dữ liệu của CSDL; Cần nói thêm rằng đây là cách đúng nhứt trong mọi lúc mọi nơi, khi mà ta cần dòng cuối cùng chứa dữ liệu
Dòng 4 Thực hiện fương thức AdvancedFilter; Trong đó
- Sh.[A8].Resize(lRow, 29) là vùng CSDL nguồn để trích lọc; (A: Để hiểu thêm về f ương thức Resize() ta đến chữ ký của SA_DQ, nha)
Dòng 5: Copy vùng kết quả lọc sang trang 'Data1' (Cũng như trên, bạn tham khảo thêm fương thức CurrentRegion tại A)

(c) 01 cột & 1 vùng gồm 6 ô thì tuỳ bạn cho là cái nào khả dĩ hơn là theo thói quen mỗi người thôi. Thêm vô đó, nếu bằng macro, ta có thể hình thành vùng này 1 cách không khó lắm đâu!

Chúc bạn vui xuân an lành!

 

[vanhesing]

(a) Các danh sách duy nhứt này ta cũng có thể viết macro sự kiện; để khi mở trang tính 'Data1' lên thì nó cập nhựt ngay thôi.

(b) Code này chỉ le que vài 3 dòng lệnh thôi mà.

Option Explicit
Sub FilterAll()
1 Dim Sh As Worksheet:                Dim lRow As Long
        
 Set Sh = ThisWorkbook.Worksheets("TONGHOP")
 
3 lRow = Sh.Cells.Find(What:="*", After:=Sh.[A1], SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
 Sh.[A8].Resize(lRow, 29).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sh.Range( _
        "BX1:BZ2"), CopyToRange:=Sh.Range("BA8:CC8"), Unique:=False
5 Sh.[BA9].CurrentRegion.Offset(1).Copy Destination:=[A5]
End Sub

Dòng 1 là khai báo các biến cần thiết;
Dòng 2 là gán trang tính 'TongHop' vô biến đối tượng Sh; Vì chúng ta áp dụng fương thức lọc (ở dòng lệnh dưới) ở trang tính không là trang hiện hành nên vậy.
Dòng 3: Tìm dòng cuối cùng có dữ liệu của CSDL; Cần nói thêm rằng đây là cách đúng nhứt trong mọi lúc mọi nơi, khi mà ta cần dòng cuối cùng chứa dữ liệu
Dòng 4 Thực hiện fương thức AdvancedFilter; Trong đó
- Sh.[A8].Resize(lRow, 29) là vùng CSDL nguồn để trích lọc; (A: Để hiểu thêm về f ương thức Resize() ta đến chữ ký của SA_DQ, nha)
Dòng 5: Copy vùng kết quả lọc sang trang 'Data1' (Cũng như trên, bạn tham khảo thêm fương thức CurrentRegion tại A)

(c) 01 cột & 1 vùng gồm 6 ô thì tuỳ bạn cho là cái nào khả dĩ hơn là theo thói quen mỗi người thôi. Thêm vô đó, nếu bằng macro, ta có thể hình thành vùng này 1 cách không khó lắm đâu!

Chúc bạn vui xuân an lành!

Thanks HYen nhiều lắm ^^ bài giải thích rất đầy đủ .
Vậy tiếp tục nhé.
Bây giờ mình muốn lọc bỏ trùng và sum số liệu đối tượng từ sheeet DATA1 sang sheet BACAOTB . Mình chỉ làm được cách lọc bỏ trùng thôi , còn sum số liệu thì chỉ dùng công thức tại cells
Thanks
Chúc bạn năm mới vui vẻ

 

[SA_DQ]

Bạn xem trong file đính kèm

Mình vừa xong & đưa lên do vội; Khi nào có thời gian sẽ rút gọn thêm. . . .

Bạn chú í : Các trường ở trang tính 'TongHop' trùng tên đã fải đổi tên vì lí do kỹ thuật. Nếu không ưng những tên như vậy bạn có thể đổi lại. Khi đó bạn cần đổi ổ 2 nơi mới là OK!

Chúc xuân vui vẽ!

 

[vanhesing]

Mình vừa xong & đưa lên do vội; Khi nào có thời gian sẽ rút gọn thêm. . . .

Bạn chú í : Các trường ở trang tính 'TongHop' trùng tên đã fải đổi tên vì lí do kỹ thuật. Nếu không ưng những tên như vậy bạn có thể đổi lại. Khi đó bạn cần đổi ổ 2 nơi mới là OK!

Chúc xuân vui vẽ!

Thanks Anh SA_DQ nhiều lắm , tết này có việc làm rồi .
Em sẽ nghiên cứu bản này chi tiết hơn .
Chúc anh và tất cả thành viên , già trẻ lớn bé trong room có một năm mới dồi dào "phúc lộc thọ "

 

[ChanhTQ@]

Macro rut gọn được rồi đây; Bạn có thể rút nữa, nếu muốn

Option Explicit
 Dim Rng As Range, Sh As Worksheet:                     Dim TrT As String
 Dim Ton As Long, Nhp As Long, Xt_ As Long
 
Sub LocBoTrungVaTinhTong()
 Dim eRs As Long, Col As Byte:                          Dim MaTB As String
  
 Set Rng = [b4].CurrentRegion
 Set Sh = ThisWorkbook.Worksheets("BAOCAOTB")
 Col = Rng.Columns.Count:                               eRs = Rng.Rows.Count
 [A4].Resize(eRs, Col).Sort Key1:=[f5], Order1:=xlAscending, Key2:=[I5] _
    , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1
 Sh.[b10].Resize(eRs, Col).ClearContents
 For Each Rng In Range([f5], [F65500].End(xlUp))
    If MaTB <> Rng.Value Then
        If Rng.Row > 5 Then
            'Ghi & Xoa So Lieu Cu '
            GhiVaXoa
        End If
            'Ghi Vo Bien Theo Ma Thiet Bi Moi:'
        MaTB = Rng.Value:                               TrT = Left(Rng.Offset(, 3).Value, 1)
        
        Ton = Rng.Offset(, Switch(TrT = "T", 4, TrT = "H", 7, TrT = "C", 10)).Value
        Nhp = Rng.Offset(, Switch(TrT = "T", 5, TrT = "H", 8, TrT = "C", 11)).Value
        Xt_ = Rng.Offset(, Switch(TrT = "T", 6, TrT = "H", 9, TrT = "C", 12)).Value
    Else
        If Left(Rng.Offset(, 3).Value, 1) = TrT Then
            'Cong Don So Lieu Theo Trang Thai:'
            Ton = Ton + Rng.Offset(, Switch(TrT = "T", 4, TrT = "H", 7, TrT = "C", 10)).Value
            Nhp = Nhp + Rng.Offset(, Switch(TrT = "T", 5, TrT = "H", 8, TrT = "C", 11)).Value
            Xt_ = Xt_ + Rng.Offset(, Switch(TrT = "T", 6, TrT = "H", 9, TrT = "C", 12)).Value
        Else
            'Chep Du Lieu Theo Trang Thai & Xoa Tri Trong Cac Bien:'
            GhiVaXoa
            'Ghi Vo Bien Theo Trang Thai Moi:'
            TrT = Left(Rng.Offset(, 3).Value, 1)
            Ton = Rng.Offset(, Switch(TrT = "T", 4, TrT = "H", 7, TrT = "C", 10)).Value
            Nhp = Rng.Offset(, Switch(TrT = "T", 5, TrT = "H", 8, TrT = "C", 11)).Value
            Xt_ = Rng.Offset(, Switch(TrT = "T", 6, TrT = "H", 9, TrT = "C", 12)).Value
        End If
    End If
 Next Rng
 Set Rng = [F65500].End(xlUp)
 GhiVaXoa
End Sub

[B]Sub GhiVaXoa()[/B]
 With Sh.[B99].End(xlUp).Offset(1)
    .Value = Rng.Value                                      'Ma TB'
    .Offset(, 1).Value = Rng.Offset(, -1).Value             'TenTB'
    .Offset(, 2).Resize(, 2).Value = Rng.Offset(, 13).Resize(, 2).Value
    .Offset(, 4).Value = Rng.Offset(, 1).Value              'DVT'
    .Offset(, Switch(TrT = "T", 5, TrT = "H", 9, TrT = "C", 13)).Value = Ton
    .Offset(, Switch(TrT = "T", 6, TrT = "H", 10, TrT = "C", 14)).Value = Nhp
    .Offset(, Switch(TrT = "T", 7, TrT = "H", 11, TrT = "C", 15)).Value = Xt_
    Ton = 0:                Nhp = 0:                        Xt_ = 0
 End With
[B]End Sub[/B]