第4章 电子表格Excel制作的应用.docx

第4章 电子表格Excel制作的应用.docx

《第4章 电子表格Excel制作的应用.docx》由会员分享，可在线阅读，更多相关《第4章 电子表格Excel制作的应用.docx（127页珍藏版）》请在冰豆网上搜索。

第4章电子表格Excel制作的应用

第4章电子表格Excel制作的应用

Excel是Office的重要组成部分，是一种功能强大的表格处理软件，用户可以在Excel中方便快捷地输入和修改数据，进行数据的存储、查询、统计等操作，同时还具有智能化的管理和计算数据能力。

Excel既可用于个人事物处理，也可广泛用于财会、审计、统计和数据分析等领域。

通过本章的学习，主要掌握电子表格的建立与格式化，表格的数据操作（编辑、计算、排序、筛选、分类汇总、透视表），图表操作，多表操作，安全性设置。

4.1Excel基础知识

在使用一种软件之前，首先要启动该软件，然后了解各部分的功能及界面的组成等。

4.1.1Excel概述

Excel可以用来制作电子表格，完成复杂的数学运算，对数据进行分析与预测，并且具有强大的图表显示功能。

1．强大的列表功能

Excel可以将用户输入的数据形成列表，而且还可以把工作表中的全部或者一部分指定为列表，用户可以方便地管理和分析列表中的数据而不用担心列表之外的其他数据。

在列表中用户可以完成各种数据计算、数据排序和汇总等操作。

2．数据计算功能

Excel提供了功能强大的公式和函数，可以对用户输入的数据进行复杂的运算。

3．数据分析统计功能

Excel提供了对数据进行排序、筛选、汇总等功能，同时还提供了数据透视表和数据透视图，使得用户可以通过自行设置行与列的关系查看源数据的不同汇总情况，同时还可以根据需要显示区域的明细数据。

4．数据图表功能

Excel提供了将工作表中的数据能各种统计图表的形式显示出来的功能，可以使复杂的数据更加生动，更能清晰地显示出数据的趋势和走向，方便数据的分析和预测。

当工作表中的数据发生变化时，图表中对应的数据也会随之自动更新。

5．XML支持

Excel提供了更广泛的支持，使用户可以更容易地共享数据。

利用XML，用户不但可以将工作表保存为XML表格或XML数据，而且还可以在任何客户定义的XML架构中读取数据，当XML数据变化时，可动态更新图表、表格和曲线图。

4.1.2Excel的窗口组成

使用Excel时应先启动它，然后才能制作各种电子表格。

工作完成后，应退出Excel。

在退出Excel前，需要保存自己的电子表格。

启动Excel的方法有：

●直接双击桌面上的快捷图标

。

●选择【开始】→【程序】→MicrosoftOffice→Excel命令。

●单击已存在的Excel文件。

启动后的Excel窗口如图4－1所示：

图4-1Excel的窗口

（1）名称框：

显示活动的单元格区域。

（2）填充柄：

拖动该柄可以向拖动方向单元格填充数据。

（3）活动的单元格：

对单元格进行操作的前提条件是使单元格成为活动单元格才能实现操作。

（4）行标志序号：

表示行顺序号。

（5）按钮组：

用于显示隐藏的工作表标签。

（6）工作表标签：

工作簿中包含的工作表标签列表。

（7）标题栏：

用来显示文档的名称，此处显示的是“Book1”。

（8）菜单栏：

Excel文档编辑的所有菜单列表。

（9）常用工具栏：

常用命令按钮，单击该按钮即可执行该命令。

（10）文本编辑框：

用于编辑单元格的内容。

（11）列标志序号：

表示列顺序号。

（12）状态栏：

显示当前可操作的状态等。

（13）格式工具栏：

用于设置被选中内容的格式。

（14）任务窗格：

集合了最常用的任务，如新建、帮助和搜索等。

（15）垂直滚动条：

用以显示垂直被遮挡部分。

（16）搜索框：

输入要寻找的搜索。

（17）水平滚动条：

用以显示水平被遮挡部分。

（18）表格编辑区：

当前工作表编辑区。

4.1.3Excel基本对象

打开一个Excel文件时，会同时存有工作簿、工作表和单元格。

1．工作簿

