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))

 

[andyminh]

Hi bạn @HieuCD
Mình gặp xíu rắc rối với sheet Báo cáo Nvl (PNNVL), mình sử dụng công thức như công thức bên sheet tổng hợp hợp đồng, đã trích xuất được thông tin của cột " tên nhãn hiệu....), tuy nhiên nó chỉ liệt kê ra trong quý đó, nhưng không gom tên cùng 1 loại lại với nhau VÀ khi gom thì gom cả tổng số lượng và tổng thành tiền luôn. Bạn có thể sửa dùm mình vấn đề này không.

 

[HieuCD]

Hi bạn @HieuCD
Mình gặp xíu rắc rối với sheet Báo cáo Nvl (PNNVL), mình sử dụng công thức như công thức bên sheet tổng hợp hợp đồng, đã trích xuất được thông tin của cột " tên nhãn hiệu....), tuy nhiên nó chỉ liệt kê ra trong quý đó, nhưng không gom tên cùng 1 loại lại với nhau VÀ khi gom thì gom cả tổng số lượng và tổng thành tiền luôn. Bạn có thể sửa dùm mình vấn đề này không.

các công thức chỉnh lại để tạo báo cáo NVL chính, còn vật liệu phụ làm tương tự
sheet NLC

N7 =IF(AND(M7=PNNLC!$H$9,COUNTIF($D$6:D6,D7)=0),MAX($N$5:N6)+1,"")

sheet PNNLC
hàm Vlookup nặng hơn Offset và Index, nhiệm vụ cột phụ là để dùng 2 hàm nầy cho nhẹ file

F18 =IF($L18="","",OFFSET(NLC!D$1,$L18-1,))
G18 =IF($L18="","",OFFSET(NLC!F$1,$L18-1,))

tổng nhiều điều kiện thì dùng hàm SumifS

I8 =SUMIFS(NLC!$G$7:$G$999,NLC!$D$7:$D$999,F18,NLC!$M$7:$M$999,$H$9)
K8 =SUMIFS(NLC!$I$7:$I$999,NLC!$D$7:$D$999,F18,NLC!$M$7:$M$999,$H$9)

 

[andyminh]

các công thức chỉnh lại để tạo báo cáo NVL chính, còn vật liệu phụ làm tương tự
sheet NLC

N7 =IF(AND(M7=PNNLC!$H$9,COUNTIF($D$6:D6,D7)=0),MAX($N$5:N6)+1,"")

sheet PNNLC
hàm Vlookup nặng hơn Offset và Index, nhiệm vụ cột phụ là để dùng 2 hàm nầy cho nhẹ file

F18 =IF($L18="","",OFFSET(NLC!D$1,$L18-1,))
G18 =IF($L18="","",OFFSET(NLC!F$1,$L18-1,))

tổng nhiều điều kiện thì dùng hàm SumifS

I8 =SUMIFS(NLC!$G$7:$G$999,NLC!$D$7:$D$999,F18,NLC!$M$7:$M$999,$H$9)
K8 =SUMIFS(NLC!$I$7:$I$999,NLC!$D$7:$D$999,F18,NLC!$M$7:$M$999,$H$9)

Sao mình nhập vào rồi, mà khi thay đổi quý ii, quý 3 thì ko có kết quả nhỉ, chỉ nhận được quý i, 2015, còn lại các quý khác của năm 2015, 2016,2017 không có kết quả

 

[HieuCD]

Sao mình nhập vào rồi, mà khi thay đổi quý ii, quý 3 thì ko có kết quả nhỉ, chỉ nhận được quý i, 2015, còn lại các quý khác của năm 2015, 2016,2017 không có kết quả

mình quên để ý thời gian, bạn chỉnh lại công thức

N7= =IF(AND(M7=PNNLC!$H$9,COUNTIFS($D$6:D6,D7,$M$6:M6,M7)=0),MAX($N$5:N6)+1,"")

 

