Cách sử dụng các hàm mảng động trong Microsoft Excel

Khi tôi lần đầu tiên khám phá các hàm mảng động của Excel, toàn bộ cách tiếp cận thiết kế bảng tính của tôi đã thay đổi. Chúng thay thế những giải pháp phức tạp, nhiều bước của tôi bằng các công thức thông minh, tự mở rộng và thích ứng khi dữ liệu tăng lên, biến các quy trình làm việc phức tạp thành các hệ thống trong một ô duy nhất.

Nếu bạn vẫn đang quản lý các bộ dữ liệu bằng cách sao chép-dán thủ công hoặc kéo các công thức dễ bị hỏng xuống hàng nghìn dòng, việc thành thạo các hàm cơ bản này sẽ hoàn toàn biến đổi cách bạn tương tác với Excel.

Cách các mảng động thay đổi luồng dữ liệu trong Excel

Sự kết thúc của CSE và sự gia tăng của phạm vi tràn

Truyền thống, các công thức Excel tạo ra một kết quả trong một ô, buộc bạn phải sao chép các phép tính xuống toàn bộ cột. Các mảng động thay đổi cơ bản quy trình làm việc này bằng cách cho phép một công thức duy nhất xuất ra một khối dữ liệu tự động mở rộng hoặc thu hẹp dựa trên kích thước nguồn.

Hành vi này được gọi là tràn (spilling). Khi bạn nhập một công thức và nhấn Enter, dữ liệu sẽ xuất hiện trong một vùng được đánh dấu bởi một đường viền xanh mỏng gọi là phạm vi tràn (spill range). Quan trọng là, khả năng này đến từ một động cơ tính toán được nâng cấp chứ không chỉ là một bộ hàm mới. Trong Excel hiện đại, các công thức có thể trả về nhiều kết quả, và cả các hàm mới lẫn hàm hiện có đều có thể tràn vào các ô liền kề khi cần thiết. Điều này thay thế nhu cầu sử dụng các công thức mảng cũ Ctrl+Shift+Enter (CSE), vốn nổi tiếng khó xây dựng và gỡ lỗi.

BỘ LỌC: Trích xuất tất cả các bản ghi phù hợp

Cô lập các hàng cụ thể mà không cần nhấn bất kỳ nút nào

Trước đây, lọc một tập dữ liệu Excel có nghĩa là sử dụng nút "Lọc" trên thanh công cụ, đánh dấu các ô, và sao chép kết quả sang nơi khác. Nhưng ngay khi dữ liệu nguồn thay đổi, những kết quả đó trở nên lỗi thời.

Hàm FILTER hoàn toàn thay thế quy trình làm việc đó. Thay vì ẩn các hàng, nó trả về dữ liệu khớp vào một vùng tràn riêng:

=FILTER(array, include, [if_empty])

Giả sử bạn có một bảng Excel tên là EmployeeMaster. Khi bạn chọn một khu vực trong ô G2, bạn muốn kết quả khớp tràn ra từ ô I2.

Sau khi nhập công thức sau vào I2, kết quả sẽ tự động cập nhật khi dữ liệu thay đổi hoặc khi một khu vực khác được chọn:

=FILTER(EmployeeMaster, EmployeeMaster[Region]=G2, "Region not found")

SẮP XẾP THEO: Tổ chức danh sách một cách thực tiễn

Từ sắp xếp cố định đến sắp xếp dựa trên tham chiếu

Việc sắp xếp cơ bản trên thanh công cụ ("A-Z") hoạt động để sắp xếp lại các danh sách tĩnh, nhưng nó nhanh chóng gặp vấn đề trong các bảng điều khiển động, nơi các hàng mới liên tục được thêm vào bộ dữ liệu. Hàm SORT của Excel đã cải thiện điều này bằng cách biến việc sắp xếp trở thành một thao tác dựa trên công thức. Tuy nhiên, nó dựa vào vị trí cột, điều này làm cho nó dễ bị lỗi khi cấu trúc bộ dữ liệu của bạn thay đổi theo thời gian.

Một cách tiếp cận linh hoạt hơn là hàm SORTBY, hàm này loại bỏ hoàn toàn sự phụ thuộc đó. Thay vì sắp xếp theo chỉ số cột, nó sắp xếp bằng cách sử dụng các mảng tham chiếu rõ ràng—nghĩa là logic của bạn liên kết trực tiếp với dữ liệu, chứ không phải bố cục vật lý của nó. Điều này chuyển việc sắp xếp từ logic dựa trên vị trí sang logic dựa trên dữ liệu, điều mà bền vững hơn nhiều trong các bảng điều khiển thực tế:

=SORTBY(array, by_array, [sort_order])

Ví dụ, trong bộ dữ liệu EmployeeMaster, bạn có thể sắp xếp trực tiếp theo MonthlySales mà không cần dựa vào vị trí cột hoặc thay đổi bộ dữ liệu cơ sở:

=SORTBY(EmployeeMaster, EmployeeMaster[MonthlySales], -1)

Bởi vì nó tham chiếu trực tiếp đến trường bằng cách sử dụng tham chiếu có cấu trúc, lớp sắp xếp vẫn tồn tại khi dữ liệu thay đổi cấu trúc. Tại thời điểm này, bạn không còn quản lý các phép tính—bạn đang định nghĩa cách dữ liệu chảy qua sổ làm việc.

