Trợ giúp code VBA để làm nhẹ thời gian tính toán của bảng tính (1 người xem)

[andyminh]

Chào các Anh/Chị GPE
Mình có một bảng tính theo dõi hồ sơ hợp đồng các năm và chi tiết một số mục như file đính kèm. Tuy file không nặng lắm, nhưng khi xử lý thì chạy rất chậm, thường là xuất hiện dòng calculating (4 pro...) rồi chạy phần % xong mới tính đc. Mình nghĩ do công thức mình dài dòng và rắc rối nên xử lý chậm.

Nay nhờ các Anh/Chị GPE hướng dẫn giúp mình chuyển công thức về VBA để thực hiện bảng tính được nhanh hơn.
Công thức của mình chủ yếu là hàm if và hàm vlookup.
Mong nhận được sự giúp đỡ của các Anh/Chị
Mình chân thành cảm ơn.

 

[giaiphap]

Sao gặp quảng cáo thứ này hoài vậy trời.

 

[andyminh]

Sao gặp quảng cáo thứ này hoài vậy trời.

Không phải quảng cáo đâu, trước mình thấy có bài viết trên GPE, thấy đẹp, mình lấy về chỉnh sửa để làm file theo dõi cho công ty mình thôi

 

[giaiphap]

Bạn up cái file một mâm dữ liệu thế này ai mà giúp cho nỗi, bạn nên nêu cụ thể một góc nhỏ nào đó trong file, sau đó dựa vào trợ giúp bạn sẽ tự áp dụng cho những cái khác.

 

[phuyen89]

Sao gặp quảng cáo thứ này hoài vậy trời.

Em thấy đẹp mà anh giaiphap, có điều không biết xoáy vào đâu, để sửa lỗi thôi.

 

[giaiphap]

Em thấy đẹp mà anh giaiphap, có điều không biết xoáy vào đâu, để sửa lỗi thôi.

Ủa tôi đâu có nói xấu đâu.

 

[andyminh]

Em thấy đẹp mà anh giaiphap, có điều không biết xoáy vào đâu, để sửa lỗi thôi.

ở các sheet, mấy cột mình tô màu, mình để công thức if, vlookup để liên kết dữ liệu của các sheet với nhau, nhưng do công thức rắc rối hay liên kết không theo thứ tự mà file khi truy xuất dữ liệu chạy chậm lắm. Mình nghe nói chuyển công thức qua code VBA thì xử lý nhẹ hơn, nên mình nhờ GPE xem có cách nào chuyển đổi ko.
Dữ liệu file hiện tại đến 3 năm lận, nhưng do là thông tin của cty nên mình chỉ để mẫu một số cái để GPE xem thử

 

[andyminh]

Bạn up cái file một mâm dữ liệu thế này ai mà giúp cho nỗi, bạn nên nêu cụ thể một góc nhỏ nào đó trong file, sau đó dựa vào trợ giúp bạn sẽ tự áp dụng cho những cái khác.

ví dụ như sheet "DH" , sheet "TTr" cột số ký hiệu văn bản, sheet "HDKT" cột số hợp đồng

 

[tranhungdao12a3]

File này của bạn đặt quá nhiều data validation cho những vùng dữ liệu chưa dùng đến. Dùng nhiều link, đặt vô số name, công thức thì cũng tương đối. Nên chậm là phải thôi.
Tôi nghĩ vũng dữ liệu nào chưa dùng đến thì không cần tham chiếu nhiều công thức đến nó, xài đến đâu bổ sung đến đó.

 

[andyminh]

File này của bạn đặt quá nhiều data validation cho những vùng dữ liệu chưa dùng đến. Dùng nhiều link, đặt vô số name, công thức thì cũng tương đối. Nên chậm là phải thôi.
Tôi nghĩ vũng dữ liệu nào chưa dùng đến thì không cần tham chiếu nhiều công thức đến nó, xài đến đâu bổ sung đến đó.

do mình chỉ gửi mẫu ít dòng thôi, chứ thực ra file dữ liệu lưu tận 3 năm lận nên vùng dữ liệu mới nhiều đến vậy.
File gốc thì dung lượng có 1mb thôi, nhưng mà xử lý chậm, mình nghĩ chỉ là do công thức nhiều khi dữ liệu lưu nhiều hơn.
với lại các công thức là mình tham khảo của các bài viết trên GPE và google nên nhiều công thức có thể viết chưa được tối ưu.

 

[be_09]

Sử dụng cùng một nội dung mà làm nhiều sheet quá, File dung lượng lớn là do màu mè và dùng nhiều Shapes quá nên nặng là phải.

 

[andyminh]

Sử dụng cùng một nội dung mà làm nhiều sheet quá, File dung lượng lớn là do màu mè và dùng nhiều Shapes quá nên nặng là phải.

