6 chức năng đã thay đổi cách bạn sử dụng Microsoft Excel

Đăng bởi

18/10/2025

Vào 18/10/2025

Sự ra đời của mảng động (dynamic arrays) đã tạo ra thay đổi lớn nhất đối với cách chúng ta làm việc với các công thức Microsoft Excel trong nhiều năm, nếu không muốn nói là hàng thập kỷ. Chúng cho phép một công thức duy nhất có thể tự động “tràn” (spill) nhiều kết quả sang các ô liền kề và tự động thay đổi kích thước, thay thế cho các công thức mảng (array formulas) Ctrl-Shift-Enter (CSE) cũ kém linh hoạt hơn.

Mảng động đi vào thế giới Excel cũng làm phát sinh một loạt các hàm mới tận dụng hành vi tính toán mới này. Trong bài viết này, tôi sẽ liệt kê sáu trong số các hàm mảng động hữu ích nhất đã làm thay đổi cách chúng ta tương tác và sử dụng chương trình bảng tính phổ biến này.

Hãy sử dụng Excel cho web hoặc ứng dụng Excel dành cho máy tính để bàn của Microsoft 365 để có các tính năng Excel mới nhất, bao gồm các hàm mảng động mới nhất. Sáu hàm trong bài viết này có sẵn trong các phiên bản Excel được phát hành vào năm 2021 trở đi, Excel cho Microsoft 365, Excel cho web, và các ứng dụng Excel dành cho thiết bị di động và máy tính bảng.

Xem thêm: Hướng dẫn cho người mới bắt đầu về các hàm lồng nhau trong Excel

Mục lục

1. FILTER: Trích xuất động một tập hợp con của dữ liệu

Hàm FILTER trích xuất một mảng dữ liệu theo các tiêu chí cụ thể, và kết quả sẽ tự động “tràn” (spill) ra từ ô mà công thức được nhập. Trước khi hàm mảng động này được giới thiệu, bạn cần tạo một công thức lồng nhau phức tạp hoặc sử dụng các công cụ tốn thời gian, như Bộ lọc Nâng cao (Advanced Filter) hoặc Power Query, để đạt được kết quả tương tự. Tuy nhiên, hàm mảng động này làm cho mọi việc trở nên dễ dàng hơn nhiều.

Cú pháp như sau: =FILTER(a,b,c)

Trong đó:

a (bắt buộc) là mảng cần lọc,
b (bắt buộc) là đối số Boolean (một đối số trả về TRUE hoặc FALSE), và
c (tùy chọn) là giá trị trả về nếu bộ lọc không tìm thấy kết quả nào.

Trong ví dụ này, hàm FILTER được sử dụng để trả về tên và tuổi của bất kỳ ai đạt điểm trên 70:

=FILTER(T_Results[[Name]:[Age]],T_Results[Score]>70)

Trong đó, T_Results[[Name]:[Age]] yêu cầu Excel trả về dữ liệu từ các cột Tên (Name) và Tuổi (Age), và T_Results[Score]>70 lọc mảng này để chỉ bao gồm những người đạt điểm trên 70, dựa trên cột Điểm (Score).

Hơn nữa, nếu bạn thay đổi một trong các điểm thấp hơn trong dữ liệu nguồn thành, ví dụ, 75, hàm FILTER sẽ nhận diện giá trị mới.

Điều quan trọng hơn là, vì công thức sử dụng tham chiếu bảng có cấu trúc (structured table references), hàm FILTER sẽ tự động cập nhật bất kỳ hàng dữ liệu mới nào.

Cùng với khả năng được sử dụng cùng với các hàm khác, hàm FILTER có thể được sử dụng để lọc dựa trên hai hoặc nhiều tiêu chí. Ở đây, kết quả trả về tên của những cá nhân trên 35 tuổi hoặc có điểm lớn hơn 65, vì ký hiệu dấu cộng (+) nối hai đối số Boolean để tạo ra logic OR:

