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é!
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:
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..)
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...)
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 nhiều điều kiện 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], ...)
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 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],...)
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 đượ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])
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])
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 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],...)
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)
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])
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)
Để 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])
Ví dụ đơn giản là nhận một chuỗi từ 1 đến 10. Công thức sẽ là:
= SEQUENCE (10)
Đâ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.
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],...)
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],...)
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)
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é!
Bạn đã dùng Excel lâu năm, nhưng đến giờ này vẫn còn đang:
Vậy thì tại sao bạn không đăng ký ngay khóa học Tuyệt đỉnh Excel - Khóa học Excel online từ cơ bản đến nâng cao của Gitiho. Bạn sẽ nhanh chóng làm chủ công cụ này và tự tin khi thực hiện công việc từ tạo bảng tính, quản lý và tính toán số liệu, thành thạo các tính năng và hàm Excel, tạo và tùy chỉnh báo cáo và biểu đồ cho đến tự động hóa Excel bằng VBA.
Bấm vào Đăng ký ngay để bắt đầu học Excel cùng Gitiho nhé!