Ngừng viết các công thức IF lồng nhau và IFS trong Excel: Hãy dùng SWITCH thay thế

Các công thức Excel lộn xộn không chỉ khó nhìn mà còn khó duy trì. Mỗi tham chiếu ô lặp đi lặp lại và dấu ngoặc rối rắm khiến công việc của bạn khó kiểm tra, chỉnh sửa và chia sẻ. Đó là lý do bạn nên sử dụng hàm SWITCH. Nó biến sự lộn xộn thành một công thức sạch sẽ, nhanh hơn và dễ hiểu hơn.

Hàm IF lồng nhau: Cơn ác mộng về cấu trúc

Công thức IF lồng nhau giống như búp bê Nga trong Microsoft Excel. Bạn bắt đầu với một câu lệnh IF đơn giản, nhưng sau đó bạn muốn thêm một điều kiện thứ hai. Rồi lại thêm điều kiện thứ ba. Đột nhiên, bạn đã đi sâu nhiều cấp độ—một hiện tượng được gọi là lồng nhau.

Giả sử bạn đang sử dụng bảng Excel này để theo dõi tiến độ dự án.

Công thức bạn nhập để xác định trạng thái dựa trên các giai đoạn đã hoàn thành trông như sau:

=IF([@Completed]=0,"Pending",IF([@Completed]=1,"Active",IF([@Completed]=2,"Active",IF([@Completed]=3,"Closed","Error"))))

Điều này gây ra vấn đề vì hai lý do chính:

• Mệt mỏi với dấu ngoặc: Với mỗi lệnh IF bạn mở, bạn cần đóng nó ở cuối cùng. Trong các bảng tính phức tạp, bạn thường thấy mình nhìn chằm chằm vào một chuỗi )))), tự hỏi liệu bạn có bỏ sót dấu nào không.
• Độ phức tạp về hình ảnh: Để hiểu chuyện gì xảy ra khi cột Completed hiển thị số 3, não của bạn phải bỏ qua hai câu lệnh IF khác trước.

Khi bạn sử dụng các hàm IF lồng nhau, bạn đang xây dựng một tháp logic dễ vỡ. Chỉ một dấu phẩy sai, cả công trình có thể đổ sập.

Hàm IFS: Cơn ác mộng về sự dư thừa

Khi Microsoft giới thiệu hàm IFS trong Excel, mục tiêu của nó là giải quyết cơn ác mộng về dấu ngoặc mà tôi đã đề cập ở trên. Nó đã thành công trong việc làm phẳng cấu trúc, cho phép bạn liệt kê các điều kiện lần lượt mà không cần lồng chúng.

Sử dụng hàm này trong cùng một bảng, bạn sẽ nhập:

=IFS([@Completed]=0,"Pending",[@Completed]=1,"Active",[@Completed]=2,"Active",[@Completed]=3,"Closed")

Mặc dù IFS là một cải tiến so với việc lồng nhau, nó lại tạo ra những vấn đề mới:

• Dư thừa: Hãy chú ý xem bạn phải gõ (hoặc nhấp) tên cột được tham chiếu trong công thức bao nhiêu lần, một lần cho mỗi phép thử logic. Điều này tạo ra các công thức rối mắt, dài gấp đôi so với cần thiết và khó duy trì hơn.
• Không có đối số "else" mặc định: Khác với IF gốc, IFS không có đối số cuối cùng kiểu "if all else fails". Kết quả là, nếu dữ liệu của bạn không khớp với bất kỳ tiêu chí nào, công thức sẽ trả về lỗi #N/A. Để khắc phục, bạn có thể thêm một câu lệnh TRUE vào cuối, nhưng điều này chỉ làm công thức trở nên rườm rà hơn.

Tóm lại, IFS giải quyết được cơn ác mộng dấu ngoặc, nhưng nó không làm gọn sự lộn xộn.

Giải pháp: CHUYỂN

Hàm SWITCH cho phép bạn xác định các quy tắc của mình trong một danh sách gọn gàng, làm cho công thức của bạn ít giống một phép tính phức tạp và giống một phép dịch đơn giản hơn.

Cú pháp SWITCH

Hàm SWITCH của Excel có cú pháp logic:

=SWITCH(target,value1,result1,[value2,result2],...,[default])

ở đâu :

• target là giá trị hoặc tham chiếu ô sẽ được so sánh với value1, value2, value3, và tiếp tục.
• value1 là giá trị đầu tiên sẽ được so sánh với mục tiêu.
• result1 là giá trị sẽ được trả về khi value1 khớp với target.
• value2,result2 là cặp giá trị-kết quả thứ hai trong tổng số tối đa 126 cặp.
• default là đối số duy nhất sau cặp giá trị-kết quả cuối cùng của bạn, đại diện cho giá trị "else" (biện pháp an toàn) để trả về nếu không tìm thấy kết quả phù hợp trong các đối số value.

SWITCH đang hoạt động

Đây là công thức bạn có thể sử dụng trong cột Trạng thái của bảng theo dõi tiến độ:

