Trích dữ liệu từ bảng tổng hợp BOM (1 người xem)

[levanduyet]

Chào các bạn,
Tôi có một file gồm một worksheet chứa dữ liệu BOM.
Trong một BOM cho một sản phẩm (bắt đầu bằng 22) thì nó có khoảng 2 đến 3 mã bán thành phẩm (bắt đầu bằng 21).
Bây giờ tôi muốn từ một worksheet khác trong cùng workbook này tôi muốn lấy các mã bán thành phẩm (dĩ nhiên là có cả số lượng, đơn vị tính) thành hàng ngang. Tôi đang lay hoay không biết nên dùng công thức nào?
Xin các bạn xem file đính kèm.

LVD

 

[ThuNghi]

Bác có đùa anh em không dzậy?
Giả dụ BomHead là đã sort, còn không sẽ làm = match

 

[anhtuan1066]

Đại ca nói đùa hay sao ấy chứ.. cái này là VLOOKUP cơ mà...

Xin lỗi... nếu dữ liệu trùng thì dùng INDEX và MATCH... hic...
Chắc đại ca buồn quá muốn chọc anh em đây!

 

[levanduyet]

Bác có đùa anh em không dzậy?
Giả dụ BomHead là đã sort, còn không sẽ làm = match

To: ThuNghi,
Cám ơn Bác. Em chỉ muốn các Component bắt đầu bằng "21" (i.e bán thành phẩm) mà thôi.

Làm VBA hoài, đâm ra chán. Tập tành theo Bác vậy.

Lê Văn Duyệt

 

[anhtuan1066]

Duyệt ơi... món này tất nhiên làm bằng công thức thì dễ rồi, nhưng tôi thấy dử liệu lớn quá, nếu mà làm bằng công thức thì sẽ bị treo máy đấy... Xem lại đi, với trường hợp cụ thề này có lẽ làm VBA sẽ nhẹ hơn
ANH TUẤN

 

[Mr Okebab]

Chào các bạn,
Tôi có một file gồm một worksheet chứa dữ liệu BOM.
Trong một BOM cho một sản phẩm (bắt đầu bằng 22) thì nó có khoảng 2 đến 3 mã bán thành phẩm (bắt đầu bằng 21).
Bây giờ tôi muốn từ một worksheet khác trong cùng workbook này tôi muốn lấy các mã bán thành phẩm (dĩ nhiên là có cả số lượng, đơn vị tính) thành hàng ngang. Tôi đang lay hoay không biết nên dùng công thức nào?
Xin các bạn xem file đính kèm.

LVD

Em lỡ dùng VBA rồi (do hiểu sai ý bác) thôi cứ up lên vậy nhé.
Bác Click vào hàng cuối cùng + 1 của cột A nhé.
Hơi bị chậm, em chưa biết cách cải tiến.


(Sao mà nhiều mặt hàng thế !!!!)
Thân

 

[levanduyet]

Duyệt ơi... món này tất nhiên làm bằng công thức thì dễ rồi, nhưng tôi thấy dử liệu lớn quá, nếu mà làm bằng công thức thì sẽ bị treo máy đấy... Xem lại đi, với trường hợp cụ thề này có lẽ làm VBA sẽ nhẹ hơn
ANH TUẤN

Dạ đúng rồi Anh. Với dữ liệu của em, và làm theo công thức Anh ThuNghi thì quả thực là chậm.

Lê Văn Duyệt

 

[anhtuan1066]

Dạ đúng rồi Anh. Với dữ liệu của em, và làm theo công thức Anh ThuNghi thì quả thực là chậm.

Lê Văn Duyệt

Vì ThuNghi chỉ làm thử có mấy chục dòng nên ko có vấn đề... Tôi thử hết toàn bộ dử liệu thì.. ôi thôi.. máy đứng luôn... Đễ tôi xem lại kỷ lần nữa liệu có thể dùng Offset để giảm bớt số dòng quét ko?
ANH TUẤN

 

[Mr Okebab]

Em lỡ dùng VBA rồi (do hiểu sai ý bác) thôi cứ up lên vậy nhé.
Bác Click vào hàng cuối cùng + 1 của cột A nhé.
Hơi bị chậm, em chưa biết cách cải tiến.


(Sao mà nhiều mặt hàng thế !!!!)
Thân

Xin sửa lại là : Bác Click vào hàng cuối cùng + 1 của cột A nhé (tính hàng cuối cùng là theo cột B). Ở VD trên là A6

