Sửa Lỗi Excel "There isn't enough memory to complete this action" Khi Mở File Lớn

Tình Huống

Bạn mở một file Excel lớn — có thể là báo cáo doanh số với 200.000 dòng, pivot table tổng hợp từ nhiều sheet, hoặc workbook chứa nhiều công thức nặng trải dài qua hàng chục cột. Rồi Excel đóng băng và hiển thị:

There isn't enough memory to complete this action. Try using less data or closing other applications.

Đôi khi lỗi xảy ra ngay giữa lúc chỉnh sửa. Đôi khi chỉ mới mở file. File có thể nặng 50MB, hoặc thậm chí dưới 10MB nhưng chứa đầy công thức volatile. Dù sao đi nữa, Excel đã hết bộ nhớ làm việc.

Nguyên Nhân

Excel nạp toàn bộ workbook vào RAM mỗi khi tính toán lại. Phiên bản 32-bit — vẫn được đóng gói với một số bản cài Office — bị giới hạn cứng ở 2GB RAM, bất kể máy bạn có bao nhiêu. Phiên bản 64-bit có thể dùng nhiều hơn, nhưng vẫn bị quá tải khi gặp:

• File với hàng trăm nghìn dòng và nhiều cột công thức
• Các hàm volatile như INDIRECT, OFFSET, NOW(), RAND() — chúng tính toán lại mỗi lần bạn nhấn phím
• Pivot table lớn với nhiều calculated field
• Quy tắc conditional formatting trải dài qua toàn bộ cột
• Hình ảnh nhúng, biểu đồ hoặc đối tượng OLE làm phình to kích thước file

Sửa Nhanh (Làm Trước)

1. Kiểm Tra Phiên Bản Excel (32-bit hay 64-bit)

Vào File → Account → About Excel. Nếu thấy 32-bit, đó chính là vấn đề. Gỡ cài đặt Office và cài lại — lần này chọn tùy chọn 64-bit.

Không chắc đang dùng phiên bản nào? Kiểm tra qua PowerShell:

# Nếu đường dẫn chứa "Program Files (x86)", đó là phiên bản 32-bit
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Office\*\Excel\InstallRoot" | Select-Object PSPath

2. Giải Phóng RAM và Mở Rộng Bộ Nhớ Ảo

Đóng Chrome, Slack và các ứng dụng nặng khác trước khi mở lại file. Sau đó tăng bộ nhớ ảo Windows:

• Nhấn Win + R, gõ sysdm.cpl, nhấn Enter
• Vào Advanced → Performance → Settings → Advanced → Virtual memory → Change
• Bỏ chọn Automatically manage, đặt Initial size là 4096 và Maximum là 8192 (cao hơn nếu còn đủ dung lượng ổ đĩa)
• Nhấn Set → OK → Restart

3. Chuyển Sang Chế Độ Tính Toán Thủ Công

Đặt Excel sang chế độ tính toán thủ công trước khi mở file. Điều này ngăn Excel tính toán lại 50.000 công thức ngay lúc tải:

// File → Options → Formulas → Calculation options → Manual
// Sau đó mở file của bạn

Hoặc dán lệnh này vào VBA Immediate Window (Ctrl+G):

Application.Calculation = xlCalculationManual

Nhấn F9 khi bạn thực sự cần tính toán lại.

Sửa Triệt Để

4. Tìm Và Loại Bỏ Công Thức Volatile

INDIRECT là thủ phạm tệ nhất — nó buộc toàn bộ workbook tính toán lại mỗi khi có bất kỳ thay đổi nào. Hãy thay thế bằng tham chiếu ô trực tiếp bất cứ khi nào có thể.

Nhấn Ctrl+F, tìm =INDIRECT, và đổi từng cái thành tham chiếu cố định. Để tìm tất cả hàm volatile trên mọi sheet cùng một lúc, chạy macro sau:

Sub FindVolatileFunctions()
    Dim ws As Worksheet
    Dim cell As Range
    Dim volatiles As Variant
    Dim v As Variant
    
    volatiles = Array("INDIRECT", "OFFSET", "NOW", "TODAY", "RAND", "RANDBETWEEN")
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                For Each v In volatiles
                    If InStr(cell.Formula, v) > 0 Then
                        Debug.Print ws.Name & "!" & cell.Address & ": " & cell.Formula
                    End If
                Next v
            End If
        Next cell
    Next ws
End Sub

