Hàm IF nhiều điều kiện trong Excel được viết theo hai cách: ghép IF với AND, OR khi một kết quả phụ thuộc nhiều điều kiện cùng lúc, và lồng nhiều IF hoặc dùng IFS khi cần chia dữ liệu thành nhiều nhóm. Chìa khoá để viết đúng là thứ tự điều kiện: Excel đọc từ trái sang phải và dừng ở điều kiện đúng đầu tiên, nên điều kiện hẹp phải đứng trước điều kiện rộng.
Bài viết đi từ hàm IF cơ bản, cách kết hợp AND và OR, đến IF lồng và IFS, qua ví dụ giả định phân nhóm công nợ theo số ngày quá hạn. Phần sau chỉ ra khi nào công thức IF đã quá dài và nên chuyển sang bảng tra, cách nhờ AI viết IF mà không bỏ sót nhánh, và các lỗi kiểu dữ liệu khiến điều kiện tưởng đúng mà Excel lại cho là sai.
Hàm IF hoạt động thế nào
Cú pháp IF(điều kiện, giá trị nếu đúng, giá trị nếu sai). Điều kiện là một phép so sánh cho ra TRUE hoặc FALSE, ví dụ D2>=10000000 hay E2="Đã giao". Hai giá trị phía sau có thể là số, chữ trong ngoặc kép, một phép tính, hoặc một công thức khác, kể cả một IF khác. Ví dụ =IF(E2="Đã giao", G2, 0) nghĩa là đơn đã giao thì tính tiền, chưa giao thì ghi 0.
Hai chi tiết nhỏ hay làm người mới mất thời gian. Thứ nhất, nếu bỏ trống giá trị nếu sai, IF trả về chữ FALSE khi điều kiện sai, trông như lỗi trong báo cáo. Hãy luôn ghi đủ cả hai nhánh, kể cả khi nhánh sai chỉ là chuỗi rỗng "". Thứ hai, so sánh chữ trong IF không phân biệt hoa thường nhưng phân biệt khoảng trắng và dấu: "Đã giao " có dấu cách ở cuối khác với "Đã giao", và "Đã giao" gõ bằng bảng mã dựng sẵn có thể khác với bản gõ bằng bảng mã tổ hợp dù nhìn y hệt.
IF cũng là nơi nhiều người nhầm giữa "trả về giá trị" và "làm một việc". IF chỉ trả về một giá trị cho ô chứa nó. IF không tô màu ô, không xoá dòng, không gửi thông báo. Muốn tô màu theo điều kiện, bạn dùng định dạng có điều kiện với cùng biểu thức; muốn lọc, bạn dùng bộ lọc hoặc FILTER. Khi xin AI giúp, mô tả rõ bạn muốn nhận một giá trị trong ô hay muốn một hành động, để nó không vòng vo đề xuất macro.
Kết hợp IF với AND, OR, NOT
AND khi mọi điều kiện phải đúng cùng lúc. Ví dụ giả định quy định thưởng: nhân viên đạt doanh số từ 50 triệu và không có đơn bị khách trả lại thì được thưởng. Công thức =IF(AND(D2>=50000000, F2=0), "Thưởng", "Không"). AND nhận nhiều điều kiện, chỉ trả TRUE khi tất cả cùng đúng.
OR khi chỉ cần một điều kiện đúng. Ví dụ miễn phí giao hàng khi đơn từ 500 nghìn hoặc khách thuộc nhóm thân thiết: =IF(OR(G2>=500000, H2="Thân thiết"), 0, 30000). Con số phí trong ví dụ là giả định; trong bảng thật nên đặt các ngưỡng vào ô riêng để sửa một chỗ.
NOT để đảo điều kiện. NOT(E2="Huỷ") tương đương E2<>"Huỷ". Dùng khi điều kiện đảo ngược dễ diễn đạt hơn, hoặc khi đảo cả một cụm AND, OR. Với điều kiện đơn, viết thẳng dấu khác cho dễ đọc.
Kết hợp AND trong OR. Quy định thực tế hay có dạng: đơn được duyệt nhanh nếu khách cũ và đơn dưới 5 triệu, hoặc nếu có xác nhận của trưởng nhóm. Công thức =IF(OR(AND(B2="Cũ", G2<5000000), I2="Đã duyệt"), "Duyệt nhanh", "Chờ"). Khi lồng như vậy, hãy viết quy định ra lời trước, đánh dấu chữ "và", "hoặc", rồi mới chuyển sang công thức.
Đặt ngưỡng ra ô riêng. Thay vì ghi cứng 50000000 hay 500000, đặt chúng vào một vùng tham số có nhãn rõ ràng và tham chiếu tuyệt đối như $L$2. Khi công ty đổi chính sách, bạn sửa một ô thay vì tìm và sửa trong hàng nghìn công thức.
Ví dụ giả định: phân nhóm công nợ theo số ngày quá hạn
Kế toán một công ty phân phối linh kiện ở Bắc Ninh có bảng công nợ: cột C là ngày đến hạn thanh toán (ngày thật), cột F là số tiền còn nợ. Cần cột G ghi nhóm: "Chưa đến hạn", "Quá hạn 1–30 ngày", "Quá hạn 31–60 ngày", "Quá hạn trên 60 ngày", và "Đã thu đủ" nếu còn nợ bằng 0.
- Thêm cột phụ E tính số ngày quá hạn: =TODAY()-C2. Số âm là chưa đến hạn. Cột phụ giúp bạn nhìn và kiểm từng dòng, công thức nhóm cũng ngắn hơn.
- Viết quy tắc ra lời theo thứ tự ưu tiên: nợ bằng 0 thì đã thu đủ, bất kể ngày; nếu không, ngày quá hạn nhỏ hơn hoặc bằng 0 là chưa đến hạn; đến 30 là nhóm một; đến 60 là nhóm hai; còn lại là trên 60.
- Với Excel 2019 trở về sau hoặc Microsoft 365, dùng IFS: =IFS(F2=0,"Đã thu đủ",E2<=0,"Chưa đến hạn",E2<=30,"Quá hạn 1–30 ngày",E2<=60,"Quá hạn 31–60 ngày",TRUE,"Quá hạn trên 60 ngày"). Điều kiện TRUE cuối cùng đóng vai nhánh "còn lại".
- Với Excel 2016, lồng IF theo đúng thứ tự đó: =IF(F2=0,"Đã thu đủ",IF(E2<=0,"Chưa đến hạn",IF(E2<=30,"Quá hạn 1–30 ngày",IF(E2<=60,"Quá hạn 31–60 ngày","Quá hạn trên 60 ngày")))).
- Kiểm từng mốc biên: tạo vài dòng thử với số ngày quá hạn bằng 0, 1, 30, 31, 60, 61 và một dòng nợ bằng 0 nhưng quá hạn lâu. Mỗi dòng phải rơi đúng nhóm.
Thứ tự trong ví dụ là cốt lõi. Nếu đặt E2<=60 trước E2<=30, mọi khoản quá hạn 10 ngày cũng rơi vào nhóm 31–60, vì điều kiện rộng đúng trước và Excel dừng ngay ở đó. Đây là lỗi hay gặp nhất của IF nhiều tầng, và cũng là lỗi AI thỉnh thoảng mắc khi bạn liệt kê quy tắc không theo thứ tự.
Lưu ý TODAY() thay đổi mỗi ngày, nên nhóm công nợ cũng đổi theo. Nếu cần chốt báo cáo tại một ngày cố định, hãy thay TODAY() bằng một ô chứa ngày chốt sổ.
Khi nào bỏ IF lồng, chuyển sang bảng tra
IF lồng bốn năm tầng vẫn quản lý được. Khi số nhóm tăng lên hoặc ngưỡng thay đổi thường xuyên, một bảng tra nhỏ kèm hàm dò tìm sẽ dễ đọc và dễ sửa hơn nhiều. Bảng dưới so sánh ba cách cho cùng một bài toán phân nhóm theo ngưỡng.
| Tiêu chí | IF lồng | IFS | Bảng tra kèm XLOOKUP hoặc INDEX MATCH |
|---|---|---|---|
| Phiên bản | Mọi phiên bản | Excel 2019 trở về sau, Microsoft 365 | Tuỳ hàm dò; INDEX MATCH chạy mọi bản |
| Thêm một nhóm mới | Sửa công thức, đếm lại ngoặc | Thêm một cặp điều kiện | Thêm một dòng vào bảng tra |
| Đổi ngưỡng | Tìm số trong công thức | Tìm số trong công thức | Sửa một ô, mọi người nhìn thấy |
| Người mới đọc hiểu | Khó khi quá ba tầng | Dễ hơn IF lồng | Dễ nhất, quy tắc nằm trong bảng |
| Rủi ro sai thứ tự | Cao | Cao | Thấp, chỉ cần bảng sắp xếp tăng dần |
Với ví dụ công nợ, bảng tra gồm hai cột: mức ngày tối thiểu (âm vô cùng thay bằng một số âm lớn, 1, 31, 61) và tên nhóm. Công thức dò kiểu "lấy mức lớn nhất không vượt quá" sẽ trả đúng nhóm. Riêng trường hợp đã thu đủ vẫn giữ một IF bên ngoài, vì nó không phụ thuộc số ngày.
Quy tắc nằm trong bảng còn có lợi ích ít người nghĩ tới: trưởng phòng không biết Excel vẫn đọc được và duyệt được quy định, thay vì phải tin vào một công thức dài họ không kiểm được.
Nhờ AI viết hàm IF mà không bỏ sót nhánh
IF là loại công thức AI viết rất nhanh, nhưng chất lượng phụ thuộc gần như hoàn toàn vào cách bạn mô tả quy tắc. Một số cách làm giúp kết quả đúng ngay:
- Viết quy tắc thành danh sách có thứ tự, mỗi dòng một điều kiện và một kết quả, nói rõ "kiểm theo đúng thứ tự này".
- Nói rõ mốc biên thuộc nhóm nào: đúng 30 ngày thuộc nhóm 1–30 hay 31–60. Chữ "dưới 30" và "đến 30" khác nhau một dấu bằng.
- Nêu nhánh còn lại: những dòng không thoả điều kiện nào thì ghi gì. Không nói, AI có thể bỏ trống và ô sẽ hiện FALSE.
- Nói phiên bản Excel để AI chọn giữa IFS và IF lồng.
- Yêu cầu AI tạo kèm một bảng dữ liệu thử nhỏ, mỗi dòng rơi vào một nhánh và có các mốc biên, cùng kết quả mong đợi. Bạn dán bảng thử vào trang tính trống và so.
- Dữ liệu thật như tên khách nợ, số tiền nợ của từng khách không cần đưa vào câu hỏi; quy tắc và vài số giả định là đủ.
Câu lệnh mẫu: "Máy dùng Excel 2016, dấu chấm phẩy. Cột E là số ngày quá hạn (số nguyên, có thể âm), cột F là số tiền còn nợ. Hãy viết công thức cho G2 trả về tên nhóm theo quy tắc sau, kiểm theo đúng thứ tự: [danh sách]. Kèm tám dòng dữ liệu thử có các mốc biên và kết quả mong đợi."
Những lỗi kiểu dữ liệu làm điều kiện IF sai lặng lẽ
Lỗi phổ biến nhất là so sánh số với số đang lưu dạng chữ. Ô D2 chứa "50000000" dạng chữ, điều kiện D2>=10000000 cho kết quả có thể khác hẳn mong đợi, vì Excel so sánh chữ với số theo quy tắc riêng chứ không theo giá trị. Dữ liệu dán từ phần mềm kế toán, từ tệp CSV hay từ tin nhắn hay mắc lỗi này. Dùng ISNUMBER để kiểm, và chuyển đổi trước khi viết điều kiện.
Lỗi thứ hai là ô trống. Excel coi ô trống bằng 0 trong phép so sánh số, nên một dòng chưa nhập doanh số có thể bị xếp vào nhóm "dưới ngưỡng" như thể nhân viên bán được 0 đồng. Nếu bảng còn dòng chưa nhập, thêm nhánh đầu tiên kiểm ô trống bằng D2="" và trả về chuỗi rỗng hoặc "Chưa nhập".
Lỗi thứ ba là ngày tháng dạng chữ. Phép trừ TODAY() cho một ô ngày dạng chữ có khi cho #VALUE!, có khi lại ra một con số nếu Excel tự đoán được ngày, nhưng đoán theo kiểu tháng trước ngày sau. Ngày 05/03 có thể bị hiểu thành mùng ba tháng năm. Nếu phép tính quá hạn cho ra những con số khó tin, hãy kiểm cột ngày trước khi nghi ngờ công thức. Chuẩn hoá ngày tháng có hướng dẫn chi tiết ở danh mục làm sạch dữ liệu.
Câu hỏi thường gặp
Excel cho lồng tối đa bao nhiêu hàm IF?
Từ Excel 2007 trở đi, bạn có thể lồng tới 64 hàm IF trong một công thức. Tuy nhiên giới hạn kỹ thuật không phải giới hạn hợp lý. Khi đã quá bốn năm tầng, công thức trở nên rất khó đọc và khó sửa; lúc đó IFS, SWITCH hoặc một bảng tra nhỏ là lựa chọn tốt hơn.
IFS và SWITCH khác nhau thế nào?
IFS kiểm lần lượt nhiều điều kiện bất kỳ và trả kết quả của điều kiện đúng đầu tiên. SWITCH so một giá trị với danh sách giá trị cố định, hợp khi chuyển mã thành tên, ví dụ mã kho ra tên kho. SWITCH không làm được so sánh lớn hơn nhỏ hơn một cách tự nhiên như IFS.
Vì sao IFS báo lỗi #N/A?
IFS trả #N/A khi không có điều kiện nào đúng. Cách xử lý là thêm cặp cuối cùng với điều kiện TRUE và kết quả mặc định, đóng vai nhánh còn lại. Nếu bạn nghĩ mọi dòng đều phải thoả một điều kiện mà vẫn gặp #N/A, hãy xem dòng đó có ô trống hay dữ liệu dạng chữ không.
Có nên dùng IF để kiểm tra dữ liệu nhập vào không?
IF có thể đánh dấu dòng sai, nhưng để ngăn nhập sai ngay từ đầu, Data Validation phù hợp hơn: giới hạn ô chỉ nhận số trong khoảng, ngày hợp lệ hay giá trị từ danh sách. Kết hợp cả hai thường hiệu quả: chặn lúc nhập và dùng IF để soát lại dữ liệu cũ.
Dùng IF với ký tự đại diện như dấu sao được không?
Không trực tiếp. Phép so sánh bằng trong IF không hiểu dấu sao là ký tự đại diện. Muốn kiểm một ô có chứa đoạn chữ nào đó, hãy kết hợp IF với ISNUMBER và SEARCH, hoặc dùng COUNTIF trên chính ô đó với điều kiện có dấu sao, rồi so kết quả lớn hơn 0.