Ebook: Hàm mảng mới trong Excel 365 2020-10-15

Microsoft 365 (tên mới của Office 365) có một số hàm mới dùng cho Excel 365 chuyên trị mảng. Tương tự hàm Query của Google Sheets, các hàm mảng mới này chỉ cần gõ công thức vào 1 ô và ta sẽ có kết quả là một mảng. Mảng này có thể là mảng 1 dòng nhiều cột, 1 cột nhiều dòng, thậm chí một ma trận dòng cột các giá trị.
Các hàm mảng đó là:
- Hàm Unique
- Hàm Sort
- Hàm SortBy
- Hàm RandArray
- Hàm Sequence
- Hàm Filter

I. Hàm UNIQUE
Hàm Unique dùng để lấy danh sách duy nhất từ 1 danh sách nguồn có dữ liệu trùng. Khác với những hàm và công thức trước đây, hàm Unique trả về 1 mảng các giá trị mà không cần Ctrl Shift Enter.

Cú pháp
Hàm Unique có 3 tham số trong đó tham số thứ nhất là bắt buộc và 2 tham số sau không bắt buộc phải điền

=UNIQUE(array, [by_col], [exactly_once])

Trong đó:

• array: vùng dữ liệu hoặc mảng cần lấy danh sách duy nhất.
• [by_col]: Tuỳ chọn dạng True/ False (mặc định False). False (hoặc 0) lấy danh sách duy nhất và trả về 1 cột, True (hoặc 1) lấy danh sách duy nhất và trả về thành 1 dòng.
• [exactly_once]: Tuỳ chọn dạng True/ False (mặc định False). Tuỳ chọn này tuỳ theo cách hiểu thế nào là duy nhất. Xem thí dụ 1.
• Nếu duy nhất mang nghĩa “chỉ lấy những giá trị xuất hiện duy nhất 1 lần”, thì sử dụng True hoặc 1. Tiếng Anh là Unique list
• Nếu duy nhất mang nghĩa “lấy duy nhất tất cả giá trị có xuất hiện” thì sử dụng False hoặc 0. Mỗi giá trị xuất hiện 1 lần hay bao nhiêu lần cũng lấy 1 trong danh sách duy nhất kết quả. Tiếng Anh là Distinct list

II. Hàm SORT
Cú pháp
Hàm Sort có 4 tham số trong đó tham số thứ nhất bắt buộc điền, 3 tham số còn lại không bắt buộc.

=SORT(array, [sort_index], [sort_order], [by_col])

• array: Vùng dữ liệu cần sắp xếp.
• [sort_index]: Số thứ tự cột (hoặc dòng) dùng để sắp xếp theo. Thí dụ muốn sắp xếp theo cột thứ hai thì tham số này điền 2. Nếu không điền sẽ mặc định là 1. Tham số này có thể điền nhiều số nếu muốn sắp xếp theo nhiều cột.
• [sort_order]: 1 là Sắp xếp tăng dần, -1 là sắp xếp giảm dần. Nếu không điền thì mặc định là 1
• [by_col]: TRUE hoặc 1 là Sắp thứ tự theo cột, FALSE hoặc 0 là sắp xếp theo dòng. Nếu không điền thì mặc định là 0

III. Hàm SORTBY
Hàm SortBy là hàm mảng dùng để sắp xếp dữ liệu gốc theo 1 hoặc nhiều cột. Sự khác biệt đối với Sort là SortBy có thể sắp xếp theo cột không nằm trong kết quả, còn Sort bắt buộc cột dùng làm tiêu chí sắp xếp phải có trong các cột kết quả
Cú pháp
Hàm SortBy có 2 tham chiếu bắt buộc và 1 hoặc nhiều tham chiếu không bắt buộc
=SORTBY(array, By_array1, [sort_order1], [By_array2], [sort_order2] ,...)