Xin "xông cảm" cho em!!

Thân!

 

[Mr Okebab]

Xin sửa lại là : Bác Click vào hàng cuối cùng + 1 của cột A nhé (tính hàng cuối cùng là theo cột B). Ở VD trên là A6

Xin "xông cảm" cho em!!

Thân!

Đây là File em đã tối ưu hóa, tuy nhiên cần 2 điều kiện :

1. Mã BOM phải là text
2. Bảng DM của BOM phải được Sort trước theo mã BOM

bác xem rồi cho ý kiến nhé.

Tuy nhiên với gần 36.500 mã thì cũng hơi . . . ngán!!


Thân

 

[levanduyet]

To: Okebab,
Nếu sử dụng VBA thì sẽ thực hiện theo 2 cách sau:
_ Dùng đối tượng Autofilter:
Dựa vào đối tượng Range kết hợp với Autofiter. Sau khi filter chúng ta lấy về kết quả là một mảng. Duyệt qua mảng này rồi xuất ra. (Nhưng chưa thử xem có bị giới hạn hay không! Ví dụ như bình thường nếu các bạn dùng Auto Filter thì chỉ lọc được danh sách khoảng 10,000 dòng mà thôi nếu tôi không nhớ lầm!)
_ Dùng phương thức tìm kiếm giống như file theo dõi phụ tùng mà anh đã đưa lên giải pháp. Cách này thì cũng tương đối nhanh. Chưa thử test thời gian, chỉ cảm giác như thế thôi.

Đối với file của em thì em trích lọc toàn bộ BOM của toàn bộ sản phẩm. Ở đây chỉ yêu cầu là: giả sử tôi có một mã sản phẩm (mã bắt đầu bằng 22), trong BOM này có 2 mã bán thành phẩm (bắt đầu bằng 21) thì chúng ta sẽ liệu kê theo hàng ngang. Thông thường một sản phẩm chỉ có vài (khoảng 3) bán thành phẩm mà thôi. Và yêu cầu nữa là dùng công thức nhưng phải cải tiến về tốc độ. Chứ nếu dùng công thức của bác ThuNghi thì treo máy luôn (giống như bác tuan1066 đã nhận xét).

Chúng ta sẽ không bàn về việc dùng VBA (hi hi hi), chúng ta bàn về việc sử dụng công thức mà thôi.

Thân,

Lê Văn Duyệt

 

[anhtuan1066]

Lúc đầu khi nhận file này tôi dự định thay vì gỏ vào cell A1 mình sẽ dùng Validation... nhưng chẳng hiểu sao khi đặt Name tham chiếu đến 36298 dòng thì Validation chẳng hiện ra... ???
Mày mò mãi mới phát hiện hình như Validation có giới hạn về số dòng... Chính xác số dòng cho phép chỉ có 32767 dòng... Lớn hơn mức đó thì Validation sẽ.. tịt (>2^15)
ANH TUẤN

 

[ThuNghi]

Bác xem qua có đúng yêu cầu? Bom Header (sort), Component (sort theo Bom Header).
File vẫn có > 36.000 row, vẫn bảo tòan DL.

 

[Thien]

Dear Mr Hiếu

- Cái code emcha sao chạy chậm quá nhỉ.
- Code emcha2 lại chạy nhanh.

Có thể giải thích lý do không?.

Thân.

 

[ThuNghi]

Bác Duyệt thấy có OK, file chạy rất nhanh mà, có thể nhanh hơn VBA. Tất cả chỉ lấy theo Côt H trong Sh Bom.
Có thể thay thế
SoLan2=COUNTIF(CodeOff,">"&0)
Bằng
SoLan2=Max(CodeOff)
Có thể bỏ luôn dòng 1 (1,2,3) thay bằng
=IF(MOD(COLUMN()+1,3)=0,3,MOD(COLUMN()+1,3)) xem lại First Column
Đang nghiên cứu thu gọn cột G và H trong Sh Bom và có gắng sẽ không phải dùng Sumproduct (chậm).

 

[Mr Okebab]

Dùng công thức cũng tốt, tuy nhiên cứ thử nhập liệu đến hàng thứ 1000 trở đi xem (tức là đã có 1000 hàng có công thức), thì thấy ngay thế nào.

Mong bác LVD cân nhắc!!

Thân!

 

[levanduyet]

To: Bác ThuNghi,

