第3章电子表格软件Excel.docx

第3章电子表格软件Excel.docx

《第3章电子表格软件Excel.docx》由会员分享，可在线阅读，更多相关《第3章电子表格软件Excel.docx（22页珍藏版）》请在冰豆网上搜索。

第3章电子表格软件Excel

第3章电子表格软件Excel67

3．1Excel2003的功能简介67

3．2电子表格的基本概念68

3．3数据的输入70

3．4使用公式与函数72

3．4．1使用公式73

3．4．2使用函数75

3．5单元格格式化处理76

3．5．1格式化处理方法76

3．5．2常用格式化操作。

77

3．6图表创建80

3．6．1图表的创建81

3．6．2图表的编辑82

3．7数据管理83

3．7．1数据清单83

3．7．2数据排序83

3．7．3数据筛选85

3．7．4分类汇总86

3．7．5数据透视表87

第3章电子表格软件Excel

3．1Excel2003的功能简介

电子表格软件Excel是微软公司Office办公集成软件的一个组成部分。

它能制作出各种电子表格，使用公式和函数对数据进行分析和运算；并可以用各种图表来表示数据。

其主要功能是：

1．制作电子表格

当启动Excel软件之后，屏幕显示由横竖线组成的空白电子表格，可在其中直接填入数据形成各种表格。

其中的单元格是Excel中数据处理的基本组成单位。

用户可以在单元格中进行各类数据（数字型、文本型、日期型、时间型、逻辑型等）和公式（四则运算、函数等）的输入和修改，还可以方便地对表格的行、列或单元格进行插入和删除；对单元格的格式（字符的字体、字型、字号、颜色、数值的格式、边框和底纹等）也可进行设置。

2．数据处理和管理

Excel软件会自动区分各类数据类型。

通过工具按钮或菜单选项，可实现数据的排序、筛选和分类汇总等操作。

利用系统提供的函数还可以完成各种数据分析，例如统计函数、规划求解、数据透视、方差分析、回归和统计中的各种检验等。

3．图表功能

Excel软件提供了14类100多种图表，包括柱形图、饼图、条形图、面积图、折线图、气泡图以及三维图等。

用户可将电子表格中的数据以图形的形式表示，并可编辑图表中的各种对象。

4．插入各种对象

Excel软件可插入各种对象，例如各种图片、声音等对象。

Excel2003在以上功能的基础上，增加了许多新功能。

（1）并排比较工作簿：

用户可方便地查看两个工作簿之间的差异，而不必将所有更改合并到一张工作簿中。

（2）改进的统计函数：

例如统计函数的许多特性（包括取整和精度）进行了改进。

（3）XML支持、智能文档、文档工作区和信息权限管理等。

3．2电子表格的基本概念

Excel软件的启动和退出的方法与启动和退出其他软件的方法相同。

Excel的窗口构成如图31所示。

与Word窗口的构成基本相同，都有标题栏、菜单栏、常用工具栏、格式栏、状态栏。

不同的地方如下：

图31Excel的窗口

1．工作簿

Excel文件称为工作簿，它用于保存工作表和图表，其扩展名为.xls。

在默认情况下，一个工作簿文件有3个工作表，用户可以根据需要增加或删除工作表。

每个工作表都有一个名字，称为标签，显示在窗口底部。

工作表标签默认为Sheet1,Sheet2,Sheet3等，可以改名。

在任一时刻只显示一个工作表，该工作表称为当前工作表。

当前工作表的标签高亮显示。

单击工作表标签选择为当前工作表。

若当前工作簿中还有工作表没有显示出来，可通过标签滚动按钮

使工作表标签滚出，再选为当前工作表。

要新建、删除或重命名工作表，可右击工作表标签，在打开的快捷菜单中选择“插入|删除|重命名”命令；移动或复制工作表用鼠标拖动选定的工作表标签或按住Ctrl键的同时拖动工作表即可。

在Excel中新建、打开和保存工作簿的操作，与Word中的操作基本相同。

2．工作区与单元格

Excel的工作区位于编辑栏的下方，由工作表区域、滚动条和工作表标签组成。

