說明
Use this function to trap and handle errors in a formula.
語法
IFERROR(value, value_if_error)
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
值 |
是 | The value or expression to check for an error. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
value_if_error |
是 | The value to return if the formula evaluates to an error. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
範例
範例資料
| A | B |
|---|---|
| 100 | 20 |
| 50 | 0 |
| 30 | 5 |
| Four | 10 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=IFERROR(A1/B1, "Cannot divide") |
Divides A1 by B1. If there's an error, returns "Cannot divide". | 5 |
=IFERROR(A2/B2, 0) |
Attempts to divide A2 by B2, and returns a "0" if there is an error. Because B2 is 0, this triggers a #DIV/0! error. | 0 |
=IFERROR(A4*B4, "Invalid calculation") |
Tries to multiply A4 (text) by B4, and returns "Invalid calculation" if an error is triggered. Because A4 is text, a #VALUE! error results. | Invalid calculation |
=IFERROR(VLOOKUP("Banana", A1:B4, 2, FALSE),"Not found") |
Tries to find "Banana" in the range A1:B4, and returns "Not found" if there is an error. As there is no banana, a #N/A error results. | Not found |
附註
- IFERROR returns a specified value if a formula evaluates to an error; otherwise, it returns the result of the formula.
- IFERROR catches all error classes (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!). It doesn't distinguish between error classes.
- IFERROR is often used with functions such as VLOOKUP, HLOOKUP, and INDEX/MATCH to handle missing data scenarios.
- IFERROR can be nested within other functions or used as part of a larger formula.
提示
- Use IFERROR to clean up spreadsheets by replacing error messages with more user-friendly text or blank cells.
- Be cautious when using IFERROR, as it can mask legitimate errors that you might want to investigate.
- Consider using the more specific IFNA function if you only need to catch #N/A errors.
- For more complex error handling, you can nest IF statements within IFERROR.
- When troubleshooting, temporarily remove IFERROR to see the actual errors your formulas are producing.