实验三Excel操作一.docx

实验三Excel操作一.docx

《实验三Excel操作一.docx》由会员分享，可在线阅读，更多相关《实验三Excel操作一.docx（38页珍藏版）》请在冰豆网上搜索。

实验三Excel操作一

实验三Excel操作

（一）

【实验目的】

1.掌握一定的防错数据输入的方法

2.掌握函数的使用方法

3.掌握公式使用及相对和绝对引用方法

4．实验完请做后面练习题!

!

!

【实验内容】

1.对单元格或区域限定并校验数据输入

（1）　建立如图3.1所示原始工作表

图3.1

（2）　定义有效数据准则之1——防止工龄录错

选定E2:

E51（假设单位有50人的编制），点击“数据”→“有效性…”，屏幕弹出图3.2画面。

考虑18岁参加工作，60岁退休,一般人的工龄应在0至42之间。

因此，操作如下：

●        点击“允许”栏右边的三角按钮，选择“整数”

●        点击“数据”栏右边的三角按钮，选择“介于”

●        在“最小值”栏输入0

●        在“最大值”栏输入42

点击“出错警告”选项，系统弹出图3.3画面，操作如下：

●        点击“样式”里的三角按钮，选择“警告”标志

●        在“出错信息”编辑栏输入你想告诫出错者的话语，如“工龄不能小于0或大于42！

”

图3.2图3.3

上述规则确定之后，试着在E1:

E51区域任一单元输入小于0或者是大于42的数，系统就会提出警告，见图3.4。

图3.4

（3）　定义有效数据准则之2——防止出生日期录错

选定F2:

F51区域，点击“数据”→“有效性…”，屏幕弹出图3.5画面。

出生日期假设在1940年至1980年之间，因而操作如下：

●        点击“允许”栏右边的三角按钮，选择“日期”

●        点击“数据”栏右边的三角按钮，选择“介于”

●         在“起始日期”栏输入1940-1-1

●         在“起始日期”栏输入1940-1-1；在“终止日期”栏输入1980-12-31

点击“输入信息”选项，系统弹出图3.6画面，操作如下：

●        输入信息“出生日期录入必须在1940至1980之间”。

　图3.5图3.6

上述规则确定之后，一旦要在F1:

F51区域任一单元准备输入数据时，系统便会提醒你，“出生日期必须在1940年至1980年之间”，见图3.7。

图3.7

选定包含这些准则的区域，然后选择“数据”→“有效性”，系统弹出图3.2画面之后，在“设置”选项下，点击左下角的“全部清除”。

这样可将“输入信息”、“错误警告”等全部去掉。

 2.使用9种函数

（1）财务函数（90个）

假设需要为一年后的某个项目预筹资金，现在将￥1000以年利6%，按月计息（月利6%/12或0.5%）存入储蓄存款帐户中，并在以后十二个月的每个月初存入￥100，则一年后该帐户的存款额等于多少？

请输入下列函数，结果见图3.8。

FV（0.5%,12,-100,-1000,1）等于￥2301.40

图3.8

（2）日期和时间函数（26个）

TODAY（）——返回计算机内部时钟的当前日期。

该函数不需要参数。

见图3.9。

NOW（）——返回计算机内部时钟的当前日期和时间。

该函数不需要参数。

见图3.10。

图3.9图3.10

（3）计算函数（81个）

AVERAGE（）——返回选定区域数值的算术平均值。

见图3.11。

MEDIAN（）——返回选定区域数值的中间值，见图3.12。

图3.11图3.12

MODE（）——返回选定区域出现频率最高的数，见图.3.13。

COUNT（）——返回选定区域数值单元的个数，见图3.14

图.3.13图.3.14

COUNTA（）——返回选定区域包含非空的单元个数，见图3.15和图3.16。

注意，图3.14、图3.15、图3.16各不相同。

图3.14仅统计数字单元的个数，得6；图3.15是连逻辑值和字符串一起统计，得9（注意空格也算字符串）；而图3.16中的B2是用键删除了内容，属真正的空单元，故得8。

图3.15图3.16

（4）数据库函数（12个）

DMIN（参数1,参数2,参数3）——返回满足给定条件的数据库中记录的字段（列）中的最小值。

参数1要求填写构成数据库的区域；参数2要求填写双引号括住的列标题，或是表示该列在工作表中位置的数值；参数3要求填写包含数据库条件的单元格区域。

DMIN（Database,"利润",A1:

B2）等于75.00，即树高超过10的苹果树的最小利润。

另外请注意，这时说的数据库必须得有数据库表的基本格式，即必须有首行（标题行）方可。

见图.3.17。

