PassEveryTest
中文

Excel Formulas and Data Analysis Course 2026

This course is for people who know basic Excel (SUM, simple IF and cell references) and want to analyze data at work. It covers logical formulas, conditional totals, lookups, text and date functions, conditional formatting, data validation, duplicates, PivotTables and charts. Study each lesson and try its practical exercise and topic quiz, then take the timed final assessment. Score 80% or more to earn a certificate of completion.

  • 课程
  • 加拿大
  • 96道题
  • 核对于2026年10月13日
  • 事实核查：2026年10月13日

快速解答

这门免费课程包括约240分钟的课程、6个不限时的专题测验（共96道题），以及一次随机抽取35道题的限时期末评估。期末评估得分达到80%或以上即可下载结业证书（私人颁发，未经认证）。 已于2026年10月13日根据官方资料核对。

从课文开始Excel Formulas and Data Analysis Course: Lessons：阅读时间约240分钟。测试中的每道题在课文中都有讲解。

你将学到什么

  • Make decisions in formulas with IF, IFS, AND, OR and NOT
  • Add, count and average data that meets several conditions with SUMIFS, COUNTIFS and AVERAGEIFS
  • Look up values with XLOOKUP, VLOOKUP and INDEX/MATCH, and handle missing values with IFERROR and IFNA
  • Split, clean and join text, and calculate with dates and working days
  • Highlight data with conditional formatting, control entries with data validation and remove duplicates safely
  • Summarize data with PivotTables and choose charts that answer a business question

得分达到80%或以上即可下载证书。

你的学习计划

阅读每一课，完成对应的专题测验（不限时，有解释），然后参加限时的期末评估。每次都会抽取新的题目组合。你的进度保存在此浏览器中。

  1. 阅读课程6课，约240分钟
  2. 专题测验1：Logical formulas: IF, IFS, AND and OR15道题
  3. 专题测验2：Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS15道题
  4. 专题测验3：Lookups: XLOOKUP, VLOOKUP, INDEX/MATCH and IFERROR15道题
  5. 专题测验4：Text and date functions17道题
  6. 专题测验5：Conditional formatting, data validation and duplicates17道题
  7. 专题测验6：PivotTables and charts for analysis17道题
  8. 结业考试35道题，35分钟 · 得分达到80%或以上

