计算机基础上机实验精华版九

实验九 Excel公式和函数的使用

一、实验目的：

1．掌握Excel公式的应用。

2．掌握Exc1e常用函数的应用。

3．掌握三大地址的区别与应用。

二、实验设备和软件：

1、硬件设备：Pentium Ⅳ 以上微型计算机。

2、软件设备：Windows XP、Excel 2002或以上版本。

三、实验原理和预备知识：

公式是利用单元格的引用地址对存放在其中的数据进行计算的等式。一个公式由三部分组成：等号、运算数据和运算符。运算数据可以是常量数据（包括数值常量和字符常量）、单元格地址引用、区域引用和Excel提供的函数。运算符可以是算术运算符（+、-、*、/、^、%）、比较运算符（>、>=、<、<=、=、<>）、文本运算符（&）和引用运算符（:）。创建公式的步骤如下：

1、选定输入公式的单元格；

2、输入等号“=”；

3、输入公式具体内容（可以直接在单元格中输入，也可以在编辑栏中输入）；

4、按下Enter键，完成公式创建。

函数是Excel自带的一些已经定义好的公式。函数处理数据的方式与直接创建的公式处理数据的方式是相同的。使用函数既可以减少输入的工作量，也可以减小输入时出错的概率。

所有函数都是由函数名和位于其后的一系列用括号括起来的参数组成的，即：函数名(参数1，参数2，……)。函数名代表了该函数具有的功能。不同类型的函数要求的参数类型不尽相同，可以是数字、文本、逻辑值、单元格地址、区域地址和另一个函数(函数嵌套)，给定的参数必须能产生有效数值。

函数可以有两种方法输入，一种是直接输入法，如果能记住函数名的拼写以及正确的参数类型，可以直接通过键盘在编辑栏或单元格中输入函数，这是一种效率较高的输入方式；另一种是粘贴函数法，通过Excel提供的函数输入指南，逐步完成函数的输入。

在Excel公式中，单元格是作为变量参与运算的，运算中指定的是存放数据的单元格地址，而不是数据本身，公式运算的结果总是采用单元格中的当前数据。如果改变单元格中的数据内容，则计算结果也会发生变化。引用单元格的公式的复制操作，可节省大量重复公式的输入。在这类公式复制过程中，可根据不同的位置或情况变换不同的单元格地址引用方式。单元格地址引用分为相对引用、绝对引用和混合引用。

1、相对引用相对引用是指单元格引用会随公式所在单元格的位置变更（公式复制时）而改变。相对引用地址由列号行号表示。

2、绝对引用绝对引用单元格地址将不随公式位置的变化而改变。绝对引用地址的表示方法是在相对地址的列号和行号前分别加上一个美元符号“$”。

3、混合引用混合引用是指只在行号或列号前加“$”符号。公式复制后，只有其中的相对地址部分发生改变而绝对地址部分不变。

四、实验内容和步骤：

1、模仿性实验

实验任务1 算术公式和逻辑函数的使用

打开“职工工资表.XLS”，在“工资表”工作表中，进行以下操作运算：

⑴、在“补贴”所在列用公式计算各职工的补贴额。补贴的计算方法如下：若基本工资大于2000元，则补贴基本工资的12%；若基本工资大于1500元、小于等于2000元，则补贴基本工资的8%；若基本工资不多于1500元，则一律补贴95元。

⑵、根据职工的出生日期，在“年龄”列用公式计算出各职工的年龄(不考虑周岁)。

⑶、用公式计算各职工的应发工资：应发工资=基本工资+补贴。

⑷、按以下要求用公式计算各职工应纳所得税额：若应发工资大于2000元，则应发工资减去1500元后的余额纳10%的税；若应发工资大于1500元但小于等于2000元，则应发工资减去1500元后的余额纳8%的税；其它情况不纳税。

⑸、计算各职工的实发工资额：实发工资=应发工资-所得税。

实验步骤

（1）、计算补贴。在H3单元格中输入公式：=IF(G3>2000,G3*12%,IF(G3>1500,G3*8%,95))。拖动H3单元格句柄到H22单元格。结果如图9-1所示。

（2）、计算年龄。在E3单元格中输入公式：=YEAR(TODAY())-YEAR(F3)，拖动E3单元格句柄到E22单元格，选择E3：E22区域，单击“格式”→“单元格”→“数字”，在“分类”中选择“常规”。结果如图9-2所示。

（3）、计算应发工资。在I3单元格中输入公式：=G3+H3，拖动I3单元格句柄到I22单元格。结果如图9-3所示。

（4）、计算所得税。

