Lọc dữ liệu thỏa nhiều kiều kiện & tự chèn dòng & tự đánh số thứ tự (1 người xem)

phuplix · 4/6/15

Thân chào các bác

Em có câu hỏi này nhờ các bác chỉ giáo dùm, em thấy có một vài vídụ nhỏ lẻ ở các bài trước có nội dung hơi giống & đã được cácbác trả lời

Tuy nhiên câu hỏi của em nó hơi nhiều yêu cầu 1 tí

Em có một bảngdatabase với nhiều cột dữ liệu

Em muốntrích xuất các dữ liệu thỏa 2-3 điều kiện sang 1 sheet mới

Tuy nhiêncác dữ liệu trích xuất này phải có tính năng tự chèn thêm dòng

Và cột sốthứ tự cũng phải tự đánh số

Em phát hiệncó một add-in của excel là A-tools giúp làm được việc này. Tuy nhiên dữ liệutrích xuất ra bị giới hạn < 100 dòng. Do đó rất mong các cao thủ ra tay giúpem vụ này.
Em nghĩ cáinày có nhiều cách nhưng dùng VBA có lẽ là đơn giản nhất phải không ?

dhn46 · 4/6/15

Chắc bạn cũng biết ít nhiều VBA, bạn tham khảo code sau và tùy biến cho phù hợp

Mã:

Sub Loc()
    Dim Arr()
    Dim ResArr()
    Dim iR As Long
    Dim DL As String
    Dim Thang As Long
    Dim Nam As Long
    Dim k As Long
    Dim TongTien As Double


    With Sheets("du lieu")
        Arr = .Range("A6:X" & .Range("X65536").End(3).Row).Value2
    End With
    ReDim ResArr(1 To UBound(Arr, 1) + 3, 1 To 7)
    With Sheets("KQ")
        DL = .[K1]
        Thang = .[K2]
        Nam = .[K3]
        For iR = 1 To UBound(Arr, 1)
            If Arr(iR, 5) = DL Then
                If Arr(iR, 23) = Thang Then
                    If Arr(iR, 24) = Nam Then
                        k = k + 1
                        ResArr(k, 1) = k
                        ResArr(k, 2) = Arr(iR, 4)
                        ResArr(k, 3) = Arr(iR, 2)
                        ResArr(k, 4) = Arr(iR, 3)
                        ResArr(k, 5) = Arr(iR, 6)
                        ResArr(k, 6) = Arr(iR, 7)
                        ResArr(k, 7) = Arr(iR, 8)
                    End If
                End If
            End If
        Next
        For iR = 1 To k
            TongTien = TongTien + ResArr(iR, 7)
        Next
        ResArr(iR, 7) = TongTien
        ResArr(iR + 1, 7) = TongTien * 10 / 100
        ResArr(iR + 2, 7) = ResArr(iR, 7) + ResArr(iR + 1, 7)


        .Rows("10:10000").ClearContents
        .[A10].Resize(iR + 2, 7) = ResArr


    End With
End Sub

phuplix · 4/6/15

Cám ơn bác nhé
Em không rành lắm về VBA. Em copy đoạn code trên vào VBA của sheet yêu cầu . Nhấn nút Run thì nó báo bảng Macro, nhấn tiếp thì nó báo lỗi Subscript out of range

Bác hướng dẫn lại giùm em cái nhé. Em hơi bị dốt !

phuplix · 5/6/15

Bác dhn46 giúp em lại nhé
Em tet marco nung no van bao loi Subcript ou range

chuot0106 · 5/6/15

phuplix đã viết:
Cám ơn bác nhé
Em không rành lắm về VBA. Em copy đoạn code trên vào VBA của sheet yêu cầu . Nhấn nút Run thì nó báo bảng Macro, nhấn tiếp thì nó báo lỗi Subscript out of range

Bác hướng dẫn lại giùm em cái nhé. Em hơi bị dốt !

Ban đưa File của bạn nên đây là xong ngay. Ai mà đoán bệnh được.

Ngoai Thanh · 5/6/15

chuot0106 đã viết:
Ban đưa File của bạn nên đây là xong ngay. Ai mà đoán bệnh được.

Chuột thế nào thế ? File của người ta ở bài một kia mà !

