Giúp lọc dữ liệu từ nhiều sheet và tính tổng (1 người xem)

[Tunguyen]

Cảm ơn bác Soibien nhiều, phương pháp điền thẳng value làm file chạy chậm nhưng dung lượng file nhỏ ( em test với khoảng 2000 mục hàng thì file size là ~ 700KB), file dùng công thức thì chạy nhanh nhưng dung lượng lên tới hơn 3MB, nhưng nếu dùng copy rồi paste value thì dung lượng file nhỏ lại.
Vì đây là dùng advance filter - unique record nên cột "Ten hang" và cột "So luong" phải ở cạnh nhau, nếu hai cột không ở cạnh nhau hoặc muốn lấy thêm thông tin từ các cột khác ( chẳng hạn "Don gia") thì thuật toán sẽ như thế nào ạ.

 

[SoiBien]

Cảm ơn bác Soibien nhiều, phương pháp điền thẳng value làm file chạy chậm nhưng dung lượng file nhỏ ( em test với khoảng 2000 mục hàng thì file size là ~ 700KB), file dùng công thức thì chạy nhanh nhưng dung lượng lên tới hơn 3MB, nhưng nếu dùng copy rồi paste value thì dung lượng file nhỏ lại.

Chắc chắn rồi.!

Vì đây là dùng advance filter - unique record nên cột "Ten hang" và cột "So luong" phải ở cạnh nhau,

Không nhất thiết đâu bạn à! vì advance filter chỉ dùng để lấy Unique tên hàng thôi, không liên quan đến việc chúng ở cạnh nhau.

nếu hai cột không ở cạnh nhau hoặc muốn lấy thêm thông tin từ các cột khác ( chẳng hạn "Don gia") thì thuật toán sẽ như thế nào ạ.

Do mình set range (biến shlist(i)) đến 2 cột thôi, nếu dữ liệu có nhiều hơn thì bạn thay đổi thông số lại cho phù hợp (số 1)

