Sự khác biệt khi xử lý nested array của công thức mảng cũ và mới trong Excel

Chúng ta hẳn ít nhiều đã biết về khái niệm mảng – array trong Excel rồi phải không nào? Trong bài viết này, SprinGo sẽ nói đến nested array (mảng lồng), cũng như sự khác biệt của công thức mảng cũ (CSE) và công thức mảng mới (Dynamic array) khi xử lý nested array nhé.

Bài toán về Nested array

Trong Excel, ta có thể tạo ra một mảng bằng các cách khác nhau. Với cách đơn giản nhất, ta viết trong dấu ngoặc nhọn như hình:

Tại đây, ta đã tạo ra 1 mảng gồm 3 phần tử bao gồm 3 phần tử: 1, 2, 3.

Trong tài liệu Excel từng chú thích mảng sẽ luôn luôn là mảng 2 chiều, tuy nhiên đó sẽ là nội dung trong một bài viết khác. Với bài viết này, ta sẽ tập trung vào mảng gộp – nested array.

Vậy nested array là gì?

Có thể hình dung đơn giản, thay vì {1;2;3} giờ chúng ta có {{1;2;3};{4;5;6};{7;8;9}}. Cũng có nghĩa là – mảng trong mảng.

Tuy nhiên, ta không thể viết trực tiếp 2 dấu ngoặc nhọn. Mà để tạo ra nested array ta có thể sử dụng một vài phương pháp, chẳng hạn như dùng hàm INDEX:

Hàm sẽ cho ra kết quả: {{1;2;3};{4;5;6};{7;8;9}}. Tuy nhiên:

Excel sẽ không thể hiển thị ra được, bởi các mảng đang đè lên nhau. Các bạn có thể hiểu ngầm rằng, bên dưới số 1 kia thực tế là mảng {1;2;3}.

Cách Dynamic Array xử lý nested array

Dynamic Array (mảng động) xuất hiện đầu tiên trong phiên bản Office 365. Tính năng này cho phép chúng ta viết và trả về mảng dễ dàng hơn trước đây rất nhiều. Cụ thể, các bạn có thể tìm đọc bài viết:

Dynamic array: https://blog.hocexcel.online/microsoft-excel-2018-dynamic-array.html

Vậy Dynamic array xử lý nested array như thế nào? Nó được xử lý theo cách “đơn giản hóa”: hiển thị phần tử đầu tiên của mảng con và bỏ mảng lồng. Tất nhiên, còn các quy tắc khác mà Microsoft có và không chia sẻ, nhưng nó không thuộc phạm vi bài viết này.

Ví dụ, trong trường hợp trên, Excel đã lấy ra 3 phần tử 1-4-7 tương ứng 3 phần tử đầu tiên của mảng {1;2;3};{4;5;6};{7;8;9} sau khi xử lý hàm INDEX:

Và nếu chúng ta lồng ra bên ngoài 1 hàm SUM, ta sẽ có kết quả là 12.

Vậy công thức mảng cũ xử lý ra sao?

Cách công thức mảng cũ (CSE) xử lý nested array

Nếu bạn nghĩ công thức mảng trong các phiên bản cũ cũng xử lý giống như vậy, thì không phải đâu!

Và tại sao công thức mảng cũ lại gọi là CSE nhỉ? Vì khi nhập công thức mảng cũ, ta cần ấn tổ hợp phím Ctrl+Shift+Enter thay vì Enter đó.

Các bạn có thể thấy trong hình, với công thức mảng đặt tại ô F1, kết quả trả là 6, chứ không phải 12.

Và nếu chúng ta chọn 2 ô sau đó sử dụng CSE, kết quả lại là 15.

Với 3 ô và CSE, kết quả lần lượt là 6 – 15 -24. Tại sao vậy?

Câu trả lời rất đơn giản: Hãy nhìn lại mảng lồng {{1;2;3};{4;5;6};{7;8;9}}. Nếu ta tách thành 3 mảng con, thì mỗi ô từ F1 đến F3 sẽ tương ứng với một lần SUM cho mảng con trong đó.

Và đó là sự khác biệt giữa cách xử lý nested array của Dynamic array và CSE.

Cũng trong bài viết này, mình có một vài note nho nhỏ: -Hàm INDEX sẽ trả về nested array nếu đối số row_number hoặc column_number là dạng mảng, dù chỉ tham chiếu tới 1 phần tử. VD: =INDEX({1;2;3;4},{1;2}) sẽ trả về {{1};{2}} và được giản lược thành {1;2} nếu sử dụng dynamic array.

-Hàm LOOKUP không trả về nested array. VD: LOOKUP({1;2},{1;2;3;4},{1;2;3;4}) sẽ trả về {1;2} kể cả CSE.

-Hàm VLOOKUP có vẻ trả về nested array hoặc không tùy vào số lượng ô được áp dụng CSE. Lý thuyết này SprinGo đang nghiên cứu thêm.

-Hàm ROW trong phiên bản Office 2013 (và có thể 1 vài phiên bản khác) trả về nested array. Trong phiên bản Office 365 không trả về nested array dù dùng CSE hay dynamic array.

Ví dụ

Cho bảng sau, với dữ liệu từ ô C3 tới F5:

Áp dụng lý thuyết trên, ta có thể tính running total cho từng dòng trong bảng chỉ với duy nhất 1 công thức:

=TRANSPOSE(MMULT(
     TRANSPOSE(TRANSPOSE(INDEX(C3:F5,{1;2;3},))*
        (ROW(INDIRECT("1:"&COUNTA(INDEX(C3:F5,{1;2;3},))))<=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(INDEX(C3:F5,{1;2;3},))))))),
         ROW(INDIRECT("1:"&COUNTA(INDEX(C3:F5,{1;2;3},))))^0))

Cách tính: Chọn một vùng cùng kích cỡ bảng (4×3) để hiển thị được hết running total (VD: H7:K9). Đặt công thức trên vào và ấn CSE.

Lưu ý: công thức trên sẽ không trả về đúng giá trị nếu sử dụng Dynamic array. Ngoài ra tại phiên bản Office 2013 có thể sẽ không hoạt động vì cách viết hàm ROW tại phiên bản đó khác. Các bạn có thể thử lại nhé.

Đọc thêm 1 số bài viết khác: Mảng động trong Excel: https://blog.hocexcel.online/ham-mang-dong-trong-excel-dynamic-array-formulas-phan-1-ham-co-kha-nang-mo-rong-vung-du-lieu.html

Công thức mảng cũ trong Excel: https://blog.hocexcel.online/cong-thuc-mang-trong-excel.html