Sửa lỗi #REF!: Cách xóa hàng mà không làm hỏng công thức của bạn

Tình huống thực tế

Tôi vừa mới dọn dẹp một bảng theo dõi tài chính gồm 500 hàng thì một công việc đơn giản bỗng trở thành một mớ hỗn độn. Tôi đã xóa khoảng 15 hàng dữ liệu nhà cung cấp "lỗi thời" từ năm 2023, và đột nhiên, toàn bộ bảng tổng hợp của tôi chuyển sang màu đỏ. Mọi giá trị tổng đều bị thay thế bằng lỗi #REF!.

Khi điều này xảy ra, Google Sheets đưa ra một lời giải thích rất cụ thể:

#REF! (This happened because you deleted a row or column that was used in a formula)

Hầu hết các công thức trong Sheets sử dụng tham chiếu ô tương đối hoặc tuyệt đối, như A5 hoặc $B$10. Nếu bạn xóa hàng vật lý nơi các ô đó tồn tại, tọa độ mục tiêu của công thức sẽ biến mất. Không giống như việc di chuyển dữ liệu—nơi Sheets tự động cập nhật tham chiếu—việc xóa hàng là một sự đứt gãy hoàn toàn. Công thức thực sự bị mất đi "bản đồ" dẫn đường của nó.

Quy trình gỡ lỗi (Debug)

Nếu bạn vừa thấy lỗi xuất hiện, "người bạn tốt nhất" của bạn là Ctrl + Z (Hoàn tác). Nếu đã quá muộn để làm điều đó, đây là cách tôi truy tìm nguồn gốc của sự đứt gãy:

Kiểm tra hiện trường: Nhấp vào một ô bị lỗi. Bạn có thể sẽ thấy =SUM(#REF!:#REF!). Các tọa độ ban đầu đã biến mất, bị ghi đè bởi thẻ lỗi.
Theo dấu vết: Sử dụng chế độ xem "Hiển thị công thức" (`Ctrl + ``) để xem có bao nhiêu ô phụ thuộc đã bị ảnh hưởng bởi việc xóa hàng.
Kiểm tra Dải ô đã đặt tên: Đôi khi việc xóa hàng xảy ra bên trong một Dải ô đã đặt tên (Named Range). Kiểm tra Dữ liệu > Dải ô đã đặt tên để xem có dải ô nào được đánh dấu màu đỏ hay không.

Các giải pháp

Bạn có thể sửa lỗi thủ công, nhưng giải pháp thực sự là xây dựng các công thức có thể "sống sót" sau khi xóa hàng.

Cách 1: Hàm INDEX (Lựa chọn chống xóa hàng)

Hàm INDEX là cách đáng tin cậy nhất để ngăn chặn điều này. Thay vì trỏ trực tiếp vào một ô, bạn trỏ vào một dải ô và chỉ định một vị trí. Nếu bạn xóa một hàng, công thức vẫn cố định vào vị trí đó, bất kể dữ liệu nào được chuyển vào.

Công thức tiêu chuẩn (Dễ hỏng):

=A5 * 0.1

Xóa Hàng 5, và công thức này sẽ bị lỗi ngay lập tức.

Công thức bền bỉ (INDEX):

=INDEX(A:A, 5) * 0.1

Bằng cách sử dụng phiên bản này, bạn đang yêu cầu mục thứ 5 trong Cột A. Nếu bạn xóa Hàng 5 hiện tại, Hàng 6 cũ sẽ chuyển lên trên. Công thức chỉ đơn giản là lấy dữ liệu mới đang nằm ở vị trí thứ 5.

Cách 2: Sử dụng INDIRECT cho các liên kết cố định

Sử dụng INDIRECT nếu bạn cần tham chiếu đến một tọa độ cụ thể—như Thuế suất trong ô B2—vốn không bao giờ được thay đổi. Hàm này coi các chuỗi văn bản là địa chỉ ô.

=INDIRECT("B2") + INDIRECT("C2")

Vì "B2" là văn bản, Google Sheets sẽ không thay đổi nó khi bạn xóa hàng. Nó sẽ luôn nhìn vào bất kỳ dữ liệu nào tình cờ nằm ở ô B2 tại thời điểm đó. Tuy nhiên, hãy sử dụng nó một cách tiết kiệm; quá nhiều hàm biến đổi (volatile functions) có thể làm chậm một trang tính có hơn 10.000 hàng.

Cách 3: Hàm OFFSET

OFFSET hoạt động tốt khi bạn muốn tham chiếu đến một ô tương đối so với một điểm neo ổn định, chẳng hạn như hàng tiêu đề mà bạn biết là sẽ không bao giờ bị xóa.

=OFFSET($A$1, 4, 0)

Công thức này bắt đầu tại ô A1 và di chuyển xuống đúng 4 hàng. Miễn là tiêu đề của bạn tại A1 vẫn giữ nguyên, công thức sẽ luôn tìm thấy giá trị ở hàng thứ 5 của cột đó.

Cách 4: Sử dụng ArrayFormula để quản lý dữ liệu linh hoạt

Nếu bạn thường xuyên nhập và xóa dữ liệu, các công thức hàng riêng lẻ sẽ là một rủi ro. Thay vào đó, hãy thử sử dụng ArrayFormula trong hàng tiêu đề.

=ArrayFormula(IF(A2:A="", "", A2:A * 1.2))

Đặt công thức này vào ô B2. Nó tính toán toàn bộ cột cùng một lúc. Nếu bạn xóa hàng 100, công thức chỉ đơn giản là tính toán lại cho các dữ liệu còn lại mà không làm mất tham chiếu.

Các bước xác minh

Sau khi áp dụng các cách sửa lỗi này, hãy kiểm tra độ bền của trang tính bằng các bước sau:

Chèn một hàng thử nghiệm: Thêm một hàng phía trên dữ liệu của bạn. Công thức vẫn lấy đúng giá trị chứ?
Xóa một hàng: Xóa một dòng dữ liệu. Nếu bạn đã sử dụng INDEX hoặc INDIRECT, lỗi #REF! sẽ không xuất hiện.
Kiểm tra tổng cộng: Xác minh rằng các dải ô SUM của bạn không bị thu hẹp. Ví dụ: đảm bảo SUM(A5:A10) không vô tình trở thành SUM(A5:A9).

Bài học rút ra

Ngừng việc "trỏ và nhấp" cho dữ liệu biến động: Nếu quy trình làm việc của bạn liên quan đến việc xóa và dán hàng, hãy ngừng nhấp vào các ô riêng lẻ. Thay vào đó, hãy sử dụng INDEX(A:A, row_number).
Khóa tiêu đề của bạn: Sử dụng Dữ liệu > Bảo vệ trang tính và dải ô để khóa các hàng trên cùng. Điều này ngăn chặn việc vô tình xóa các điểm neo của OFFSET hoặc ARRAYFORMULA.
Xóa nội dung, đừng xóa hàng: Bất cứ khi nào có thể, hãy sử dụng phím Backspace hoặc Delete để xóa nội dung trong ô. Việc xóa cấu trúc hàng thực tế mới là nguyên nhân gây ra nhiều thiệt hại nhất.