File dùng công thức mảng nên chạy chậm (4 người xem)

[Taphucquang]

Mình có File quản lý kho nhưng dùng công thức mảng nên file chạy chậm . xin các cao nhân chỉ giáo
* trong sheet CT152
tại ô N12 mình dùng công thức mảng {=IFERROR(MATCH(1,('BK-NX'!$G$5:$G$9855=$G$6)*($I$2<='BK-NX'!$C$5:$C$9855>=$E$2),),"")}
tại ô N13 minhd dùng công thức mảng {=IFERROR(MATCH(1,(OFFSET('BK-NX'!$G$5,N12,0):'BK-NX'!$G$9855=$G$6)*($I$2<=(OFFSET('BK-NX'!$C$5,N12,0):'BK-NX'!$C$9855)>=$E$2),0)+N12,"")}
* Trong sheet TH-NXT chủ yếu dùng hàm Sumproduc
nhờ mọi người tư vấn công thức cho file nhẹ hơn hoặc dùng VBA ạ vì dữ liệu hàng ngàn dòng nên máy chạy chậm

 

[aitinhhinh]

Dung power query để tổng hợp sẽ nhẹ hơn rất nhiều, vì công thức nó update real-time được khuếch đại lên bởi các vòng lặp rất chậm.
query khi nào bấm refresh nó mới chạy, nhẹ hơn nhiều

 

[Taphucquang]

Dung power query để tổng hợp sẽ nhẹ hơn rất nhiều, vì công thức nó update real-time được khuếch đại lên bởi các vòng lặp rất chậm.
query khi nào bấm refresh nó mới chạy, nhẹ hơn nhiều

ngoài giải pháp này bạn có ý tưởng gì mới ko

 

[87kilua]

Cần tính cột nào thì bấm F9. Công thức cột đó đến các giá trị công thức liên quan đến cột cần tính. Giá trị phụ thuộc theo cột cần tính

Bài đã được tự động gộp: 15/5/22

Nhìn công thức thì tạm ổn, có 100 mặt hàng là toi rồi. cùng cho giá trị 15483. Cộng dồn nhiều quá ảnh hưởng đến tốc độ tính toán

Bài đã được tự động gộp: 15/5/22

Thay sumproduct bằng sumifs cũng được mà

 

[Taphucquang]

Cần tính cột nào thì bấm F9. Công thức cột đó đến các giá trị công thức liên quan đến cột cần tính. Giá trị phụ thuộc theo cột cần tính
View attachment 275980

Bài đã được tự động gộp: 15/5/22

Nhìn công thức thì tạm ổn, có 100 mặt hàng là toi rồi. cùng cho giá trị 15483. Cộng dồn nhiều quá ảnh hưởng đến tốc độ tính toán
View attachment 275981View attachment 275982

Bài đã được tự động gộp: 15/5/22

Thay sumproduct bằng sumifs cũng được mà

hàm sumifs không giải quyết được số liệu nằm trong khoảng giới hạn thời gian bạn ơi

 

[VetMini]

hàm sumifs không giải quyết được số liệu nằm trong khoảng giới hạn thời gian bạn ơi

Dóc tổ . . . . . . . .

 

[87kilua]

hàm sumifs không giải quyết được số liệu nằm trong khoảng giới hạn thời gian bạn ơi

Sumifs là vua hàm sum. Nó mạnh hơn sumproduct, vì thế microsoft sản xuất ra hàm sumifs ra sau hàm sumproduct. Nếu bạn hiểu về lịch sử phát triển các hàm trong các phiên bản khác nhau. Nó là hàm dành cho dân kế toán. thay thế cho cách viết sumproduct,match

 

[Taphucquang]

thank bạn đã chỉ giáo, vậy với công thức này có cách nào cho nhẹ hơn được không bạn

 

[87kilua]

Có sử dụng hàm cơ sở dữ liệu Dsum hoặc tính năng lọc nâng cao. file nhẹ hơn, hoặc tốc độ tính toán sẽ nhanh hơn

 

[HieuCD]

Mình có File quản lý kho nhưng dùng công thức mảng nên file chạy chậm . xin các cao nhân chỉ giáo
* trong sheet CT152
tại ô N12 mình dùng công thức mảng {=IFERROR(MATCH(1,('BK-NX'!$G$5:$G$9855=$G$6)*($I$2<='BK-NX'!$C$5:$C$9855>=$E$2),),"")}
tại ô N13 minhd dùng công thức mảng {=IFERROR(MATCH(1,(OFFSET('BK-NX'!$G$5,N12,0):'BK-NX'!$G$9855=$G$6)*($I$2<=(OFFSET('BK-NX'!$C$5,N12,0):'BK-NX'!$C$9855)>=$E$2),0)+N12,"")}
* Trong sheet TH-NXT chủ yếu dùng hàm Sumproduc
nhờ mọi người tư vấn công thức cho file nhẹ hơn hoặc dùng VBA ạ vì dữ liệu hàng ngàn dòng nên máy chạy chậm