每个工作表最多可以有65536行（行号1~65536）和256列（列号A,B,C,…,Z,AA,AB…IV）组成，其中行和列交叉处为单元格，输入的数据保存在单元格中，每个单元格可容纳32767个字符。

在单元格中只能显示1024个字符，而在编辑栏中可显示全部32767个字符（可以在Excel的“帮助”中查找“Excel规范与限制”查看相关内容）。

单元格的地址由其所在的列号和行号标识。

如“E3”表示位于第E列第3行的单元格。

为了区分不同工作表中的单元格，在地址前加工作表名称，如“Sheet1!

E3”表示“Sheet1”工作表的“E3”单元格。

3．当前单元格与单元格区域的选取

要想使用哪个单元格，先要选取该单元格。

被选取的当前单元格由深色粗框线框起，被选取的单元格区域除左上角单元格（当前单元格）外均以黑底反白显示。

在所选单元格或区域的右下角有一个小方块（称为填充柄），用鼠标向外拖动可扩展或填充单元格区域，向内拖动则清除部分区域。

选取单元格或单元格区域的方法如下：

（1）单个单元格：

单击相应的单元格，或用方向键移动到相应的单元格。

（2）某个单元格区域：

单击选定该区域的第一个单元格，然后拖动鼠标直至选定最后一个单元格。

（3）工作表中所有单元格：

单击全选按钮。

（4）不相邻的单元格或单元格区域：

先选定第一个单元格或单元格区域，然后按住Ctrl键再选定其他的单元格或单元格区域。

（5）整行：

单击行号。

（6）整列：

单击列标。

（7）相邻的行或列：

沿行号或列标拖动鼠标。

（8）不相邻的行或列：

先选定第一行或第一列，然后按住Ctrl键再选定其他的行或列。

4．编辑栏

编辑栏用于显示工作表中当前单元格的地址及数据。

编辑栏左边为“名称框”

，显示当前单元格的地址。

当向单元格输入数据或进行数据修改时，在编辑栏中出现“取消”

、“输入”

和“编辑公式”

三个按钮，并将输入的数据或公式显示在编辑栏的右边。

单击编辑栏中的“取消”按钮或按Esc键，可取消本次输入，单击编辑栏中的“输入”按钮或按Enter键确认用户输入单元格的内容，单击“编辑公式”按钮将出现“插入函数”对话框，帮助用户输入函数。

3．3数据的输入

要向单元格输入数据，首先要单击该单元格使其成为当前单元格，然后输入数据。

数据可从键盘输入，也可自动输入。

Excel对输入的数据会自动区分类型。

在默认情况下，数值型数据右对齐、文本型数据左对齐。

1．文本输入

文本包括汉字、英文字母、数字、空格及所有键盘能输入的符号。

文本输入后在单元格中左对齐。

有时为了将学号、电话号码、邮政编码等数字作为文本处理，在输入时要在数字前加上一个单引号。

文本输入超出单元格的宽度时，将扩展到右列；若右列已有数据，将被截断显示。

2．数值输入

数值类型的数据包括0～9、+、-、E、e、￥、%、小数点（.）和千分位（,）。

数值输入后在单元格中自动右对齐。

若数值输入过长，则以科学记数法显示。

如123451234512，则显示1.23E+11。

单元格中的数值保留15位有效数字。

若小数输入超出设定的位数，将从超出位四舍五入显示。

3．日期时间数据输入

输入日期的格式为：

“yy/mm/dd”或“yy-mm-dd”。

输入时间格式为：

“hh：

mm（AM/PM）”。

其中表示时间时在AM/PM与分钟之间应有空格，比如7：

20PM，缺少空格将被当作字符数据处理。

4．分数输入

为了和日期类型数据相区分。

输入分数时前加0和空格，如：

03/4。

5．“自动填充”输入

用鼠标拖曳单元格右下角的填充柄可以实现有规律的数据的输入，如等差、等比、系统定义的序列以及用户自定义的序列。

填充相同的数据（相当于复制数据）：

选中一个单元格或区域，往水平或垂直方向直接拖曳填充柄即可。

填充序列数据：

通过“编辑|填充|序列”命令，打开“序列”对话框，如图32所示，可以进行等差序列、等比序列、日期等的输入。