图.3.17

（5）数学函数（67个）

SUMIF（参数1，<条件表达式>）——根据<条件表达式>，对参数1表示的区域求和。

见图3.18    MOD（参数1,参数2）——返回参数1除以参数2后的余数。

见图3.19。

图.3.18图.3.19

PI（）——返回圆周率π的值，见图3.20。

图3.20图3.21

（6）文本函数（33个）

MID（S,C1,C2）——从字符串S的C1位置起，查找C2个长度的字符。

见图3.21。

LEFT（S，n）——返回字符串S中左边n个字符。

见图3.22。

图3.22

（7）逻辑函数（6个）

AND（C1,C2,…）——如果C1，C2等逻辑表达式均为真，则整个AND函数为真；否则，如果C1，C2等逻辑表达式任一个为假，则AND函数为假。

IF（L1,T1,T2）——如果逻辑表达式L1真，则IF函数值为T1；否则，IF函数值为T2。

   例如，小学生的语文和数学成绩都超过95，可评为三好学生。

执行办法如图3.23。

图3.23

（8）信息（20个）

INFO（system）——返回当前操作系统的信息。

如图3.24中D2单元的值是pcdos，这是因为演示此例的计算机的操作系统是Windows。

如果操作系统是Macintosh的话，则D2值为mac。

    ISTEXT（参数）——当参数是文本时返回值是True，否则返回值是False。

见图3.25。

图3.24图3.25

（9）工程（81个）

BIN2DEC（参数）——将参数表示的二进制数或由参数指定单元的二进制数转换为十进制数。

见图3.26。

DEC2BIN（参数）——将参数表示的十进制数或由参数指定单元的十进制数转换为二进制数。

见图3.27。

图3.26图3.27

3.使用公式及引用

（1）公式中单元格引用

EXCEL最常用的两个名词莫过于单元格和区域。

A1代表工作表左上角第一个单元格的引用；B1代表第一行中第二个单元格的引用；C2:

E4代表着从第二行第三列单元开始至第四行第五列单元所包括的共12个单元的一个区域引用。

这些都是大家比较熟悉的知识了，但若再问一下，3:

3代表什么，C:

D又代表什么，恐怕好多EXCEL的用户未必清楚了。

其实，3:

3就表示第三行，C:

D表示的是从C列到D列这两个列的整个区域。

上述表示法平时不太用，但在函数或宏调用中就显得很灵活了。

下面举两个例子。

ROWS（参数）——返回参数指定的行数。

ROWS（C:

D）的结果为65536。

COLUMNS（参数）——返回参数指定的列数。

COLUMNS（3:

3）的结果为256。

（2）相对引用

通常，EXCEL将公式中的单元格和区域引用解释为相对引用。

也就是说，当你从原位置复制或移动公式到新位置时，EXCEL自动调整单元格引用以反映新位置相对于原位置的方位。

例如，图1.3.29中单元格B6中的公式是合计B列中上方的数值。

当你横向往右复制此公式时，EXCEL理解你是仍想合计其它各列相对应的上方的数值，因此调整运算公式从B6的=SUM（B2:

B5），到C6成为=SUM（C2:

C5），到D6成为=SUM（D2:

D5），到E6成为=SUM（E2:

E5）。

见图3.28、图3.29。

图3.28图3.29

（3）绝对引用

然而，有些情况下，相对引用行不通。

例如图3表中，在C3单元统计少年投票的人数占全部投票人数的比例。

这时，必须指定公式为=B3/$B$7。

这里美元符$表示跟在其后面的引用值是不变的，即$B$7什么位置都代表B7单元格引用，这就是绝对引用。

这样，往下复制公式时，从C3的=B3/$B$7到C4时，变为=B4/$B$7（成了青年人数除以总人数。

注意，分子是相对引用，会变的；而分母的$B$7是绝对引用，永不改变的。

）；同理，复制到C5，公式变成=B5/$B$7（中年人数除以总人数）；复制到C6，公式变成=B6/$B$7（老年人数除以总人数）。

见图3.30及图3.31。

图3.30图3.31

（4）混合引用

细心的读者兴许会琢磨，上例中分母中的绝对引用，可否写成B$7。

这是对的，大家一验证便知。

这种只对列头或只对行号加$符号的式子就叫混合引用。

如A$3、$D4等。

混合引用既有相对引用的特点，又有绝对引用之特点。

在混合引用中，$加在数字前，对行的引用就限定不变；$加在字母前，对列的引用就限定不变。

请看下例，图3.32中C1单元有公式=A$1+$B1。

把此公式复制到C2时，相对位置发生变化是，列不变，行增1。