在J3单元格中输入公式：=IF(I3>2000,(I3-1500)*10%,IF(I3>1500,(I3-1500)*8%,0))。拖动J3单元格句柄到J22单元格。结果如图9-4所示。

（5）、计算实发工资。在K3单元格中输入公式：=I3-J3，拖动K3单元格句柄到K22单元格。结果如图9-5所示。

实验结果

结果如图9-1、图9-2、图9-3、图9-4、图9-5所示。

实验任务2 常用函数的使用

将“Sheet4”工作表改名为“公式与函数”，然后完成以下操作：

（1）、用函数统计出“工资表”工作表中职工的平均年龄、所得税总额和最高实发工资。

（2）、在“公式与函数”工作表的A1单元格中输入“46.057”作为变量x的值，然后在A2:G2区域实现以下函数运算：

、将x值舍人到个位、求x的小数部分、产生一个100至300间的随机整数、求1222211除以111的余数、

（3）、在“公式与函数”工作表的A4:J4区域中用日期公式填入以下规律变化的日期数据：“Jan 01,1999、Jan 02,1999、Jan 03,1999、……”。

（4）、在“公式与函数”工作表的A6:C13区域中输入以下电话号码升位表，如图9-6所示。然后按以下规律用公式完成电话号码由7位到8位的升位计算：原电话号码第一位为8时则在8之后插入1，否则在原号码首位直接加8。

实验步骤

（1）、将鼠标指针指向“sheet4”工作表，单击鼠标右键，在快捷菜单中选择“重命名”，输入工作表名称“公式与函数”。

（2）、统计函数的使用。在“工资表”工作表中，分别在B24、D24、G24单元格中输入下列公式：=AVERAGE(E3:E22)、=SUM(J3:J22)、=MAX(K3:K22)。

（3）、数学函数的使用。在“公式与函数”工作表中的A1单元格中输入“46.057”，然后分别在A2、B2、C2、D2、E2、F2、G2单元格中依次输入下列公式：=SQRT(A1)、=ROUND(A1,0)、=A1-INT(A1)、=INT(RAND()*(300-100)+1)+100、=MOD(1222211,111)、=SQRT(SIN(A1*PI()))/(COS(PI()/12)+1)。

（4）、用函数计算日期序列。先用日期函数公式按要求产生规律变化的序列：在“公式与函数”工作表中的A4单元格中输入初始日期数据“1999/1/1”，在B4单元格中输入公式：=DATE(YEAR(A4),MONTH(A4),DAY(A4)+1)，拖动B4单元格的句柄到J4单元格。然后通过自定义格式显示输出：选择区域A4：J4，单击“格式”→“单元格”→“数字”，在“分类”中选择“自定义”，在“类型”框中输入“mmm dd,yyyy”，单击【确定】按钮。

（5）、电话号码升位。在“公式与函数”工作表A6:C13区域中输入图9-6所示的电话号码升位表，在C7单元格中输入公式：=IF(LEFT(A7,1)="8","81"&RIGHT(A7,6),"8"&A7)，拖动C7单元格的句柄到C13单元格。

实验结果

结果如图9-7所示。

实验任务3 三大地址的使用

将“Sheet3”工作表改名为“三大地址”，在区域A7：G9生成如图9-8所示的数据表格，在B9单元格输入一个公式，并将其沿行方向复制，算出当月止之累计营业额，即B9的值为一月份营业额；C9的值为一月、二月营业额之和；依此类推。

实验步骤

（1）、工作表重命名。将鼠标指针指向“sheet3”工作表，单击鼠标右键，在快捷菜单中选择“重命名”，输入工作表名称“三大地址”。

（2）、混合地址计算。在区域A7：G9生成如图所示的数据表格，在单元格B9 中输入公式：=SUM($B8:B8)，拖动B9单元格句柄到G9单元格。

实验结果

结果如图9-9所示。

2、设计性实验

实验任务1 计算周岁

在“工资表(2)”工作表的区域E3：E22中，按周岁计算各职工的年龄。

实验结果

结果如图9-10所示。

实验任务2 混合地址计算

在“三大地址”工作表中，在区域A12：E19生成如图9-11所示的数据表格，该表给出了存入银行的不同本金和不同存款年限的年利率，要求在C14单元格输入计算税后本息合计的公式，再通过复制公式，完成全部税后本息合计的计算，要求结果以两位小数显示，注意利息要扣20%的税。

实验结果

结果如图9-12所示。

实验任务3 计算九九乘法表