图32“序列”对话框

例如要输入一个等差数列2,4,6,…，首先在选定起始的单元格内输入数据2，然后用鼠标水平或垂直方向选中复制范围，打开“序列”对话框，选择等差序列，填入步长值2，按“确定”按钮即可。

也可以先输入这列数据开始的两个数据2和4，然后选中这两个单元格，将鼠标箭头移向区域右下角的填充柄，按下左键拖曳到最后一个单元格即可。

填充系统定义的序列或用户自定义序列：

通过选择“工具|选项”命令，打开“选项”对话框，选择“自定义序列”选项卡，如图33所示。

用户可添加新序列或修改已有的自定义序列。

使用时只要在单元格中输入序列中的任何一个，即可用拖曳填充柄的方法完成序列中数据的循环输入。

图33“自定义序列”选项卡

用户添加自定义序列的方法是在“自定义序列”选项卡的输入序列框中输入自定义的数据，并在每个输入的数据后面加回车，最后单击“添加”按钮，再按“确定”按钮保存。

6．有效性输入

在输入数据之前，可利用“数据|有效性”命令进行数据有效范围的设置，以防止在数据输入时输入不合法的数据。

例如：

输入的日期应大于等于2000/1/1小于或等于2050/1/1，则有效性输入设置如图34所示。

图34“数据有效性”对话框

3．4使用公式与函数

Excel软件的强大功能体现在数值计算上。

通过公式和函数可以对表中数据进行总计、平均、汇总以及其他复杂的运算，可以建立工作簿、表、单元格间的运算关系，而且如果原始数据改变，公式计算结果将会自动改变。

3．4．1使用公式

公式是以“＝”开头，由运算符、单元格地址、常量和函数组成。

在公式中可以使用的运算符如表31所示。

表31运算符

运算符

结果

举例

算术运算符

数值

加+、减-、乘*、除/、百分号%、乘方^

关系运算符

真/假

等于=、大于>、小于<、大于等于>=、小于等于<=、不等于<>

文本运算符

文本

连接&

引用运算符

区域：

（A1:

C3表示A1到C3两个引用之间的9个单元格）

联合，（将多个引用合并为一个引用，如A1:

C3，E1:

F3表示15个单元格的引用）

运算符的优先顺序由高到低为：

区域：

、联合，、负号-、（百分号%、乘方^）、（乘*，除/）、（加+、减-）、连接&、（等于=、大于>、小于<、大于等于>=、小于等于<=、不等于<>）。

注意：

括号中的运算符的优先级相同。

优先级相同时，按由左向右顺序计算。

1．输入公式

选择要输入公式的单元格，然后在单元格或编辑栏中输入公式。

输入公式时，在编辑栏和单元格内都显示公式。

公式编辑完后按Enter键，单元格显示公式的计算结果，而编辑栏显示公式。

修改公式则单击公式所在的单元格，在编辑栏上对公式进行编辑。

例如，对图35所示的部分学生数据计算总评成绩。

总评成绩由两部分构成：

平时成绩的20％和期末成绩的80％。

在编辑栏或D2单元格内输入=B2*0.2+C2*0.8，然后按Enter键或

按钮，其余人的成绩可利用复制公式的方式完成。

图35公式输入

2．复制公式

在计算完D2后还要计算D3和D4，为了快速计算，可以使用公式复制来完成计算。

方法一：

使用“选择性粘贴”复制公式

具体操作步骤是复制一个有完整公式的单元格，并将公式粘贴到目标单元格中。

（1）单击D2单元格。

（2）选择“编辑|复制”命令。

（3）选中D3：

D4单元格。

（4）选择“编辑|选择性粘贴”命令，弹出“选择性粘贴”对话框，如图36所示。

（5）在“粘贴”选项组中选择“公式”，然后单击“确定”按钮。

图36“选择性粘贴”对话框

方法二：

填充复制公式

用鼠标拖动复制的方法快速填充公式，Excel自动更改公式中的单元格引用，操作步骤为：

（1）选中带公式的单元格，如D2。