=FILTER(T_Results[Name],(T_Results[Age]>35)+(T_Results[Score]>65))

Mặt khác, nhân hai đối số Boolean đại diện cho logic AND, chỉ trả về những người thỏa mãn cả hai tiêu chí:

=FILTER(T_Results[Name],(T_Results[Age]>35)*(T_Results[Score]>65))

Trong cả hai trường hợp, kết quả đã sẵn sàng để tự động thay đổi kích thước nếu dữ liệu nguồn thay đổi để có nhiều hoặc ít cá nhân thỏa mãn tiêu chí hơn.

2. RANDARRAY: Tự động tràn một mảng động gồm các số ngẫu nhiên

Mặc dù các hàm RAND (từ Excel 2003 trở đi) và RANDBETWEEN (từ 2007 trở đi) đã làm cho việc tạo số ngẫu nhiên dễ dàng hơn nhiều so với các phiên bản Excel cũ, cả hai đều chỉ trả về một kết quả duy nhất. Tuy nhiên, hàm mảng động RANDARRAY có thể trả về nhiều kết quả và kết hợp khả năng của các hàm tiền nhiệm vào một.

RAND, RANDBETWEEN, và RANDARRAY đều là hàm dễ bay hơi (volatile functions), nghĩa là chúng sẽ làm mới để tạo ra số ngẫu nhiên mới bất cứ khi nào bảng tính tính toán lại hoặc được mở lại. Hành vi này có thể làm chậm sổ làm việc, vì vậy hãy sử dụng các hàm này một cách tiết kiệm nếu có thể.

Đây là cú pháp RANDARRAY: =RANDARRAY(a,b,c,d,e)

Trong đó:

a là số hàng cần trả về,
b là số cột cần trả về,
c là số nhỏ nhất trong mảng ngẫu nhiên,
d là số lớn nhất trong mảng ngẫu nhiên, và
e là FALSE cho số thập phân (mặc định) hoặc TRUE cho số nguyên.

Tất cả các đối số đều là tùy chọn, mặc dù bạn nên lưu ý điều gì sẽ xảy ra nếu một số đối số bị bỏ qua. Nếu bạn bỏ qua đối số a và b, hàm sẽ trả về một giá trị thập phân duy nhất giữa 0 và 1. Bỏ qua đối số c và d sẽ mặc định là 0 cho số nhỏ nhất và 1 cho số lớn nhất. Bỏ qua đối số e sẽ trả về một số thập phân.

Ở đây, gõ:

=RANDARRAY(7,1,1,40,TRUE)

Sẽ tạo ra một mảng các số nguyên ngẫu nhiên trải dài bảy hàng, một cột, với giá trị nhỏ nhất là 1 và giá trị lớn nhất là 40.

Bạn có thể làm cho công thức linh hoạt hơn bằng cách tham chiếu ô thay vì mã hóa cứng các đối số:

=RANDARRAY(D1,D2,D3,D4,D5)

Trong đó ô D5 (đối số e) chứa một hộp kiểm (checkbox) trả về TRUE (số nguyên) khi được chọn hoặc FALSE (số thập phân) khi không được chọn.

Cũng có thể sử dụng RANDARRAY để xáo trộn ngẫu nhiên một mảng hiện có. Ở đây, tôi muốn xáo trộn các số nhóm trong bảng T_Groups. Để làm điều này, tôi sẽ gõ:

=SORTBY(T_Groups[Group],RANDARRAY(COUNTA(T_Groups[Group])))

Trong đó các hàm RANDARRAY và COUNTA trả về một mảng số ngẫu nhiên lý thuyết trải dài 11 hàng, từ đó quyết định thứ tự sắp xếp mà hàm SORTBY trả về.

Để biến mảng động kết quả thành một mảng cố định, hãy chọn tất cả các ô chứa số ngẫu nhiên, nhấn Ctrl+C để sao chép, sau đó nhấn Ctrl+Shift+V để dán chúng dưới dạng giá trị.

