Vấn đề
Bạn nhập công thức SORT() hoặc UNIQUE(), nhấn Enter, nhưng thay vì danh sách đã sắp xếp, bạn nhận được lỗi #REF! màu đỏ. Di chuột vào sẽ hiện thông báo:
Array result was not expanded because it would overwrite data in...
Các công thức mảng trong Google Sheets — SORT, UNIQUE, FILTER, ARRAYFORMULA — đều tự động "tràn" kết quả vào các ô liền kề. Ngay khi bất kỳ ô nào trong vùng đó có nội dung (dù chỉ là một dấu cách hoặc số không), Sheets sẽ từ chối thực thi và ném ra lỗi #REF!.
Nguyên nhân
Giả sử bạn nhập =SORT(A2:A20) vào ô C2. Sheets cần vùng C2:C20 hoàn toàn trống để ghi 19 giá trị đã sắp xếp. Chỉ cần một ô như C5 có dữ liệu — nhãn, số thừa, bất cứ thứ gì — toàn bộ công thức sẽ bị lỗi.
Các nguyên nhân thường gặp:
- Một công thức khác hoặc giá trị tĩnh nằm trong vùng tràn.
- Dấu cách thừa (
') — ô trông có vẻ trống nhưng thực ra không phải. - Lần chạy trước của cùng công thức đó để lại giá trị chưa được dọn dẹp.
- Các ô được gộp (merged) ở bất kỳ đâu trong vùng đầu ra — Sheets không thể ghi vào vùng ô đã gộp.
Cách khắc phục nhanh: Xóa vùng tràn
Tìm thứ đang chặn đầu ra và xóa nó đi. Đơn giản vậy thôi.
- Nhấp vào ô đang hiển thị
#REF!. - Một vùng viền màu xanh lam xuất hiện — đây là nơi Sheets muốn ghi kết quả.
- Chọn tất cả các ô bên trong viền xanh đó, bỏ qua ô chứa công thức.
- Nhấn Delete để xóa nội dung của chúng.
- Công thức sẽ tự động hoạt động trở lại.
Vẫn bị lỗi sau khi xóa? Ô đang chặn có thể chứa ký tự vô hình. Nhấp vào ô đó và kiểm tra thanh công thức — nếu có bất cứ thứ gì, dù chỉ là một dấu nháy đơn, hãy nhấn Delete lần nữa để xóa hoàn toàn.
Xử lý ô được gộp (Merged Cells)
Ô được gộp sẽ làm hỏng công thức tràn một cách thầm lặng. Vùng trông có vẻ trống, nhưng chính việc gộp ô mới là nguyên nhân gây chặn. Nếu xóa dữ liệu không giúp được gì, hãy kiểm tra xem có ô gộp không:
- Chọn toàn bộ vùng đầu ra dự kiến.
- Vào Định dạng → Gộp ô → Bỏ gộp.
- Xóa mọi nội dung xuất hiện lại, sau đó nhập lại công thức.
Giải pháp lâu dài: Dành riêng cột cho công thức
Giải pháp thực sự là cô lập. Hãy đặt công thức tràn vào một cột mà không có gì khác can thiệp vào. Việc trộn lẫn đầu ra động với nhãn tĩnh trong cùng một cột là mầm mống khiến lỗi này quay lại ngay khi dữ liệu tăng thêm.
Bố cục lỗi — công thức và dữ liệu tĩnh dùng chung cột B:
| A | B |
|-------------|-------------------|
| Raw data | =SORT(A2:A10) | ← spill targets B2:B10
| more data | |
| ... | Header label | ← B5 blocks the spill!
Bố cục đúng — công thức được dùng riêng cột C:
| A | B (empty buffer) | C |
|-------------|-----------------|-------------------|
| Raw data | | =SORT(A2:A10) |
| more data | | |
| ... | | |
Cột B đóng vai trò vùng đệm trực quan. Có hơi thừa không? Có thể. Nhưng nó ngăn việc chồng lấn ngẫu nhiên khi bảng tính của bạn ngày càng lớn hơn.
Bọc trong IFERROR như một lưới an toàn
Khi dữ liệu nguồn thay đổi kích thước liên tục và không thể đảm bảo vùng tràn luôn trống, hãy bọc công thức trong IFERROR để người dùng khác thấy thông báo thay vì lỗi đỏ:
=IFERROR(SORT(A2:A100), "Output blocked — clear column C")
Cách này không khắc phục nguyên nhân gốc rễ. Nó chỉ giúp bảng tính trông gọn gàng trong khi bạn xử lý xung đột bố cục.
Dung lượng mỗi công thức cần
Mỗi công thức dưới đây sẽ tràn xuống phía dưới kể từ ô nhập vào. Hãy xóa toàn bộ cột đầu ra trước khi nhập công thức.
=SORT(A2:A50) ' cần tối đa 49 hàng trong cột đầu ra
=SORT(A2:A50, 1, FALSE) ' cùng vùng, thứ tự giảm dần
=UNIQUE(B2:B100) ' tối đa 99 hàng (loại trùng làm giảm số lượng)
=SORT(UNIQUE(B2:B100)) ' kết hợp — vẫn cần tính cho 99 hàng
=FILTER(A2:B50, C2:C50="active") ' số hàng phụ thuộc vào bao nhiêu dòng khớp điều kiện
Lồng SORT bên trong UNIQUE không làm giảm yêu cầu về dung lượng. Hãy tính dư cho toàn bộ độ dài vùng dữ liệu nguồn.
Các bước xác nhận
Sau khi đã xóa các ô đang chặn:
- Ô
#REF!cập nhật ngay lập tức và hiển thị giá trị đầu tiên trong danh sách đã sắp xếp hoặc danh sách duy nhất. - Một viền xanh lam nhạt bao quanh toàn bộ vùng tràn — đây là xác nhận công thức đang chạy đúng.
- Nhấp vào bất kỳ ô nào bên trong vùng tràn (không phải ô chứa công thức). Thanh công thức hiển thị công thức bị mờ màu xám, nghĩa là đây là kết quả tràn chứ không phải ô nhập độc lập.
- Thêm một giá trị mới vào vùng dữ liệu nguồn và quan sát đầu ra tự động điều chỉnh.
Nếu #REF! xuất hiện trở lại sau khi chỉnh sửa ở nơi khác trong bảng tính, có thể một công thức khác đang phát triển và lấn vào cột đầu ra của bạn. Nhấn Ctrl+End để nhảy đến ô cuối cùng được dùng và kiểm tra những gì nằm gần vùng tràn của bạn.