在“三大地址”工作表中的区域A21：J30中生成如图9-13所示的“九九乘法表”，要求先在B21：J21区域中输入一行数据1~9，在A22：A30区域中输入一列数据1~9，然后在B22单元格中输入一个公式，再将该公式沿行/列两个方向复制，从而得出九九乘法表。

实验结果

结果如图9-13所示。

实验任务4 学生成绩表绩点的计算和奖学金等级的判定

(1)、打开“学生成绩表.XLS”文件，在“课程1”和“课程2”工作表中分别计算“总评成绩”。计算方法：总评成绩=平时成绩*30%+期末成绩*70%，要求结果四舍五入到整数位，如总评成绩为76.5，则舍入后的实际总评成绩应为77。

(2)、在“课程1”和“课程2”工作表中分别计算各学生的课程“绩点”。计算方法：成绩60分时绩点为1，成绩每增加1分绩点增加0.1；成绩小于60分时绩点为0。

(3)、在“奖学金”工作表中计算各学生的“平均绩点”，并判断其应获得的“奖学金等级”。奖学金等级判断方法：“平均绩点”在4.0或以上者能获得一等奖学金，3.5或以上者能获得二等奖学金，3.0或以上者能获得三等奖学金。要求“平均绩点” 四舍五入到小数点后1位。提示：此工作表中“绩点1”和“绩点2”的数据可通过“粘贴值”或“粘贴链接”从“课程1”和“课程2”中获得。

实验结果

结果如图9-14、图9-15、图9-16所示

五、思考题：

1、函数运算用到的参数必需放在一对紧接函数名的括号中，但对于无参函数而言，则括号可以省略。这句话对吗？

2、在Excel中，所有函数实现的功能都可以用非函数的方法来完成。这句话对吗？

3、相对地址、绝对地址和混合地址的共性在哪里？主要区别又在哪里？

第二篇：第九版计算机文化基础实验教程习题答案

第一章

一、单项选择题

1.A 2.A 3.A 4.A 5.C 6.D 7.C 8.B 9.C

10.B 11.B 12.D 13.A 14.C 15.C 16.C 17.A 18.A

19.A 20 C 21.D 22.B 23.A 24.B 25.D 26.C 27.A

28.B 29.B 30.A 31.A 32.C 33.D 34.D 35.B 36.A

37.C 38.A 39.B 40.B 41.B 42.B 43.C 44.A 45.C

46.A 47.A 48.C 49.A

二、多项选择题

1．BC 2．BCD 3．ABD 4．ABC 5．AD 6．ABC

7．ABD 8．AB 9．ABD 10．AC

三、填空题

1.地址码 2．1024 3．总线（Bus） 4．显卡

5．点阵码矢量码 6．过程控制 7．算术运算逻辑运算 8．地址

9．冯·诺依曼的存储程序工作 10．字 11．应用软件

12．二进制 13．输入/输出或者（I/O） 14．CD-ROM

15．非击打式

16．American Standard Code for Information Interchange（美国标准信息交换代码）

17．寄存器 18．信息处理 19．中央处理器 20．3

四、判断题

1．T 2．F 3．F 4．T 5．T 6．F

7．T 8．F 9．F 10．T 11．T 12．T

13．F 14．T 15．T 16．T 17．F 18．F

19．T 20．F 21．T 22．F 23．F

第二章

一、单项选择题

1. A 2. D 3. C 4. D 5. B 6. B 7. C 8. B 9. D

10. D 11. A 12. B 13. C 14. B 15. C 16. D 17. C 18. D

19. D 20. A 21. D 22. C 23. C 24. D 25. B 26. D 27. B

28. B 29. D 30. A 31. D 32. D 33. B 34. D 35. B 36. B

37. C 38. D 39. B 40. A 41. A 42. A 43. C 44. A 45. B

46. D 47. C 48. C 49. D 50. B

二、多项选择题

1. ACD 2. ABCD 3. ABD 4. ABC 5. AB 6. BCD

7. ABC 8. AD 9. ABCD 10. ABD 11. ABC 12. ABCD

13. AC 14. ABCD 15. ABCD 16. ABCD 17. ABCD 18. ABC

19. AB 20. ABD

三、判断题

1. F 2. F 3. F 4. F 5. T 6. T 7. T 8. T 9. F

10. F 11. F 12. F 13. F 14. T 15. F 16. F 17. F 18. F

19. T 20. F

四、填空题

1. 操作系统 2. setup.exe 3. 任务栏 4. 还原 5. Alt+Print Screen键 6. 安全

7. Alt+F4 8. Ctrl+Alt+Del 9. 分时 10. 实时

第3章

一、单选题：

1. C

2. D

3. A

4. D

5. D

