Hàm mảng mới trong Excel 365: Hàm Unique, Sort, SortBy, RandArray, Sequence, Filter (6 người xem)

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
Dưới đây xin giới thiệu lần lượt các hàm trên, nội dung do tôi nghiên cứu trên mạng (các tài liệu tiếng Anh), thực hành và viết lại

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

Thí dụ mẫu cho hàm UNIQUE
Thí dụ 1
Sự khác nhau khi dùng tham số thứ 3 là True hay False (1 hay 0), mặc định False.

Distinct list
Công thức ô H2 là
H2 =UNIQUE(B2:B13)



Với công thức bỏ qua tham số 2 và 3, sẽ mặc định tham số 2 là cột (kết quả thành 1 cột) và tham số 3 lấy hết danh sách duy nhất tất cả giá trị có xuất hiện.

Nhận xét
Chỉ cần gõ công thức vào 1 ô trên cùng và enter, kết quả xuất hiện ở nhiều ô của cả cột. Không cần Ctrl Shift Enter
Vùng kết quả có viền xanh nhạt chung quanh
Ô đầu tiên chứa công thức để chỉnh sửa, các ô còn lại nhìn thấy công thức trên thanh công thức nhưng không cho sửa xoá
Chỉ cần xoá ô đầu tiên, không cần xoá cả vùng như công thức mảng phiên bản cũ.

Unique list (exactly once)
Công thức ô G2 là:
G2 =UNIQUE(B2:B13,,1)


Với công thức bỏ qua tham số 2 và gán True cho tham số 3, hàm trả về 1 cột các giá trị chỉ xuất hiện duy nhất 1 lần. Các giá trị xuất hiện nhiều lần bị bỏ qua

Thí dụ 2 – UNIQUE với 2 hoặc nhiều cột
Hàm UNIQUE không giới hạn số lượng cột.

Lấy danh sách duy nhất từ 2 hoặc nhiều cột
Tham số thứ nhất bao nhiêu cột thì kết quả trả về bấy nhiêu cột.
Công thức gõ vào ô F2 là:

F2 =UNIQUE(A2:B13)

Kết quả trả về là 2 cột



Nối 2 cột
Dùng công thức nối chuỗi nối hẳn 2 mảng vơi nhau, không cần ctrl Shift enter
Công thức:
H2 =UNIQUE(A2:A13&” “&B2:B13)



Nhiều cột không đúng thứ tự cột
Đôi khi chúng ta cần lấy các cột cách xa nhau, hoặc thay đổi thứ tự cột kết quả. Trường hợp này ta dùng hàm CHOOSE để lấy và sắp xếp

• Trường hợp lấy 2 cột A và B nối với nhau (họ tên), và cột E (võ công), công thức G2 là

G2 =UNIQUE(CHOOSE({1,2},A2:A13&” “&B2:B13,E2:E13))

• Trường hợp giống như trên nhưng đổi cột võ công ra trước họ tên thì công thức là đổi 2 lên trước 1 trong hàm CHOOSE

G2 =UNIQUE(CHOOSE({2,1},A2:A13&” “&B2:B13,E2:E13))​

Hoặc đổi E lên trước​

G2 =UNIQUE(CHOOSE({1,2},E2:E13,A2:A13&” “&B2:B13))​

Thí dụ 3 – Dùng UNIQUE cho tham chiếu ngang nhiều cột
Mặc định hàm Unique trả về mảng giá trị theo dòng, nhưng tham số thứ 2 cho phép trả về mảng giá trị theo cột
Sử dụng tham số thứ 2 LÀ TRUE hoặc 1
Công thức tại ô B9 là
B9 =UNIQUE(B1:I1,1)

Kết quả sẽ dàn hàng ngang nhiều cột



Dùng TRANSPOSE chuyển dòng thành cột
Công thức ô K2 như sau, và không cần Ctrl Shift Enter
K2 =TRANSPOSE(UNIQUE(B1:I1,1))



Thí dụ 4 – kết hợp UNIQUE và SORT tạo validation list
Công thức tại G2 là
G2 =SORT(UNIQUE(A2:A13&” “&B2:B13))



Để dùng kết quả trên làm dữ liệu nguồn cho ô I2, đứng tại I2 mở hộp thoại Data validation
Trên hộp thoại chọn List, và Source ghi =$G$2#