工作簿是用于存储并处理工作数据的文件，是Excel中存储数据的基本单位。

通常据说的Excel文件指的就是工作簿文件。

在Excel中各种数据都是以工作表的形式存储在工作簿中的，一个工作簿最多可以有255个工作表。

启动Excel后，系统会自动创建一个新的、空白工作簿，Excel给它赋予一个临时的名字“Book1”，其扩展文件名为“.xls”。

2．工作表

工作表是工作簿的重要组成部分，是Excel中用来处理和存储数据的主要文档，也称为电子表格。

当用户Excel时，首先见到的就是工作表，它由许多排列整齐的单元格组成。

一张工作表有65536个行、256个列。

行号用数字区分，从1到65536；列号用26个英文字母进行编号，列号从A到IV。

一个工作簿中可以包含多张工作表。

一般来说，一张工作表保存一类相关的信息，在一个工作簿中可以管理多个类型的相关信息。

新建一个工作簿时，Excel默认提供了3个工作表，其名字分别是Sheet1，Sheet2和Sheet3，分别显示在工作表标签中。

但在某一时刻，用户只能在一张工作表上进行工作，即只有一个工作表处于活动的状态。

通常把该工作表称为活动工作表或当前工作表，其工作表标签以反白显示，名称下方有单下划线。

在实际工作中，用户可以根据自己的需要创建更多的工作表。

3．单元格

每张工作表中由256列和65536行组成，列和行交叉形成的每个网格称为一个单元格。

每一列列标由A，B，C…表示，每一行行号由1，2，3…表示，每个单元格的位置由交叉的列、行名表示。

每张工作表都由许多的长方形小格子构成，称为单元格。

单元格是Excel中最小的操作单位，用户可以在单元格中输入任何数据，如字符串、公式和函数等。

单元格由纵横直线相交而成，因此可以由它们的行和列的位置来命名。

如，D2表示第二行与第四列交叉的单元格。

工作表中只有一个单元格为当前工作的单元格，称为活动单元格，屏幕上带粗线黑框的单元格就是活动单元格，此时可以在该单元格中输入和编辑数据。

在活动单元格的右下角有一个小黑方块，称为填充柄，利用此填充柄可以填充某个单元格区域的内容。

4．单元格区域

单元格区域是指一组被选中的单元格。

一个单元格区域可以小到只有两个单元格，也可以大到整个工作表，单元格区域中的单元格可以相邻，也可以是彼此分离的。

对一个单元格区域的操作就是对该区域中所有的单元格执行相同的操作。

在Excel中可以使用绝对和相对两种方式来引用单元格区域。

4.2Excel基本操作技巧

对工作表编辑主要是针对单元格、行、列以及整个工作表进行的包括撤消、恢复、复制、粘贴、移动、插入、删除、查找和替换等操作。

4.2.1选定单元格

单元格是组成工作表的基本单位，也是Excel中最小的数据处理单位，因此Excel中很多操作都是基于单元格的。

熟悉对单元格的编辑操作，有利于建立一个更完好的工作表。

对单元格进行操作之前，首先必须选择单元格。

选择单元格的方法很多，主要有下面几种。

1．选定一个单元格

只要在工作表中单击任意一人单元格就可以选定这个单元格。

当单元格被选定时会被一个黑色边框包围，其所在的行列号均突出显示，并且在名称框中还会显示其名称。

如图4-2所示。

图4-2选定单元格

2．选定连续的多个单元格

单击选定该连续单元格区域左上角的单元格，然后按住鼠标左键，拖动鼠标到单元格区域右下角单元格就可以选定这一单元格区域。

如图4-3所示。

图4-3选定连续的多个单元格

小贴士：

利用键盘选定单元格

1）选定要选择范围的第一个单元格上。

2）按住Shift键，然后按相应的箭头键选择该范围的其他单元格。

3．选定非连续的多个单元格

选定不连续的多个单元格，只需单击选定一个单元格，然后按住Ctrl键再单击其他单元格即可。

如图4-4所示。

图4-4选定非连续的多个单元格

4．选定整行单元格

把鼠标移动到所选行左边的行号处，当鼠标变成向右黑色实心箭头时，单击即可选定该行。

5．选定整列单元格