Ứng dụng rộng rãi của RANDARRAY làm cho nó trở thành một hàm đặc biệt mạnh mẽ. Ví dụ, nó có thể được sử dụng trong mô phỏng dữ liệu và lấy mẫu ngẫu nhiên hoặc để tạo văn bản hoặc ngày tháng ngẫu nhiên. Vì vậy, nếu đây là lần đầu tiên bạn tiếp xúc với RANDARRAY, hãy thử nghiệm và tận hưởng!

3. SEQUENCE: Tạo động một mảng số thứ tự

Hãy nhớ những ngày xưa cũ (không mấy tốt đẹp) khi bạn chỉ có thể tạo các danh sách số thứ tự tĩnh một cách thủ công bằng cách sử dụng tay nắm điền (fill handle) hoặc các hàm ROW/COLUMN? Giờ đây, bạn không cần phải làm vậy nữa, bởi vì hàm SEQUENCE vô cùng đơn giản và hữu ích tạo ra một danh sách động các giá trị theo một trình tự, với hình dạng, kích thước và bước nhảy được xác định.

Cú pháp như sau: =SEQUENCE(a,b,c,d)

Trong đó:

a (bắt buộc) là số hàng mà trình tự sẽ trải dài (theo chiều dọc),
b (tùy chọn) là số cột mà nó sẽ trải dài (theo chiều ngang),
c (tùy chọn) là số bắt đầu trong trình tự, và
d (tùy chọn) là bước nhảy giữa mỗi giá trị trong trình tự.

Nếu bạn bỏ qua bất kỳ đối số tùy chọn nào, chúng sẽ mặc định là một.

Trong ví dụ này, gõ:

 =SEQUENCE(10,1,5,5)

Vào ô A2 sẽ tạo ra một danh sách dài 10 hàng, rộng một cột, bắt đầu bằng 5, và tăng thêm 5 mỗi lần.

Việc tham chiếu các ô trong công thức làm cho nó linh hoạt hơn, vì bạn có thể nhanh chóng thay đổi các tham số mà không cần chỉnh sửa công thức:

 =SEQUENCE(E1,E2,E3,E4)

Sau đó, nếu bạn thay đổi các giá trị trong những ô đó, mảng tràn (spilled array) sẽ thay đổi kích thước ngay lập tức.

Bản chất động của hàm SEQUENCE thực sự phát huy tác dụng khi nó được kết hợp với các hàm khác. Trong ảnh chụp màn hình bên dưới, công thức:

=SEQUENCE(COUNTA(B:B)-1)

Đếm số ô không trống trong cột B—trừ đi một để loại bỏ tiêu đề cột—để xác định số hàng mà trình tự nên chiếm.

Bạn cũng có thể lồng hàm DATE để tạo ra một chuỗi ngày tháng. Gõ:

=SEQUENCE(1,20,DATE(2024,3,1),7)

Làm cho các ngày chạy ngang qua hàng đầu tiên trong 20 cột, bắt đầu từ ngày 1 tháng 3, và tăng thêm bảy ngày mỗi lần.

Hãy áp dụng định dạng số “Date” (Ngày tháng) cho tất cả các ô liên quan trước khi nhập công thức SEQUENCE-DATE.

Trên thực tế, SEQUENCE có thể được kết hợp với nhiều hàm để tạo ra các danh sách động dựa trên nhiều điều kiện khác nhau, vì vậy việc nó được giới thiệu vào Excel cho Microsoft 365 năm 2020 và phiên bản độc lập 2021 của chương trình thực sự là một sự thay đổi cuộc chơi.

4. SORTBY: Trích xuất và sắp xếp động một mảng