[andyminh]

mình quên để ý thời gian, bạn chỉnh lại công thức

N7= =IF(AND(M7=PNNLC!$H$9,COUNTIFS($D$6:D6,D7,$M$6:M6,M7)=0),MAX($N$5:N6)+1,"")

Thanks bạn @HieuCD rất nhiều,

Mình nghe bạn nói công thức Bodau.... rất nặng, bạn có biện pháp thay thế công thức đó ko, hoặc làm cách nào để tạo list chọn tên NVL, tên số hợp đồng từ danh sách mà không để dòng trống nhiều trong list không?

 

[HieuCD]

Thanks bạn @HieuCD rất nhiều,

Mình nghe bạn nói công thức Bodau.... rất nặng, bạn có biện pháp thay thế công thức đó ko, hoặc làm cách nào để tạo list chọn tên NVL, tên số hợp đồng từ danh sách mà không để dòng trống nhiều trong list không?

cụ thể bạn muốn làm gì ở ô nào

 

[andyminh]

Cụ thể là ở sheet HDKT, mình muốn lấy danh sách khách hàng sheet KH làm list chọn để lấy số hđ, và danh sách này không để trống hàng nhiều (vd: ở sheet KH, CỘT D 11: D400 chẵn hạn, mình mới nhập từ D11: D200, khi tạo list validation thì sẽ có khoảng trống từ D200400, như vậy khi chọn tên khách hàng sẽ rất khó),
ý mình muốn là khi tạo list chỉ hiện từ D11200, nếu ở cột D khách hàng mình nhập thêm D201...D210 thì list bên HDKT cũng sẽ hiện đến ô D210.

thêm ý nữa nếu có thể, đó là mình đánh ký tự vào ô G để tìm kiếm tên nhanh trong list danh sách khách hàng chẵn hạn, vì khi danh sách dài, mà muốn tìm thì kéo từng tí một để xem cũng hơi khó.

 

[HieuCD]

Cụ thể là ở sheet HDKT, mình muốn lấy danh sách khách hàng sheet KH làm list chọn để lấy số hđ, và danh sách này không để trống hàng nhiều (vd: ở sheet KH, CỘT D 11: D400 chẵn hạn, mình mới nhập từ D11: D200, khi tạo list validation thì sẽ có khoảng trống từ D200400, như vậy khi chọn tên khách hàng sẽ rất khó),
ý mình muốn là khi tạo list chỉ hiện từ D11200, nếu ở cột D khách hàng mình nhập thêm D201...D210 thì list bên HDKT cũng sẽ hiện đến ô D210.

thêm ý nữa nếu có thể, đó là mình đánh ký tự vào ô G để tìm kiếm tên nhanh trong list danh sách khách hàng chẵn hạn, vì khi danh sách dài, mà muốn tìm thì kéo từng tí một để xem cũng hơi khó.

mình thấy công thức tên List_Kh để tạo data validation cũng tạm ổn, bạn chỉ cần đổi cột A thành cột đúng
để tạo list theo ký tự nhập thì bạn phải mở khóa macro mới làm được

 

[andyminh]

Đổi cột A thành cột đúng là sao bạn

 

[HieuCD]

Đổi cột A thành cột đúng là sao bạn

file hiện tại bạn dùng cột A, trình bày của bạn là cột D, nên mình không biết bạn muốn dùng cột nào

Cụ thể là ở sheet HDKT, mình muốn lấy danh sách khách hàng sheet KH làm list chọn để lấy số hđ, và danh sách này không để trống hàng nhiều (vd: ở sheet KH, CỘT D 11: D400 chẵn hạn, mình mới nhập từ D11: D200, khi tạo list validation thì sẽ có khoảng trống từ D200400, như vậy khi chọn tên khách hàng sẽ rất khó),
ý mình muốn là khi tạo list chỉ hiện từ D11200, nếu ở cột D khách hàng mình nhập thêm D201...D210 thì list bên HDKT cũng sẽ hiện đến ô D210.

 

