=IFERROR(B2/C2,0) trả về kết quả của B2/C2, hoặc 0 khi kết quả đó là lỗi. Đối số đầu tiên là công thức bạn cần; đối số thứ hai là nội dung hiện ra thay cho bất kỳ lỗi nào công thức đó gây ra.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | Units | Plain | With IFERROR |
| 2 | Pens | $120 | 80 | $1.50 | $1.50 |
| 3 | Paper | $300 | 50 | $6.00 | $6.00 |
| 4 | Ink | $90 | 0 | #DIV/0! | $0.00 |
| 5 | Tape | $45 | 30 | $1.50 | $1.50 |
| 6 | Clips | $0 | 0 | #DIV/0! | $0.00 |
Ink và Clips có 0 đơn vị, nên phép chia thông thường ở cột D báo #DIV/0!. Cột E hiện $0,00 cho hai mặt hàng này và giá bình thường cho mọi hàng khác. Nhập 15 vào C4 và cả hai cột đều hiện giá của Ink.
Cú pháp hàm IFERROR
=IFERROR(value, value_if_error)
valuelà công thức cần tính.value_if_errorđược trả về khivaluelà bất kỳ lỗi nào: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!, và các lỗi mới hơn như #CALC!.- Nếu
valuekhông phải lỗi, IFERROR trả về nguyên giá trị đó.
Giá trị thay thế có thể là một số (0), văn bản ("Not found"), văn bản rỗng ("") hoặc một công thức khác, ví dụ một phép tra cứu thứ hai trong bảng khác: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),VLOOKUP(E2,G2:I6,3,FALSE)).
IFERROR với VLOOKUP
Phép tra cứu trả về #N/A khi giá trị không có trong bảng. Bọc nó trong IFERROR để hiện một thông báo thay thế:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | Kiwi | Not found | |
| 4 | Carrot | Vegetable | $0.80 | Milk | $1.10 | |
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Kiwi không có trong danh sách, nên F3 ghi Not found. Nhập Kiwi vào A4 thay cho Carrot và F3 sẽ tìm thấy. Với XLOOKUP bạn không cần IFERROR cho việc này, vì đối số thứ tư của nó chính là giá trị khi "không tìm thấy": =XLOOKUP(E2,A2:A6,C2:C6,"Not found").
IFNA: chỉ bắt lỗi #N/A
IFNA hoạt động như IFERROR nhưng chỉ thay #N/A. Với tra cứu, đó thường là điều bạn cần: #N/A nghĩa là "không tìm thấy", một câu trả lời bình thường, còn bất kỳ lỗi nào khác nghĩa là chính công thức bị sai. Trong trang tính này, các công thức lấy cột 4 của một bảng chỉ có ba cột, một lỗi đánh máy:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | IFERROR | IFNA | |
| 2 | Apple | Fruit | 1.2 | Pear | Not found | #REF! | |
| 3 | Pear | Fruit | 1.5 | ||||
| 4 | Carrot | Vegetable | 0.8 | ||||
| 5 | Bread | Bakery | 2.4 | ||||
| 6 | Milk | Dairy | 1.1 |
Pear có trong bảng, vậy mà F2 ghi Not found: IFERROR đã biến lỗi #REF! do sai số cột thành cùng thông báo như khi thiếu sản phẩm. G2 để lỗi #REF! hiện ra, nên bạn thấy công thức bị hỏng. Đổi số 4 thành 3 trong G2 và nó trả về 1,5. IFNA cần Excel 2013 trở lên.
Trả về ô trống thay cho lỗi
Để không hiện gì, hãy dùng văn bản rỗng, tức hai dấu ngoặc kép, làm giá trị thay thế:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Last year | This year | Growth |
| 2 | Jan | 200 | 240 | 20% |
| 3 | Feb | 0 | 150 | |
| 4 | Mar | 180 | 171 | -5% |
| 5 | Apr | 90 | ||
| 6 | May | 250 | 300 | 20% |
Tháng Hai và tháng Tư năm ngoái không có doanh số, nên không tính được tăng trưởng và ô để trống. Các tháng khác hiện 20%, âm 5% và 20%. Ô chứa "" là văn bản: SUM và AVERAGE bỏ qua nó, nhưng =D3*2 cho #VALUE!. Nếu các công thức sau còn tính toán trên cột này, hãy trả về 0.
Luyện tập: tra cứu có giá trị dự phòng
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Look for | Stock | ||
| 2 | Apple | 40 | Kiwi | |||
| 3 | Pear | 25 | ||||
| 4 | Carrot | 60 | ||||
| 5 | Bread | 12 | ||||
| 6 | Milk | 30 |
Đến lượt bạn: Trong F2, tra cứu tồn kho của sản phẩm ở E2 từ A2:B6, và hiện "Not found" khi sản phẩm không có trong danh sách.
Vì sao giấu mọi lỗi có thể che mất sai sót
IFERROR không sửa được gì; nó chỉ quyết định ô sẽ hiện gì. Trước khi bọc một công thức trong IFERROR:
- Tìm hiểu vì sao lỗi xảy ra. Khi một ô Units trống gây ra #DIV/0!, cách sửa đúng có thể là dữ liệu cần ai đó nhập vào, không phải một mức giá bằng 0.
- Ưu tiên IFNA cho tra cứu, để số cột sai (#REF!), tên gõ sai (#NAME?) hay văn bản trong cột số (#VALUE!) vẫn hiện ra.
- Kiểm tra đúng trường hợp cụ thể với phép chia.
=IF(C2=0,0,B2/C2)chỉ xử lý số chia bằng 0; một tham chiếu sai ở B2 vẫn báo lỗi. Trang về #DIV/0! so sánh hai cách làm. - Chọn giá trị thay thế không thể nhầm với dữ liệu. Số 0 trong cột giá trông như một mức giá thật và kéo giá trị trung bình xuống;
""hay "Not found" thì không.
Hãy bọc công thức sau cùng, khi nó đã cho kết quả đúng ở những hàng lẽ ra phải chạy đúng.
Câu hỏi thường gặp
Làm sao dùng IFERROR với VLOOKUP?
Bọc phép tra cứu lại: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). Khi E2 không có ở cột đầu tiên, ô hiện Not found thay cho #N/A. =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found") làm điều tương tự nhưng vẫn hiện các lỗi khác.
Làm sao để IFERROR trả về ô trống?
Dùng văn bản rỗng làm đối số thứ hai: =IFERROR(B2/C2,""). Ô trông như trống nhưng chứa văn bản, nên =D2+1 trên ô đó cho #VALUE!; còn SUM và AVERAGE thì bỏ qua nó.
IFERROR và IFNA khác nhau thế nào?
IFERROR thay thế mọi lỗi: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! và #NULL!. IFNA chỉ thay #N/A, tức kết quả "không tìm thấy" của phép tra cứu, và để các lỗi khác hiện ra, nên công thức bị hỏng không bị che đi.
Làm sao thay #N/A bằng 0 trong Excel?
Bọc công thức trong IFNA với giá trị 0: =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),0). XLOOKUP có sẵn giá trị thay thế ở đối số thứ tư: =XLOOKUP(E2,A2:A6,C2:C6,0).
Phiên bản Excel nào có IFERROR và IFNA?
IFERROR có từ Excel 2007 và IFNA từ Excel 2013. Trong các tệp cũ bạn có thể gặp =IF(ISERROR(B2/C2),0,B2/C2), làm cùng việc với IFERROR nhưng tính công thức hai lần.
