Làm sao giảm thời gian tính toán của EXCEL?

[TIENLV]

Tôi có một bảng tính EXCEL khá cồng kềnh (do yêu cầu công việc, số lượng dữ liệu và công thức lớn), mỗi khi thay đổi dữ liệu ở 01 CELL thì EXCEL tính toán lại toàn bộ các công thức ở tất cả các SHEET trong bảng tính, thời gian mất khoảng 10 giây, tuy không lớn nhưng gây cảm giác khó chịu.
Có cách nào làm EXCEL chỉ tính toán ở SHEET hiện tại không? Khi chuyển sang SHEET khác thì mới tính toán lại ở SHEET này? Hoặc tăng tốc tính toán?
Xin mọi người chỉ giáo! Cảm ơn nhiều!

 

[duyennt]

Vấn đề của bạn tôi cũng đang gặp phải, tôi tạm sử dụng chế độ tính trong Tool -> Option -> Calculation là Manual, sau khi nhập xong dữ liệu một thời gian tôi lại chuyển sang Automatic để cho Excel tính như vậy sẽ đỡ mất công tính sau mỗi lần sửa dữ liệu, nhưng nó cũng hơi bất tiện. Tôi cũng đang chờ có cao thủ nào chỉ giáo cách khác hay hơn đây.

 

[adam_tran]

Có nhiều nguyên nhân dẩn đến file Excel của bạn chạy chậm.

1. Máy tính cũ: Cái này dễ hiểu rồi
2. CSDL quá lớn: Cách tốt nhất là bạn nên dùng phần mềm chuyên nghiệp hơn
3. Tổ chức CSDL không hợp lý, cồng kềnh: Vấn đề này không dễ nói, hầu hết bạn phải tự rút kinh nghiệm ra qua quá trình làm việc với Excel và tham khảo các file Excel khác. Khi dùng Excel, mỗi năm bạn nên chuyển sang 1 file mới.
4. Công thức cồng kềnh: Thay vì sử dụng quá nhiều công thức mảng, bạn có thể dùng Sumproduct. Vấn đề này bạn tham khảo các bài viết của chị handung107 hoặc gửi message tới chị ấy, bạn sẽ nhanh chóng tìm được topic mình cần. Ngoài ra bạn cũng chỉ nên để 1 dòng công thức đầu tiên thôi, còn lại nên để giá trị, khi cần mới copy xuống. Các Name trong Workbook cũng không nên mở quá nhiều dòng, vừa đủ là được. Setup chế độ Calculation sang Manual cũng là một cách.
V...v
Đây là vấn đề mà nhiều người gặp và kinh nghiệm của mỗi người là khác nhau tùy vào nguyên nhân họ gặp phải! Cách tốt nhất để giải quyết là... làm nhiều đi, đọc nhiều vào!

 

[handung107]

Nếu bạn chịu khó đọc thật kỹ các Topic đã có trên diễn đàn, bạn sẽ thấy những vấn đề bạn adamtran nêu ra :
- Đặt tên và nhãn : hãy chú ý dãy và mảng động : sử dụng hàm OFFSET để đặt tên cho dãy, CSDL của bạn nới rộng đến đâu, tên sẽ nới rộng đến đó. Bạn sẽ đỡ tốn bộ nhớ để đặt sẵn tên cho một vùng lớn. Bạn phải thường xuyên loại bỏ những tên dư thừa đến những vùng tham chiếu không còn tồn tại
-Chỉ sử dụng một hàng chứa công thức : (như bạn adam tran đã nói). Riêng với Excel 2000 trở lên, có tính năng cập nhật công thức. Bạn đọc lại bài 3
http://www.webketoan.com/forum/showthread.php?t=7230
Như thế, bạn không cần cài sẵn công thức làm gì để phí bộ nhớ
- Tận dụng các khả năng có sẵn của Excel, hạn chế cài thêm các Add-in không cần thiết, các Format Conditional để định dạng có điều kiện, hãy thay bằng Data / Validation. Loại Format Conditional tốn rất nhiều bộ nhớ, nhất là với CSDL nhiều hàng và nhiều cột
- Tránh sử dụng công thức mảng, thay bằng SUMPRODUCT
-Thiết kế CSDL cho thật hợp lý, điều này quan trọng nhất không phải chỉ là File Excel mà còn với nhiều File dùng bằng các phần mềm ứng dụng khác
Trên đây, chỉ là vài ý kiến bổ sung, điều quan trọng là bạn hãy dành cho Excel một tình yêu thật sự để khám phá nó. Tôi dám chắc rằng File Excel thiết kế tốt sẽ làm bạn hài lòng hơn nhiều File KT bằng Access, FoxPro...đấy, nhưng kiến thức Excel của bạn phải khá vững vàng để xử lý các tình huống trên Excel

 