chuot0106 · 5/6/15

Ngoai Thanh đã viết:
Chuột thế nào thế ? File của người ta ở bài một kia mà !

Đây là File giả lập của người ta thôi NgoaiThanh ah. Người ta cho code vào file thật nó mới báo lỗi mà!

doveandrose · 5/6/15

out of range là đúng rồi đâu cần ngạc nhiên
muốn code này chạy thì tạo 1 cái sheet mới đặt tên là kq
copy vùng K1:K3 của sheet a-tool sang sheet kq
chạy macro

SA_DQ · 5/6/15

Chả cần đao to búa lớn làm gì cho mệt xác!

Chỉ cần fương thức AdvancedFilter, Sau đó vài thao tác nhỏ mọn như cho ẩn các dòng thừa, chép công thức tới,. . . là được rồi!

phuplix · 5/6/15

Cám ơn bác doveandrose

Em cũng đã làm thử như bác hướng dẫn
Kết quả thì OK rồi bác ạ
Nhưng có 3 điều bất tiện sau :

1- Mỗi lần đổi điều kiện lọc : đại lý, tháng thì phải ấn chạy marco
2- Tuy trích xuất ra được dữ liệu nhưng dữ liệu này nó đè lên các ô Tổng, VAT... chứ không đẩy các ô này xuống
3- Không format border các ô này được. Đã chỉnh format border xong, sau đó thay điều kiện lọc thì lại quay lại format ô như cũ

Em có đính kèm file các bác xem sẽ dễ hiểu hơn

best regard

chuot0106 · 5/6/15

phuplix đã viết:
Cám ơn bác doveandrose

Em cũng đã làm thử như bác hướng dẫn
Kết quả thì OK rồi bác ạ
Nhưng có 3 điều bất tiện sau :

1- Mỗi lần đổi điều kiện lọc : đại lý, tháng thì phải ấn chạy marco
2- Tuy trích xuất ra được dữ liệu nhưng dữ liệu này nó đè lên các ô Tổng, VAT... chứ không đẩy các ô này xuống
3- Không format border các ô này được. Đã chỉnh format border xong, sau đó thay điều kiện lọc thì lại quay lại format ô như cũ

Em có đính kèm file các bác xem sẽ dễ hiểu hơn

best regard

Có Teamview ko tôi vào xem giúp cho!

Ba Tê · 5/6/15

phuplix đã viết:
Cám ơn bác doveandrose

Em cũng đã làm thử như bác hướng dẫn
Kết quả thì OK rồi bác ạ
Nhưng có 3 điều bất tiện sau :

1- Mỗi lần đổi điều kiện lọc : đại lý, tháng thì phải ấn chạy marco
2- Tuy trích xuất ra được dữ liệu nhưng dữ liệu này nó đè lên các ô Tổng, VAT... chứ không đẩy các ô này xuống
3- Không format border các ô này được. Đã chỉnh format border xong, sau đó thay điều kiện lọc thì lại quay lại format ô như cũ

Em có đính kèm file các bác xem sẽ dễ hiểu hơn

best regard

Thử kiểu này xem sao. Hide cột M nếu không muốn thấy nó.
Thay đổi K1:K3 là code chạy

Uyen Nhien · 5/6/15

Mình có file dữ liệu cũng có yêu cầu giống bạn, giờ mình tải lên cho bạn tham khảo nhé, công thức hơn bị nhức đầu. Hồi trước không biết sao mình lại siêu vậy có thể làm được, giờ thì quên rồi. !$@!!

Ba Tê · 5/6/15

Uyen Nhien đã viết:
Mình có file dữ liệu cũng có yêu cầu giống bạn, giờ mình tải lên cho bạn tham khảo nhé, công thức hơn bị nhức đầu. Hồi trước không biết sao mình lại siêu vậy có thể làm được, giờ thì quên rồi.

Gởi file mà có Password thì gởi làm gì, đừng để người khác nói mình "Lếu".
Dù là phá PW là chuyện "...như con thỏ".

phuplix · 8/6/15

Cám ơn bác doveanrose... sau khi làm theo hướng dẫn của bác thì em đã thành công, tuy nhiên do em dốt nên thấy đoạn code của bác dài quá nên em sợ, em định copy qua cho thêm vài file nữa mà sợ làm hổng được.

