Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ cung cấp cho bạn Giới thiệu về Công thức mảng động trong Excel và Google Trang tính.
Giới thiệu
Vào tháng 9 năm 2022, Microsoft đã giới thiệu Công thức Mảng Động cho Excel. Mục đích của chúng là giúp viết các công thức phức tạp dễ dàng hơn và ít bị lỗi hơn.
Công thức mảng động có nghĩa là cuối cùng thay thế Công thức mảng, tức là các công thức nâng cao yêu cầu sử dụng Ctrl + Shift + Enter (CSE).
Dưới đây là so sánh nhanh giữa Công thức mảng và Công thức mảng động được sử dụng để trích xuất danh sách các phòng ban duy nhất từ danh sách của chúng tôi trong phạm vi A2: A7.
Công thức mảng kế thừa (CSE):
Công thức sau được nhập vào ô D2 và được nhập bằng cách nhấn Ctrl + Shift + Enter và sao chép nó xuống từ D2 đến D5.
| 1 | {= IFERROR (INDEX ($ A $ 2: $ A $ 7, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")} |
Công thức mảng động:
Công thức sau chỉ được nhập vào ô D2 và nhập bằng cách nhấn Enter. Chỉ cần nhìn thoáng qua, bạn có thể biết việc viết Công thức mảng động dễ dàng và dễ hiểu như thế nào.
| 1 | = DUY NHẤT (A2: A7) |
khả dụng
Kể từ tháng 8 năm 2022, Công thức Mảng Động chỉ khả dụng cho Người dùng Office 365.
Tràn và Phạm vi tràn
Công thức mảng động hoạt động bằng cách trả về nhiều kết quả cho một dải ô dựa trên một công thức duy nhất được nhập vào một ô.
Hành vi này được gọi là "Đổ ra" và phạm vi ô nơi đặt kết quả được gọi là "Phạm vi tràn". Khi bạn chọn bất kỳ ô nào trong phạm vi tràn, Excel sẽ đánh dấu ô đó bằng một đường viền mỏng màu xanh lam.
Trong ví dụ dưới đây, công thức mảng động LOẠI ở trong phòng giam D2 và kết quả đã được phân bổ trong phạm vi Đ2: D7
| 1 | = SORT (A2: A7) |
Kết quả của công thức là động có nghĩa là nếu thay đổi xảy ra trong phạm vi nguồn, kết quả cũng thay đổi và phạm vi tràn sẽ thay đổi kích thước.
#TRÀN!
Bạn nên lưu ý rằng nếu Phạm vi Tràn của bạn không hoàn toàn trống, thì lỗi #SPILL sẽ được trả về.
Khi bạn chọn lỗi #SPILL, Phạm vi Tràn mong muốn của công thức được đánh dấu bằng đường viền gạch ngang màu xanh lam. Di chuyển hoặc xóa dữ liệu trong ô không trống sẽ xóa lỗi này cho phép tràn công thức.
Ký hiệu tham chiếu tràn
Để tham chiếu phạm vi tràn của công thức, chúng tôi đặt # ký hiệu sau tham chiếu ô của ô đầu tiên trong vùng tràn.
Bạn cũng có thể tham chiếu sự cố tràn bằng cách chọn tất cả các ô trong phạm vi tràn và tham chiếu đến sự cố tràn sẽ được tạo tự động.
Trong ví dụ dưới đây, chúng tôi muốn đếm số lượng nhân viên trong công ty của chúng tôi bằng công thức COUNTA sau khi chúng được sắp xếp theo thứ tự bảng chữ cái bằng cách sử dụng công thức mảng động LOẠI.
Chúng tôi nhập LOẠI công thức trong D2 để sắp xếp các nhân viên trong danh sách của chúng tôi:
| 1 | = SORT (A2: A7) |
Sau đó chúng tôi nhập COUNTA công thức trong G2 để đếm số lượng nhân viên:
| 1 | = COUNTA (D2 #) |
Lưu ý việc sử dụng # trong D2 # để tham khảo các kết quả được tràn bởi SORT trong phạm vi D2: D7.
Công thức mới
Dưới đây là danh sách đầy đủ các Công thức Mảng Động mới:
- DUY NHẤT - Trả về danh sách các giá trị duy nhất từ một phạm vi
- LOẠI - Sắp xếp các giá trị trong một phạm vi
- SORTBY - Sắp xếp các giá trị dựa trên một phạm vi tương ứng
- LỌC - Lọc một phạm vi dựa trên các tiêu chí được cung cấp
- RANDARRAY - Trả về một mảng số ngẫu nhiên từ 0 đến 1
- SỰ NỐI TIẾP - Tạo danh sách các số liên tiếp như 1, 2, 3, 4, 5
Công thức mảng độngs trong Google Trang tính
Tất cả các ví dụ trên đều hoạt động hoàn toàn giống nhau trong Google Trang tính cũng như trong Excel.
