Sửa lỗi 'Excel cannot complete this task with available resources' Khi Xử Lý Dữ Liệu Lớn

Lỗi Gặp Phải

Bạn đang thực hiện thao tác — sắp xếp 500 nghìn hàng, làm mới pivot table, chạy VLOOKUP qua nhiều sheet — thì Excel hiện thông báo:

Excel cannot complete this task with available resources. Choose less data or close other applications.

Đôi khi lỗi xảy ra ngay lập tức. Đôi khi Excel đứng máy hai phút rồi mới crash. Dù theo cách nào, công việc của bạn bị kẹt và hộp thoại không cho bạn biết phải xử lý gì.

Nguyên Nhân

Excel đã chạm giới hạn cứng — RAM, không gian địa chỉ, hoặc số lượng đối tượng GDI. Các nguyên nhân phổ biến:

• Excel 32-bit: bị giới hạn khoảng ~2 GB RAM dù máy bạn có bao nhiêu RAM đi nữa. Một workbook với 300 nghìn hàng và công thức có thể chạm trần này rất nhanh.
• Công thức volatile ở quy mô lớn: INDIRECT, OFFSET, NOW(), RAND() — những hàm này tính toán lại mỗi lần bạn nhấn phím, trên mọi ô sử dụng chúng
• Công thức mảng cũ trên vùng dữ liệu khổng lồ: mảng CSE trên 100 nghìn hàng trở lên tiêu thụ bộ nhớ không cân xứng
• Nhiều workbook lớn mở cùng lúc: tất cả workbook trong cùng một phiên Excel dùng chung một vùng bộ nhớ
• Định dạng thừa: định dạng cả cột thay vì chỉ các ô có dữ liệu buộc Excel theo dõi trạng thái định dạng cho hơn một triệu ô — một kẻ ngốn bộ nhớ thầm lặng
• Các bước Power Query chưa tối ưu: tải toàn bộ bảng vào bộ nhớ trước rồi mới lọc, thay vì lọc trước

Cách Sửa 1: Chuyển Sang Excel 64-bit (Hiệu Quả Nhất)

Đang chạy Excel 32-bit với dữ liệu lớn là nguyên nhân phổ biến nhất — và cách khắc phục chỉ cần cài lại một lần.

Kiểm tra phiên bản trước:

File → Account → About Excel

Hộp thoại sẽ hiển thị rõ "32-bit" hoặc "64-bit". Nếu là 32-bit:

• Gỡ cài đặt Office hiện tại qua Settings → Apps
• Tải bộ cài 64-bit từ tài khoản Microsoft hoặc cổng cấp phép theo khối lượng của bạn
• Cài Office 64-bit

Lưu ý quan trọng: các COM add-in và plugin XLL 32-bit cũ có thể bị lỗi trên 64-bit. Trước khi cài lại, hãy kiểm tra các add-in bạn đang dùng tại File → Options → Add-ins.

Cách Sửa 2: Chuyển Sang Chế Độ Tính Toán Thủ Công

Lỗi xảy ra khi bạn đang chỉnh sửa hoặc dán dữ liệu? Tính năng tự động tính toán lại gần như chắc chắn đang kích hoạt và làm cạn kiệt bộ nhớ.

Formulas → Calculation Options → Manual

Excel sẽ không tính toán lại cho đến khi bạn nhấn F9. Thực hiện tất cả các chỉnh sửa, rồi kích hoạt tính toán một lần khi sẵn sàng. Chuyển lại sang Automatic sau đó nếu cần.

Nếu Excel đứng máy đến mức không thể thao tác menu, hãy chạy lệnh này qua VBA:

Application.Calculation = xlCalculationManual
' ... thực hiện công việc của bạn ...
Application.Calculation = xlCalculationAutomatic

Cách Sửa 3: Đóng Các Workbook và Ứng Dụng Khác

Mỗi workbook đang mở trong cùng phiên Excel đều lấy từ cùng một vùng bộ nhớ. Hãy đóng những gì không cần thiết.

