Câu hỏi Bảng tính Excel lớn mất quá nhiều thời gian để cập nhật liên kết hoặc tính toán công thức


Tôi có một bảng tính Excel với 5000 hàng và cột cho đến AY (kích thước 12MB). Ngoại trừ sáu cột đầu tiên, phần còn lại chứa vlookups hoặc các công thức khác. Tất cả các vlookups nằm trong một bảng tính Excel riêng biệt. Tôi đã thay đổi cài đặt Excel để cập nhật liên kết theo cách thủ công và tính toán công thức. Bây giờ mỗi lần tôi cố gắng cập nhật các liên kết, Excel bị treo hoặc mất khoảng 15 phút.

Ngay bây giờ tôi đang làm việc trên chia worksheet thành bốn trường hợp; nơi tôi sẽ cập nhật bốn bảng Excel khác nhau theo thứ tự. Nhưng nó yêu cầu sao chép các cột chỉ mục theo cách thủ công sang giai đoạn tiếp theo. Mất 3-4 giờ để cập nhật các liên kết. Hôm qua tôi bắt đầu nhận được thông báo lỗi:

Excel không đủ tài nguyên để hoàn thành thao tác.

Tôi đã không còn lựa chọn nào khác, nhưng phải chia toàn bộ hoạt động thành bốn bước nhỏ đòi hỏi sự can thiệp thủ công.

Tôi nhìn INDEX. Không thực sự áp dụng cho trường hợp của tôi. Bất kỳ ý tưởng về cách tôi có thể làm cho nó được thực hiện một cách nhanh chóng?


4
2017-08-20 19:33


gốc


Điều đó nghe có vẻ giống như một ứng cử viên tốt cho một ứng dụng cơ sở dữ liệu (truy cập ms hoặc khác) chứ không phải là một bảng tính. Excel thực sự không được thực hiện để xử lý một lượng lớn dữ liệu. - Stewbob


Các câu trả lời:


5000 hàng của 50 cột không phải là một trang tính lớn. 12MB là một kích thước tệp vừa và nhỏ hoàn toàn hợp lý và bạn không cần phải làm gì hết sức mạnh mẽ như làm lại cơ sở dữ liệu hoặc một cái gì đó tương tự.

Từ mô tả của bạn, vấn đề dường như là các liên kết. Sách giáo khoa được tham chiếu lớn đến mức nào? Họ có tính toán lại khi bạn liên kết với họ không? Tôi thích ý tưởng mở đầu: bạn có thể thấy những gì đang xảy ra sau đó.

Việc sử dụng bộ nhớ của bạn (xem Task Manager) có vượt quá RAM vật lý có sẵn của bạn không? Một khi bộ nhớ đã được trao đổi ra đĩa, mọi thứ bắt đầu chậm lại rất nhiều.

Ngoài trợ giúp trước khi mở và sắp xếp, tôi khuyên bạn nên xem xét các công thức không tra cứu: có thể chuyển đổi thành công thức mảng và được gọi một lần cho mỗi phép tính thay vì một lần cho mỗi hàng không?

Trên điều VLOOKUP: bạn đã thử sử dụng chưa INDEX(value_range, MATCH(lookup_key, key_range))? Có những tình huống mà nó nhanh hơn.

Bạn không nói nếu bạn có bất kỳ VBA nào. Nếu bạn làm như vậy, hãy xem các hàm VBA được gọi bao nhiêu lần và chúng mất bao nhiêu thời gian. Mã VBA mà làm cho rất nhiều tham chiếu đến các đối tượng Excel có thể tốn kém.


3
2017-09-12 13:50





Nếu có bất kỳ dữ liệu nào trong tệp của bạn mà không cần phải có điều kiện và sẽ vẫn giữ nguyên (ví dụ: ngày sinh của một người) thì sau vlookup đầu tiên của bạn để lấy dữ liệu, 'Sao chép' và 'Dán Đặc biệt - Giá trị' toàn bộ cột / hàng để ngăn chặn tệp tính toán lại các ô đó mỗi khi bạn chạy phép tính. Chạy một vlookup trên dữ liệu có nguồn gốc từ một vlookup tôi đã nhận thấy mất khá nhiều thời gian.


3
2017-07-27 19:04





Thật không may, đó là cách nó được với bảng tính Excel ... VBA không phải là chính xác các tùy chọn nhanh nhất hiện có.

Bạn có thể muốn xem xét di chuyển dữ liệu sang cơ sở dữ liệu Microsoft Access.


2
2017-08-20 19:49





Mở (các) sổ làm việc mà bạn đang liên kết đến trước khi mở sổ làm việc chứa các liên kết.

Và sắp xếp dữ liệu, sau đó sử dụng tùy chọn được sắp xếp trong VLOOKUP (sử dụng True cho tham số cuối cùng).


2
2017-09-12 10:55





Tôi sử dụng Office 10 để giải quyết vấn đề này.

  1. Tôi đã mở tập tin khổng lồ của mình, lưu lại nó dưới dạng tệp sổ làm việc kiểu Excel 1997-2003.
    • Nó nhắc một tin nhắn hỏi tôi điều gì đó (tôi không thể nhớ).
  2. Tôi nhấp OK và tôi đã có một tập tin Excel 1997-2003 mới.
    • Các tập tin đã đi từ 25MB đến dưới 1MB.
  3. Sau đó, tôi chỉ tái lưu lại lần này từ tệp 1997-2003 cũ sang một sổ làm việc mới và nó thậm chí còn nhỏ hơn.

Tôi đã có vấn đề này với nhiều sách bài tập và điều này đã luôn luôn giải quyết nó cho tôi.


2
2017-11-04 18:06





Có thể có máy tính nhanh hơn hoặc nâng cấp RAM của bạn


1
2017-08-20 19:44



điều này chỉ đi xa cho đến nay, thật đáng buồn: Excel sẽ không tận dụng được nhiều hơn một CPU hoặc lõi CPU: ( - warren


Tôi đồng ý với Mike Woodhouse. Đây không phải là nhiều dữ liệu.

Khi thử nghiệm, tôi sẽ tạo một bảng tính mới với dữ liệu từ cả hai bảng tính được hợp nhất thành 1 bảng tính mới. Điều này sẽ cho bạn biết liệu sự cố có đang xảy ra do dữ liệu nằm trong hai bảng tính riêng biệt hay không. Đây không phải là một vấn đề nhưng nó sẽ là một thử nghiệm tốt.


0
2017-09-12 14:51





Tôi không biết liệu đây có phải là trợ giúp cho tình huống của bạn vì nó liên quan đến các liên kết cơ sở dữ liệu hay không, nhưng tôi đã có cùng một vấn đề ngoại trừ việc mất quá nhiều thời gian để cập nhật một bảng tính chạy truy vấn cơ sở dữ liệu.

Cách tôi thúc đẩy mọi thứ bởi một yếu tố của 15 là lần đầu tiên làm cho truy vấn chọn dữ liệu cần thiết thay vì làm cho Excel lọc nó. Thứ hai, trong cửa sổ Connection Properties của kết nối DB của tôi, tôi đã đánh dấu tùy chọn "Xóa dữ liệu khỏi phạm vi dữ liệu ngoài trước khi lưu sổ làm việc." Điều này đã giúp rất nhiều để tệp của tôi không phát triển liên tục về kích thước với lượng dữ liệu lớn hơn mà tôi có thể đang truy vấn.

Giờ đây, toàn bộ quá trình làm mới của tôi mất khoảng 2-3 giây thay vì 30-45.


0
2018-05-11 21:48