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
(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.