Với ký tự #, Excel 365 hiểu là lấy mảng kết quả bắt đầu từ G2 chứ không phải chỉ 1 giá trị chứa trong ô G2
Kết quả validation tại ô I1 như sau

​

(Còn tiếp)

​

 

Hàm Unique (tiếp theo)
Thí dụ 5 – Công thức cho kết quả giống Pivot table theo dòng và cột
Công thức
Với bảng dữ liệu và kết quả mong muốn thống kê dạng ma trận dòng cột kiểu Pivot table đơn giản.

• Tiêu đề cột H1:I1 có công thức tại H1 như sau:

H2 =TRANSPOSE(UNIQUE(C2:C13))

• Tiêu đề dòng G2:G3 có công thức tại ô G2 như sau

G2 =UNIQUE(D213)

• Ma trận kết quả gồm 4 ô, có công thức tại ô H2:

H2=COUNTIFS(C2:C13,H1#,D213,G2#)
Với ý nghĩa H1# là mảng H1:H2, G2# là mảng G2:G3




Tự động bổ sung khi định dạng table
Nếu vùng dữ liệu định dạng table, thì khi bổ sung dữ liệu (thêm dòng 14), tất cả công thức mảng với hàm mảng của excel 365 sẽ tự cập nhật (thêm dòng “Thi lại”)

​

 

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

Thí dụ ứng dụng hàm Sort
Thí dụ 1 – SORT trả về 1 mảng giá trị dòng và cột
Trong thí dụ này, hàn Sort đơn giản sắp xếp các giá trị theo cột thứ nhất
Công thức ở ô F2 là
F2 =SORT(A2: D9)



Thí dụ 2 – Sắp thứ tự theo cột khác và giảm dần
Sắp xếp giảm theo cột Số lượng, Công thức tại ô F2 là
F2 =SORT(A2: D9,2,-1)



Sắp xếp giảm theo cột Thành tiền, Công thức tại ô F2 là
F2 =SORT(A2: D9,4,-1)



Thí dụ 3 – Kết hợp SORT và SEQUENCE chỉ trả về 1 số cột và dòng cần thiết
Thí dụ này lấy 5 giá trị “Thành tiền” lớn nhất của bảng dữ liệu và chỉ lấy 2 cột Mặt hàng và thành tiền
Công thức ô F2 là
F2 =INDEX(SORT(A2: D9,4,-1),SEQUENCE(5),{1,4})
Hàm Index dùng mảng 2 cột đã sắp xếp giảm thành tiền như thí dụ 1, dùng hàm SEQUENCE để lấy 5 dòng đầu, và lấy 2 cột 1 và 4 trong dấu {}
Khi chưa có hàm Sort và Sequence, phải dùng nhiều bước tín htoán bằng công thức hoặc phải sử dụng Pivot table.



Thí dụ 4 – Kết hợp FILTER và SORT
Thí dụ này dùng hàm FILTER lồng trong hàm Sort để lấy các dòng dữ liệu có thành tiền lớn hơn 3,500,000 đồng và sắp thứ tự nhỏ dần
Công thức ô F2 là
F2 =SORT(FILTER(A2: D9,D2: D9>3500000),4,-1)



Thí dụ 5 – SORT theo nhiều cột
Thí dụ này sắp xếp dữ liệu theo cột B (nhóm) tăng dần sau đó là cột E (thành tiền) giảm dần
Công thức tại ô P2 là
P2 =SORT(A2:E14,{2,5},{1,-1})
Tham số thứ 3 và 4 là 2 mảng, mỗi mảng 2 phần tử. Mảng {2,5} thể hiện sắp xếp ưu tiên cột 2 rồi tới cột 5. Mảng {1,-1} thể hiện kiểu sắp xếp: sắp xếp cột 2 tăng dần rồi đến cột 5 giảm dần.



Thí dụ 6: Sắp xếp theo dòng
Tham số thứ 4 nếu là True (1) nghĩa là sắp xếp theo dòng, thí dụ sau đây sắp thứ tự mảng dữ liệu theo dòng 1 là dòng tiêu đề theo alphabet tăng dần.
Công thức A7 là:
A7 =SORT(A1:E5,,1,1)

​

 

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

Thí dụ ứng dụng SortBy
Thí dụ 1 – cột tiêu chí sắp xếp không cần có trong kết quả
Thí dụ này chỉ lấy cột mặt hàng và sắp xếp theo thàn htiền giảm dần
Công thức H2 là
H2 =SORTBY(A2:A14,E2:E14,-1)



Thí dụ 2 – công thức chứa SORTBY cho dữ liệu dạng table
Công thức H2 tham chiếu table lấy 2 cột mặt hàng và nhóm, sắp theo thành tiền giảm dần như sau:
H2 =SORTBY(Table2[[Mặt hàng]:[Nhóm]],Table2[Thành tiền],-1)



Thí dụ 3 – Dùng SORTBY sắp xếp nhiều tiêu chí
Thí dụ 1 và 2 ở trên chỉ sắp theo 1 tiêu chí là thành tiền, thí dụ 3 này sắp theo nhóm trước rồi mới sắp theo thành tiền
Công thức H2 là
H2 =SORTBY(A2:B14,B2:B14,1,E2:E14,-1)



Trường hợp sau đây có thể dùng hàm Sort thay cho SortBy, do cột dùng để sắp xếp có nằm trong kết quả, và là cột 2
Công thức G2:
G2 =SORTBY(CHOOSE({1,2},A2:A14,E2:E14),E2:E14,-1)

Công thức J2
J2 =SORT(CHOOSE({1,2},A2:A14,E2:E14),2,-1)



Thí dụ 4 – Sử dụng Choose để lấy cột bất kỳ và sắp xếp bất kỳ
Thí dụ này cho thấy chỉ lấy cột Mặt hàng, Nhóm, và Thành tiền, sắp xếp trước tiên theo Nhóm tăng dần và sau đó theo Thành tiền giảm dần
Công thức G2 là
G2 =SORTBY(CHOOSE({1,2,3},A2:A14,B2:B14,E2:E14),B2:B14,1,E2:E14,-1)

Lưu ý nếu muốn cột nhóm ra trước cột mặt hàng thì đảo thứ tự trong hàm Choose: hoặc đảo mảng thành {1,3,2), hoặc đảo trực tiếp B2:B14 lên trước A2:A14



Thí dụ 5 –Kết hợp FILTER và SORTBY
Thí dụ này liệt kê và sắp xếp 5 mặt hàng có thành tiền lớn hơn 1 giá trị nằm ở ô I1, hiện tại I1 = 3 triệu.
Công thức G2 là
G2 =FILTER(SORTBY(CHOOSE({1,2}, A2:A14,E2:E14), E2:E14,-1),SORTBY(E2:E14, E2:E14,-1),>I1)

Ta thấy phải sử dụng 2 lần SORTBY: 1 lần sắp xếp cho dữ liệu gốc và 1 lần cho điều kiện lớn hơn 3 triệu ở I1 (của hàm Filter)



Nếu không sắp xếp cho cột E là điều kiện Filter, kết quả sẽ sai như sau: Có cả giá trị nhỏ hơn 3 triệu



Thí dụ 6 – Chỉ lấy 1 giá trị sau khi dùng SORTBY
Với thí dụ bên trên, dùng công thức SORTBY kết hợp với CHOOSE ta lấy được Mặt hàng và thàn htiền giảm dần như hình sau



Giờ nếu chỉ muốn lấy mặt hàng có doanh thu lớn nhất, hoặc lớn thứ 2, thứ bất kỳ, thì kết hợp với Index như sau:
Công thức tại ô K2 dùng Index cho chính công thức bên trên

K2 =INDEX(SORTBY(CHOOSE({1,2},A2:A14,E2:E14),E2:E14,-1),J2,0)
Trong đó: J2 là tham số thứ 2 của Index, là thứ tự dòng muốn lấy (dòng 3) và 0 là tham số thứ 3 của Index, mang ý nghĩa lấy hết các cột.



Lấy mặt hàng có thành tiền lớn nhất, chỉ cần đổi J2 thành 1



Nếu lấy mặt hàng có thàn htiền nhỏ nhất, thay vì đếm tay mặt hàng là 13 mặt hàng, ta dùng hàm Count để đếm cột thành tiền, hoặc CountA để đếm mặt hàng.

 

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.

Thí dụ áp dụng RANDARRAY
Thí dụ 1 – Ứng dụng đơn giản
Một cột
Công thức ô B2 chỉ sử dụng tham số thứ nhất
B2 =RANDARRAY(5)



Một dòng
Công thức D2 chỉ sử dụng tham số thứ 2
D2 =RANDARRAY(,4)



Nhiều dòng, cột
Sử dụng cả 2 tham số 1 và 2
D2 =RANDARRAY(5,4)



Thí dụ 2 – Kết hợp RANDARRAY và SORTBY
Sắp thứ tự ngẫu nhiên cho 1 danh sách có sẵn.
Công thức C2 là
C2 =SORTBY(A2:A9,RANDARRAY(COUNTA(A2:A9)),1)



Bắt cặp thi đấu ngẫu nhiên trong 1 danh sách có sẵn
Công thức C2 là
C2 =INDEX(SORTBY(A2:A9,RANDARRAY(COUNTA(A2:A9)),1),SEQUENCE(2))
Trong đó Sequence(2) tạo ra 1 mảng {1,2} để Index lấy 2 dòng 1 và 2.



Thí dụ 3 – Sử dụng mọi tham số của RANDARRAY
Thí dụ sau đây tạo khối số ngẫu nhiên nguyên trong khoảng 10 và 100, có 5 dòng và 5 cột
Công thức E3
=RANDARRAY(5,5,10,100,1)



Nếu muốn số ngẫu nhiên tròn bao nhiêu thì nhân với bấy nhiêu (tròn 10.000 thì nhân với 10.000)

 

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.

Thí dụ ứng dụng hàm SEQUENCE
Thí dụ 1 – Sequence căn bản
Chỉ dùng 1 tham số
Công thức căn bản tại B2 đánh số thứ tự từ 1 đến 5
B2 =SEQUENCE(5)



Sử dụng hết 5 tham số
Công thức E5 tạo ra vùng kết quả 5 dòng 4 cột, bắt đầu từ 5 và tăng 5, dòng trước cột sau
E5 =SEQUENCE(5,4,5,5)



Điều khiển start và step:
Nếu số bắt đầu và bước ghi ở 2 ô trên bảng tính thì khi thay đổi 2 ô này, có thể thay đổi toàn bộ giá trị của kết quả. Công thức là
E5 =SEQUENCE(5,4,D1,D2)


Step âm

- Tạo ra dãy số giảm 5 con bắt đầu từ 8, bước giảm 1​

E2 =SEQUENCE(5,,8,-1)

- Vùng số 5 dòng, 4 cột, bắt đầu từ 70, bước giảm 5​

Cũng công thức như trên nhưng H2 = -5​

Thí dụ 2 – SEQUENCE cột trước dòng sau
Chỉ cần dùng Transpose mà không cần Ctrl Shift Enter
Công thức J2
J2 =TRANSPOSE(SEQUENCE(5,4,H1,H2))



Thí dụ 3 – Kết hợp SEQUENCE với các hàm khác
Tạo danh sách 12 tháng trong năm 2020 theo cột
Dùng hàm Date, tháng là Sequence(12), định dạng “mm/yyyy”
B2 =DATE(2020,SEQUNCE(12),1)



Tạo bảng chấm công theo hàng ngang
Cũng dùng hàm Date, năm và tháng lấy ở 2 ô điều khiển, ngày là 1 Sequence hàng ngang cộng với lấy ngày cuối của tháng để giớ hạn số cột (hàm EOMONTH)
Công thức D17
D17 =DATE(C16,C15,SEQUENCE(1,DAY(EOMONTH(DATE(C16,C15,1),0))))
Định dạng “dd”
Tuỳ theo tháng bảng chấm công tự co giãn 28, 29, 30, 31 cột tương ứng số ngày của tháng.



Thí dụ 4 – Kết hợp với INDEX
Do hàm Sequence có tham số bước tăng giảm, ta có thể dùng Index kết hợp với Sequence để lấy dữ liệu cách quãng theo bước như chỉ lấy dữ liệu dòng lẻ, hoặc lấy dữ liệu dòng chẵn.
Chỉ lấy dòng lẻ
Với dữ liệu như trong hình, mỗi người làm 2 công việc 1 chính 1 phụ thể hiện trên 2 dòng công việc chính ở dòng trên. Nếu dữ liệu đã sắp xếp theo tên, có thể chỉ lấy riêng các công việc chính ở dòng thứ nhất của mỗi người (có thể coi là các dòng lẻ)
Công thức F2:
F2 =INDEX(B2:C15,SEQUENCE(COUNTA(B2:B15)/2,1,1,2),SEQUENCE(1,2))
SEQUENCE thứ nhất lấy dãy số từ 1 đến nửa số phần tử, bắt đầu từ 1 và bước tăng là 2, sẽ tạo ra mảng các số lẻ {1,3,5,7,9,11,13}, dùng làm tham số dòng cho Index
SEQUENCE thứ hai tạo dãy số 1 dòng 2 cột mang giá trị {1;2}, dùng làm tham số cột cho Index
Kết quả là lấy các dòng lẻ và đủ 2 cột



Chỉ lấy dòng chẵn
Chỉ cần thay đổi SEQUENCE thứ nhất bắt đầu từ 2 (thay vì 1), sẽ được mảng các số chẵn {2,4,6,8,10,12,14}. Kết quả là Index chỉ lấy các dòng chẵn
F2 =INDEX(B2:C15,SEQUENCE(COUNTA(B2:B15)/2,1,2,2),SEQUENCE(1,2))



Thí dụ 5 – Tạo chuỗi số từ 1 đến 15 và sắp xếp ngẫu nhiên
Công thức C21:
C21 =SORTBY(SEQUENCE(14),RANDARRAY(15))

 

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

Thí dụ ứng dụng hàm FILTER
Thí dụ 1 – FILTER 1 điều kiện
Lọc dữ liệu với điều kiện thành tiền lớn hơn 3 triệu, với 3 triệu nằm ở ô L1
Công thức ô G2:
G2 =FILTER(A2:E14,E2:E14>L1)



Thí dụ 2 – Lỗi #CALC! và khắc phục
Nếu L1 = 30 triệu và không có mặt hàng nào có thành tiền đạt mức đó, công thức trả về lỗi #CALC!



Sử dụng tham số thứ 3 để khắc phục
G2 =FILTER(A2:E14,E2:E14>L1,”Bự quá”)



Nếu muốn thông báo cho tất cả cột thì tham số thứ 3 là 1 mảng các giá trị



Thí dụ 3 –FILTER với nhiều điều kiện.
Điều kiện And: Nhân 2 điều kiện với nhau
G11 =FILTER(A2:E14,(B2:B14=L11)*(E2:E14>L10),"Không có")
Nhóm mặt hàng là giải khát và thành tiền lớn hơn 500 ngàn



Điều kiện vừa And vừa Or: Các điều kiện Or cộng với nhau, vừa And vừa Or thì vừa cộng vừa nhân đúng nguyên tắc nhân trước cộng sau, 2 Or phải bỏ trong dấu ngoặc (a+b) rồi mới nhân
G11 =FILTER(A2:E14,(B2:B14=L11)*(C2:C14>L12)+(E2:E14>L10),"Không có")



Thí dụ 4 – Kết hợp FILTER với hàm khác
Kết hợp với Sort theo nhóm sau đó thành tiền giảm dần
G11=SORT(FILTER(A2:E14, (B2:B14=L11) * (C2:C14>L12) + (E2:E14>L10), "Không có"), {2,5},{1,-1})



Kết hợp với SORT, nhưng FILTER không lấy tất cả cột bằng hàm CHOOSE
G2=FILTER(CHOOSE({1,2,3},B2:B14,A2:A14,E2:E14),E2:E14>J1),3,-1)