5. Thu Hẹp Phạm Vi Conditional Formatting

Quy tắc áp dụng cho toàn bộ cột như $A:$A là kẻ ngốn bộ nhớ thầm lặng. Excel đánh giá chúng trên hơn một triệu dòng, dù dữ liệu của bạn chỉ đến dòng 500.

Đầu tiên, kiểm tra mức độ nghiêm trọng:

Sub CleanConditionalFormatting()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name & ": " & ws.Cells.FormatConditions.Count & " quy tắc"
    Next ws
End Sub

Sau đó vào Home → Conditional Formatting → Manage Rules và thu hẹp bất kỳ quy tắc nào đang tham chiếu cả cột. Đổi $A:$A thành $A$2:$A$50000 chẳng hạn.

6. Chuyển Đổi Công Thức Tĩnh Thành Giá Trị

Một công thức không bao giờ thay đổi chỉ đang lãng phí chu kỳ tính toán. Hãy đóng băng nó:

• Chọn vùng dữ liệu
• Ctrl+C để copy
• Ctrl+Shift+V → Paste Special → Values

Để thực hiện cho toàn bộ sheet đang hoạt động:

Sub ConvertFormulasToValues()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.UsedRange.Value = ws.UsedRange.Value
End Sub

7. Tách Dữ Liệu Thô Khỏi Phần Phân Tích

Giữ 200.000 dòng dữ liệu thô cùng pivot table trong một workbook là đang tự chuốc họa. Hãy tách chúng ra:

• Lưu dữ liệu thô trong một file data.xlsx riêng biệt
• Kéo dữ liệu vào workbook phân tích qua Power Query (Data → Get Data → From File)
• Power Query tải dữ liệu theo kiểu lazy và xử lý dataset lớn hiệu quả hơn nhiều so với tham chiếu sheet trực tiếp

8. Xóa Dòng Và Cột "Ma"

Các file Excel cũ tích lũy rác ẩn — dòng trống Excel vẫn theo dõi, dữ liệu đã xóa nhưng để lại định dạng, cột kéo dài vô lý đến tận dòng 1.048.576. Macro sau sẽ tìm và xóa chúng:

Sub ReduceFileSize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    
    For Each ws In ThisWorkbook.Worksheets
        lastRow = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lastCol = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        
        If lastRow < ws.Rows.Count Then
            ws.Rows(lastRow + 1 & ":" & ws.Rows.Count).Delete
        End If
        
        If lastCol < ws.Columns.Count Then
            ws.Columns(lastCol + 1 & ":" & ws.Columns.Count).Delete
        End If
    Next ws
    
    ' Lưu dưới dạng xlsx để loại bỏ overhead tương thích với định dạng cũ
    ThisWorkbook.SaveAs ThisWorkbook.FullName, xlOpenXMLWorkbook
End Sub

Kiểm Tra

Đóng Excel hoàn toàn, sau đó mở lại file. Kiểm tra theo danh sách sau:

• File tải thành công mà không báo lỗi bộ nhớ
• Kiểm tra kích thước file: chuột phải → Properties. Một file 200k dòng được tối ưu tốt nên dưới 20MB
• Mở Task Manager (Ctrl+Shift+Esc) khi file đang mở — mức sử dụng bộ nhớ của Excel nên dưới 500MB với workbook lớn thông thường
• Buộc tính toán lại toàn bộ bằng Ctrl+Alt+F9. Không lỗi = hoàn tất
• Kiểm tra File → Options → Formulas — thời gian tính toán nên tính bằng giây, không phải phút

Khi Excel Thực Sự Không Thể Xử Lý

Một số dataset thực sự quá lớn cho bảng tính. Lúc đó, hành động đúng đắn là ngừng cố sức với Excel và chuyển sang công cụ được tạo ra cho mục đích đó:

• Python + pandas: xử lý hàng triệu dòng dễ dàng
• DuckDB: chạy SQL trực tiếp trên file CSV hoặc Parquet — cực nhanh cho phân tích dữ liệu
• Power BI: được tạo ra chuyên biệt cho việc trực quan hóa dataset lớn

# Chỉ tải các cột cần thiết — giảm đáng kể lượng bộ nhớ sử dụng
import pandas as pd

df = pd.read_excel('large_file.xlsx', 
                   usecols=['Date', 'Sales', 'Region'],
                   engine='openpyxl')

result = df.groupby('Region')['Sales'].sum()
print(result)