（2）将鼠标指针指向D2单元格右下角的填充柄，指针变成一个小“+”字形，按住鼠标左键向D4方向拖动，即实现了公式的复制。

3．公式中单元格的引用

Excel的单元格引用有两种基本方式：

相对引用和绝对引用。

（1）相对引用

相对引用是指引用的单元格地址会随公式所在的单元格的位置变化而改变。

也就是当把一个含有单元格地址的公式复制到一个新的位置时，公式中的单元格地址会随之改变。

例如在上图35中，D2单元格中的公式“=B2*0.2+C2*0.8”复制到D3单元格后，D3中公式变为“=B3*0.2+C3*0.8”，其结果也相应计算出来。

（2）绝对引用

公式中的绝对引用不会随目标单元格地址的改变而变化。

在行号或列号前面添加“$”代表绝对引用。

如图37中，计算C3到C6的8折后的价格，计算中都要引用一个不变的单元格B8，在C3单元格中输入“=B2*$B$8”，“$B$8”就是单元格B8的绝对引用，它在公式复制中始终保持不变。

图37单元格的绝对引用

3．4．2使用函数

Excel提供了许多内置函数，包括：

常用、财务、日期与时间、数学与三角函数、统计、文本、逻辑函数等。

函数包含两部分：

函数名和参数表。

语法形式为：

函数名（参数1，参数2，…），其中参数可以是常量、单元格、单元格区域或其他函数。

函数的输入有以下两种方法：

（1）直接输入法：

在“=”后直接输入函数名和参数。

例如“=sum（A1：

C1）”，此方法要求函数名和参数输入要正确。

（2）使用函数向导：

一般先选中要输出结果的单元格，然后通过“插入|函数”命令或单击“自动求和”

旁的箭头，选择“其他函数”菜单项，打开“插入函数”对话框，如图38所示。

选择函数和参数，单击“确定”。

图38“插入函数”对话框

Excel常用函数有以下一些：

（1）求和函数：

=SUM（number1,number2，…），返回其参数的和。

（2）平均值函数：

=AVERAGE（number1,number2，…），返回其参数的算术平均值。

（3）计数函数：

=COUNT（value1,value2,…），求计算包含数字的单元格以及参数列表中的数字的个数。

（4）最大值函数：

=MAX（number1,number2，…），返回一组数值中的最大值，忽略逻辑值及文本。

（5）标准偏差：

=STDEV（number1,number2，…），计算给定样本的标准偏差（忽略样本中的逻辑值及文本）。

注意：

以上函数中的参数number1,number2等可以是数值，也可以是包含数值的引用，如SUM（A1:

C1）。

（6）IF函数：

=IF（logical_test，value_if_true，value_if_false），创建条件公式。

IF函数使用下列参数：

logical_test：

要选取的条件（逻辑表达式，它能产生逻辑值（TRUE或FALSE））。

value_if_true：

条件为真时返回的值。

value_if_false：

条件为假时返回的值。

例如，公式＝IF（A1>=60，“及格”，“不及格”），表示对Al单元格中的数据进行测试。

如果其值大于或等于60，返回值为“及格”字符串，否则，返回值为“不及格”字符串，如图39所示。

图39使用IF函数

3．5单元格格式化处理

单元格的格式是指单元格内容的文本和数字格式、对齐方式、行高和列宽、单元格的边框、底纹图案以及单元格的保护方式等信息。

3．5．1格式化处理方法

单元格格式化可以通过下面四种方法进行格式设置。

（1）使用“格式”工具栏，如图310所示。

可通过直接用鼠标单击这些按钮来进行相应的格式化操作。

图310“格式”工具栏

（2）用鼠标单击“格式|单元格”命令或用鼠标右击选择快捷菜单中“设置单元格格式”命令，打开“单元格格式”对话框，如图311所示，它有6个选项卡，分别为：

数字、对齐、字体、边框、图案和保护等。

用户可进行相应的单元格数据的格式设置。

图311“单元格格式”对话框

（3）使用“格式|自动套用格式”命令，使用系统预定义的制表格式。

（4）使用“格式|条件格式”命令，可根据条件设置有关的格式。

3．5．2常用格式化操作。

1．设置“对齐”格式

