Excel for Beginners Course：Formulas and basic functions

1. 第1题，共18题

   What character must every Excel formula start with?

   • A$
   • B=
   • C#
   • D+
   显示答案

   答案: B. =

   Excel formulas always begin with the equal sign (=).

2. 第2题，共18题

   Which operator multiplies two numbers in a formula?

   • A&
   • B*
   • C^
   • Dx
   显示答案

   答案: B. *

   Excel uses the asterisk (*) to multiply. The ^ sign raises a number to a power and & joins text.

3. 第3题，共18题

   What does the formula =4+6*2 return?

   • A14
   • B16
   • C20
   • D12
   显示答案

   答案: B. 16

   Excel multiplies before it adds: 6*2 = 12, then 4 + 12 = 16.

4. 第4题，共18题

   In the timesheet below, what does =SUM(B2:B5) return?

   	A	B	C	D	E	F
   1	Employee	Mon	Tue	Wed	Thu	Fri
   2	Ana	8	7.5	8	0	6
   3	Ben	6	8	8	8	4
   4	Chen	7	7		7.5	8
   5	Dana	8	8	8	8	8

   • A29
   • B4
   • C21
   • D29.5
   显示答案

   答案: A. 29

   SUM adds every number in B2:B5: 8 + 6 + 7 + 8 = 29 hours worked on Monday.

5. 第5题，共18题

   In the timesheet below, what does =SUM(B2:F2) return?

   	A	B	C	D	E	F
   1	Employee	Mon	Tue	Wed	Thu	Fri
   2	Ana	8	7.5	8	0	6
   3	Ben	6	8	8	8	4
   4	Chen	7	7		7.5	8
   5	Dana	8	8	8	8	8

   • A29.5
   • B5
   • C28.5
   • D29
   显示答案

   答案: A. 29.5

   B2:F2 is Ana's row: 8 + 7.5 + 8 + 0 + 6 = 29.5 hours.

6. 第6题，共18题

   You select the empty cell under a column of numbers. Which keyboard shortcut inserts a SUM formula with AutoSum?

   • AShift+F11
   • BCtrl+1
   • CCtrl+S
   • DAlt+=
   显示答案

   答案: D. Alt+=

   Alt+= is the AutoSum shortcut. You can also select AutoSum on the Home tab or the Formulas tab and press Enter.

7. 第7题，共18题

   In the timesheet below, what does =MAX(C2:C5) return?

   	A	B	C	D	E	F
   1	Employee	Mon	Tue	Wed	Thu	Fri
   2	Ana	8	7.5	8	0	6
   3	Ben	6	8	8	8	4
   4	Chen	7	7		7.5	8
   5	Dana	8	8	8	8	8

   • A7.5
   • B30.5
   • C8
   • D7
   显示答案

   答案: C. 8

   MAX returns the largest number in the range. The Tuesday hours are 7.5, 8, 7 and 8, so the largest is 8.

8. 第8题，共18题

   In the timesheet below, what does =MIN(F2:F5) return?

   	A	B	C	D	E	F
   1	Employee	Mon	Tue	Wed	Thu	Fri
   2	Ana	8	7.5	8	0	6
   3	Ben	6	8	8	8	4
   4	Chen	7	7		7.5	8
   5	Dana	8	8	8	8	8

   • A0
   • B4
   • C6
   • D8
   显示答案

   答案: B. 4

   MIN returns the smallest number in the range. Friday's hours are 6, 4, 8 and 8, so the smallest is 4.

9. 第9题，共18题

   In the timesheet below, cell D4 is empty. What does =COUNT(B4:F4) return?

   	A	B	C	D	E	F
   1	Employee	Mon	Tue	Wed	Thu	Fri
   2	Ana	8	7.5	8	0	6
   3	Ben	6	8	8	8	4
   4	Chen	7	7		7.5	8
   5	Dana	8	8	8	8	8

   • A5
   • B4
   • C29.5
   • D3
   显示答案

   答案: B. 4

   COUNT counts only cells that contain numbers. Empty cells are not counted, so the answer is 4.