Cám ơn bác SA-DQ, cái code để chạy advance filter & ẩn dòng, chép công thức... rất tuyệt. Ngắn gọn, súc tích, dễ xài
Em đã ngâm cứu & chỉnh sửa lại thành công.

Tuy nhiên ở cái file mới em không biết chỉnh thiếu phần gì mà có 1 đoạn code nó báo lỗi sai giá trị. Nhờ bác nghiền ngẫm file đính kèm & sửa code lại giùm em

Ngoài ra , các bác nào cao thủ viết giúp em đoạn code cho sheet !List vehicle. Sheet này em xài quá nhiều hàm SUMIF, mỗi giá trị có 11 cột SUMIF. Mà file của em hơn 5,000 giá trị. Mỗi lần chạy là nó lê lết cái máy gần 15 phút.
Ngoài VBA nếu có hàm nào đơn giản hơn thì các bác chỉ giùm em luôn thể. Tại sheet này có thể về lâu dài lại thêm bớt cột. Dòng VBA em lại không biết cách sửa. --=0

Chânb thành cảm ơn các bác

SA_DQ · 9/6/15

[thongbao]Tuy nhiên ở cái file mới em không biết chỉnh thiếu phần gì mà có 1 đoạn code nó báo lỗi sai giá trị. Nhờ bác nghiền ngẫm file đính kèm & sửa code lại giùm em[/thongbao]
Bạn thử ghi bằng bộ thu xem macro có bị lỗi không?
Mình ghi thử thì được.

phuplix · 9/6/15

Em thử rồi mà nó chạy không được bác ơi
Nó báo lỗi này. Có hình đây

SA_DQ · 9/6/15

phuplix đã viết:
Em thử rồi mà nó chạy không được bác ơi
Nó báo lỗi này.

Ô [M1] của bạn có vấn đề;

Bạn nên lập danh sách duy nhất cho nó; Không nên làm như bạn, rất dễ gây lỗi chính tả không cần thiết.

Sau khi chạy macro mà bị lỗi thì hàng loạt công thức bên trang CSDL cũng lỗi; Theo mình các công thức bị lỗi cũng cần điều chỉnh cho hết lỗi hay xóa bỏ để macro chạy lần sau khỏi bị fiền hà!

phuplix · 9/6/15

Em chưa hiểu lắm ý của bác. Bác có thể giải thích cặn kẽ hơn em sai chỗ nào ko. Ô nào bị sai, cột nào bị lỗi

SA_DQ · 9/6/15

1./ Mình thấy rằng bên trang CSDL chỉ có 2 đại lí; Đó là An Khánh & Me linh;
1.1 Nếu để "Mê linh" mà chạy, macro sẽ báo lỗi
Ta sang trang CSDL thì hàng loạt công thức liên quan đến đại lí này sẽ báo lỗi!
Lúc đó mình dùng lệnh 'Goto' & xóa những công tưức này đi, thì hầu hết các ô dữ liệu trong các hàng liên quan đến 'Mê linh' này sẽ biến mất
1.2 Nếu chạy macro với 'An Khánh' thì báo lỗi; Mình nghĩ do lỗi chính tả khi nhập vô [M1]

Sau khi tạo danh sách duy nhất cho các đại lí (chỉ có 2) & ghi từ bộ thu macro thì fương thức AdvancedFilter kết quả OK với tháng 4;
(Như vậy dữ liệu về ngày của bạn cũng cần xem lại; Với mình thì mình sẽ xài ngày hệ Mẽo & định dạng theo hệ Fáp cho quen mắt người Việt.)

phuplix · 9/6/15

Cám ơn hướng dẫn của bác

Bác xem lại giùm em. Ở ô Database & ô M1, em đâu thấy cái nào là Me Linh đâu. Tất cả đều là Mai Linh mà

Em cũng đã thử chỉnh lại định dạng ngày tháng & list đại lý. Kết quả vẫn báo lỗi y chang hà bác ơi !

Em nghĩ là lỗi đến từ phần code của VBA. Tại em chôm code từ file khác sang nhưng chưa hiểu lắm ý nghĩa code nên chỉnh lại giá trị chưa đúng. Bác xem thử giùm em.