ĐẶC BIỆT: Dọn dẹp dữ liệu lộn xộn bằng cách loại bỏ các bản sao

Loại bỏ các mục lặp lại để xây dựng các chiều sạch

Việc xây dựng các danh sách sạch gồm các phòng ban, tên hoặc ID trong Excel trước đây đòi hỏi các công cụ phá hủy như "Remove Duplicates", mà không phản ứng với dữ liệu mới.

Bây giờ, hàm UNIQUE trả về một danh sách trực tiếp các giá trị riêng biệt:

=UNIQUE(array, [by_col], [exactly_once])

Ví dụ, gõ:

=UNIQUE(EmployeeMaster[Department])

vào ô P1 quét bảng của bạn và tạo ra một danh sách sạch các phòng ban duy nhất, danh sách này mở rộng khi có phòng ban mới được thêm vào tập dữ liệu nguồn.

XLOOKUP: Truy xuất mảng dữ liệu mà không làm hỏng bố cục

Kéo nhiều cột liền kề cùng lúc từ một giá trị tra cứu duy nhất

Hàm XLOOKUP thường được coi là chỉ là một sự thay thế cho VLOOKUP, nhưng sức mạnh thực sự của nó trong Excel hiện đại nằm ở cách nó phù hợp một cách tự nhiên với mô hình spill. Nó có thể trả về toàn bộ bản ghi thay vì giá trị đơn lẻ, tràn kết quả theo chiều ngang khi được cung cấp một phạm vi trả về nhiều cột, mà không phụ thuộc vào vị trí cột.

Đây là cú pháp:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Trong bảng EmployeeMaster của chúng tôi, bạn có thể lấy đầy đủ thông tin nhân viên bằng cách truyền một mục tìm kiếm từ ô G2, so khớp với cột ID, và đặt mảng trả về trải dài qua nhiều tiêu đề:

=XLOOKUP(G2, EmployeeMaster[EmployeeID], EmployeeMaster[[EmployeeName]:[MonthlySales]])

Kết quả tràn ngang từ ô công thức sang các cột liền kề, và vì nó không phụ thuộc vào các chỉ số cột cố định, nó vẫn ổn định ngay cả khi cấu trúc bảng thay đổi.

VSTACK và HSTACK: Kết hợp các nguồn dữ liệu rải rác

Xếp chồng bố cục nhanh, theo công thức gốc mà không cần công cụ nặng

Hợp nhất các bộ dữ liệu Excel riêng biệt truyền thống thường yêu cầu sao chép thủ công hoặc sử dụng Power Query. Đối với các quy trình làm việc nhẹ, ưu tiên công thức, VSTACK (xếp chồng theo chiều dọc) và HSTACK (xếp chồng theo chiều ngang) cho phép bạn kết hợp các mảng trực tiếp trong các phép tính ô mà không cần khởi chạy các công cụ chuẩn bị dữ liệu bên ngoài. Điều này làm cho chúng trở nên lý tưởng cho việc tổng hợp dữ liệu nhanh, ưu tiên công thức.

Cú pháp để xếp chồng theo chiều dọc là:

=VSTACK(array1, [array2], ...)

Và để xếp chồng theo chiều ngang, bạn sử dụng:

=HSTACK(array1, [array2], ...)

Quy trình làm việc này phát huy hiệu quả khi bạn có các bảng riêng biệt đại diện cho các nhật ký đơn giản, theo chu kỳ. Ví dụ, giả sử bạn có ba bảng Excel chứa số liệu nhân viên theo quý (Q1_Master, Q2_Master, và Q3_Master). Bạn có thể xếp chúng nối tiếp nhau bằng cách gõ:

=VSTACK(Q1_Master, Q2_Master, Q3_Master)

Điều này tạo ra một bộ dữ liệu thống nhất duy nhất ngay trong lưới của bạn, tự động nối các hàng một cách liền mạch và phản ánh các thay đổi trong các bảng nguồn mà không cần hợp nhất thủ công.

Ngoài các chức năng mảng động cơ bản

Bản đồ năng lực của các công cụ dựa trên kết quả tràn trong Excel

Khi bạn hiểu các hàm mảng động cơ bản, Excel mở rộng cùng một mô hình dựa trên spill này vào một tập hợp các tùy chọn chuyên biệt rộng hơn:

Cùng nhau, những điều này mở rộng cùng một nguyên tắc trên toàn Excel: dữ liệu không còn được xử lý thủ công, mà liên tục được định hình lại thông qua các lớp công thức kết nối.

Làm chủ tương lai của bảng tính

Mảng động là một trong những thay đổi mang tính cách mạng nhất mà Microsoft đã thực hiện đối với Excel trong nhiều năm, thay thế các quy trình làm việc cũ cồng kềnh bằng một mô hình nơi dữ liệu được cập nhật, định dạng lại và tính toán lại khi sổ làm việc phát triển. Khi bạn bắt đầu tận dụng tối đa quy trình làm việc hiện đại này, việc biết cách sửa các lỗi #SPILL! trong Excel sẽ giúp mọi thứ hoạt động trơn tru.