10. 第10题，共18题

    In the timesheet below, cell D4 is empty. What does =AVERAGE(B4:F4) return?

    	A	B	C	D	E	F
    1	Employee	Mon	Tue	Wed	Thu	Fri
    2	Ana	8	7.5	8	0	6
    3	Ben	6	8	8	8	4
    4	Chen	7	7		7.5	8
    5	Dana	8	8	8	8	8

    • A7.375
    • B29.5
    • C7.5
    • D5.9
    显示答案

    答案: A. 7.375

    AVERAGE ignores empty cells. It adds 7 + 7 + 7.5 + 8 = 29.5 and divides by 4 numbers, giving 7.375.

11. 第11题，共18题

    In the timesheet below, Ana has 0 hours on Thursday. What does =AVERAGE(B2:F2) return?

    	A	B	C	D	E	F
    1	Employee	Mon	Tue	Wed	Thu	Fri
    2	Ana	8	7.5	8	0	6
    3	Ben	6	8	8	8	4
    4	Chen	7	7		7.5	8
    5	Dana	8	8	8	8	8

    • A7.375
    • B6
    • C29.5
    • D5.9
    显示答案

    答案: D. 5.9

    AVERAGE includes cells that contain zero. It divides 29.5 by 5 numbers, giving 5.9.

12. 第12题，共18题

    In the attendance sheet below, P means present and A means absent. What does =COUNTIF(B2:F6,"A") return?

    	A	B	C	D	E	F
    1	Name	Mon	Tue	Wed	Thu	Fri
    2	Amrit	P	P	A	P	P
    3	Bella	P	A	A	P	P
    4	Carlos	P	P	P	P	P
    5	Divya	A	P	P		P
    6	Erin	P	P	P	A	P

    • A5
    • B4
    • C19
    • D25
    显示答案

    答案: A. 5

    COUNTIF counts the cells in the range that match the criteria. There are 5 cells that contain A.

13. 第13题，共18题

    In the attendance sheet below, Divya's Thursday cell (E5) is empty. What does =COUNTA(B5:F5) return?

    	A	B	C	D	E	F
    1	Name	Mon	Tue	Wed	Thu	Fri
    2	Amrit	P	P	A	P	P
    3	Bella	P	A	A	P	P
    4	Carlos	P	P	P	P	P
    5	Divya	A	P	P		P
    6	Erin	P	P	P	A	P

    • A3
    • B1
    • C5
    • D4
    显示答案

    答案: D. 4

    COUNTA counts cells that are not empty, whether they hold text or numbers. E5 is empty, so the answer is 4.

14. 第14题，共18题

    In the sheet below, what does =SUM(A2:C2) return?

    	A	B	C
    1	Mon	Tue	Wed
    2	8	N/A	7

    • A8
    • B#VALUE!
    • C0
    • D15
    显示答案

    答案: D. 15

    SUM ignores text in the cells it refers to, so it adds only 8 and 7 and returns 15.

15. 第15题，共18题

    In the sheet below, what does =A2+B2+C2 return?

    	A	B	C
    1	Mon	Tue	Wed
    2	8	N/A	7

    • A15
    • B0
    • CN/A
    • D#VALUE!
    显示答案

    答案: D. #VALUE!

    When you add cells with + and one of them holds text, Excel returns the #VALUE! error. SUM would ignore the text instead.

16. 第16题，共18题

    In the sheet below, C2 contains =IF(B2>=32,"Full","Part") and is copied down to C5. How many cells in C2:C5 show "Full"?

    	A	B	C
    1	Employee	Hours	Status
    2	Ana	29.5
    3	Ben	34
    4	Chen	31.5
    5	Dana	40

    • A2
    • B4
    • C3
    • D1
    显示答案

    答案: A. 2

    IF shows "Full" only when the hours are 32 or more. Ben (34) and Dana (40) meet the test, so 2 cells show "Full".

17. 第17题，共18题

    What does =ROUND(1234.567,-2) return?

    • A1235
    • B1200
    • C1300
    • D1234.57
    显示答案

    答案: B. 1200

    A negative num_digits rounds to the left of the decimal point. -2 rounds to the nearest hundred, so the result is 1200.

18. 第18题，共18题

    In the sheet below, C2 is empty. What does =B2/C2 return?

    	A	B	C
    1	Item	Cost	Units
    2	Gloves	84

    • A#DIV/0!
    • B#VALUE!
    • C84
    • D0
    显示答案

    答案: A. #DIV/0!

    Excel shows #DIV/0! when a number is divided by zero or by an empty cell. A formula like =IF(C2,B2/C2,0) avoids the error.

键盘：按1–4作答，按 Enter 进入下一题。