Hàm SORTBY loại bỏ nhu cầu phải sử dụng PivotTables hoặc các công thức phức tạp để sắp xếp dữ liệu một cách động. Và mặc dù công cụ bộ lọc nâng cao có thể tạo ra kết quả tương tự trong thời gian ngắn, nhưng về lâu dài nó sẽ không cập nhật kết quả để phản ánh các thay đổi trong dữ liệu nguồn. Tóm lại, SORTBY làm cho việc trích xuất và sắp xếp dữ liệu trực tiếp trở nên dễ dàng.

Mặc dù cú pháp của hàm có vẻ phức tạp, nhưng thực ra nó khá trực quan: =SORTBY(a,b¹,b²,c¹,c²...)

Trong đó:

a (bắt buộc) là phạm vi hoặc mảng bạn muốn sắp xếp,
b¹ (bắt buộc) là phạm vi hoặc mảng đầu tiên mà bạn muốn dữ liệu được sắp xếp theo,
b² (tùy chọn) là thứ tự sắp xếp cho b¹ (1 cho tăng dần/mặc định, -1 cho giảm dần), và
c¹ và c² (tùy chọn) là mảng và thứ tự thứ hai mà bạn muốn sắp xếp dữ liệu theo, và bạn có thể có tổng cộng tối đa 64 cặp này.

Trong ảnh chụp màn hình bên dưới, công thức trích xuất và sắp xếp bảng T_Results theo cột Điểm (Score) theo thứ tự giảm dần:

=SORTBY(T_Results,T_Results[Score],-1)

SORTBY tạo ra một mảng mới có tính động, nghĩa là dữ liệu nguồn không bị ảnh hưởng và kết quả tự động cập nhật để phản ánh mọi thay đổi.

Lần này, SORTBY được sử dụng để sắp xếp dữ liệu theo cột Tuổi (Age), sau đó là cột Điểm (Score), cả hai đều theo thứ tự giảm dần:

 =SORTBY(T_Results,T_Results[Score],-1,T_Results[Age],-1)

Hàm SORTBY có thể được sử dụng với các hàm khác trong Excel để tinh chỉnh kết quả hơn nữa. Ở đây, nó được sử dụng cùng với hàm FILTER để tạo ra kết quả sắp xếp dữ liệu theo cột Điểm, nhưng chỉ hiển thị những người đạt được hơn 60 điểm tổng thể:

=FILTER(SORTBY(T_Results,T_Results[Score],-1),SORTBY(T_Results[Score],T_Results[Score],-1)>60)

5. UNIQUE: Trả về động một danh sách các giá trị khác biệt

Hàm UNIQUE là hàm mà những người sử dụng Excel hàng ngày đã chờ đợi—ngay cả khi họ không nhận ra điều đó. Trước khi nó được thêm vào Excel, nếu bạn muốn trích xuất các giá trị duy nhất từ một mảng, bạn sẽ cần lồng ít nhất ba hàm trong một công thức mảng CSE cũ, tạo một PivotTable hoặc nhấp qua nhiều menu và hộp thoại khác nhau. Tất cả các phương pháp này đều tốn thời gian, điều mà nhiều người trong chúng ta không có.

Tuy nhiên, hàm UNIQUE cho phép bạn đạt được kết quả tương tự chỉ trong vài giây. Hơn nữa, nó có tính động, tự cập nhật để phản ánh các thay đổi trong dữ liệu nguồn, và tạo ra một mảng tự co giãn khi cần thiết.

Cú pháp UNIQUE có ba đối số—một bắt buộc và hai tùy chọn: =UNIQUE(a,b,c)

Trong đó:

a (bắt buộc) là mảng để trả về các giá trị duy nhất,
b (tùy chọn) là FALSE (mặc định) để trả về các hàng duy nhất hoặc TRUE để trả về các cột duy nhất, và
c (tùy chọn) là FALSE (mặc định) để trả về tất cả các giá trị khác biệt hoặc TRUE để trả về các giá trị chỉ xuất hiện một lần.

UNIQUE là không phân biệt chữ hoa chữ thường. Nói cách khác, nó sẽ coi Geek, GEEK, và geek là cùng một chuỗi văn bản.

