Microsoft Office Specialist: Excel Exam Preparation：Perform operations by using formulas and functions

1. 第1题，共20题

   Which function counts the cells in a range that are not empty, including cells with text?

   • ACOUNT
   • BCOUNTBLANK
   • CCOUNTA
   • DSUM
   显示答案

   答案: C. COUNTA

   COUNT counts only numbers. Use COUNTA to count any cell that is not empty, such as text, numbers or errors.

2. 第2题，共20题

   What does =SUM(B2:B6) return?

   	A	B
   1	Day	Calls
   2	Mon	14
   3	Tue	9
   4	Wed	17
   5	Thu	11
   6	Fri	20

   • A14.20
   • B71
   • C61
   • D20
   显示答案

   答案: B. 71

   SUM adds every number in the range: 14 + 9 + 17 + 11 + 20 = 71.

3. 第3题，共20题

   D2 contains =B2*C2. You copy D2 down to D3. What formula is in D3?

   • A=B2*C2
   • B=B3*C2
   • C=B3*C3
   • D=C2*D2
   显示答案

   答案: C. =B3*C3

   Relative references change by the same number of rows and columns as the move. One row down, B2 and C2 become B3 and C3.

4. 第4题，共20题

   The tax rate is in B1. C4 contains =B4*$B$1. You copy C4 to C5. What formula is in C5?

   • A=B5*B2
   • B=B4*$B$1
   • C=B5*$B$2
   • D=B5*$B$1
   显示答案

   答案: D. =B5*$B$1

   B4 is relative, so it becomes B5. $B$1 is absolute, so it stays the same.

5. 第5题，共20题

   You type =A1 and, with A1 selected in the formula, press F4 twice. What reference do you get?

   • A$A1
   • BA$1
   • CA1
   • D$A$1
   显示答案

   答案: B. A$1

   F4 cycles through $A$1, A$1, $A1 and back to A1. The second press gives A$1 (row locked).

6. 第6题，共20题

   What does =AVERAGE(B2:B7) return? (B4 is empty.)

   	A	B
   1	Week	Hours
   2	Wk 1	8
   3	Wk 2	6
   4	Wk 3
   5	Wk 4	10
   6	Wk 5	0
   7	Wk 6	6

   • A5
   • B7.5
   • C30
   • D6
   显示答案

   答案: D. 6

   AVERAGE ignores empty cells but counts zeros: 30 ÷ 5 = 6.

7. 第7题，共20题

   What does =MAX(B2:B7)-MIN(B2:B7) return?

   	A	B
   1	Guard	Rounds
   2	Ari	42
   3	Bo	57
   4	Cy	38
   5	Di	61
   6	Ed	45
   7	Flo	50

   • A23
   • B19
   • C99
   • D61
   显示答案

   答案: A. 23

   MAX finds 61 and MIN finds 38; 61 − 38 = 23.

8. 第8题，共20题

   What does =COUNT(B2:B9) return?

   	A	B
   1	Client	Fee
   2	C1	25
   3	C2	N/A
   4	C3
   5	C4	0
   6	C5	40
   7	C6
   8	C7	15
   9	C8	TBD

   • A8
   • B4
   • C3
   • D6
   显示答案

   答案: B. 4

   COUNT counts only cells with numbers (25, 0, 40 and 15), so the result is 4. Text such as N/A and empty cells are not counted.

9. 第9题，共20题

   What does =COUNTBLANK(B2:B9) return?

   	A	B
   1	Client	Fee
   2	C1	25
   3	C2	N/A
   4	C3
   5	C4	0
   6	C5	40
   7	C6
   8	C7	15
   9	C8	TBD

   • A3
   • B2
   • C4
   • D6
   显示答案

   答案: B. 2

   COUNTBLANK counts empty cells only. B4 and B7 are empty; the 0 in B5 is a value, so it is not counted. The result is 2.

10. 第10题，共20题

    D2 contains =IF(C2>=B2,"Met","Short") and is filled down to D5. Who gets "Short"?

    	A	B	C	D
    1	Name	Target	Actual	Result
    2	Ali	50	52
    3	Bo	40	40
    4	Cy	45	44
    5	Di	30	36

    • ACy
    • BDi
    • CBo
    • DAli
    显示答案

    答案: A. Cy

    IF returns "Met" when Actual is greater than or equal to Target. Only Cy (44 against 45) is below target; Bo is equal, so Bo is Met.

