Cách sử dụng các hàm IFS và LET thay câu lệnh IF lồng nhau trong Excel

Các câu lệnh IF lồng nhau từng là lựa chọn hàng đầu mỗi khi cần xử lý nhiều điều kiện trong Microsoft Excel. Tuy nhiên, việc chúng hoàn thành công việc không có nghĩa chúng là công cụ tốt nhất cho công việc. Việc vật lộn với vô số dấu ngoặc đơn và cố gắng theo dõi IF nào kiểm soát kết quả nào cũng tẻ nhạt như việc tự mình kiểm tra các con số. Và nếu thử xem lại công thức một tuần sau, chắc hẳn bạn sẽ phải vật lộn nhớ lại lý do tại sao mình lại thiết lập nó theo cách đó.

Đó là lý do tại sao bạn nên ngừng xếp chồng các hàm IF như xếp khối gỗ Jenga để bắt đầu sử dụng các hàm IFS và LET trong Excel. Kết hợp cả hai, và những gì từng trông giống như dòng code phức tạp sẽ trở thành một tập hợp các hướng dẫn rõ ràng mà bạn thực sự có thể duy trì, chia sẻ và giải thích mà không gặp khó khăn.

Các hàm IF lồng nhau rất tệ, nhưng hàm IFS thì tốt hơn

Khi bạn chỉ cần hai kết quả, hàm IF sẽ đủ. Nhưng ngay khi bạn bắt đầu xếp chồng các điều kiện (nếu thế này, thì thế kia, nếu không thì thế này, thì thế kia), mọi thứ sẽ trở thành một mớ hỗn độn. Ban đầu, nó hoạt động. Nhưng nếu đã dành thời gian làm việc với các bảng tính kinh doanh thực tế, bạn sẽ biết nó nhanh chóng trở nên mơ hồ như thế nào:

=IF(B2>100, "Excellent", IF(B2>80, "Good", IF(B2>60, "Average", IF(B2>40, "Poor", IF(B2>20, "Very Poor", IF(B2>=0, "Terrible"))))))

Về mặt kỹ thuật, Microsoft cho phép bạn lồng tối đa 64 hàm IF. Nhưng nếu gần đến giới hạn đó, bạn không biết làm thế nào để giữ được sự tỉnh táo, vì những điểm khó khăn tăng lên rất nhanh.

Trước tiên, hãy thử xem lại công thức IF lồng nhau sâu 6 tháng sau và hiểu rõ ý nghĩa của nó. Chúc bạn may mắn khi tìm đúng vị trí để thêm điều kiện khác mà không vô tình phá vỡ toàn bộ chuỗi. Nếu sau đó vô tình bỏ sót một dấu ngoặc đơn, bạn sẽ mất cả tiếng đồng hồ để tìm ra lỗi.

May mắn thay, Microsoft đã giới thiệu hàm IFS để giải quyết nhiều vấn đề này. Có sẵn trong các gói đăng ký Office 2019 và Microsoft 365, hàm IFS có thể thay thế nhiều câu lệnh IF lồng nhau trong Excel và dễ đọc hơn nhiều. Dưới đây là ví dụ về công thức tính điểm khi sử dụng IFS:

=IFS(B2>100, "Excellent", B2>80, "Good", B2>60, "Average", B2>40, "Poor", B2>20, "Very Poor", B2>=0, "Terrible")

Hàm IFS hoạt động bằng cách kiểm tra các điều kiện theo thứ tự: test1, value-if-true; test2, value-if-true; và cứ thế, với số lượng bài kiểm tra tùy ý (tối đa 127 bài). Nói một cách đơn giản, bạn đang muốn nói: "Nếu điểm trên 100, thì thuộc nhóm Excellent; nếu trên 80, thì thuộc nhóm Good; nếu trên 60, thì thuộc nhóm Average...".