Trong ví dụ này, công thức được nhập vào ô E2 trích xuất các tên duy nhất từ cột Name của bảng T_Players. Các đối số b và c bị bỏ qua, vì vậy nó mặc định trả về các giá trị duy nhất (đối số c) theo hàng (đối số b).

=UNIQUE(T_Players[Name])

Ở đây, gõ:

=SORT(UNIQUE(T_Players[Name]))

Vào ô E2 sẽ trả về một danh sách các tên duy nhất theo thứ tự bảng chữ cái.

Cuối cùng, bạn có thể sử dụng UNIQUE với SORT và FILTER để trích xuất một danh sách theo bảng chữ cái gồm các mục duy nhất dựa trên các tiêu chí nhất định. Trong ví dụ này, tôi đã trích xuất tên của tất cả những người chơi đã ghi được hơn 6 điểm, với kết quả được sắp xếp theo bảng chữ cái:

=SORT(UNIQUE(FILTER(T_Players[Name],T_Players[Score]>6)))

Trong tất cả các ví dụ này, kết quả sẽ tự động cập nhật để phản ánh mọi thay đổi trong dữ liệu nguồn. Hơn nữa, vì công thức tham chiếu đến một tiêu đề cột trong bảng Excel, nếu bạn thêm hoặc xóa hàng, dữ liệu được trích xuất sẽ điều chỉnh theo.

6. XLOOKUP: Truy xuất động nhiều mục liên quan

XLOOKUP đã xuất hiện lâu hơn một chút so với các hàm trên, nhưng tính hữu ích của nó đã tăng lên cùng với sự ra đời của mảng động.

Nói một cách đơn giản, hàm XLOOKUP là một phiên bản hiện đại hóa của VLOOKUP và HLOOKUP, tìm kiếm một giá trị cụ thể trong một tập dữ liệu và trả về một giá trị tương ứng. Điểm mạnh của XLOOKUP so với các hàm tiền nhiệm là khả năng thực hiện tra cứu dọc và ngang, tra cứu giá trị ở bất kỳ cột hoặc hàng nào, hiểu các mảng tra cứu và trả về là các đối số riêng biệt, xử lý lỗi và mặc định theo các cài đặt ưu tiên.

Đây là cách nó hoạt động: =XLOOKUP(a,b,c,d,e,f)

Trong đó:

a (bắt buộc) là giá trị tra cứu,
b (bắt buộc) là mảng tra cứu,
c (bắt buộc) là mảng trả về,
d (tùy chọn) là văn bản trả về nếu giá trị tra cứu (a) không tìm thấy trong mảng tra cứu (b),
e (tùy chọn) là chế độ so khớp (0 (mặc định) = so khớp chính xác; -1 = so khớp chính xác hoặc mục nhỏ hơn tiếp theo; 1 = so khớp chính xác hoặc mục lớn hơn tiếp theo; 2 = so khớp ký tự đại diện), và
f (tùy chọn) là chế độ tìm kiếm (1 (mặc định) = từ trên xuống dưới hoặc từ trái sang phải; -1 = thứ tự ngược lại; 2 = tìm kiếm nhị phân trong mảng tăng dần; -2 = tìm kiếm nhị phân trong mảng giảm dần).

XLOOKUP nổi tiếng nhất với khả năng trả về một kết quả duy nhất. Công thức này trả về tên người chơi từ cột Name của bảng T_Results trong ô G2 khi một số ID được nhập vào ô F2:

=XLOOKUP(F2,T_Results[ID],T_Results[Name])

Tuy nhiên, nó có thể trả về nhiều hơn một kết quả, biến nó thành một hàm mảng động. Lần này, khi tôi nhập một ID vào ô F2, XLOOKUP trả về tên, tuổi và điểm số của người chơi dưới dạng mảng tràn kéo dài đến ô I2, giúp tôi không cần phải sử dụng một công thức riêng biệt cho mỗi thông tin:

=XLOOKUP(F2,T_Results[ID],T_Results[[Name]:[Score]])

Ngoài việc tràn theo chiều ngang, XLOOKUP có thể tràn theo chiều dọc. Công thức này trả về điểm số của bất kỳ người chơi nào có ID được nhập vào các ô từ F2 đến F13:

=XLOOKUP(F2:.F13,T_Results[ID],T_Results[Score],"")

Dấu chấm (còn được gọi trong ngữ cảnh này là toán tử tham chiếu cắt (trim reference operator)) sau dấu hai chấm trong đối số giá trị tra cứu loại bỏ khoảng trắng thừa (trailing blanks) khỏi tham chiếu, cải thiện hiệu suất của bảng tính và công thức.

Ví dụ cuối cùng này sử dụng một công thức XLOOKUP duy nhất để trả về tất cả điểm (grades) của học sinh dựa trên điểm số của họ, ngăn ngừa nhu cầu phải sao chép nó vào tất cả các ô trong cột C:

=XLOOKUP(B2:.B100,T_Boundaries[Score],T_Boundaries[Grade],"FAIL",-1)

7. Những điều cần lưu ý về hàm và công thức mảng động trong Excel

Dưới đây là một số điểm cuối cùng quan trọng cần lưu ý khi sử dụng các hàm mảng động:

Kết quả của một công thức mảng động không thể tràn vào một cột của bảng Excel. Vì vậy, bạn phải luôn nhập nó vào một ô thông thường.
Nếu một ô nơi mảng động cần tràn đã có dữ liệu, công thức sẽ trả về lỗi #SPILL!, vì vậy hãy đảm bảo có đủ chỗ trống trước khi nhập công thức.
Để tham chiếu một mảng động trong một công thức khác, hãy gõ ký hiệu thăng (#), còn được gọi là toán tử phạm vi tràn (spill range operator), ngay sau khi tham chiếu ô đầu tiên mà thôi.
Mảng động tham chiếu đến một sổ làm việc khác chỉ tạo ra kết quả mong đợi nếu cả hai sổ làm việc đều đang mở.

Một cách khác để thực hiện nhiều phép tính bằng một công thức duy nhất là sử dụng hằng số mảng (array constants). Mặc dù chúng kém linh hoạt hơn công thức mảng động, nhưng chúng giúp bạn tiết kiệm việc phải nhập nhiều công thức hoặc công thức dài.

8. Kết luận

Sáu chức năng mới của Microsoft Excel đã thực sự cách mạng hóa cách bạn làm việc với dữ liệu, từ các công cụ AI như Analyze Data, hàm động như FILTER và XLOOKUP, đến khả năng trực quan hóa mạnh mẽ với Power Query. Những tính năng này không chỉ giúp tiết kiệm thời gian mà còn nâng cao độ chính xác và sáng tạo trong phân tích dữ liệu, biến Excel thành trợ thủ đắc lực cho cả người mới bắt đầu lẫn chuyên gia. Hãy khám phá và áp dụng ngay các chức năng này để đưa công việc của bạn lên một tầm cao mới!

Xem thêm: Mọi thứ bạn cần nên biết về xác thực dữ liệu trong Microsoft Excel

Nếu bạn cần phần mềm, thiết bị hoặc giải pháp công nghệ để tối ưu hóa trải nghiệm sử dụng Excel, hãy ghé thăm COHOTECH – cửa hàng cung cấp các sản phẩm và dịch vụ công nghệ chất lượng cao, luôn sẵn sàng đồng hành cùng bạn trong mọi dự án!

Hãy chia sẻ trải nghiệm của bạn với những tính năng mới của Excel hoặc bất kỳ mẹo sử dụng nào trong phần bình luận bên dưới. Đừng quên chia sẻ bài viết này với bạn bè và đồng nghiệp để cùng nhau làm chủ Microsoft Excel nhé!