在默认情况下，Excel自动将文字内容向左对齐、数值内容向右对齐。

用户也可利用“单元格格式”对话框的“对齐”选项卡设置单元格的对齐格式，如图312所示。

图312“对齐”选项卡

其中：

“水平对齐”下拉列表框包括常规、靠左、居中、靠右、填充、两端对齐、跨列居中、分散对齐。

“垂直对齐”下拉列表框包括靠上、居中、靠下、两端对齐、分散对齐。

“自动换行”表示对输入的文本根据单元格列宽自动换行。

“缩小字体填充”表示减小单元格中的字符大小，使数据的宽度与列宽相同。

“合并单元格”表示将多个单元格合并为一个单元格，合并前左上角单元格的内容为合并后单元格的内容。

在实际的应用中通常选择“格式”工具栏中的“合并及居中”按钮

来合并单元格。

它还具有取消一组单元格合并的功能。

“方向”框用来改变单元格中文本旋转的角度，还可以在文本框下面的“度”文本框中输入文本旋转的度数以改变文本的方向。

2．设置边框和底纹

设置边框和底纹可使工作表的重点内容突出显示出来，还可以区分工作表不同部分并且更加美观和容易阅读。

（1）设置边框

在默认情况下，Excel并不为单元格设置边框，工作表中的网格线在打印时，虽然可以选择打印显示，但是显示后的效果是整个页面都有线条。

不能突出显示表格部分，所以我们通常不会打印网格线，而是通过“边框”命令来修饰表格。

首先选择要设置边框的所有单元格，然后利用“单元格格式”对话框的“边框”选项卡设置边框，如图313所示。

图313“边框”选项卡

先在“线条”选项组的“样式”列表框中选择线条样式，在“颜色”下拉列表框中选择边框颜色。

然后在“预置”选项组的“外边框”和“内部”按钮，或“边框”选项组中的相应的边框按钮，完成添加或删除边框，并可在预览框中看到边框应用的效果，边框会同文本的旋转而旋转。

（2）设置底纹

使用底纹可以给单元格加上色彩和图案。

先选择要设置底纹的所有单元格，然后在“单元格格式”对话框中选择“图案”选项卡。

在“颜色”列表中选择所需的颜色，或在“图案”下拉列表框中，选择所需的图案样式和颜色。

3．取消/显示网格线

选择“工具|选项”命令，弹出“选项”对话框。

选择“视图”选项卡，单击“窗口选项”中的“网格线”的复选框，如图314所示。

图314“选项”对话框

4．行高与列宽设置

在默认情况下，当单元格中输入的字符串超过列宽时，超出的文字被隐藏，数字则用“#######”表示。

当然，完整的数据还在单元格中，只是没有显示。

因此可以调整行高和列宽，以便于显示完整的数据。

（1）快速调整

将鼠标指向列标（或行号）的分隔线，当鼠标指针变成双箭头

（

）时，拖动鼠标至适当宽度，或直接双击鼠标左键即可完成宽度的调整。

（2）精确调整

选择要调整的单元格区域，用鼠标单击“格式”菜单中的“列”或“行”子菜单进行设置：

选择“列宽”或“行高”，显示其对话框，输入所需的宽度或高度。

选择“最合适的列宽”命令，取选定列中最宽的数据为宽度自动调整。

选择“最合适的行高”命令，取选定行中最高的数据为高度自动调整。

选择“隐藏”命令，将选定的列或行隐藏。

选择“取消隐藏”命令，将隐藏的列或行重新显示。

5．设置数字的格式

在“单元格格式”对话框的“数字”选项卡的“分类”列表中选择合适的格式，如图315所示。

图315“数字”选项卡

6．设置条件格式

选择“格式|条件格式”打开“条件格式”对话框，其中提供了最多用3个不同表达式设置不同的格式。

如图316，对小于60、大于90分的成绩以不同的格式显示。

对已设置的条件格式，可以利用“删除”按钮进行格式删除；利用“添加”按钮进行条件格式的添加。

图316“条件格式”对话框

3．6图表创建

Excel中的图表类型共有十四种，包括柱形图、饼图、条形图、面积图、折线图、气泡图以及三维图等，每一类又有若干种子类型。