Cái CodeOff, BOMOff, Solan là bác đặt tên cho vùng nào vậy?

To:Okebab,
Đúng là chậm khi mình nhập vào. Nhưng anh đang "ngâm cứu cái công thức" nên đành chấp nhận vậy!

Tks
LVD

 

[ThuNghi]

CodeOff, BOMOff, Solan là bác đặt tên cho vùng nào vậy?

BomOff, CodeOff là những vùng lấy từ BomDetail
BomOff=OFFSET(BomDetail,DongDau,0,SoLan,6) chỉ là giới hạn cái vùng BomDetail bắt đầu từ dongdau và lấy bao nhiêu dòng (solan)
Trong đó solan là =COUNTIF(BomH,CONGTHUC!$A4) đếm bao nhiêu lần thỏa DK mà BomHeader = CONGTHUC!$A4, chỉ tại dòng 4 thì lấy theo A4, dòng i lấy theo Ai
Chả biết giải thích thế nào, chẳng qua là thu gọn công thức = define name thôi.
Còn cột G cũng có thể bỏ vì o cần, lúc đó cột H sẽ là
=IF(AND(($A2=$A1),LEFT($C2,2)="21"),$H1+1,0)
Giảm bớt dung lượng mà nhanh hơn. Cứ thêm cột phụ để tránh sumproduct là tốc độ cải thiện ngay. Sau đó rút gọn bớt cột phụ.
Hôm nay được HD cao thủ, nên hơi bối rối.

 

[levanduyet]

Cứ thêm cột phụ để tránh sumproduct là tốc độ cải thiện ngay. Sau đó rút gọn bớt cột phụ.

Đúng vậy. Em sợ cái thằng SumProduct, hôm trước em có làm cho người ta dùng cái công thức SumProduct nhưng người ta la quá.

Cám ơn Anh.

LVD

 

[ThuNghi]

Cũng từ file của Bác Duyệt, nếu chỉ có BomHeader sort còn component không sort thì làm thế nào, các bạn cùng tham gia để cùng nghiên cứu về các ct của Excel.
Vẫn tôn trọng định dạng của file.

 

[anhtuan1066]

Tôi cũng từng nghĩ qua về chuyện dử liệu chưa sort... tất nhiên đơn giản nhất là dùng 1 cột phụ như ta vẩn làm... Có điều dử liệu của Duyệt nhiều quá, nếu dùng cột phụ thì công thức đi qua vài chục ngàn dòng... lúc đó chắc chắn sẽ treo máy ngay...
Dử liệu đã sort rồi thì tương đối dễ hơn 1 chút vì ta có thể dùng MATCH, COUNTIF và OFFSET đễ định vị dc mãng...
Khó đấy!
ANH TUẤN

 

[levanduyet]

Tôi cũng từng nghĩ qua về chuyện dử liệu chưa sort... tất nhiên đơn giản nhất là dùng 1 cột phụ như ta vẩn làm... Có điều dử liệu của Duyệt nhiều quá, nếu dùng cột phụ thì công thức đi qua vài chục ngàn dòng... lúc đó chắc chắn sẽ treo máy ngay...
Dử liệu đã sort rồi thì tương đối dễ hơn 1 chút vì ta có thể dùng MATCH, COUNTIF và OFFSET đễ định vị dc mãng...
Khó đấy!
ANH TUẤN

Vậy giải pháp cuối cùng là :
_ Tùy thuộc vào số record mà quyết định giải pháp VBA hoặc Công thức.
_ Vậy chúng ta có thể lấy giá trị 10,000 record làm chuẩn được không?

To: anhtuan1066,
Theo ý anh, anh có thể cập nhật lại file của anh ThuNghi và upload được không ạ?

Lê Văn Duyệt

 

[anhtuan1066]

Cách làm của tôi thì cũng gần giống ThuNghi... với điệu kiện dử liệu đã dc sort... nếu ko thì đành bó tay (vì dử liệu quá nhiều phải làm cột phụ thì file nặng lắm)
Ý tôi muốn nói rằng khi dử liệu dc sort thì ta ko phải thêm cột phụ và công thức, như vậy đứng về mặc tốc độ xử lý là co thể chấp nhận dc
ANH TUẤN

 

[ThuNghi]

