Cần giúp: Các nguyên nhân làm tăng thời gian tính toán của excel và phương pháp khắc phục

Liên hệ QC

benhxike

Gia Tuấn
Tham gia
20/5/10
Bài viết
10
Được thích
0
Giới tính
Nam
Chào các bạn!
Mình đang làm một file excel về nhập, xử lý và xuất dữ liệu. Bời vì lượng dữ liệu khá lớn nên gây ra tình trạng mỗi khi thay đổi giá trị một ô nào đó điều phải chờ excel chạy tính toán trong 3 đến 4 giây rất bất tiện.
Mình chỉ dùng các hàm trên excel chủ yếu là vlookup, offset, match, index cùng với Conditional Formatting và chức năng Data Validation với các danh sách chọn 2 đến 3 lớp.
PC của mình là Win 7 Ulimate 32 bit, Pentium Dual Core 3 GHz, RAM 2GB
Cho mình hỏi:
1. Những loại hàm tìm kiếm nào tốn thời gian nhất?
2. Những hàm tìm kiếm nếu mình chọn vùng tìm kiếm với số dòng khoảng 2000 thì có làm tăng thời gian xử lý của hàm không?
3. Tính năng Data Validation có gây ra tình trạng chậm file excel không? Và cho mình hỏi nhỏ là chức năng này ở trên Google Trang tính chỉ có thể hoạt động theo Range, có nghĩa là không thể thêm danh sách phụ vào danh sách chính được, có cách nào khắc phục hay không?
4. Chức năng Conditional Formatting có tồn nhiều tài nguyên không?
5. Với phần cứng thì những thông số nào ảnh hưởng đến khả năng tính toán của excel?
6. Giữa 3 công thức ở 3 ô riêng biệt và 1 ô gộp công thức của cả 3 ô thì bên nào tính toán nhanh hơn?
Với những phương pháp mình tìm trên Google thì đa số chỉ giải quyết theo kiểu thủ công nhưng ở đây mình muốn tìm những phương pháp tự động hóa cao (ví dụ như dùng VBA), không mất công sửa công thức vì mỗi khi mình lập công thức đều dung các hàm tìm kiếm để loại bỏ bớt các điều kiện cố định chỉ dùng 1 đến 2 giá trị tìm kiếm cố định nên công thức thường dài 3 đến 4 dùng ở cửa sổ công thức.
Xin cảm ơn!
 
chức năng này ở trên Google Trang tính chỉ có thể hoạt động theo Range, có nghĩa là không thể thêm danh sách phụ vào danh sách chính được, có cách nào khắc phục hay không?
Chưa hiểu cho thêm vào như nào, bạn vẽ hình minh họa được không?

vlookup, offset, match, index cùng với Conditional Formatting và chức năng Data Validation
Xếp thứ tự đại khái (từ siêu rùa tới bình thường):
Conditional Formatting, Data Validation, offset, index, vlookup, match

Cái gì mình gõ vào bảng tính đều làm ảnh hướng tới tốc độ xử lý của máy tính hết.

Ví dụ: Không có công thức nào sứt, chỉ cần chọn 5 cột, gõ số 1 rồi nhấn Ctrl + Enter là đơ luôn máy tính rồi.
 
Bạn xem lại các vùng tham chiếu trong hàm, conditional formating, data validation xem có nhiều quá không, tham chiếu cả cột có khi còn đơ cả máy ấy chứ.
 
Bạn xem lại các vùng tham chiếu trong hàm, conditional formating, data validation xem có nhiều quá không, tham chiếu cả cột có khi còn đơ cả máy ấy chứ.
Đấy chính là vấn đề mình muốn hỏi, các hàm, định dạng và danh sách chọn chỉ gói gọn trong vùng dữ liệu thôi, nhưng vấn đề là vùng dữ liệu lại khá lớn với chiều rộng khoảng 20 cột và chiều cao khoảng 2000 dòng với 10 Sheet dữ liệu như vậy.
 
Chưa hiểu cho thêm vào như nào, bạn vẽ hình minh họa được không?
Trên Google Trang tính là như thế này:
Xác thực dữ liệu.png
Chỉ có thể là "Danh sách từ một dải ô" (VD:A2:A10) hoặc "Danh sách mục" riêng lẻ (VD: a,b,c)
Còn trên Excel 2010 là:
Name Manager.png
Data Validation.png
Có thể dùng các Name trung gian được xác định bởi các hàm để dò tìm theo điều kiện.
 
Chỉ có thể là "Danh sách từ một dải ô" (VD:A2:A10) hoặc "Danh sách mục" riêng lẻ (VD: a,b,c)
Data Validation của Google Sheets mục "Công thức tùy chỉnh" (sao ghét cái phiên bản dịch thế), hiện tại chưa hỗ trợ nhập công thức.