=SWITCH([@Completed],0,"Pending",1,"Active",2,"Active",3,"Closed","Unknown")

Đây là lý do tại sao đây là lựa chọn tốt hơn trong bối cảnh này:

• Bạn chỉ tham chiếu một lần: Bạn chỉ tham chiếu cột Hoàn thành một lần. Trong một bảng tính lớn, việc giảm các tham chiếu nhiều lần xuống chỉ còn một lần sẽ làm cho công thức của bạn gọn gàng hơn nhiều, khó bị hỏng hơn và dễ thay đổi hơn.
• Dễ đọc: Vì không có bất kỳ tham chiếu nào bị lặp lại, bạn có thể đọc nó bằng tiếng Anh đơn giản: "Nhìn vào cột Hoàn thành; nếu là 0, Đang chờ; nếu là 1, Đang hoạt động..." và tiếp tục như vậy.
• Lưới an toàn tích hợp: Nếu ai đó nhập một con số vào cột Completed mà không nên có, SWITCH sẽ phát hiện sai sót bằng một đối số “else” ở cuối (“Unknown”).

Bằng cách sử dụng SWITCH, bạn sẽ ngừng đấu tranh với các cú pháp phức tạp và bắt đầu viết các công thức thực sự có ý nghĩa.

Mẹo SWITCH(TRUE): Chiêu thức của người dùng cao cấp

Một lời chỉ trích phổ biến về hàm SWITCH của Excel là nó chỉ xử lý các kết quả khớp chính xác, ví dụ như chuyển số 1 thành từ "Active." Nếu bạn muốn sử dụng các ký hiệu lớn hơn hoặc nhỏ hơn, hầu hết mọi người đều cho rằng họ phải quay lại sử dụng logic phức tạp của IF hoặc IFS.

Tuy nhiên, bạn thực sự có thể sử dụng SWITCH cho các khoảng giá trị bằng cách sử dụng một mẹo thông minh: bắt đầu công thức với TRUE:

=SWITCH(TRUE,[@Completed]=0,"Pending",[@Completed]<=2,"Active",[@Completed]=3,"Closed","Error")

Bằng cách đặt đối số đầu tiên là TRUE, bạn nói với Excel tiếp tục tìm trong công thức cho đến khi bạn tìm thấy một biểu thức đúng. Điều này có nghĩa là bạn có thể bao quát cả 1 và 2 cùng một lúc, trả về "Active" trong một đối số.

Đăng ký nhận bản tin để có công thức Excel gọn gàng hơn

Đăng ký nhận bản tin để có công thức Excel gọn gàng hơn

Bằng việc đăng ký, bạn đồng ý nhận bản tin và email tiếp thị, đồng thời chấp nhận Điều khoản Sử dụng và Chính sách Bảo mật của Valnet. Bạn có thể hủy đăng ký bất cứ lúc nào.

Một lý do khác khiến điều này thay đổi cuộc chơi là bạn nhận được sự linh hoạt về mặt toán học của câu lệnh IF trong khi vẫn giữ được cấu trúc danh sách tuyến tính gọn gàng của SWITCH.

Sự thỏa hiệp 'một lần điểm'

Đây là tuyên bố miễn trừ trách nhiệm: việc sử dụng thủ thuật TRUE phần nào làm suy yếu quy tắc "chỉ tính một lần" mà tôi đã nói trước đó. Bởi vì bạn đang sử dụng các toán tử toán học (< hoặc >), bạn phải lặp lại tham chiếu cho mỗi kiểm tra logic, giống như cách bạn làm trong công thức IFS.

Tuy nhiên, việc uốn nắn quy tắc này ở đây cũng không sao vì bạn đang đánh đổi sự lặp lại tối thiểu để có sự tổ chức tối đa. Ngay cả khi có các tham chiếu lặp lại, cấu trúc của SWITCH vẫn giữ logic của bạn trong một danh sách tuyến tính nghiêm ngặt. Điều này có nghĩa là bạn không phải mò tìm dấu ngoặc đóng hoặc tự hỏi mình đang nằm trong khối lồng nào.

Ngoài ra, bạn vẫn nhận được lợi ích từ đối số “else” cuối cùng đó—một mạng lưới an toàn mà IFS không cung cấp.

Chọn chức năng phù hợp

Mặc dù SWITCH rất hữu ích để sắp xếp logic của bạn, nhưng nó không phải là một cây đũa thần cho mọi tình huống. Có những lúc các công thức lồng nhau hoặc các câu lệnh IFS phức tạp thực sự là lựa chọn hợp lý hơn:

Nếu bạn thấy mình đang sử dụng cùng một phép tính phức tạp nhiều lần trong một công thức, hãy sử dụng hàm LET, cho phép bạn đặt tên cho các kết quả tính toán. Bằng cách khai báo các biến của bạn ngay từ đầu công thức, bạn có thể làm cho công thức của mình rõ ràng hơn, nhanh hơn và dễ kiểm tra hơn.