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.

Hàm mảng động cho phép bạn dễ dàng làm việc với nhiều giá trị cùng lúc trong một công thức, có thể tự mở rộng/resize mảng kết quả. Mảng động sẽ giải quyết một số vấn đề thực sự khó trong Excel và về cơ bản thay đổi cách thiết kế và xây dựng trang tính

Tuy nhiên, hàm mảng động chỉ có sẵn trong Excel 365. Excel 2016 và Excel 2019 không cung cấp hỗ trợ chức năng mới này.

▪ 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

121113910 1988621534606434 5490098407752083761 O

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.

121112808 1988621721273082 4407766840864119515 O

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!

121193823 1989300951205159 7684801416988036894 O

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.

121112346 1988621951273059 4728415806746571789 O

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.

121209822 1988621837939737 6726652063434844745 O

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

121087687 1988621767939744 1789713188667364899 O