Có cách rất đơn giản là mình điền công thức vào cột phụ, muốn gì cũng được.
Cuối cùng là mình phang cả cột phụ kia vào mục "Danh sách từ một dải ô".
Điều tuyệt vời là Data Validation của Google Sheets sẽ tự động bỏ các cells trống.
(Cái này trên Excel 2019/ 365 cũng mới có).
 
Xếp thứ tự đại khái (từ siêu rùa tới bình thường):
Conditional Formatting, Data Validation, offset, index, vlookup, match

Cái gì mình gõ vào bảng tính đều làm ảnh hướng tới tốc độ xử lý của máy tính hết.

Ví dụ: Không có công thức nào sứt, chỉ cần chọn 5 cột, gõ số 1 rồi nhấn Ctrl + Enter là đơ luôn máy tính rồi.
Cảm ơn về xếp hạng của bạn.
1. Conditional Formatting chỉ dùng để làm đẹp và dò tìm công thức có thể bỏ đi được.
2. Data Validation liên đến sự tiện dụng của công việc nhập dữ liệu nên nếu bỏ đi thì khá bất tiện. Mình cũng chỉ dùng Data Validation đa lớp chọn cho 1 Sheet (30 ô 2 lớp chọn và 60 ô 3 lớp chọn) còn Data Validation 1 lớp chọn cho 35 Sheet (Mỗi Sheet 1 đến 2 ô).
3. offset, index, vlookup, match liên quan đến việc copy paste công thức ở nhưng vùng khác mà không bị sai. Ví dụ nếu dùng các địa chỉ cố định $A2:$A10 thì khi mỗi khi paste nó qua vũng dữ liệu khác phải thay đổi theo, còn nếu dùng công thức: =IFERROR(IFERROR(VLOOKUP($A7;OFFSET('SĐK-Văn'!$U$2:$AK$2000;0;CH$2;1900;17-CH$2);CK$2;FALSE);VLOOKUP($A7;OFFSET('SĐK-Văn'!$AL$2:$BB$1900;0;CH$2;1900;17-CH$2);CK$2;FALSE));"") thì có thể có thể paste nó sang vùng dữ liệu tương tự mà không phải chỉnh sửa công thức. Điều này làm giảm thời gian và công sức của mình khi phải làm việc với khoảng 30 Sheet xử lý dữ liệu như vậy.
 
Data Validation của Google Sheets mục "Công thức tùy chỉnh" (sao ghét cái phiên bản dịch thế), hiện tại chưa hỗ trợ nhập công thức.

Có cách rất đơn giản là mình điền công thức vào cột phụ, muốn gì cũng được.
Cuối cùng là mình phang cả cột phụ kia vào mục "Danh sách từ một dải ô".
Điều tuyệt vời là Data Validation của Google Sheets sẽ tự động bỏ các cells trống.
(Cái này trên Excel 2019/ 365 cũng mới có).
Khi nhập công thức vào 1 ô:
=if(left(C4,2)="12",$Y$2:$Y$46,if(left(C4,2)="11",$Y$47:$Y$93,if(left(C4,2)="10",$Y$94:$Y$132,"")))
thì có thông báo: "Không thể tìm thấy giá trị của mảng", Vậy phải nhập công thức như thế nào hả bạn.
Không thể tìm thấy giá trị của mảng.png
Đúng là chức năng loại bỏ ô trống trong danh sách chọn làm cho công thức phức tạp hơn nhiều.
Cảm ơn bạn.
 
Khi nhập công thức vào 1 ô:
=if(left(C4,2)="12",$Y$2:$Y$46,if(left(C4,2)="11",$Y$47:$Y$93,if(left(C4,2)="10",$Y$94:$Y$132,"")))
thì có thông báo: "Không thể tìm thấy giá trị của mảng", Vậy phải nhập công thức như thế nào hả bạn.
Mình share vào gửi link file nhé. Thế giới bên Google Sheets khác hoàn toàn Excel.

Đúng là chức năng loại bỏ ô trống trong danh sách chọn làm cho công thức phức tạp hơn nhiều.
Chức năng siêu hịn đó mình.
 
Chưa hiểu cho thêm vào như nào, bạn vẽ hình minh họa được không?
...
Lúc đầu tôi cũng toan vào tư vấn. Nhưng ngẫm lại chờ chút, thêm chút dữ liệu nữa xem sao?

Quả nhiên, cuối cùng thì nguyên nhân là "dữ liệu khủng" (mới đầu thì nói 2000 dòng, bi giờ tăng lên 30 sheets)

30 sheets thì đầu tiên là bỏ ác hàm OFFSET. Kế đó cố chuyển các hàm VLookup về Index/Match.
2G RAM thì hơi hẻo. Chỗ nào không bắt buộc phải conditioning formatting thì gỡ bỏ. Màu mè tô tiếc gì đó bỏ tuốt.
 
