Microsoft Excel từ lâu đã trở thành một công cụ quản lý dữ liệu mạnh mẽ và không thể thiếu trong nhiều lĩnh vực như kinh doanh, nghiên cứu và giáo dục. Với hơn 450 hàm tích hợp sẵn, tiềm năng của ứng dụng này là vô cùng lớn, từ những phép tính đơn giản nhất đến các phân tích dữ liệu phức tạp. Dù bạn mới bắt đầu làm quen với bảng tính hay đã có nhiều năm kinh nghiệm, luôn có những cách thức mới để làm việc hiệu quả hơn. Để giúp bạn đẩy nhanh hành trình học hỏi và tối ưu hóa quy trình làm việc, chúng tôi đã tổng hợp 7 hàm Excel nâng cao, vượt xa các hàm cơ bản như SUM hay DIVIDE. Khi đã thành thạo những hàm này, bạn sẽ cải thiện đáng kể cả tốc độ lẫn năng suất cho các dự án của mình.
1. Hàm IF: Đơn giản nhưng cực kỳ mạnh mẽ
IF là hàm logic cơ bản đã quen thuộc với nhiều người dùng Excel và lập trình viên. Hàm này trả về một giá trị dựa trên một phép so sánh logic, tương tự như cách chúng ta diễn đạt “nếu điều này, thì điều kia” trong giao tiếp hàng ngày.
Hàm IF được sử dụng rộng rãi và có khả năng so sánh cả giá trị số và văn bản. Nó hỗ trợ các phép toán học và có thể kết hợp linh hoạt 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òn có thể được dùng để kiểm tra lỗi hoặc lồng ghép nhiều điều kiện phức tạp hơn.
Cú pháp cơ bản:
=IF(điều_kiện_so_sánh, giá_trị_trả_về_nếu_đúng, giá_trị_trả_về_nếu_sai)
Ví dụ về hàm IF trong Excel hiển thị cách kiểm tra điều kiện giảm giá dựa trên số lượng mua
Trong ví dụ này, ô G7 sẽ hiển thị liệu John có nên nhận chiết khấu nếu anh ấy mua hơn 40 mặt hàng hay không. Hàm IF giúp chúng ta tự động hóa quyết định này.
2. Hàm SUMIFS: Tính tổng theo nhiều điều kiện chính xác
SUMIFS là sự kết hợp mạnh mẽ giữa hàm SUM cơ bản và hàm IF. Nó cộ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 cụ thể, tổng doanh số của một nhân viên duy nhất, hoặc tổng các 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 duy nhất, bạn có thể thêm tới 127 tiêu chí và phạm vi bổ sung. 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 “mờ” (fuzzy searches) thay vì yêu cầu khớp chính xác. Điều này mang lại sự linh hoạt đáng kể trong việc phân tích dữ liệu.
Lưu ý không nhầm lẫn SUMIFS với SUMIF, vì chúng có thứ tự đối số khác nhau. SUMIFS linh hoạt hơn vì có thể xử lý nhiều điều kiện cùng lúc.
Cú pháp cơ bản:
=SUMIFS(vùng_tổng, vùng_điều_kiện1, điều_kiện1, [vùng_điều_kiện2, điều_kiện2], ...)
Sử dụng hàm SUMIFS trong Excel để tính tổng số lượng trái cây mà John đã mua
Chúng ta có thể dùng hàm SUMIFS để thống kê tổng số lượng trái cây mà John đã mua, ví dụ như tổng số táo hoặc cam.
3. Hàm XLOOKUP: Công cụ tra cứu dữ liệu mạnh mẽ thay thế VLOOKUP
Được bổ sung vào Excel từ năm 2019, XLOOKUP là một bước tiến lớn trong các hàm tra cứu của Excel. Nó tổng hợp những ưu điểm tốt nhất của VLOOKUP (ít mạnh mẽ hơn) và INDEX MATCH (phức tạp hơn) để tạo ra một công cụ cực kỳ hiệu quả. Hàm XLOOKUP hoạt động linh hoạt theo cả 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. Đặc biệt hơn, XLOOKUP có thể trả về một mảng với nhiều mục và 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ả.
Hàm này giúp bạn tiết kiệm thời gian đáng kể khi cần tìm kiếm thông tin trong các bảng tính có kích thước bất kỳ.
Cú pháp cơ bản:
=XLOOKUP(giá_trị_tra_cứu, mảng_tra_cứu, mảng_kết_quả)
Minh họa hàm XLOOKUP trong Microsoft Excel giúp tìm số lượng cherry đã bán
XLOOKUP có thể giúp chúng ta nhanh chóng tìm ra số lượng cherry đã bán từ một danh sách dài các mặt hàng.
4. Hàm LET: Định nghĩa biến trong công thức Excel để tối ưu
Hàm LET cho phép bạn gán tên biến trong phạm vi một phép tính duy nhất, 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 tham chiếu đến một giá trị nhiều lần. Nó không chỉ 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à còn tăng khả năng đọc hiểu công thức, giúp 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 “counter”.
Bất cứ khi nào bạn thấy mình gọi một số hoặc một phép tính lặp đi lặp lại trong một công thức, hãy xem xét việc gán một tên bằng hàm LET. Mỗi hàm LET hỗ trợ lên đến 126 cặp tên/giá trị trong một câu lệnh duy nhất. Tuy nhiên, hãy nhớ 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.
Cú pháp cơ bản:
=LET (tên, giá_trị_tên, phép_tính)
Sử dụng hàm LET trong Excel để định nghĩa biến BANANA_COST giúp công thức dễ đọc và dễ cập nhật
Định nghĩa giá chuối bằng biến BANANA_COST giúp công thức dễ đọc hơn. Nếu cần thay đổi giá chuối, chúng ta chỉ cần cập nhật giá trị biến một lần duy nhất.
5. Hàm UNIQUE: Tìm kiếm các giá trị duy nhất trong một phạm vi
Hàm UNIQUE sẽ quét một danh sách các giá trị và chỉ trả về những mục khác biệt (không trùng lặp). Điều này vô cùng hữu ích khi bạn cần lọc các bộ dữ liệu lớn chứa nhiều mục trùng lặp, chẳng hạn như danh sách khách hàng, sản phẩm, hoặc địa chỉ email.
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 hàm UNIQUE với các hàm khác, chẳng hạn như SORT, để sắp xếp thứ tự đầu ra theo ý muốn. Điều này giúp bạn tạo ra các danh sách sạch và dễ quản lý hơn từ dữ liệu thô.
Cú pháp cơ bản:
=UNIQUE (vùng_dữ_liệu)
Hàm UNIQUE trong Excel trả về danh sách duy nhất các tên khách hàng từ một cột dữ liệu
Hàm UNIQUE chỉ trả về các tên khách hàng duy nhất từ danh sách, loại bỏ các mục trùng lặp.
6. Hàm TEXTBEFORE và TEXTAFTER: Cắt chuỗi linh hoạt
Đôi khi, bạn cần trích xuất một phần thông tin từ một mục dữ liệu phức tạp, chẳng hạn như từ một tên đầy đủ, địa chỉ hoặc tên công ty. Đây là lúc các hàm TEXTBEFORE và TEXTAFTER trở nên cực kỳ hữu ích. Chúng cho phép bạn đặt một dấu phân cách (delimiter) và chỉ trả về phân đoạn thông tin nằm trước hoặc sau dấu phân cách đó.
Ngoài ra, bạn có thể thêm các đối số để 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. Điều này mang lại sự linh hoạt cao trong việc xử lý và phân tích dữ liệu văn bản.
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)
Ví dụ, với chuỗi “Nguyễn Văn A – Giám đốc Marketing”, nếu dùng TEXTBEFORE với dấu phân cách “-“, bạn sẽ nhận được “Nguyễn Văn A”. Ngược lại, TEXTAFTER sẽ cho kết quả “Giám đốc Marketing”.
7. Tự tạo hàm tùy chỉnh: Khai phá sức mạnh tiềm ẩn của Excel
Khi các hàm tích hợp sẵn không thể đáp ứng hết nhu cầu phức tạp của bạn, Excel cung cấp khả năng tự tạo các công thức toán học phức tạp, có thể tái sử dụng và gọi bằng một tên đơn giản. Chức năng này giống như việc bạn lập trình các hàm riêng cho bảng tính của mình.
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. Điều này mở ra khả năng tự động hóa và tùy chỉnh Excel theo cách riêng của bạn.
Việc thiết lập một hàm tùy chỉnh khá đơn giản. Bạn chỉ cần mở Name Manager (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 của bạn sẽ sẵn sàng hoạt động. Lần tới khi 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 để khám phá
Những hàm được đề cập ở trên chỉ là một phần nhỏ trong số vô vàn khả năng mà Excel mang lại. Bạn có thể dành nhiều năm để tìm hiểu tất cả chúng, nhưng điều quan trọng là hãy nắm vững một vài hàm được sử dụng phổ biến và mạnh mẽ trước tiên. Có vô số cách sáng tạo để sửa đổi và kết hợp các hàm khác nhau, chẳng hạn như với EXACT, TRIM, CONCATENATE, và nhiều hàm khác nữa. Nếu một hàm tạo ra đầu ra, rất có thể bạn có thể sử dụng đầu ra đó làm đầu vào cho một hàm khác để đạt được những kết quả phức tạp hơn. Việc sử dụng Excel một cách sáng tạo là một trải nghiệm bổ ích, và đôi khi, còn khá thú vị.
Hãy bắt đầu áp dụng ngay hôm nay để tối ưu hóa công việc của bạn! Chia sẻ những khám phá về các hàm Excel hữu ích của bạn tại 360congnghe.com!