Hi bạn, mình cần nhiều sheet để dể quản lý, và theo dõi thông tin. Với lại đây là mình không nói dung lượng lớn (vì thực chất file nặng chỉ có 1 MB mà thông tin lưu trữ của mình hơn 3 năm. ý mình là khi sử dụng truy xuất thông tin, file tính toán chậm, vậy thôi.

 

[Lê Duy Thương]

Hi bạn, mình cần nhiều sheet để dể quản lý, và theo dõi thông tin. Với lại đây là mình không nói dung lượng lớn (vì thực chất file nặng chỉ có 1 MB mà thông tin lưu trữ của mình hơn 3 năm. ý mình là khi sử dụng truy xuất thông tin, file tính toán chậm, vậy thôi.

Bạn chọn tất cả các cells trong sheet. Sau đó chọn clear format thử xem. Sau đó lưu và đóng file lại.
Tiếp tục mở file vừa lưu để thấy được file tính toán chậm do đâu

 

[HieuCD]

Chào các Anh/Chị GPE
Mình có một bảng tính theo dõi hồ sơ hợp đồng các năm và chi tiết một số mục như file đính kèm. Tuy file không nặng lắm, nhưng khi xử lý thì chạy rất chậm, thường là xuất hiện dòng calculating (4 pro...) rồi chạy phần % xong mới tính đc. Mình nghĩ do công thức mình dài dòng và rắc rối nên xử lý chậm.

Nay nhờ các Anh/Chị GPE hướng dẫn giúp mình chuyển công thức về VBA để thực hiện bảng tính được nhanh hơn.
Công thức của mình chủ yếu là hàm if và hàm vlookup.
Mong nhận được sự giúp đỡ của các Anh/Chị
Mình chân thành cảm ơn.

ngoài các nguyên nhân như chú trọng hình thức, format quá nhiều, các object .... còn do các cột có hàm Match(0,...) hoặc Index(...) là hàm mảng, dùng cột phụ sẽ nhẹ rất nhiều

 

[andyminh]

ngoài các nguyên nhân như chú trọng hình thức, format quá nhiều, các object .... còn do các cột có hàm Match(0,...) hoặc Index(...) là hàm mảng, dùng cột phụ sẽ nhẹ rất nhiều

Hàm match với index mình lấy từ công thức cũ của file đó, rồi sửa chữa lại, Bạn có thể hướng dẫn mình cách chỉnh sửa công thức đó = cách thêm cột phụ được không?

 

[HieuCD]

Hàm match với index mình lấy từ công thức cũ của file đó, rồi sửa chữa lại, Bạn có thể hướng dẫn mình cách chỉnh sửa công thức đó = cách thêm cột phụ được không?

- các Name đặt trùng tên khá nhiều, xóa những tên bị trùng
- Name: BoDau... là công thức mảng khá nặng
làm cho bạn sheet: CTKH các sheet khác bạn theo mẫu làm theo

 

[andyminh]

- các Name đặt trùng tên khá nhiều, xóa những tên bị trùng
- Name: BoDau... là công thức mảng khá nặng
làm cho bạn sheet: CTKH các sheet khác bạn theo mẫu làm theo

Sheet CTHD mình làm được rồi, tính toán cũng nhẹ hơn hẵn, nhưng về công thức ở cột tổng hợp hợp đồng (BAOCAOTH), bạn giúp mình công thức sao để chỉ liệt kê các hđ theo năm cụ thể tại ô J6 đc không?

 

[HieuCD]

Sheet CTHD mình làm được rồi, tính toán cũng nhẹ hơn hẵn, nhưng về công thức ở cột tổng hợp hợp đồng (BAOCAOTH), bạn giúp mình công thức sao để chỉ liệt kê các hđ theo năm cụ thể tại ô J6 đc không?

tạo cột phụ sheet HDKT, ô P12 nhập công thức và copy xuống các ô dưới

=IF(YEAR(I12)=BAOCAOTH!$J$6,MAX($P$10:P11)+1,"")

tạo cột phụ sheet BAOCAOTH, ô N10 nhập công thức

=IFERROR(MATCH(ROWS($1:1),HDKT!$P$1:$P$243,0),"")

copy xuống
dùng công thức tương tự sheet CTKH cho các các ô còn lại

 

[andyminh]

tạo cột phụ sheet HDKT, ô P12 nhập công thức và copy xuống các ô dưới

=IF(YEAR(I12)=BAOCAOTH!$J$6,MAX($P$10:P11)+1,"")

tạo cột phụ sheet BAOCAOTH, ô N10 nhập công thức

=IFERROR(MATCH(ROWS($1:1),HDKT!$P$1:$P$243,0),"")

copy xuống
dùng công thức tương tự sheet CTKH cho các các ô còn lại

Làm phiền bạn xíu nữa, chổ cột thanh toán, lấy thông tin bên sheet CT thì làm sao để đưa vào bảng tổng hợp đó vậy

 

[HieuCD]

Làm phiền bạn xíu nữa, chổ cột thanh toán, lấy thông tin bên sheet CT thì làm sao để đưa vào bảng tổng hợp đó vậy

bạn phải thống nhất cách nhập số hợp đồng của sheet CT với các sheet khác mới tính được theo công thức

K10 =IF(N10="","",SUMIF(CT!$E$11:$E$231,D10,CT!$K$11:$K$231))