Hàm mảng động là một trong những tính năng hữu ích và mạnh mẽ nhất. Thay vì viết các công thức phức tạp mà bạn sao chép 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 nhiều ô nếu cần. Khi dữ liệu của bạn thay đổi, các chức năng này sẽ cập nhật theo thời gian thực. Điều này làm cho các báo cáo và tóm tắt hiệu quả hơn và ít xảy ra lỗi hơn.
Trong hướng dẫn này, chúng tôi trình bày 5 cách các hàm mảng động (FILTER, UNIQUE, SORT) sẽ thay đổi cách bạn làm việc.
Mảng động &tràn trong Excel
Mảng động: Chèn công thức vào một ô và Excel sẽ 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 sẽ tự động tăng hoặc co lại. Bạn sẽ nhận ra một phạm vi bị đổ vì:
- Công thức chỉ tồn tại ở ô trên cùng bên trái.
- Các ô khác hiển thị đường viền sáng và nếu bạn nhấp vào chúng, bạn sẽ thấy công thức có màu xám.
- Bạn có thể gọi toàn bộ phạm vi bị đổ là A2# (ký hiệu băm).
Mảng động có sẵn trong Microsoft 365 (Excel cho Microsoft 365), Excel 2021 trở lên.
1. Tạo danh sách duy nhất cho bản tóm tắt mà không trùng lặp
Trước mảng động, việc loại bỏ các bản sao cần phải có “Xóa các bản sao” hoặc các công thức phức tạp. UNIQUE tạo ra các danh sách độc đáo, có thể chia sẻ để tóm tắt nhanh chóng. Nó lý tưởng cho danh sách thả xuống, danh sách xác thực và trang tổng quan không có trục.
Liệt kê vùng duy nhất:
- Chọn một ô và chèn công thức sau.
Công thức này đưa ra một danh sách các sản phẩm độc đáo.
Sự kết hợp độc đáo:
Nhận các kết hợp độc đáo giữa Khu vực VÀ Nhân viên bán hàng:
Điều này trả về một cột tràn hai cột hiển thị mọi kết hợp duy nhất.
Đếm số đơn đặt hàng duy nhất:
- Kết hợp UNIQUE với COUNTA để có bản tóm tắt.
Công thức này tính toán tổng số đơn đặt hàng duy nhất. Nếu bạn thêm hàng mới vào tập dữ liệu, danh sách sẽ tự động làm mới.
Các giá trị xuất hiện chính xác một lần:
Tìm các giá trị chỉ xảy ra một lần.
Đối số thứ ba (TRUE ) chỉ trả về các giá trị không được lặp lại.
Sử dụng UNIQUE để xác thực dữ liệu (Thả xuống):
- Chọn ô nơi bạn muốn thả xuống.
- Đi tới Dữ liệu tab>> chọn Xác thực dữ liệu .
- Đặt Cho phép vào Danh sách .
- Trong Nguồn , gõ:
Bây giờ, danh sách thả xuống luôn hiển thị các vùng duy nhất hiện tại dựa trên dữ liệu của bạn.
Tóm tắt được nhóm bằng cách sử dụng UNIQUE:
Bạn có thể ghép UNIQUE với SUMIF để tạo một bản tóm tắt được nhóm động.
Tính tổng doanh thu của các vùng khác nhau:
=SUMIF(B2:B61, I15#, G2:G61)
Công thức này đề cập đến toàn bộ danh sách các vùng bị tràn (I15# ). SUMIF trả về tổng doanh thu cho từng khu vực. Thêm hoặc thay đổi các hàng trong Doanh số, đồng thời các khu vực và tổng số sẽ tự động điều chỉnh.
Bây giờ, hãy 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 dành cho mảng động. Nó chỉ trích xuất những hàng đáp ứng điều kiện của bạn và đưa kết quả vào một phạm vi giống như bảng. Sử dụng chức năng này, bạn có thể tạo báo cáo cập nhật ngay lập tức.
Lọc doanh số bán hàng theo khu vực:
- Để hiển thị hành vi động của hàm LỌC, hãy sử dụng trình đơn thả xuống cho vùng.
=FILTER(A2:G61, B2:B61="East")
- Để làm cho ô tiêu chí linh hoạt hơn, hãy tham chiếu ô tiêu chí từ trình đơn thả xuống.
=FILTER(A2:G61, B2:B61=I4)
Điều này sẽ tràn ra tất cả các hàng trong đó Khu vực là "Phía Đông". Đó là một báo cáo nhỏ mở rộng hoặc thu gọn nếu bạn thêm hoặc xóa dữ liệu. Thay đổi I4 thành “North” và báo cáo sẽ tự động cập nhật—không cần làm mới VBA hoặc thủ công.
Điều này giúp loại bỏ các bản sao dữ liệu tĩnh; báo cáo của bạn luôn phản ánh nguồn.
Nhiều tiêu chí:
Lọc cho khu vực phía Đông VÀ số tiền trên 1.000 USD:
=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")
Dấu hoa thị (* ) hoạt động như AND. Bạn có thể sử dụng dấu cộng (+ ) với điều kiện OR.
Bạn có thể xây dựng trang tổng quan tương tác nơi người dùng chọn tiêu chí từ danh sách thả xuống.
3. Tạo danh sách được sắp xếp tự động bằng SORT() và SORTBY()
Sắp xếp được sử dụng có nghĩa là sao chép dữ liệu hoặc sử dụng bảng. SORT tạo ra các chế độ xem được sắp xếp linh hoạt, có thể tràn ra. Bất cứ khi nào bạn thêm, xóa hoặc cập nhật tập dữ liệu, nó sẽ tự động sắp xếp dữ liệu.
Bảng xếp hạng doanh số bán hàng được sắp xếp tự động:
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 vẫn còn nguyên. Thêm một ưu đãi giảm giá mới và nó sẽ tự động xuất hiện ở đúng vị trí.
Nhiều cấp độ sắp xếp:
Sắp xếp theo Nhân viên bán hàng, sau đó theo Số tiền:
=SORT(A2:G61, {3,7}, {1,-1})
Dấu 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 nhau:
Hàm SORTBY cho phép bạn sắp xếp một phạm vi dựa trên các 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 vẫn giữ tất cả các cột:
=SORTBY(A2:G61, C2:C61, 1)
4. Kết hợp LỌC và ĐỘC ĐÁO để có các bản tóm tắt duy nhất được nhắm mục tiêu
Đối với các bản tóm tắt nâng cao, bạn có thể kết hợp các chức năng để lọc trước, sau đó thống nhất, tạo ra một danh sách sạch sẽ, tự động cập nhật. Kết hợp các chức năng để 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"))
Cái này lọc sản phẩm từ miền Bắc, sau đó tung ra những sản phẩm độc đáo.
- Tiếp theo, thêm hàm SORT để sắp xếp bản tóm tắt.
=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))
Bây giờ công thức sẽ đưa ra một danh sách sản phẩm duy nhất được sắp xếp.
Điều này thay thế các công thức mảng rườm rà như {=INDEX(…)} cho các danh sách được lọc duy nhất. Thay đổi dữ liệu hoặc tiêu chí và nó sẽ cung cấp thông tin cập nhật một cách liền mạch cho các báo cáo như kiểm kê sản phẩm trong khu vực.
5. Các trang tóm tắt động, dựa trên tiêu chí (Kết hợp LỌC, ĐỘC ĐÁO, SẮP XẾP)
Bây giờ, hãy kết hợp các chức năng này thành một trang báo cáo/tóm tắt nhỏ cập nhật từ một số ô tiêu chí.
Xây dựng trang tổng quan cấp khu vực:danh sách thả xuống khu vực (do UNIQUE điều khiển), danh sách khu vực được lọc (LỌC) và danh sách các sản phẩm hàng đầu theo khu vực (LỌC + SẮP XẾP).
Bước 1:Thả xuống khu vực bằng cách sử dụng UNIQUE
Một danh sách các vùng duy nhất đã được tạo và sử dụng để tạo danh sách thả xuống.
Bước 2:Chi tiết doanh số 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ừ menu thả xuống và bảng bán hàng sẽ tự động cập nhật.
Bước 3:Sản phẩm hàng đầu ở khu vực đã chọn
Xác định sản phẩm nào bán được nhiều nhất ở khu vực đã chọn.
- Tạo một bảng nhỏ có tiêu đề Sản phẩm và Tổng doanh thu.
- Ở L4, lấy các sản phẩm độc đáo được bán ở khu vực đã chọn:
=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))
Thao tác này sẽ hiển thị danh sách các sản phẩm cho khu vực đó.
- Trong M4, tính tổng doanh thu trên mỗi sản phẩm ở khu vực đó:
=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)
Điều này trả về một danh sách tổng doanh thu khớp với từng sản phẩm trong L4# .
- Để hiển thị chúng được sắp xếp theo doanh thu (giảm dần), hãy sắp xếp hai cột bị chia tách với nhau:
=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)
Đây, CHOOSE({1,2}, L4#, M4#) xây dựng một mảng hai cột (Sản phẩm và Tổng doanh thu). 2 có nghĩa là “sắp xếp theo cột thứ 2 (Tổng doanh thu)”; -1 có nghĩa là thứ tự giảm dần.
Báo cáo Sản phẩm động hàng đầu ở [Khu vực đã chọn]:
- Thay đổi danh sách thả xuống vùng trong B4. Lưu ý rằng tất cả các bản tóm tắt đều được cập nhật.
- Thêm dữ liệu mới vào Bán hàng và dữ liệu đó sẽ được thêm vào báo cáo.
- Không sao chép công thức, 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 trình bày 5 cách các hàm mảng động (FILTER, UNIQUE, SORT) thay đổi cách bạn làm việc. Các hàm mảng động loại bỏ công việc bận rộn trong việc duy trì bảng tính. Bạn tập trung vào phân tích thay vì sao chép công thức và sửa các tham chiếu bị hỏng. Các báo cáo tự cập nhật. Bảng điều khiển cập nhật tự động. Sau khi bắt đầu sử dụng các hàm này, bạn sẽ thấy chúng lý tưởng cho các bản tóm tắt và trang tổng quan—bạn có thể tạo 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ó công thức mảng phức tạp và không có cột trợ giúp.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!