[SA_DQ]

Cho hỏi &ề bộ nhớ ca Excel?!

Nếu ít dùng công thức mà chuyển ~ công thức nào chuyển được sang VBA thì Excel có chạy nhanh hơn được không, Qúi &ị?!

 

[adam_tran]

Nếu ít dùng công thức mà chuyển ~ công thức nào chuyển được sang VBA thì Excel có chạy nhanh hơn được không, Qúi &ị?!

Tôi e là không. Tôi cũng thử coding một vài hàm chuẩn của Excel, hoặc coding một số function thay vì dùng công thức tổng hợp trên Array hoặc sumproduct, thì hàm coding từ VBA chạy chậm hơn khoảng 10 lần.
Xem http://webketoan.com/forum/showthread.php?t=8671
Theo lời của anh Tuấnktcdcntt thì các hàm Excel được viết trên ngôn ngữ lập trình bậc thấp (có thể = mã nhị phân) rố tích hợp vào DLL. Còn VBA thì khi chạy nó phải dịch sang mã máy, rồi đưa vào bộ nhớ... mới sử dụng nên chậm hơn. Chưa nói các giải thuật của Microsoft chắc chắn hơn chúng ta nhiều nhiều...

 

[handung107]

Thực ra, Excel là một bảng tính thông minh, bạn hãy tận dụng khả năng của Excel nhiều nhất, trước khi nghĩ đến VBA. Những gì không thể giải quyết được bằng Excel, ta hãy nhờ VBA. Nhiều giải pháp bằng VBA làm chậm khả năng tính toán của Excel hơn bình thường, nhất là phải duyệt qua các vòng lặp, VBA cũng cần dùng đến các tên và nhãn, nên việc tiết kiệm bộ nhớ khi đặt tên cho dãy cũng là cần thiết. Đặc biệt, Excel kết hợp với Query để lọc dữ liệu thật tuyệt vời nhưng đây là lãnh vực rất ít người biết đến, nên nhiều bạn cứ nghĩ chỉ có Access là làm được việc này

 

[Tuanktcdcn]

Giải pháp về tốc độ xử lý bảng tính nhanh