[andyminh]

file hiện tại bạn dùng cột A, trình bày của bạn là cột D, nên mình không biết bạn muốn dùng cột nào

Cột A là mình coppy dữ liệu từ sheet KH, và sheet HDKT để đưa qua sheet DULIEU, sau đó dùng hàm BODAU để xóa khoảng trắng trong list danh sách cần lấy (đó là list khách hàng, và list số hợp đồng), nhưng theo bạn thì công thức này nặng, nên mình mới muốn bạn giúp mình tối ưu công thức để cho nhẹ xử lý hơn ấy

 

[HieuCD]

Cột A là mình coppy dữ liệu từ sheet KH, và sheet HDKT để đưa qua sheet DULIEU, sau đó dùng hàm BODAU để xóa khoảng trắng trong list danh sách cần lấy (đó là list khách hàng, và list số hợp đồng), nhưng theo bạn thì công thức này nặng, nên mình mới muốn bạn giúp mình tối ưu công thức để cho nhẹ xử lý hơn ấy

hàm BODAU để xóa khoảng trắng trong list danh sách cần lấy ? là gì mình không hiểu, tại sao không tạo list trực tiếp từ sheet khách hàng bỏ luôn sheet DULIEU

 

[andyminh]

hàm BODAU để xóa khoảng trắng trong list danh sách cần lấy ? là gì mình không hiểu, tại sao không tạo list trực tiếp từ sheet khách hàng bỏ luôn sheet DULIEU

Xin lỗi, cái hàm Bodau đó mình cũng ko biết là làm để làm gì nữa ), mình thử cái list_kh, với list_hd thì đã tránh được dòng trắng, giờ chỉ còn 1 cái nữa là có cách nào dùng list_kh vs list_hđ mà khi mình đánh ký tự vào tự lọc những kết quả giống để mình chọn cho nhanh không? kiểu search ấy: VD mình đánh chữ Công ty thì nó chỉ liệt kê chững khách hàng có chữ công ty, mình đánh chữ doanh nghiệp thì chỉ liệt kê tên những doanh nghiệp để chọn nhanh không?

 

[HieuCD]

Xin lỗi, cái hàm Bodau đó mình cũng ko biết là làm để làm gì nữa ), mình thử cái list_kh, với list_hd thì đã tránh được dòng trắng, giờ chỉ còn 1 cái nữa là có cách nào dùng list_kh vs list_hđ mà khi mình đánh ký tự vào tự lọc những kết quả giống để mình chọn cho nhanh không? kiểu search ấy: VD mình đánh chữ Công ty thì nó chỉ liệt kê chững khách hàng có chữ công ty, mình đánh chữ doanh nghiệp thì chỉ liệt kê tên những doanh nghiệp để chọn nhanh không?

bạn đọc lại bài #28

 

[andyminh]

bạn đọc lại bài #28

file mình cũng có sài macro mà, nhưng chỉ sài VBA login khi vào file thôi

 

[HieuCD]

file mình cũng có sài macro mà, nhưng chỉ sài VBA login khi vào file thôi

Bạn search trên diễn đàn có nhiều code cho trường hợp nầy

 

[andyminh]

@HieuCD
hàm vlookup của một mã khách trong sheet HDKT muốn thay = hàm offset đc không vậy bạn

 

[HieuCD]

@HieuCD
hàm vlookup của một mã khách trong sheet HDKT muốn thay = hàm offset đc không vậy bạn

Nếu có thứ tự dòng thì dùng hàm Offset hay Index, trường hợp nầy dùng hàm Vlookup là hợp lý vì chưa biết thứ tự dòng lấy dữ liệu
dĩ nhiên vẫn dùng hàm offset hoặc index được khi lồng với hàm match tìm thứ tự dòng

 

[andyminh]

à được rồi