Cám ơn bác }}}}}

SA_DQ · 9/6/15

Em nghĩ là lỗi đến từ phần code của VBA. Tại em chôm code từ file khác sang nhưng chưa hiểu lắm ý nghĩa code nên chỉnh lại giá trị chưa đúng. Bác xem thử giùm em.

Code trong file của người ta vẫn chạy vù vù với file dữ liệu của bạn đưa lên lần đầu mà!

Bạn nên đưa 1 ít dữ liệu của bạn chép nối vô file đó xem sao.

phuplix · 9/6/15

Cám ơn các bác

Sau khi nghiền ngẫm kĩ lưỡng em đã phát hiện ra được 2 chỗ sai trong đoạn code
Một chỗ ở ô bắt đầu filter bên sheet Database, 1 chỗ sai chỗ Validation.

Em nhờ các bác giải tiếp câu hỏi thứ 2

Bên sheet List vehicle em dùng nhiều hàm Sumif tại các cột từ K-W. Làm file nặg bà cố. Mỗi lần chạy là mất 10 phút
File em gửi lên đây ví dụ ít data, chứ file đầy đủ của em hơn 5000 dòng, chạy file muốn treo máy

Nhờ các bác chỉ em cách thay thế hàm nào khác cho nhẹ hơn hoặc VBA code nào thay cho đống hàm này giùm

Cám ơn các bác nhiều

phuplix · 10/6/15

Cám ơn bác SA-DQ lần nữa

Em đã thành công với đoạn code VBA của Bác. Không chỉ cho file này mà còn cả các file khác.

Tuy nhiên em có một thắc mắc là dùng cách này thì kết quả ra rất OK nếu giá trị filter ra được nhiều dòng.

Còn nếu kết quả advance filter chỉ ra 1 dòng thì nó vẫn báo lỗi.

Có cách nào fix lỗi này không bác ?

Bác xem thử ví dụ ở sheet : Form khong approve. Nếu em chọn các đại lý khác thì không sao. Nhưng nếu chọn đại lý Vân Nam + tháng 3+ năm 2015 thì sẽ báo lỗi vì kết quả filter chỉ ra có 1 dòng !

Em đính kèm file như bên dưới

Ba Tê · 10/6/15

phuplix đã viết:
Cám ơn các bác

Sau khi nghiền ngẫm kĩ lưỡng em đã phát hiện ra được 2 chỗ sai trong đoạn code
Một chỗ ở ô bắt đầu filter bên sheet Database, 1 chỗ sai chỗ Validation.

Em nhờ các bác giải tiếp câu hỏi thứ 2

Bên sheet List vehicle em dùng nhiều hàm Sumif tại các cột từ K-W. Làm file nặg bà cố. Mỗi lần chạy là mất 10 phút
File em gửi lên đây ví dụ ít data, chứ file đầy đủ của em hơn 5000 dòng, chạy file muốn treo máy

Nhờ các bác chỉ em cách thay thế hàm nào khác cho nhẹ hơn hoặc VBA code nào thay cho đống hàm này giùm

Cám ơn các bác nhiều

Đưa file dữ liệu gì mà kết quả toàn bằng 0 làm sao kiểm tra? Ẩu thiệt!

phuplix · 10/6/15

Cám ơn thầy Ba Tê đã giúp em

Em xin lỗi đã đính kèm file quá ít data. Vì file chính thức của em đến tận hơn 10 Mb. Nén lại cũng gần 2Mb. Không upload lên diễn đàn được được em cắt bớt data. làm cho thầy viết code xong thì không kiểm tra lai kết quả được

Em xin gửi lại file đầy đủ theo đường link
http://www.mediafire.com/download/c...phieu_SCMP_2012-2015_(P_KD)_-_kaizen_form.rar

Thầy xem lại giúp em ! Em đã copy đoạn code VBA của thầy vào sheet List vehicle! nhưng không hiểu sao nó không hoạt động. Nó báo lỗi out of range.

Cám ơn thầy Ba Tê nhiều @$@!^%

Ba Tê · 10/6/15

phuplix đã viết:
Cám ơn thầy Ba Tê đã giúp em