Excel cung cấp cho chúng ta rất nhiều hàm để làm, chúng thuộc các nhóm hàm thống kê, tài chính, tìm kiếm, toán học,...ta không dùng nó một cách độc lập mà thường phối hợp để cho ra những công thức, chúng ta đều đa số tự đắc chiêm ngưỡng thành quả của mình qua các hàm của Excel đó và cứ thế tin tưởng mải miết lập công thức rồi đế một ngày ta nhìn thấy thông báo trạng thái Calculation 1-100% ->Cần có một giả pháp tăng tốc cho Project của mình!
Vấn đề này hết sức quan trọng, nó là một trong những vấn đề quan tâm hàng đầu với bất kỳ ai đang làm việc với Excel một cách nghiêm túc. I cũng đã từng gặp chuyện này như các bạn và cũng đã từng nghĩ hơi "tiêu cực" về Excel nhưng cuối cùng I vẫn yêu nó.
I xin đưa ra một chút kinh nghiệm trong xử lý về tốc độ:
1-Cách chọn Hàm:
Bạn hãy nên chọn hàm nào tối ưu nhất để làm, điều này thật là mù mịt để biết đâu là tối ưu? Chúng ta nên tham khảo mọi người đã làm có kinh nghiệm, có rất nhiều người trong EFC cho ta lời khuyên như chị Handung107 vẫn khuyên mọi người nên dùng Sumproduct(,..) hơn là {=Sum(If(,..))}-công thức mảng, trong quá trình làm rồi ta cũng rút được nhiêm thêm thôi.
I xin đưa thêm cách lựa chọn cho các bạn. Nếu bạn cần lập sổ chi tiết đại loại có dùng hàm trích lọc như Offset, Index, Lookup, Hlookup,Choose,...Các hàm này nếu bạn phải copy cho nhiều dòng thì nó không phải là giải pháp tốt, riêng I thì không dùng các hàm này mà chỉ dùng IF,And,Or sau đó kết hợp với Autofilter tốc độc thực sự nhanh hơn nhiều nhưng đổi lại file của tôi dung lượng lại tốn hơn. Khi dùng các hàm như Index(RowID,ColID,VUNG), Hlookup(Valuefind,VUNG,col,kind),..Ham(,,VUNG..) tức là các hàm phải dùng tham số là một vùng thì mỗi một hàm nó cần phải xử lý quét từng dòng trong VUNG, nếu VUNG mà có nhiều dòng thì càng chậm. Bạn thử hình dung xem nếu một bảng tính, trong một ô I đặt công thức có 1 hàm Index và 1 hàm Hlookup nếu VUNG của ta có 100 dòng, lúc này một công thức trong ô Excel phải quét 2*100=200 lệnh (đại lọai như vậy), công thức này ta lại copy cho 50 dòng vậy tổng lượt quét là 200*50=10000 bạn có thể coi đây la số phép tính. Nếu I dùng hàm IF, And, Or thì I chỉ mất đúng 100 phép tính (100 là số dòng của VUNG), sau đó dùng Autofilter lọc ra những dòng có giá trị. Tại sao chỉ như vậy? Bởi I cùng phép so sánh "ngang bằng" [ =If(VUNGSheet!A1="NHAPKHO",VUNGSheet!C1,0) và copy theo số dòng có trong VUNGSheet, không hề có một VUNG nào tham gia mà chỉ là so sánh dòng=dòng]. Máy tính dù tốt đến mấy, EXCEL dù thông minh đến mấy nhưng ta so sánh số phép tính 100 và 10000 trên cùg một máy tính đó và cùng version của EXCEL thì thấy cái gì là giải pháp.
2-Giữ lại dòng đầu tiên có công thức:
EXCEL làm chúng yêu thích nó một phần là do tính "động đậy". Trong công thức ở Sheet1!A1 mà có Sheet2!A1, trong công thức ở Sheet2!A1 có Sheet3!A2,trong công thức ở Sheet3!A2 có Sheet4!A1 nếu I thay đổi giá trị ở Sheet4!A1 thì tất cả các Sheet có liên quan đều bị thay đổi. Như vậy tại sao mà nó thay đổi giá trị ở các Sheet? Vì EXCEL có sự kiện Automantic-nó sẽ lìm tất cả các mối liên hệ từ thời cụ kỵ đến nay có dòng huyết thống và tính lại tất cả các công thức mà một thành viên có. Trong tình huống trên thì từ các ô từ Sheet1->Sheet3 đều phải tính lại, nếu trong mỗi Sheet mà các ô lại liên kết (Link) với nhau thì bạn hãy lấy máy tính cá nhân để nhân số phép tính mà EXCEL phải tính lại sau mỗi một giá trị thay đổi (Các đối tượng không chung huyết thống thì được bỏ qua). Đó là nguyên nhân tốc độ máy tính chậm!
Một vấn đề như sau: giả sử I có 6 sổ. 1-Sổ chi tiết131;2-Sổ Tổng hợp 131; 3-Sổ cái; 4- Báo cáo Thuế; 5-Cân đối TK;6-Cân đối kế toán các sổ này được liên kết với nhau. Hàng ngày I chỉ quan tâm tới công nợ "131" như thế thì thường xuyên dùng 2 sổ 1-Sổ chi tiết131 và 2-Sổ Tổng hợp 131 còn tất cả các sổ kia I cần xem nó vào cuối tháng. Nếu chúng ta cứ để công thức ở tất cả các sổ thì thật lãng phí vì chúng ta chỉ xem có 2 sổ thôi, vậy nên giữ lại công thức ở 2 sổ còn các sổ khác để lại công thức ở dòng đầu tiên rồi xóa hết công thức ở các dòng dưới-> sẽ tiết kiệm được dung lượng của file và giảm số phép tính mà EXCEL phải Refresh lại sau mỗi giá trị thay đổi->Tốc độ được cải thiện tốt! Khi cần xem số liệu ở các sổ đã xóa bạn chỉ cần copy công thức dòng đầu là xong.
Nếu các báo cáo thì chúng ta nên copy công thức tới các dòng rôi Pastevalue, như vậy vẫn giữ được giá trị mà không gây ảnh hưởng tới tốc độ máy tính vì ta đã ngắt liên kết.
3-Chọn thuộc tính Automantic/Menual trên EXCEL:
Nếu dữ liệu mà ít thì bạn nên để Automantic, khi nhiều thì nên chọn Menual khi nào cần Refresh thì chọn Automantic sau khi được update thì chọn lại Menual.
Để làm được điều nàu bạn vào menu Tools\Options->Calculation

 

