Câu lệnh IF của Excel được sử dụng rộng rãi để đưa ra quyết định, nhưng chúng không phải lúc nào cũng là giải pháp hay nhất. Khi các điều kiện của bạn trở nên phức tạp hơn, các câu lệnh IF lồng nhau có thể trở nên khó đọc, duy trì và khắc phục sự cố. Để xử lý các tình huống nhiều lớp đó, Excel cung cấp một số lựa chọn thay thế mạnh mẽ có thể giúp công thức của bạn hiệu quả hơn và dễ hiểu hơn.
Trong hướng dẫn này, chúng tôi sẽ trình bày 7 hàm Excel có thể thay thế câu lệnh IF.
Hãy xem xét một tập dữ liệu bán hàng mẫu để hiển thị các so sánh công thức song song.
1. IFS() – Xử lý nhiều điều kiện một cách tinh tế
Hàm IFS đánh giá nhiều điều kiện theo trình tự và trả về giá trị tương ứng với điều kiện TRUE đầu tiên. Nó hoàn hảo để thay thế các câu lệnh IF lồng nhau. Các câu lệnh IF lồng nhau dài ra và khó gỡ lỗi. IFS đơn giản hóa logic phân tầng.
Cú pháp:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Hãy phân loại giá trị đơn đặt hàng thành xếp hạng hiệu suất:
- Tuyệt vời cho các đơn hàng trên 10.000 USD.
- Tốt cho các đơn hàng từ $5.000 đến $10.000.
- Trung bình cho các đơn hàng từ 2.000 USD đến 5.000 USD.
- Dưới mục tiêu cho bất cứ điều gì ít hơn.
Phương pháp NẾU:
=IF(J2>10000, "Excellent", IF(J2>5000, "Good", IF(J2>2000, "Average", "Below Target")))
Phương pháp tiếp cận IFS:
=IFS(J2>10000, "Excellent", J2>5000, "Good", J2>2000, "Average", TRUE, "Below Target")
Hàm IFS chiếm một cặp đối số:
- Điều kiện và giá trị trả về nếu điều kiện đó là TRUE.
- Đối số TRUE cuối cùng đóng vai trò là đối số chung cho các trường hợp không đáp ứng các điều kiện trước đó.
Lợi ích:
- Không có dấu ngoặc khó hiểu.
- Dễ đọc và bảo trì.
- Thể hiện rõ ràng mức độ ưu tiên của các điều kiện.
2. SWITCH() – Hoàn hảo cho các kết quả khớp chính xác
SWITCH hiệu quả hơn nhiều câu lệnh IF khi bạn cần so sánh một giá trị với nhiều khả năng chính xác. Nó so sánh một giá trị với nhiều kết quả khớp chính xác.
Cú pháp:
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Hãy phân công người quản lý bán hàng cho các khu vực:
- Đông:Morgan Smith
- Tây:Taylor Wong
- Miền Bắc:Jamie Rodriguez
- Miền Nam:Casey Johnson
Phương pháp NẾU:
=IF(E2="East", "Morgan Smith", IF(E2="West", "Taylor Wong", IF(E2="North", "Jamie Rodriguez", IF(E2="South", "Casey Johnson", "Unassigned"))))
Phương pháp CHUYỂN ĐỔI:
=SWITCH(E2, "East", "Morgan Smith", "West", "Taylor Wong", "North", "Jamie Rodriguez", "South", "Casey Johnson", "Unassigned")
- SWITCH đánh giá một biểu thức dựa vào danh sách các giá trị, sau đó trả về kết quả tương ứng với giá trị khớp đầu tiên.
- Đối số cuối cùng đóng vai trò là giá trị mặc định nếu không tìm thấy kết quả khớp nào.
Hãy lưu ý cách tiếp cận SWITCH rõ ràng và dễ đọc hơn nhiều so với các câu lệnh IF lồng nhau.
Lợi ích:
- Nhỏ gọn và trực quan.
- Dễ dàng mở rộng và sắp xếp lại.
- Không cần lồng nhau.
3. CHOOSE() – Chọn từ danh sách theo vị trí
Khi logic của bạn liên quan đến việc chọn từ danh sách được xác định trước dựa trên vị trí số, CHỌN sẽ đơn giản hơn IF lồng nhau.
Cú pháp:
=CHOOSE(index_num, value1, [value2], ...)
Hãy chuyển đổi số quý thành tháng đầu tiên trong quý đó:
Phương pháp NẾU:
=IF(F2=1, "January", IF(F2=2, "April", IF(F2=3, "July", IF(F2=4, "October", "Invalid Quarter"))))
CHỌN Cách tiếp cận:
=CHOOSE(F2, "January", "April", "July", "October")
- CHOOSE trả về giá trị từ danh sách dựa trên số vị trí.
- Không giống như IF, nó không đánh giá các điều kiện mà chọn một mục từ danh sách được cung cấp dựa trên giá trị chỉ mục.
Chúng ta cũng có thể sử dụng CHỌN để chỉ định mục tiêu bán hàng hàng quý dựa trên số quý:
=CHOOSE(F2, 50000, 75000, 60000, 90000)
Điều này chỉ định các mục tiêu bán hàng khác nhau theo quý.
Lợi ích:
- Ngắn gọn và rõ ràng hơn khi có hơn 3 tùy chọn.
- Ít xảy ra lỗi logic hơn.
- Dễ dàng cập nhật hoặc sắp xếp lại.
4. XLOOKUP – Quyết định dựa trên bảng
Khi đưa ra quyết định dựa trên phạm vi giá trị hoặc bảng tra cứu, hàm LOOKUP mạnh hơn hàm IF lồng nhau. Hàm XLOOKUP tìm kiếm một giá trị và trả về kết quả tương ứng từ một mảng khác.
Cú pháp:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Hãy tạo cấu trúc hoa hồng dựa trên danh mục sản phẩm:
- Điện tử:5%
- Nội thất:7%
- Khác:4%
Phương pháp NẾU:
=IF(C2="Electronics", J2*0.05, IF(C2="Furniture", J2*0.07, J2*0.04))
Phương pháp tiếp cận XLOOKUP (Excel hiện đại):
=J2*XLOOKUP(C2, $P$2:$P$4, $Q$2:$Q$4, 0.04)
Công thức này tra cứu giá trị đơn hàng dựa trên danh mục và tỷ lệ hoa hồng, sau đó tính toán hoa hồng bán hàng.
Lợi ích:
- Hoạt động tốt với các bảng dữ liệu.
- Dễ đọc hơn với danh sách dài hơn.
- Linh hoạt (có thể bao gồm dự phòng mặc định).
5. AND()/OR() – Kết hợp nhiều điều kiện
Khi phải đáp ứng nhiều điều kiện (AND) hoặc phải đáp ứng ít nhất một điều kiện (OR), các hàm này sẽ đơn giản hóa công thức của bạn. Hàm AND trả về TRUE nếu tất cả các điều kiện đều đúng. Hàm OR trả về TRUE nếu bất kỳ điều kiện nào đúng.
Cú pháp:
=AND(logical1, [logical2], ...)
=OR(logical1, [logical2], ...)
Hãy gắn cờ các đơn hàng là "Ưu tiên" nếu:
- Chúng là Điện tử đơn đặt hàng VÀ
- Giá trị đơn hàng trên $5.000 , VÀ
- Trạng thái là Đang xử lý .
Phương pháp NẾU:
=IF(C2="Electronics", IF(J2>5000, IF(K2="Processing", "Priority", "Normal"), "Normal"), "Normal")
VÀ Cách tiếp cận:
=IF(AND(C2="Electronics", J2>5000, K2="Processing"), "Priority", "Normal")
Công thức này trả về mức độ ưu tiên dựa trên danh mục và trạng thái. Cả hai điều kiện đều phải TRUE.
Hãy xác định các đơn hàng đủ điều kiện được giảm giá thêm nếu:
- Họ đến từ phương Tây vùng, HOẶC
- Đơn đặt hàng lớn hơn 10 đơn vị, HOẶC
- Mức chiết khấu hiện tại là 0% .
HOẶC Cách tiếp cận:
=IF(OR(E2="West", G2>10, I2=0), "Eligible for Discount", "Not Eligible")
Cách tiếp cận này làm cho logic rõ ràng hơn và dễ bảo trì hơn, đặc biệt khi các điều kiện ngày càng phức tạp hơn.
Lợi ích:
- Biểu thức logic rõ ràng hơn.
- Dễ sửa đổi điều kiện hơn.
- Hoạt động tốt bên trong IF, FILTER, v.v.
6. MIN()/MAX() – Đơn giản hóa các giới hạn phạm vi
Khi so sánh hai số, MIN/MAX loại bỏ sự cần thiết của các nhánh có điều kiện. Hàm MIN trả về giá trị nhỏ nhất trong phạm vi đã chọn. Hàm MAX trả về số lớn nhất trong phạm vi đã chọn.
Cú pháp:
=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)
Hãy triển khai chính sách trong đó:
- Số lượng đặt hàng tối thiểu cho bất kỳ sản phẩm nào là 5 đơn vị.
- Số lượng đặt hàng tối đa cho bất kỳ sản phẩm nào là 20 đơn vị.
- Các đơn đặt hàng ngoài những giới hạn này cần được điều chỉnh.
Phương pháp NẾU:
=IF(G2<5, 5, IF(G2>20, 20, G2))
Phương pháp tiếp cận MIN/MAX:
Công thức này đảm bảo giá trị nằm trong khoảng từ 5 đến 20 mà không cần câu lệnh IF lồng nhau.
Hãy thực hiện chính sách giảm giá trong đó:
- Phần trăm chiết khấu thông thường được áp dụng cho đơn đặt hàng.
- Mức giảm giá tối đa không được vượt quá $1.500 , bất kể tỷ lệ phần trăm.
- Giá cuối cùng không được thấp hơn $500 .
Tính giá cuối cùng sau khi áp dụng các ràng buộc này:
Phương pháp NẾU:
=IF(J2-((J2*I2)/100)<500, 500, IF((J2*I2)/100>1500, J2-1500, J2-((J2*I2)/100)))
Phương pháp tiếp cận MIN/MAX:
=MAX(500, J2-MIN(1500, J2*I2/100))
Cách tiếp cận này thân thiện với người dùng hơn và dễ hiểu hơn so với các câu lệnh IF lồng nhau.
Lợi ích:
- Súc tích và đáng tin cậy.
- Lý tưởng cho giới hạn, giá sàn và logic định giá.
7. FILTER() – Kết quả có điều kiện động (Excel 365+)
FILTER là một hàm mạnh mẽ trả về các giá trị bằng cách đáp ứng các tiêu chí đã chỉ định, thay thế hiệu quả các câu lệnh IF phức tạp kiểm tra nhiều điều kiện.
Cú pháp:
=FILTER(array, include, [if_empty])
Hãy tạo báo cáo động từ dữ liệu bán hàng của chúng tôi. Tìm tất cả các đơn đặt hàng Điện tử có giá trên 5.000 USD.
Phương pháp tiếp cận IF (Đối với một hàng):
=IF(AND(C2="Electronics", J2>5000), B2, "")
Điều này sẽ cần phải được sao chép xuống cho mỗi hàng, tạo ra kết quả bị phân mảnh.
Phương pháp LỌC:
=FILTER(B2:B13, (C2:C13="Electronics")*(J2:J13>5000), "No matching products")
Công thức này lọc các sản phẩm từ ca điện tử
Hàm FILTER có thể kết hợp nhiều điều kiện và trả về tất cả các bản ghi trùng khớp, khiến hàm này trở nên lý tưởng để tạo các báo cáo động, dựa trên điều kiện mà không cần logic IF phức tạp. Nó đặc biệt mạnh mẽ đối với các tác vụ phân tích dữ liệu mà bạn cần lọc và trình bày dữ liệu dựa trên nhiều tiêu chí.
Kết luận
Câu lệnh IF rất linh hoạt nhưng không phải lúc nào chúng cũng là giải pháp hiệu quả nhất. Có những hàm Excel khác có thể khắc phục được hạn chế của hàm IF. Bằng cách nắm vững các lựa chọn thay thế này cho câu lệnh IF, bạn có thể viết các công thức Excel rõ ràng hơn, dễ bảo trì hơn. Mỗi chức năng đều có điểm mạnh cho các tình huống cụ thể và việc chọn đúng chức năng có thể cải thiện đáng kể khả năng đọc và hiệu suất của bảng tính của bạn.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!