說明
Use this function to check multiple conditions and return a value corresponding to the first TRUE condition without nesting multiple IF statements. Can be used with CHILDREFS.
語法
IFS(logical_test1, value_if_true1, […, logical_test_127, value_if_true_127])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
logical_test_1 |
是 | The first condition to evaluate. | Any value or expression that can be evaluated to TRUE or FALSE. |
value_if_true_1 |
是 | The value to return if logical_test_1 is TRUE. |
Any value, including text, numbers, or logical values. |
logical_test_n, |
選用 | Additional pairs of conditions and results. | Same criteria as logical_test_1 and value_if_true_1. |
範例
範例資料
| A | B |
|---|---|
| Score | Grade |
| 95 | A |
| 84 | Very Good |
| 75 | Good |
| No Score | |
| 60 | 失敗 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F") |
Assigns a letter grade based on the score in A2. | A |
=IFS(A3>95,"Excellent",A3>82,"Very Good",A3>75,"Good",A3>65,"Satisfactory",TRUE,"Needs Improvement") |
Provides a descriptive evaluation based on the score in A3. | Very Good |
=IFS(A4=100,"Perfect",A4>=90,"Outstanding",A4>=80,"Great",A4>=70,"Good",A4>=60,"Pass",TRUE,"Fail") |
Gives a more detailed grading scale, including a "Perfect" score. | Good |
=IFS(ISBLANK(A5),"No Score",A5>100,"Invalid",A5>=70,"Pass",TRUE,"Fail") |
Handles blank cells and scores above 100, in addition to Pass/Fail grading. | No Score |
=IFS(ISBLANK(A6),"No Score",A6>100,"Invalid",A6>=70,"Pass",TRUE,"Fail") |
Handles blank cells and scores above 100, in addition to Pass/Fail grading. | 失敗 |
附註
- IFS evaluates conditions in order and returns the value corresponding to the first TRUE condition.
- If no conditions are TRUE and there's no final TRUE condition, IFS returns a #N/A error.
- IFS can handle up to 127 condition/value pairs.
- IFS is more efficient and readable than nested IF statements for multiple conditions.
提示
- Always include a final TRUE condition to handle cases where no other conditions are met.
- Use IFS instead of nested IF statements when you have more than two conditions to check.
- Ensure your conditions are mutually exclusive to avoid unexpected results.
- For complex logical tests, you can use the AND, OR, and NOT functions within the IFS conditions.
- When working with large datasets, consider using VLOOKUP or INDEX/MATCH for better performance.