Hàm FILTER tiếp theo
Thí dụ 5 – Dùng FILTER tạo validation list phụ thuộc
Trước đây muốn tạo validation cho 1 ô sau đó phụ thuộc vào việc chọn giá trị cho ô đó tạo validation cho 1 ô khác, phải đặt name, phải sắp xếp dữ liệu theo chuẩn cấu trúc và sắp xếp theo thứ tự đúng.
Bây giờ với hàm Sequence công việc đơn giản hơn nhiều.
Từ danh sách mặt hàng và nhóm tương ứng sắp xếp lộn xộn, yêu cầu là chọn nhóm ô D9 bằng validation list, sau đó với nhóm chọn của D9, validation chọn mặt hàng ở ô F9 phải tương ứng với nhóm chọn.
Bước 1:
Tạo danh sách nhóm duy nhất (dùng làm list cho validation ô D9)
D2=UNIQUE(A2:A15)




Bước 2
Tạo validation cho D9, list nguồn cho validation là =D2#



Bước 3
Tạo list mặt hàng phụ thuộc vào nhóm đã chọn ở D9
Công thức F2
F2 =FILTER(B2:B15,A2:A15=D9)



Khi thay D9 từ Tươi sống thành Giải khát bằng cách chọn Validation, các giá trị F2:Fn thay đổi theo


