Microsoft Excel Expert Certification 笔记 (MO201)

---

1. Advanced formulas and Macros

1.1. Reference types

---

1.2. Logical operations and functions

• Operators: =, >, <, >=, <=, <>
• In Excel, True = 1, False = 0.

• IF function: =IF(logical_test, [value_if_true], [value_if_false])
• Nested IF function: =IF(logical_test_1, [value_if_true], IF(logical_test_2, [value_if_true], [value_if_false])
• IFS function: =IFS(logical_test1, value_if_true1, [logical_test2], [value_if_true2],…)
• Nested IF function can return the same result with IFS function.
• NOT function: =NOT(logical)
• AND function: =AND(logical1, [logical2],…] (returns true if all the logicals are true)
• OR function: =OR(logical1, [logical2],…] (returns true if only one logical is true)
• SWITCH function: =SWITCH(expression, value1, result1, [default_or_value2, result2],…)
• Use SWITCH instead of IFS when your comparisons only require exact matches
• COUNTIF function: =COUNTIF(range, criteria)
• SUMIF function: =SUMIF(range, criteria, [sum_range])
• AVERAGEIF function: =AVERAGEIF(range, criteria, [average_range])
• COUNTIFS function: =COUNTIFS(criteria_range1, criteria1,…)
• SUMIFS function: =SUMIFS(sum_range, criteria_range1, criteria1,…)
• AVERAGEIFS function: =AVERAGEIFS(average_range, criteria_range1, criteria1,…)
• MAXIFS function: =MAXIFS(max_range, criteria_range1, criteria1,…)
• MINIFS function: =MINIFS(min_range, criteria_range1, criteria1,…)

---

1.3. Date and time functions

• Press Ctrl + 1 to enter the formatting table.

• Converting texts to dates: Use text functions like LEFT/RIGHT/MID to extract date components from a string, and the DATE function to convert the result into a proper date value.
• WEEKDAY function: =WEEKDAY(serial_number, [return_type])
  1: Sunday (1) to Saturday (7)
  2: Monday (1) to Sunday (7)
  3: Monday (0) to Sunday (6)
• WORKDAY function: =WORKDAY(start_date, days, [holidays])
• NETWORKDAYS function: =NETWORKDAYS(start_date, end_date, [holidays])

---

1.4. Macros and form controls

• A macr