Em xin lỗi đã đính kèm file quá ít data. Vì file chính thức của em đến tận hơn 10 Mb. Nén lại cũng gần 2Mb. Không upload lên diễn đàn được được em cắt bớt data. làm cho thầy viết code xong thì không kiểm tra lai kết quả được

Em xin gửi lại file đầy đủ theo đường link
http://www.mediafire.com/download/c...phieu_SCMP_2012-2015_(P_KD)_-_kaizen_form.rar

Thầy xem lại giúp em ! Em đã copy đoạn code VBA của thầy vào sheet List vehicle! nhưng không hiểu sao nó không hoạt động. Nó báo lỗi out of range.

Cám ơn thầy Ba Tê nhiều

Máy của bạn thuộc dạng "mạnh vô địch". Vì mở file và sử dụng được.
Bạn có nghĩ là mỗi công thức của bạn trong một ô thôi, như
=SUMIFS(Database!I:I;Database!J:J;"ML";Database!E:E;"<"&DATE(2015;1;1);Database!B:B;">="&DATE(2015;1;1);Database!B:B;"<"&DATE(2015;2;1))
Nó sẽ tính cột cả cột 65.536 dòng. I x J x E x B x B = 327.680 ô.
1.000 ô công thức thì mỗi lần tính lại máy tính sẽ ra sao?
Híc!
Máy tôi thì "đơ" rồi.
-----------------------------------
Thấy bạn xài SUMIFS(), sao không lưu file .xlsm hay .xlsb cho nhẹ file mà lưu .xls ?
Tôi bỏ mấy sheet công thức của bạn, chỉ để lại 2 sheet cho code chạy thử thôi nhé.
Code là ở trong Module mà.
Lưu .xlsb cho nhẹ dung lượng.

phuplix · 10/6/15

Hic... vậy mới khổ đó thầy Ba Tê

Ngoài cách dùng cái hàm đó em không biết dùng cách nào khác.

Nếu thầy ngoài VBA mà có hàm nào khác hay hơn hoặc cách nào khác hay hơn thì chỉ em giùm ! }}}}}

Đã test thử file của thầy. Chạy OK lắm

Em hỏi tiếp : Cái nút nhấn đó là do VBA tạo ra hay là dạng button trong combo box vậy ?
Mỗi lần nhấn nút GPE thì nó mới update phải ko thầy. Hay là mỗi lần Save ?

Lí do em save thành .xls là vì trong 2 sheet mà thầy xóa có lưu một đoạn VBA của bác SA-DQ. Bắt buộc phải lưu file ở dạng excel 2003.

Em hỏi luôn câu cuối; có cách nào thay dòng lệnh này bằng một dòng lệnh khác cho nó chạy nhanh hơn không. Vì em còn nhiều file khác nữa cùng hay dùng dạn sumifs với nhiều điều kiện như vầy.
=SUMIFS(Database!I:I;Database!J:J;"ML";Database!E: E;"<"&DATE(2015;1;1);Database!B:B;">="&DATE(2015;1 ;1);Database!B:B;"<"&DATE(2015;2;1))

Ba Tê · 11/6/15

Mỗi lần nhấn nút GPE thì nó mới update phải ko thầy. Hay là mỗi lần Save ?

Đúng vậy, Tạo cái nút để nhấn, không nhấn thì ăn thua gì.

Lí do em save thành .xls là vì trong 2 sheet mà thầy xóa có lưu một đoạn VBA của bác SA-DQ. Bắt buộc phải lưu file ở dạng excel 2003.

Chưa thấy ai nói bị "khống chế" chuyện này, 2003 chạy được thì mấy "em" sau 2003 đều chạy được.

Em hỏi luôn câu cuối; có cách nào thay dòng lệnh này bằng một dòng lệnh khác cho nó chạy nhanh hơn không. Vì em còn nhiều file khác nữa cùng hay dùng dạn sumifs với nhiều điều kiện như vầy.

Thay vì B:B, nếu "rảnh rỗi" thì tạo Name động, bi nhiêu dòng có dữ liệu thì xài bi nhiêu thôi, mắc gì cái nào cũng xài cả cột, nếu là 2007 về sau nó sẽ là 1048576 dòng chứ không phải là 65536 như 2003 đâu, có mà tốn tiền cà phê và thuốc lá chờ nó chạy.
Những người xài vùng dữ liệu B:B hay A:A trong công thức đều là những "đại gia Excel", không ai dám "đụng" đâu.(Treo máy tức chết)