Bước 4
Tạo validation cho ô F9, nguồn của validation list là =F2#



Kết quả khi D9 thay đổi thì danh sách chon của F9 thay đổi tương ứng

 

Bổ sung tài liệu pdf

 

Bổ sung hàm Filter khi chỉ lấy 1 số cột và thứ tự cột khác dữ liệu gốc
Trong thí dụ 4, muốn dùng hàm Filter để lọc lấy 1 số cột và thứ tự cột lộn xộn không giống thứ tự cột của dữ liệu gốc, thì dùng hàm Choose.

G2=FILTER(CHOOSE({1,2,3},B2:B14,A2:A14,E2:E14),E2:E14>J1),3,-1)



Nếu lấy nhiều cột thì hàm choose sẽ dài ra khá nhiều, nên có 2 cách khác. Với file đính kèm sẽ có 3 cách:

Cách 1: Hàm Choose
Đó là cách bên trên:
J4 =FILTER(CHOOSE({1,2,3,4,5},Sales[Ngày],Sales[Nhân viên],Sales[Mặt hàng],Sales[Số lượng],Sales[Thành tiền]),Sales[Nhân viên]=K2)



Cách 2: Dùng hàm Index lấy 1 số cột bằng hàm Match

Dùng hàm Match để lấy mảng thứ tự cột có sẵn của bảng kết quả