Set shlist(shcount) = Range(ActiveCell.Offset(1, 0), _
Cells(Cells(65536, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column + [COLOR=blue][B][U]1[/U][/B][/COLOR]))

Sau đó Bạn phải điều chỉnh lại công thức vlookup cho nó tìm đúng đến cột tên hàng

fmlStr = "Vlookup(RC[-" & i & "],'" & shlist(i).Parent.Name _
                     & "'!R" & shlist(i).Row & "C" & shlist(i).Column & ":R" _
                     & shlist(i).Row + shlist(i).Rows.Count - 1 & "C" & shlist(i).Column + shlist(i).Columns.Count - 1 & ",[COLOR=blue][B]2[/B][/COLOR],0)"

và tương tự cho cột "đơn giá"

thân.

Chiêu này học lại của bác Vungoc, hê hê.

 

[Nguyễn Duy Tuân]

Bạn có thể tham khảo một cách không giống kiểu Excel.

 

[Tunguyen]

Em lại phiền bác Soibien.
Có một vấn đề là nếu tại danh mục tên hàng của một sheet nào đó có dòng trống, từ dòng trống của sheet đó cho đến các dòng của các sheet tiếp theo sẽ không nằm trong range của Advance filter.
Vấn đề thứ 2, hàm Vlookup( hay excel ?) không nhận ra kí tự "~" khi search, em cũng không dùng replace để thay thế nó bằng kí tự khác được, do vậy list tonghop thì có danh mục hàng có kí tự "~" nhưng số lượng = 0, có cách nào không anh.

Bạn có thể tham khảo một cách không giống kiểu Excel.

Cách của bác cũng rất hay, tuy nhiên không phù hợp với trường hợp của em là các range data bị biến đổi, số lượng sheet data nhiều.
Em để dành bài của bác cho bài toán tiếp theo của em.

Em lại phiền bác Soibien.
Có một vấn đề là nếu tại danh mục tên hàng của một sheet nào đó có dòng trống, từ dòng trống của sheet đó cho đến các dòng của các sheet tiếp theo sẽ không nằm trong range của Advance filter.
Vấn đề thứ 2, hàm Vlookup( hay excel ?) không nhận ra kí tự "~" khi search, em cũng không dùng replace để thay thế nó bằng kí tự khác được, do vậy list tonghop thì có danh mục hàng có kí tự "~" nhưng số lượng = 0, có cách nào không anh.

Vấn đề thứ nhất em đã giải quyết bằng cách sort data để loại dòng có dữ liệu trống sau đó mới advance filter:

'sort data de loai dong du lieu trong
        Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        'filter for unique record

        Range(Cells(1, 1), Selection.End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange _
                                                                                       :=Cells(1, 2), Unique:=True

Còn vấn đề thứ 2 em chưa giải quyết được, em đang hỏi anh google xem thế nào.

Hì, em hỏi anh google và anh ấy bảo là nếu dùng find "~" thì không thấy nhưng mà nếu dùng find "~~" thì sẽ thấy.
Nhưng mà nếu mà có ký tự "~" thì làm thế nào để thằng Vlookup nó hiểu các bác nhỉ ?

Em đành phải replace tất cả các ký tự "~" ( tilde - excel gọi là wildcard character ) bằng ký tự "-" ( ký tự thông thường ) rồi mới Vlookup :

 For i = 1 To shcount
            shlist(i).Cells.Replace What:="~~", Replacement:="-", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            shlist(i).Columns(1).Copy Cells(65536, 1).End(xlUp).Offset(1, 0)
        Next i

 

[SoiBien]

Để không phải replace by hand, bạn dùng ý tưởng này nhé:
=VLOOKUP(SUBSTITUTE(a1,"~","~~"),...)

Thân.

 

[Tunguyen]

Em cho replace tự động rồi mới vlookup đấy chứ, nhưng em sẽ xem thử dùng SUBTITUTE thì có chạy nhanh hơn không.
Em muốn điền công thức sum của từng cột ở phía dưới của dòng dữ liệu thì làm thế nào hả anh Soibien ?

 

[SoiBien]

ta đã có tongMH rồi, sau khi filldown Vlookup, pastspecials xlvalue, bạn dùng

for i = 1 to shcount
activecell.offset(tongMH+1,i).formula = "Sum(R[-"&tongMH......
next i

thân.

 

[Tunguyen]

Phải là

for i = 1 to shcount
activecell.offset(tongMH+1,i).formula = "[COLOR="Red"]=[/COLOR]Sum(R[-"&tongMH......
next i

đúng không anh.
Em làm gần hoàn chỉnh file của em rồi, sau khi hoàn thiện em sẽ post lên để bác kiểm tra cho em, cũng như em sẽ liệt kê những cái em học được qua chủ đề này.
Cảm ơn bác soibien nhiều.

 

[otterr]

Tôi có Tool có thể làm được việc này. email cho tôi tôi gửi cho.
Sodo18180@yahoo.com

 

[tammaotacngo]

Lọc đơn giá

Em thấy mọi người bàn luận rất hay em có điều này muốn hỏi xem liệu có cách nào hay không giúp em với.
Chả là công ty em chuyên sản xuất và gia công các mặt hàng. Mã hàng, tên hàng và quy cách đều do khách hàng quy định vì vậy cứ mỗi tháng lại phát sinh rất nhiều mã hàng khác nhau, đơn giá cho từng mã hàng khác nhau vì thế cứ cuối tháng em lại lam công nợ rất vất vả. Giá thì cứ loạn cả lên em lại phải dò tim từng tí một.các bác có cách nào không ạ? giúp em với!!!

 

[kentbie]

File này bao gồm phần chỉnh sửa cho lỗi ở trên và ở file trước mình điền thẳng value sau khi lookup, điều này làm cho excel chạy rất chậm trong trường hợp có nhiều loại hàng. File này mình dùng cách điền công thức vào rồi filldown công thức xuống.

Dear bac Soibien,

Cảm ơn bác, file này rất hữu ích với bên em, nhưng mạn phép có voi đòi hai bà Trưng một chút... nếu muốn thêm cột số tiền và đơn giá vào nữa thì làm thế nào ạ?

Cảm ơn bác rất nhiều!

 

[ChanhTQ@]

Bạn nên đưa file giả lập của bạn lên

& chở tin từ bạn!

​

​

 

[kentbie]

& chở tin từ bạn!

​

​

Đây là file của em ah!
Các sheet data sẽ cố định từ dòng 14 trở lên. Các dòng phía sau biến đổi.
Có khoảng 40 invoices mỗi tháng
khoảng hơn 300 mã nguyên vật liệu

Cảm ơn các bác rất nhiều!