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é!
Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết, dễ hiểu nhất
Các cú pháp hàm mới trong Excel 2019 và Microsoft 365
Bạn đang đọc: 12 công thức 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, là một công cụ tuyệt vời để báo cáo giải trình, nghiên cứu và 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 tài liệu. Các hàm như COUNTIFS, SUMIFS, VLOOKUP, v.v. là những hàm mạnh và được sử dụng liên tục nhất kể từ khi sinh ra trong Excel. Tuy nhiên, trong Excel 2019 và 365, Microsoft đã cho sinh ra 1 số ít hàm mới có ích, gắn liền với việc làm hàng ngày của người dùng hơn. Sau đây là 12 hàm mới :
Hàm MAXIFS
Trong những 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 khoanh vùng phạm vi khi một hoặc nhiều điều kiện kèm theo được thỏa mãn nhu cầu, bạn phải sử dụng hàm MAX với hàm IF bằng 1 số ít thủ pháp. Điều đó không khó lắm nhưng mất thời hạn và gây ra sự khó hiểu so với một số ít 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à khoanh vùng phạm vi chứa giá trị lớn nhất .
- Criteria_range1 ( bắt buộc ):Là tập hợp những ô cần nhìn nhận theo tiêu chuẩn .
- Criteria1 ( bắt buộc ): Đây là tiêu chuẩn ở dạng số, biểu thức, văn bản xác lập ô nào sẽ được nhìn nhận là lớn nhất .
- Criteria_range2, Criteria2 ( tùy chọn ) :Các khoanh vùng phạm vi bổ trợ và những tiêu chuẩn được link của chúng. Bạn hoàn toàn có thể nhập tối đa 126 cặp khoanh vùng phạm vi / tiêu chuẩn .
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 khoanh vùng phạm vi khi toàn bộ những điều kiện kèm theo đã cho đều được thỏa mãn nhu cầu .Cú pháp của hàm là :
=MINIFS(min_range,criteria_range1,criteria1,criteria_range2,criteria2…)
- Min_range ( bắt buộc ) : Là khoanh vùng phạm vi chứa giá trị nhỏ nhất .
- Criteria_range1 (bắt buộc):Là tập hợp những ô cần nhìn nhận theo tiêu chuẩn .
- Criteria1 ( bắt buộc ): Đây là tiêu chuẩn ở dạng số, biểu thức, văn bản xác lập ô nào sẽ được nhìn nhận là nhỏ nhất.
- Criteria_range2, Criteria2 ( tùy chọn ) :Các khoanh vùng phạm vi bổ trợ và những tiêu chuẩn được link của chúng. Bạn hoàn toàn có thể nhập tối đa 126 cặp khoanh vùng phạm vi / tiêu chuẩn .
Hàm IFS
Hàm IF lồng nhau có một tính năng đặc biệt quan trọng trong đời sống việc làm hàng ngày của mình, mình rất thích nó. Nhưng so với 1 số ít người mới học, nó rất phức tạp. Hàm IF lồng nhau được cho phép tất cả chúng ta kiểm tra nhiều điều kiện kèm theo và trả về một giá trị khác khi bất kể điều kiện kèm theo nào được phân phối. 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 hoàn toàn có thể có nhiều điều kiện kèm theo phối hợp và tác dụng 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
Xem thêm : 25 kiến thức và kỹ năng Excel mà bạn nên biết từ cơ bản, tầm trung đến nâng cao
Hàm SWITCH
Hàm SWITCH trả về những giá trị khác nhau tùy thuộc vào một hiệu quả của biểu thức. Nghe giống hàm IFS nhỉ ? Trên trong thực tiễn, hàm này là để sửa chữa 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ề tác dụng 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 tác dụng được trả về nếu nếu giá trị tương ứng khớp với biểu thức được phân phối .
- [ default ] : Trả về giá trị mặc định nếu không có giá trị nào được cung ứng khớp với biểu thức được phân phối .
Ví dụ : Chúng ta có 1 số ít mã của Hoa Kỳ. Công thức SWITCH sẽ cho tất cả 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ự ở đầu cuối 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 kèm theo 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 vận dụng công thức thực sự khó khăn vất vả. 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 tài 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 tài liệu ( nhập số đơn cử ). Nếu không nhập thì mặc định là cột / dòng tiên phong .
- [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 list 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ựa như như hàm SORT. Sự độc lạ duy nhất là mảng sắp xếp ựa trên những giá trị trong khoanh vùng phạm vi hoặc mảng khác. Sắp xếp hoàn toàn có thể được thực thi 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 khoanh vùng phạm vi A2 : A11 theo khoanh vùng 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, tất cả chúng ta đã sử dụng phối hợp 1 số ít hàm để nhận toàn bộ những giá trị duy nhất từ list đã 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 những 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 những số duy nhất từ list 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, tất cả chúng ta sử dụng phối hợp những hàm. Giải pháp thực sự hiệu suất cao nhưng nó phức tạp. Excel 2019 và 365 phân phối 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 thuầ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ự phối hợp của hàm RAND và RANDBETWEEN. Bạn hoàn toàn có thể nhận những số ngẫu nhiên, phân số hoặc số nguyên. Bạn hoàn toà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ả những đối số trong hàm này là tùy chọn. Theo mặc định, nó hoạt động giải trí 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 xử lý bằng hàm CONCAT. Hàm hoàn toàn có thể lấy nhiều ô, khoanh vùng 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 triển khai nối tổng thể những ô trong một khoanh vùng phạm vi nhưng nó không nối những ô với bất kể dấu ngăn cách nào. Giả sử nếu bạn đang sẵn sàng chuẩn bị tài liệu cho tệp CSV thì bạn sẽ cần nối những ô bằng dấu phẩy. Trong trường hợp đó cả 2 hàm CONCA TENATE và hàm CONCAT đều không làm được .Trong trường hợp này, hàm TEXTJOIN hoạt động giải trí tuyệt vời và nối những văn bản đã cho với dấu phân làn 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 giờ đây hãy cùng rèn luyện 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é !
Source: https://thiennhuong.com
Category: Thủ Thuật