11. 第11题，共20题

    In a table, a formula reads =[@Hours]*[@Rate]. What does [@Hours] refer to?

    • AThe whole Hours column
    • BThe Hours cell in the same row as the formula
    • CThe Hours value shown in the table's total row
    • DThe Hours header cell
    显示答案

    答案: B. The Hours cell in the same row as the formula

    The @ sign means "this row", so [@Hours] is the Hours value in the formula's own row.

12. 第12题，共20题

    What does =SUM(Shifts[Hours]) add up?

    • AEvery cell on the Shifts sheet
    • BThe Hours column including the header and total row
    • COnly the Hours cell in the current row
    • DThe data cells in the Hours column of table Shifts
    显示答案

    答案: D. The data cells in the Hours column of table Shifts

    Table[Column] refers to the data cells of that column; the header and total row are not included.

13. 第13题，共20题

    B2 contains =$A2*B$1. You copy B2 to D4. What formula is in D4?

    • A=$A4*D$1
    • B=$A2*D$1
    • C=$A4*B$1
    • D=C4*D3
    显示答案

    答案: A. =$A4*D$1

    $A2 keeps column A but its row moves down 2 (A4). B$1 keeps row 1 but its column moves right 2 (D$1).

14. 第14题，共20题

    A2 contains CAL-2041-N. What does =MID(A2,5,4) return?

    • A041-
    • B-204
    • C2041
    • DCAL-
    显示答案

    答案: C. 2041

    MID starts at character 5 and returns 4 characters. Characters 1–4 are "CAL-", so the result is 2041.

15. 第15题，共20题

    A2 contains Bow River Patrol. What does =LEN(A2) return?

    • A3
    • B14
    • C15
    • D16
    显示答案

    答案: D. 16

    LEN counts every character, including the two spaces, so the result is 16.

16. 第16题，共20题

    A2 contains Priya and B2 contains Sandhu. What does =CONCAT(LOWER(LEFT(A2,1)),".",LOWER(B2)) return?

    • Apsandhu
    • Bpriya.sandhu
    • Cs.priya
    • Dp.sandhu
    显示答案

    答案: D. p.sandhu

    LEFT(A2,1) takes "P", LOWER makes the parts lower case, and CONCAT joins them in order with the full stop between: p.sandhu.

17. 第17题，共20题

    A2:A5 hold Ana, (empty), Raj and Li. What does =TEXTJOIN(", ",TRUE,A2:A5) return?

    • AAna, Raj, Li
    • BAnaRajLi
    • CAna, , Raj, Li
    • DAna,Raj,Li
    显示答案

    答案: A. Ana, Raj, Li

    TEXTJOIN puts the delimiter (comma and space) between items. TRUE tells it to skip empty cells, so no extra comma appears.

18. 第18题，共20题

    D2 contains =SORT(A2:B6,2,-1). Which name appears in D2?

    	A	B	C	D
    1	Name	Score
    2	Ivy	72
    3	Jon	88
    4	Kai	65
    5	Lea	91
    6	Max	80

    • AIvy
    • BJon
    • CKai
    • DLea
    显示答案

    答案: D. Lea

    Sort index 2 sorts by the Score column, and -1 means descending, so the highest score (91, Lea) comes first.

19. 第19题，共20题

    How many values does =UNIQUE(A2:A9) return?

    	A
    1	Site
    2	North
    3	South
    4	North
    5	East
    6	West
    7	South
    8	North
    9	East

    • A4
    • B1
    • C3
    • D8
    显示答案

    答案: A. 4

    UNIQUE returns each distinct value once: North, South, East, West. (Only with exactly_once set to TRUE would it return just West.)

20. 第20题，共20题

    You enter =UNIQUE(A2:A40) in C2. It should return 8 names, but C5 already contains text. What does C2 show?

    • A#N/A
    • B#VALUE!
    • C#SPILL!
    • D#NAME?
    显示答案

    答案: C. #SPILL!

    UNIQUE and SORT spill their results into the cells below. The 8 results need C2:C9, and C5 is not empty, so the formula shows #SPILL! until you clear C5.

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