Chỉnh lại công thức dùng cột phụ cho 10.000 dòng dữ liệu, ngày báo cáo thống nhất các sheet
Xem các công thức trong file ở các ô tô màu cho dể tìm, file thực tế không nên tô màu vì làm nặng file

 

[dinhduy]

Em la

Chỉnh lại công thức dùng cột phụ cho 10.000 dòng dữ liệu, ngày báo cáo thống nhất các sheet
Xem các công thức trong file ở các ô tô màu cho dể tìm, file thực tế không nên tô màu vì làm nặng file

Em lại được học anh cách dùng hàm AGGREGATE, xưa giờ tìm và nghĩ mãi không ra luôn. Cám ơn anh nhiều !

=IFERROR(AGGREGATE(15;6;ROW($1:$10000)/($D$6='BK-NX'!$E$5:$E$10004)/('BK-NX'!$C$5:$C$10004>=$A$5)/('BK-NX'!$C$5:$C$10004<=$E$5);ROWS($1:1));"")

 

[doredore1988]

Mình xem qua file thì thấy hàm Index có vẻ là nhiều nhất, cũng không biết là loại bỏ hàm index có nhẹ hơn ko, nhưng theo mình thì dùng hàm INDIRECT có thể xử lý đc gần hết hàm index trong file của bạn.
Ví dụ của mình để loại bỏ hàm Index:

('CT-152'!D13)
=@IF($N13="","",INDEX('BK-NX'!$D$5:$D$9855,'CT-152'!$N13))

thay bằng =@IF($N13="","",INDIRECT("'BK-NX'!$D"&($N13+4)))

 

[HieuCD]

Mình xem qua file thì thấy hàm Index có vẻ là nhiều nhất, cũng không biết là loại bỏ hàm index có nhẹ hơn ko, nhưng theo mình thì dùng hàm INDIRECT có thể xử lý đc gần hết hàm index trong file của bạn.
Ví dụ của mình để loại bỏ hàm Index:

('CT-152'!D13)
=@IF($N13="","",INDEX('BK-NX'!$D$5:$D$9855,'CT-152'!$N13))

thay bằng =@IF($N13="","",INDIRECT("'BK-NX'!$D"&($N13+4)))

Trích xuất dữ liệu, Index nhẹ hơn các hàm khác rất nhiều

 

[VetMini]

Trích xuất dữ liệu, Index nhẹ hơn các hàm khác rất nhiều

Ngày xưa, Index cũng nằm trong nhóm hàm voilatile.
Khi nhìn nhận khuyết điểm của hàm volatile đối với các file lớn, nhiều biến động, MS viết lại hàm này thành non-volatile. (*1)
Nhiều tay chơi Excel lúc ấy khuyên rằng chỗ nào Indirect và/hoặc Offset có thể thay được bằng Index thì nên thay.

(*1) trong quá trình "viết lại" này, Index cũng được thêm mọt số tính năng khiến nó rất mạnh bạo (powerful). Điển hình là tính năng "cái gì có thể dịch ra thành tham chiếu range thì nó sẽ làm theo".

 

[doredore1988]

Cám ơn các bác về kiến thức nay. Lần đầu tiếp xúc dữ liệu nặng nên mù tịch về hàm voilatile và non-voilatile

 

[Taphucquang]

Trích xuất dữ liệu, Index nhẹ hơn các hàm khác rất nhiều

vậy rốt cuộc trong trường hợp này index và indirect cái nào sẽ hiệu quả hơn, giúp file chạy nhanh hơn

 

[HieuCD]

vậy rốt cuộc trong trường hợp này index và indirect cái nào sẽ hiệu quả hơn, giúp file chạy nhanh hơn

Giời ạ! Mình đã nói rất rỏ rồi mờ

 

[VetMini]

Giời ạ! Mình đã nói rất rỏ rồi mờ

Mình thì không nói rõ.
Nhưng mình chỉ mách cho người siêng, chịu khó thử chứ không thích dọn cỗ tận miệng.
Người khôn ngoan thử mà cũng không nhận ra thì cứ chọn đại, và ghi nhớ cái kia. Lúc nào thấy có vấn đề thì thử chọn lại cái đã bị bỏ qua.