Công thức mảng động và hành vi mảng bị tràn

Các công thức Excel trả về tập hợp giá trị, còn được gọi là mảng, trả về các giá trị này cho các ô lân cận. Hành vi này được gọi là tràn.

Các công thức có thể trả về mảng có kích cỡ có thể thay đổi được gọi là công thức mảng động. Các công thức hiện đang trả về các mảng đang tràn thành công có thể được gọi là công thức mảng bị tràn.

Sau đây là một số ghi chú để giúp bạn hiểu và sử dụng các loại công thức này.

Tràn có nghĩa là gì?

Lưu ý: Các công thức mảng cũ hơn, còn được gọi là công thức mảng cũ luôn trả về kết quả có kích cỡ cố định – chúng luôn tràn vào cùng một số lượng ô. Hành vi tràn được mô tả trong chủ đề này không áp dụng cho các công thức mảng cũ.

Tràn có nghĩa là một công thức đã dẫn đến nhiều giá trị và các giá trị đó đã được đặt vào các ô lân cận. Ví dụ: =SORT(D2:D11,1,-1), sắp xếp một mảng theo thứ tự giảm dần, sẽ trả về mảng tương ứng gồm 10 hàng. Nhưng bạn chỉ cần nhập công thức vào ô trên cùng bên trái hoặc F2 trong trường hợp này và công thức sẽ tự động tràn xuống ô F11.

Sắp xếp các giá trị trong các ô D2:D11 bằng công thức =SORT(D2:D11,1,-1)

Điểm chính

Khi bạn nhấn Enter để xác nhận công thức của mình, Excel sẽ tự động định cỡ phạm vi đầu ra cho bạn và đặt kết quả vào từng ô trong phạm vi đó.
Nếu bạn đang viết công thức mảng động để thao tác trên danh sách dữ liệu, có thể sẽ hữu ích nếu bạn đặt công thức đó vào bảng Excel, rồi sử dụng tham chiếu có cấu trúc để tham chiếu đến dữ liệu. Điều này là do các tham chiếu có cấu trúc tự động điều chỉnh khi thêm hoặc xóa hàng khỏi bảng.
Các công thức mảng bị tràn không được hỗ trợ trong chính các bảng Excel, vì vậy bạn nên đặt chúng vào trong lưới bên ngoài Bảng. Bảng là lựa chọn phù hợp nhất để chứa các hàng và cột dữ liệu độc lập.
Sau khi bạn nhập một công thức mảng bị tràn, khi bạn chọn ô bất kỳ trong vùng tràn, Excel sẽ đặt viền được tô sáng xung quanh phạm vi. Viền sẽ biến mất khi bạn chọn một ô bên ngoài khu vực này.
Chỉ có thể chỉnh sửa ô đầu tiên trong vùng tràn. Nếu bạn chọn một ô khác trong vùng tràn, công thức sẽ hiển thị trong thanh công thức nhưng văn bản bị "tạo bản sao" và không thể thay đổi được. Nếu bạn cần cập nhật công thức, bạn nên chọn ô trên cùng bên trái trong phạm vi mảng, thay đổi công thức nếu cần, sau đó Excel sẽ tự động cập nhật phần còn lại của vùng tràn cho bạn khi bạn nhấn Enter.
Chồng lấp công thức – Không thể nhập công thức mảng nếu có bất kỳ nội dung nào chặn phạm vi đầu ra. Và nếu điều này xảy ra, Excel sẽ trả về lỗi #SPILL! và cho biết có sự cố chặn. Nếu bạn loại bỏ sự cố chặn, công thức sẽ tràn như mong đợi. Trong ví dụ dưới đây, phạm vi đầu ra của công thức chồng lấp một phạm vi khác có dữ liệu và được hiển thị với một đường viền chấm chấm chồng lên các ô có các giá trị cho biết công thức không thể tràn. Loại bỏ dữ liệu chặn hoặc sao chép dữ liệu ở nơi khác và công thức sẽ tràn như mong đợi.
Các công thức mảng cũ được nhập bằng tổ hợp phím CTRL+SHIFT+ENTER (CSE) vẫn được hỗ trợ vì lý do tương thích ngược nhưng không nên sử dụng nữa. Nếu muốn, bạn có thể chuyển đổi công thức mảng cũ thành công thức mảng động bằng cách định vị ô đầu tiên trong phạm vi mảng, sao chép nội dung công thức, xóa toàn bộ phạm vi mảng cũ, rồi nhập lại công thức vào ô trên cùng bên trái. Trước khi nâng cấp công thức mảng cũ thành công thức mảng động, bạn nên biết một số điểm khác biệt trong cách tính toán giữa hai công thức này.
Excel cung cấp hỗ trợ có giới hạn cho mảng động giữa các sổ làm việc và kịch bản này chỉ được hỗ trợ khi cả hai sổ làm việc đều đang mở. Nếu bạn đóng sổ làm việc nguồn thì mọi công thức mảng động được liên kết sẽ trả về lỗi #REF! khi được làm mới.