Thủ Thuật Excel

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

Posted on by Jina
26
Th9

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, Học Excel Online 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:

=INDEX({1,4,7;2,5,8;3,6,9},0,{1;2;3})

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}.

Tìm hiểu thêm:  Hàm TRUNC trong Excel: Cú pháp và cách sử dụng từ A-Z Việc xử lý và làm tròn số là một phần quan trọng khi bạn làm việc với bảng tính Excel. Trong bài viết này, chúng ta sẽ khám phá một trong những hàm quan trọng nhất để thực hiện công việc này - đó là hàm TRUNC. Hàm Trunc trong Excel không chỉ giúp làm tròn số mà còn có những ứng dụng linh hoạt khác trong việc xử lý dữ liệu. Hãy cùng tìm hiểu về cú pháp và cách sử dụng của hàm này để tận dụng tối đa khả năng mạnh mẽ của Excel trong việc làm tròn và xử lý số liệu.1. Hàm Trunc là gì?Hàm Trunc trong Excel được dùng với mục đích làm tròn một số thành số nguyên bằng cách cắt bỏ các chữ số ở phần thập phân.Ứng dụng của hàm Trunc:- Làm tròn chữ thập phân đơn giản nhất.- Giúp bạn làm việc với Excel chuyên nghiệp hơn. - Hàm Trunc có thể kết hợp với hàm khác trong Excel để hỗ trợ cho công việc. >>> Xem thêm: Hướng dẫn nhanh 3 cách chỉnh kích thước ô trong excel2. Cách sử dụng của hàm Trunc trong ExcelCú pháp hàm:Cú pháp hàm TRUNC:  =TRUNC(number, [num_digits])Trong đó:- Number: Giá trị cần được làm tròn- Num_digits: Chữ số ở phần thập phân còn lại sau khi được làm tròn. Đây là tham số không bắt buộc và mặc định là 0.Lưu ý:- Hàm TRUNC sẽ trả về giá trị lỗi #VALUE nếu Number không phải là số. - Hàm sẽ không được làm tròn nếu Number là số nguyên.- Giá trị được làm tròn khi có chứa các chữ số ở phần thập phân. 3. Số thập phân là gì?Số thập phân bao gồm phần nguyên và phần thập phân. Chúng được phân cách với nhau bằng dấu phẩy. Trong đó:- Phần Nguyên: Gồm số nguyên dương, số nguyên âm và số 0. Những số này đứng trước dấu phẩy. - Phần thập phân: Là các số tự nhiên sau dấu phẩy. >>> Xem thêm: Cách sử dụng conditional formatting trong excel để tô màu, đánh dấu ô4. Cách sử dụng hàm TRUNC trong ExcelVí dụ minh họa: Làm tròn các số thập phân bằng hàm TRUNC. - Bước 1: Tại ô muốn hiển thị kết quả, bạn nhập công thức =TRUNC(B4).Cách sử dụng hàm TRUNC trong Excel - Hình 1- Bước 2: Nhấn Enter để hiển thị kết quả. Sau đó dùng chuột kéo xuống hết các ô để hiển thị kết quả còn lại của ô tính. Cách sử dụng hàm TRUNC trong Excel - Hình 2>>> Xem thêm: 4 cách tách họ và tên trong excel nhanh và đơn giản nhất5. Tổng kếtThông qua nội dung bài viết trên, Unica đã cùng bạn tìm hiểu về hàm TRUNC trong Excel. Ngoài những kiến thức trên, nếu bạn muốn học Excel Online hiệu quả thì có thể tham khảo các khóa học Excel cơ bản trên Unica.Cảm ơn và chúc các bạn thành công! 18/01/2023 687 Lượt xem

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:

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?

Tìm hiểu thêm:  Cách sử dụng hàm VLOOKUP kết hợp với hàm IF kèm ví dụ

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 Học Excel Online đ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.

Ứng dụng xử lý nested array để tính runnning total trong 1 công thức duy nhất

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é.

Tìm hiểu thêm:  Cách hiển thị chỉ số trên, chỉ số dưới trong Excel Khóa học mới xuất bản

Đọc thêm 1 số bài viết khác:
Mảng động trong Excel:

Công thức mảng cũ trong Excel: