12 công thức hàm mới trong Excel 2019 và Microsoft 365

Mặc dù các hàm có sẵn trong các phiên bản Excel cũ đủ để thực hiện bất kỳ loại tính toán và tự động hóa nào, nhưng đôi khi các công thức trở nên phức tạp. Ví dụ: Trong phiên bản Excel 2016, nếu bạn không tìm thấy giá trị lớn nhất với một số điều kiện, bạn phải sử dụng một số thủ thuật phức tạp. Những điều nhỏ nhặt nhưng quan trọng này sẽ được giải quyết trong Excel 2019 và 365.
Có hơn 10 hàm mới trong Excel 2019 và 365 giúp giảm thiểu nỗ lực của con người và sự phức tạp của các công thức. Cùng Gitiho tìm hiểu trong bài viết này nhé! 

Các cú pháp hàm mới trong Excel 2019 và Microsoft 365

Excel là một công cụ tuyệt vời để báo cáo, phân tích, sắp xếp và tự động hóa dữ liệu. Các hàm Excel giúp ích rất nhiều cho việc thao tác với dữ liệu. Các hàm như COUNTIFS, SUMIFS, VLOOKUP, v.v. là những hàm mạnh và được sử dụng thường xuyên nhất kể từ khi ra đời trong Excel. Tuy nhiên, trong Excel 2019 và 365, Microsoft đã cho ra đời một số hàm mới hữu ích, gắn liền với công việc hàng ngày của người dùng hơn. Sau đây là 12 hàm mới:

Hàm MAXIFS

Trong các phiên bản từ Excel 2016 trở xuống, nếu bạn muốn nhận giá trị lớn nhất trong một phạm vi khi một hoặc nhiều điều kiện được thỏa mãn, bạn phải sử dụng hàm MAX với hàm IF bằng một số thủ thuật. Điều đó không khó lắm nhưng mất thời gian và gây ra sự khó hiểu đối với một số người.

Excel 2019 giới thiệu một hàm mới có tên là hàm MAXIFS. Hàm này trả về giá trị lớn nhất từ ​​một mảng khi tất cả các điều kiện đã cho đều phù hợp.

Cú pháp của hàm là:

=MAXIFS(max_range,criteria_range1,criteria1,criteria_range2,criteria2..)

• Max_range (bắt buộc):  Là phạm vi chứa giá trị lớn nhất.
• Criteria_range1 (bắt buộc):  Là tập hợp các ô cần đánh giá theo tiêu chí.
• Criteria1 (bắt buộc):  Đây là tiêu chí ở dạng số, biểu thức, văn bản xác định ô nào sẽ được đánh giá là lớn nhất.
• Criteria_range2, Criteria2 (tùy chọn): Các phạm vi bổ sung và các tiêu chí được liên kết của chúng. Bạn có thể nhập tối đa 126 cặp phạm vi/tiêu chí. 

Hàm MINIFS

Tương tự như hàm MAXIFS, hàm MINIFS được sử dụng để nhận giá trị nhỏ nhất từ ​​một phạm vi khi tất cả các điều kiện đã cho đều được thỏa mãn.

Cú pháp của hàm là:

=MINIFS(min_range,criteria_range1,criteria1,criteria_range2,criteria2...)

• Min_range (bắt buộc): Là phạm vi chứa giá trị nhỏ nhất.
• Criteria_range1 (bắt buộc):  Là tập hợp các ô cần đánh giá theo tiêu chí.
• Criteria1 (bắt buộc):  Đây là tiêu chí ở dạng số, biểu thức, văn bản xác định ô nào sẽ được đánh giá là nhỏ nhất.
• Criteria_range2, Criteria2 (tùy chọn): Các phạm vi bổ sung và các tiêu chí được liên kết của chúng. Bạn có thể nhập tối đa 126 cặp phạm vi/tiêu chí. 

Hàm IFS

Hàm IF lồng nhau có một chức năng đặc biệt trong cuộc sống công việc hàng ngày của mình, mình rất thích nó. Nhưng đối với một số người mới học, nó rất phức tạp. Hàm IF lồng nhau cho phép chúng ta kiểm tra nhiều điều kiện và trả về một giá trị khác khi bất kỳ điều kiện nào được đáp ứng. Các công thức trở nên phức tạp với ngày càng nhiều IF trong hàm.

Excel 2019 và Excel 365 sử dụng hàm IFS. Nó có thể kiểm tra nhiều điều kiện và trả về các giá trị khác nhau cho mỗi điều kiện.

Cú pháp của hàm IFS:

=IFS (condition1, Value1_If_True, [condition2, Value2_If_True], ...)

• Condition1 (bắt buộc): Điều kiện  đầu tiên.
• Value1_If_True (bắt buộc): Kết quả nếu điều kiện đầu tiên là đúng.
• [Condition2] (tùy chọn): Điều kiện thứ hai, nếu có.
• [Value1_If_True]: Kết quả nếu điều kiện thứ hai là đúng.

Bạn có thể có nhiều điều kiện kết hợp và kết quả mà bạn muốn.

Ví dụ: Bạn cần phải chấm điểm cho học sinh theo điều kiện như sau:
- Điểm A nếu điểm trên 90
- Điểm B nếu điểm trên 70
- Điểm C nếu điểm trên 50
- Điểm D nếu điểm trên 20.
- Điểm F nếu điểm dưới 20.

Cú pháp hàm trong ví dụ này là:
= IFS (A2 <20, ”F”, A2 <50, ”D”, A2 <70, ”C”, A2 <90, ”B”, A2> 90, ”A”)

Trong đó:
A2 “F”: trả về F nếu điều kiện 1 thỏa mãn.
A2 “D”: trả về D nếu điều kiện 2 thỏa mãn.
A2 ”B”: trả về B nếu điều kiện 3 thỏa mãn.
A2> 90: điều kiện 4

Hàm SWITCH

Hàm SWITCH trả về các giá trị khác nhau tùy thuộc vào một kết quả của biểu thức. Nghe giống hàm IFS nhỉ? Trên thực tế, hàm này là để thay thế một loại công thức IF lồng nhau khác.

Không giống như hàm IFS trả về giá trị dựa trên TRUE, FALSE; hàm SWITCH trả về kết quả dựa trên GIÁ TRỊ được trả về bởi biểu thức.

Cú pháp hàm là:

=SWITCH (expression, value1,result1, [default or value2,result2],...) 

• Expression: Biểu thức hoặc giá trị được so sánh với mỗi giá trị được cung cấp như tham chiếu ô, công thức hoặc giá trị tĩnh.
• value1, [value2]: Giá trị được so sánh với biểu thức được cung cấp.
• result1 và [result2]: Các kết quả được trả về nếu nếu giá trị tương ứng khớp với biểu thức được cung cấp.
• [default]: Trả về giá trị mặc định nếu không có giá trị nào được cung cấp khớp với biểu thức được cung cấp.

Ví dụ: Chúng ta có một số mã của Hoa Kỳ. Công thức SWITCH sẽ cho chúng ta biết tên bang tương ứng.

= SWITCH (RIGHT (A2,2), "UT", "Utah", "TX", "Texas", "OH", "Ohio", "?")

Ở đây hàm RIGHT trích xuất 2 ký tự cuối cùng của ô để khớp với mã vùng trong công thức và trả về tên bang tương ứng với nó hoặc trả về “?” nếu không có điều kiện nào khớp.

Hàm FILTER

Hàm FILTER được sử dụng để lọc dữ liệu dựa trên một số tiêu chí như chúng ta đã sử dụng tùy chọn bộ lọc từ tab Home trong Excel. Hàm FILTER hoạt động giống như tùy chọn bộ lọc, nó khác là sẽ trả về dữ liệu đã lọc bằng cách sử dụng một hàm. Dữ liệu đã lọc này có thể được sử dụng làm nguồn dữ liệu cho các công thức khác.

Cú pháp của hàm FILTER là:

=FILTER(array,include,[if_empty])