用户可将电子表格中的数据以图形的形式表现出来，并可编辑图表中的各种对象。

创建图表有两种方式，一是对选定的数据源直接按F11键快速创建图表；二是利用图表向导创建图表。

3．6．1图表的创建

创建图表的步骤如下：

（1）首先选择要在图表中使用数据的单元格区域，在本例中选图317中的学号、英语、数学三列数据。

图317选中的数据源

（2）选择“插入|图表”命令，或单击“常用”工具栏中“图表向导”按钮

，在“图表类型”对话框中选择图表类型和子类型。

（3）选择图表的数据区域。

如果选择“行”单选项，则使数据图表以横向排定数据，选择“列”单选项，则使数据图表以纵向排定数据。

（4）在“标题”选项卡上的“图表标题”、“分类（X）轴”、“数值（Y）轴”等文本框中输入相应的说明性文字，还可以选择其他选项卡进行设置。

（5）图表放置的位置。

选择“作为新工作表插入”单选项，并键入新工作表的名称，将生成一个独立图表工作表。

选择“作为其中对象插入”单选项，将在图表数据源所在工作表中插入图表，即生成一个“嵌入式图表”，如图318所示。

图318生成的图表

3．6．2图表的编辑

图表编辑是指对图表中各个对象的编辑，包括数据的增加、删除等。

图表的编辑一般有两种方法：

一是通过指向图表的快捷菜单进行编辑；二是通过“图表”工具栏进行编辑。

选择“视图|工具栏|图表”命令，可打开“图表”工具栏，如图319所示。

图319“图表”工具栏

1．图表对象

图表中的对象由图表区、绘图区、图表标题、数值轴、分类轴、网络线、图例、数据系列等组成，如图318所示。

图表对象的选择方法：

通过单击“图表”工具栏的“图表对象”下拉按钮，选择某对象，或者用鼠标直接单击某个图表对象。

2．图表中数据的编辑

图表与生成它们的数据是相链接的，如果工作表中的数据源发生了变化，则图表中的对应数据也会自动更新。

删除数据系列：

如果要同时删除工作表和图表中的数据，只需删除工作表中的数据，图表将自动更新。

如果希望删除图表中的数据系列，而保持工作表中的数据，用鼠标在绘图区内单击需删除的数据系列，按Delete键即可。

添加数据系列：

对嵌入式图表，只要选中要添加的数据区域，然后用鼠标指向单元格区域的边缘。

当鼠标指针变为箭头时，按住鼠标左键，将单元格区域拖到图表中即可。

对独立图表可通过选择“图表|添加数据”命令进行。

3．图表对象的格式化

将鼠标指向要格式化的对象，单击右键，选择快捷菜单中该对象的格式设置，包括图案、文字和对齐、位置、颜色等。

不同的对象可有不同的格式设置选项。

3．7数据管理

3．7．1数据清单

数据清单是工作表中的单元格构成的矩形区域。

建立数据清单可以对数据进行排序、筛选、分类汇总等操作。

数据清单有以下特点：

（1）在一个工作表上只能建立一个数据清单。

数据清单的第一行是列标志，用来给每列命名。

（2）数据清单中的一列称为一个字段，一行称为一条记录。

列标志（也称字段名）必须在数据清单的第一行。

（3）每一列必须是类型相同的数据，不能有完全相同的两行内容，不允许有空行或空列。

数据清单与其他的数据之间至少留出一个空白列和一个空白行。

数据清单既可像一般工作表一样直接进行建立和编辑，也可通过“数据|记录单”命令以记录为单位进行新建、删除、移动、修改和数据查询等操作，如图320所示。

图320数据编辑

3．7．2数据排序

数据排序是把一列或多列无序的数据变为有序的数据。

可以根据一列或多列中的数据值进行排序，也可以按照某行中的数值对列排序。

对英文字母，按字母次序（默认不区分大小写）排序，汉字可按拼音或笔画排序。

1．简单排序

简单排序是指用一个字段按升序或降序排列，可以利用“常用”工具栏中的“升序”

或“降序”

按钮快速地实现，也可通过“数据|排序”