Giúp đỡ tăng tốc file khi nhiều công thức mảng (1 người xem)

[LYSM]

Em chào các anh chị,
Em có file dữ liệu để tổng hợp số liệu theo ngày chẵn, lẻ và một số công thức tính bình quân gia quyền. Do file có nhiều công thức mảng và lượng dữ liệu lớn nên rất ỳ ạch. Nhờ các anh chị xem giúp em có thể tăng tốc file hoặc chuyển sang VBA giúp em. Từ cột O đến cột AJ là phần nhập dữ liệu. Phần từ cột C đến M là phần kết quả. Trên đây em lấy ví dụ là 2 đầu mã số, mỗi đầu mã số được thiết lập ngày chẵn hoặc lẻ bởi cell A4 và A40. Các anh chị xem công thức dễ hơn nghe em miêu tả (em dốt văn quá ạ)
quên mất, còn 1 sheet "chất lượng" nữa là sheet vlookup cũng nhiều công thức
Em cảm ơn các anh chị

 

[LYSM]

Hic, không ai giúp em vụ này à (

 

[nhapmon]

Hic, không ai giúp em vụ này à (

theo tôi thì bạn nên hỏi từng ý một thôi, giải thích rỏ ràng yêu cầu của từng công việc.
chứ bi giờ mà đọc một công thức thôi (chứ đừng nói là đọc hết) để hiểu là phê lắm rồi,mà có hiểu được thì mới có thể áp dụng cthức khác hoặc vba.

vì vây bạn cứ nêu yêu cầu từng điểm một, rồi từ từ ráp lại

 

[LYSM]

theo tôi thì bạn nên hỏi từng ý một thôi, giải thích rỏ ràng yêu cầu của từng công việc.
chứ bi giờ mà đọc một công thức thôi (chứ đừng nói là đọc hết) để hiểu là phê lắm rồi,mà có hiểu được thì mới có thể áp dụng cthức khác hoặc vba.

vì vây bạn cứ nêu yêu cầu từng điểm một, rồi từ từ ráp lại

Vâng, vậy để em giải thích từng phần một
Tại các cột J, K, L là công thức tính bình quân gia quyền, đối với 1 mã số có 2 phần là chất lượng và sản lượng. Phần sản lượng (SL) là vùng từ cột O đến AE, phần chất lượng (CL) là vùng từ cột AG đến AJ.
Đối với 1 mã, chất lượng bình quân gia quyền được tính = (CL1*SL1+CL2*SL2+...CLn*SLn)/(SL1+SL2+...SLn). Ở đây CL1,2,3..là các lần của các ngày khác nhau và SL1,2,3.. là sản lượng tương ứng với chất lượng.
Lấy ví dụ mã PL002 có 2 kết quả chất lượng là
PL002 C04.07 có các chỉ tiêu: SNF=8.27, %béo=3.19 (cell(AH,5) và cell(AI,5))
PL002 S05.07 có các chỉ tiêu: SNF=8.37, %béo=3.48 (cell(AH,6) và cell(AI,6))
tương ứng với 2 kết quả chất lượng trên có các sản lượng C04.07=30.7 (cell(AC,5)) và S05.07=29.8(cell(AD,5)) (C04.07 là buổi chiều ngày 04 tháng 7)
Vậy công thức bình quân gia quyền đối với chỉ tiêu SNF ở cell(J,5) là (8.27*30.7+8.37*29.8)/(30.7+29.8)=8.32

nên tại cell(J,5) em áp dụng công thức:IF(SUMPRODUCT((COUNTIFS($AG$5:$AG$2500,$B5&" "&$R$2:$AE$2,AH$5:AH$2500,">0"))*($R5:$AE5))=0,"",ROUND(SUMPRODUCT((NOT(ISNA(MATCH($AG$5:$AG$2500,$B5&" "&$R$2:$AE$2,0))))*(AH$5:AH$2500)*($AK$5:$AK$2500))/SUMPRODUCT((COUNTIFS($AG$5:$AG$2500,$B5&" "&$R$2:$AE$2,AH$5:AH$2500,">0"))*($R5:$AE5)),2))
bác xem có công thức nào ngắn hơn không ạ. bác có thể qua link http://www.giaiphapexcel.com/forum/...Giúp-đỡ-công-thức-tính-trung-bình-có-trọng-số để hiểu rõ hơn, công thức này do thầy ndu..giúp em ạ

 

[nhapmon]

công thức do thầy ndu giải thì chắc chỉ có thầy mới tối ưu nó được thui.................bạn hỏi làm sao mình rút ngắn được nữa..............
mình thử dzọc vba nếu được

 

[LYSM]

Phần tính tổng sản lượng theo ngày nhập chẵn, lẻ:
Các mã trên được nhập vào kho 1 các buổi sáng+chiều của từng ngày, nhưng từ kho 1 sang kho 2 lại nhập 1 ngày/lần hoặc 2 ngày/lần. nếu nhập 1 ngày 1 lần thì sản lượng tổng bằng sản lượng chiều hôm trước + sáng hôm sau-sản lượng ứng với ngày đã có chất lượng (nếu có)
Ví dụ mã PL002: (ở đây chỉ quan tâm đến tần suất nhập vào kho 2)
* Nếu mã này được nhập 1ngày/lần (tương ứng với cell(A,4)=2)
thì ngày 05/07 (cell(I,5)) sản lượng tổng = sản lượng C04.07 (cell(AC,5))+sản lượng S05.07 (cell(AD,5))-phần sản lượng ứng với chất lượng (cell(AK,5)+cell(AK,6))=0

* Nếu nhập 2 ngày/lần vào ngày lẻ (ứng với cell(A,4)=1)
Sản lượng tổng của ngày 05.07 =tổng sản lượng(C03.07+S04.07+C04.07+S05.07) (sum(AA5:AD5))-phần sản lượng tương ứng với chất lượng (Sum(AK5:AK6)=31.5
với các ngày khác không có chất lượng thì không phải trừ đi phân sản lượng tương ứng đó

* Nếu nhập 2 ngày/lần vào ngày chẵn (ứng với A4=0)
Sản lượng tổng của ngày 04.07 (ngày 05.07 tính sang ngày 06.07) =tổng sản lượng (C02.07+S03.07+C03.07+S04.07) (Sum(Y5:AB5))-phần sản lượng tương ứng với chất lượng (bây giờ thì không có) =0+31.5+0+31.5=63
Các công thức ở hàng 4 cũng tương tự nguyên lý trên nhưng là tổng của sản lượng tất cả các mã/ngày (từ PL002 đến PL028) và không có phần trừ đi sản lượng ứng với chất lượng ạ.

Mong các bác giúp đỡ

 

[LYSM]

công thức do thầy ndu giải thì chắc chỉ có thầy mới tối ưu nó được thui.................bạn hỏi làm sao mình rút ngắn được nữa..............
mình thử dzọc vba nếu được

Vâng, mục đích của em là giảm thời gian tính toán của file nếu chuyển được VBA thì càng tốt ạ

 

[nhapmon]

Vâng, vậy để em giải thích từng phần một

PL002 C04.07 có các chỉ tiêu: SNF=8.27, %béo=3.19 (cell(AH,5) và cell(AI,5))
PL002 S05.07 có các chỉ tiêu: SNF=8.37, %béo=3.48 (cell(AH,6) và cell(AI,6))
tương ứng với 2 kết quả chất lượng trên có các sản lượng C04.07=30.7 (cell(AC,5)) và S05.07=29.8(cell(AD,5)) (C04.07 là buổi chiều ngày 04 tháng 7)
Vậy công thức bình quân gia quyền đối với chỉ tiêu SNF ở cell(J,5) là (8.27*30.7+8.37*29.8)/(30.7+29.8)=8.32

ủa mà hình như bạn tính mà ko sử dụng kết quả có sẳn.
chẳng hạn như công thức bên trên, đã có cột sản lượng được tính ở cột AK, bạn ko sử dụng mà phải tính lại trong cthức
ví dụ cthức J5 có thể sữa thành
J5==IFERROR(SUMPRODUCT((LEFT($AG$5:$AG$64,5)=$B5)*(AH$5:AH$64)*$AK$5:$AK$64)/SUMPRODUCT((LEFT($AG$5:$AG$64,5)=$B5)*$AK$5:$AK$64),"")

kéo qua phải, kéo xuống
thêm một điểm nữa.
bạn hãy đặt name động cho vùng "nhập chất lượng" AG:AK,
rồi đưa vào cthức trên--->như vậy trong cthức sẻ giảm bớt số dòng trống ko cần thiết (trong công thức cũ của bạn tới 2500dòng)

bạn thử áp dụng cho các cthức khác xem có cải thiện tốc độ tính ko

 

[LYSM]

ủa mà hình như bạn tính mà ko sử dụng kết quả có sẳn.
chẳng hạn như công thức bên trên, đã có cột sản lượng được tính ở cột AK, bạn ko sử dụng mà phải tính lại trong cthức
ví dụ cthức J5 có thể sữa thành
J5==IFERROR(SUMPRODUCT((LEFT($AG$5:$AG$64,5)=$B5)*(AH$5:AH$64)*$AK$5:$AK$64)/SUMPRODUCT((LEFT($AG$5:$AG$64,5)=$B5)*$AK$5:$AK$64),"")

kéo qua phải, kéo xuống
thêm một điểm nữa.
bạn hãy đặt name động cho vùng "nhập chất lượng" AG:AK,
rồi đưa vào cthức trên--->như vậy trong cthức sẻ giảm bớt số dòng trống ko cần thiết (trong công thức cũ của bạn tới 2500dòng)

bạn thử áp dụng cho các cthức khác xem có cải thiện tốc độ tính ko

Vâng, vậy còn cthuc ở #6 nữa ạ, bác xem có rút gọn dc ko?

 

[nhapmon]

Vâng, vậy còn cthuc ở #6 nữa ạ, bác xem có rút gọn dc ko?

cthức này nhìn khủng quá...........chịu hỏng nổi...............

tôi chỉnh lại các ở các cột
1- cột J--->L
2- và cột AK, cái nào có thể tránh dùng sumproduct, bạn nên tránh, ví nó là công thức mảng nên khá là nặng nề
3-sử dụng mane động để hạn chế dòng trống trong cthức mảng

bạn thử áp dụng xem có cải thiện được phần nào tốc độ ko

 

[LYSM]

cthức này nhìn khủng quá...........chịu hỏng nổi...............

tôi chỉnh lại các ở các cột
1- cột J--->L
2- và cột AK, cái nào có thể tránh dùng sumproduct, bạn nên tránh, ví nó là công thức mảng nên khá là nặng nề
3-sử dụng mane động để hạn chế dòng trống trong cthức mảng

bạn thử áp dụng xem có cải thiện được phần nào tốc độ ko

hôm nay em cũng đã chuyển năm động như bác nói, khá nhẹ nhàng. Còn bên sheet "chất lượng" toàn các hàm Vlookup bác có cách nào giải quyết không ạ?

 

[LYSM]

Có ai giúp đỡ em nốt công thức ở #6 không ạ?