• array (bắt buộc): vùng dữ liệu cần lọc
• include (bắt buộc): điều kiện cần lọc và nằm trong cột nào (xác định đồng thời cả 2 yếu tố này)
• if_empty (tùy chọn): Giá trị trả về nếu không có kết quả

Hàm SORT

Trong Excel 2016 trở xuống, việc tạo một sắp xếp phải áp dụng công thức thực sự khó khăn. Quá trình này được đơn giản hóa trong Excel 2019 và 365 như sau:

Hàm SORT sắp xếp mảng đã cho theo thứ tự tăng dần hoặc giảm dần theo cột / hàng đã cho.

Cú pháp của hàm SORT là:

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

• array: Là mảng dữ liệu cần sắp xếp
• [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ể). Nếu không nhập thì mặc định là cột/dòng đầu tiên.
• [sort_order]:  Thứ tự mà bạn muốn sắp xếp. Đối với tăng dần là 1 và giảm dần là -1. Theo mặc định, nó là 1.
• [by_col]:  Đặt giá trị True (1) nếu bạn muốn sắp xếp một mảng ngang. Theo mặc định, nó là False (0) cho dữ liệu dọc.

Ví dụ: hãy sắp xếp danh sách sau theoc ột thứ 2, thứ tự giảm dần

Công thức hàm trong trường hợp này là:
= SORT (A2: C10,2, -1)

Hàm SORTBY

Hàm SORTBY tương tự như hàm SORT. Sự khác biệt duy nhất là mảng sắp xếp ựa trên các giá trị trong phạm vi hoặc mảng khác. Sắp xếp có thể được thực hiện bởi một hoặc nhiều cột.

Cú pháp hàm:

=SORTBY(array,sorting_array1,[order],...)

• array: Vùng dữ liệu mà bạn muốn sắp xếp.
• Sorting_array1: Tiêu chí sắp xếp. Kích thước của mảng này phải tương thích với array.
• [order] (tùy chọn): Đặt nó thành -1 nếu bạn muốn thứ tự giảm dần. Theo mặc định, nó tăng dần (1).

Giả sử bạn muốn sắp xếp phạm vi A2: A11 theo phạm vi B2: B11, theo thứ tự giảm dần. Khi đó, công thức trong Excel 2019 hoặc 365 sẽ là:

=SORTBY(A2:A11,B2:B11,-1)

Hàm UNIQUE

Trong Excel 2016 trở xuống, chúng ta đã sử dụng kết hợp một số hàm để nhận tất cả các giá trị duy nhất từ ​​danh sách đã cho . Công thức được sử dụng khá phức tạp và khó hiểu.

Excel 2019 và 365 giới thiệu một hàm UNIQUE đơn giản trả về tất cả các giá trị duy nhất từ ​​một vùng dữ liệu nhất định.

Cú pháp của hàm UNIQUE là:

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

• array: Mảng mà bạn muốn trích xuất các giá trị duy nhất:
• [by_col] : Đặt giá trị TRUE (1) nếu mảng nằm ngang. Theo mặc định, nó là FALSE cho dữ liệu dọc.
• [exactly_once] : đặt giá trị TRUE (1) nếu bạn muốn trích xuất các giá trị chỉ xuất hiện một lần trong mảng. Theo mặc định, nó là FALSE (0) để trích xuất tất cả các giá trị duy nhất.

Ví dụ: Trích xuất các số duy nhất từ danh sách sau:

Cú pháp hàm trong trường hợp này là:

=UNIQUE(A2:A11)

Hàm SEQUENCE

Để có một dãy số trong Excel 2016 trở xuống, chúng ta sử dụng kết hợp các hàm. Giải pháp thực sự hiệu quả nhưng nó phức tạp. Excel 2019 và 365 cung cấp giải pháp dưới dạng hàm SEQUENCE trả về chuỗi số.

Cú pháp của hàm SEQUENCE là:

=SEQUENCE(rows,[columns],[start],[step])

• Rows: Số hàng mà bạn muốn chia.
• [column] (tùy chọn): Số cột mà bạn muốn chia. Đầu tiên các con số sẽ điền vào các cột và sau đó là các hàng. Theo mặc định, nó là 1.
• [start] (tùy chọn): Số bắt đầu của dãy số. Theo mặc định, nó là 1
• [step]: Đây là khoảng cách cho số tiếp theo. Theo mặc định, nó là 1.