Lúc đầu tôi cũng toan vào tư vấn. Nhưng ngẫm lại chờ chút, thêm chút dữ liệu nữa xem sao?

Quả nhiên, cuối cùng thì nguyên nhân là "dữ liệu khủng" (mới đầu thì nói 2000 dòng, bi giờ tăng lên 30 sheets)

30 sheets thì đầu tiên là bỏ ác hàm OFFSET. Kế đó cố chuyển các hàm VLookup về Index/Match.
2G RAM thì hơi hẻo. Chỗ nào không bắt buộc phải conditioning formatting thì gỡ bỏ. Màu mè tô tiếc gì đó bỏ tuốt.
1. Vấn đề là nếu bỏ offset thì mỗi sheet mình phải sửa công thức 10 lần, rất rối và dễ sai, ko biết sai chỗ nào => mệt não.
2. Chuyển vlookup về index match mình sẽ thử cảm ơn bạn.
3. Condition Formating thì chắc bỏ hết để tiết kiệm bộ nhớ.
 
1. Vấn đề là nếu bỏ offset thì mỗi sheet mình phải sửa công thức 10 lần, rất rối và dễ sai, ko biết sai chỗ nào => mệt não.
...
OFFSET là hàm volatile (*). Không bỏ được nó thì đừng nói tiếp chuyện giảm thời gian tính toán.

(*) nếu bạn chưa biết "volatile": loại hàm này sẽ tính toán lại mỗi khi có bất cứ thay đổi nhỏ nào trên worksheet. Bất kể thay đổi ấy có liên quan đến nó hay không.
Nếu bạn lâu lâu mới chỉnh sửa một chút thì không sao. Chỉnh sửa đều đặn sẽ chết với nó.
(nhưng đây là tôi nói Excel, Google Sheets thì phải hỏi Google)
 
Lần chỉnh sửa cuối:
OFFSET là hàm volatile (*). Không bỏ được nó thì đừng nói tiếp chuyện giảm thời gian tính toán.

(*) nếu bạn chưa biết "volatile": loại hàm này sẽ tính toán lại mỗi khi có bất cứ thay đổi nhỏ nào trên worksheet. Bất kể thay đổi ấy có liên quan đến nó hay không.
Nếu bạn lâu lâu mới chỉnh sửa một chút thì không sao. Chỉnh sửa đều đặn sẽ chết với nó.
(nhưng đây là tôi nói Excel, Google Sheets thì phải hỏi Google)
Nếu bỏ được mình cũng ko cần hỏi nữa rồi. Ý mình là có giải pháp nào thay thế đượ hay ko. Mình kỳ vọng vba sẽ giải quyết được vấn đề này. Vì mình ko rành vba lắm nên ko tìm được thuật toán giải quyết.
 
Nếu bỏ được mình cũng ko cần hỏi nữa rồi. Ý mình là có giải pháp nào thay thế đượ hay ko. Mình kỳ vọng vba sẽ giải quyết được vấn đề này. Vì mình ko rành vba lắm nên ko tìm được thuật toán giải quyết.
Tại bạn hỏi như sau, tôi mới trả lời.

...
Mình chỉ dùng các hàm trên excel chủ yếu là vlookup, offset, match, index cùng với Conditional Formatting và chức năng Data Validation với các danh sách chọn 2 đến 3 lớp.
...
Nếu không bỏ đợc hàm nào thì nói thẳng ra từ bài #1, đỡ mất công người ta tư vấn không đúng chỗ.
 
Nếu không bỏ đợc hàm nào thì nói thẳng ra từ bài #1, đỡ mất công người ta tư vấn không đúng chỗ.
Cũng không phải là không bỏ được mà nếu có phương pháp thay thể tương đương giúp giảm nhẹ công thức bạn ah. Mình rất thích viết công thức theo kiểu tự động hóa, tự xác định vùng dữ liệu theo điều kiện chứ không phải xác định vùng dữ liệu cố định thủ công.
 
1. Thay ram 2G bằng ram 4G

2. Thấy công thức mới tư vấn được, có thể sử dụng Name hoặc cột phụ.
 
Ngày xưa, Index đưpowcj "hân hạnh" nằm trong nhóm volatile.
Khi bảng tính càng ngày càng lớn và phức tạp ra, MS đã nhìn trước được vấn đề và đổi Index thành hàm cố định.

Vì vậy, Index có thể thay phần lớn các công thức có Offset. Trên nguyên lý vận hành, Index cần nhiều mặt interface hơn cho nên sẽ chậm hơn Offset một chút. Nhưng trên nguyên lý sử dụng lâu dài, Index cố định hơn cho nên sẽ tiết kiệm resrouce của máy hơn. Đường trường tri mã lực, tự suy tính lấy.
 
Web KT
Back
Top Bottom