Microsoft Excel từ lâu đã khẳng định vị thế là một công cụ quản lý dữ liệu mạnh mẽ, không thể thiếu trong các lĩnh vực kinh doanh, nghiên cứu và giáo dục. Với hơn 450 hàm tích hợp sẵn, ứng dụng này luôn mang đến vô vàn cách thức để người dùng làm việc hiệu quả hơn, dù bạn mới bắt đầu với bảng tính đầu tiên hay đã có nhiều năm kinh nghiệm. Để giúp bạn đẩy nhanh hành trình khám phá và làm chủ Excel, chúng tôi đã tổng hợp 7 hàm vượt xa các chức năng cơ bản như SUM hay DIVIDE. Khi đã thành thạo những hàm Excel nâng cao này, bạn sẽ nhanh chóng cải thiện đáng kể tốc độ và năng suất trong các dự án của mình.
7. Hàm IF
Đơn giản nhưng đầy uy lực
Hàm IF là một trong những hàm quen thuộc với nhiều người dùng Excel và cả những lập trình viên. Chức năng đơn giản này trả về một giá trị dựa trên kết quả của một phép so sánh logic, tương tự như cách chúng ta nói “nếu điều này, thì điều kia”.
Hàm IF là một hàm logic cơ bản và được sử dụng rộng rãi. Nó có khả năng so sánh cả giá trị số và văn bản, hỗ trợ các phép toán học và có thể kết hợp với nhiều hàm Excel khác. Khi một điều kiện được đáp ứng, kết quả đầu ra có thể được tùy chỉnh để hiển thị văn bản, số hoặc thậm chí là các ký hiệu. Hơn nữa, hàm IF có thể được sử dụng để kiểm tra lỗi và lồng ghép nhiều lần để xử lý các điều kiện phức tạp hơn.
Sử dụng hàm IF trong Excel để kiểm tra điều kiện giảm giá cho khách hàng dựa trên số lượng mua.
Trong tập dữ liệu mẫu này, ô G7 sẽ cho biết liệu John có được giảm giá hay không nếu anh ta mua hơn 40 mặt hàng.
Cú pháp cơ bản:
=IF(biểu_thức_so_sánh, giá_trị_nếu_đúng, giá_trị_nếu_sai)
6. Hàm SUMIFS
Cộng các số nếu đáp ứng nhiều điều kiện
Hàm SUMIFS là sự kết hợp giữa hàm SUM cơ bản và hàm IF. Nó cộng tổng tất cả các giá trị thỏa mãn một hoặc nhiều điều kiện được chỉ định. Nếu bạn cần tính tổng điểm của một học sinh, tổng doanh số của một nhân viên cụ thể, hoặc tổng hóa đơn cho công việc đã hoàn thành, đây chính là hàm bạn nên sử dụng.
Ngoài một điều kiện đơn lẻ, bạn có thể bổ sung tới 127 tiêu chí và phạm vi điều kiện khác. Hàm SUMIFS thường được kết hợp với các ký tự đại diện (wildcard characters), cho phép bạn thực hiện tìm kiếm gần đúng thay vì yêu cầu khớp chính xác.
Hãy chắc chắn không nhầm lẫn SUMIFS với SUMIF, vì chúng có thứ tự đối số khác nhau.
Ví dụ cách dùng hàm SUMIFS trong Excel để tính tổng số lượng trái cây đã mua bởi một người dùng cụ thể.
Chúng ta có thể sử dụng SUMIFS để tổng hợp tất cả các loại trái cây mà John đã mua.
Cú pháp cơ bản:
=SUMIFS(phạm_vi_tính_tổng, phạm_vi_điều_kiện1, điều_kiện1, [phạm_vi_điều_kiện2, điều_kiện2], ...)
5. Hàm XLOOKUP
Một hàm tra cứu cực kỳ mạnh mẽ
Được thêm vào Excel lần đầu tiên vào năm 2019, XLOOKUP có vai trò quan trọng với Excel như WASD đối với chơi game. Nó là công cụ không thể thiếu để tra cứu các chỉ mục trong các bảng tính ở mọi kích thước, kết hợp những ưu điểm tốt nhất của hàm VLOOKUP kém mạnh mẽ hơn và hàm INDEX MATCH phức tạp hơn. XLOOKUP hoạt động cả theo chiều ngang và chiều dọc. Nếu không tìm thấy kết quả khớp chính xác, nó sẽ trả về kết quả gần đúng nhất. Tuyệt vời hơn nữa, nó có thể trả về một mảng với nhiều mục. Ngoài ra, hàm này còn bao gồm đối số if_not_found để hiển thị một thông báo cụ thể khi không tìm thấy kết quả.
Minh họa ứng dụng hàm XLOOKUP trong Excel để tra cứu doanh số bán hàng của một loại sản phẩm.
XLOOKUP có thể giúp chúng ta tìm ra số lượng cherry đã được bán.
Cú pháp cơ bản:
=XLOOKUP(giá_trị_tra_cứu, mảng_tra_cứu, mảng_trả_về)
4. Hàm LET
Gán biến trong một phép tính duy nhất
Hàm LET cho phép gán tên biến trong một phép tính duy nhất. Khái niệm này tương tự như việc gán biến trong lập trình. Điều này đặc biệt hữu ích cho các công thức dài và phức tạp, nơi một giá trị được tham chiếu nhiều lần. Không chỉ giúp cải thiện hiệu suất bằng cách lưu trữ kết quả từ một phép tính trước đó, mà nó còn tăng khả năng đọc hiểu công thức, cung cấp ngữ cảnh tốt hơn và dễ dàng gỡ lỗi hơn.
Ví dụ, nếu chúng ta gán “counter” một giá trị là 7 và sử dụng nó trong toàn bộ hàm LET, chúng ta chỉ cần thay đổi giá trị gán ở đầu công thức để cập nhật biến này.
Bất cứ khi nào bạn thấy mình gọi một số hoặc một kết quả tính toán lặp đi lặp lại trong một công thức, hãy cân nhắc xem có phù hợp để gán tên bằng hàm LET hay không. Mỗi hàm LET hỗ trợ tối đa 126 cặp tên/giá trị trong một câu lệnh duy nhất. Tuy nhiên, hãy lưu ý rằng một khi bạn định nghĩa một cặp tên/giá trị, nó phải được sử dụng ở đâu đó trong phép tính của hàm LET.
Sử dụng hàm LET trong Excel để định nghĩa biến giá chuối, giúp công thức dễ đọc và chỉnh sửa hơn.
Việc định nghĩa giá chuối bằng biến BANANA_COST giúp công thức dễ đọc hơn. Ngoài ra, nếu cần thay đổi giá chuối, chúng ta chỉ cần thay đổi giá trị của biến một lần duy nhất.
Cú pháp cơ bản:
=LET(tên, giá_trị_tên, phép_tính)
3. Hàm UNIQUE
Tìm các mục nhập duy nhất trong một phạm vi
Hàm UNIQUE quét một danh sách các giá trị và chỉ trả về những giá trị riêng biệt, không trùng lặp. Điều này đặc biệt hữu ích khi lọc các tập dữ liệu lớn có nhiều mục nhập trùng lặp. So với các hàm khác trong danh sách này, UNIQUE có cấu trúc đơn giản hơn, chỉ yêu cầu gọi hàm và một phạm vi dữ liệu. Tất nhiên, bạn có thể kết hợp UNIQUE với các hàm khác, chẳng hạn như SORT, để sửa đổi thứ tự của kết quả đầu ra.
Hàm UNIQUE trong Excel hiển thị danh sách tên khách hàng không trùng lặp từ một tập dữ liệu.
Hàm UNIQUE chỉ trả về các tên khách hàng duy nhất.
Cú pháp cơ bản:
=UNIQUE(phạm_vi_dữ_liệu)
2. Hàm TEXTBEFORE và TEXTAFTER
Cắt và trích xuất dữ liệu tùy ý
Đôi khi, bạn cần trích xuất một phần thông tin từ một mục nhập dữ liệu phức hợp, chẳng hạn như từ một họ tên đầy đủ, địa chỉ hoặc tên công ty. Đây chính là lúc các hàm TEXTBEFORE và TEXTAFTER phát huy tác dụng. Chúng cho phép bạn đặt một dấu phân cách (delimiter) và chỉ trả về phần thông tin nằm trước hoặc sau dấu phân cách đó. Ngoài ra, bạn có thể bao gồm các đối số để làm cho hàm phân biệt chữ hoa/thường hoặc đảo ngược thứ tự tìm kiếm để bắt đầu từ cuối chuỗi.
Cú pháp cơ bản:
=TEXTBEFORE(văn_bản, dấu_phân_cách)
=TEXTAFTER(văn_bản, dấu_phân_cách)
1. Tự tạo hàm của riêng bạn!
Gọi một công thức phức tạp bằng một tên dễ nhớ
Khi các hàm tích hợp sẵn không đáp ứng được yêu cầu, Excel cho phép bạn tạo ra các công thức toán học phức tạp có thể tái sử dụng, và bạn có thể gọi chúng bằng một tên đơn giản. Ví dụ, bạn có thể xây dựng một hàm chuyển đổi đơn vị, điều chỉnh giá trị tiền tệ theo lạm phát, tính toán chi phí nhập khẩu sau thuế và vận chuyển, hoặc thậm chí thiết lập một phương trình bậc hai như một máy tính tích hợp sẵn.
Thiết lập một hàm tùy chỉnh rất dễ dàng. Bạn chỉ cần mở Name Manager (Trình quản lý tên) trong Excel, định nghĩa tên, phạm vi và công thức của nó, và hàm đó đã sẵn sàng để hoạt động. Lần tới khi bạn cần sử dụng, chỉ cần gõ tên hàm, bao gồm các đối số phù hợp, và để nó thực hiện “phép thuật” của mình.
Luôn có nhiều điều để học hỏi
Những hàm vừa được giới thiệu chỉ là một phần nhỏ trong những gì Excel có thể mang lại. Bạn có thể mất nhiều năm để tìm hiểu tất cả, nhưng việc làm chủ một vài hàm được sử dụng phổ biến trước tiên luôn là một ý tưởng hay. Có vô số cách sáng tạo để sửa đổi và kết hợp các hàm, chẳng hạn như với EXACT, TRIM, CONCATENATE và nhiều hàm khác. Nếu một hàm tạo ra một kết quả đầu ra, rất có thể bạn có thể chuyển kết quả đó vào một hàm khác để đạt được nhiều điều hơn nữa. Sử dụng Excel một cách sáng tạo là một trải nghiệm bổ ích, và có thể nói là thú vị.