[Tuanktcdcn]

Giải pháp về tốc độ xử lý bảng tính nhanh (tiếp)

4-VBA chọn Application.ScreenUpdating=True/False :
Khởi đầu một Sub hay Function bạn nên chọn Application.ScreenUpdating=False lúc này mọi sự thay đổi trên các Cell (ô) tạm thời EXCEL không vẽ ra mà để sức lực cho việc phân tích nhưng các biến hệ thống vẫn nhận giá trị, cuối mỗi Sub hay Function cần phải có Application.ScreenUpdating=True. Việc làm này tưởng đơn giản nhưng trong một tập hợp nhiều dòng lệnh nếu không khéo nó sẽ làm cho kết quả bị sai (thường bị khi Copy công thức rồi Pastevalue/không phải Value thực)
Một cấu trúc mà I vẫn thường dùng:

'=========================
Sub Thutuc()
Dim OldSCR as Boolean
OldSCR=Application.ScreenUpdating

If Application.ScreenUpdating=False then
Application.ScreenUpdating=False
End If

Dòng lệnh 1
..........
Dòng lệnh n

If Application.ScreenUpdating<>OldSCR then
Application.ScreenUpdating=OldSCR
End If

End Sub

'=========================
Sub MainThutuc()
Dim OldSCR as Boolean
OldSCR=Application.ScreenUpdating

If Application.ScreenUpdating=False then
Application.ScreenUpdating=False
End If

Dòng lệnh 1

Thutuc

Lenh 2
..........
Dòng lệnh n

If Application.ScreenUpdating<>OldSCR then
Application.ScreenUpdating=OldSCR
End If

End Sub
'=========================

Như cấu trúc trên thấy trong MainThutuc có Thutuc nhưng khi Thutuc kết thúc nó vấn đảm bảo cho MainThutuc có môi trường ban đầu-Bảo toàn môi trường cũ. Nếu chúng ta không khéo léo thì sẽ làm cho cái này phá cái kia.
5-VBA chọn Application.EnableEvents = True/False :
Application.EnableEvents là biến hệ thống của Excel Application (Excel Property) kiểu Boolean.
Application.EnableEvents =False thì các Sub sự kiện của Excel ngừng hoạt động
Application.EnableEvents =True thì các Sub sự kiện của Excel hoạt động lại bình thường.