因此，$B1就变成$B2（值为4）；而A$1中由于绝对引用行限定成1，所以A$1仍是A$1（值为1）；这样，公式成为=A$1+$B2，值是5，见图3.33。

当把C1单元公式复制到D1时，相对变化是行不变，列增1。

换句话说，只需要改变引用中的字母部分。

于是对于A$1，应变成B$1（值为2）；对于$B1，绝对引用限定字母亦不能变，所以仍是$B1（值为2）。

这样，公式成为=B$1+$B1，值是4，见图3.34

图3.32图3.33图3.34

（5）三维引用其它工作表中的单元格

所谓三维引用，是指在同一工作薄中，一个表中的公式使用其它工作表中的单元格和区域。

要输入一个三维地址，以工作表名作为单元格地址的开始，以感叹号结束。

例如，工作表Sheet1有数据见图3.35，工作表Sheet2有数据见图3.36。

把光标定位于工作表Sheeet3上A1单元，输入公式：

=Sheet1!

A1+Sheet2!

B1。

这是将工作表Sheet1上A1单元的值（11）加上工作表Sheet2上B1单元的值（22）放在工作表Sheet3上的A1单元，结果为33。

图3.35图3.36

又将光标定位于工作表Sheet3上A2单元，输入公式：

=Sheet1!

A2+Sheet2!

B2。

这是将工作表Sheet1上A2单元的值（13）加上工作表Sheet2上B2单元的值（24）放在工作表sheet3上的A2单元，结果为37。

见图3.38。

图3.38

（6）引用其它工作薄的内容

当需要引用另外工作薄中工作表的内容时，如何办？

首先打开两个工作薄

例如，把“乒将成绩.XLS”（图3.38）和“乒将信息.XLS”两个工作薄（图3.39）都打开。

然后填写公式

引用另外工作薄上的内容，只要将其路径写在单引号里面，再使用相对引用、绝对引用和混合引用。

比如，我们要在“乒将信息”工作薄里把“乒将成绩”工作薄里的有关内容添加进来，就这么做：

将光标定位于“乒将信息”里Sheet1的D2，然后输入公式：

=A2&"曾荣获"&'E:

\excel例\[乒将成绩.xls]Sheet1'!

B2。

结果如图3.39。

图3.38

                                  图3.39

【思考题】

a.设有如下一个教材销售情况表，现要求给定C3一个公式，先得出销往北京的总金额，然后往右复制公式。

问D3中的公式应是什么？

b.在图3.39中，将D2公式复制到D3时，结果是什么？

练习题

判断

1.工作表中的列宽和行高是固定的。

（）

2.Excel中要打开存入磁盘上的一个工作簿，从"文件"菜单上选择"打开"命令。

（）

3.（Excel电子表格）执行SUM（A1∶A10）和SUM（A1，A10）这两个函数的结果是相同的。

（）

4.使用选择性粘贴，两单元格可实现加减乘除等算术运算。

（）

5.一个Excel工作簿中，不能超过255张工作表。

（）

6.中文Excel中要在A驱动器存入一个文件，从"另存为"对话框的保存位置下拉列表框中选A：

。

（）

7.如在单元格中输入的数据宽度大于单元格宽度时则无法显示。

（）

8.Excel在工具栏提供了一个"格式刷"按钮，可以利用它进行单元格的复制和移动。

（）

9.工作表中可以任意地删除单元格。

（）

10.若在单元格中输入了公式后，该单元格将显示公式计算的结果。

（）

11.在Excel中输入一个公式时，允许以等号开头。

（）

12.Excel单元格中的数据不能垂直居中，但可以水平居中。

（）

13.如果中文Excel要改变工作表的名字，只要单击选中的工作表的标签，此时屏幕显示一个对话框，在"名称"框中输入新的名字，按下"确定"按钮后即可。

（）

14.在Excel中查看或修改当前打印机的设置，可以从"文件"菜单中选择"打印"选项，打开"打印机"对话框。

（）

15.Excel建立工作表时，单元格的高度和宽度可以调整。

（）

16.Excel中所建立的图表，在工作表数据变化后，图表也随之改变。

（）

17.对于已经保存过的Excel文件，"文件"菜单下的"保存"和"另存为"这两个命令的作用是一样的。

（）

18.可以命名工作表的区域。

（）

19.在Excel中制作的表格可以插入到Word中。

（）

20.使用"图表向导"建立图表时，一般要经过4个步骤。

（）

21.Excel中，"撤消"选项可以撤消最近一次的操作。

（）

22.运算符号具有不同的优先级，并且这些优先级是不可改变的。

（）