Ví dụ đơn giản là nhận một chuỗi từ 1 đến 10. Công thức sẽ là:

= SEQUENCE (10)

 

Hàm RANDARRAY

Đây là một công thức mảng động khác trả về một mảng số ngẫu nhiên. Nó là sự kết hợp của hàm RAND và RANDBETWEEN. Bạn có thể nhận các số ngẫu nhiên, phân số hoặc số nguyên. Bạn có thể chỉ định số lượng số được nhập vào mà bạn muốn.

Cú pháp của hàm RANDARRAY là:

=RANDARRAY([rows],[columns],[min],[max],[integer])

Tất cả các đối số trong hàm này là tùy chọn. Theo mặc định, nó hoạt động như hàm RAND.

• [rows]:  Số hàng bạn muốn điền. Mặc định = 1.
• [columns]:  Số  cột bạn muốn điền. Mặc định = 1.
• [min]:  Giá trị thấp nhất mà bạn muốn trả về. Mặc định = 0.
• [max]:  Giá trị tối đa mà bạn muốn trả về. Mặc định = 1.
• [integer]:  Đặt giá trị True, nếu bạn muốn các số ngẫu nhiên là số nguyên. Theo mặc định, nó là False và trả về các số ngẫu nhiên dạng phân số.

Hàm CONCAT

Trong Excel 2016 trở xuống, không dễ để nối nhiều ô hoặc dải ô bằng một công thức. Vấn đề này đã được Excel 2019 và 365 giải quyết bằng hàm CONCAT. Hàm có thể lấy nhiều ô, phạm vi làm đối số.

Cú pháp của hàm CONCAT là:

=CONCAT(text1,[text2],...)

• text1: Bất kỳ văn bản hoặc dải ô nào mà bạn muốn nối.
• [text2] (tùy chọn): Bất kỳ văn bản hoặc phạm vi nào.

Hàm TEXTJOIN

Hàm trên thực hiện nối tất cả các ô trong một phạm vi nhưng nó không nối các ô với bất kỳ dấu phân cách nào. Giả sử nếu bạn đang chuẩn bị dữ liệu cho tệp CSV thì bạn sẽ cần nối các ô bằng dấu phẩy. Trong trường hợp đó cả 2 hàm CONCATENATE và hàm CONCAT đều không làm được.

Trong trường hợp này, hàm TEXTJOIN hoạt động tuyệt vời và nối các văn bản đã cho với dấu phân cách tùy chọn.

=TEXTJOIN(delimiter,ignore_empty_cells,text1,[text2],...)

• delimiter:  Đây là dấu phân cách mà bạn muốn sử dụng giữa các văn bản riêng lẻ. Nó có thể là dấu phẩy (,), dấu chấm phẩy (;) hoặc bất cứ dấu gì, thậm chí là không có gì.
• ignore_empty_cells:  Đây là một biến nhị phân. TRUE nếu bạn muốn bỏ qua các ô trống trong phạm vi hoặc FALSE để bao gồm các ô trống.
• text1:  Đây là văn bản mà bạn muốn nối. Nó có thể là văn bản, ô tính hoặc một phạm vi.
• [text2] (tùy chọn): Bất kỳ văn bản hoặc phạm vi nào.

Giả sử mình muốn nối dải ô A1: A10 với dấu phẩy và bỏ qua các ô trống

Cú pháp hàm như sau: =TEXTJOIN(",",1,A1:A10)

Kết luận

Trong bài viết trên, Gitiho đã giới thiệu tổng quát cho bạn về những cú pháp hàm mới trong Excel 2019 và 365. Chúng mình đã có một số những bài viết về từng hàm cụ thể được gán link tương ứng, bạn hãy đón xem nhé!

Ngay bây giờ hãy cùng luyện tập thêm với bộ bài tập Excel có đáp án của Gitiho để nằm lòng tin học văn phòng nhé!