Q4 =FILTER(INDEX(Sales,SEQUENCE(ROWS(Sales)),MATCH(Q17:U17,Sales[#Headers],0)),Sales[Nhân viên]=R16)



Cách 3: Dùng hàm Index lấy 1 số cột bằng mảng các số thứ tự

Q18 =FILTER(INDEX(Sales,SEQUENCE(ROWS(Sales)),{1,4,2,5,7}),Sales[Nhân viên]=R2)




Xem thêm trong file đính kèm.

 

Cám ơn tác giả với bài viết đầy tâm huyết. Bản thân mình sẽ học thật chăm chỉ.
Trân trọng.

 

Tôi thấy 1 số hàm đã update cho Excel 2021, chưa kiểm tra lại với Office 2016 (chắc chắn phải update thì may ra có )

 

Thí dụ 3

Bác chỉ giúp em, em sai ở chỗ nào mà kết quả không giống thí dụ 3

 

Bác chỉ giúp em, em sai ở chỗ nào mà kết quả không giống thí dụ 3
View attachment 281787

Đổi {1;4} thành {1,4} .

 

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

Thí dụ ứng dụng hàm FILTER
Thí dụ 1 – FILTER 1 điều kiện
Lọc dữ liệu với điều kiện thành tiền lớn hơn 3 triệu, với 3 triệu nằm ở ô L1
Công thức ô G2:
G2 =FILTER(A2:E14,E2:E14>L1)

View attachment 244603

Thí dụ 2 – Lỗi #CALC! và khắc phục
Nếu L1 = 30 triệu và không có mặt hàng nào có thành tiền đạt mức đó, công thức trả về lỗi #CALC!

View attachment 244604

Sử dụng tham số thứ 3 để khắc phục
G2 =FILTER(A2:E14,E2:E14>L1,”Bự quá”)

View attachment 244605

Nếu muốn thông báo cho tất cả cột thì tham số thứ 3 là 1 mảng các giá trị

View attachment 244606

Thí dụ 3 –FILTER với nhiều điều kiện.
Điều kiện And: Nhân 2 điều kiện với nhau
G11 =FILTER(A2:E14,(B2:B14=L11)*(E2:E14>L10),"Không có")
Nhóm mặt hàng là giải khát và thành tiền lớn hơn 500 ngàn

View attachment 244607

Điều kiện vừa And vừa Or: Các điều kiện Or cộng với nhau, vừa And vừa Or thì vừa cộng vừa nhân đúng nguyên tắc nhân trước cộng sau, 2 Or phải bỏ trong dấu ngoặc (a+b) rồi mới nhân
G11 =FILTER(A2:E14,(B2:B14=L11)*(C2:C14>L12)+(E2:E14>L10),"Không có")

View attachment 244608

Thí dụ 4 – Kết hợp FILTER với hàm khác
Kết hợp với Sort theo nhóm sau đó thành tiền giảm dần
G11=SORT(FILTER(A2:E14, (B2:B14=L11) * (C2:C14>L12) + (E2:E14>L10), "Không có"), {2,5},{1,-1})

View attachment 244609

Kết hợp với SORT, nhưng FILTER không lấy tất cả cột bằng hàm CHOOSE
G2=FILTER(CHOOSE({1,2,3},B2:B14,A2:A14,E2:E14),E2:E14>J1),3,-1)

View attachment 244610

Hàm FILTER tiếp theo
Thí dụ 5 – Dùng FILTER tạo validation list phụ thuộc
Trước đây muốn tạo validation cho 1 ô sau đó phụ thuộc vào việc chọn giá trị cho ô đó tạo validation cho 1 ô khác, phải đặt name, phải sắp xếp dữ liệu theo chuẩn cấu trúc và sắp xếp theo thứ tự đúng.
Bây giờ với hàm Sequence công việc đơn giản hơn nhiều.
Từ danh sách mặt hàng và nhóm tương ứng sắp xếp lộn xộn, yêu cầu là chọn nhóm ô D9 bằng validation list, sau đó với nhóm chọn của D9, validation chọn mặt hàng ở ô F9 phải tương ứng với nhóm chọn.
Bước 1:
Tạo danh sách nhóm duy nhất (dùng làm list cho validation ô D9)
D2=UNIQUE(A2:A15)

View attachment 244614


Bước 2
Tạo validation cho D9, list nguồn cho validation là =D2#

View attachment 244615

Bước 3
Tạo list mặt hàng phụ thuộc vào nhóm đã chọn ở D9
Công thức F2
F2 =FILTER(B2:B15,A2:A15=D9)

View attachment 244617

Khi thay D9 từ Tươi sống thành Giải khát bằng cách chọn Validation, các giá trị F2:Fn thay đổi theo

View attachment 244616
Bước 4
Tạo validation cho ô F9, nguồn của validation list là =F2#

View attachment 244618

Kết quả khi D9 thay đổi thì danh sách chon của F9 thay đổi tương ứng

View attachment 244619

Xin vui lòng cho hỏi, tại sao List Validation không sử dụng dữ liệu từ Name lấy dữ liệu từ kết quả của các hàm FILTER, UNIQUE, v.v...? Phải chăng MS Excel bỏ quên việc cập nhật này? Hoặc tôi chưa biết cách sử dụng?

 

Xin vui lòng cho hỏi, tại sao List Validation không sử dụng dữ liệu từ Name lấy dữ liệu từ kết quả của các hàm FILTER, UNIQUE, v.v...? Phải chăng MS Excel bỏ quên việc cập nhật này? Hoặc tôi chưa biết cách sử dụng?

Theo mình hiểu thì vì mấy hàng đó là hàm trả về mảng nên nó không hoạt động trong Validation

 

Theo mình hiểu thì vì mấy hàng đó là hàm trả về mảng nên nó không hoạt động trong Validation

Thế một bảng dữ liệu hoặc hàm OFFSET không phải là hàm mảng sao?

 

Thế một bảng dữ liệu hoặc hàm OFFSET không phải là hàm mảng sao?

Đúng rồi, ví như Offset là hàm trả về Range cụ thể sheet

 

Xin vui lòng cho hỏi, tại sao List Validation không sử dụng dữ liệu từ Name lấy dữ liệu từ kết quả của các hàm FILTER, UNIQUE, v.v...? Phải chăng MS Excel bỏ quên việc cập nhật này? Hoặc tôi chưa biết cách sử dụng?

thường thì em dùng như cách này