IFS là một bước tiến lớn so với các hàm IF lồng nhau, nhưng nó không hoàn hảo. Bạn vẫn phải lặp lại bất kỳ phép tính phức tạp nào bên trong mỗi điều kiện. Đó là lúc LET xuất hiện, và nó biến IFS từ tốt thành tuyệt vời.

Hàm LET là cách viết logic phức tạp gọn gàng hơn

Bí quyết để viết công thức Excel mà bạn thực sự có thể duy trì

Với hàm LET, bạn có thể đặt tên cho một phép tính phức tạp, thực hiện một lần, rồi tham chiếu tên đó trong toàn bộ công thức. Điều này giúp logic của bạn hiệu quả và dễ theo dõi hơn nhiều. Sau đây là cách LET có thể đơn giản hóa công thức tính điểm hơn nữa với hàm IFS:

=LET(score,B2,IFS(score>100,"Excellent",score>80,"Good",score>60,"Average",score>40,"Poor",score>20,"Very Poor",score>=0, "Terrible"))

Sự khác biệt rất rõ ràng, vì bạn có thể biết ngay ý nghĩa của score mà không cần phải lục tung các tham chiếu ô. Mặc dù đây là một trường hợp đơn giản, nhưng nó mới chỉ là một phần nhỏ những gì LET có thể làm.

Hàm LET hỗ trợ tối đa 126 biến, nghĩa là bạn có thể chia nhỏ ngay cả logic phức tạp nhất thành các phần dễ quản lý hơn. Nó cũng cải thiện hiệu suất. Thông thường, nếu bạn lặp lại cùng một biểu thức nhiều lần, Excel sẽ tính toán lại mỗi lần. Với LET, bạn chỉ cần tính toán một lần, đặt tên và sử dụng lại khi cần.

Đây là một ví dụ phức tạp hơn cho thấy sức mạnh thực sự của việc kết hợp LET và IFS. Hãy tưởng tượng bạn đang tính toán tỷ lệ hoa hồng dựa trên hiệu suất bán hàng, khu vực và thời gian làm việc:

=LET(sales,B2, territory,C2, tenure, D2, base_rate,0.05, sales_bonus,IFS(sales>50000,0.02,sales>25000,0.01,sales>=0, 0), territory_bonus,IFS(territory="Premium",0.015,territory="Standard",0.01,TRUE,0.005), tenure_bonus,IFS(tenure>5,0.01,tenure>2,0.005,TRUE,0), total_rate,base_rate+sales_bonus+territory_bonus+tenure_bonus, total_rate*sales)

Công thức này tính tổng hoa hồng của một nhân viên bằng cách kết hợp tỷ lệ cơ bản, tiền thưởng bán hàng, tiền thưởng khu vực và tiền thưởng thời gian làm việc.

Cú pháp chung của LET là LET(name1, value1, name2, value2, …, calculation), và bạn có thể thấy cấu trúc đó được áp dụng trong ví dụ này. Mỗi biến được định nghĩa ở trên cùng - doanh số, lãnh thổ, thời gian công tác, mức lương cơ bản và 3 khoản thưởng - trước khi được kết hợp thành tổng mức lương cuối cùng. Mỗi phần của công thức đều được gắn nhãn và dễ theo dõi, thay vì bị chôn vùi trong một mớ logic lặp đi lặp lại.

Một công thức như thế này sẽ cực kỳ khó viết và bảo trì với các hàm IF lồng nhau. Với LET và IFS, mọi thành phần đều được định nghĩa rõ ràng và dễ điều chỉnh. Nếu cần thay đổi phần thưởng lãnh thổ cao cấp, bạn chỉ cần cập nhật một dòng. Nếu không có LET, bạn sẽ phải tìm kiếm trong toàn bộ công thức, tìm và sửa từng trường hợp theo cách thủ công.

Cuối cùng, bạn sẽ có một công thức dễ bảo trì và ít có khả năng bị hỏng hơn khi bạn xem lại nó sau nhiều tháng.