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

Tunguyen · 17/10/07

Chào các bác, em là người mới tham gia diễn đàn, về VBA thì mới dừng ở mức ghi Macro rồi bắt chước. Vì thế em muốn nhờ mọi người giúp em code VBA cho file sau của em.
Em có dữ liệu các mặt hàng và số lượng từ nhiều sheet, em cần tổng kết các mặt hàng đó vào 1 sheet và lấy số tổng số lượng ( như file đính kèm ).
Các bác giúp em với, em cảm ơn trước.

duongsatdn · 17/10/07

Bài này đâu cần VBA. Chỉ dùng công thức là được rồi.
Bạn xem thử nhé!

anhtuan1066 · 17/10/07

duongsatdn đã viết:
Bài này đâu cần VBA. Chỉ dùng công thức là được rồi.
Bạn xem thử nhé!

Cái này chưa ồn lắm... vì món quan trọng nhất ở đây là lấy dc danh sách các mặt hàng đấy... Lấy xong rồi thì mới tính tới tỗng cộng chứ... Ở đây chỉ có vài mặt hàng, còn thực tế nó đến hàng trăm, thậm chí là hàng ngàn... Ai ngồi lọc nổi mấy mặt hàng này bằng cách gõ gõ?

Tunguyen · 17/10/07

duongsatdn đã viết:
Bài này đâu cần VBA. Chỉ dùng công thức là được rồi.
Bạn xem thử nhé!

Không phải vậy đâu bác ơi. Như bác anhtuan nói là đúng đấy ạ, danh mục mặt hàng nó có hàng nghìn, hơn nữa lại không cố định nên không dùng hàm vlookup được.
Ý em muốn là từ 3(hay nhiều ) sheet lọc ra một danh sách rồi lấy tổng số lượng chứ danh sách không cố định và có sẵn.

Em nói thêm là yêu cầu này phát sinh do 2 vấn đề :

1. Các mặt hàng phát sinh mới liên tục
2. Lỗi typo.

ThuNghi · 17/10/07

Tôi góp ý Tunguyen nhé:
1/ Phải lập dm trước khi nhập SL phát sinh
2/ Còn chưa thì phải có công đoạn copy tòan bộ mã hàng vào 1 dm sau đó dùng advance filter, Unique record only => tạo ra danh mục. Hơi phê
3/ Dùng sumif là OK.

anhtuan1066 · 17/10/07

Cái này dùng công thức cũng ko phải là ko thể... nhưng nếu dử liệu nhiều quá thì e rằng file sẽ "nặng"... các cao thủ góp ý việc dùng VBA trong trường hợp này như thế nào đây?

Tunguyen · 17/10/07

ThuNghi đã viết:
Tôi góp ý Tunguyen nhé:
1/ Phải lập dm trước khi nhập SL phát sinh
2/ Còn chưa thì phải có công đoạn copy tòan bộ mã hàng vào 1 dm sau đó dùng advance filter, Unique record only => tạo ra danh mục. Hơi phê
3/ Dùng sumif là OK.

Đúng là dùng phương pháp này là được, lập lại danh mục rồi dùng lookup chứ nhỉ. Nhưng mà danh mục này có gần 3000 mục, ngày xưa em không biết dùng sumproduct nên để tính được tổng tiền thì file nặng 80MB +-+-+-+

, giờ nhờ học ở GPE nên file chỉ còn 5MB. /-*+/

Dung công thức thì hơi thủ công quá, mỗi lần lập danh mục mất thời gian lắm, có bác nào dùng VBA giúp em với.

Không có ai giúp em à. @!##

ThuNghi · 22/10/07

Copy tòan bộ mã hàng các sh vào 1 sh nào đó, sau đó dùng advance filter, chọn unique record only. Hay là nghiên cứu bài ttphong2007 (13/10/07) dùng công thức mảng.

SoiBien · 22/10/07

Cấu trúc các bảng của bạn là cố định?
i.e :

cột tên hàng có value là "Ten Hang", dưới nó bắt đầu là list?
cột số lượng nằm ngay bên cạnh cột tên hàng?
mỗi list ở một sheet?

Tunguyen · 22/10/07

SoiBien đã viết:
Cấu trúc các bảng của bạn là cố định?
i.e :

cột tên hàng có value là "Ten Hang", dưới nó bắt đầu là list?

cột số lượng nằm ngay bên cạnh cột tên hàng?

mỗi list ở một sheet?

Vâng, có thể để cố định thế được, trong file ví dụ em để cột tên hàng bắt đầu ở các ô khác nhau, nhưng để đơn giản có thể để bắt đầu từ cùng 1 ô.

SoiBien · 22/10/07

Tunguyen đã viết:
Vâng, có thể để cố định thế được, trong file ví dụ em để cột tên hàng bắt đầu ở các ô khác nhau, nhưng để đơn giản có thể để bắt đầu từ cùng 1 ô.

Vậy để mình viết cho bạn một cái Macro, hix, lười quá không muốn thao tác bằng tay chứ gì/

ThuNghi · 22/10/07

Làm cho bạn file lấy DM = ct mà phê quá nên không tiếp tục. Để SoiBien làm VBA thôi.

Tunguyen · 22/10/07

SoiBien đã viết:
Vậy để mình viết cho bạn một cái Macro, hix, lười quá không muốn thao tác bằng tay chứ gì/

