Hàm mảng động là thay đổi lớn nhất đối với công thức Excel trong nhiều năm, có lẽ là thay đổi lớn nhất từ trước đến nay.
FILTER: Lọc dữ liệu theo điều kiện
RANDARRAY: Tạo và trả về mảng số ngẫu nhiên
SEQUENCE: Tạo danh sách các số liên tiếp trong một mảng
SORT: Sắp xếp các giá trị trong một phạm vi hoặc mảng.
SORTBY: Sắp xếp nội dung mảng dựa trên các giá trị tương ứng
UNIQUE: Trả về các giá trị duy nhất trong một danh sách
1. Hàm SORT
Hàm Excel SORT là một công thức tra cứu và tham chiếu sắp xếp các giá trị trong một phạm vi hoặc mảng.
Cấu trúc hàm
=SORT(array,[sort_index],[sort_order],[by_col])
Trong đó:
array: là mảng dữ liệu cần sắp xếp (có thể gồm nhiều dòng, nhiều cột nhưng phải liên tục)
sort_index: tiêu chuẩn sắp xếp theo cột hay dòng thứ mấy trong mảng dữ liệu (nhập số cụ thể, không bắt buộc). Mặc định là cột/dòng đầu tiên
sort_order: thứ tự sắp xếp theo tăng dần hay giảm dần. Không bắt buộc, mặc định là tăng dần.
by_col: sắp xếp theo cột hay theo dòng. Không bắt buộc, mặc định là theo cột
Ưu thế của hàm SORT
Thay thế công cụ Sort của tab Data trong một số trường hợp, giúp loại bỏ các thao tác thủ công
Chức năng Dynamic Arrays (Mảng động) cho phép một công thức duy nhất tạo ra kết quả mở rộng sang các ô khác
Linh hoạt theo các yêu cầu sắp xếp dữ liệu
Sắp xếp nhiều cột
Dữ liệu cập nhật tự động
2. Hàm SORTBY
Hàm này cho phép bạn sắp xếp nội dung của dải hoặc mảng dựa trên các giá trị có trên dải ô/mảng.
Cấu trúc hàm
= SORTBY (Array, by_array1, [sort_order1], [by_array2, sort_order2],…)
Trong đó:
array: Mảng hoặc dải ô cần sắp xếp;
by_array1: Mảng hoặc dải ô cần sắp xếp theo;
[sort_order1]: Thứ tự dùng để sắp xếp. 1 để tăng dần, -1 cho giảm dần. Mặc định là tăng dần;
[by_array2]: Mảng hoặc dải ô cần sắp xếp theo;
[sort_order2]: Thứ tự dùng để sắp xếp. 1 để tăng dần, -1 cho giảm dần. Mặc định là tăng dần.
Ứng dụng của hàm SORTBY:
Sắp xếp danh sách người theo độ tuổi, theo thứ tự tăng hoặc giảm dần…
Xáo trộn dữ liệu một cách ngẫu nhiên.
Tự động mở rộng khi được liên kết với một bảng
Sử dụng với nhiều cột
Trả về các cột theo thứ tự bất kỳ
Lời khuyên
Nếu bạn cần sắp xếp một dải ô hoặc mảng dựa trên một dải ô hoặc mảng khác, bạn nên sử dụng SORTBY. Hàm SORTBY có thể xử lý các thay đổi cột vì nó tham chiếu đến một dải ô chứ không phải l số chỉ mục như hàm SORT.
Nếu danh sách được sắp xếp dựa trên các giá trị riêng, thì hàm SORT phù hợp hơn.
3. Hàm FILTER
Hàm FILTER hiện khả dụng trên phiên bản Office 365, cùng #UniTrain trải nghiệm tính năng tuyệt vời của nó nhé!
Hàm FILTER của Excel lọc một loạt dữ liệu dựa trên tiêu chí được cung cấp và trích xuất các bản ghi phù hợp (ô khác, sheet khác, workbook khác).
Cấu trúc hàm
=FILTER (array, include, [if_empty])
Trong đó:
array: vùng dữ liệu cần lọc (không bắt buộc phải có tiêu đề)
include: điều kiện cần lọc là gì, nằm trong cột nào (xác định đồng thời cả 2 yếu tố này)
if_empty: Nếu không có kết quả thì trả về giá trị gì? (không bắt buộc nhập)
Chú ý:
Hàm có thể hoạt động với cả mảng dọc và mảng ngang.
Hàm trả về kết quả là dạng mảng, dựa theo số cột của array và số dòng kết quả thu được
Không phụ thuộc vào tiêu đề của các trường dữ liệu.
Kết quả trả về không kèm theo định dạng của dữ liệu gốc
Không cần sử dụng dạng công thức mảng
Đối số bao gồm phải có thứ nguyên tương thích với đối số mảng , nếu không bộ lọc sẽ trả về lỗi #VALUE!
Nếu FILTER được sử dụng giữa các sổ làm việc, cả hai sổ làm việc phải được mở, nếu không FILTER sẽ trả về lỗi #REF!
4. Hàm RANDARRAY
Bạn có thể chỉ định số lượng hàng và cột cần điền, giá trị tối thiểu và tối đa và xem xét khả năng trả về toàn bộ số hoặc giá trị thập phân.
Cấu trúc hàm
=RANDARRAY([rows],[columns],[min],[Max],[whole_number])
Trong đó:
[rows]: Số hàng được trả về;
[columns]: Số lượng cột được trả về;
[min]: Số lượng tối thiểu bạn muốn được trả về;
[Max]: Số lượng tối đa bạn muốn được trả về;
[whole_number]: Trả về một số nguyên hoặc một giá trị thập phân.
Ứng dụng của hàm Randarray:
Tạo một dãy số ngẫu nhiên hoặc danh sách số ngẫu nhiên trong khoảng từ 0 -1
Tạo một mảng số nguyên ngẫu nhiên
Trả về một mảng số ngẫu nhiên có trong mảng chỉ định
Một số lưu ý:
Tham đối dạng số tối thiểu phải nhỏ hơn số tối đa, nếu không RANDARRAY sẽ trả về lỗi.
Kích thước hoặc mảng được xác định bởi các đối số hàng và cột được cung cấp.
Các giá trị được tạo bởi RANDARRAY có thể là giá trị thập phân hoặc số nguyên.
5. Hàm SEQUENCE
Hàm này cho phép bạn tạo một danh sách các số liên tiếp trong một mảng.
Cấu trúc hàm
=SEQUENCE(row, [column], [start], [step])
Trong đó:
row: số lượng hàng đánh số.
column: số lượng cột đánh số, mặc định là 1.
start: số đầu tiên trong trình tự.
step: số lượng cần tăng cho mỗi giá trị tiếp theo trong mảng.
Ưu thế của hàm SEQUENCE:
Sử dụng SEQUENCE kết hợp TEXT, DATE, YEAR, TODAY tạo một danh sách động gồm các tháng, với cơ sở năm hiện tại.
Hàm SEQUENCE có thể thay thế Autofill, dùng để đánh số thứ tự.
Có thể sử dụng hàm để lập bảng cửu chương.
6. Hàm UNIQUE
UNIQUE là một hàm dùng để trích xuất danh sách các giá trị riêng biệt hoặc danh sách các giá trị chỉ xuất hiện một lần, tức là các giá trị duy nhất. Hàm có thể trả về một danh sách duy nhất hoặc riêng biệt từ một cột hoặc từ nhiều cột.
Giá trị trả về có thể là văn bản, số, ngày tháng, thời gian…
Cấu trúc hàm
= UNIQUE (Array, [by_col], [exactly_once])
Trong đó:
array: mảng/vùng dữ liệu cần lọc
by_col: phương thức so sánh trùng theo cột (TRUE) hay theo hàng (FALSE). Mặc định là FALSE.
exactly_once: Đối số exactly_once là một giá trị lô-gic sẽ trả về hàng hoặc cột xảy ra chính xác một lần trong phạm vi hoặc mảng. Đây là khái niệm cơ sở dữ liệu duy nhất.
Ưu thế của hàm UNIQUE:
Tính ứng dụng rất cao khi kết hợp với Data Validation/List để tạo danh sách chọn giá trị không trùng.
Hàm UNIQUE lấy ra các giá trị xuất hiện chỉ một lần trong danh sách hoặc dải ô.
Ngoài ra, hàm này có thể kết hợp thêm với hàm SORT để vừa lọc giá trị không trùng, vừa sắp xếp danh sách các giá trị này theo ý muốn.
Có thể áp dụng trong các báo cáo tổng hợp, báo cáo chi tiết rất hiệu quả.