Chỉ sợ calculating lúc nhập DL thì có chậm, còn nếu trích xuất thì vô tư. Nếu giảm còn 10.000 dòng thì nhập DL nhanh hơn, tối ưu nhất là dùng f9 khi nhập DL.
Tôi thử làm 1 ct nếu component không sort, tại G2
=IF($C2="",0,IF(LEFT($C2,2)*1<>21,0,COUNTIF(MangDo,">="&2100000000)-COUNTIF(MangDo,">="&2200000000)))
Trong đó:
mangdo:=INDIRECT("C"&FRow+1&":c"&ROW())
FRow:=MATCH(BOM!$A2,BomH,0): Tìm dòng đầu thấy BomHeader
Đây là file nghiên cứu công thức.

 

[levanduyet]

Chỉ sợ calculating lúc nhập DL thì có chậm

Vâng, như em nói ở trên đó là lý do tại sao người dùng k chấp nhận.

LVD

 

[Mr Okebab]

Dear Mr Hiếu

- Cái code emcha sao chạy chậm quá nhỉ.
- Code emcha2 lại chạy nhanh.

Có thể giải thích lý do không?.

Thân.

Cách thứ nhất chậm vì mình dùng chỉ dùng vòng lặp, nó phải chạy hết 1 vòng thì mới dừng lại
Còn cách thứ 2 thì dùng công thức của Excel, mặc dù công thức này (countif) cũng là vòng lặp nhưng nó đã được biên dịch ra mã máy nên tốc độ cực nhanh.

Đấy chính là sự khác nhau giữa 2 Sub.

Thân!

 

[Mr Okebab]

Vậy giải pháp cuối cùng là :
_ Tùy thuộc vào số record mà quyết định giải pháp VBA hoặc Công thức.
_ Vậy chúng ta có thể lấy giá trị 10,000 record làm chuẩn được không?

To: anhtuan1066,
Theo ý anh, anh có thể cập nhật lại file của anh ThuNghi và upload được không ạ?

Lê Văn Duyệt

Tất nhiên em không phủ nhận cái hay của công thức. Nhưng cái em muốn anh nghĩ đến là sự tối ưu, sự hiệu quả cơ. Bằng cách nào cũng được nhưng phải hiệu quả.

Nếu bằng công thức thì việc nhập liệu cũng không đến nỗi khó khăn đâu, ta không phải nhấn F9 để Calculate mà có thể dùng sự kiện Worksheet_change để tính toán vùng chỉ định (Offset).

Tuy nhiên, nếu danh mục nhiều cỡ đó thì nhất định cái nhập liệu cũng . . . quá trời. Khi đó em dám chắc là File đó phải cỡ . . . vài chục Mb (vì toàn công thức tham chiếu thôi)
Như vậy về dung lượng và tính toán sẽ cực kỳ khó khăn cho anh.

Còn VBA ?? Tại sao anh lại không thích nhỉ ?? Miễn là hiệu quả là được. Còn lại thì đâu có ảnh hưởng gì đâu.

Chỉ sợ calculating lúc nhập DL thì có chậm, còn nếu trích xuất thì vô tư. Nếu giảm còn 10.000 dòng thì nhập DL nhanh hơn, tối ưu nhất là dùng f9 khi nhập DL.

Việc tính toán đã được chia thành 2 công đoạn : Lúc nhập liệu và lúc truy xuất. Nếu lúc nhập liệu chưa Calculate thì lúc truy xuất sẽ . . . cực chậm. Còn nếu đã tính toán rồi thì nó sẽ nhanh hơn rất nhiều (vì đã được giảm tải)

Thân!

 

[levanduyet]

Còn VBA ?? Tại sao anh lại không thích nhỉ ?? Miễn là hiệu quả là được. Còn lại thì đâu có ảnh hưởng gì đâu.

Anh thích chứ sao không? Nhưng muốn thử sức với cái công thức đó mà và qua đó ta biết được cái giới hạn của công thức.

Còn file anh upload lên, đó chỉ là file mình xuất ra từ SAP mà thôi.

Lê Văn Duyệt

 

[june226]

chào các anh chị!
Em dc sếp giao nghiên cứu phương pháp này ma em ko biết bắt đầu từ đâu, anh chị giúp em với ạ!

 

[vanle33]

chào các anh chị!
Em dc sếp giao nghiên cứu phương pháp này ma em ko biết bắt đầu từ đâu, anh chị giúp em với ạ!

Bạn muốn nghiên cứu "Trích dữ liệu từ bảng tổng hợp BOM"?
Bắt đầu từ việc tải file và nghiên cứu cách sử dụng và thử làm + ...