Hì, vấn đề không phải là luời quá không muốn thao tác bằng tay, mà vì bây giờ vẫn làm bằng tay nhưng mà mất nhiều thời gian lắm mà lại hay bị nhầm.
Tạm thời em dùng cách của anh thunghi, hì, em cũng bái phục anh thunghi, xưa em cứ nghĩ mình xài công thức excel giỏi lắm, giờ mới thấy biển học đúng là vô bờ. +-+-+-+

ThuNghi · 23/10/07

To: Tunguyen
Lâu lắm rồi mới nghe được lời khen chân tình. Vui vì bạn chịu khó xem file, bản thân tôi cũng không muốn xem lại.
Tặng bạn file làm bằng VBA. Trong đây chỉ sd 6 name (lỡ đặt TenHang1,...,SL1) còn bao nhiêu name khác bạn xóa đi nếu không dùng công thức. Để nghiên cứu cũng vui.
File này cũng dễ hiểu lắm, vì tôi chưa biết cách làm cho khó hiểu.

Tunguyen · 23/10/07

Cảm ơn anh nhiều, em đang xem file của anh, dễ hiểu nhưng mà khó lập, hì, em không tự viết được thế này, nhưng mà từ file của anh em áp dụng được nhiều việc khác đây, cảm ơn anh nhiều nha.

Có một lỗi là nếu sheet data mà không có dữ liệu sẽ báo lỗi tại dòng :

iR* = Data0*.Range("TenHang*").Rows.Count

Fix nó thế nào hả bác.

Với cả bây giờ mà em muốn add thêm các sheet data thì phải add thủ công trong VBA, anh giúp cho em chọn các sheet nào mình muốn đưa vào một cách tự đông được không ?

SoiBien · 23/10/07

Tunguyen đã viết:
Không có ai giúp em à.

Bạn xem nhé.

ThuNghi · 24/10/07

Bạn nghiên cứu file sau. Hy vọng giúp được bạn chuyện khác.
Chúc thành công.

Tunguyen · 25/10/07

Cảm ơn bác Soibien và bác Thunghi nhiều, em đang nghiền ngẫm 2 file của các bác, các bác cho em hậu tạ nhé.

Có một vấn đề, khi em dùng file của bác Soibien, tongMH sẽ cố định bằng 37.

Mã:

 'filter for unique record

        Range(Cells(1, 1), Selection.End(xlDown)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Range(Cells(1, 1), Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select

        tongMH = Selection.Rows.Count - 1
     
        Selection.Copy Worksheets(shTongKet).Cells.Find(listTitle)
        .Sheets(shTongKet).Activate
        Application.DisplayAlerts = False
        .Sheets("tmpTongKet").Delete
        Application.DisplayAlerts = True

SoiBien · 27/10/07

Ngộ nhỉ, để mình xem lại nhé. Lúc viết thì không test trên nhiều loại.

Tunguyen đã viết:

Có một vấn đề, khi em dùng file của bác Soibien, tongMH sẽ cố định bằng 37.

Mã:

 'filter for unique record
 
        Range(Cells(1, 1), Selection.End(xlDown)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Range(Cells(1, 1), Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
 
        tongMH = Selection.Rows.Count - 1
 
        Selection.Copy Worksheets(shTongKet).Cells.Find(listTitle)
        .Sheets(shTongKet).Activate
        Application.DisplayAlerts = False
        .Sheets("tmpTongKet").Delete
        Application.DisplayAlerts = True

Bạn thay đoạn code trên bằng đoạn này nhé.

Mã:

        [COLOR=green]'filter for unique record[/COLOR]
        Range(Cells(1, 1), Selection.End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange _
        :=Cells(1, 2), Unique:=[COLOR=darkblue]True[/COLOR]
        Cells(1, 2).Select
        Range(ActiveCell, Selection.End(xlDown)).Select
        tongMH = Selection.Rows.Count - 1
        Selection.Copy Worksheets(shTongKet).Cells.Find(listTitle)
        .Sheets(shTongKet).Activate
        Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
        .Sheets("tmpTongKet").Delete
        Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]

SoiBien · 28/10/07

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.

Tunguyen · 30/10/07

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 · 30/10/07

Tunguyen đã viết:
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)

Mã:

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

Mã:

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 · 30/10/07

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

Tunguyen · 30/10/07

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.

TuanVNUNI đã viết:
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.

Tunguyen đã viết:
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:

Mã:

'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 :

Mã:

 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 · 31/10/07

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

Thân.

Tunguyen · 1/11/07

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 · 1/11/07

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

PHP:

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

thân.

Tunguyen · 2/11/07

Phải là

Mã:

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 · 8/11/07

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 · 8/4/11

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 · 1/9/11

SoiBien đã viết:
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@ · 1/9/11

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

& chở tin từ bạn!
;;;;;;;;;;;

kentbie · 1/9/11

ChanhTQ@ đã viết:
& 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!

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

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

Thành viên mới

File đính kèm

Thành viên tiêu biểu

File đính kèm

Thành viên gạo cội

Thành viên mới

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

Thành viên gạo cội

Thành viên mới

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

Thành viên tiêu biểu

Thành viên mới

Thành viên tiêu biểu

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

File đính kèm

Thành viên mới

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

File đính kèm

Thành viên mới

Thành viên tiêu biểu

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

File đính kèm

Thành viên mới

Thành viên tiêu biểu

Thành viên tiêu biểu

File đính kèm

Thành viên mới

Thành viên tiêu biểu

Nghị Hách

File đính kèm

Thành viên mới

Thành viên tiêu biểu

Thành viên mới

Thành viên tiêu biểu

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

0901452không62

Thành viên mới

File đính kèm

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