Mảng động trong Excel đã thay đổi cách sử dụng tất cả các hàm và công thức trong Excel. Để hiểu mảng động, chúng ta cần hiểu cách công thức hoạt động – một công thức trả về đầu ra cho một ô. Khi các công thức tham chiếu đến một dải ô, chúng vẫn được lập trình để trả về một giá trị duy nhất cho mỗi mục nhập.
1. Tính khả dụng trong các phiên bản Excel
- Mảng động không có sẵn trong các phiên bản trước Excel 365, do đó, chúng không khả dụng trong Excel 2019.
- Có 8 chức năng mới mà chức năng mảng động được tích hợp sẵn.
- Các hàm và công thức mảng động cũng có sẵn trong Excel Online
2. Đặc trưng
Có ba tính năng đặc biệt đáng chú ý của Mảng động Excel: Hành vi tràn tự động, toán tử phạm vi tràn và toán tử giao nhau ngầm.
1. Spill behavior
Khi công thức mảng động tự động trả về giá trị vào nhiều ô dựa trên bất kỳ đầu ra nào được yêu cầu, hành động này được gọi là Spill (tràn). Spill có một số lợi thế, bao gồm:
- – Excel xác định và áp dụng vùng đầu ra được yêu cầu mà không có bất kỳ thay đổi nào trong cách nhập công thức.
- – Khi dữ liệu nguồn thay đổi, kết quả tràn sẽ cập nhật ngay lập tức.
- – Giảm nhu cầu tham khảo tuyệt đối và tương đối.
- – Giảm nhu cầu sao chép công thức trong tập dữ liệu.
- – Khả năng dễ dàng tạo danh sách xác thực dữ liệu và phạm vi được đặt tên cập nhật khi danh sách mở rộng hoặc hợp đồng.
Vùng đầu ra của công thức mảng động được gọi là phạm vi tràn. Phạm vi tràn được xác định bởi một hình chữ nhật xung quanh khu vực đầu ra khi ít nhất một trong các ô được chọn.
Tương tự như các công thức CSE của Excel, chỉ ô đầu tiên trong vùng tràn là có thể chỉnh sửa được. Các công thức trong các ô khác có màu xám và không thể sửa đổi. Nếu sửa đổi trong phạm vi tràn sẽ phá hủy phạm vi và dẫn đến lỗi #SPILL!
2. Toán tử phạm vi tràn
Excel đã giới thiệu toán tử phạm vi tràn , #, để biểu thị kết quả đầu ra của một mảng tràn.
Ví dụ: D2# đề cập đến phạm vi tràn bắt đầu bằng ô D2, được điền bằng công thức mảng động UNIQUE (B2: B7). Công thức trong ô E2 tham chiếu đến các giá trị trong ô D2: D5 với biểu thức D2# .
=COUNTIF(B2:B7,D2#)
3. Toán tử giao lộ ngầm
Logic giao nhau ngầm không phải là mới. Excel đã làm điều này trong nền với Pre-Dynamic Excel. Trong bảng Excel, toán tử giao nhau ngầm, @, từ lâu đã được sử dụng để chỉ ra một phép toán tham chiếu đến một giá trị trong cùng một hàng với công thức. Giao lộ ngầm định buộc một công thức trả về một giá trị duy nhất vì một ô chỉ có thể chứa một giá trị duy nhất.
Trong ví dụ dưới đây, công thức sau được nhập vào Pre-Dynamic Excel.
=A4:A8/SUM($A$4:$A$8)
Pre-Dynamic Excel không thể đổ kết quả đến các ô B4: B8 và phải quyết định giá trị nào sẽ trả về trong ô nơi công thức được nhập. Mặc dù biểu tượng @ không được nhìn thấy, Excel sử dụng giao điểm ngầm ẩn trong nền để trả về giá trị bằng cách sử dụng ô trong cùng hàng hoặc cột với công thức.
Với khả năng trả về nhiều giá trị trong Dynamic Excel, công thức tương tự này sẽ tự động đổ kết quả đến phạm vi B4: B8.
Nếu bạn muốn buộc Excel trả về một đầu vào, hãy nhập thủ công toán tử giao nhau ngầm, @ và Excel sẽ sử dụng giá trị trong hàng tương ứng để thực hiện phép tính.
Ví dụ: trong hình ảnh bên dưới, mảng H3: I6 sử dụng hàm SORT để sắp xếp Doanh số bán hàng theo khu vực từ cao xuống thấp thay vì theo thứ tự bảng chữ cái.
=SORT(E3:F6,2,-1)
Tuy nhiên, nếu chỉ quan tâm đến việc biết tên của khu vực bán hàng hàng đầu, chúng ta có thể đặt toán tử ngầm trước tên hàm, buộc Excel phải trả về một giá trị duy nhất.
=@SORT(E3:F6,2,-1)
Khi quá trình chuyển đổi sang Mảng động Excel, bạn sẽ thấy toán tử giao nhau ngầm để tương thích ngược. Khi một công thức kích hoạt giao cắt ngầm được tạo trong Pre-Dynamic Excel và sau đó được mở trong môi trường động, toán tử @ bây giờ có thể hiển thị để cho biết lý do tại sao chỉ một giá trị duy nhất được trả về.
3. Hàm & công thức mảng động
Có 8 hàm mảng động mới đi kèm với các đặc tính mảng động được tích hợp sẵn, có nghĩa là các hàm này được xây dựng để trả về phạm vi động. Các chức năng này là:
- – FILTER – lọc các bản ghi sử dụng các tiêu chí.
- – RANDARRAY – tạo một danh sách hoặc một mảng số ngẫu nhiên.
- – SEQUENCE – tạo danh sách tuần tự hoặc mảng số.
- – SORT – sắp xếp danh sách hoặc mảng theo cột hoặc hàng trong mảng đó.
- – SORTBY – sắp xếp danh sách hoặc mảng theo danh sách hoặc mảng khác.
- – UNIQUE – trích xuất các giá trị duy nhất từ một danh sách.
- – XLOOKUP – tra cứu linh hoạt trả về các giá trị tương ứng trong danh sách hoặc mảng.
- – XMATCH – tra cứu linh hoạt trả về vị trí tương đối của các giá trị trong danh sách hoặc mảng.
4. Kết hợp các hàm mảng động
Các chức năng trên độc lập, nhưng việc kết hợp chúng cho phép bạn hoàn thành nhiều nhiệm vụ trong một mục nhập. Dưới đây là một vài ví dụ.
Ví dụ 1 – SẮP XẾP và LỌC
SORT(array, [sort_index], [sort_order], [by_col])FILTER(array, include, [if_empty])Với sự kết hợp này, hàm SORT được bao quanh bởi hàm FILTER để sắp xếp các kết quả của một mảng đã lọc.
=SORT(FILTER(A2:D12,B2:B12="sandwich"),4)
Công thức trong ô F2 lọc mảng A2: D12 bằng cách tìm kiếm các bản ghi có danh mục “sandwich”. Hàm SORT sắp xếp mảng này theo giá, cột thứ 4.
Ví dụ 2 – SORT và UNIQUE
SORT(array, [sort_index], [sort_order], [by_col])UNIQUE(array, [by_col], [exactly_once])Sử dụng SORT xung quanh hàm UNIQUE để trích xuất các giá trị duy nhất trong một mảng và sau đó sắp xếp các bản ghi phù hợp với một tiêu chí nhất định.
Hàm UNIQUE ở trên trả về các giá trị khác biệt trong phạm vi B3: B8 và hàm SORT được sử dụng để sắp xếp theo thứ tự bảng chữ cái. Các kết quả được tự động đổ vào phạm vi E3: E6.
Có các tùy chọn khác cho các hàm UNIQUE và SORT.
Ví dụ 3 – COUNTA và UNIQUE
COUNTA(value1, [value2]…)UNIQUE(array, [by_col], [exactly_once])Khi COUNTA được lồng với UNIQUE, bạn có thể nhanh chóng đếm số lượng giá trị duy nhất trong một mảng bằng cách sử dụng một mục nhập duy nhất.
=COUNTA(UNIQUE(Database[Team Name]))
Ví dụ 4 – FILTER và FILTER
FILTER(array, include, [if_empty])Bạn thậm chí có thể lồng một hàm FILTER trong một FILTER khác để trả về các cột đã chọn của các bản ghi đã lọc!
=FILTER(FILTER(Menu,Menu[Price]<5),{1,0,1})
Sử dụng hằng số mảng {1,0,1} làm đối số bao gồm của hàm FILTER bên ngoài yêu cầu Excel loại trừ cột thứ 2 (vì 1 = TRUE và 0 = FALSE).
5. Danh sách xác thực dữ liệu mảng động
Trước đó, việc tạo danh sách thả xuống động để xác thực dữ liệu là một nhiệm vụ từ trung cấp đến nâng cao vì Excel không có phương pháp tích hợp để xử lý việc này.
Sau khi danh sách nguồn được tạo bằng hàm hoặc công thức động, việc tạo danh sách xác thực dữ liệu động từ một mảng tràn giờ đây đơn giản như sử dụng chỉ báo phạm vi tràn.
Bước 1 – Tạo danh sách nguồn bằng công thức hoặc hàm mảng động
Trong ví dụ bên dưới, hàm SORT được sử dụng để tạo danh sách nguồn theo bảng chữ cái.
=SORT(Menu[Item Name])
Bước 2 – Sử dụng chỉ báo phạm vi tràn để tham khảo đầu ra động
- – Từ ô mà bạn muốn có danh sách thả xuống, hãy chuyển đến tab Dữ liệu và chọn lệnh Xác thực Dữ liệu. Chọn List từ menu thả xuống Allow .
- – Tham khảo phạm vi tràn trong trường Source. Chỉ báo phạm vi tràn phải được nhập thủ công sau khi tham chiếu đến ô đầu tiên trong phạm vi tràn. Nếu xác thực dữ liệu sẽ được sao chép sang các ô khác, nên sử dụng tham chiếu ô tuyệt đối
=$A$14#
6. Lỗi khi sử dụng mảng động
Khi làm việc với công thức mảng động, bạn có thể gặp một số thông báo lỗi mới. Thay vì hiểu sai về chúng, đây là danh sách nhanh về những nguyên nhân phổ biến hơn và những nguyên nhân có thể xảy ra.
#SPILL!
Phạm vi tràn không trống
Excel sẽ trả về lỗi #SPILL! thông báo lỗi trong ô đầu tiên của phạm vi tràn nếu có vật cản, nghĩa là, nếu bất kỳ ô nào trong vùng đầu ra được yêu cầu không trống. Để sửa lỗi này, hãy xóa (các) giá trị trong (các) ô cản trở hoặc di chuyển công thức đến vị trí có đủ ô trống để chứa đầu ra.
Phạm vi tràn không xác định
Lỗi này có thể xảy ra khi Excel không thể xác định có bao nhiêu hàng và cột sẽ cần thiết cho đầu ra.
Ví dụ: Khi các hàm biến đổi trả về các giá trị khác nhau mỗi khi công thức được tính toán lại (ví dụ: RAND, RANDBETWEEN, RANDARRAY) được lồng với một số hàm động khác. Xem ví dụ bên dưới.
Lưu ý rằng cùng một công thức có thể hoạt động trong một số lần lặp lại và không thành công trong một số lần lặp khác chỉ đơn giản là do hàm không ổn định. Bạn có thể thử lại hoặc tính toán công thức từng bước, tức là tạo các giá trị ngẫu nhiên bằng công thức, sao chép và dán chúng dưới dạng giá trị, sau đó đặt chúng vào một mảng theo cách thủ công.
# CALC!
Mảng trống không được hỗ trợ
Vì Mảng động Excel không hỗ trợ mảng trống, nên bất kỳ công thức nào dẫn đến mảng trống sẽ trả về lỗi #CALC!
Ví dụ: Nếu không có bản ghi nào trong mảng nguồn khớp với tiêu chí trong đối số bao gồm của hàm FILTER, thì Excel sẽ trả về lỗi #CALC!. Trong trường hợp đó, giải pháp sẽ là bao gồm đối số if_empty tùy chọn để hiển thị một phản hồi thay thế.
Nguồn: Goskills
Xem thêm
Combo 3 Khóa Học Kỹ Năng Excel Nâng Cao