Với các phiên bản Excel trước đây, trong hầu hết các công thức ta chỉ có thể thể hiện dữ liệu đầu tiên của mảng dữ liệu. Hiện tại, Microsoft đã cho ra mắt công thức mảng động và vùng tràn trong Excel. Tính năng này đem lại những thay đổi và hiệu quả ra sao, hãy cùng Học Excel Online đọc bài viết dưới đây để tìm hiểu nhé.

Công thức mảng động và vùng tràn trong Excel

Microsoft vừa trình làng một tính năng mới cho Excel. Tính năng này sẽ thay đổi cách ta làm việc với các công thức sau này: Các công thức mảng động (Dynamic array formula)
Tính năng này cho phép trả về nhiều kết quả trên một vùng thông qua chỉ một công thức. Nó được gọi là vùng tràn (spill range).
Hiện tại Excel đang có 7 hàm mảng động mà sau này chắc chắn sẽ nhiều hơn. Ta có thể sử dụng để tạo 1 list các giá trị độc nhất (loại bỏ giá trị trùng lặp), sắp xếp danh sách, lọc dữ liệu… Một điểm nữa là những hàm cũ cũng được cải tiến để áp dụng vùng tràn này.

Chúng ta sẽ tạm biệt Ctrl + Shift + Enter

Mục đích của tính năng mới này là thay thế những công thức mảng mà ta đưa vào Excel thông qua Ctrl + Shift + Enter (CSE). Những công thức CSE phức tạp và khó đoán số lượng, giờ thì bai bai nhé. Hãy cùng đi vào những ví dụ cụ thể:

Hàm UNIQUE

Với hàm UNIQUE ta có thể tạo một list độc nhất (hay còn gọi là thao tác xóa trùng). Để dùng hàm này, chỉ cần nhập như trong hình trên thôi.

SPILL RANGE

Hãy cùng làm quen với thuật ngữ “spill” nhé. Cái “vùng tràn” này là vùng chứa kết quả của những hàm mảng động mới. Nó có thể là một hàng/cột/hàng & cột.

LỖI #SPILL

Lỗi này xuất hiện khi trong vùng tràn chứa một hoặc nhiều dữ bên ngoài. Khi gặp lỗi này, bạn có thể ấn vào “Select Obstructing Cells” để chọn toàn bộ những ô cản trở việc hiển thị kết quả, sau đó di chuyển hoặc xóa để công thức có thể hiển thị đúng.

HÀM SORT

Tương tự UNIQUE, hàm SORT cũng là một hàm mảng động mới. Kết quả trả về hàm này là danh sách đã được sắp xếp theo thứ tự.

KÝ HIỆU CHO THAM CHIẾU VÙNG TRÀN

Với công thức trên chúng ta sử dụng dấu # – đây là tham chiếu tới vùng tràn. Ký hiệu này cho phép ta tham chiếu toàn bộ vùng bằng cách đặt nó sau địa chỉ đầu tiên của ô thuộc vùng tràn đó.
Đây là một ký hiệu vô cùng hữu dụng. Bạn có thể sử dụng như một tham chiếu tới nguồn dữ liệu cho công thức mảng động khác như ví dụ sử dụng SORT. Đồng thời bạn cũng có thể kết hợp với các công thức thông thường như SUM, COUNT,… hoặc tham chiếu như VLOOKUP, INDEX, MATCH…
Kể cả đặt tên vùng hay data validation cũng không loại lệ – ta có thể sử dụng # để tham chiếu.

KẾT HỢP CÔNG THỨC MẢNG ĐỘNG

Công thức mảng động có thể kết hợp với nhau. Chẳng hạn như ta có thể rút ngắn ví dụ trên bằng việc đưa SORT và UNIQUE vào cùng một công thức.

SỬ DỤNG MẢNG ĐỘNG CHO DATA VALIDATION

Thay vì chọn cả vùng như trước, ta có thể tạo một danh sách cho data validation bằng cách đưa dấu # vào sau tham chiếu tới ô đầu tiên trong vùng tràn:
Phần thú vị nhất ở đây là vùng tràn luôn tự động update. Mọi thứ đều “động”, có nghĩa là ta không phải lo tới việc kiểm soát vùng hoặc update lại công thức. Ký hiệu tham chiếu luôn bao gồm tất cả những gì được thêm vào vùng tràn.

NHỮNG HÀM HIỆN TẠI CŨNG CÓ THỂ HIỂN THỊ VÙNG TRÀN

Ngoài những hàm mảng động mới, ngay cả những hàm đang hiện hữu trong Excel cũng có thể thể hiện dưới dạng mảng động và hiển thị kết quả trong vùng tràn.

KẾT LUẬN

Mảng động sẽ sớm phổ biến trong Excel và sẽ là một bước chuyển đổi quan trọng trong việc sử dụng công thức. Nếu bạn từng sử dụng Google Sheets, bạn sẽ biết rằng đây không hẳn là công nghệ mới mẻ gì. Tuy nhiên với việc cho ra mắt vùng tràn và tham chiếu, đây là một bước tiến lớn trong việc sử dụng công thức và những tính năng khác.