phuplix · 12/6/15

Cám ơn thầy BaTê

Chưa thấy ai nói bị "khống chế" chuyện này, 2003 chạy được thì mấy "em" sau 2003 đều chạy được.
=> File excel nếu có VAB thì khi lưu với dạng .xlsx nó bắt phải lưu riêng marco & VBA thành 1 file riêng. Còn khi lưu dưới dạng 2003 thì chỉ lưu 1 file. Em thấy các bác trên diễn đàn khi làm giúp VBA cho em đều lưu thành office 2003 hết mà thầy.
Nếu thầy có cách nào hay hơn thì hướng dẫn giùm em

THầy cho em hỏi thêm cái.
Khi em làm file & lưu file này dạng .xls trên ổ đĩa D thì không sao. Nhưng khi em lưu lại thành dạng .xlsb trên ổ đĩa F , khi nhấn nút GPE thì nó báo lỗi (không tìm thấy file này tại ổ đĩa D). Tuy nhiên , nếu em nhấn View- Rune Macro thì nó vẫn chạy được bình thường.
Thầy có thể giải thích em giùm cái để hiểu được thông suốt hơn.

Ba Tê · 12/6/15

phuplix đã viết:
Cám ơn thầy BaTê

Chưa thấy ai nói bị "khống chế" chuyện này, 2003 chạy được thì mấy "em" sau 2003 đều chạy được.
=> File excel nếu có VAB thì khi lưu với dạng .xlsx nó bắt phải lưu riêng marco & VBA thành 1 file riêng. Còn khi lưu dưới dạng 2003 thì chỉ lưu 1 file. Em thấy các bác trên diễn đàn khi làm giúp VBA cho em đều lưu thành office 2003 hết mà thầy.
Nếu thầy có cách nào hay hơn thì hướng dẫn giùm em

THầy cho em hỏi thêm cái.
Khi em làm file & lưu file này dạng .xls trên ổ đĩa D thì không sao. Nhưng khi em lưu lại thành dạng .xlsb trên ổ đĩa F , khi nhấn nút GPE thì nó báo lỗi (không tìm thấy file này tại ổ đĩa D). Tuy nhiên , nếu em nhấn View- Rune Macro thì nó vẫn chạy được bình thường.
Thầy có thể giải thích em giùm cái để hiểu được thông suốt hơn.

View attachment 142268

- Với Excel >=2007, File có Macro bắt buộc phải lưu dưới kiểu .xlsm hoặc .xlsb, lưu với .xlsx thì nó không chứa Macro.
- Ai biết ổ F của bạn là ổ nào mà trả lời.
Lưu .xls vào ổ D chạy được sao không lưu thành .xlsm hay .xlsb vào ổ D mà xài?
Híc!

Lọc dữ liệu thỏa nhiều kiều kiện & tự chèn dòng & tự đánh số thứ tự (1 người xem)

Người dùng đang xem chủ đề này

Thành viên chính thức

File đính kèm

Hướng tới tương lai

Thành viên chính thức

Thành viên chính thức

Thành viên gắn bó

Cánh Diều xanh

Thành viên gắn bó

hello

/(hông là gì!

File đính kèm

Thành viên chính thức

Thành viên gắn bó

Cạo Rồi Khỏi Gội

File đính kèm

Thành viên mới

File đính kèm

Cạo Rồi Khỏi Gội

Thành viên chính thức

File đính kèm

/(hông là gì!

Thành viên chính thức

/(hông là gì!

Thành viên chính thức

/(hông là gì!

Thành viên chính thức

/(hông là gì!

Thành viên chính thức

File đính kèm

Thành viên chính thức

File đính kèm

Cạo Rồi Khỏi Gội

File đính kèm

Thành viên chính thức

Cạo Rồi Khỏi Gội

File đính kèm

Thành viên chính thức

Cạo Rồi Khỏi Gội

Thành viên chính thức

Cạo Rồi Khỏi Gội

Thời gian đếm ngược.