6. C

7. B

8. B

9. A

10. D

11. A

12. A

13. B

14. C

15. D

16. D

17. C

18. C

19. B

20. A

21. C

22. A

23. B

24. C

25. C

26. A

27. B

28. C

29. D

30. A

31. C

32. C

33. D

34. D

35. B

36. D

37. A

38. D

39. B

40. B

41. C

42. D

43. C

44. D

45. A

46. A

47. C

48. C

49. D

50. B

51. B

52. C

53. C

54. C

55. C

56. A

57. C

58. C

59. D

60. B

61. A

62. D

63. B

64. A

65. D

66. D

67. B

68. C

69. A

70. B

71. D

72. B

73. A

74. B

75. C

76. B

77. B

78. D

79. B

80. B

81. B

82. B

83. B

84. C

85. C

86. C

87. B

88. A

89. C

90. D

二、多项选择题

1. CD

2. CD

3. AC

4. AC

5. AC

6. ABC

7. ABCD

8. AB

9. AC

10. AD

11. AB

12. AB

13. AC

14. AC

15. AB

16. AD

17. AD

18. AB

19. CD

20. B

21. AD

22. AB

23. AC

24. BC

25. ABCD

三、判断题：

1. F

2. T

3. T

4. F

5. F

6. T

7. T

8. T

9. F

10. T

11. T

12. F

13. T

14. F

15. T

16. F

17. F

18. F

19. F

20. T

21. T

22. F

23. F

24. T

25. F

26. T

27. F

28. F

29. F

30. F

31. T

32. F

33. F

34. F

35. T

36. T

37. F

38. T

39. T

40. T

41. F

42. T

43. T

44. F

45. F

46. F

47. F

48. F

49. T

50. T

51. T

52. T

53. T

54. T

55. T

56. T

57. F

58. T

59. F

60. T

61. F

62. T

63. F

64. F

65. F

四、填空题：

1. 关闭

2. 11

3. N

4. 剪切

5. 段落

6. 主文档

7. 格式

8. 重复标题行

9. 普通页面

10. 替换

11. Word/Excel等

12. 5

13. 文件

14. 印刷

15. 下划线

16. 模板

17. 首行首行

18. 工具

19. 视图

20. 两端对齐

21. 公式编辑器

22. 另存为

23. 打印预览

24. 宋体

25. 表格

26. Word文档

27. 宏

28. 新建

29. 打开

30. Backspace Del

31. 文件打印

32. 建立数据源

33. 行距设置值

34. 箭头左键

35. 格式

36. 插入

37. 标尺

38. 字体

39. 相等的行高

40. Ctrl+S

41. 工具字符统计

42. 视图

43. 插入

44. Ctrl+End

45. Ctrl+Pageup

46. 插入符号

47. 主词典

48. 分隔符

49. Insert

50. 下划线

第四章

一、单项选择题

1. B 2. D 3. C 4. D 5. D 6. C 7. A 8. B 9. D

10. A 11. B 12. B 13. A 14. A 15. C 16. D 17. B 18. C

19. B 20. B 21. A 22. C 23. B 24. B 25. B 26. C 27. B

28. A 29. B 30. B 31. A 32. C 33. B 34. A 35. B 36. C

37. D 38. B 39. C 40. A 41. B 42. D 43. C 44. D 45. B

46. B 47. A 48. B 49. B 50. D

二、多项选择题

1. BCDE 2. AB 3. AD 4. AD 5. CD 6. ABD

7. ABD 8. BCD 9. ABD 10. CD 11. AB 12. AD

13. ACD 14. AB 15. ABCD 16. ABCD 17. AB 18. ABCD

19. ABCD 20. ABCD

三、判断题

1. T 2. F 3. F 4. F 5. T 6. F 7. T 8. F 9. F

10. T 11. F 12. T 13. F 14. F 15. F 16. T 17. F 18. F

19. T 20. F

四、填空题

1. 自动填充 2. 工作簿工作表 3. 相对引用绝对引用混合引用

4. 排序 5. =SUM(E5:E20) 6. .xls

7. 列标（题） 8. 批注 9. 编辑

10. 6 11. 嵌入式图表图表工作表

12. 链接嵌入 13. 边框填充柄

第五章

一、单项选择题

1. D 2. B 3. C 4. D 5. A 6. B 7. A 8. C 9. D 10. B

11. C 12. D 13. C 14. C 15. D 16. C 17. A 18. D 19. B 20. C

21. D 22. A 23. C 24. B 25. D 26. A 27. D 28. D 29. D 30. C

二. 多项选择题