• array: Vùng dữ liệu cần sắp xếp lại.
• By_array1: Cột tiêu chí sắp xếp.
• [sort_order1]: Cách sắp xếp cho ByArray1: Là 1 sẽ sắp xếp tăng dần, là -1 sẽ sắp xếp giảm dần, mặc định 1
• [By_array2…]: Cột tiêu chí sắp xếp 2.
• [sort_order2]: Cách sắp xếp cho ByArray2: Là 1 sẽ sắp xếp tăng dần, là -1 sẽ sắp xếp giảm dần, mặc định 1.

Nếu sắp xếp theo tiêu chí thứ 3 hoặc hơn nữa, có thể thêm vào như tham số Byarray2

IV. Hàm RANDARRAY
Hàm RandArray giúp cho việc chỉ cần gõ công thức tại 1 ô mà kết quả cho những giá trị ngẫu nhiên ở 1 khối ô xác định, khác với hàm Rand trước đây chỉ cho giá trị đơn lẻ tại chính ô chứa công thức.

Ngoài ra RandArray còn có thể tạo dãy số ngẫu nhiên trong khoảng giá trị cho trước giống như hàm RandBetween (với số nguyên)

Cú pháp
Hàm RandArray có 5 tham số và không tham số nào là bắt buộc.

=RANDARRAY([Rows], [Columns], [Min], [Max], [Integer])

• [Rows]: Số dòng của kết quả, nếu bỏ qua thì mặc định là 1.
• [Columns]: Số cột của kết quả, nếu bỏ qua thì mặc định là 1.
• [Min]: Số nhỏ nhất của kết quả, mặc định 0.
• [Max]: Số lớn nhất của kết quả, mặc định 0.999999999999999 (số lớn nhất nhỏ hơn 1)
• [Integer]: Kiểu giá trị số ngẫu nhiên: TRUE (1) cho số nguyên, FALSE (0) cho số thập phân với 15 ký số sau dấu thập phân.

Theo cú pháp trên, nếu không sử dụng tham số nào ta sẽ có 1 giá trị đơn nhỏ hơn 1 giống như hàm Rand.
Hàm RandArray là 1 hàm volatile nên sẽ bị tính lại bất kỳ khi nào có sự thay đổi trên bảng tính.


V. Hàm SEQUENCE
Hàm Sequence tạo ra một chuỗi số theo thứ tự tăng hoặc giảm cho dòng, cột hoặc cả khối
Cú pháp
Hàm Sequence có 1 tham số bắt buộc và 3 tham số không bắt buộc.
=SEQUENCE(Rows, [Columns], [Start], [Step])

• Rows: Số dòng tạo thứ tự
• [Columns]: Số cột muốn tạo thứ tự, mặc định 1.
• [Start]: Số bắt đầu, mặc định 1.
• [Step]: Bước tăng/ giảm, mặc định 1. Nếu giảm ghi số âm

Ghi chú: Excel tạo chuỗi số thứ tự cho 1 khối ô với nguyên tắc ngang trước dọc sau.

VI. Hàm FILTER
Lọc dữ liệu là công việc thường xuyên của mọi người dùng Excel. Lọc nhiều tiêu chí ở nhiều cột bằng AutoFilter chỉ có thể là And các điều kiện, không có Or
Lọc có nhiều điều kiện vừa And vừa Or là phải dùng Advanced filter và dùng copy to another location. Khi thay đổi điều kiện lọc phải làm lại động tác Advanced filter một lần nữa.
Với hàm mảng Filter của Excel 365, lọc dữ liệu hoàn toàn dễ dàng và khắc phục mọi nhược điểm của Autofilter, Advanced filter.
Cú pháp
Hàm Filter có 2 tham số bắt buộc và 1 tham số tuỳ chọn
=FILTER(array, include, [if_empty])

• array: vùng dữ liệu cần lọc.
• include: Các điều kiện lọc có giá trị đúng/ sai (True/ False)
• [if_empty]: Giá trị hiển thị nếu không có dữ liệu thoả điều kiện lọc

Tải file đính kèm (ở đầu trang, góc bên phải) để xem các ví dụ ứng dụng của các hàm này