Một trong các sub sự kiện quan trọng liên quan tới quá trình biến động của Sheet, Cell trên bảng tính:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Trong các Sub nếu có các lệnh làm thay đổi giao diện, giá trị ô, Select (dịch chuyển Sheet/Cell) lúc đó tất cả các sự kiện nêu trên của EXCEL đều thực hiện. Thật không hay nếu trong các Sub trên lại có các lệnh.
Vậy bạn nên dùng Application.EnableEvents. Bạn có thể dùng theo cách như Application.ScreenUpdating.
I cảnh báo rằng, nếu bạn không xử lý tốt ngữ cảnh thì hệ thông EXCEL của bạn sẽ bị tê liệt toàn bộ lúc đó bạn phải thoát khỏi EXCEL hoặc một cách nào khác như gọi một Sub có lệnh Application.EnableEvents=True (Việc này không dễ thực hiện khi Project đã đóng gói).
6-VBA Xử lý toàn bộ tình huống:
Khi viết Sub/Function một số tình huống ta chưa đưa ra khi đó có thể Excel sẽ báo lỗi "Run-time". Theo cách thông thường người ta dùng bẫy lỗi "On Error ..." nhưng việc này sẽ làm cho tốc độ xử lý của EXCEL bị giảm, dùng "On Error ..." chỉ là giải pháp tùnh thế tạm thời, bạn nên phân tích mọi tình huống sảy ra, cuối cùng mới dùng "On error..."
7-VBA Bộ nhớ bị chiếm dụng :
Khi viết lệnh mọi ngượi thận trọng các việc sau:
Khai báo Biến/Sub/Function: đừng tùy tiện khai báo Public nó sẽ làm cho bộ nhớ bị chiếm dụnh
Dùng biến Object/Class: Khi chúng ta tạo một đối tượng trên VB/VBA dùng lệnh Set Oj=Object/Class, khi đã dùng xong hãy nhớ viết thêm Set Oj=Nothing để hòan trả biến nhớ.
8-VBA Xử lý thuật toán tốt:
Vấn đề này I đã viết trong một topic nào đó rồi các bạn có thể tìm lại.

Trên là một chút kinh nghiệm I rut ra từ bản thân có thể chưa hòa tòa chính xác, mọi người có giải pháp náo xin đưa tiếp ra.

 

[the7habitsman]

Mấy cái Tuân nói đều đúng...trừ cái câu này:

6-VBA Xử lý toàn bộ tình huống:
Khi viết Sub/Function một số tình huống ta chưa đưa ra khi đó có thể Excel sẽ báo lỗi "Run-time". Theo cách thông thường người ta dùng bẫy lỗi "On Error ..." nhưng việc này sẽ làm cho tốc độ xử lý của EXCEL bị giảm, dùng "On Error ..." chỉ là giải pháp tùnh thế tạm thời, bạn nên phân tích mọi tình huống sảy ra, cuối cùng mới dùng "On error..."

Mấy cái On Error... thì ko làm code chạy chậm đến mức ko thể chấp nhận được. Ngôn ngữ lập trình nào cũng nên viết bẫy lỗi. Đó là nguyên tắc lập trình rồi. Ở chỗ tôi ko viết code theo chuẩn thì sẽ bị bắt viết lại (vì bắt buộc phải qua khâu Xem xét mã nguồn (Coding Review) mà)

bạn nên phân tích mọi tình huống xảy ra, cuối cùng mới dùng