1. ABCD 2. ABCD 3. ABC 4. AD 5. BCD 6. ACD 7. ABCD

8. BD 9. AD 10. AC 11. AC 12. AB 13. ABC 14. AD

15. AC 16. ABC 17. CD 18. ABCD 19. ABCD 20. ABCD

三. 判断题

1. T 2. T 3. F 4. T 5. F 6. T 7. F 8. F 9. T 10. F

11. T 12. F 13. T 14. T 15. T 16. T 17. F 18. T 19. T 20. F

四．填空题

1. ppt 2. 幻灯片 3. 应用所有幻灯片 4. 来自文件 5. KS.pps

6. 8个尺寸控点 7. 添加文字 8. 设计模板 9. Esc键

第7章

一、单项选择题

1. C 2. B 3. C 4. D 5. B 6. C 7. C 8. C 9. C

10. A 11. D 12. C 13. B 14. A 15. D 16. A 17. A 18. A

19. D 20. A 21. B 22. C 23. A 24. B 25. D 26. C 27. A

28. D 29. A 30. A 31. B 32. B 33. D 34. C 35. C 36. B

37. B 38. D 39. D 40. C 41. C 42. A 43. B 44. B 45. B

二、多项选择题

1. ABD 2. BCD 3. ABCD 4. ACD 5. BCD 6. AB

7. AB 8. ABCD 9. ABC 10. ACE 11. ACDE 12. ABCDE

13. BCDE 14. AE 15. AD

三、判断题

1. F 2. F 3. F 4. T 5. F 6. F 7. T 8. F 9. T

10. T 11. F 12. T 13. T 14. F 15. T 16. F 17. T 18. T

19. T 20. T 21. F 22. T 23. F 24. F 25. T 26. F

四、填空题

1. 局域网城域网广域网局域网

2. 有线介质无线传输介质

3. 电磁干扰

4. 单模光纤多模光纤

5. 层次

6. 传输控制协议

7. 应用层传输层网际层网络接口层网络接口层应用层

8. 文件传输协议实现文件的上传和下载

9. CERNet 10. 共享资源交流信息发布和获取信息

11. 路由 12. 同一子网

13. 公用计算机互联网（ChinaNet）、中国教育和科研网（CERNet）、中国科技信息网（CSTNet）、国家公用经济信息通信网络（ChinaGBN）

14. 1994 15. 资源子网通信子网 16. 硬件、软件和数据资源

17. 拓扑结构 18. 域名解析 DNS 19. HTTP

第九章

一.单项选择题

1. B 2. B 3. D 4. D 5. A 6. C 7. D 8. D 9. C 10. C

11. D 12. C 13. B 14. C 15. B 16. A 17. A 18. D 19. B 20. C

二. 多项选择题

1. ABD 2. AB 3. ABCD 4. ABC 5. ABCD 6. AC 7. ABCD

8. ABC 9. ABD 10. ABC 11. ABCD 12. ABD 13. ABD

三. 判断题

1. F 2. T 3. T 4. T 5. T 6. F 7. F 8. T 9. F 10. F

11. F 12. T 13. T 14. T 15. F 16. F 17. T 18. T 19. T

20. T 21. F

第10章

一、单项选择题

1.A 2.B 3.B 4.A 5.D 6.B 7.D 8.B 9.D

10. D 11. D 12. B 13. C 14. C 15. A 16. C 17. A 18. D

19. C 20. D

二、判断题

1.F 2.F 3.T 4.F 5.T 6. T 7. F 8. T 9. F

10. T 11. F 12. F 13. F 14. F 15. F

三、填空题

1.结构隐患 2.黑客 3.密码技术防火墙技术病毒与反病毒技术

4.加密 5.虚拟专用网（VPN）技术 6.E-mail 软件下载

7.硬件保护软件预防 8.衍生性 9.计算机病毒疫苗 10.RAM

11.FAT32 NTFS NTFS 12.Administrator 8位以上的字母数字符号的混合组合

13.使用防火墙更新和安装系统补丁停止不必要的服务 14.“中”以上

15.设置IE的安全级别屏蔽插件和脚本清除临时文件

16.电子商务 17.数字签名 18.安全性 19.电子政务 20.认证中心

21.瑞典 22.安全立法 23. 可靠性 24. 保密性

25. 偶然事故恶意攻击 26. 计算机犯罪 27. 计算机病毒

28. 引导区型病毒文件型病毒 29. Office系列 30. 硬件保护软件预防

31. DES RSA 32. 网络层防火墙 33．Administrator、Guest

34. 内网、外网、因特网、物理隔离 35. CA

相关推荐