Chuyện gì đang xảy ra
Bạn nhập một công thức ARRAYFORMULA nhưng thay vì điền kết quả xuống cả cột, Google Sheets lại hiển thị một tam giác lỗi nhỏ. Di chuột vào đó và bạn thấy:
Array result was not expanded because it would overwrite data in B2.
Công thức dừng lại hoàn toàn. Tham chiếu ô trong thông báo (B2, C5, D10, v.v.) thay đổi tùy theo vị trí xung đột, nhưng nguyên nhân gốc rễ luôn giống nhau: có thứ gì đó đang chiếm một ô mà mảng cần ghi vào.
Tại sao lỗi này xảy ra
ARRAYFORMULA cần một vùng hoàn toàn trống để trải kết quả ra. Nếu chỉ một ô trong phạm vi đó chứa bất kỳ thứ gì — một giá trị, một công thức khác, hay thậm chí một khoảng trắng lạc — Sheets sẽ từ chối mở rộng. Nó sẽ không tự ý ghi đè dữ liệu của bạn, điều này thực ra là đúng đắn, nhưng kết quả là bạn có một công thức bị hỏng.
Các nguyên nhân phổ biến:
- Một hàng tiêu đề hoặc nhãn được nhập trực tiếp vào cột đầu ra
- Dữ liệu cũ còn sót lại từ lần nhập thủ công
- Một công thức khác đang chiếm một ô trong vùng trải kết quả
- Một ô trông có vẻ trống nhưng thực ra chứa ký tự khoảng trắng (
" ") - Một ARRAYFORMULA thứ hai trong cùng cột đang cạnh tranh các ô giống nhau
Bước 1 — Tìm ô đang chặn
Thông báo lỗi chỉ thẳng vào vấn đề. Nếu nó ghi "would overwrite data in B2", hãy nhấp vào B2 và kiểm tra thanh công thức. Tuy nhiên, Sheets chỉ báo cáo xung đột đầu tiên — có thể còn nhiều ô chặn khác ở phía dưới cột.
Để quét toàn bộ vùng đầu ra nhanh chóng:
- Nhấp vào ô chứa ARRAYFORMULA của bạn (ví dụ: B1).
- Nhấn Ctrl + Shift + End (Windows/Linux) hoặc Cmd + Shift + End (Mac) để nhảy đến ô cuối cùng có dữ liệu trong trang tính.
- Kiểm tra bằng mắt toàn bộ cột để tìm ô nào không trống.
Thay vì nhìn từng ô, hãy nhấn Ctrl + F, bật tùy chọn "Tìm kiếm bằng biểu thức chính quy", và tìm kiếm .+ trong cột đích. Mọi ô có nội dung sẽ hiện ra ngay lập tức.
Bước 2 — Xóa các ô đang chặn
Chọn các ô bị chiếm và nhấn Delete — không phải Backspace. Delete xóa nội dung tại chỗ mà không dịch chuyển các hàng, đó chính xác là điều bạn cần.
Có tiêu đề muốn giữ lại? Hãy di chuyển nó lên phía trên vùng đầu ra của mảng hoặc sang một cột khác, rồi điều chỉnh công thức cho phù hợp.
Chú ý khoảng trắng ẩn. Một ô có thể trông hoàn toàn trống nhưng vẫn chứa " ". Nhấp vào ô đó và kiểm tra thanh công thức — nếu thấy bất kỳ thứ gì, hãy xóa đi.
Bước 3 — Sửa hoặc cơ cấu lại công thức
Dọn sạch đường đi và ARRAYFORMULA của bạn sẽ tự động mở rộng. Vẫn còn lỗi? Có khả năng phạm vi của công thức đang được xác định không chính xác.
Ví dụ cơ bản — nhân cả cột với 1.1
=ARRAYFORMULA(A2:A100 * 1.1)
Công thức này ghi kết quả vào B2:B100. Tất cả 99 ô đó phải trống trước khi bạn nhập công thức vào B1.
Dùng phạm vi mở
=ARRAYFORMULA(IF(A2:A="", "", A2:A * 1.1))
Lớp bọc IF(A2:A="", "", ...) ngăn mảng ghi chuỗi rỗng xuống vô tận. Bỏ nó đi và Sheets sẽ trải giá trị trống vào hàng nghìn hàng — điều đó bản thân nó có thể gây xung đột nếu có dữ liệu ở xa bên dưới.
Giữ tiêu đề trong cùng cột
Muốn có nhãn "Price" ở B1 và kết quả mảng bắt đầu từ B2? Đặt ARRAYFORMULA vào B2, không phải B1:
B1: Price
B2: =ARRAYFORMULA(IF(A2:A="", "", A2:A * 1.1))
Tiêu đề nằm phía trên phạm vi của mảng, nên không có xung đột.
Kết hợp tiêu đề vào trong công thức
Một số người thích dùng một công thức duy nhất xử lý cả tiêu đề lẫn dữ liệu:
=ARRAYFORMULA({"Price"; IF(A2:A="", "", A2:A * 1.1)})
Cú pháp {"Price"; ...} tạo ra một mảng dọc — hàng 1 là chuỗi tiêu đề, phần còn lại là dữ liệu được tính toán. Đặt công thức này vào B1 và để trống tất cả các ô phía dưới.
Bước 4 — Xử lý trường hợp "hai ARRAYFORMULA trong một cột"
Hai công thức ARRAYFORMULA cạnh tranh các hàng chồng lên nhau? Một cái sẽ luôn thua. Hãy gộp chúng thành một công thức duy nhất, hoặc phân vùng các hàng sao cho phạm vi không bao giờ chồng lấp:
=ARRAYFORMULA(IF(ROW(A:A)=1, "Header", IF(A:A="", "", A:A * 1.1)))
Một công thức, một nguồn sự thật duy nhất. Hàng 1 lấy tiêu đề; mọi hàng còn lại thực hiện phép tính.
Kiểm tra kết quả
Sau khi xóa các ô chặn và điều chỉnh công thức:
- Tam giác lỗi biến mất khỏi ô công thức.
- Kết quả tự động điền xuống cột khi bạn thêm dữ liệu vào cột nguồn.
- Nhấp vào bất kỳ ô nào trong vùng đầu ra — một đường viền xanh nhạt sẽ tô sáng toàn bộ vùng trải kết quả, và thanh công thức hiển thị công thức gốc bị mờ đi (chỉ đọc, vì nó do mảng kiểm soát).
- Nhập một giá trị mới vào cột nguồn và xem kết quả xuất hiện ngay lập tức — không cần thao tác thủ công nào.
Bài học rút ra
- Mảng sở hữu vùng đầu ra của nó. Đừng bao giờ nhập trực tiếp vào các ô mà nó kiểm soát. Dữ liệu bạn nhập sẽ phá vỡ mảng và kích hoạt lại lỗi tương tự vào lần tiếp theo công thức tính toán lại.
- Lên kế hoạch bố cục cột trước khi viết công thức. Quyết định cột nào mảng sẽ chiếm dụng, rồi để nó hoàn toàn trống ngoại trừ ô chứa công thức.
- Bọc phạm vi mở bằng IF. Các phạm vi như
A2:Acần có điều kiện bảo vệIF(A2:A="", "", ...). Nếu không có nó, mảng sẽ lấp đầy mọi hàng trong trang tính bằng các giá trị trống. - Một ARRAYFORMULA cho mỗi cột. Nhiều mảng cạnh tranh trong cùng một cột là một mớ rắc rối khi bảo trì. Hãy hợp nhất chúng thành một công thức duy nhất.