Cái này thì lại đúng (Cái đó người ta hay gọi là...vét cạn mọi tình huống xảy ra với từng dòng lệnh mình định viết.

If Application.ScreenUpdating=False then
Application.ScreenUpdating=False
End If

Đoạn này ko hiểu lắm . Đùa thôi. Chắc vội quá Tuân viết nhầm
à, nhân tiện về cái lệnh kiểu If a = True Then hoặc If a = False Then
thì các bạn nên viết như thế này cho nó chuyên nghiệp (ko những chuyên nghiệp mà còn tăng tốc độ, đố các bạn chứng minh được tại sao đấy

If a Then '// tương đương với If a = True Then
If Not a Then '// tương đương với If a = False Then

Thông thường, 1 quá trình tính toán dài, tốn nhiều resource của PC thì hay làm đờ máy vì thực ra trong quá trình chạy đó có rất nhiều thứ đang thực hiện (như refresh (paint) cái màn hình chẳng hạn,...). Chính vì vậy mới có cái tip như Tuân nói.

Thông thường thì người ta hay làm như sau:
1. Dùng 1 lệnh nào đó có trong ngôn ngữ lập trình để "Lock" lại các controls (vì mỗi control thường là 1 window (trừ cái label,...) và các controls đó nó thường Paint liên tục. Trong trường hợp VBA thì Tuân dùng Application.ScreenUpdating=False, còn ở VB thì dùng API, ko nhớ lệnh nào nữa... nhưng search cái chắc ra ngay. Một số Grid như VS FlexGrid cũng có riêng cái method để ko paint grid.
2. Thực hiện 1 (vài) câu lệnh mà tốn nhiều resources như LoadDatatoGrid, to Treview, Listview,... Long time transactions,....

3. Paint lại các window (controls, active form) (trong VBA thì Application.ScreenUpdating=True)

Nếu thấy những điều tớ nói...lằng nhằng phức tạp quá thì bỏ qua nhé. Còn nếu vẫn muốn tìm hiểu thêm thì nên đọc về Windows Programming (có sách này đấy - hoặc vào MSDN mà search)

 

[Tuanktcdcn]

Cảm ơn the7habitsman
If Application.ScreenUpdating<>False then
Application.ScreenUpdating=False
End If
Đói quá nên nhầm!

 

[SA_DQ]

Cách nào để đo thời gian thực thi các công thức?

Trong VBA ta có thể dùng hàm Timer() để đo đếm thời gian từ đầu Sub sau khi đã khai báo biến & gán = Timer() & cuối của Sub: ta tính hiệu của chúng là được; Nhưng &ới bảng tính thì ta là sao đo đếm được chậm nhanh = 1 cách thuyết phục (không ai cãi được) không đây Quý &ị?!
http://diendan.vietnamnet.vn/message_view.asp?forumid=113&msgid=20050726084735554675

 

[Tuanktcdcn]

Trong vấn đề này, nếu những người có CSDL nhỏ hoặc tính móc nối ít thì khó mà nhận biết được. Chúng ta hãy test trên bảng tính nhiều dữ liệu và công thức rồi xem!

 

[Tran Chau]

-Xem thêm về topic nầy tại: http://www.decisionmodels.com, ngoài ra còn có các bài viết về Array, Dynamic Ranges,Sumif ...có cả 1 soft Excel : hình như là fast excel, tuy nhiên khi install thì hỏi password nên...phải mua hoặc nhở *** + free addin :RangeCal: chỉ thực hiện cal trên Range
-j-walk thì có thủ thuật; dựa vào cách chép công thức không làm thay đổi address: Range.Formulas= Range.Formulas. tác giả đã sao lưu các formula thành text; add ‘ or space hay sao lưu các formula vào comment. Khi thực hiện tính toán thì chép ngược lại

 

[Tran Chau]

I thấy trong các utilities Excel của ASAP, PUP... thường để các Sub trong 1 file khác cho nhẹ file.File chính chỉ chứa các report và tạo menu, chạy macro từ wb # có nhiều người viết như cuốn Power 2002 j-walk....

 

[Tuanktcdcn]

Việc đặt các Sub và Function của chương trình vào một file khác còn file chương trình kết nối, nếu chương trình không cho Save thì chỉ có ý nghĩa là khi nhà thiết kế update phiên bản theo thư viện (*.xla) từng thành phần, còn tốc độ không cải thiện được đáng kể. Vì khi khởi động file chương trình nó vẫn phải load *.xla liên quan vào.
Nếu file chương trình cho phep người dùng Save lại thì chương trình này không phải là một Application lớn nên không lo về tốc độ.

 

[alovo]

Thực ra, Excel là một bảng tính thông minh, bạn hãy tận dụng khả năng của Excel nhiều nhất, trước khi nghĩ đến VBA. Những gì không thể giải quyết được bằng Excel, ta hãy nhờ VBA. Nhiều giải pháp bằng VBA làm chậm khả năng tính toán của Excel hơn bình thường, nhất là phải duyệt qua các vòng lặp, VBA cũng cần dùng đến các tên và nhãn, nên việc tiết kiệm bộ nhớ khi đặt tên cho dãy cũng là cần thiết. Đặc biệt, Excel kết hợp với Query để lọc dữ liệu thật tuyệt vời nhưng đây là lãnh vực rất ít người biết đến, nên nhiều bạn cứ nghĩ chỉ có Access là làm được việc này

:food-smil Em rất đồng ý với ý kiến của chi handung, nhân tiện chị cho em hỏi thêm về mấy tính năng của excel như:Goal seek, scenanios,slove và Get external data và đặc biệt cách sử dụng tính năng paste specal picture khi copy đồ thị thay đổi pivotable.