把鼠标移动到所选列顶端的列号处，当鼠标变成向下黑色实心箭头时，单击即可选定该列。

6．选定全部单元格

把鼠标移动到行号1顶端或者是列号A左侧的单元格上，单击即可选定所有的单元格。

如图4-5所示。

图4-5选取所有单元格（整个工作表）

小贴士：

选择多行或多列时，如果行号或列的字母编号不是连续的，则说明表格中可以包含隐藏的行或列。

如果对表格中的可见部分执行清除或删除操作，也会清除或删除隐藏部分中的内容。

如图4-6所示。

图4-6选取多行

4.2.2移动、复制与粘贴单元格数据

移动和复制单元格数据可以有效地使用已经存在的单元格内的数据，在有些情况下可以极大地减少工作量，提高工作效率。

复制与粘贴是一个互相关联的操作，一般来说复制的目的是为了粘贴，而粘贴的前提是要先复制（剪贴也是复制的一种形式）。

1.移动单元格数据

在对单元格进行编辑的时候，经常要把单元格中的数据移动到其他位置。

移动单元格数据的方法很多，下面主要介绍使用鼠标和菜单命令。

1）使用菜单命令移动：

使用【编辑】→【剪切】命令也可以实现数据的移动，具体方法如下：

选定要移动数据的单元格。

选择菜单栏上【编辑】→【剪切】命令（或按快捷键Ctrl+X），然后选定目标单元格，执行【编辑】→【粘贴】命令（或按快捷键Ctrl+V），就可以实现数据的移动。

小贴士：

移动数据，还可以单击常用工具栏上的【剪切】按钮，或右击单元格，在弹出的快捷菜单上选择【剪切】命令。

同样可以单击常用工具栏上的【粘贴】按钮，或右击目标单元格，在弹出的快捷菜单中选择【粘贴】命令，都可以实现数据的移动。

2）使用鼠标移动：

使用鼠标移动单元格数据是最常用的一种方法，它操作起来简单方便，具体方法如下。

选定要移动数据的单元格，将鼠标移动到该单元格的底部，当鼠标指针变为向左上方的箭头，并且下方出现四个方向的小箭头时，拖动鼠标到目标单元格，释放鼠标，即可把数据移动到目标单元格。

2.复制并粘贴单元格

当需要将工作表中的单元格复制到其他工作簿中时，可以使用常用工具栏来完成复制工作。

1）复制单元格数据

如果多个单元格中包含相同的数据，可以使用复制功能来创建单元格中的数据。

复制数据的方法主要有以下几种方式。

（1）使用菜单命令：

选择菜单栏上【编辑】→【复制】命令，可以实现数据的复制操作，具体方法同移动单元格的方法基本相同。

（2）使用鼠标：

使用鼠标复制单元格数据是最常用的一种方法，它操作起来简单方便，具体方法如下。

选定要复制数据的单元格，将鼠标移动到该单元格的询问，当鼠标指针变为指向左上方的箭头，并且下方出现四个方向的小箭头时，按下Ctrl键的同时拖动鼠标到目标单元格，释放鼠标，即可把数据复制到目标单元格。

小贴士：

也可以选定需要被复制的单元格后，用鼠标指向该单元格，当鼠标指针变成斜向箭头，按住Ctrl键不放，然后按住鼠标左键并将选定区域拖动到目标位置，松开鼠标和Ctrl键，即可完成复制操作。

2）复制并粘贴单元格中特定的内容

复制单元格中特定内容具体方法如下：

（1）选定需要复制的单元格区域。

（2）单击常用工具栏中的【复制】按钮。

（3）选定粘贴区域的左上角单元格。

（4）执行【编辑】→【选择性粘贴】命令，打开如图4-7所示的【选择性粘贴】对话框。

图4-7【选择性粘贴】对话框

（5）选择所需的选项后，单击【确定】按钮即可粘贴单元格中的特定的内容。

4.2.3插入和删除单元格

1.插入单元格

所谓插入单元格，就是指在原来的位置插入新的单元格，而原位置的单元格将顺延到其他位置。

插入单元格的操作步骤如下。

（1）选定需要插入单元格的位置。

（2）选择【插入】→【单元格】命令，弹出【插入】对话框，如图4-8所示。

