Bạn đã quen thuộc với mảng (array) trong Excel? Bài viết này sẽ đi sâu vào mảng lồng (nested array), phân tích sự khác biệt trong cách xử lý giữa công thức mảng cũ (CSE) và công thức mảng động (Dynamic Array).
Mảng Lồng là gì?
Trong Excel, mảng được tạo bằng nhiều cách, đơn giản nhất là sử dụng dấu ngoặc nhọn: {1;2;3}. Đây là mảng chứa 3 phần tử. Vậy mảng lồng là gì? Đơn giản là “mảng trong mảng”, ví dụ: {{1;2;3};{4;5;6};{7;8;9}}. Tuy nhiên, ta không thể nhập trực tiếp nested array bằng hai dấu ngoặc nhọn. Một cách tạo nested array là sử dụng hàm INDEX:
Ví dụ hàm INDEX tạo mảng lồng
=INDEX({1,4,7;2,5,8;3,6,9},0,{1;2;3})Hàm INDEX trên trả về {{1;2;3};{4;5;6};{7;8;9}}. Tuy nhiên, Excel không hiển thị trực tiếp nested array:
Excel không hiển thị mảng lồng
Chỉ phần tử đầu tiên của mảng con đầu tiên (số 1) được hiển thị. Các phần tử khác bị ẩn.
Xử Lý Mảng Lồng với Mảng Động (Dynamic Array)
Mảng động (Dynamic Array) trong Office 365 giúp viết và trả về mảng dễ dàng hơn. Vậy Dynamic Array xử lý mảng lồng như thế nào? Nó “đơn giản hóa” bằng cách hiển thị phần tử đầu tiên của mỗi mảng con và loại bỏ cấu trúc lồng.
Trong ví dụ trên, Dynamic Array lấy 3 phần tử đầu tiên của các mảng con (1, 4, 7):
Dynamic Array đơn giản hóa mảng lồng
Nếu dùng hàm SUM, kết quả sẽ là 12:
Hàm SUM với Dynamic Array
Xử Lý Mảng Lồng với Công Thức Mảng Cũ (CSE)
Công thức mảng cũ (CSE – Ctrl + Shift + Enter) xử lý mảng lồng khác với Dynamic Array.
Công thức mảng CSE
Với công thức mảng tại F1, kết quả là 6, không phải 12:
Kết quả CSE tại F1
Nếu chọn 2 ô và dùng CSE, kết quả là 15:
Kết quả CSE với 2 ô
Với 3 ô, kết quả là 6, 15, 24. Tại sao? Mỗi ô từ F1 đến F3 tương ứng với tổng của một mảng con trong {{1;2;3};{4;5;6};{7;8;9}}.
Lưu ý về Hàm và Mảng Lồng
INDEX: Trả về nested array nếu đối sốrow_numberhoặccolumn_numberlà mảng.LOOKUP: Không trả về nested array.VLOOKUP: Có thể trả về nested array tùy thuộc vào số ô áp dụng CSE.ROW: Trong Office 2013 trả về nested array, nhưng không trong Office 365.
Ví dụ Tính Running Total
Cho bảng dữ liệu sau:
Bảng dữ liệu ví dụ
Tính running total cho từng dòng bằng công thức mảng CSE:
=TRANSPOSE(MMULT(TRANSPOSE(TRANSPOSE(INDEX(C3:F5,{1;2;3},))*(ROW(INDIRECT("1:"&COUNTA(INDEX(C3:F5,{1;2;3},))))Chọn vùng H7:K9, nhập công thức và nhấn CSE:
Kết quả Running Total
Lưu ý: Công thức này không hoạt động với Dynamic Array và có thể không hoạt động trong Office 2013.
Kết luận
Việc hiểu rõ sự khác biệt giữa cách xử lý mảng lồng của Dynamic Array và công thức mảng CSE giúp bạn sử dụng Excel hiệu quả hơn. Mảng động đơn giản hóa việc làm việc với mảng, trong khi CSE cung cấp khả năng kiểm soát chi tiết hơn. Hãy lựa chọn phương pháp phù hợp với nhu cầu của bạn.
Discussion about this post