23.同Windows的其他应用程序一样，Excel中必须先选择操作对象，然后才进行操作。

（）

24.Excel中单元格太窄不能显示数字时，将在单元格内显示问号。

（）

25.（Excel电子表格）Excel工作表中，不能改变单元格的宽度和高度。

（）

26.对于尚未保存过的Excel文件，"文件"菜单下的"保存"和"另存为"这两个命令的作用是一样的。

（）

27.Excel工作簿中所有图表都可以独自打印输出。

（）

28.（Excel电子表格）D2单元格中的公式为=a2+a3-c2，向下自动填充时，D4单元格的公式应为a4+a5-c4。

（）

29.在Excel的输入中按End键，光标插入点会移到单元格末尾。

（）

30.Excel中使用函数，既可以直接输入函数及参数，也可以使用插入菜单。

（）

31."编辑"菜单中的"剪切"命令可将所选数据转移到剪贴板上。

（）

32.在Excel公式栏中可进行编辑函数。

（）

33.在Excel工作表中，公式单元格显示的是公式计算的结果。

（）

34.COUNT（）函数的参数只可以是单元格、区域，不能是常数。

（）

35.（Excel电子表格）Excel中提供了在输入项前添加"'"的方法来区分是"数字字符串"而非"数字"数。

（）

36.在Excel中进行打印预览时，只能一页一页地查看。

（）

37.在Excel中，单元格是单元。

（）

38.Excel对每一个新建的工作簿，都采用"Book1"作为它的临时名字。

（）

39.在Excel"格式"工具栏中设有"左对齐"、"右对齐"、"居中"3个按钮。

（）

40.Excel中除了可用工具栏来改变数据的格式外，也同样可以选择"格式"菜单的"单元格格式"选项改变数据的格式。

（）

41.若当前工作表有Sheet1和Sheet2，在Sheet1工作表的B2单元输入"3月"，则在Sheet2工作表的B2单元格也出现"3月"。

（）

42.在"页面设置"对话框中，设有"页面"等三个标签。

（）

43.如果想输入的同时覆盖原来内容，可以使用键盘上的Ins键，可以在插入方式和改写方式间进行切换。

（）

44."零件1、零件2、零件3、零件4……"，不可以作为自动填充序列。

（）

45.（Excel电子表格）在Excel中，若使用"撤消"按钮，不能撤消最近一次以上的操作。

（）

46.EXCEL工作表中，日期时间在单元格的默认显示为靠右对齐。

（）

47.Excel电子表格）Excel97单元格中的数据可以水平居中，但不能垂直居中。

（）

48.中文Excel要打开存入磁盘上的一个工作薄，从文件菜单上选择"打开"命令。

（）

49.Excel工作表可使用剪贴板插入到WORD文档中，但不能用OLE插入到WORD文档。

（）

50.Excel在工具栏提供了一个"格式刷"按钮，可以利用它进行单元格格式的移动和复制。

（）

51.要关闭Excel，至少有3种方法。

（）

52.向EXCEL工作表中输入公式时，若在公式中夹有文本数据，应使用西文单引号将文本部分括住。

（）

53.退出中文Excel可利用"系统控制菜单"，只要先存储现有工作文件，然后单击"系统控制菜单"中的"关闭"命令。

（）

54.向EXCEL工作表中输入文本数据，若文本数据全由数字组成，应在数字前加一个西文单引号。

（）

55.EXCEL工作表中，文本数据在单元格的默认显示为靠右对齐。

（）

56.SUM（A1：

A3，5）的作用是求A1与A3两个单元格比值和5的和。

（）

57.EXCEL文档可转换为文本格式

58.（Excel电子表格）工作表中的列宽和行高是固定不变的。

（）

59.在Excel的帮助窗口中，双击某一个主题，即可得到子帮助主题，想查看正文，则可以再次双击该子主题。

（）

60.一个图表建立好后，其标题不能修改或添加。

（）

61.Excel工作表中，数值型数据在单元格的默认显示为左对齐。

（）

62.选择"编辑"菜单的"定位"选项，可以打开定位对话框。

在对话框的"引用"框中输入一个单元格地址，然后按回车键，指定单元格就会变为当前单元格。

（）

63.（Excel电子表格）在执行对图表的操作以前，必须选定图表，然后再将图表激活，可以双击选定的图表来激活该图表。

（）

64.单元格的地址是由所在的行和列决定，例如B5单元格在B行、5列。

（）

65.利用Excel"单元格内部直接编辑"功能，可以直接对一个单元格的内容进行编辑。

（）