图4-8【插入】对话框

（3）选中相应的单选按钮。

（4）单击【确定】按钮，即可在所选行（或列）的上方（或左侧）插入一行（或一列）。

2.插入行、列

插入行（或列）的操作步骤如下。

（1）选取需要插入行（或列）的后面一行（或一列）。

（2）选择【插入】→【行】（或【列】）命令。

（3）或者在图所示的【插入】对话框中，选中【整行】（或【整列】）单选按钮，然后单击【确定】按钮，即可在所选行（或列）插入一行（或一列）。

小贴士：

插入一行后，按下F4键可以重复上一步操作，在Office的软件中常用该功能键或执行多次相同的操作。

3.删除单元格

所谓删除单元格，是指将选定的单元格从工作表中删除，并用周围的其他单元格来填补留下的空白。

删除单元格的操作步骤如下。

（1）选定需要删除的单元格或单元格区域。

（2）选择【编辑】→【删除】命令，打开【删除】对话框，如图4-9所示。

图4-9【删除】对话框

（3）选中相应的单选按钮。

（4）单击【确定】按钮，则可删除相应的单元格。

4.删除行和列

删除行（或列）的操作方法：

选定需要删除的行（或列），按下Delete键，或选择【编辑】→【删除】命令。

具体方法如下：

（1）选定要删除的整行或整列。

（2）选择菜单栏上【编辑】→【删除】命令，即可删除整行或整列。

4.2.4合并居中单元格

通常在设计表格时，都希望标题名称放在整个数据的中间，最为简单的方法就是使用单元格合并和居中，其具体操作步骤如下。

（1）选择要设置单元格居中的多个单元格。

（2）单击【格式】工具栏中的【合并及居中】按钮

。

即可把选定的单元格以及单元格中的内容合并且居中，如图4-10所示。

图4-10合并和居中单元格后

4.2.5调整列宽、行高

在Excel中，经常会遇到表的某个单元格中的输入信息显示不出来，而显示“##”，这时通过适当调整一下工作表的行高和列宽，就可以把信息显示出来。

1.使用对话框调整列宽与行高

在默认情况下，单元格的列宽会指定宽度为8.38，如果觉得这个数值不合适，可以结其标准列宽进行调整，在调整标准列宽前的工作表是正常默认的宽度。

（1）选择【格式】→【列】→【列宽】（或【标准列宽】）命令，打开【列宽】（或【标准列宽】）对话框。

（2）在【列宽】（或【标准列宽】）文本框中输入数值，如输入“7”，如图4-11所示。

图4-11使用对话框调整列宽与行高

小贴士：

使用【列宽】对话框调整列宽时，调整的是选中的单元格的列宽；而使用【标准列宽】对话框调整的是整个工作表的标准列宽。

（3）单击【确定】按钮，此时每列单元格都以更改后的情况向标准列宽靠拢。

同样，选中要调整行高的任意一个单元格（当然，选中多个也可以，只要适中的单元格在该行即可），然后选择【格式】→【行】→【行高】命令，再在【行高】文本框中输入数值，如图所示。

最后单击【确定】按钮，即可以设定行的高度。

如果选中的单元格分布在多个行，那么在【行高】对话框设置会同时改变多个行的行高。

2.使用鼠标调整列宽或行高

如果要调整列的宽度，将鼠标指针移到要调整宽度的列的右边框，鼠标指针变成

形状。

按下鼠标左键并拖动就可以改变列宽，随着鼠标的移动，有一条虚线指示此时释放鼠标左侧时列的右框线位置，并且指针的右上角也显示此时的列宽，如图4-12所示。

图4-12使用鼠标调整单元格列宽

调整行高也可以使用与此类似的方法。

另外也可以在鼠标指针

形状后，拖动鼠标到适当的位置时释放鼠标，即可改变单元格的宽度。

3.调整最适合的列宽与行高

如果要调整最适合的列宽，将鼠标指针移到要调整宽度的列的右边框，鼠标指针变成

形状，如图4-13所示。

双击鼠标，列宽就会自动调整到最合适的宽度，如图4-14所示。

图4-13调整前的列宽图4-14调整后的列宽

同样，在一行单元格的行号处，用鼠标双击该行的下框线，该行就会按单元格的内容自动调整到最适合的行高。