专题测验1：Logical formulas: IF, IFS, AND and OR

  1. 第1题，共15题

    What does =AND(5>3, 2>4) return?
    显示答案

    答案: B. FALSE

    AND returns TRUE only when every test is TRUE. 2>4 is FALSE, so the result is FALSE.

  2. 第2题，共15题

    What does =OR(5>8, 3<4) return?
    显示答案

    答案: D. TRUE

    OR returns TRUE when at least one test is TRUE. 3<4 is TRUE, so the result is TRUE.

  3. 第3题，共15题

    In the sheet below, what does =NOT(D3="South") return?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: B. FALSE

    D3 holds South, so D3="South" is TRUE. NOT reverses it, so the result is FALSE.

  4. 第4题，共15题

    In the sheet below, E2 contains =IF(AND(B2>=50000,C2>=12),"Bonus","No bonus") and is copied down to E7. How many cells show "Bonus"?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: B. 2

    Both tests must be TRUE. Only Ana (52,000 and 14) and Eli (58,000 and 25) pass both.

  5. 第5题，共15题

    In the sheet below, E2 contains =IF(OR(B2>=50000,C2>=20),"Bonus","No bonus") and is copied down to E7. How many cells show "Bonus"?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: C. 4

    OR needs just one TRUE test. Ana, Chloé and Eli pass on sales, and Ben passes on accounts (22), so 4 people get "Bonus".

  6. 第6题，共15题

    What must you put around text such as North when you use it inside a formula like =IF(D2="North","Yes","No")?
    显示答案

    答案: B. Double quotation marks

    Text in a formula goes inside double quotation marks. Without them, Excel tries to read the word as a name and usually shows #NAME?.

  7. 第7题，共15题

    How many conditions can you test in one IFS function?
    显示答案

    答案: A. 127

    Microsoft says IFS can test up to 127 different conditions. (AND and OR can each take up to 255.)

  8. 第8题，共15题

    In the sheet below, what does =IFS(B5>=60000,"Gold",B5>=50000,"Silver") return?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: C. #N/A

    Dev's sales (39,000) make neither test TRUE. When no condition in IFS is TRUE, it returns the #N/A error.

  9. 第9题，共15题

    You want IFS to return "Bronze" when none of the earlier tests are TRUE. What do you put as the last test?
    显示答案

    答案: A. TRUE

    To give IFS a default result, enter TRUE as the final logical test and the default value after it.

  10. 第10题，共15题

    In the sheet below, E2 contains =IFS(B2>=60000,"Gold",B2>=50000,"Silver",TRUE,"Bronze") and is copied down. What does E6 show?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: C. Silver

    E6 checks Eli's 58,000. It is not 60,000 or more, but it is 50,000 or more, so IFS stops at "Silver".

  11. 第11题，共15题

    In the sheet below, E4 contains =IF(B4>=50000,"Silver",IF(B4>=60000,"Gold","Bronze")). What does it show for Chloé?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: D. Silver

    IF stops at the first TRUE test. 61,000 is already 50,000 or more, so "Silver" is returned and the Gold test is never reached. Put the highest tier first.

  12. 第12题，共15题

    In the sheet below, E2 contains =IF(NOT(OR(D2="North",D2="South")),"Check","OK") and is copied down to E7. How many cells show "Check"?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: A. 2

    OR is TRUE for North or South, and NOT flips it. Only the two East rows (Dev and Fatima) give "Check".

  13. 第13题，共15题

    In the timesheet below, D2 contains =IF(B2>40,(B2-40)*C2*1.5,0). What does D2 show?
    ABC
    1NameHoursRate
    2Gus4422
    3Hana3824
    4Ivan4021
    显示答案

    答案: D. 132

    Gus worked 44 hours, which is more than 40. Overtime pay is (44-40) x 22 x 1.5 = 132.

  14. 第14题，共15题

    In the timesheet below, D4 contains =IF(B4>40,(B4-40)*C4*1.5,0). What does D4 show?
    ABC
    1NameHoursRate
    2Gus4422
    3Hana3824
    4Ivan4021
    显示答案

    答案: C. 0

    Ivan worked exactly 40 hours. 40>40 is FALSE (> means strictly greater), so IF returns the value_if_false, 0.

  15. 第15题，共15题

    In the sheet below, E3 contains =IF(C3>=20,IF(B3>=50000,"A","B"),"C"). What does E3 show?
    ABCD
    1NameSalesAccounts区域
    2Ana5200014North
    3Ben4750022South
    4Chloé610009North
    5Dev3900018East
    6Eli5800025South
    7Fatima4500011East
    显示答案

    答案: A. B

    Ben has 22 accounts, so the outer test is TRUE and Excel checks the inner IF. His sales (47,500) are under 50,000, so it returns "B".

考前须知

  • IFS can test up to 127 conditions. If none is TRUE it returns #N/A, so enter TRUE as the last test to give a default result.
  • In SUMIFS the range to add comes first, but in SUMIF it comes third. SUMIFS accepts up to 127 range/criteria pairs.
  • XLOOKUP uses an exact match by default and can return a value from a column on either side. VLOOKUP only looks from left to right.
  • IFERROR catches the #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! errors.
  • Excel stores dates as serial numbers so they can be used in calculations. January 1, 1900 is serial number 1.
  • Data validation shows messages and blocks invalid entries only when people type in a cell. Copied or filled data can get past it.
  • Remove Duplicates deletes data permanently, so Microsoft suggests copying the original data to another worksheet first.
  • In a PivotTable, Count is the default summary for value fields that have text or blank cells; Sum is the default for numbers.

常见问题

What certificate do I get, and what is it not?

If you score 80% or more on the final assessment, you can download a certificate of completion for this course. It shows you finished a general skills course. It is not an accredited qualification, not a Microsoft certification such as Microsoft Office Specialist, and not an employer or government certification.

Should I take the Excel for beginners course first?

If you are new to formulas, yes. This course assumes you can already use SUM, AVERAGE, COUNTIF, a simple IF and absolute references ($A$1).

Which version of Excel do I need?

The lessons describe Excel for Microsoft 365. XLOOKUP needs Excel for Microsoft 365 or Excel 2021 and later; it is not in Excel 2016 or 2019. IFS and TEXTJOIN need Excel 2019 or later. In older versions, use VLOOKUP or INDEX and MATCH, and nested IF.

How does the final assessment work?

It has 35 questions picked at random from the course question bank, and you have 35 minutes. Many questions show a small spreadsheet and ask what a formula returns. You get a new mix each time.

Can I practise with my own work files?

Use the practice sheets in the lessons. If you use real work data, follow your employer's policies and privacy rules, and work on a copy so you do not change the original.

这份证书是否经过认证或获得官方认可？

不是。这是一份私人颁发的结业证书，仅用于知识学习。它未获得任何政府、执照发放机构、监管机构或教育机构的认可、认证或批准，也不能替代法律要求的任何执照、资格证书或培训。

资料来源

相关测试