66.当选择"文件"菜单的打印预览选项时，用鼠标单击"常用"工具栏的"打印预览"按钮，Excel将显示"打印预览"窗口。

（）

67.要启动Excel只能通过"开始"按钮这一种方法。

（）

68.（Excel电子表格）Excel中常用工具栏中的格式刷，不能复制数据，只能复制数据的格式。

（）

69.在Excel的使用工作中，可以单击鼠标右键打开快捷菜单。

（）

70.在工作表窗口的工具栏中有一个"Σ"自动求和按钮。

它代表了工作函数中的"SUM（）"函数。

（）

71.在Excel中，可以通过"编辑"菜单中的"替换"或"查找"命令来实现对单元格中数据的查找或替换操作。

（）

72.（Excel电子表格）Excel只提供了三种建立图表的方法。

（）

73.Excel中的图表是指将工作表中的数据用图形的方式表示出来。

（）

74.如果Excel的函数中有多个参数，必须以分号隔开。

（）

75.SUM（A1∶A3，5）的作用是求A1与A3两个单元格的和，再加上5。

（）

76.（Excel电子表格）Excel不能在Internet上使用。

（）

77.（Excel电子表格）"图表向导"方法可以生成不嵌入工作表的图表。

（）

78.修改Excel文档后，换名存盘，可以单击文件菜单中的"另存为"命令。

（）

79.（Excel电子表格）在Excel工作表中可以完成超过四个关键字的排序。

（）

80.Excel工作表中，时间日期在单元格的默认显示为右对齐。

（）

81.在Excel中，已为用户建立了多个函数，用户只能使用这些已建立好的函数，而不能自定义。

（）

82.Excel在输入公式过程中，总是使用运算符号来分割公式的项目，在公式中不能包括"空格"。

（）

83.中文Excel隐藏工作表可以先选定要隐藏的工作表，执行"格式"菜单中的"工作表"命令中的"隐藏"命令，此时选定的工作表将从屏幕上消失。

（）

84.在Excel中不允许使用四个方向键选择当前单元格。

（）

85.（Excel电子表格）在Excel工作中不能插入图形。

（）

86.（Excel电子表格）可以用填充柄执行单元格的复制操作。

（）

87.在选中某单元格或某个单元格范围后，可以按"Del"键来删除单元格内容。

（）

88.公式单元格中显示的是公式，而且公式计算的结果显示在下面的单元格。

（）

89.要关闭Excel，不少于3种方法。

（）

90.在Excel中，行高不能改变，但列宽可以增加。

（）

91.在数据粘贴过程中，如果目的地已经有数据，则Excel会请示是否将目的地的数据后移。

（）

92.Excel工作表中，文本数据在单元格的默认显示为右对齐。

（）

93.Excel工作簿的表格称为"列表"，"列表"是一个二维数据表格。

（）

94."编辑"菜单中的"粘贴"命令可以将剪贴板上的内容放入到工作表中。

（）

95.Excel允许同时打开多个工作簿，但一次只能激活一个工作簿窗口。

（）

96.查找功能不能查找出哪些单元格中包含了指定输入字符串。

（）

97.使用"重复"按钮允许用户重复执行前17次执行的操作。

（）

98.若在Excel工作表中输入文本数据，如果文本数据全由数字组成，应在数字前加一个西文单引号。

（）

99.折线图是Excel默认的图表类型。

（）

100.若要把A4单元格中的89000改为89780，只要单击该单元格，在其中插入光标，并将其插入点移至9后面，按两次Del键后删除数字9后面的两个0，然后输入数字78，按回车键后确认修改。

（）

101.一个函数的参数可以为函数。

（）

102.Excel中单元格的宽度是固定的，为8个字符宽度。

（）

103.要进行数据查找，可在"工具"菜单中选择"查找"选项，屏幕显示"查找"对话框，在对话框中的"查找内容"框中输入查找内容就可以了。

（）

104.在Excel工作表中不能插入行。

（）

105.在Excel中，日期和时间是独立的一种数据类型，不可以包含到其他运算中，也不可以相加减。

（）

106.启动Excel后，会产生一个名为BOOK1.DOC的文件。

（）

107.Excel中的"拼写检查"选项允许检查工作表和图表，以便发现是否有拼写错误。

（）

108.Excel中没有自动保存和自动填充功能。

（）

109.Excel中可以按需要改变单元格的宽度和高度。

（）

110.在Excel中，输入的文本可以为数字、空格和非数字字符的组合。

（）

111.在"单元格格式"的"图案"标签对话框中，单击对话框的"图案"列表框，将会看到24种图案的列表和一个调色板。

（）

112.工作表是Excel的主体部分，共有256列，65536行，因此，