4.2.6查找和替换

如果在一张数据信息量很大的工作表中，用滚动条一屏一屏地查找要查看或编辑的单元格，将会非常繁琐，Excel的查找功能帮助用户快速查找定位，另外还可以用替换功能将需要修改的单元格内容进行替换。

1.查找

查找就是根据给定的内容，快速地定位到某个单元格。

使用查找的具体方法如下：

（1）选定要查找的单元格区域。

（2）选择菜单栏上【编辑】→【查找】命令，打开【查找和替换】对话框，如图4-15所示。

图4-15【查找和替换】对话框

（3）在该对话框的【查找内容】文本框中输入要查找的内容，单击【查找全部】按钮，Excel将会把所有符合条件的单元格以列表的方式显示出来，单击【查找下一个】按钮，Excel将选定的单元格处开始查找符合条件的第一个单元格，并使它成为活动单元格，每单击一下【查找下一个】按钮，活动单元格将变为下一个符合条件的单元格。

2.替换

替换和查找功能很相似，它能够把查找到的内容替换成用户需要的内容。

使用替换的具体方法如下：

（1）在如图所示的【查找和替换】对话框中，单击【替换】选项卡，打开【替换】选项卡，如图4-16所示。

图4-16【替换】选项卡

（2）在该对话框的【替换为】文本框中输入要替换的内容。

（3）单击【全部替换】按钮，就可以替换所选区域中所有要替换的内容。

4.2.7修改与删除数据

1．修改单元格数据

当在单元格中输入错误的数据时，可以对单元格进行修改。

修改单元格数据的方法如下：

（1）双击要修改数据的单元格，在单元格中出现闪烁的光标后，就可以像编辑Word文档一样编辑单元格中的数据。

（2）单击要修改数据的单元格，在编辑栏中显示出单元格中待编辑的数据，此时即可在编辑栏中编辑单元格的数据。

2．删除单元格数据

Excel中用户可以删除单元格中的数据，重新输入数据，删除数据的方法如下：

（1）选定要删除数据的单元格。

（2）在下一级菜单中有如下四个选项：

●【全部】：

彻底删除单元格中的全部内容、格式和批注。

●【格式】：

只删除格式，保留单元格中的数据。

●【内容】：

只删除单元格中的内容，保留其他的所有属性。

●【批注】：

只删除单元格附带的注释。

这里选择【内容】，就可以删除单元格中的数据。

在键盘上按下Delete键，也可以删除选定单元格中的数据。

小贴士：

这里讲的清除和前面讲的删除是不同的，删除会把单元格连同数据一起删除，而清除则只删除单元格中的内容、格式或批注，单元格仍保留在工作表中。

4.3工作表数据的输入

工作表由单元格组成，单元格内的数据包括格式和值两部分，输入数据时可以先设置格式再输入值，也可以先输入值，再将其格式转换成指定的格式。

无论Excel用在哪个行业，均必须明确数据的类型。

Excel提供了12种数据类型（常规、数值、货币、分数、文本、时间、日期等），最常用的是字符串、数值、时间、日期、逻辑类型的数据。

图4-17数据类型效果

从图4-17中可以看出数值型、时间型或日期型数据右对齐，字符型数据左对齐，逻辑型数据居中对齐，这是Excel默认的对齐方式。

4.3.1数据与数据类型

Excel中所有的数据都是有类型的，基本类型包括普通型、文字型和数值型。

数值型又分为日期型、时间型和逻辑型等。

文字型和数值型可以进行运算，又形成公式型等。

在菜单栏【格式】→【单元格】对话框【数字】选项卡的【分类项】下拉列表框中，有所有数据类型的列表。

如果没有特别指明类型，Excel选择【常规格式】默认。

1.文字型

由字符、汉字、英文字母及数字等组成。

也称作字符型或文本型。

在单元格中默认情况下左对齐。

如果是作为字符出现的数字被认为是数字串，其前面要加输单引号“’”引导。

2.数值型

由数字0~9、正号（+）、负号（－）、小数点（.）、乘号（*）、除号（/）、百分号（%）、指数符号（E或

e）、货币符号（￥或$）和千位符号（，）等组成。

数值型默认右对齐。

3.日期和时间型

