Hàm mảng động là những tính năng hữu ích và mạnh mẽ nhất. Thay vì viết công thức phức tạp sao chép xuống hàng trăm hàng, bạn viết một công thức tự động "tràn" kết quả vào bao nhiêu ô cần thiết. Khi dữ liệu thay đổi, các hàm này cập nhật theo thời gian thực. Điều này làm cho báo cáo và tóm tắt hiệu quả và ít lỗi hơn.
Trong hướng dẫn này, chúng tôi chỉ 5 cách hàm mảng động (FILTER, UNIQUE, SORT) sẽ thay đổi cách bạn làm việc.
Mảng Động & Spilling Trong Excel
Mảng động: Chèn công thức trong một ô, và Excel tự động điền hoặc tràn kết quả vào các ô lân cận. Nếu kích thước kết quả thay đổi (nhiều hàng hơn, ít hàng hơn), phạm vi tràn tự động co giãn. Bạn sẽ nhận ra phạm vi tràn vì:
- Công thức chỉ sống ở ô góc trên bên trái.
- Các ô khác hiển thị viền nhẹ, và nếu bạn click vào chúng, bạn sẽ thấy công thức bị xám.
- Bạn có thể tham chiếu toàn bộ phạm vi tràn là A2# (dấu thăng).
Mảng động có sẵn trong Microsoft 365 (Excel for Microsoft 365), Excel 2021 và mới hơn.
1. Tạo Danh Sách Duy Nhất Cho Tóm Tắt Không Trùng Lặp
Trước mảng động, loại bỏ trùng lặp yêu cầu "Remove Duplicates" hoặc công thức phức tạp. UNIQUE tạo danh sách duy nhất có thể tràn cho tóm tắt nhanh. Lý tưởng cho dropdown, danh sách validation và dashboard không cần pivot.
Liệt Kê Khu Vực Duy Nhất:
- Chọn một ô và chèn công thức sau.
Công thức này tràn danh sách sản phẩm duy nhất.
Kết Hợp Duy Nhất:
Lấy kết hợp duy nhất của Khu Vực VÀ Nhân Viên Bán Hàng:
Điều này trả về tràn hai cột hiển thị mọi kết hợp duy nhất.
Đếm Đơn Hàng Duy Nhất:
- Kết hợp UNIQUE với COUNTA cho tóm tắt.
Công thức này tính tổng số đơn hàng duy nhất. Nếu bạn thêm hàng mới trong tập dữ liệu, danh sách tự động làm mới.
Giá Trị Xuất Hiện Chính Xác Một Lần:
Tìm giá trị chỉ xuất hiện một lần duy nhất.
Tham số thứ ba (TRUE) chỉ trả về giá trị không lặp lại.
Dùng UNIQUE Cho Data Validation (Dropdown):
- Chọn ô nơi bạn muốn dropdown.
- Vào tab Data >> chọn Data Validation.
- Đặt Allow thành List.
- Trong Source, gõ:
Bây giờ dropdown luôn hiển thị khu vực duy nhất hiện tại dựa trên dữ liệu của bạn.
Tóm Tắt Nhóm Dùng UNIQUE:
Bạn có thể cặp UNIQUE với SUMIF để xây dựng tóm tắt nhóm động.
Tính tổng doanh thu các khu vực khác nhau:
=SUMIF(B2:B61, I15#, G2:G61)
Công thức này tham chiếu toàn bộ danh sách khu vực tràn (I15#). SUMIF trả về tổng doanh thu cho mỗi khu vực. Thêm hoặc thay đổi hàng trong Sales, và khu vực cùng tổng tự động điều chỉnh.
Bây giờ, thay đổi khu vực và Tổng Doanh Thu sẽ tự động cập nhật dựa trên lựa chọn.
2. Tự Động Lọc Dữ Liệu Cho Báo Cáo Động
Lọc truyền thống yêu cầu bộ lọc thủ công hoặc công thức phức tạp. Hàm FILTER() là một trong những công cụ mạnh mẽ nhất cho mảng động. Nó trích xuất chỉ các hàng đáp ứng điều kiện của bạn và tràn kết quả vào vùng giống bảng. Dùng hàm này, bạn có thể tạo báo cáo cập nhật ngay lập tức.
Lọc Bán Hàng Theo Khu Vực:
- Để thể hiện hành vi động của FILTER, dùng dropdown cho khu vực.
=FILTER(A2:G61, B2:B61="East")
- Để làm nó động hơn, tham chiếu ô tiêu chí từ dropdown.
=FILTER(A2:G61, B2:B61=I4)
Điều này tràn tất cả hàng mà Khu vực là "East." Đây là mini báo cáo mở rộng hoặc co lại nếu bạn thêm hoặc xóa dữ liệu. Thay đổi I4 thành "North," và báo cáo tự động cập nhật—không cần VBA hay làm mới thủ công.
Điều này loại bỏ bản sao tĩnh của dữ liệu; báo cáo của bạn luôn phản ánh nguồn.
Nhiều Tiêu Chí:
Lọc khu vực East VÀ số tiền trên $1,000:
=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")
Dấu sao (*) hoạt động như AND. Bạn có thể dùng cộng (+) cho điều kiện OR.
Bạn có thể xây dựng dashboard tương tác nơi người dùng chọn tiêu chí từ dropdown.
3. Tạo Danh Sách Tự Động Sắp Xếp Dùng SORT() Và SORTBY()
Sắp xếp từng nghĩa là sao chép dữ liệu hoặc dùng bảng. SORT tạo view sắp xếp động, có thể tràn. Bất cứ khi nào bạn thêm, xóa hoặc cập nhật tập dữ liệu, nó tự động sắp xếp dữ liệu.
Bảng Xếp Hạng Bán Hàng Tự Động Sắp Xếp:
Công thức này sắp xếp toàn bộ phạm vi dữ liệu theo cột 7 (Số Lượng) theo thứ tự giảm dần (-1). Dữ liệu gốc không bị ảnh hưởng. Thêm một đơn hàng top mới, và nó tự động xuất hiện ở vị trí đúng.
Nhiều Cấp Sắp Xếp:
Sắp xếp theo Nhân Viên Bán Hàng, sau đó theo Số Lượng:
=SORT(A2:G61, {3,7}, {1,-1})
Ngoặc nhọn tạo mảng: sắp xếp theo cột 3 tăng dần, sau đó cột 7 giảm dần.
Sắp Xếp Theo Tiêu Chí Khác:
Hàm SORTBY cho phép bạn sắp xếp một phạm vi dựa trên giá trị trong phạm vi khác.
Sắp xếp toàn bộ phạm vi theo tên nhân viên bán hàng trong khi giữ tất cả cột:
=SORTBY(A2:G61, C2:C61, 1)
4. Kết Hợp FILTER Và UNIQUE Cho Tóm Tắt Duy Nhất Có Điều Kiện
Cho tóm tắt nâng cao, bạn có thể kết hợp hàm để lọc trước, sau đó duy nhất hóa, tràn danh sách sạch, tự động cập nhật. Kết hợp các hàm để tạo báo cáo tự duy trì.
- Chọn một ô và chèn công thức sau.
=UNIQUE(FILTER(D2:D61, B2:B61="North"))
Điều này lọc sản phẩm từ khu vực Bắc, sau đó tràn các sản phẩm duy nhất.
- Tiếp theo, thêm hàm SORT để sắp xếp tóm tắt.
=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))
Bây giờ công thức tràn danh sách sản phẩm duy nhất đã sắp xếp.
Điều này thay thế các công thức mảng cồng kềnh như {=INDEX(…)} cho danh sách duy nhất đã lọc. Thay đổi dữ liệu hoặc tiêu chí, và nó tràn cập nhật liền mạch cho báo cáo như tồn kho sản phẩm theo khu vực.
5. Trang Tóm Tắt Động, Có Điều Kiện (Kết Hợp FILTER, UNIQUE, SORT)
Bây giờ, kết hợp các hàm này thành mini trang tóm tắt/báo cáo cập nhật từ vài ô tiêu chí.
Xây dựng dashboard cấp khu vực: dropdown khu vực (được điều khiển bởi UNIQUE), danh sách khu vực đã lọc (FILTER), và danh sách sản phẩm hàng đầu theo khu vực (FILTER + SORT).
Bước 1: Dropdown Khu Vực Dùng UNIQUE
Một danh sách khu vực duy nhất đã được tạo và dùng để xây dropdown.
Bước 2: Chi Tiết Bán Hàng Cấp Khu Vực
=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")
Công thức này lọc dữ liệu bán hàng dựa trên khu vực. Thay đổi khu vực từ dropdown và bảng bán hàng sẽ tự động cập nhật.
Bước 3: Sản Phẩm Hàng Đầu Trong Khu Vực Đã Chọn
Xác định sản phẩm nào bán nhiều nhất trong khu vực đã chọn.
- Tạo bảng nhỏ với tiêu đề Product và Total Revenue.
- Trong L4, lấy sản phẩm duy nhất bán trong khu vực đã chọn:
=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))
Điều này tràn danh sách sản phẩm cho khu vực đó.
- Trong M4, tính tổng doanh thu mỗi sản phẩm trong khu vực đó:
=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)
Điều này trả về danh sách tràn tổng doanh thu khớp với từng sản phẩm trong L4#.
- Để hiển thị chúng sắp xếp theo doanh thu (giảm dần), sắp xếp hai cột tràn cùng nhau:
=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)
Ở đây, CHOOSE({1,2}, L4#, M4#) xây dựng mảng hai cột (Sản Phẩm và Tổng Doanh Thu). 2 nghĩa là "sắp xếp theo cột thứ 2 (Tổng Doanh Thu)"; -1 nghĩa là thứ tự giảm dần.
Báo Cáo Sản Phẩm Hàng Đầu Động Trong [Khu Vực Đã Chọn]:
- Thay đổi dropdown khu vực ở B4. Lưu ý rằng tất cả tóm tắt đều cập nhật.
- Thêm dữ liệu mới vào Sales, và nó sẽ được thêm vào báo cáo.
- Không công thức sao chép, không sắp xếp thủ công, không làm mới PivotTable.
Kết Luận
Hướng dẫn này chứng minh 5 cách hàm mảng động (FILTER, UNIQUE, SORT) thay đổi cách bạn làm việc. Hàm mảng động loại bỏ việc vất vả duy trì spreadsheet. Bạn tập trung vào phân tích thay vì sao chép công thức và sửa tham chiếu hỏng. Báo cáo tự cập nhật. Dashboard tự động cập nhật. Một khi bạn bắt đầu dùng các hàm này, bạn sẽ thấy chúng lý tưởng cho tóm tắt và dashboard—bạn có thể xây dựng báo cáo cập nhật ngay lập tức mà không cần làm mới thủ công, không công thức mảng phức tạp, và không cột phụ trợ.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!