• Đóng mọi workbook bạn không đang dùng
• Tắt các tab Chrome, Slack, Teams — chỉ riêng một cuộc gọi video có thể ngốn hơn 500 MB
• Mở Task Manager (Ctrl+Shift+Esc) → kiểm tra bộ nhớ của Excel trong tab Details

Cần mở nhiều workbook lớn cùng lúc? Hãy khởi chạy chúng trong các phiên Excel riêng biệt — mỗi phiên có không gian bộ nhớ riêng:

Start → Excel (giữ Alt khi nhấp vào file thứ hai để mở trong phiên mới)

Hoặc từ dòng lệnh:

excel.exe /x

Cách Sửa 4: Tìm và Thay Thế Các Công Thức Volatile

Các công thức volatile tốn kém một cách đáng ngạc nhiên. Một hàm INDIRECT lồng bên trong công thức được dùng ở 50.000 ô đồng nghĩa với 50.000 lần tính toán lại mỗi lần nhấn phím.

Dùng macro này để tô sáng mọi ô chứa hàm volatile:

Sub FindVolatile()
    Dim cell As Range
    Dim volatileFuncs As Variant
    volatileFuncs = Array("INDIRECT", "OFFSET", "NOW", "TODAY", "RAND", "RANDBETWEEN")
    
    For Each cell In ActiveSheet.UsedRange
        If cell.HasFormula Then
            Dim f As String
            f = UCase(cell.Formula)
            Dim v As Variant
            For Each v In volatileFuncs
                If InStr(f, v) > 0 Then
                    cell.Interior.Color = RGB(255, 200, 0)
                End If
            Next v
        End If
    Next cell
End Sub

Sau đó thay thế các hàm gây hại nhất bằng các lựa chọn không volatile:

• INDIRECT("Sheet1!A"&ROW()) → cơ cấu lại dữ liệu để tránh hoàn toàn việc tham chiếu động
• OFFSET(A1, n, 0) → INDEX(A:A, n+1) làm điều tương tự mà không kích hoạt tính toán lại
• TODAY() / NOW() trong các phép tính → dán dưới dạng giá trị tĩnh, cập nhật thủ công khi cần

Cách Sửa 5: Dọn Dẹp Định Dạng Thừa

Vấn đề này khiến nhiều người bất ngờ. Ai đó chọn cả một cột và nhấn "Bold" hoặc đổi màu nền. Excel giờ lưu dữ liệu định dạng cho toàn bộ 1.048.576 ô trong cột đó — kể cả các ô trống.

Kiểm tra Excel nghĩ vùng dữ liệu thực sự của bạn lớn đến đâu:

' Trong cửa sổ VBA immediate (Alt+F11 → Ctrl+G)
Debug.Print ActiveSheet.UsedRange.Address

Nếu kết quả trả về $A$1:$XFD$1048576 trong khi dữ liệu của bạn chỉ đến hàng 5.000, bạn đang gặp vấn đề định dạng.

Khắc phục theo ba bước:

• Chọn tất cả các hàng và cột vượt ngoài vùng dữ liệu thực tế của bạn
• Home → Clear → Clear All
• Lưu file và theo dõi dung lượng — giảm từ 45 MB xuống còn 8 MB là chuyện bình thường

Muốn dùng công cụ tích hợp? Add-in Inquire (Excel 2013 trở lên) có tùy chọn Clean Excess Cell Formatting chuyên dụng.

Cách Sửa 6: Tối Ưu Power Query

Lỗi Power Query thường xuất phát từ một điều: lọc dữ liệu xảy ra quá muộn trong query. Excel tải toàn bộ bảng vào bộ nhớ trước, rồi mới cắt bớt — lãng phí RAM cho dữ liệu bạn vốn định loại bỏ.

Di chuyển các bước lọc lên đầu query:

// Trước (tải tất cả, rồi mới lọc)
let
    Source = Excel.Workbook(...),
    AllData = Source{[Name="Data"]}[Data],
    Filtered = Table.SelectRows(AllData, each [Year] = 2024)