●日期型：

以年月日为序，间以减号或右斜杠，如2012-4-3或2012/4/3。

●时间型：

以时分秒为序，间隔冒号，如21：

09：

37。

12小时制上午、下午其后加A（或AM）、P（或PM），如9：

09：

37P。

●日期和时间型：

先日期后时间，中间间隔以空格，如2012-4-321：

09：

37。

●数值化：

日期和时间可以数值化，数值化以后日期为整数部分，时间为小数部分。

4.逻辑型

逻辑型只有True（真）和False（假）两个值。

用于比较运算所产生的结果。

5.公式型

公式型以＝、+、－或以粘贴函数为前导，然后输入常数、单元格、单元格区域、函数和运算符组成的公式。

提供数据的单元格（即公式中的单元格）称为引用单元格，使用引用单元格数据的单元格（即保存公式的单元格）称为从属单元格。

6.普通型

包括各种图形图像、艺术字、公式和图表等插入的对象。

4.3.2输入基本的数据

1．输入字符型数据

字符型的数据是由汉字、字母或其他字符开头的数据，但不能是数字开头，如“甘肃兰州”、“兰州121”等。

默认情况下，字符型的数据输入后在单元格中自动左对齐。

如果输入的字符为数字，但该数字不参与整体数据的运算，则在输入时，在数字前加上一个单引号“’”，如输入“’098856”，表示输入的数字为字符型数据，如图4-18所示。

图4-18将数字以字符型数据输入

小贴士：

当在活动单元格内所输入数据的宽度超过了单元格的宽度时，有以下几种处理方法：

1）若右侧单元格无内容则当前单元格自动扩宽，变为宽单元格。

2）若右侧单元格有内容，则超出的部分自动隐藏。

此时可通过双击列标后的竖线或选择【格式】→【列】→【最

适合的列宽】命令，将隐藏内容显示出来。

3）用Alt+Enter组合键强断行，实现在一个单元格内输入几行数据。

2．输入数值型数据

数值型数据是由数字0~9、正号、负号、小数点、“/”、“%”、指数符号“E”或“e”、货币符号“￥”或“$”和千位符号“，”等组成。

单击工作表中的任意单元格后输入数据即可，默认情况下，数值型的数据在输入后自动右对齐。

在输入数据时，需要注意收下几点：

●如果要输入一个负值数据，那么在输入时首先输入一个负号“—”，或在数字前加一个小括号。

如输入—100或（100）都可以得到一个相同的值—100。

●如果要输入一个分数。

分数分为真分数和假分数两种，输入真分数时，先输入数字0和空格，再输入内容；输入假分数时在系数和分数之间还要输入一个空格。

●如果要输入一个百分数，首先输入数字，然后再输入一个“%”。

3．输入时间和日期型数据

在某单元格内输入“5-2”时，显示结果“5月2日”；在某单元格内输入“=5-1”，则显示结果为1。

这说明当输入的数据符合某种格式时，Excel将自动以该种格式存储数据。

所以当输入“5/2”、“5-2”时，系统默认为“5月2日”；当输入“11：

35”、“22：

45”时，系统默认是时间；也可以在单元格内同时输入日期和时间，二者之间以空格隔开。

输入具有某种格式的日期和时间，具体操作方法如下：

在单元格中输入日期和时间的数值，再右击该单元格，在弹出的快捷菜单中选择【设置单元格格式】命令，打开【单元格格式】对话框，如图4-19所示。

选择【数字】选项卡，在【分类】列表框中选择【日期】或【时间】选项，然后在右侧选择需要的类型即可。

图4-19【数字】选项卡

4．输入逻辑型数据

Excel中某条件成立用TRUE表示，否则用FLASE表示。

如在B2中输入“=7>2”，则显示值为TRUE。

如图4-20所示。

图4-20逻辑型数据输入效果

5．输入特殊符号

除了在单元格中输入文本、数值和日期时间等数据外，还可以在单元格中输入特殊符号。

输入特殊符号的具体方法如下：

（1）选定要输入特殊符号的单元格。

（2）选择菜单栏【插入】→【特殊符号】命令，打开【插入特殊符号】对话框，如图4-21所示。

图4-21【插入特殊符号】对话框

（3）在该对话框若