in
    Filtered

// Sau (lọc sớm = chỉ các hàng cần thiết trong bộ nhớ)
let
    Source = Excel.Workbook(...),
    AllData = Source{[Name="Data"]}[Data],
    TypedYear = Table.TransformColumnTypes(AllData, {{"Year", Int64.Type}}),
    Filtered = Table.SelectRows(TypedYear, each [Year] = 2024)
in
    Filtered

Đồng thời tắt tính năng làm mới nền cho các query lớn — nó âm thầm tiêu thụ bộ nhớ trong khi bạn làm việc:

Data → Queries & Connections → nhấp chuột phải vào query → Properties → bỏ chọn "Enable background refresh"

Cách Sửa 7: Chuyển Tập Dữ Liệu Lớn Ra Khỏi Sheet

Vượt quá 200 nghìn hàng, lưới sheet của Excel không phải nơi phù hợp để xử lý dữ liệu. Ba lựa chọn tốt hơn:

• Power Pivot: xử lý hàng chục triệu hàng nhờ nén theo cột. Bật tại File → Options → Add-ins → COM Add-ins → Microsoft Office Power Pivot.
• Power Query + Data Model: tải dữ liệu vào model thay vì đổ thẳng lên sheet
• Tổng hợp trước bằng Python hoặc SQL: tóm tắt trước khi nhập — Excel chỉ nhận kết quả, không phải dữ liệu thô

Ví dụ Python nhanh để rút gọn file CSV triệu hàng thành bản tóm tắt dễ quản lý:

import pandas as pd

df = pd.read_csv('large_dataset.csv')
summary = df.groupby(['region', 'product']).agg({'sales': 'sum', 'units': 'count'})
summary.to_excel('summary_for_excel.xlsx', index=False)

Cách Sửa 8: Tăng Bộ Nhớ Ảo (Windows)

Nâng cấp phần cứng không phải lúc nào cũng khả thi. Nếu bạn thực sự bị hạn chế RAM, tăng file trang của Windows cho Excel thêm không gian hoạt động — chậm hơn RAM thật, nhưng ngăn được crash cứng.

• Win+R → sysdm.cpl → Advanced → Performance Settings → Advanced → Virtual Memory
• Bỏ chọn "Automatically manage"
• Đặt Initial size thành 1,5× RAM của bạn tính bằng MB, Maximum thành 3× RAM của bạn tính bằng MB (ví dụ: 12288 MB initial / 24576 MB max cho máy 8 GB RAM)
• Nhấp Set → OK → Khởi động lại

Kiểm Tra Kết Quả

Tái hiện đúng thao tác đã gây crash trước đó — cùng thao tác sắp xếp, làm mới pivot, hoặc công thức đó. Trong khi chạy:

• Theo dõi bộ nhớ của Excel trong Task Manager. Dưới 80% RAM khả dụng là dấu hiệu tốt.
• Nếu bạn đã tắt tự động tính toán, hãy bật lại: Formulas → Calculation Options → Automatic
• Kiểm tra dung lượng file sau khi lưu. Giảm đáng kể (ví dụ: 45 MB → 8 MB) xác nhận định dạng thừa đã được dọn sạch thành công.

Phòng Ngừa

• Dùng Excel 64-bit cho mọi công việc nặng về dữ liệu. Không có lý do chính đáng nào để ở lại 32-bit.
• Chỉ định dạng các ô có dữ liệu — không bao giờ định dạng cả hàng hay cả cột
• Ưu tiên XLOOKUP hoặc INDEX/MATCH thay vì VLOOKUP trên vùng dữ liệu lớn. Tránh tham chiếu cả cột như A:A khi dữ liệu của bạn có giới hạn xác định.
• Giữ dữ liệu nguồn trong Power Query hoặc Data Model, không phải trên sheet thô
• Chạy Inquire → Clean Excess Cell Formatting định kỳ trên các workbook dùng lâu dài
• Chia file lớn theo năm hoặc khu vực thay vì duy trì một workbook khổng lồ